Question Dataset into MS Access

[lordrom]

I am mildly proficient at VB6, but am completely new to .net hence my question

I have been sent a program that basically collects some data from somewhere and sticks it into a form

Dim dsOdds As DataSet = GetSqlDataSetBySP("spGet", Me.txtConnString.Text)
If (dsOdds.Tables.Count = 2) Then
Me.dgvOdds.DataSource = dsOdds.Tables(0)
Me.dgvLastUpdate.DataSource = dsOdds.Tables(1)
End If

I want to take the data in these tables and stick them into MS Access so I can keep an historical record of the data.

I have copied this from a forum so i can connect to the database..

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:\SBBOdds.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Can anyone help me with how to update the database, whether it is possible to send the dataset over in one hit, or more likely send it over row by row after retreiving it from the dataset.

Thanks in advance

John

 

[jmcilhinney]

You would use an OleDbDataAdapter to save the contents of the DataTable in one go. Check this out for examples:

Retrieving and Saving Data in Databases

The example in post #3 is most relevant to you, although you've already got a populated DataTable, so you don't have to add rows. The thing is, the RowState of each DataRow has to be Added in order for it to be inserted when you call Update. There are two ways that can happen:

1. If you have access to it, set the AcceptChangesDuringFill property of the SqlDataAdapter used to retrieve the data to False.
2. Loop through the Rows collection of the DataTable and call SetAdded on each DataRow.

 

[lordrom]

okay, I'm still way off the mark here....

If (dsOdds.Tables.Count = 2) Then
Me.dgvOdds.DataSource = dsOdds.Tables(0)
Me.dgvLastUpdate.DataSource = dsOdds.Tables(1)
End If


Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:\SBBOdds.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [SBOOdds].* FROM [SBOOdds]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet

dbCommand.Update(dsOdds.Tables(0))

The last line returns an error but I've no idea what. Can I update the whole dataset in one go?
Why they turned an easy to follow language into this i'll never know.

 

[jmcilhinney]

Instead of complaining about the language being so hard to understand, I would suggest reading information more carefully when it's provided. You are making two mistakes that should be obvious from the examples I provided. First, you are trying to call Update on a Command object, but it has no such method. As the examples I provided show, Update is called on the DataAdapter object. Secondly, you are calling Update, which is for saving data, rather than Fill which is for retrieving data.

You are also making a third mistake, although it may not be so obvious from my examples. You are creating a DataSet but you aren't creating any DataTables in it, then you try to get the first DataTable from the DataSet. If the DataSet contains no DataTables then that will obviously fail. You have three options to fix that:

1. If you expect to be able to get a DataTable at index 0 in the DataSet's Tables collection then you have to add a DataTable at index 0 in the DataSet's Tables collection.

Dim dataSet As New DataSet

dataSet.Tables.Add(New DataTable)

2. That's a bit pointless though. If you're just going to use the DataTable anyway, what's the DataSet for? Why not just use the DataTable and forget the DataSet altogether? That's what I do in my code examples.

Dim table As New Datatable

3. If there really is a genuine reason to use a DataSet, e.g. you need multiple DataTables and, optionally, DataRelations between them, then you can have the Fill method create a DataTable in the DataSet for you. When you call Fill, pass the DataSet and the name of the DataTable. If the DataTable doesn't exist, it will be created.

Dim myDataSet As New DataSet

myDataAdapter.Fill(myDataSet, "MyTable")

 

[lordrom]

the reason i am using dataset is that the original code i was sent used datasets.

Dim dsOdds As DataSet = GetSqlDataSetBySP("spGetSBOBetOdds", Me.txtConnString.Text)
If (dsOdds.Tables.Count = 2) Then
Me.dgvOdds.DataSource = dsOdds.Tables(0)
Me.dgvLastUpdate.DataSource = dsOdds.Tables(1)
End If

so when i click a button the system goes off to retrieve data from somewhere and returns a dataset with 2 tables, which it then sticks into 2 datagrids on a form

so i am missing the point of datasets and datatables, so i'll go and read up the differences.

