Solved

update Access database from datatable not working - vb.net/windows app

Posted on 2004-08-11
32
585 Views
Last Modified: 2008-01-09
This seems like the million dollar question around here, so I really hate to ask about it AGAIN.. but none of the many examples and methods seem to work in my code. (I'm new @ vb.net , hopefully there isn't too much tar and feathers around =) )

What the code is doing:

1. SQL query takes a row of information out of the database based on order number and item code (works)
2. Puts this data into a DataTable called 'itemTable' (works)
3. Determines if it should decrease the need_scan variable (works)
4. Stores data in the need_scan field as variable 'intScan' (works)
5. Decreases intScan by 1, and stores this new value as 'updateScan' (works)
6. Updates the need_scan field with the new variable updateScan ******** problem ********
7. Updates the Access database with this new datatable ******* problem ******

In design mode I created a DataAdapter 'odaChecking' , and connection 'odcChecking' - (Also a DataSet dsChecking, but I haven't used it)

Part of this question is also seeing if updating based on SQL or an updated DataTable or potentially a DataSet will work. So far all 3 methods have failed me! On to the code...........

' /// Query to get the item
        itemTable = New DataTable
        dsItems = New DataSet

        sql = ("SELECT order_no,item,qty,need_scan,processed FROM checking WHERE order_no=" & dblOrderNo & "AND item='" & strItem & "'")
       
        odaChecking.SelectCommand.CommandText = sql
        odaChecking.Fill(itemTable)

        ' /// Check to see if Item exists for that Order
        Dim item_found As Boolean = False
        If itemTable.Rows.Count > 0 Then

            ' /// Item Found, Perform Updates
            item_found = True
            Dim intScan, updateScan As Integer

            intScan = Convert.ToInt32(itemTable.Rows(0).Item("need_scan"))

            ' /// Make sure Qty hasn't already been met
            If intScan < 1 Then
                MsgBox("Quantity for " & strItem & " has already been met")
                txtItem.Focus()
                Exit Sub
            Else
                ' /// Decrease need_scan and Update the Database

                updateScan = intScan - 1
                itemTable.Rows(0).Item("need_scan") = updateScan         ' <---Trying to update the DataTable itemTable, its not 'saving' --
                odaChecking.Fill(dsItems, "itemTable")        '<--- was filling a DataSet with the itemTable data, seeing if it changed or not --
                'View changed data with dsItems DataSet
                OrderGrid.DataSource = dsItems                 '<--- Viewing the (hopefully) changed Datatable , no dice !----
             
            End If

 ' /// Attempt to update the database based on SQL string (Even if I can get the itemTable to update, then its trying to update the database)

            Dim updateSQL As String
            odaChecking.UpdateCommand.CommandText  = updateSQL
            updateSQL = ("UPDATE checking SET need_scan=" & updateScan & " WHERE order=" & dblOrderNo & "AND item='" & strItem & "'")

  ' /// Attempt to update the database based on the dataset dsItems

            odaChecking.Update(dsItems) '<--- doesn't work either

Thanks for any help!
0
Comment
Question by:tj44
  • 16
  • 16
32 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11781262
Hi tj44,

this is where it goes wrong

  updateScan = intScan - 1
                itemTable.Rows(0).Item("need_scan") = updateScan         ' OK
                odaChecking.Fill(dsItems, "itemTable")        'Not OK you fill itemtable with the old data again you haven't saved the things you changed in the previous line, and you don't have to the change you made in the previous line should immidiately be visible in the datagrid.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11781294
after that ofcourse you  still have to save your changed data back to the database with your odachecking.update
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11781498
so this should be enough

 updateScan = intScan - 1
 itemTable.Rows(0).Item("need_scan") = updateScan        
 OrderGrid.DataSource = dsItems  

if you want to save your data back to the datatable do something like

Dim cb As OLEDBCommandBuilder
cb = New oledbCommandBuilder(odaChecking)        
odachecking.update(dsitems)

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:tj44
ID: 11785546
Sorry, still having problems

I saved the data from the query into the DataTable called itemTable, then tried to update it, not dsItems              

itemTable.Rows(0).Item("need_scan") = updateScan      '<-- This makes the change in the itemTable DataTable
               
odaChecking.Fill(dsItems, "itemTable")        '<-- Try to put itemTable data, into the DataSet dsItems

If I put the line: OrderGrid.DataSource = dsItems as you suggested, dsItems has not been filled with anything and the grid is blank. I can view the "updated" DataTable by doing the line you suggested above, but with itemTable as the datasource rather than dsItems

OrderGrid.DataSource = itemTable '<-- Shows the updated decreased 'need_scan' value on the screen

Problem is, when I go to save this, using:       odaChecking.Update(itemTable)         , I get this error:

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

I tried to put these lines in that you suggested:

Dim cb As OLEDBCommandBuilder   '<--- says OLEDBCommandBuilder undefined
cb = New oledbCommandBuilder(odaChecking)      '<-- again says its undefined  

I'm wondering if odaChecking.Update doesn't like DataTables.. so that's why I tried putting itemTable into dsItems , but itemTable isn't passing the changed 'need_scan' field to dsItems

-TJ


0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11785790
I'm totally lost at what you are trying to do.

you say you have a datatable Itemtable which is filled from dataadapter odachecking (odaChecking.Fill(itemTable))
you then change the item need_scan with a new value (itemTable.Rows(0).Item("need_scan") = updateScan )
and then you do this again

odaChecking.Fill(dsItems, "itemTable")  --> WHY

This will discard your changes, and just fill the table again from the database, this will not put the itemtable into the dataset

I think this is what you are trying to do (I'm relying on the code you gave in the first place)

        dsItems = New DataSet

        sql = ("SELECT order_no,item,qty,need_scan,processed FROM checking WHERE order_no=" & dblOrderNo & "AND item='" & strItem & "'")
       
        odaChecking.SelectCommand.CommandText = sql
        odaChecking.Fill(dsitems,"itemtable")

        ' /// Check to see if Item exists for that Order
        Dim item_found As Boolean = False
        If dsitems.tables("itemtable").Rows.Count > 0 Then

            ' /// Item Found, Perform Updates
            item_found = True
            Dim intScan, updateScan As Integer

            intScan = Convert.ToInt32(itemTable.Rows(0).Item("need_scan"))

            ' /// Make sure Qty hasn't already been met
            If intScan < 1 Then
                MsgBox("Quantity for " & strItem & " has already been met")
                txtItem.Focus()
                Exit Sub
            Else
                ' /// Decrease need_scan and Update the Database

                updateScan = intScan - 1
                dsitems.tables("itemTable").Rows(0).Item("need_scan") = updateScan        
                OrderGrid.DataSource = dsItems                
                ordergrid.datamember = "itemtable"              
            End If

to save your updated table back to your database (NOT DATASET, because that has already been updated)

Dim cb As System.Data.OleDb.OleDbCommandBuilder
cb = New System.Data.OleDb.OleDbCommandBuilder(odaChecking)        
odachecking.update(dsitems)
dsitems.acceptchanges

if you're still having problems, let me know
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11785833
sorry, this sentence

This will discard your changes, and just fill the table again from the database, this will not put the itemtable into the dataset

should be

This will just fill the table again from the database and put it in the dataset so now you have 2 itemtables
One with your changes itemtable and
one without dsitems.tables("itemtable") and then you try to update this one

0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11785857
sorry I forgot to update this line

intScan = Convert.ToInt32(itemTable.Rows(0).Item("need_scan"))

it should be

intScan = Convert.ToInt32(dsitems.tables("itemTable").Rows(0).Item("need_scan"))
0
 

Author Comment

by:tj44
ID: 11786085
Thanks Ronald,

I see what you mean about the odaChecking.Fill(dsItems, "itemTable"). For some reason I was thinking it would fill a new dataset with the updated DataTable.

To answer the question about what I'm trying to do, all I want is to bring out that specific row from the database, and change the need_scan field value, and update the database with the new value

I implemented your code, and I do not get any errors. However, I exit the program and open my database, and the need_scan field value
has *not* changed.

Code:

sql = ("SELECT order_no,item,qty,need_scan,processed FROM checking WHERE order_no=" & dblOrderNo & "AND item='" & strItem & "'")

        odaChecking.SelectCommand.CommandText = sql
        odaChecking.Fill(dsitems, "itemtable")

        ' /// Check to see if Item exists for that Order
        Dim item_found As Boolean = False

        If dsItems.Tables("itemtable").Rows.Count > 0 Then
            ' /// Item Found, Perform Updates
            item_found = True
            Dim intScan, updateScan As Integer

            intScan = Convert.ToInt32(dsItems.Tables("itemTable").Rows(0).Item("need_scan"))

            ' /// Make sure Qty hasn't already been met
            If intScan < 1 Then
                MsgBox("Quantity for " & strItem & " has already been met")
                txtItem.Focus()
                Exit Sub
            Else
                ' /// Decrease need_scan and Update the Database

                updateScan = intScan - 1
                dsItems.Tables("itemTable").Rows(0).Item("need_scan") = updateScan
                OrderGrid.DataSource = dsItems
                OrderGrid.DataMember = "itemtable"

            End If

        End If

        Dim cb As System.Data.OleDb.OleDbCommandBuilder
        cb = New System.Data.OleDb.OleDbCommandBuilder(odaChecking)
        odaChecking.Update(dsItems)
        dsItems.AcceptChanges()


0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11786143
Ok, now we are getting somewhere. Could you add this line in your code
        MsgBox(dsitems.HasChanges())

your put that line here

                updateScan = intScan - 1
                dsItems.Tables("itemTable").Rows(0).Item("need_scan") = updateScan
                MsgBox(dsitems.HasChanges())
                OrderGrid.DataSource = dsItems
                OrderGrid.DataMember = "itemtable"

and tel me what the messagebox says
0
 

Author Comment

by:tj44
ID: 11786169
it returns TRUE
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11786198
That is good

Does your ordergrid show the changed data ?
0
 

Author Comment

by:tj44
ID: 11786211
Yes, the ordergrid is showing the updated value
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11786450
Ok so it is the commandbuilder, maybe the selectcommand is to complex for the commandbuilder to work.
(which I still think is strange)

then try your original idea

Dim updateSQL As String
updateSQL = ("UPDATE checking SET need_scan=" & updateScan & " WHERE order=" & dblOrderNo & "AND item='" & strItem & "'")
odaChecking.UpdateCommand.CommandText  = updateSQL
odachecking.update(dsitems)
0
 

Author Comment

by:tj44
ID: 11786672
Unfortunatley I tried that just a minute ago,

The database still isn't being updated.

I've created my adapter and connection in design mode, clicking the Data tab and creating a new adapter that way, as well as a connection

On my frmMain_load I have the following to connect to the database:

Try
            Me.odcChecking.ConnectionString = _
              "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source = C:\databases\checking.mdb"
        Catch eConnection As System.Exception
            MessageBox.Show(eConnection.Message)
        End Try

In other examples it seems people make their connections and adapters in the code. Just wondering if somewhere this could cause a problem, like opening and closing a connection. It was my understanding that using my DataAdapter calls (odaChecking) opened and closed the connection as needed. I just don't understand how an SQL statement can get the data just fine, but an SQL statement isn't able to update it.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11787202
You are correct the dataadapters fill and update method will open the connection for you.

Could you post the COMPLETE sub where you try to update maybe there is something else I overlooked.

Because I just tried something simular and that updated the access database just fine.

No worries we'll get there


0
 

Author Comment

by:tj44
ID: 11787279
Okay, I'm doing up a test_app here real qick, with just the basics to read, update, and write to the database using SQL
If I can get THAT working then I should be able to apply it!

I'll have code in a minute ;)
0
 

Author Comment

by:tj44
ID: 11787490
Okay, here is a complete sub

Private Sub btnOrder_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOrder.Click
        Dim dblOrder As Double
        Dim sql, strItem, updateSQL As String
        Dim dt As DataTable
        Dim ds As DataSet
        Dim intScan, updateScan As Integer

        dt = New DataTable
        ds = New DataSet

        dblOrder = Val(txtOrder.Text)
        strItem = txtItem.Text


        sql = ("SELECT orderID,item,qty,need_scan,processed FROM checking WHERE orderID=" & dblOrder & "AND item='" & strItem & "'")
        odaAdapter.SelectCommand.CommandText = sql
        odaAdapter.Fill(ds, "dt")

        Dim item_found As Boolean = False
        If ds.Tables("dt").Rows.Count > 0 Then
            item_found = True

            intScan = Convert.ToInt32(ds.Tables("dt").Rows(0).Item("need_scan"))
            MsgBox(intScan)
            If intScan < 1 Then
                MsgBox("Too Many")
                Exit Sub
            End If

            updateScan = intScan - 1
            ds.Tables("dt").Rows(0).Item("need_scan") = updateScan
            MsgBox(ds.HasChanges())
            ordergrid.DataSource = ds
            ordergrid.DataMember = "dt"
        End If
        updateSQL = ("UPDATE checking SET need_scan=" & updateScan & "WHERE order=" & dblOrder & "AND item='" & strItem & "'")
        odaAdapter.UpdateCommand.CommandText = updateSQL
        odaAdapter.Update(ds)


Has changes says true
in the ordergird, it displays the updated value

Still isn't updating though
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11787538
Could you also add the MsgBox(ds.HasChanges())

just before odaAdapter.Update(ds)
and tell me the result
0
 

Author Comment

by:tj44
ID: 11787657
It also says true
0
 

Author Comment

by:tj44
ID: 11787716
Something I just noticed. The database number for need_scan is 5

I have this line

intScan = Convert.ToInt32(ds.Tables("dt").Rows(0).Item("need_scan"))
            MsgBox(intScan) <----------------------------------------------------------- Says 5

Those two haschanges says True

But I added this:

 MsgBox(ds.HasChanges())
        MsgBox(intScan)   <------------ It still says 5!
        odaAdapter.Update(ds)

I think haschanges is lying ;)

0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11787743
Hold the phone,

in your selectstatement you use orderID

        sql = ("SELECT orderID,item,qty,need_scan,processed FROM checking WHERE orderID=" & dblOrder & "AND item='" & strItem & "'")

and in your update you use just order

        updateSQL = ("UPDATE checking SET need_scan=" & updateScan & "WHERE order=" & dblOrder & "AND item='" & strItem & "'")
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11787795
also there aren't any spaces between the WHERE and AND

so try this

        updateSQL = "UPDATE checking SET need_scan=" & updateScan & " WHERE orderID=" & dblOrder & " AND item='" & strItem & "'"
0
 

Author Comment

by:tj44
ID: 11787816
and.. I used the same variable to test if it had changed! DUH!

of course intScan will say 5.. I should have put msgbox(updateScan)

I'll try these to changes real quick! =)
0
 

Author Comment

by:tj44
ID: 11787861
Argh..

Here's the code; changes still aren't happening

 updateSQL = "UPDATE checking SET need_scan=" & updateScan & " WHERE orderID=" & dblOrder & " AND item='" & strItem & "'"
        odaAdapter.UpdateCommand.CommandText = updateSQL
        MsgBox(ds.HasChanges())
        MsgBox(updateScan)
        odaAdapter.Update(ds)

I click the button, and the first msgbox(intscan) tells me 5

it runs, the last msgbox(updateScan) tells me 4

I click the button again; first message says 5 again.. shouldn't it say 4 if it was really working?

0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11788024
Argh.. indeed

if you just type something like

updateSQL = "UPDATE checking SET need_scan= 4  WHERE orderID= 1  AND item='sometext'"
(ofcourse replace the 1 and the sometext with the correct identifiers)

if this doesn't work could you try that same statement directly in Access and if it works there
0
 

Author Comment

by:tj44
ID: 11788178
Okay.. I changed the code so basically it just runs a select query, then and update query with those values

sql = ("SELECT orderID,item,qty,need_scan,processed FROM checking WHERE orderID=" & dblOrder & "AND item='" & strItem & "'")
        odaAdapter.SelectCommand.CommandText = sql
        odaAdapter.Fill(ds, "dt")

updateSQL = "UPDATE checking SET need_scan= 4  WHERE orderID= 1009  AND item='test'"

        odaAdapter.UpdateCommand.CommandText = updateSQL
        MsgBox(ds.HasChanges())
        odaAdapter.Update(ds)

has changes is now returning false

Updates did not happen. So I went into Access, and formed a new query. I ran it, and it updated correctly.

changed to SQL view and here is the statement:

UPDATE checking SET checking.need_scan = 4
WHERE (((checking.orderID)=1009) AND ((checking.item)="test"));
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11788287
sorry, just this won't update indeed

sql = ("SELECT orderID,item,qty,need_scan,processed FROM checking WHERE orderID=" & dblOrder & "AND item='" & strItem & "'")
        odaAdapter.SelectCommand.CommandText = sql
        odaAdapter.Fill(ds, "dt")

'**********add this line between*****

ds.Tables("dt").Rows(0).Item("need_scan") = 4

updateSQL = "UPDATE checking SET need_scan= 4  WHERE orderID= 1009  AND item='test'"

        odaAdapter.UpdateCommand.CommandText = updateSQL
        MsgBox(ds.HasChanges())
        odaAdapter.Update(ds)

I'm afraid this is my last comment for today it is 22:55 over here and I'm going home now. But tomorrow morning 8:30. I will have a solution for you, I promise.

Until tomorrow tj44

0
 

Author Comment

by:tj44
ID: 11788335
Ok Ronald!

I appreciate your help!
0
 

Author Comment

by:tj44
ID: 11789266
If interested, this may also help with a greater understanding of my overrall project.

http://www.eponymity.com/vb/

-TJ
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 250 total points
ID: 11790884
Good morning tj44,

I've tried to recreate your program from the link, and came up with this (this works I've tested it)
Just cut and paste it in to a new form (for testing and see if it works, It should)

