!!!!!HELP!!!!! UpdateCommand Not working in ADO.NET !!!!!!HELP!!!!!!

  • Thread starter Thread starter Gaurav Pahuja
  • Start date Start date
G

Gaurav Pahuja

Hi All,

I am having real problems with oledbadapter updatecommand. I am using
dataset to get the data from a access database. I am using databinding
to bind data with textboxes and currencymanager to navigate through
the records. I am trying to update the access table, when the record
is updated using the textboxes.
I am using currencymanager.endcurrentedit() as suggested by some posts
in this group, but it is still not working. Dataset.Table.GetChanges
gives me the changes made in textboxes but I am still not able to send
those changes to the access table. I have specified a updatecommand,
added all the commandparameters but when I call
Myadapter.update("dataset","tablename") nothing happens(no errors.
exception and alas...no updates in the access
table!!!!!!!!!!!!!!!!!!!). I have been struggling with this since last
2 days.
Your time and help is highly appriciated.

Thanks
Gaurav
 
Let's see your code--and before do, make sure you aren't executing
AcceptChanges.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks for replying William. Here is the code:

I am using a class to handle the connections. The class code is at the
end of the message.

PL is a instance of class that handles the connections.

The following code relates to the datasets, data reterival and data
updates.
UpdateMaster,CopyMasterToFinal subroutines deal with updating the
access table.

GetAccessData subroutine deals with data reterival - I have a feeling
that I am doing something here that is affecting the update...may be
sorting the table!!!

Please let me know if you need any other information to help me.
Once again, thanks for your help.

Needy
Gaurav


Dim tbCurrent As String
Dim tbUpdates As String
Dim tbFinal As String
Private WithEvents myCurrencyManager As CurrencyManager
Private WithEvents myCurrencyManager1 As CurrencyManager
'Creating a db connection
Dim PL As New ProdLib.ProdClass

