updating a dataset to msaccess

[trimakassi]

I get an error everytimes i try to update my dataset to msaccess.

here is the code:

Dim dRow As DataRow

row = DsSpendings1.Tables(0).Rows.Count()


dRow = DsSpendings1.Tables(0).NewRow
dRow("Date") = CDate(txtDate.Text)
dRow("Transaction") = row
'filter to get the store name
DsStores1.Tables(0).DefaultView.RowFilter = "store = '" & cboStore.Text & "'"
'getting the store id
dRow("Store") = CInt(DsStores1.Tables(0).DefaultView.Item(0)(2))
dRow("Amount") = CInt(txtAmount.Text)
dRow("Receipt") = CStr(txtreceipt.Text)
dRow("Memo") = CStr(txtMemo.Text)

DsSpendings1.Tables(0).Rows.Add(dRow)

'Me.BindingContext(DsSpendings1, "DbSpendings").AddNew()
OleDbDataAdapter1.Update(DsSpendings1)

thanks

Trimakassi
Boston

 

[jebenson]

what line of code is causing the error, wnd what is the error?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[trimakassi]

OleDbDataAdapter1.Update(DsSpendings1)

notice that the me.bindingcontext line is not active

thanks jebenson

 

[trimakassi]

I think before updating the database I should insert a row using some kind of direct control over the Ms Access database. what do you think

 

[chrissie1]

it should be

Me.BindingContext
(DsSpendings1, "DbSpendings").endcurrentedit()

but I think that's not it

So I ask what jebenson already did, what is the errormessage.

BTW a .update should be in a try...catch statement IMHO.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[trimakassi]

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

this would be the error that I get and it stops on the update line

OleDbDataAdapter1.Update(DsSpendings1, "dbSpendings")

I think that when it tries to update, it finds that in the database there is less rows than in the dataset. maybe this is what causes the prob.
How do we directly insert a row into a database?

thank you guys

 

[bboffin]

The Update method tries to be intelligent and will INSERT any new rows in the dataset into the real database and UPDATE rows that have changed provided that you have specified (or generated) the SQL statements to do this.

The normal way is to use the 'Configure Data Adapter' wizard to do this. This can be activated from the options in the Properties window for the adapter - provided of course that it was created using the designer.

If the Data Adapter has been declared only in code you will have to create OleDbCommand objects for the InsertCommand and UpdateCommand and assign them to the corresponding properties of the Data Adapter.

The documentation of the Update method is not very explicit about what parameters these commands should be expecting. The generated versions seem to use @ColumnName and @Orig_ColumnName following SQL Server conventions which may not be appropriate for Access.

My guess is that it may be safer to loop round the rows in the DataTable inspecting the RowState property of each row and executing an appropriate SQL statement for each row.

For Each lRow As DataRow In DsSpendings1.Tables(0).Rows
    Select Case lRow.RowState
    Case DataRowState.Added
       InsertDataRow(lRow)      ' subroutine to insert data
    Case DataRowState.Modified
       UpdateDataRow(lRow)      ' subroutine to update data
    Case DataRowState.Deleted
       DeleteDataRow(lRow)      ' subroutine to delete data
    End Select
Next

Bob Boffin

 

[ghost807]

i frequently run into using a reserved name on accident
recently is was indicators(reserved)
we sell weight indicators

these are some examples from a recent project i have worked on. maybe they will help you out.
i mostly use access(for the cost reasons) for my databasing so this might be the right answers for you



database retrieval

    Public Function GetJob(ByVal JobNumber As String) As clsJob
        Try
            Dim IDX As Integer = 0
            Dim job As New clsJob
            Dim Ds As New TestAppDB
            Dim Connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            Application.ExecutablePath.Substring(0, Application.ExecutablePath.Length - 11) & "testappdb.mdb"
            Dim adapter As New OleDbDataAdapter("SELECT * FROM tblJob WHERE JobNumber LIKE " & _
            Chr(34) & "%" & JobNumber & "%" & Chr(34), Connect)
            adapter.Fill(Ds, "tblJob")


            For IDX = 0 To Ds.tblJob.Rows.Count - 1
                Dim idx1 As Integer
                For idx1 = 0 To Ds.tblJob.Columns.Count - 1
                    If Ds.tblJob(IDX).Item(idx1) Is DBNull.Value Then
                        Ds.tblJob(IDX).Item(idx1) = ""
                    End If
                Next

                With job
                    .JobNumber = Ds.tblJob(IDX).JobNumber
                    .Description(1) = Ds.tblJob(IDX).Description1
                    .Description(2) = Ds.tblJob(IDX).Description2
                    .Description(3) = Ds.tblJob(IDX).Description3
                    .Description(4) = Ds.tblJob(IDX).Description4
                    .Comment(1) = Ds.tblJob(IDX).Comment1
                    .Comment(2) = Ds.tblJob(IDX).Comment2
                    .Comment(3) = Ds.tblJob(IDX).Comment3
                    .Comment(4) = Ds.tblJob(IDX).Comment4
                    .UDF(1) = Ds.tblJob(IDX).UDF1
                    .UDF(2) = Ds.tblJob(IDX).UDF2
                    .UDF(3) = Ds.tblJob(IDX).UDF3
                    .UDF(4) = Ds.tblJob(IDX).UDF4
                    .UDF(5) = Ds.tblJob(IDX).UDF5
                    .UDF(6) = Ds.tblJob(IDX).UDF6
                    .UDF(7) = Ds.tblJob(IDX).UDF7
                    .UDF(8) = Ds.tblJob(IDX).UDF8
                    .UDF(9) = Ds.tblJob(IDX).UDF9
                    .UDF(10) = Ds.tblJob(IDX).UDF10
                End With
            Next
            Ds.Dispose()
            adapter.Dispose()
            Return job
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Function

updating a record

    Public Sub UpdateJob(ByVal Job As clsJob)
        Try
            Dim Connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            Application.ExecutablePath.Substring(0, Application.ExecutablePath.Length - 11) & "testappdb.mdb"

            Dim conn As New OleDbConnection(Connect)
            Dim adapter As New OleDbDataAdapter
            Dim SQLString As String = "SELECT * FROM tblJob WHERE JobNumber = " & Chr(34) & Job.JobNumber & Chr(34)
            adapter.SelectCommand = New OleDbCommand(SQLString, conn)
            Dim cmdbuilder As New OleDbCommandBuilder(adapter)
            conn.Open()
            Dim JobUpdate As New TestAppDB
            adapter.TableMappings.Add("Table", "tblJob")
            adapter.Fill(JobUpdate.Tables("tblJob"))
            Dim idx As Integer
            For idx = 0 To JobUpdate.tblJob.Rows.Count - 1
                With Job
                    If Job.JobNumber.ToUpper = JobUpdate.tblJob(idx).JobNumber.ToUpper Then
                        JobUpdate.tblJob(idx).Description1 = .Description(1)
                        JobUpdate.tblJob(idx).Description2 = .Description(2)
                        JobUpdate.tblJob(idx).Description3 = .Description(3)
                        JobUpdate.tblJob(idx).Description3 = .Description(4)
                        JobUpdate.tblJob(idx).Comment1 = .Comment(1)
                        JobUpdate.tblJob(idx).Comment2 = .Comment(2)
                        JobUpdate.tblJob(idx).Comment3 = .Comment(3)
                        JobUpdate.tblJob(idx).Comment4 = .Comment(4)
                        JobUpdate.tblJob(idx).UDF1 = .UDF(1)
                        JobUpdate.tblJob(idx).UDF2 = .UDF(2)
                        JobUpdate.tblJob(idx).UDF3 = .UDF(3)
                        JobUpdate.tblJob(idx).UDF4 = .UDF(4)
                        JobUpdate.tblJob(idx).UDF5 = .UDF(5)
                        JobUpdate.tblJob(idx).UDF6 = .UDF(6)
                        JobUpdate.tblJob(idx).UDF7 = .UDF(7)
                        JobUpdate.tblJob(idx).UDF8 = .UDF(8)
                        JobUpdate.tblJob(idx).UDF9 = .UDF(9)
                        JobUpdate.tblJob(idx).UDF10 = .UDF(10)

                        Dim idx1 As Integer
                        For idx1 = 0 To JobUpdate.tblJob.Columns.Count - 1
                            If JobUpdate.tblJob(idx).Item(idx1) Is "" Then
                                JobUpdate.tblJob(idx).Item(idx1) = DBNull.Value
                            End If
                        Next
                    End If
                End With
            Next
            adapter.Update(JobUpdate)
            adapter.Dispose()
            JobUpdate.Dispose()
            conn.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