when i set the dataadapter to update i get the following:-

Error 1 Value of type 'System.Data.DataTable' cannot be converted to 'System.Data.DataSet'. C:\Documents and Settings\Len Bristow\My Documents\john\CenterOddsSample\CenterOddsSample\CenterOddsSample\Form1.vb 55 28 CenterOddsSample

which i guess relates to what you are saying about datatables and datasets. i'll have another look at it all and see what i can come up with. no doubt i'll be back soon.

thanks for your patience

 

[jmcilhinney]

A DataSet is basically an in-memory representation of a database, while a DataTable is basically an in-memory representation of a database table. As with a database, the DataSet itself does not contain the data. The DataSet contains DataTables, which themselves contain DataColumns that describe the data and DataRows that contain the data. The DataSet can also contain DataRelations that connect the DataTables it contains. Unlike a database, DataTables can exist in isolation without a containing DataSet.

The code you posted most recently is calling a method that obviously creates the DataSet and DataTables for you. If you're going to create the DataSet yourself then you have to create the DataTables yourself too. As for that error message, you are apparently using a DataTable where a DataSet is required, but we can't be more specific than that because you haven't shown us the code that causes the error. First you gave us code and no error message and now you've given us the error message with no code. In general, we need the cause and the effect if we are to determine the solution.

 

[lordrom]

Dim dsOdds As DataSet = GetSqlDataSetBySP("spGetSBOBetOdds", Me.txtConnString.Text)
If (dsOdds.Tables.Count = 2) Then
Me.dgvOdds.DataSource = dsOdds.Tables(0)
Me.dgvLastUpdate.DataSource = dsOdds.Tables(1)
End If


Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:\SBBOdds.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "select * from [SBOOdds].* FROM [SBOOdds]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
'Dim dataSet As System.Data.DataSet = New System.Data.DataSet

'###
Dim insert As New SqlCommand("INSERT INTO SBOOdds (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
dbConnection)
insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
dataAdapter.InsertCommand = insert
dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim table As New DataTable
'Retrieve the data.
dataAdapter.FillSchema(table, SchemaType.Source)
'Add the new rows to the DataTable, e.g.
Dim row As DataRow = table.NewRow()
row("Name") = "Name"
row("Quantity") = 100
row("Unit") = "Unit"
table.Rows.Add(row)
'Save the changes.
dataAdapter.Update(table)
End Sub

This is a copy of the code you pointed me to. I get the above mentioned errors on the following 2 lines:-

dataAdapter.FillSchema(table, SchemaType.Source)
dataAdapter.Update(table)

I thought that the tables had been created since the data is pasted into the datagrids and works fine. dsOdds.tables(0) and dsOdds.tables(1)

 

[lordrom]

the code now appears to be a cross between uploading a whole dataset in one go, and adding row by row data manually.

if I can at least get either method wroking that would be great

i've tried changing datasets in tables and viceversa but I still get the same error

essentially the code i was first sent retrieves 2 tables within 1 dataset and pastes them in 2 datagrids on a form. i just want to send dsodds.table(0) into a database in one hit if possible

 

[lordrom]

Private Function GetSqlDataSetBySP(ByVal spName As String, ByVal sqlConnectionString As String) As DataSet
Dim objDA As New SqlDataAdapter
Dim objDS As New DataSet
Dim sqlConnection As New SqlConnection(sqlConnectionString)
Dim myCommand As SqlCommand

Try
myCommand = New SqlCommand(spName, sqlConnection)
myCommand.CommandType = CommandType.StoredProcedure
objDA.SelectCommand = myCommand
objDA.Fill(objDS)

Return objDS

Finally
If (sqlConnection IsNot Nothing) Then
If (sqlConnection.State <> ConnectionState.Closed) Then
sqlConnection.Close()
End If
End If
sqlConnection = Nothing

End Try
End Function

This is the bit that goes off and get the data and returns a dataset with 2 datatables

 

[jmcilhinney]

We seem to have got off track here as you appear to have ignored what I said in the first post.

