Thread: Adding New Record to access database

Netters,
I am confident with vb6. Could anyone give me a step by step on how to add a new record to access database using vb.net. I am able to open and close connection to the database. I have looked at several sites but they are not comprehensive for a .net newbie and are concentrating on viewing rather than adding new data to the database.
thanks in advance,
tata

To add a new record to a database you need to use an OleDbCommand with a valid SQL insert statement as the CommandText. You can add a single record using the ExecuteNonQuery() method of the OleDbCommand like so:

Code:

Dim connection As New OleDbConnection(<connection string>)
Dim insertCommand As New OleDbCommand("INSERT INTO Table1 (KeyField, Field1) VALUES ('keyValue', 'value1'), connection)

Try
	connection.Open()
	insertCommand.ExecuteNonQuery()
Catch ex As Exception
	MessageBox.Show("Data Access Error.")
Finally
	connection.Close()
End Try

More often, though, you will use an OleDbAdapter with a DataSet or DataTable. The InsertCommand property of the OleDbAdapter should be set to a valid parameterised OleDbCommand. This means that instead of field values in the CommandText you use "?" symbols and the values will be automatically drawn from each row of the table, like so:

Code:

Dim connection As New OleDbConnection(<connection string>)

'Create the data adapter with a select command.
Dim adapter As New OleDbDataAdapter("SELECT KeyField, Field1 FROM Table1", connection)

Dim data As New DataSet

Try
	adapter.Fill(data, "Table1")
Catch ex As Exception
	'...
End Try

Dim table1 As DataTable = data.Tables("Table1)

'Create a new row.
Dim newRow As DataRow = table1.NewRow()

newRow("KeyValue") = "New Key"
newRow("Field1") = "New Field1 Value"
table1.Rows.Add(newRow)

'Create the insert command.
Dim insertCommand As New OleDbCommand("INSERT INTO Table1 (KeyField, Field1) VALUES (?, ?)", connection)

'Add command parameters.
insertCommand.Parameters.Add("KeyField", OleDbType.VarChar, 0, "KeyField")
insertCommand.Parameters.Add("Field1", OleDbType.VarChar, 0, "Field1")

adapter.InsertCommand = insertCommand

Try
	adapter.Update(data, "Table1") 'or adapter.Update(table1)
Catch ex As Exception
	'...
End Try

I would suggest you look through the help for the System.Data namespace for info on the DataSet, DataTable and DataRow classesif you need it, and the System.Data.OleDb namespace for info on the OleDbConnection, OleDbDataAdapter and OleDbCommand classes.

jimcilhinney,

Thanks very much for the timely response, you have given me a good direction.

I am rather confused with this line in the reply.

Dim insertCommand As New OleDbCommand("INSERT INTO Table1 (KeyField, Field1) VALUES ('keyValue', 'value1'), connection)

My database name is candidates.mdb, the table I would like to work with is tblCandidates, it has the following fields CandidateID, Name, Course, Address, CourseID. CandidateID is the key for tblCandidates.

how do I translate this information to this declaration

Dim insertCommand As New OleDbCommand("INSERT INTO Table1 (KeyField, Field1) VALUES ('keyValue', 'value1'), connection)

Thanks in advance,
tata

Code:

Dim insertCommand As New OleDbCommand("INSERT INTO tblCandidates (CandidateID, Name, Course, Address, CourseID) VALUES (?,?,?,?,?)", connection)

If you are inserting a single row, use the first example and substitute the actual field values for the "?" symbols, using String.Format or by concatenating Strings. If you are inserting multiple rows that have already been added to a DataTable, use the second example and add parameters to the command, something like this:

Code:

insertCommand.Parameters.Add("CandidateID", OleDbType.Integer, 0, "CandidateID")
insertCommand.Parameters.Add("Name", OleDbType.VarWChar, 0, "Name")
insertCommand.Parameters.Add("Course", OleDbType.VarWChar, 0, "Course")
insertCommand.Parameters.Add("Address", OleDbType.VarWChar, 0, "Address")
insertCommand.Parameters.Add("CourseID", OleDbType.Integer, 0, "CourseID")

jimcilhinney,

Dim Connection AsNew OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLED B.4.0; Data Source = C:/candidates.mdb")
Dim insertCommand AsNew OleDb.OleDbCommand("INSERT INTO tblCandidates (CandidateID, Name, Course, Address, CourseID) VALUES ('Maganizo','Monawe','MMT','NNT2','SRT')", Connection)

Try

Connection.Open()

MsgBox("Connection Opened")

insertCommand.ExecuteNonQuery()

MsgBox("query executed")

Catch ex As Exception

MessageBox.Show("Data Access Error.")

Finally

Connection.Close()

EndTry

When I run the code I get the error "Data Access Error" which is what the catch statement is asked to do incase of error, is there anything with my declarations, I am also trying to work out this?

Thanks,
Tata

Change

MessageBox.Show("Data Access Error.")

to

MessageBox.Show(ex.ToString())

to get more detail about the exception.

jimcilhinney,

Super, now successful. Actually the changing of

MessageBox.Show("Data Access Error.")

to

MessageBox.Show(ex.ToString()

assisted very much because it showed me that I misspelt the table I was working with. Please accept my sincere thanks.

Tata

I am new to vb.net and was trying the code given by jmcilhinneyto update the access database I found the following error message
type "OleDbConnection" is not defained
type "OleDbDataAdapter" is not defained
type "oledbcommand" is not defained
type "oledbtype" is not defained

As I just copy and paste the command I am sure it is not misspelled.
please help.
Jamal Saleh

jamal_s, welcome to the forum.

The classes you name are in the System.Data.OleDb namespace. You must either explicitly include the namespace (ie System.Data.OleDb.OleDbConnection) or, as a shorthand, include an Import statement at the very top of the code page before any class declarations. The statement would read: Imports System.Data.OleDb

The way the I always suggest to learn ADO.NET is to use the Data Form Wizard which will create a form, dataset schema, and all the code for you based on a set of questions. You can then view and modify the code. To start the Wizard, right click the project and select 'Add New', select 'Data Form Wizard', then answer the questions given by the wizard.

Thanks Paszt, you have showed me the right direction I am practicing as you advise me I am using drug and drop method of Oledbconnection,oledbdataadpter and dataset. When I am dropping dataset I got two options one typed dataset and the other one untyped dataset. I want a typed dataset when I gave a name for example dataset1 I got an error message " Visual studio couldn't create an instance of dataset1. Confirm the class name is a valid dataset class"

Can you help me how to solve this ?

You have to already have a typed DataSet in your project to create an instance of it. A typed DataSet is like a class in that it is an item that you add to your project in the Solution Explorer. It appears in your project as a .xsd file, which is the XML schema for the DataSet. When you add a typed DataSet to a Form, you are creating an instance of that object type, just like adding a button to a Form creates an instance of the Button type. To add a typed DataSet to your project, right-click the project in the Solution Explorer, select "Add Item..." and then select DataSet. Using this method creates an empty DataSet that you will have to build up yourself. It is much easier, and more common, to generate one from a data source. If you have added a DataAdapter to your Form you can use the "Generate DataSet..." function that appears at the bottom of the Properties window for the DataAdapter.

Hi,

as we know that you are using designer (design time) you could try this:

In server Explorer, find the database table you want, and drag it to your form. This will create a connection and a data adapter. Select the data adapter, then below your Properties Window, click on the link "Generate DataSet ...

or with code:

PHP Code:

 Dim myDataSet As New DataSet 


Kind regards

edit: sorry jmcilhinney, i didn't see you are posting too ... regards

thanks jmcilhinney and Kulrom I tried both ways and it is working fine.
Regards
Jamal

Please all, if you are asking new questions that do not relate specifically to this thread then start a new thread of your own. This applies especially if you didn't start this thread in the first place.

Thanks