'Form Load event
Private Sub frmValidateProducts_Load(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Three datatable names
tbCurrent = "Master"
tbUpdates = "Updates"
tbFinal = "Final"
Dim tbTemp As System.Data.DataTable
'Get Data from the database in Dataset
GetAccessData(PL)
If PL.ExitImport = True Then
PL.ExitImport = False
cmdNext.Enabled = False
cmdPrev.Enabled = False
cmdFirst.Enabled = False
cmdLast.Enabled = False
cmdCancelEdit.Enabled = False
btnSearch.Enabled = False
btnLink.Enabled = False
Exit Sub
End If
'Exit Sub
'Creating final data
tbTemp = PL.MyDataSet.Tables(tbCurrent).Clone()
tbTemp.TableName = tbFinal
PL.MyDataSet.Tables.Add(tbTemp)
UpdateMaster()
BindTextBoxes(PL)
End Sub


'Gets the data from master table and validation table.
Sub GetAccessData(ByRef PL As ProdLib.ProdClass)
Dim ConExtend As String
Dim sQuery As String
Dim sQuery1 As String

Try
'Setting extended settings
ConExtend = "User Id=" & MainForm.dbUser & ";Password=" &
MainForm.dbPass & ";" & _
"Jet OLEDB:System Database=" & MainForm.dbMW & ";"
'Setting a connection with Access Library
PL.SetMyConnString(MainForm.dbDB,
"Microsoft.Jet.OLEDB.4.0; ", ConExtend)
PL.SetConnection()
PL.SetDataSet()

'Query gets all the new products in validation from master
table.
sQuery = "select * " & _
"from master_product_library " & _
"where flag5 = True"
'Query gets all the updates on existing products from
master table.
sQuery1 = "select distinct master_product_library.* " & _
"from master_product_library, updates_product_library " &
_
"where master_product_library.upc =
updates_product_library.upc"

'Filling the dataset with access data. Column names in
access are preserved.
PL.SetAdapter(sQuery)
PL.FillDataSet("tempMaster")
PL.SetAdapter(sQuery1)
PL.FillDataSet("tempMaster")
RemoveDuplicateUPC(PL.MyDataSet, "tempMaster", "", "UPC")
Dim tbTempMaster As System.Data.DataTable
tbTempMaster = PL.MyDataSet.Tables("tempMaster").Clone()
tbTempMaster.TableName = tbCurrent
PL.MyDataSet.Tables.Add(tbTempMaster)
SortTable("tempMaster", tbCurrent, "UPC ASC")
PL.MyDataSet.Tables.Remove("tempMaster")
PL.MyDataSet.Tables(tbCurrent).DefaultView.Sort = "UPC"
'Query gets all the updates in update table.
sQuery1 = "select distinct updates_product_library.* " & _
"from master_product_library, updates_product_library " &
_
"where master_product_library.upc =
updates_product_library.upc"

''Filling the dataset with access data. Column names in
access are preserved.
PL.SetAdapter(sQuery)
PL.FillDataSet("temp")
PL.SetAdapter(sQuery1)
PL.FillDataSet("temp")
Dim tbTemp As System.Data.DataTable
tbTemp = PL.MyDataSet.Tables("temp").Clone()
tbTemp.TableName = tbUpdates
PL.MyDataSet.Tables.Add(tbTemp)
SortTable("temp", tbUpdates, "UPC ASC")
PL.MyDataSet.Tables.Remove("temp")
PL.MyDataSet.Tables(tbUpdates).DefaultView.Sort = "UPC"
If PL.MyDataSet.Tables(tbUpdates).Rows.Count = 0 Then
MessageBox.Show("No Updates present for validation")
PL.ExitImport = True
Exit Sub
End If
DataGrid1.DataSource = PL.MyDataSet
Catch ex As Exception
MsgBox("Access Connection encountered a problem." & ex.Message
& " " & ex.Source)
End Try
End Sub



Sub RemoveDuplicateUPC(ByRef M_DataSet As System.Data.DataSet,
ByVal tbName As String, ByVal sExpr As String, ByVal sSortVal As
String)

Dim OldKey As Object
Dim dr As DataRow
'For Each dr In M_DataSet.Tables(tbName).Select("", "0")
For Each dr In M_DataSet.Tables(tbName).Select(sExpr,
sSortVal)
If dr.Item(sSortVal) = OldKey Then
M_DataSet.Tables(tbName).Rows.Remove(dr)
Else
OldKey = dr.Item(sSortVal)
End If
Next
End Sub


Sub SortTable(ByVal tbTableForm As String, ByVal tbTableTo As
String, ByVal SortVal As String)
For Each drow As System.Data.DataRow In
PL.MyDataSet.Tables(tbTableForm).Select("", SortVal)
PL.MyDataSet.Tables(tbTableTo).ImportRow(drow)
Next
End Sub


'Code that calls the adapter.update
Sub CopyMasterToFinal()
Dim drow As System.Data.DataRow
Dim UpdateQuery As String
UpdateMaster()
myCurrencyManager1.EndCurrentEdit()
PL.UpdateAdapter(tbCurrent)
End Sub


'Used to create the commandparameters. Refer to the class below.
Sub UpdateMaster()
Dim UpdateQuery As String
UpdateQuery = "Update master_product_library set ID = ID where UPC
= UPC"
PL.SetMyCommandText(UpdateQuery)
PL.SetMyCommandParameter("UPC",
System.Data.OleDb.OleDbType.VarChar, 16, "UPC")
PL.SetMyCommandParameter("ID",
System.Data.OleDb.OleDbType.VarChar, 16, "ID")
PL.SetMyAdapterUpdate()
End Sub


'*********************************************
Imports System
Imports System.Data

Namespace ProdLib

Public Class ProdClass
'Creates a connection, adapter and command to connect to the
database
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim MyAdapter As System.Data.OleDb.OleDbDataAdapter
Dim MyCommand As System.Data.OleDb.OleDbCommand
'Shared instance of Mydataset is used to store all the tables.
Public Shared MyDataSet As System.Data.DataSet
Dim MyConnString As String
'ExitImport will remove
Public Shared ExitImport As Boolean

Sub ProdClass()
MyConnection.ConnectionString = ""
MyCommand.Connection = MyConnection
Dim sQuery As String
MyCommand = New System.Data.OleDb.OleDbCommand
MyAdapter = New System.Data.OleDb.OleDbDataAdapter
ExitImport = False
End Sub

Sub ClearDataSet()
MyDataSet.Reset()
End Sub

Function SetMyConnString(ByVal filepath As String, ByVal
ConnProvider As String, ByVal ConnExtended As String) As String
'sets the connection string to connect to a data source
If ConnExtended.Equals("") Then
MyConnString = "Provider=" & ConnProvider & "data
source=" & filepath & ";"
Else
MyConnString = "Provider=" & ConnProvider & _
"data source=" & filepath & ";" & _
ConnExtended
End If
End Function

Private Function GetMyConnString()
Return MyConnString
End Function

Function SetConnection() As String
'connects to a datasource with the help of connection
string
MyConnection = New
System.Data.OleDb.OleDbConnection(GetMyConnString())
End Function

Sub CloseConnection()
MyConnection.Close()
End Sub

Sub SetAdapter(ByVal sQuery As String)
'sets the adapter to query the database using the given
connection
MyAdapter = New System.Data.OleDb.OleDbDataAdapter(sQuery,
MyConnection)
End Sub

Sub SetAdapterAcceptSetting(ByVal accept As Boolean)
'MyAdapter.AcceptChangesDuringFill = accept
End Sub

Sub SetMyCommandText(ByVal sQuery As String)
MyCommand = New System.Data.OleDb.OleDbCommand(sQuery,
MyConnection)
'MsgBox("Before open " & MyConnection.State())
If MyConnection.State <> ConnectionState.Open Then
MyCommand.Connection.Open()
End If
End Sub

Sub SetMyCommandParameter(ByVal paraName As String, ByVal
fieldType As System.Data.OleDb.OleDbType, ByVal fieldSize As Integer,
ByVal fieldSourceCol As String)
MyCommand.Parameters.Add(paraName, fieldType, fieldSize,
fieldSourceCol)
End Sub

Sub SetMyAdapterInsert()
Try
MyAdapter.InsertCommand = MyCommand
Catch exp As Exception
MsgBox("Insert Exception ." & exp.Message & " " &
exp.Source)
End Try

End Sub

Sub SetMyAdapterUpdate()
Try
MyAdapter.UpdateCommand = MyCommand
Catch ex As Exception
MsgBox("Update Exception ." & ex.Message & " " &
ex.Source)
End Try

End Sub
Sub UpdateAdapter(ByVal dtTable As String)
MyAdapter.Update(MyDataSet, dtTable)
End Sub

Sub FillDataSet(ByVal TblName As String)
MyAdapter.Fill(MyDataSet, TblName)
End Sub
Sub SetDataSet()
MyDataSet = New System.Data.DataSet
End Sub
'**************************************
 
Thanks for replying William. Here is the code:

I am using a class to handle the connections. The class code is at the
end of the message.

PL is a instance of class that handles the connections.

The following code relates to the datasets, data reterival and data
updates.
UpdateMaster,CopyMasterToFinal subroutines deal with updating the access
table.

GetAccessData subroutine deals with data reterival - I have a feeling
that I am doing something here that is affecting the update...may be
sorting the table!!!

Please let me know if you need any other information to help me.
Once again, thanks for your help.

Needy
Gaurav


<code>
Dim tbCurrent As String
Dim tbUpdates As String
Dim tbFinal As String
Private WithEvents myCurrencyManager As CurrencyManager
Private WithEvents myCurrencyManager1 As CurrencyManager
'Creating a db connection
Dim PL As New ProdLib.ProdClass

'Form Load event
Private Sub frmValidateProducts_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'Three datatable names
tbCurrent = "Master"
tbUpdates = "Updates"
tbFinal = "Final"
Dim tbTemp As System.Data.DataTable
'Get Data from the database in Dataset
GetAccessData(PL)
If PL.ExitImport = True Then
PL.ExitImport = False
cmdNext.Enabled = False
cmdPrev.Enabled = False
cmdFirst.Enabled = False
cmdLast.Enabled = False
cmdCancelEdit.Enabled = False
btnSearch.Enabled = False
btnLink.Enabled = False
Exit Sub
End If
'Exit Sub
'Creating final data
tbTemp = PL.MyDataSet.Tables(tbCurrent).Clone()
tbTemp.TableName = tbFinal
PL.MyDataSet.Tables.Add(tbTemp)
UpdateMaster()
BindTextBoxes(PL)
End Sub


'Gets the data from master table and validation table.
Sub GetAccessData(ByRef PL As ProdLib.ProdClass)
Dim ConExtend As String
Dim sQuery As String
Dim sQuery1 As String

Try
'Setting extended settings
ConExtend = "User Id=" & MainForm.dbUser & ";Password=" &
MainForm.dbPass & ";" & _
"Jet OLEDB:System Database=" & MainForm.dbMW & ";"
'Setting a connection with Access Library
PL.SetMyConnString(MainForm.dbDB, "Microsoft.Jet.OLEDB.4.0;
", ConExtend)
PL.SetConnection()
PL.SetDataSet()

'Query gets all the new products in validation from master
table.
sQuery = "select * " & _
"from master_product_library " & _
"where flag5 = True"
'Query gets all the updates on existing products from master
table.
sQuery1 = "select distinct master_product_library.* " & _
"from master_product_library, updates_product_library " & _
"where master_product_library.upc =
updates_product_library.upc"

'Filling the dataset with access data. Column names in
access are preserved.
PL.SetAdapter(sQuery)
PL.FillDataSet("tempMaster")
PL.SetAdapter(sQuery1)
PL.FillDataSet("tempMaster")
RemoveDuplicateUPC(PL.MyDataSet, "tempMaster", "", "UPC")
Dim tbTempMaster As System.Data.DataTable
tbTempMaster = PL.MyDataSet.Tables("tempMaster").Clone()
tbTempMaster.TableName = tbCurrent
PL.MyDataSet.Tables.Add(tbTempMaster)
SortTable("tempMaster", tbCurrent, "UPC ASC")
PL.MyDataSet.Tables.Remove("tempMaster")
PL.MyDataSet.Tables(tbCurrent).DefaultView.Sort = "UPC"
'Query gets all the updates in update table.
sQuery1 = "select distinct updates_product_library.* " & _
"from master_product_library, updates_product_library " & _
"where master_product_library.upc =
updates_product_library.upc"

''Filling the dataset with access data. Column names in
access are preserved.
PL.SetAdapter(sQuery)
PL.FillDataSet("temp")
PL.SetAdapter(sQuery1)
PL.FillDataSet("temp")
Dim tbTemp As System.Data.DataTable
tbTemp = PL.MyDataSet.Tables("temp").Clone()
tbTemp.TableName = tbUpdates
PL.MyDataSet.Tables.Add(tbTemp)
SortTable("temp", tbUpdates, "UPC ASC")
PL.MyDataSet.Tables.Remove("temp")
PL.MyDataSet.Tables(tbUpdates).DefaultView.Sort = "UPC"
If PL.MyDataSet.Tables(tbUpdates).Rows.Count = 0 Then
MessageBox.Show("No Updates present for validation")
PL.ExitImport = True
Exit Sub
End If
DataGrid1.DataSource = PL.MyDataSet
Catch ex As Exception
MsgBox("Access Connection encountered a problem." & ex.Message &
" " & ex.Source)
End Try
End Sub



Sub RemoveDuplicateUPC(ByRef M_DataSet As System.Data.DataSet, ByVal
tbName As String, ByVal sExpr As String, ByVal sSortVal As String)

Dim OldKey As Object
Dim dr As DataRow
'For Each dr In M_DataSet.Tables(tbName).Select("", "0")
For Each dr In M_DataSet.Tables(tbName).Select(sExpr, sSortVal)
If dr.Item(sSortVal) = OldKey Then
M_DataSet.Tables(tbName).Rows.Remove(dr)
Else
OldKey = dr.Item(sSortVal)
End If
Next
End Sub


Sub SortTable(ByVal tbTableForm As String, ByVal tbTableTo As
String, ByVal SortVal As String)
For Each drow As System.Data.DataRow In
PL.MyDataSet.Tables(tbTableForm).Select("", SortVal)
PL.MyDataSet.Tables(tbTableTo).ImportRow(drow)
Next
End Sub


'Code that calls the adapter.update
Sub CopyMasterToFinal()
Dim drow As System.Data.DataRow
Dim UpdateQuery As String
UpdateMaster()
myCurrencyManager1.EndCurrentEdit()
PL.UpdateAdapter(tbCurrent)
End Sub


'Used to create the commandparameters. Refer to the class below.
Sub UpdateMaster()
Dim UpdateQuery As String
UpdateQuery = "Update master_product_library set ID = ID where UPC =
UPC"
PL.SetMyCommandText(UpdateQuery)
PL.SetMyCommandParameter("UPC", System.Data.OleDb.OleDbType.VarChar,
16, "UPC")
PL.SetMyCommandParameter("ID", System.Data.OleDb.OleDbType.VarChar,
16, "ID")
PL.SetMyAdapterUpdate()
End Sub


'*********************************************
Imports System
Imports System.Data

Namespace ProdLib

Public Class ProdClass
'Creates a connection, adapter and command to connect to the
database
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim MyAdapter As System.Data.OleDb.OleDbDataAdapter
Dim MyCommand As System.Data.OleDb.OleDbCommand
'Shared instance of Mydataset is used to store all the tables.
Public Shared MyDataSet As System.Data.DataSet
Dim MyConnString As String
'ExitImport will remove
Public Shared ExitImport As Boolean

Sub ProdClass()
MyConnection.ConnectionString = ""
MyCommand.Connection = MyConnection
Dim sQuery As String
MyCommand = New System.Data.OleDb.OleDbCommand
MyAdapter = New System.Data.OleDb.OleDbDataAdapter
ExitImport = False
End Sub

Sub ClearDataSet()
MyDataSet.Reset()
End Sub

Function SetMyConnString(ByVal filepath As String, ByVal
ConnProvider As String, ByVal ConnExtended As String) As String
'sets the connection string to connect to a data source
If ConnExtended.Equals("") Then
MyConnString = "Provider=" & ConnProvider & "data
source=" & filepath & ";"
Else
MyConnString = "Provider=" & ConnProvider & _
"data source=" & filepath & ";" & _
ConnExtended
End If
End Function

Private Function GetMyConnString()
Return MyConnString
End Function

Function SetConnection() As String
'connects to a datasource with the help of connection string
MyConnection = New
System.Data.OleDb.OleDbConnection(GetMyConnString())
End Function

Sub CloseConnection()
MyConnection.Close()
End Sub

Sub SetAdapter(ByVal sQuery As String)
'sets the adapter to query the database using the given
connection
MyAdapter = New System.Data.OleDb.OleDbDataAdapter(sQuery,
MyConnection)
End Sub

Sub SetAdapterAcceptSetting(ByVal accept As Boolean)
'MyAdapter.AcceptChangesDuringFill = accept
End Sub

Sub SetMyCommandText(ByVal sQuery As String)
MyCommand = New System.Data.OleDb.OleDbCommand(sQuery,
MyConnection)
'MsgBox("Before open " & MyConnection.State())
If MyConnection.State <> ConnectionState.Open Then
MyCommand.Connection.Open()
End If
End Sub

Sub SetMyCommandParameter(ByVal paraName As String, ByVal
fieldType As System.Data.OleDb.OleDbType, ByVal fieldSize As Integer,
ByVal fieldSourceCol As String)
MyCommand.Parameters.Add(paraName, fieldType, fieldSize,
fieldSourceCol)
End Sub

Sub SetMyAdapterInsert()
Try
MyAdapter.InsertCommand = MyCommand
Catch exp As Exception
MsgBox("Insert Exception ." & exp.Message & " " &
exp.Source)
End Try

End Sub

Sub SetMyAdapterUpdate()
Try
MyAdapter.UpdateCommand = MyCommand
Catch ex As Exception
MsgBox("Update Exception ." & ex.Message & " " &
ex.Source)
End Try

End Sub
Sub UpdateAdapter(ByVal dtTable As String)
MyAdapter.Update(MyDataSet, dtTable)
End Sub

Sub FillDataSet(ByVal TblName As String)
MyAdapter.Fill(MyDataSet, TblName)
End Sub
Sub SetDataSet()
MyDataSet = New System.Data.DataSet
End Sub
'**************************************
 
Hmmm...Difficult to say from just looking at the code. Try one thing, hook
up an eventhandler for the DataTable.Rowchanging event and see if it is
getting fired.
 
Finally I got it working. The command parameters were not added in the
same sequence as they appeared in update command.

Thanks for your help!!
Gaurav
 
Hi Thanks for the reply, but I figured it out (Finally...after three
days of work!!!!).
The command parameters were not added in the same sequence as they
appear in update command. Finally it has started to work.

Thanks to all for help and support.
Gaurav
 
Back
Top