The example in post #3 is most relevant to you, although you've already got a populated DataTable, so you don't have to add rows. The thing is, the RowState of each DataRow has to be Added in order for it to be inserted when you call Update. There are two ways that can happen:

1. If you have access to it, set the AcceptChangesDuringFill property of the SqlDataAdapter used to retrieve the data to False.
2. Loop through the Rows collection of the DataTable and call SetAdded on each DataRow.

You're adding rows and I explicitly said that you didn't have to. Where are you doing one of the two options I provided, to the EXISTING DataTable?

 

[lordrom]

Dim dsOdds As DataSet = GetSqlDataSetBySP("spGetSBOBetOdds", Me.txtConnString.Text)
If (dsOdds.Tables.Count = 2) Then
Me.dgvOdds.DataSource = dsOdds.Tables(0)
Me.dgvLastUpdate.DataSource = dsOdds.Tables(1)
End If


Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:\SBBOdds.mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "select * from [SBOOdds].* FROM [SBOOdds]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
'Dim dataSet As System.Data.DataSet = New System.Data.DataSet

Dim row As DataRow
For Each row In dsOdds.Tables(0).Rows
row.SetAdded()
Next

dataAdapter.Update(dsOdds.Tables(0))

ok, point taken. code added but same error as before on the last line

 

[lordrom]

having reread your post, i'm thinking it might be something to do with the SELECT command being used rather than the INSERT command?

 

[lordrom]

Private Sub GetSBOBetOddsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetSBOBetOddsToolStripMenuItem.Click
Dim dsOdds As DataSet = GetSqlDataSetBySP("spGetSBOBetOdds", Me.txtConnString.Text)
If (dsOdds.Tables.Count = 2) Then
Me.dgvOdds.DataSource = dsOdds.Tables(0)
Me.dgvLastUpdate.DataSource = dsOdds.Tables(1)
End If


Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:\SBBOdds.mdb"
'Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Using connection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
Using adapter As New OleDb.OleDbDataAdapter("SELECT * FROM SBOOdds", _
connection)
Dim insert As New OleDb.OleDbCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
connection)
insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
adapter.InsertCommand = insert
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim table As New DataTable
'Retrieve the data.
adapter.FillSchema(table, SchemaType.Source)
'Add the new rows to the DataTable, e.g.
Dim row As DataRow = table.NewRow()
row("Name") = dsOdds.Tables(0).Rows.Item(0)
row("Quantity") = dsOdds.Tables(0).Rows.Item(1)
row("Unit") = dsOdds.Tables(0).Rows.Item(2)
table.Rows.Add(row)
'Save the changes.
adapter.Update(table)
End Using
End Using
End Sub

So I have this and it is only giving me 1 error on the following line

Using adapter As New OleDb.OleDbDataAdapter("SELECT * FROM SBOOdds", _
connection)

This is the error

Overload resolution failed because no accessible 'New' can be called without a narrowing conversion:
'Public Sub New(selectCommandText As String, selectConnection As System.Data.OleDb.OleDbConnection)': Argument matching parameter 'selectConnection' narrows from 'System.Data.IDbConnection' to 'System.Data.OleDb.OleDbConnection'.
'Public Sub New(selectCommandText As String, selectConnectionString As String)': Argument matching parameter 'selectConnectionString' narrows from 'System.Data.IDbConnection' to 'String'.

Any help on this greatly appreciated. thanks

 

[lordrom]

actually i see i am adding rows again, but then i copied the code from your thread on vbforums whilst starting again for the umteenth time.

so i've gone around in circles all day on this and am still no nearer to understanding this procedure. is there any chance you could post the code i need please? I'm sure I'd learn more from seeing the correct solution rather than beating my head against a wall all day.

 

[lordrom]

okay...on the following line

tblSBOOddstableAdapter.Update(dsOdds.Tables(0))

i get a casting error, the number must be less than infinity.

i have tried to set the entire dataset to be string formats (.tostring()) but this hasn't helped.

it wouldn't be because the table has an autoincrement ID field would it? or is that all handled for me?

any help appreciated. thanks