Public Class Form3
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents txtorder As System.Windows.Forms.TextBox
    Friend WithEvents btnorder As System.Windows.Forms.Button
    Friend WithEvents txtitem As System.Windows.Forms.TextBox
    Friend WithEvents btncheck As System.Windows.Forms.Button
    Friend WithEvents odaadapter As System.Data.OleDb.OleDbDataAdapter
    Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbUpdateCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbDeleteCommand1 As System.Data.OleDb.OleDbCommand
    Friend WithEvents OleDbConnection2 As System.Data.OleDb.OleDbConnection
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.txtorder = New System.Windows.Forms.TextBox
        Me.btnorder = New System.Windows.Forms.Button
        Me.txtitem = New System.Windows.Forms.TextBox
        Me.btncheck = New System.Windows.Forms.Button
        Me.odaadapter = New System.Data.OleDb.OleDbDataAdapter
        Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
        Me.OleDbConnection2 = New System.Data.OleDb.OleDbConnection
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(16, 8)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(328, 224)
        Me.DataGrid1.TabIndex = 0
        '
        'txtorder
        '
        Me.txtorder.Location = New System.Drawing.Point(360, 48)
        Me.txtorder.Name = "txtorder"
        Me.txtorder.Size = New System.Drawing.Size(168, 20)
        Me.txtorder.TabIndex = 1
        Me.txtorder.Text = ""
        '
        'btnorder
        '
        Me.btnorder.Location = New System.Drawing.Point(376, 80)
        Me.btnorder.Name = "btnorder"
        Me.btnorder.Size = New System.Drawing.Size(136, 32)
        Me.btnorder.TabIndex = 2
        Me.btnorder.Text = "Button1"
        '
        'txtitem
        '
        Me.txtitem.Location = New System.Drawing.Point(360, 176)
        Me.txtitem.Name = "txtitem"
        Me.txtitem.Size = New System.Drawing.Size(168, 20)
        Me.txtitem.TabIndex = 3
        Me.txtitem.Text = ""
        '
        'btncheck
        '
        Me.btncheck.Location = New System.Drawing.Point(384, 224)
        Me.btncheck.Name = "btncheck"
        Me.btncheck.Size = New System.Drawing.Size(128, 40)
        Me.btncheck.TabIndex = 4
        Me.btncheck.Text = "Button1"
        '
        'odaadapter
        '
        Me.odaadapter.DeleteCommand = Me.OleDbDeleteCommand1
        Me.odaadapter.InsertCommand = Me.OleDbInsertCommand1
        Me.odaadapter.SelectCommand = Me.OleDbSelectCommand1
        Me.odaadapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "checking", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("item", "item"), New System.Data.Common.DataColumnMapping("need_scan", "need_scan"), New System.Data.Common.DataColumnMapping("orderID", "orderID"), New System.Data.Common.DataColumnMapping("processed", "processed"), New System.Data.Common.DataColumnMapping("qty", "qty")})})
        Me.odaadapter.UpdateCommand = Me.OleDbUpdateCommand1
        '
        'OleDbSelectCommand1
        '
        Me.OleDbSelectCommand1.CommandText = "SELECT item, need_scan, orderID, processed, qty FROM checking"
        Me.OleDbSelectCommand1.Connection = Me.OleDbConnection2
        '
        'OleDbInsertCommand1
        '
        Me.OleDbInsertCommand1.CommandText = "INSERT INTO checking(item, need_scan, orderID, processed, qty) VALUES (?, ?, ?, ?" & _
        ", ?)"
        Me.OleDbInsertCommand1.Connection = Me.OleDbConnection2
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("item", System.Data.OleDb.OleDbType.VarWChar, 50, "item"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("need_scan", System.Data.OleDb.OleDbType.Integer, 0, "need_scan"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("orderID", System.Data.OleDb.OleDbType.Integer, 0, "orderID"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("processed", System.Data.OleDb.OleDbType.Integer, 0, "processed"))
        Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("qty", System.Data.OleDb.OleDbType.Integer, 0, "qty"))
        '
        'OleDbUpdateCommand1
        '
        Me.OleDbUpdateCommand1.CommandText = "UPDATE checking SET item = ?, need_scan = ?, orderID = ?, processed = ?, qty = ? " & _
        "WHERE (item = ?) AND (orderID = ?) AND (need_scan = ? OR ? IS NULL AND need_scan" & _
        " IS NULL) AND (processed = ? OR ? IS NULL AND processed IS NULL) AND (qty = ? OR" & _
        " ? IS NULL AND qty IS NULL)"
        Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection2
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("item", System.Data.OleDb.OleDbType.VarWChar, 50, "item"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("need_scan", System.Data.OleDb.OleDbType.Integer, 0, "need_scan"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("orderID", System.Data.OleDb.OleDbType.Integer, 0, "orderID"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("processed", System.Data.OleDb.OleDbType.Integer, 0, "processed"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("qty", System.Data.OleDb.OleDbType.Integer, 0, "qty"))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_item", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "item", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_orderID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "orderID", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_need_scan", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "need_scan", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_need_scan1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "need_scan", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_processed", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "processed", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_processed1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "processed", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_qty", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "qty", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_qty1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "qty", System.Data.DataRowVersion.Original, Nothing))
        '
        'OleDbDeleteCommand1
        '
        Me.OleDbDeleteCommand1.CommandText = "DELETE FROM checking WHERE (item = ?) AND (orderID = ?) AND (need_scan = ? OR ? I" & _
        "S NULL AND need_scan IS NULL) AND (processed = ? OR ? IS NULL AND processed IS N" & _
        "ULL) AND (qty = ? OR ? IS NULL AND qty IS NULL)"
        Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection2
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_item", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "item", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_orderID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "orderID", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_need_scan", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "need_scan", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_need_scan1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "need_scan", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_processed", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "processed", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_processed1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "processed", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_qty", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "qty", System.Data.DataRowVersion.Original, Nothing))
        Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_qty1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "qty", System.Data.DataRowVersion.Original, Nothing))
        '
        'OleDbConnection2
        '
        Me.OleDbConnection2.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
        "ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\databases\checking.md" & _
        "b"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provid" & _
        "er=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Exte" & _
        "nded Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB" & _
        ":Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet O" & _
        "LEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Databa" & _
        "se=False"
        '
        'Form3
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(552, 357)
        Me.Controls.Add(Me.btncheck)
        Me.Controls.Add(Me.txtitem)
        Me.Controls.Add(Me.btnorder)
        Me.Controls.Add(Me.txtorder)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "Form3"
        Me.Text = "Form3"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim ds As New DataSet

    Private Sub btnorder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnorder.Click
        Dim dblorder As Double = Val(txtorder.Text)
        odaadapter.SelectCommand.CommandText = "SELECT orderID,item,qty,need_scan,processed FROM checking WHERE orderID=" & dblorder
        odaadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        odaadapter.Fill(ds, "dt")
        DataGrid1.DataSource = ds
        DataGrid1.DataMember = "dt"
    End Sub


    Private Sub btncheck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncheck.Click
        Dim dblOrder As Double = Val(txtorder.Text)
        Dim strItem As String = txtitem.Text
        Dim sql, updateSQL As String
        Dim intScan, updateScan As Integer

        Dim dr As DataRow
        dr = ds.Tables(0).Rows.Find(New Object() {dblOrder, strItem})

        If Not IsNothing(dr) Then
            intScan = Convert.ToInt32(dr.Item("need_scan"))
            If intScan < 1 Then
                MsgBox("Too Many")
                Exit Sub
            End If

            updateScan = intScan - 1
            dr.Item("need_scan") = updateScan
        End If

        updateSQL = ("UPDATE checking SET need_scan=" & updateScan & " WHERE orderid=" & dblOrder & " AND item='" & strItem & "'")
        odaadapter.UpdateCommand.CommandText = updateSQL
        odaadapter.Update(ds, "dt")
    End Sub
