DataAdapter Update

[Guest]

Hi,

I have a function to fill the data into the dataset:

Private Sub LoadSystemDataDB(ByVal m_str As String, ByVal da As _
OleDbDataAdapter, ByVal tbName As String)
Dim OleDbConn As New OleDbConnection(OLEDB_CONNECTION_1)
OleDbConn.Open()
da = New OleDbDataAdapter(m_str, OleDbConn)
Dim cmd As New OleDbCommandBuilder(da)
da.Fill(dsDataSet, tbName)
dsDataSet.CaseSensitive = True
OleDbConn.Close()
End Sub

However when i try to update the data using:
Dim autogen As New OleDbCommandBuilder(daLoginDetails)
daDataAdapter.Update(dsDataSet, "myTable")

I get error when i try to update it. The error is on the Update line where
it saids "The DataAdapter.SelectCommand property needs to be initialized".

So i guess i was doing something wrong when filling the data??
or what is the proper way to make a fill data function? as i have many
tables to load into the dataset.

Hope anyone can help?
many thanks.

 

[Guest]

Try something like this:

da.SelectCommand = cmd
da.Fill(dsDataSet, tbName)

 

[Guest]

Try placing a

da.SelectCommand = cmd

just prior to your fill statement.

 

[Guest]

typo error:
Instead of
daDataAdapter.Update(dsDataSet, "myTable")

It should be:
daLoginDetails.Update(dsDataSet, "myTable")

can anyone help please?

 

[Guest]

I tried to add something like that in the function, but it said

"Value of type System.Data.OleDb.OleDbCommand.' cannot be converted to
'System.Data.OleDb.OleDbCommandBuilder'

?
thanks

 

[Guest]

thanks Darell, i have added the line

da.SelectCommand = cmd

just before the fill method.
but it saids:

"Value of type System.Data.OleDb.OleDbCommand.' cannot be converted to
'System.Data.OleDb.OleDbCommandBuilder' "

hence could not compile.
Do you know the problem?
or is it the wrong method i m filling my dataset or my function?

many thanks. i hope someone can help me please?

 

[Guest]

I read your post wrong the first time and thought that cmd was a Command
object.

But : I don't believe that you need to create the commandbuilder twice, once
will do. Make certain that the dataset is available to you when the update
method is called and make certain that the connection back to the underlying
database is open.

 

[Guest]

Private Sub LoadSystemDataDB(ByVal m_str As String, ByRef da As _
OleDbDataAdapter, ByVal tbName As String, ByRef cmd as OleDbCommandBuilder)
Dim OleDbConn As New OleDbConnection(OLEDB_CONNECTION_1)
da = New OleDbDataAdapter
da.SelectCommand = new OleDBCommand(m_str, OleDbConn)
Dim cmd As New OleDbCommandBuilder(da)
OleDbConn.Open
da.Fill(dsDataSet, tbName)
dsDataSet.CaseSensitive = True
OleDbConn.Close()
End Sub

Note the "ByRef" in the sub statement, these are Reference objects not value
objects.

 

[Guest]

ic, Thank you very much!

Private Sub LoadSystemDataDB(ByVal m_str As String, ByRef da As _
OleDbDataAdapter, ByVal tbName As String, ByRef cmd as OleDbCommandBuilder)
Dim OleDbConn As New OleDbConnection(OLEDB_CONNECTION_1)
da = New OleDbDataAdapter
da.SelectCommand = new OleDBCommand(m_str, OleDbConn)
Dim cmd As New OleDbCommandBuilder(da)
OleDbConn.Open
da.Fill(dsDataSet, tbName)
dsDataSet.CaseSensitive = True
OleDbConn.Close()
End Sub

Note the "ByRef" in the sub statement, these are Reference objects not value
objects.

 

[Darrell Wesley]

Change Dim cmd As New OleDbCommandBuilder(da)
to cmd = new OleDbCommandBuilder(da)

The calling program should have the statements:

dim cb as OleDbCommandBuilder ' no NEW key word
Dim daDataAdapter as OleDbDataAdapter ' no NEW keyword
Public dsDataSet as New DataSet ' needs to go in front of this module so
that it's truely public

Call LoadSystemDataDB(sqlString, daDataAdapter, TblName, cb)
' after the call returns back to the caller
Dim OleDbConn As New OleDbConnection(OLEDB_CONNECTION_1)
daDataAdapter.Update(dsDataSet, "myTable")
OleDbConn.Close

The subroutine will create the NEW version of it.
Don't create another NEW version in the calling program you only want it
once.

I also assume that the dataset is a Public Object that was created in the
calling program.