error @ ExecuteScalar() ? need help !

[jk velu]

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.aspx.vb

HELP me

 

[Hans Kesting]

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

 

[Guest]

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]

for an example, see here (watch for wrap):
http://msdn.microsoft.com/library/d...systemdatasqlclientsqlparameterclasstopic.asp
or here
http://www.dotnetjunkies.com/quickstart/aspplus/doc/webdataaccess.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