[ I can't insert an integer into sqlite table using vb.net ]

Inside vb.net the code below works

cmd.CommandText = "CREATE TABLE Notes ( num INTEGER, name TEXT)"
Try
      cmd.ExecuteNonQuery()
Catch ex As Exception
      MsgBox("Error during save operation: " & ex.Message)
End Try

cmd.CommandText = "insert into Notes(num , name ) VALUES( 3, 'Bob') "
Try
    Console.WriteLine(cmd.ExecuteNonQuery())
Catch ex As Exception
    MsgBox("Error during save operation: " & ex.Message)
End Try

But I want to put an Integer variable instead of inserting the number 3. I tried this :

Dim count As Integer = 1
cmd.CommandText = "insert into Notes(num , name ) VALUES(" + count " + , 'Bob') "

Try
     Console.WriteLine(cmd.ExecuteNonQuery())
Catch ex As Exception
      MsgBox("Error during save operation: " & ex.Message)
End Try

But then I have the exception:

Conversion from string "insert into Notes(isFirst" to type 'Double' is not valid.

I know that the line:

"insert into Notes(num , name ) VALUES(" + count + ", 'Bob') "

seems to be totally wrong but I cannot find any other solution, since I tried them all.

How can I insert the count variable into the table?

Thanks in advance.

Answer 1

You should parameterize the query. I'm not sure about the exact syntax for SQLite, as I use Oracle, but this should definitely get you on the right track.

Dim count As Integer
'Whatever code you need to get the count from the user.

cmd.CommandText = "insert into Notes(num , name ) VALUES( @count, 'Bob')"

Dim myParam As New SQLiteParameter("@count")
cmd.Parameters.Add(myParam)

'Continue with executing the query, storing the result, etc.

Answer 2

You have the "+" sign on the wrong side of the double quotes:

cmd.CommandText = "insert into Notes(num , name ) VALUES(" + count " + , 'Bob') "

should be

cmd.CommandText = "insert into Notes(num , name ) VALUES(" & count & ", 'Bob') "

And as ChrisStauffer mentions, use parameters. Always.