DataSet updated but not updating the database???? Help!!!

[vbdotnetboy]

when i run the following code the State column in the dataset gets updated, but i can't get it to update with the database. thanx in advance for any help...it's monday what can i say

Private Sub SaveNewShipLog()
        Dim LibInfoAdp As OleDbDataAdapter 'For Library Information Table access
        Dim LibProjAdp As OleDbDataAdapter 'For Project Table access
        Dim ShipLogAdp As OleDbDataAdapter 'For Ship Log Table access
        Dim ShipLstAdp As OleDbDataAdapter 'For Ship Log List Table access
        Dim adoBldr As OleDbCommandBuilder
        Dim adoDs As DataSet
 
        Try
            'Setup the adapters
            LibInfoAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("library information"), adoCnn)
            LibProjAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("lib_proj_tbl"), adoCnn)
            ShipLogAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("ship_log_tbl"), adoCnn)
            ShipLstAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("shp_log_lstn_tbl"), adoCnn)
 
            'Make sure the adapters that the primary key.
            LibInfoAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            LibProjAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            ShipLogAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            ShipLstAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
            'Fill the dataset.
            adoDs = New DataSet
            LibInfoAdp.Fill(adoDs, "[Library Information]")
            LibProjAdp.Fill(adoDs, "lib_proj_tbl")
            ShipLogAdp.Fill(adoDs, "ship_log_tbl")
            ShipLstAdp.Fill(adoDs, "shp_log_lstn_tbl")
 
            'Test for library information change
            Me.LibraryInfoUpdateTest(LibInfoAdp, adoDs)
 
            'Dim dRow As DataRow = adoDs.Tables("ship_log_tbl").NewRow()
            'dRow("PROJ_ID") = lngPROJ_ID
            'dRow("LIB_TYPE_ID") = 1
            'dRow("SHP_MTH_ID") = 1
            'dRow("NSO_ID") = 1
            'dRow("ship_date") = Today
            'dRow("attetion_of") = "tester"
            'dRow("cd_cs_person") = "tester2"
            'dRow("lst_pricing") = True
 
            'adoDs.Tables("ship_log_tbl").Rows.Add(dRow)
 
            'adoBldr = New OleDbCommandBuilder(ShipLogAdp)
            'ShipLogAdp.Update(adoDs, "ship_log_tbl")
            'adoBldr.Dispose()
            'adoBldr = Nothing
 
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
 
    Private Function LibraryInfoUpdateTest(ByRef adp As OleDbDataAdapter, ByVal ds As DataSet) As Boolean
        Dim dRow As DataRow = ds.Tables("[Library Information]").Rows(0)
        Dim bld As New OleDbCommandBuilder(adp)
 
        Debug.Write(dRow("State"))
        dRow("State") = "PA"
        Debug.Write(dRow("State"))
        dRow.AcceptChanges()
        adp.Update(ds, "[Library Information]")
        bld.Dispose()
        bld = Nothing
    End Function
 
    Private Function BulidShippingLogSQL(ByVal DB_Table_Name As String) As String
        Select Case DB_Table_Name.ToLower
            Case "library information"
                Return "SELECT " & _
                            "[Library Information].ID, " & _
                            "[Library Information].[Library Name], " & _
                            "[Library Information].Address1, " & _
                            "[Library Information].Address2, " & _
                            "[Library Information].City, " & _
                            "[Library Information].State, " & _
                            "[Library Information].Zip, " & _
                            "[Library Information].[Library Type] " & _
                       "FROM " & _
                            "[Library Information] " & _
                       "WHERE " & _
                            "[Library Information].ID = " & lngLIB_ID
            Case "lib_proj_tbl"
                Return "SELECT * " & _
                       "FROM " & _
                            "lib_proj_tbl " & _
                       "WHERE " & _
                            "PROJ_ID = " & lngPROJ_ID & " AND LIB_ID = " & lngLIB_ID
            Case "ship_log_tbl"
                Return "SELECT * FROM ship_log_tbl"
            Case "shp_log_lstn_tbl"
                Return "SELECT * FROM shp_log_lstn_tbl"
            Case Else
 
        End Select
    End Function

[SeanGrebey]

Get rid of the AcceptChanges line. That resets the RowState property of all rows to Unchanged. Try that and see if it helps.

