ASP.NET/VB/SQL: trying to insert data, getting error "no value given for required parameters"

Question

I am pretty sure this is a basic syntax error, I am new at this and basically figuring things out by trial and error... I am trying to insert data from textboxes into an Access database, where the primary key fields in tableCourse are prefix and course_number. It keeps giving me the "no value given for one or more required parameters" error. Here is my codebehind:

Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick
 'Collect Data
 Dim myDept = txtDept.Text
 Dim myFirst = txtFirstName.Text
 Dim myLast = txtLastName.Text
 Dim myPrefix = txtCoursePrefix.Text
 Dim myNum = txtCourseNum.Text
 'Define Connection
 Dim myConn As New OleDbConnection
 myConn.ConnectionString = AccessDataSource1.ConnectionString
 'Create commands
 Dim myIns1 As New OleDbCommand("INSERT INTO tableCourse (department, name_first, name_last, prefix, course_number) VALUES (@myDept, @myFirst, @myLast, @myPrefix, @myNum)", myConn)
 'Execute the commands
 myConn.Open()
 myIns1.ExecuteNonQuery()
End Sub

EDIT: I have parameters inside of my AccessDataSource, is there a way to use those? Sorry if that's a stupid question, I'm still learning this stuff and it really confuses me...

EDIT: Not really sure which answer to use, lol... I tried this:

Dim myIns1 As New OleDbCommand("INSERT INTO tableCourse (department, name_first, name_last, prefix, course_number) VALUES (?, ?, ?, ?, ?)", myConn)
 myIns1.Parameters.AddWithValue("@myDept", myDept)
 myIns1.Parameters.AddWithValue("@myFirst", myFirst)
 myIns1.Parameters.AddWithValue("@myLast", myLast)
 myIns1.Parameters.AddWithValue("@myPrefix", myPrefix)
 myIns1.Parameters.AddWithValue("@myNum", myNum)

And it inserts the data, I checked the database and it's all there, but it still gives me an error:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Answer 1

You need to do something like this:

Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick
 'Collect Data
 Dim myDept = txtDept.Text
 Dim myFirst = txtFirstName.Text
 Dim myLast = txtLastName.Text
 Dim myPrefix = txtCoursePrefix.Text
 Dim myNum = txtCourseNum.Text
 'Define Connection
 Dim myConn As New OleDbConnection
 myConn.ConnectionString = AccessDataSource1.ConnectionString
 'Create commands
 Dim myIns1 As New OleDbCommand("INSERT INTO tableCourse (department, name_first, name_last, prefix, course_number) VALUES (@myDept, @myFirst, @myLast, @myPrefix, @myNum)", myConn)
 myIns1.Parameters.AddWithValue("@myDept", myDept)
 myIns1.Parameters.AddWithValue("@myFirst", myFirst)
 myIns1.Parameters.AddWithValue("@myLast", myLast)
 myIns1.Parameters.AddWithValue("@myPrefix", myPrefix)
 myIns1.Parameters.AddWithValue("@myNum", myNum)
 'Execute the commands
 myConn.Open()
 myIns1.ExecuteNonQuery()
End Sub

Answer 2

You need to add parameters to the OleDbCommand.
You also need to dispose the command and the connection using Using blocks.

• Sara: What do you mean by "dispose the command and the connection using Using blocks"? Sorry, like I said I'm new at this.

Answer 3

Looks like you just need to provide some values for your command statement. Something like:

Dim myIns1 As New OleDbCommand("INSERT INTO tableCourse (department, name_first, name_last, prefix, course_number) VALUES (@myDept, @myFirst, @myLast, @myPrefix, @myNum)", myConn)
myIns1.Parameters.Add("@myDept", OleDbType.VarChar, 50).Value = myDept;
myIns1.Parameters.Add("@myFirst", OleDbType.VarChar, 50).Value = myFirst;
' ... and so on and so forth for each parameter you have.

Answer 4

You are creating a SQL command with a lot of parameters - which I applaud as the proper way to do it! - but you never create those parameters and pass in the values!

After you've created the OleDbCommand, you now need to add OleDbParameter for each of the @myDept, @myFirst etc. to the OleDbCommand object (its OleDbCOmmand.Parameters collection), and fill in the values from your text boxes.

Also: the OleDbCommand doesn't support those named parameters (unlike the SqlCommand, which does) - you need to replace those with just simply question marks:

Dim myIns1 As New OleDbCommand("INSERT INTO tableCourse (department, name_first, name_last, prefix, course_number) VALUES (?, ?, ?, ?, ?)", myConn)
myIns1.Parameters.Add("@myDept", OleDbType.VarChar, 50).Value = txtDept.Text.Trim();
....
myIns1.Parameters.Add("@myNum", OleDbType.Int).Value = 42;

With this setup, it becomes imperative that you create and add your OleDbParameter instances in the proper order!