Adding to a database

Hi i'm new to using VB.Net and using databases (MSAccess) with it. I want to add text from a combobox and two textboxs to my database. I'm getting the error message "No value given for one or more required parameters". Any help would be gratefully appreciated.

This is my code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

' Open a database connection.

Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _

"Data Source=../Video.mdb;Persist Security Info=False"

Dim conn As New System.Data.OleDb.OleDbConnection(strConnection)

conn.Open()

' Create a DataAdapter object and set its SELECT command.

Dim strSQL As String = "INSERT INTO Person(Title, first_name, surname) VALUES(Title.Text, TextBox1.Text, TextBox2.Text)"

Dim insertDataAdapter As OleDb.OleDbDataAdapter = _

New OleDb.OleDbDataAdapter(strSQL, conn)

Dim insertCommand As New OleDb.OleDbCommandBuilder(insertDataAdapter)

Dim insertDataSet As DataSet = New DataSet ' Now DataSet

insertDataAdapter.Fill(insertDataSet, "AllRows")

' Get a reference to the "AllRows" DataTable.

Dim insertDataTable As DataTable = insertDataSet.Tables("AllRows")

Dim row As DataRow

row = insertDataTable.NewRow() ' Add a record

row("Title") = Trim(Title.Text)

row("first_name") = Trim(TextBox1.Text)

row("surname") = Trim(TextBox2.Text)

insertDataTable.Rows.Add(row)

insertDataAdapter.Update(insertDataSet, "AllRows") ' Update the database

' Close the database connection and release resources

insertDataTable.Dispose()

insertDataSet.Dispose()

insertDataAdapter.Dispose()

conn.Close()

conn.Dispose()

' All done! Display MessageBox

MessageBox.Show("Record has been added", "All done!", _

MessageBoxButtons.OK, MessageBoxIcon.None)

TextBox1.Clear() ' Clear TextBox

End Sub

.NET Development15

Just a little bit of helpful critisism to clean up your code, if you do not need the dataset and you are only manipulating the datable, I would eliminate the dataset alltogether. You can do fill methods on datatables alone.

I would verify that you have the names set up correctly to your data sources, it sounds like something is being missed.

Where in your code does it throw this error

Also, if vb has the ability like C# to use using statements for opening and closing code I would use that as well, as this will help make your code a litte bit easier to read.

You will need to add parameters to the command. There is a good example of how this should be done for Access databases at http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.aspx.

Also, note that the INSERT string that is passed to the constructor is passed on to the SelectCommand property, which is probably not what you intend. The sample I link to above shows how the different commands in an adapter can be set.

Marcelo - MSFT

Modify the code accordingly

Dim strSQL As String =

Dim insertDataAdapter As OleDb.OleDbDataAdapter = _

New OleDb.OleDbDataAdapter(strSQL, conn)

Dim insertCommand As New OleDb.OleDbCommandBuilder(insertDataAdapter)

insertCommand.Parameters.Add ("Title", DBType.String, 255, "Title");

insertCommand.Parameters.Add ("first_name", DBType.String, 255, "first_name");

insertCommand.Parameters.Add ("surname", DBType.String, 255, "surname");

Dim insertDataSet As DataSet = New DataSet ' Now DataSet

insertDataAdapter.Fill(insertDataSet, "AllRows")

' Get a reference to the "AllRows" DataTable.

Dim insertDataTable As DataTable = insertDataSet.Tables("AllRows")

Dim row As DataRow

row = insertDataTable.NewRow() ' Add a record

row("Title") = Trim(Title.Text)

row("first_name") = Trim(TextBox1.Text)

row("surname") = Trim(TextBox2.Text)

insertDataTable.Rows.Add(row)

insertDataAdapter.Update(insertDataSet, "AllRows") ' Update the database

Cheers guys, any critisms is welcome as it adds the learning process. Bappi cheers for that i had to add

insertDataAdapter.InsertCommand.Parameters.Add( , DbType.String, 255, "Title")

but i get an error saying NullReferenceException. Object reference not set to an instance of an object. refering to this line

sorry for that. the line should be

insertDataAdapter.InsertCommand.Parameters.Add( , OleDbType.VarChar, 255, "Title" );

but before that you have to specify the InsertCommand of the insertDataAdapter.

Hope this will help you.