error @ ExecuteScalar() ? need help !

i am executing a query in DataGrid1_itemCOmmend.

Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.
Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand

Dim intCount As Integer
Dim cmdvrfy As SqlCommand
cmdvrfy = New SqlCommand("Select * FROM tbl_rating Where DocumentID
=" & DocumentID & "AND ip =" & UserIP & ";", conPubs)
conPubs.Open()
cmdvrfy.Connection = conPubs
cmdvrfy.CommandType = CommandType.Text
intCount = cmdvrfy.ExecuteScalar()
conPubs.Close()
If intCount = 0 Then '127.0.0.1
'Opens the Panel for the user !
Dim MyPanel As Panel
MyPanel = (e.Item.FindControl("Panel1"))
MyPanel.Visible = True
Else
MsgBox("You have already rated this !!!!")
End If

End Sub

But i get this error msg. that is

Line 1: Incorrect syntax near '.0'.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect
syntax near '.0'.

Source Error:


Line 82: cmdvrfy.Connection = conPubs
Line 83: cmdvrfy.CommandType = CommandType.Text
Line 84: intCount = cmdvrfy.ExecuteScalar()
Line 85: conPubs.Close()
Line 86: If UserIP = "" Then '127.0.0.1


Source File: c:\inetpub\wwwroot\Rating\star_rating\WebForm1.asp x.vb

HELP me

jk velu

jk velu wrote:
> i am executing a query in DataGrid1_itemCOmmend.
>
> Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As
> System. Web.UI.WebControls.DataGridCommandEventArgs) Handles
> DataGrid1.ItemCommand
>
> Dim intCount As Integer
> Dim cmdvrfy As SqlCommand
> cmdvrfy = New SqlCommand("Select * FROM tbl_rating Where
> DocumentID =" & DocumentID & "AND ip =" & UserIP & ";", conPubs)

UserIP is a string, so you should enclose it in quotes
... "AND ip = ' " & UserIP & " ';" ... (note: extra spaces added for clarity - remove them)

By the way, it is better (safer, maybe even faster) to use parameters instead
of inline values.

Hans Kesting

> conPubs.Open()
> cmdvrfy.Connection = conPubs
> cmdvrfy.CommandType = CommandType.Text
> intCount = cmdvrfy.ExecuteScalar()
> conPubs.Close()
> If intCount = 0 Then '127.0.0.1
> 'Opens the Panel for the user !
> Dim MyPanel As Panel
> MyPanel = (e.Item.FindControl("Panel1"))
> MyPanel.Visible = True
> Else
> MsgBox("You have already rated this !!!!")
> End If
>
> End Sub
>
> But i get this error msg. that is
>
> Line 1: Incorrect syntax near '.0'.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
>
> Exception Details: System.Data.SqlClient.SqlException: Line 1:
> Incorrect syntax near '.0'.
>
> Source Error:
>
>
> Line 82: cmdvrfy.Connection = conPubs
> Line 83: cmdvrfy.CommandType = CommandType.Text
> Line 84: intCount = cmdvrfy.ExecuteScalar()
> Line 85: conPubs.Close()
> Line 86: If UserIP = "" Then '127.0.0.1
>
>
> Source File: c:\inetpub\wwwroot\Rating\star_rating\WebForm1.asp x.vb
>
> HELP me

Hans Kesting

Hey Hans Kesting,

I was breaking my head for past 2 days exploring ExecuteScalar(). That was
really help full.
By da way, you said about something “use parameters�. I too read about it
but not sure about its implementation.

Would you mind sharing you thoughts on using parameters to this context I
stated below?

Dim UserIP As String
UserIP = (Request.UserHostAddress)
Dim intCount As Integer
Dim cmdvrfy As SqlCommand
cmdvrfy = New SqlCommand("Select * FROM tbl_rating Where DocumentID
=" & DocumentID & "AND ip ='" & UserIP & "';", conPubs)
conPubs.Open()
cmdvrfy.Connection = conPubs
cmdvrfy.CommandType = CommandType.Text
intCount = cmdvrfy.ExecuteScalar()
conPubs.Close()

thx
JK


"Hans Kesting" wrote:

> jk velu wrote:
> > i am executing a query in DataGrid1_itemCOmmend.
> >
> > Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As
> > System. Web.UI.WebControls.DataGridCommandEventArgs) Handles
> > DataGrid1.ItemCommand
> >
> > Dim intCount As Integer
> > Dim cmdvrfy As SqlCommand
> > cmdvrfy = New SqlCommand("Select * FROM tbl_rating Where
> > DocumentID =" & DocumentID & "AND ip =" & UserIP & ";", conPubs)
>
> UserIP is a string, so you should enclose it in quotes
> ... "AND ip = ' " & UserIP & " ';" ... (note: extra spaces added for clarity - remove them)
>
> By the way, it is better (safer, maybe even faster) to use parameters instead
> of inline values.
>
> Hans Kesting
>
> > conPubs.Open()
> > cmdvrfy.Connection = conPubs
> > cmdvrfy.CommandType = CommandType.Text
> > intCount = cmdvrfy.ExecuteScalar()
> > conPubs.Close()
> > If intCount = 0 Then '127.0.0.1
> > 'Opens the Panel for the user !
> > Dim MyPanel As Panel
> > MyPanel = (e.Item.FindControl("Panel1"))
> > MyPanel.Visible = True
> > Else
> > MsgBox("You have already rated this !!!!")
> > End If
> >
> > End Sub
> >
> > But i get this error msg. that is
> >
> > Line 1: Incorrect syntax near '.0'.
> > Description: An unhandled exception occurred during the execution of
> > the current web request. Please review the stack trace for more
> > information about the error and where it originated in the code.
> >
> > Exception Details: System.Data.SqlClient.SqlException: Line 1:
> > Incorrect syntax near '.0'.
> >
> > Source Error:
> >
> >
> > Line 82: cmdvrfy.Connection = conPubs
> > Line 83: cmdvrfy.CommandType = CommandType.Text
> > Line 84: intCount = cmdvrfy.ExecuteScalar()
> > Line 85: conPubs.Close()
> > Line 86: If UserIP = "" Then '127.0.0.1

> > Source File: c:\inetpub\wwwroot\Rating\star_rating\WebForm1.asp x.vb
> >
> > HELP me

=?Utf-8?B?dmVsdQ==?=

for an example, see here (watch for wrap):
http://msdn.microsoft.com/library/de...classtopic.asp
or here
http://www.dotnetjunkies.com/quickst...ess.aspx#param

Basically:
- write the query with named placeholders instead of literal values (note: no need for delimeters now)
- create parameters with correct type and value, and add them to the command object
- execute the query

you query would look like
Select * FROM tbl_rating Where DocumentID = @docid AND ip = @ip

advantage:
- better performance
- no problems with embedded quotes in strings or with dates
- not vulnerable to "sql injection" attacks

Hans Kesting


velu wrote:
> Hey Hans Kesting,
>
> I was breaking my head for past 2 days exploring ExecuteScalar().
> That was really help full.
> By da way, you said about something "use parameters". I too read
> about it but not sure about its implementation.
>
> Would you mind sharing you thoughts on using parameters to this
> context I stated below?
>
> Dim UserIP As String
> UserIP = (Request.UserHostAddress)
> Dim intCount As Integer
> Dim cmdvrfy As SqlCommand
> cmdvrfy = New SqlCommand("Select * FROM tbl_rating Where
> DocumentID =" & DocumentID & "AND ip ='" & UserIP & "';", conPubs)
> conPubs.Open()
> cmdvrfy.Connection = conPubs
> cmdvrfy.CommandType = CommandType.Text
> intCount = cmdvrfy.ExecuteScalar()
> conPubs.Close()
>
> thx
> JK
>
>
> "Hans Kesting" wrote:
>
>> jk velu wrote:
>>> i am executing a query in DataGrid1_itemCOmmend.
>>>
>>> Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As
>>> System. Web.UI.WebControls.DataGridCommandEventArgs) Handles
>>> DataGrid1.ItemCommand
>>>
>>> Dim intCount As Integer
>>> Dim cmdvrfy As SqlCommand
>>> cmdvrfy = New SqlCommand("Select * FROM tbl_rating Where
>>> DocumentID =" & DocumentID & "AND ip =" & UserIP & ";", conPubs)
>>
>> UserIP is a string, so you should enclose it in quotes
>> ... "AND ip = ' " & UserIP & " ';" ... (note: extra spaces added for
>> clarity - remove them)
>>
>> By the way, it is better (safer, maybe even faster) to use
>> parameters instead
>> of inline values.
>>
>> Hans Kesting
>>
>>> conPubs.Open()
>>> cmdvrfy.Connection = conPubs
>>> cmdvrfy.CommandType = CommandType.Text
>>> intCount = cmdvrfy.ExecuteScalar()
>>> conPubs.Close()
>>> If intCount = 0 Then '127.0.0.1
>>> 'Opens the Panel for the user !
>>> Dim MyPanel As Panel
>>> MyPanel = (e.Item.FindControl("Panel1"))
>>> MyPanel.Visible = True
>>> Else
>>> MsgBox("You have already rated this !!!!")
>>> End If
>>>
>>> End Sub
>>>
>>> But i get this error msg. that is
>>>
>>> Line 1: Incorrect syntax near '.0'.
>>> Description: An unhandled exception occurred during the execution of
>>> the current web request. Please review the stack trace for more
>>> information about the error and where it originated in the code.
>>>
>>> Exception Details: System.Data.SqlClient.SqlException: Line 1:
>>> Incorrect syntax near '.0'.
>>>
>>> Source Error:
>>>
>>>
>>> Line 82: cmdvrfy.Connection = conPubs
>>> Line 83: cmdvrfy.CommandType = CommandType.Text
>>> Line 84: intCount = cmdvrfy.ExecuteScalar()
>>> Line 85: conPubs.Close()
>>> Line 86: If UserIP = "" Then '127.0.0.1
>
>>> Source File: c:\inetpub\wwwroot\Rating\star_rating\WebForm1.asp x.vb
>>>
>>> HELP me

Hans Kesting