Prepared Statements in Vb.Net

Prepared Statements in VB.NET

Prepared statements are nothing but Parametrized SqlCommands enclosed in a Transaction.

For example, this is a Prepared Statement:

Using c As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
   c.Open()
using mytransaction = c.BeginTransaction()

   Dim command = New SqlCommand("INSERT INTO yourtable(image) values (@image)", c)
   ''# this is specific to the FileUploadControl but the idea is to get the
   ''#image in a byte array; however you do it, it doesn't matter
    Dim buffer(FileUpload1.PostedFile.ContentLength) As Byte
    FileUpload1.PostedFile.InputStream.Read(buffer, 0, buffer.Length)
    command.Parameters.AddWithValue("@image", buffer)
    command.ExecuteNonQuery()    
 mytransaction .Commit()
End Using
End Using

What is a simple example of using prepared statements in vb net using Provider=Microsoft.Jet.OLEDB.4.0

This is a version of your code with parameters and correct use of disposable objects

Private Sub test_password()
    ' Do not return all the fields, just count....'
    ' ...and password is a reserved keyword in access...'
    ' need square brackets around it otherwise ...syntax error...'
    Dim str = "SELECT COUNT(*) FROM users where [password] =  @pass"
    Using myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;....")
    Using cmd = New OleDbCommand(str, myConnection)

        myConnection.Open()
        cmd.Parameters.Add("@pass", OleDbType.VarWChar).Value = pass.Text
        ' ExecuteScalar returns the first column of the first row from your query'
        Dim counter = Convert.ToInt32(cmd.ExecuteScalar)
        If counter > 0 Then
             MsgBox("password was found")
        Else
             MsgBox("password was NOT found")
        End If
    End Using 
    End Using    
End Sub

The Using Statement ensures that every disposable object (connection and command in this context) are correctly disposed when you have finished to use them.

The parameter placeholder @pass is inserted in place of the actual value and a parameter is added to the command parameter collection with the matching datatype expected by the field and the correct value.

Note that in OleDb, parameters placeholders are usually represented by a question mark ? but Access accepts also parameters placeholders expressed with the @xxxx format. However, the parameters should be added in the parameter collection, following the same order in which their placeholders appears in the command text.

Prepared Statement using VB.NET, MySql, and OleDbConnection

Try this code:

Using cmd As New OleDbCommand("EXEC dbo.SearchActivities @value1 = ?, @value2 = ?", New OleDbConnection(mySqlConn))
  cmd.Parameters.AddWithValue("@value1", LoggedUser.Text)
  cmd.Parameters.AddWithValue("@value2", searchAFEs.Text)

Writing to mysql with prepared statement

The error is not the result of the parameter count not matching the number of the supplied parameters.

In your insert statement you did not specify which columns you would like to insert values into, therefore mysql expects you to provide values for all columns in the table. case_updates table has more than 4 columns, hence the error message. Either you need to supply values to all columns in the table in the values() clause or you need to list the columns you would like to insert into.

INSERT INTO case_updates(lid, updatetext, entrytype, `user`) ...

(assuming your field names match your parameter names)

Prepared Statements For ODBC in VB.net

nevermind, solved it myself.

Dim cmd As String = "insert into sites(id) values(?)"

Seems that it still uses the question marks, contrary to what i have found on the web

What is wrong with my prepared statement?

When using prepared statements from an API you don't use the SQL PREPARE syntax but a specific API function. For .Net, it is SqlCommand.Prepare.

The documentation provides a pretty good example.

Add Parameter to Prepared Statement For ODBC in VB.net

You can try to add command parameter the way shown in the post you referred :

Dim cmd As String = "insert into sites(field1, field2) values(?,?)"
Dim odcmd As New OdbcCommand

odcmd.CommandText = cmd

odcmd.Parameters.Add("@field1", OdbcType.Int)
odcmd.Parameters("@field1").Value = 5
odcmd.Parameters.Add("@field2", OdbcType.Int)
odcmd.Parameters("@field2").Value = 8

But there is an important point to note, that isn't explained there :

The order in which OdbcParameter objects are added to the OdbcParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Related question : Can ODBC parameter place holders be named?

Multiple SQL statement using VB

The string concatenation operator in VB.NET is the ampersand character (&).

You could use also the + symbol but it is not recommended because it could cause side effects when operands are ambiguos.

Said that, you could use just one SqlCommand passing the two command texts together separated by a semicolon.

Also note that you should always use a parameterized query when possible,

Alas the first query cannot be parameterized because you cannot use a parameter to express a table name. So please be sure to check the content of this TextBox2 before using it.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Using con  = New SqlConnection(........)
        Using cmd = New SqlCommand("CREATE TABLE " & TextBox2.Text & _
                    " (myId INTEGER PRIMARY KEY, myName CHAR(50), myAddress CHAR(255), " & _
                    " myBalance FLOAT);Update Tarrifs Set Name=@name", con)
             con.Open()
             cmd.Parameters.AddWithValue("@name", TextBox2.Text)
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

Another good practice is to apply the Using statement when you deal with Disposable objects like the SqlConnection and the SqlCommand. This will ensure the proper closing of the connection and the disposing of both the connection and the command

---

Related Topics