February 3, 2012 at 4:15 am
Hi Guys,
Any idea why the following code produces the following errors and any ways I can fix them?
<WebMethod()>-
<XmlInclude(GetType(SAAMSubFilter))> _
Public Function GetSubscribersFilter(ByVal fromSearch As Integer, ByVal SearchId As Integer, ByVal ExactMatch As Integer, ByVal searchFor As String, ByVal thisPub As Integer, ByVal thisSubType As Integer, ByVal ZC As Int16, ByVal thisZone As Int16, ByVal thisCountry As Int16, ByVal thisIndustry As Integer) As SAAMSubFilter()
Dim MyConString As String = "Server=xx.xx.xx.xxx;uid=yyyyy;pwd=zzzzzz;database=saam"
Dim returnedList As New ArrayList()
Dim DbConnection As New System.Data.SqlClient.SqlConnection(MyConString)
DbConnection.Open()
Dim DbCommand As SqlCommand = DbConnection.CreateCommand()
Dim strsql As String = " "
strsql = "Select DISTINCT row_number() over (order by SubId) as rownum,saamfilter.SubId as Id,"
strsql += " saamfilter.Reference, saamfilter.Title, saamfilter.FirstName AS Forename, saamfilter.Surname, saamfilter.SubId, saamfilter.Company, "
strsql += " saamfilter.Add1 AS [Address Line 1], saamfilter.PostZip AS Zip, Country.Name AS Country"
strsql += " FROM SAAMFilter INNER JOIN"
strsql += " Country ON SAAMFilter.Country = Country.CountryId where "
Errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
Msg 103, Level 15, State 4, Line 12
The identifier that starts with ' saamfilter.Reference, saamfilter.Title, saamfilter.FirstName AS Forename, saamfilter.Surname, saamfilter.SubId, saamfilter.Comp' is too long. Maximum length is 128.
Thanks guys - I may have a few more questions today! :rolleyes: 🙂
Editor: Masked credentials
February 3, 2012 at 4:34 am
Looks like you're trying to run a VB script through SSMS.
John
February 3, 2012 at 4:38 am
Admittedly I am - what should I do?
February 3, 2012 at 4:42 am
Is there a way I can change minimal things without having to completely rewrite the query?
February 3, 2012 at 4:42 am
Depends on what you're trying to achieve. Where did you get the VB code from? If it's a question about how to compile VB code so that you can execute it, I'm not the best person to answer that.
John
February 3, 2012 at 4:49 am
My programmer boss gave it to me...Considering he has 20 years of experience I was hoping it would work! http://www.sqlservercentral.com/Forums/Skins%5CClassic%5CImages/MessageIcons/Crazy.gif
I'm really just asking is there a way of getting rid of any of he code that's ther to allow the query to execute succesfully?
Regards,
Jon
February 3, 2012 at 4:57 am
I'm still trying to achieve the same search and filter functionality on a webpage, full code is:
<WebMethod()>-
<XmlInclude(GetType(SAAMSubFilter))> _
Public Function GetSubscribersFilter(ByVal fromSearch As Integer, ByVal SearchId As Integer, ByVal ExactMatch As Integer, ByVal searchFor As String, ByVal thisPub As Integer, ByVal thisSubType As Integer, ByVal ZC As Int16, ByVal thisZone As Int16, ByVal thisCountry As Int16, ByVal thisIndustry As Integer) As SAAMSubFilter()
Dim MyConString As String = "Server=xx.xx.xxx.xxx;uid=yyyy;pwd=zzzzzzz;database=saam"
Dim returnedList As New ArrayList()
Dim DbConnection As New System.Data.SqlClient.SqlConnection(MyConString)
DbConnection.Open()
Dim DbCommand As SqlCommand = DbConnection.CreateCommand()
Dim strsql As String = " "
strsql = "Select DISTINCT row_number() over (order by SubId) as rownum,saamfilter.SubId as Id,"
strsql += " saamfilter.Reference, saamfilter.Title, saamfilter.FirstName AS Forename, saamfilter.Surname, saamfilter.SubId, saamfilter.Company, "
strsql += " saamfilter.Add1 AS [Address Line 1], saamfilter.PostZip AS Zip, Country.Name AS Country"
strsql += " FROM SAAMFilter INNER JOIN"
strsql += " Country ON SAAMFilter.Country = Country.CountryId where "
Dim strCSV As String = ''
If thisPub <> -1
If Right(strsql, 6) = "where "
strsql += "pubid in ( " & thisPub & " ) "
Else if
strsql += " and pubid in ( ' & thisPub & ' ) '
If thisSubType <> -1 Then
If Right(strsql, 6) = "where " Then
strsql += " SubscripType in ( " & thisSubType & " ) "
Else
strsql += " and SubscripType in ( " & thisSubType & " ) "
End If
End If
If thisIndustry <> -1 Then
If Right(strsql, 6) = "where " Then
strsql += " Industry in ( " & thisIndustry & " ) "
Else
strsql += " and Industry in ( " & thisIndustry & " ) "
end
BEGIN
If ZC <> -1 Then
If thisZone <> -1 Then
If Right(strsql, 6) = "where " Then
strsql += " SAAMFilter.Zone in ( " & thisZone & " ) "
Else
strsql += " and SAAMFilter.Zone in ( " & thisZone & " ) "
Else
If Right(strsql, 6) = "where " Then
strsql += " Country in ( " & thisCountry & " ) "
Else
strsql += " and Country in ( " & thisCountry & " ) "
End If
END
If fromSearch = 1 Then
Dim thisSearch As String = " "
Select Case SearchId
Case 1
thisSearch = "Surname"
Case 2
thisSearch = "SubId"
Case 3
thisSearch = "Company"
Case 4
thisSearch = "PostZip"
End Select
;
Select Case ExactMatch
Case -1
If Right(strsql, 6) = "where " Then
strsql += thisSearch & " = '" & searchFor & "'"
Else
strsql += " and " & thisSearch & " = '" & searchFor & "'"
Case 0
If Right(strsql, 6) = "where " Then
strsql += thisSearch & " Like '" & searchFor & "%'"
Else
strsql += " and " & thisSearch & " Like '" & searchFor & "%'"
Case 1
If Right(strsql, 6) = "where " Then
strsql += thisSearch & " Like '%" & searchFor & "%'"
Else
strsql += " and " & thisSearch & " Like '%" & searchFor & "%'"
End Select
strsql += ' order by SAAMFilter.Surname,SAAMFilter.FirstName "
;
DbCommand.CommandText = strsql
Dim retString As String = " "
Dim fCount As Integer = DbReader.FieldCount
For i As Integer = 0 To fCount - 1
retString = DbReader.GetName(i)
Next
Dim DbReader As SqlDataReader = DbCommand.ExecuteReader()
while(dbreader.Read())
Dim temp As New SAAMSubFilter()
temp.thisRow = DbReader.GetInt64(0)
temp.Id = DbReader.GetInt32(1)
temp.Reference = " " & DbReader.GetSqlString(2).ToString
temp.Title = " " & DbReader.GetSqlString(3).ToString
temp.Forename = " " & DbReader.GetSqlString(4).ToString
temp.Surname = " " & DbReader.GetSqlString(5).ToString
temp.Subid = DbReader.GetInt32(6)
temp.Company = " " & DbReader.GetSqlString(7).ToString
temp.Address_Line_1 = " " & DbReader.GetSqlString(8).ToString
temp.Zip = " " & DbReader.GetSqlString(9).ToString
temp.Country = " " & DbReader.GetSqlString(10).ToString
returnedList.Add(temp)
End While
DbReader.Close,
DbCommand.Dispose,
DbConnection.Close
Dim ArrayofSAAMSubFilter As SAAMSubFilter() = New SAAMSubFilter(returnedList.Count - 1) {}
For i As Integer = 0 To returnedList.Count - 1
ArrayofSAAMSubFilter(i) = DirectCast(returnedList(i), SAAMSubFilter)
Next
Return ArrayofSAAMSubFilter
End Function
;
SQL Server is green all the way down the left but I'm obviously still getting a lot of errors!
Regards,
Jon
February 3, 2012 at 5:09 am
I hope you have a decent firewall... you've posted IP address, logon and password in your connection string!!
February 3, 2012 at 5:12 am
My programmer boss gave it to me...Considering he has 20 years of experience I was hoping it would work!
Maybe it would if it were run from the right place - but definitely not SSMS.
I'm really just asking is there a way of getting rid of any of he code that's ther to allow the query to execute succesfully?
Well, you could extract the SQL from the VB and run that. But I've a feeling that won't do what you're trying to achieve, which is to call the code from some application.
I'm still trying to achieve the same search and filter functionality on a webpage...
Presumably it'll work from the web page, provided the VB compiles and the SQL is syntactically correct and so on.
SQL Server is green all the way down the left but I'm obviously still getting a lot of errors!
What do you mean by "green all the way down"? What errors are you getting?
John
February 3, 2012 at 5:12 am
Dont worry too much about it!
Does anybody know of there is a tool to turn vba code into a sql statement? U seem to be able to do it the other way round which is a bit irritating! :crazy:
February 3, 2012 at 5:15 am
Errors are the following:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.
Msg 103, Level 15, State 4, Line 12
The identifier that starts with ' saamfilter.Reference, saamfilter.Title, saamfilter.FirstName AS Forename, saamfilter.Surname, saamfilter.SubId, saamfilter.Comp' is too long. Maximum length is 128.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'strsql'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '='.
Msg 103, Level 15, State 4, Line 68
The identifier that starts with '
End Select
;
Select Case ExactMatch
Case -1
If Right(strsql, 6' is too long. Maximum length is 128.
Msg 156, Level 15, State 1, Line 103
Incorrect syntax near the keyword 'Read'.
Msg 156, Level 15, State 1, Line 123
Incorrect syntax near the keyword 'Close'.
Msg 102, Level 15, State 1, Line 126
Incorrect syntax near 'Dim'.
Msg 178, Level 15, State 1, Line 130
A RETURN statement with a return value cannot be used in this context.
February 3, 2012 at 5:17 am
The 'Don't worry about it too much' wasn't for you John - still need your help!
February 3, 2012 at 5:18 am
No, that's the errors you get if you run it from SSMS. You said you were running it from a web page. I think you need to ask your programmer boss how to do this, or ask him to assign you some programmer resource to help you out.
John
February 3, 2012 at 5:41 am
So there is no way of converting vb code to SQL and nobody knows what I should do? 🙁
February 3, 2012 at 6:12 am
can anyone give me some example working T-SQL2005 code that will work for the functionality I'm trying to achieve (search or filter on particular fields on a web page) ?
Regards
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply
