Solved

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

Posted on 2004-08-11
32
579 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:tj44
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
it returns TRUE
0
 
LVL 25

Expert Comment

by:RonaldBiemans
Comment Utility
That is good

Does your ordergrid show the changed data ?
0
 

Author Comment

by:tj44
Comment Utility
Yes, the ordergrid is showing the updated value
0
 
LVL 25

Expert Comment

by:RonaldBiemans
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:tj44
Comment Utility
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
Comment Utility
Could you also add the MsgBox(ds.HasChanges())

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

Author Comment

by:tj44
Comment Utility
It also says true
0
 

Author Comment

by:tj44
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok Ronald!

I appreciate your help!
0
 

Author Comment

by:tj44
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now