problem with sql statement using vb.net

Question

Hi

I've got some problems. I've got this code:

Dim infoID As Integer = objCommand1.ExecuteScalar()
Dim strSQL2 As String = "insert into feedBackHotel (infoID, feedBackView) values("   + infoID + ",'" + FeedBack + "')"
Dim objCommand2 As New SqlCommand(strSQL2, conn)
objCommand2.ExecuteNonQuery()

The problem here is..the strSQL2 can capture the infoID from the previous statement, but it didn't insert into the database. It will pop out this error:

"Conversion from string "insert into feedBackHotel (infoI" to type 'Double' is not valid."

in both table related, use same data type (int)

but for the feedBackHotel's infoID I allow it to be null..because if I make it not null, it will show another error..

I'm using VB.NET ..Can anyone help?

Answer 1

first, you're trying to create a string from types that won't concatenate naturally. you should use the .toString() method.

second, DON'T DO THAT. :)

you open yourself up to a world of hurt when dynamically generating sql statements like that. use something like

Dim strSQL2 As String = "insert into feedBackHotel (infoID, feedBackView) values(@InfoID,@FeedBackView)"

and then

objCommand2.parameters.addwithvalue("@FeedBackView",FeedBack)
objCommand2.parameters.addwithvalue("@infoID",infoID)

third, hungarian notation?? and not used consistently? if you're going to use something awful like hungarian notation, at least use it all the time, otherwise you're not even adhering to bad standards well.

Answer 2

You're attempting to concatenate a double to a string, which is causing your error. You're also building unparameterized SQL statements, which is a deadly sin. Using parameters will solve both your problems. Try something like this:

Dim strSQL2 As String = "insert into feedBackHotel (infoID, feedBackView) values(@infoID, @Feedback)"

Dim objCommand2 As New SqlCommand(strSQL2, conn)
objCommand2.Parameters.AddWithValue("@infoID", infoID)
objCommand2.Parameters.AddWithValue("@Feedback", FeedBack)
objCommand2.ExecuteNonQuery()