Multi tool use

ASPX VB.Net OleDb Insert Parameter into Query

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.

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:

PARAMETERS

OleDbType.Integer

OleDbType.SmallInt

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.

Thanks to everyone.

SELECT

BigInt

Integer

SmallInt

PARAMETERS

2 Answers
2

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


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.

Dim cmd As OleDbCommand

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.