[vbdotnetboy]

i tried that and it get the following error:

Syntax error (missing operator) in query expression '( (ID = ?) AND ((? = 1 AND Library Name IS NULL) OR (Library Name = ?)) AND ((? = 1 AND Address1 IS NULL) OR (Address1 = ?)) AND ((? = 1 AND Address2 IS NULL) OR (Address2 = ?)) AND ((? = 1 AND City IS NULL) OR (City = ?)) AND ((? = 1 AND State IS NULL) O'.

also when i look at the state of the bld object for OleCommandBuilder for the UpdateCommand it is set to Nothing. I thought that the commandbuilder is suppose to know what to select based on what operation you are doing?


thanx for suggestion

[vbdotnetboy]

*bump*

[vbdotnetboy]

Can anybody give me a suggestion? I have even tried an example that I found in a book...and it doesn't even work...so I'm really confused...


Thanx in advance.

[vbdotnetboy]

Ok as you can see from the code, I have most of commented out. the only code that isn't commented is the where i build the SQL and the code that should update the database...i say should be with the exception of table names and the names of variables and the function that builds and returns the SQL....it's the same structure and same code from a book....and it still doesn't work....what gives??? *** BIG TIME ***!!!!!!!!!!!! Here's the code.

Private Sub SaveNewShipLog()
        Dim LibInfoAdp As OleDbDataAdapter 'For Library Information Table access
        'Dim LibProjAdp As OleDbDataAdapter 'For Project Table access
        'Dim ShipLogAdp As OleDbDataAdapter 'For Ship Log Table access
        'Dim ShipLstAdp As OleDbDataAdapter 'For Ship Log List Table access
        Dim adoBldr As OleDbCommandBuilder
        Dim adoDs As DataSet
 
        Try
            'Setup the adapters
            LibInfoAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("library information"), adoCnn)
            'LibProjAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("lib_proj_tbl"), adoCnn)
            'ShipLogAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("ship_log_tbl"), adoCnn)
            'ShipLstAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("shp_log_lstn_tbl"), adoCnn)
 
            'Make sure the adapters that the primary key.
            'LibInfoAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            'LibProjAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            'ShipLogAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            'ShipLstAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
            adoBldr = New OleDbCommandBuilder(LibInfoAdp)
            'Fill the dataset.
            adoDs = New DataSet
            LibInfoAdp.Fill(adoDs, "[Library Information]")
            'LibProjAdp.Fill(adoDs, "lib_proj_tbl")
            'ShipLogAdp.Fill(adoDs, "ship_log_tbl")
            'ShipLstAdp.Fill(adoDs, "shp_log_lstn_tbl")
 
            'Test for library information change
            'Me.LibraryInfoUpdateTest(LibInfoAdp, adoDs)
 
            adoDs.Tables("[Library Information]").Rows(0)("State") = "PA"
 
            LibInfoAdp.Update(adoDs, "[Library Information]")
            adoBldr.Dispose()
            adoBldr = Nothing
            'Dim dRow As DataRow = adoDs.Tables("ship_log_tbl").NewRow()
            'dRow("PROJ_ID") = lngPROJ_ID
            'dRow("LIB_TYPE_ID") = 1
            'dRow("SHP_MTH_ID") = 1
            'dRow("NSO_ID") = 1
            'dRow("ship_date") = Today
            'dRow("attetion_of") = "tester"
            'dRow("cd_cs_person") = "tester2"
            'dRow("lst_pricing") = True
 
            'adoDs.Tables("ship_log_tbl").Rows.Add(dRow)
 
            'adoBldr = New OleDbCommandBuilder(ShipLogAdp)
            'ShipLogAdp.Update(adoDs, "ship_log_tbl")
            'adoBldr.Dispose()
            'adoBldr = Nothing
 
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
 
    'Private Function LibraryInfoUpdateTest(ByVal adp As OleDbDataAdapter, ByVal ds As DataSet) As Boolean
        'Dim bld As New OleDbCommandBuilder(adp)
        'Dim dRow As DataRow = ds.Tables("[Library Information]").Rows(0)
 
        'adp.UpdateCommand = bld.GetUpdateCommand()
 
        'ds.Tables("[Library Information]").Rows(0)("State") = "PA"
        'adp.Update(ds, "[Library Information]")
        'bld.Dispose()
        'bld = Nothing
    'End Function
 
    Private Function BulidShippingLogSQL(ByVal DB_Table_Name As String) As String
        Select Case DB_Table_Name.ToLower
            Case "library information"
                Return "SELECT " & _
                            "ID, " & _
                            "[Library Name], " & _
                            "Address1, " & _
                            "Address2, " & _
                            "City, " & _
                            "State, " & _
                            "Zip, " & _
                            "[Library Type] " & _
                       "FROM " & _
                            "[Library Information] " & _
                       "WHERE " & _
                            "ID = " & lngLIB_ID
            Case "lib_proj_tbl"
                Return "SELECT * " & _
                       "FROM " & _
                            "lib_proj_tbl " & _
                       "WHERE " & _
                            "PROJ_ID = " & lngPROJ_ID & " AND LIB_ID = " & lngLIB_ID
            Case "ship_log_tbl"
                Return "SELECT * FROM ship_log_tbl"
            Case "shp_log_lstn_tbl"
                Return "SELECT * FROM shp_log_lstn_tbl"
            Case Else
 
        End Select
    End Function

