Help on Access Databases using VB2005

its pretty much the same as SQL except perhaps some keyword changes and connection string changes.

To "get" data from a dataset, once connected, use a DataAdapter to fill the dataset. Be sure to declare the dataset and oleDbDataAdapter as a global variable so you can access them through different functions/subs in your code:

Dim theOleDbCommand as new OleDbCommand("SELECT * FROM tableName") 'this would be your query to select data

Me.theDataAdapter as new OleDbDataAdapter(theOleDbCommand)

Me.theDataSet as new DataSet()

Me.theDataAdapter.Fill(theDataSet)

'close your connection in this line, then bind:

Me.theDataGridView.DataSource = Me.theDataSet.Tables(0).DefaultView 'would bind the data to the datagridview

in the DataGridView itself you can add records or delete and edit them, then finally when you are finished:

Me.theDataAdapter.Update(Me.theDataSet)

this would then commit the changes made in your datagridview (or more technically the dataset) into the MS Access database

does this help?

ahmedilyas at 2007-8-31 >

Sure. Well in a sense its the same but more specifically....you need to read about the OleDbCommand:

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommand.aspx

There are a couple of examples in there also which I hope will help.

To insert a record into the database with the given values, a typical example would be this:

Dim theOleDbCommand as new OleDbCommand("INSERT INTO tableName (field1, field2) VALUES(?, ?)") theOleDbCommand.Connection = new OleDbConnection(connectionString) Dim param1 as new OleDbParameter("@param1", OleDbType.type, size) param1.Value = ValueHere Dim param2 as new OleDbParameter("@param2", OleDbType.type, size) param2.Value = ValueHere theOleDbCommand.Parameters.Add(param1) theOleDbCommand.Parameters.Add(param2) theOleDbCommand.Connection.Open() theOleDbCommand.ExecuteNonQuery() theOleDbCommand.Connection.Close()

This typically inserts a row of data, with the given parameters/values etc... into the database.

It's the same process with selecting data (as you know how to do), or deleting or updating data - just giving it the appropriate values/parameters/command. To Update a record:

UPDATE tableName SET field1 = newValue, field2 = newValue WHERE [field] = value

The OleDbCommand is the one that holds all your query details, which executes on the database end and performs your operations.

To Delete a record:

DELETE FROM tableName WHERE [field] = value

generally giving the field ID and the ID record to delete, is used.

does this help you in some way?

ahmedilyas at 2007-8-31 >

Dear Ahmedilyas,

I tried your sample code, and is getting an Error "Syntax error in INSERT INFO" statement,

when executing mycommand.ExecuteNonQuery() .

I have a database with a table called Events, with fields: dates,adress,input,value,crc

Here is my code:

Dim connString As String
Dim cnOleDb As OleDbConnection
Dim cnSqlDb As SqlConnection
Dim myConnection As Data.Common.DbConnection

Dim myCommand As OleDbCommand

myCommand = New OleDbCommand("INSERT INTO Events (dates, adress, input, value, crc) VALUES(?,?,?,?,?)")

Dim param1 As New OleDbParameter("@dates", OleDbType.Date)
param1.Value = Now
Dim param2 As New OleDbParameter("@adress", OleDbType.BSTR, 3)
param2.Value = "A"
Dim param3 As New OleDbParameter("@input", OleDbType.Integer)
param3.Value = 1
Dim param4 As New OleDbParameter("@value", OleDbType.Integer)
param4.Value = 2
Dim param5 As New OleDbParameter("@crc", OleDbType.Integer)
param5.Value = 3
myCommand.Parameters.Add(param1)
myCommand.Parameters.Add(param2)
myCommand.Parameters.Add(param3)
myCommand.Parameters.Add(param4)
myCommand.Parameters.Add(param5)

From here I connect and open the datababase succesfully.

Then run the ExecuteNonQuery()

Pleaase could you have a look what the sysntax error could be.

Thanks

Secsi at 2007-8-31 >

The exeception viewer display InnerException as "Nothing"

Could it be a problem with the database.

I am sure that the field names are correct, as well as the table name.

Secsi at 2007-8-31 >

Did you get my project via e-mail ?

Thanks

MArtin

Secsi at 2007-8-31 >

Hi,

I assume you did not get my e-mail, so I will send it again.

I tried your last proposal, and it stil gives the same message.

Thanks

Secsi at 2007-8-31 >