Adding a record

    Public Sub AddJob(ByVal Job As clsJob)
        With Job
            If .JobNumber = "" Then
                MsgBox("Please Enter a Valid Number for the Job.")
                Exit Sub
            End If
            Try
                Dim Connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.ExecutablePath.Substring(0, Application.ExecutablePath.Length - 11) & "testappdb.mdb"
                Dim Conn As New OleDbConnection(Connect)
                Dim adapter As New OleDbDataAdapter

                adapter.SelectCommand = New OleDbCommand("SELECT * FROM tblJob", Conn)
                Dim cmdbuilder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)
                Conn.Open()

                Dim addable As New TestAppDB
                adapter.TableMappings.Add("Table", "tblJob")
                adapter.Fill(addable.Tables("tblJob"))

                Dim cRow As TestAppDB.tblJobRow _
                = addable.tblJob.NewtblJobRow

                cRow.JobNumber = .JobNumber
                cRow.Description1 = .Description(1)
                cRow.Description2 = .Description(2)
                cRow.Description3 = .Description(3)
                cRow.Description4 = .Description(4)
                cRow.Comment1 = .Comment(1)
                cRow.Comment2 = .Comment(2)
                cRow.Comment3 = .Comment(3)
                cRow.Comment4 = .Comment(4)
                cRow.UDF1 = .UDF(1)
                cRow.UDF2 = .UDF(2)
                cRow.UDF3 = .UDF(3)
                cRow.UDF4 = .UDF(4)
                cRow.UDF5 = .UDF(5)
                cRow.UDF6 = .UDF(6)
                cRow.UDF7 = .UDF(7)
                cRow.UDF8 = .UDF(8)
                cRow.UDF9 = .UDF(9)
                cRow.UDF10 = .UDF(10)

                ''''''''''''''''''''''''''''''''''''''''''
                '''Check and set Nulls else the DB will Flip Out
                ''''''''''''''''''''''''''''''''''''''''''
                Dim idx As Integer
                For idx = 0 To addable.tblJob.Columns.Count - 1
                    If cRow.Item(idx) Is "" Then
                        cRow.Item(idx) = DBNull.Value
                    End If

                Next
                ''''''''''''''''''''''''''''''''''''''''''
                '''We are done. Add the row
                ''''''''''''''''''''''''''''''''''''''''''
                addable.tblJob.AddtblJobRow(cRow)
                adapter.Update(addable)
                Conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End With

    End Sub

how i normally delete a record

    Public Sub DeleteJob(ByVal Job As clsJob)

        Dim Connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.ExecutablePath.Substring(0, Application.ExecutablePath.Length - 11) & "testappdb.mdb"
        Dim deleteConn As New OleDbConnection(Connect)
        Dim deleteCommand As New OleDbCommand("DELETE * FROM tblJob WHERE JobNumber = " & Chr(34) & Job.JobNumber & Chr(34), deleteConn)
        deleteConn.Open()
        Try
            deleteCommand.ExecuteNonQuery()
        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try
        deleteConn.Close()
        deleteCommand.Dispose()

    End Sub

 

[trimakassi]

Hey bob,
I am sorry this might seem stupid but is the Datarowstate(lrow) an integrated function or what cause when I try to insert it it not recognized

I tried actually using SQL code but without success.

 

[trimakassi]

this is my new sql code that i tested and worked in a test example dealing with one table, one column, and one textbox. Now that I have adapted it to my program it gives me an syntax error in insert into statement!!




Try
OleDbConnection1.Open()
Dim command As String
Dim store As Integer
'set up an SQL insert
DsStores1.Tables(0).DefaultView.RowFilter = "store = '" & cboStore.Text & "'"
'getting the store id
STORE = CInt(DsStores1.Tables(0).DefaultView.Item(0)(2))

command = "insert into dbSpendings(date,Transaction,Store,Amount,Receipt,Memo)" & " values('" & CDate(txtDate.Text) & "',row,STORE,'" & CInt(txtAmount.Text) & "','" & CStr(txtReceipt.Text) & "','" & CStr(txtMemo.Text) & "')"

OleDbDataAdapter1.InsertCommand.CommandText = command

' do the insert
OleDbDataAdapter1.InsertCommand.ExecuteNonQuery()
Catch exceptionObject As Exception
MessageBox.Show(exceptionObject.Message)
Finally
OleDbConnection1.Close()
End Try

 

[ghost807]

ok, now what you should do is use access and create a query just like what you want and test it there.(you'll have to make up some of the data but in sql view that should be ok.)
i'm willing to bet it's a simple punc. error.


also these words are reserved
Reserved Words

http://support.microsoft.com/default.aspx?scid=kb;en-us;209187

date
memo
values

 

[ghost807]

For existing objects with names that contain reserved words, you can avoid errors by surrounding the object name with brackets ([ ]).

 

[trimakassi]

GHOST807 you got it on the spot, I just figured it out. it was indeed the [].

thanks guys

Trimakassi
Boston

 

[ghost807]

Glad to hear it.
i find that i forget to look and see what names/words i'm using and depending on if i'm using msaccess or sql it always gets me into trouble.