[SeanGrebey]

Hmmm....I usually just write my own SQL and not use the CommandBuilders, but I would suggest that you try creating your command builder after you have filled the dataadapter, not before hand.



'Fill the dataset.
adoDs = New DataSet
LibInfoAdp.Fill(adoDs, "[Library Information]")

adoBldr = New OleDbCommandBuilder(LibInfoAdp)

That is just a thought though....

[mendhak]

I believe the AcceptChanges should come after Update.

For example:

Private Sub DataUpdate()
        Dim da As SqlClient.SqlDataAdapter
        Dim cbuild As SqlClient.SqlCommandBuilder
        Dim dr As DataRow
        Dim strSQL As String
        Dim strConn As String
        Dim intID As Integer
 
        intID = CType(ListBox1.SelectedItem, ListItemNumeric).ID
        dr = ds.Tables("Products").Rows.Find(intID)
 
        dr.BeginEdit()
 
 
        dr("ProductName") = txtName.Text
        dr("SupplierID") = CType(cboSupplier.SelectedItem, ListItemNumeric).ID
        'dr("SupplierID") = CInt(cboSupplier.SelectedIndex + 1)
 
 
        dr("CategoryID") = CType(cboCategory.SelectedItem, ListItemNumeric).ID
        dr("QuantityPerUnit") = txtQty.Text
        dr("UnitPrice") = CDec(txtPrice.Text)
        dr("UnitsInStock") = CShort(txtInStock.Text)
        dr("UnitsOnOrder") = CShort(txtOnOrder.Text)
        dr("ReorderLevel") = CShort(txtReorder.Text)
        dr("Discontinued") = CBool(chkDisc.Checked)
 
        dr.EndEdit()
 
        Try
            strConn = ConnectStringBuild()
            strSQL = "SELECT * FROM Products"
            da = New SqlClient.SqlDataAdapter(strSQL, strConn)
            cbuild = New SqlClient.SqlCommandBuilder(da)
            da.UpdateCommand = cbuild.GetUpdateCommand()
            da.Update(ds, "Products")
            ds.AcceptChanges()
            da.UpdateCommand.Connection.Close()
            ListLoad() 'ignore this line
 
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
 
        End Try
 
    End Sub

[siomai]

hi mendhak! I was checking out the code you posted here and I was just wondering what is the 'ListItemNumeric' in this line?

intID = CType(ListBox1.SelectedItem, ListItemNumeric).ID

thanks! and hope you'd reply on this one...this will really help me cause I'm having the same problem. Take care!

[mendhak]

Originally posted by siomai
hi mendhak! I was checking out the code you posted here and I was just wondering what is the 'ListItemNumeric' in this line?

intID = CType(ListBox1.SelectedItem, ListItemNumeric).ID

thanks! and hope you'd reply on this one...this will really help me cause I'm having the same problem. Take care!

I refuse to believe you have the same problem! ListItemNumeric is a class I created, which holds a Value, and an ID types, which I basically use to populate my listboxes so that I can "use" these values when I'm running a method of some sort.

The line of code that you put there takes the Object in the ListBox, and converts it to ListItemNumeric type and then gets its ID out.

