Incorrect syntax near the keyword 'and'.


this is my url,
localhost/test.aspx?ID=10&id1=1

i get this error , while executing my application.
Incorrect syntax near the keyword 'and'.

Possibly , am not gettin querystring value .in the sql query, hw can i solve this issue..?
Dim ID as String
Dim id1 as String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not (Page.IsPostBack) Then
            ID = Request.QueryString("ID")
            id1 = Request.QueryString("id1")
            Get()
        End If
    End Sub
 
 
    Public Sub Get()        
       cmd= New SqlCommand("SELECT * FROM tbl1  WHERE sID = " & ID & " and cid =" & id1 & "", myConnection)
        myConnection.Open()
        dr= cmd.ExecuteReader()
        While (dr.Read())
            response.write(dr.Item("ci")
        End while

Open in new window

LVL 18
Rajar AhmedConsultantAsked:
Who is Participating?
 
GiftsonDJohnConnect With a Mentor Commented:
Directly use the querystring values in sql like below

cmd= New SqlCommand("SELECT * FROM tbl1  WHERE sID =@sID and cid =@cid", myConnection)
cmd.Parameters.AddWithValue("@sID", Request("ID"))
cmd.Parameters.AddWithValue("@cid", Request("id1"))
0
 
dineeshCommented:

Hi

Is sID & Cid text columns on the DB?

can u give a sample of the ID & id1 that comes from qstring?

regards
Dinesh
0
 
Rajar AhmedConsultantAuthor Commented:

 This is sample url which comes....
 localhost/test.aspx?ID=10&id1=1
 
 
 sid and cid are not a text column  it is a integer column ...in the database
 

<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
GiftsonDJohnCommented:
The best practice to pass values to Sql is by using SqlParameters.

cmd= New SqlCommand("SELECT * FROM tbl1  WHERE sID =@sID and cid =@cid", myConnection)
cmd.Parameters.AddWithValue("@sID", ID)
cmd.Parameters.AddWithValue("@cid", id1)
       
0
 
silemoneCommented:
this error is actually from the sql...not your code...  Giftson offers a viable solution...if that doesn't work...try inserting ' ' in you select statement...i.e.  make sure you're passing right types...if @cid is varchar, then it needs '' around it...
0
 
Rajar AhmedConsultantAuthor Commented:
but , i checked with profiler , and managed to found that , like this
 
exec sp_executesql N'SELECT * FROM tbl1 WHERE sid = @sID and cid = @cid)', N'@sID nvarchar(4000),@cid nvarchar(4000)', @sID = default, @cid = default


 now i get this error,
Prepared statement '(@sID nvarchar(4000),@cid nvarchar(4000))SELECT * FROM tbl1' expects parameter @sID, which was not supplied.
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
0
 
SharathData EngineerCommented:
The dynamic sql is wrong. extra parentesis in the WHERE clasue. There should be some values passed with paraments @sId and @cId. not default.
exec sp_executesql N'SELECT * FROM tbl1 WHERE sid = @sID and cid = @cid', 
                   N'@sID nvarchar(4000),@cid nvarchar(4000)', 
                   @sID = 'some value', @cid = 'some other value'

Open in new window

0
 
SharathData EngineerCommented:
i am not sure but can you try this.
Dim ID as String
Dim id1 as String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not (Page.IsPostBack) Then
            ID = Request.QueryString("ID")
            id1 = Request.QueryString("id1")
            Get()
        End If
    End Sub
 
 
    Public Sub Get()        
       cmd= New SqlCommand("SELECT * FROM tbl1  WHERE sID = '" & ID & "' and cid = '" & id1 & "'", myConnection)
        myConnection.Open()
        dr= cmd.ExecuteReader()
        While (dr.Read())
            response.write(dr.Item("ci")
        End while

Open in new window

0
 
srikanthreddyn143Connect With a Mentor Commented:
Try using this instead of yours

 cmd= New SqlCommand("SELECT * FROM tbl1  WHERE sID = " & ID.ToString() & " and cid =" & id1.ToString(), myConnection)
0
 
Rajar AhmedConsultantAuthor Commented:
when the page is again refreshed , the value of those strings gets null and hence am gettin this error....first time am gettin properly ,
0
 
srikanthreddyn143Commented:
when page is refreshed, check the querystring values?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.