• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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

0
Rajar Ahmed
Asked:
Rajar Ahmed
  • 3
  • 2
  • 2
  • +3
2 Solutions
 
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
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
GiftsonDJohnCommented:
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
 
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
 
srikanthreddyn143Commented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now