CODEWITHSUNDEEP
asp.netsqlvb.netado.net
Sara
Sara

Reputation: 185

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

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."

Upvotes: 0

Views: 1148

Answers (4)

marc_s
marc_s

Reputation: 754200

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!

Upvotes: 0

msarchet
msarchet

Reputation: 15232

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

Upvotes: 1

Wes P
Wes P

Reputation: 9840

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.

Upvotes: 0

SLaks
SLaks

Reputation: 887195

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

Upvotes: 0

Related Questions