ASPX VB.Net OleDb Insert Parameter into Query

Question

This is my first time writing in VB.Net for aspx pages.

The problem I having is that the parameter is not going into the query at the line for cmd.Parameters.Add.

The error I am getting is

No value given for one or more required parameters.

on the line

reader = cmd.ExecuteReader;

I have tried:

• Adding the PARAMETERS at the top of the query like I have shown;
• Removing and Adding [] around the parameter;
• Changing OleDbType.Integer to OleDbType.SmallInt or OleDbType.BigInt

I know the query works as I can place it into MS Access and will run once I add the parameter. But not when I run it in Visual Studio.

Dim reader As OleDbDataReader
Dim cmd As OleDbCommand
Dim SQL As String = "PARAMETERS [@ID] Long; " &
                    "SELECT tblField.FieldName, " &
                           "tblField.FieldCaption, " &
                           "tblField.FieldMinCharNum, " &
                           "tblField.FieldMaxCharNum, " &
                           "tblField.FieldDefault, " &
                           "tblField.FieldSection, " &
                           "tblField.FirstQuestion, " &
                           "tblField.FieldDescription, " &
                           "tblField.FieldRegEx " &
                     "FROM tblField " &
                     "WHERE tblField.FieldID = [@ID];"

cmd = New OleDbCommand(SQL, Connection.Connection)
cmd.Parameters.Add("[@ID]", OleDbType.Integer).Value = ID

reader = cmd.ExecuteReader

I have a work around to make it work by just pre-inserting the parameter into the SQL string. But I want to make this work for other areas of the page that are yet to be written. Where user inputs are coming back into database so inputs are sanitised.

Answer 1

OLEDB doesn't use @ to identify parameters. It uses ? and allocates parameters in the order they appear in the SQL amend your code to...

Dim reader As OleDbDataReader
Dim cmd As OleDbCommand
Dim SQL As String = "SELECT tblField.FieldName, " &
                           "tblField.FieldCaption, " &
                           "tblField.FieldMinCharNum, " &
                           "tblField.FieldMaxCharNum, " &
                           "tblField.FieldDefault, " &
                           "tblField.FieldSection, " &
                           "tblField.FirstQuestion, " &
                           "tblField.FieldDescription, " &
                           "tblField.FieldRegEx " &
                      "FROM tblField " &
                     "WHERE tblField.FieldID = ?"

cmd = New OleDbCommand(SQL, Connection.Connection)
cmd.Parameters.Add("?", OleDbType.Integer).Value = ID

reader = cmd.ExecuteReader

Answer 2

I don't understand why your mentioning SQL are you retrieving the data from SQL Query or are you going to insert data into the table.

your using Dim cmd As OleDbCommand means use to insert the input values into the database like see below sample code.

       query = "INSERT INTO ds.students (ID,NAME,PIC)" & _
       "VALUES (@ID,@NAME,@PIC);"
        Dim cmd As OracleCommand = New OracleCommand(query, con)
        cmd.Parameters.Add("@ID", Convert.ToInt32(TextBox1.Text))
        cmd.Parameters.Add("@NAME", Convert.ToString(TextBox2.Text))
        cmd.Parameters.Add("@PIC", arrImage)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

you can try this also

Using cn As OracleConnection = New OracleConnection(connectionString)

    cn.Open()

    Using cmd As OracleCommand = New OracleCommand()

        Const sql As String = "Insert into test_table (val1, val2) values (:var1, :var2)"
        cmd.Connection = cn
        cmd.Parameters.Add(New OracleParameter("var1", TxtField1.Text))
        cmd.Parameters.Add(New OracleParameter("var2", TxtField2.Text))
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()

    End Using

End Using

if you want to insert the values into the database change your code according to given samples.

Hope this will help you.