End Class




0
 

Author Comment

by:tj44
ID: 11795440
Greetings!

I've been working on the code for a while this morning.

I created a new project, and then pasted the code into the form. To get the adapter to see my database,
I right-clicked and re-configured it. Clicking the test button succeeded in the 'adapter wizard', and then it said all of the query commands
were built properly. After this wizard runs it creates a new OleDbConnection. (In this case OleDbConnection1)

I clicked on all of your OleDbCommands in design view:  OleDbInsertCommand1, OleDbSelectCommand1, etc, and changed their connection tab to OleDbConnection1 (instead of your original as 2) so now they should be using the proper connection to see the database

I run the program, and type in the order # and it finds and displays the data properly.  

I type in test , and then I get an error:

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

Additional information: Table doesn't have a primary key."

At this Line:

Dim intScan, updateScan As Integer

        Dim dr As DataRow
        dr = ds.Tables(0).Rows.Find(New Object() {dblOrder, strItem})   <-------- THIS LINE

        If Not IsNothing(dr) Then


The database does have a primary key though. Fields: id (primary) , orderID, item, qty, need_scan, processed
Proof is here: http://www.eponymity.com/test.mdb
;)

Oh, and I'll show you my original code for checking the Order#

Here: http://www.eponymity.com/sample_code.txt

It gives me hope you got this working! I'm grateful for your efforts.
I guess I just need to modify I few things then..I'll continue to wrestle with this =)

-TJ
0
 

Author Comment

by:tj44
ID: 11797734
Ronald!

With some tweaks of the code, my database is now updating!

Thats a huge relief.. now I can start figuring out ways to show a whole updated order on the
screen.

I appreciate all your help!

-TJ
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.NET 2008 Populate DataModel with DataTable 44 28
Help with consolidating excel files using VB.net 2 24
.NET 2008 VB and C# 6 28
vb.net and creating a class 5 17
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question