[siomai]

i see...

so that means i can't apply your code to my program?

hmm...could you help me? I can't seem to update my database...

I'm getting so frustrated already..I use the dataadapter.update(dataset) code which doesn't update my database.

please help me! thanks!

here's my code...I'm just trying to edit saved records in my database:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim dr As DataRow
Try
dr.BeginEdit()
dr = DsCust1.Tables("Customername").NewRow
dr("CustomerID") = txtCustID.Text
dr("Customername") = txtCustname.Text
dr("Address") = txtCustAdd.Text
dr("Tel") = txtCustTel.Text
dr("Fax") = txtCustFax.Text
dr("Attention") = txtCustAtt.Text
dr.EndEdit()
daCust1.Update(DsCust1)
MsgBox("Customer record updated", MsgBoxStyle.OKOnly)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Error")
End Try

please help me, mendhak! Take care!

[mendhak]

Did you try something similar to what I posted?

strConn = 'Your connection string goes here
            strSQL = "SELECT * FROM YOURTABLENAME"
'da is dimmed as a dataadapter, you may already have one existing... in your case, dacust1
            da = New SqlClient.SqlDataAdapter(strSQL, strConn)
'cbuild is dimmed as a commandbuilder.  Dim cbuild as SqlClient.SqlCommandBuilder
'This line below will build your SQL statements...
            cbuild = New SqlClient.SqlCommandBuilder(da)
 
            da.UpdateCommand = cbuild.GetUpdateCommand()
            da.Update(ds, "YOURTABLENAME")
            ds.AcceptChanges()
            da.UpdateCommand.Connection.Close()

Modify it to suit your program, then post back.

Don't forget to put it in a Try...Catch block so that you can see the errors, if any.

[siomai]

thanks for replying mendhak!

unfortunately, the code did not update my database and showed no error. But I know I there's something wrong with my coding(or logic )...please bear with me and don't get tired of helping me...

my case is that i created my data adapter,dataset and connection using the wizard. will that cause me any problem?

here's my code now... please to check it out! (thanks! thanks! thanks!)

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim dr As DataRow
Dim strSQL As String
Dim cbuild As System.Data.OleDb.OleDbCommandBuilder

Try
strSQL = "Select * from Customername"
daCust1 = New System.Data.OleDb.OleDbDataAdapter(strSQL, cn1)

cbuild = New System.Data.OleDb.OleDbCommandBuilder(daCust1)

daCust1.UpdateCommand = cbuild.GetUpdateCommand
daCust1.Update(DsCust1, "Customername")
DsCust1.AcceptChanges()
daCust1.UpdateCommand.Connection.Close()
MsgBox("Record has been updated.", MsgBoxStyle.OKOnly)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Error")
End Try

End Sub

[SeanGrebey]

Is your connection opened?

[siomai]

hi SeanGrebey! I made my connection using the wizard and I think it's automatically opened,or not? Eitherway, i tried to add cn1.open to may code and it still did not update my database. Please help me...I really don't know what to do now.

here's my code now:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim dr As DataRow
Dim strSQL As String
Dim cbuild As System.Data.OleDb.OleDbCommandBuilder

Try
cn1.Open()
dr = DsCust1.Tables("Customername").NewRow
dr("CustomerID") = txtCustID.Text
dr("Customername") = txtCustname.Text
dr("Address") = txtCustAdd.Text
dr("Tel") = txtCustTel.Text
dr("Fax") = txtCustFax.Text
dr("Attention") = txtCustAtt.Text
strSQL = "Select * from Customername"
daCust1 = New System.Data.OleDb.OleDbDataAdapter(strSQL, cn1)

cbuild = New System.Data.OleDb.OleDbCommandBuilder(daCust1)

daCust1.UpdateCommand = cbuild.GetUpdateCommand
daCust1.Update(DsCust1, "Customername")
DsCust1.AcceptChanges()
daCust1.UpdateCommand.Connection.Close()
MsgBox("Record has been updated.", MsgBoxStyle.OKOnly)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.OKOnly, "Error")
End Try

End Sub

Hope you could help me! Thanks in advance!

[siomai]

hey guys! I got it already...

Thanks for all your help...

I added a code that I got from one of our friends here in vbforums... just tried it and it worked!

Good day to all!