Bugger ME!!! DataAdapter Update help...

Mothra

Mothra

Well-known member
Joined
Nov 26, 2002
Messages
173
Location
Fresno, California
I am having megga trouble with this bit of code and I just can not see why its not working. Can someone PLEASE take a look and open my eyes so I may see the light?!?

The code is as follows:

Code: 
Add work order to database
    Public Function AddToDB(ByVal wo As clsWorkOrder)
        Declarations
        Dim cn As OleDbConnection
        Dim cnStr As String
        Dim strSQL As String
        Dim dsNewWO As DataSet
        Dim daNewWO As OleDbDataAdapter
        Dim drNew As DataRow

        Set the connection string value
        cnStr = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\MTRS\DB\MADB.mdb"

        strSQL = "SELECT * FROM MADB"

        Try Add the work order information to the database

            Connect to the database
            cn = New OleDbConnection(cnStr)
            cn.Open()
            Create & Fill the data adapter
            daNewWO = New OleDbDataAdapter(strSQL, cn)
            daNewWO.SelectCommand = New OleDbCommand(strSQL, cn)
            Dim autoGen As OleDbCommandBuilder = New OleDbCommandBuilder(daNewWO)
            dsNewWO = New DataSet("MADB")
            daNewWO.FillSchema(dsNewWO, SchemaType.Source, "MADB")
            daNewWO.Fill(dsNewWO)
            drNew = dsNewWO.Tables("MADB").NewRow

            With drNew
                .BeginEdit()
                
                     Code to update my DataRow is here...
                      Its far to long to post and it works anyway...

                .EndEdit()
            End With
            Add the new row to the table
            dsNewWO.Tables("MADB").Rows.Add(drNew)
            Update the data in the database
            daNewWO.Update(dsNewWO)
            Inform the user that the work order was added
            MsgBox("Work Order # " & mJobNumber & Chr(13) & "has been added to the database", _
                    MsgBoxStyle.Information, "New Work Order...")

        Catch ex As Exception
            MsgBox(Err.Description & " " & Err.Number & Chr(13) & ex.Message)
            Exit Function

        Finally
            Close the database connection and data elements
            cn.Close()
            cn = Nothing
            daNewWO = Nothing
            dsNewWO = Nothing
            drNew = Nothing

        End Try

    End Function

The error Im getting comes when I call the DataAdapter.Update method and is an...

"Invalid argument or procedure call"

I just cant see whats not right...

Thanks!
 
Got the update command...still no workie...

I added the INSERT command (not the update since Im trying to add a new record to the DB...) just before calling the .Update method and still no dice...

Code: 
            daNewWO.InsertCommand = autoGen.GetInsertCommand
            Update the data in the database
            daNewWO.Update(dsNewWO)

I also must ammend my earlier statement, I am NOT getting an error, its just not adding the record to the database. Sorry about that...
 
you are passing wo As clsWorkOrder but not using it. ??

Is this entire routine (AddToDB) supposed to isert/update the database? If so, is it changes to a Datagrid, Dataset or ?
 
Working with the dataadapter you dont need to open and close the connection, unless you have a need otherwise to keep the connection open, the dataadapter opens it as needed and returns it to the state it was in when it updates/fills.
 
Code: 
        Dim sconnstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source =" & strdatasource & ""
        Dim oledb As New OleDbConnection(sconnstring)
        oledbdataadapter2 = New OleDbDataAdapter("SELECT * FROM Employees ORDER BY Somefield", oledb)
        Dim olecmdbuilder2 As OleDbCommandBuilder
        olecmdbuilder2 = New OleDbCommandBuilder(oledbdataadapter2)
        oledbdataadapter2.Fill(dataset2, "Employees")

         Dim NewRow As DataRow = dataset2.Tables("employees").NewRow
   

        NewRow("Colname") = "somedata"
        


        dataset2.Tables("Employees").Rows.Add(NewRow)
        oledbdataadapter2.Update(dataset2, "Employees")

Works for me.
 
Give this a try....should work.

Code: 
   Dim cnStr As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\MTRS\DB\MADB.mdb"
        Dim oledb As New OleDbConnection(cnstr)
        oledbdataadapter= New OleDbDataAdapter("SELECT * FROM MADB"oledb)
        Dim olecmdbuilder As OleDbCommandBuilder
        olecmdbuilder = New OleDbCommandBuilder(oledbdataadapter)
         dim dsNewWO as New DataSet()
        oledbdataadapter.Fill(dsNewWO, "MADB")
         Dim NewRow As DataRow = dsNewWO.Tables("MADB").NewRow
        NewRow("ColumnName") = somestuff.Text
        
        dsNewWO.Tables("MADB").Rows.Add(NewRow)
        oledbdataadapter.Update(dsNewWO, "MADB")
 
Still nothing...

I tried that last bit of code (almost) word for word and still get the "Procedure call or argument invalid" error. Here is what Ive got...

Code: 
        cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\MTRS\DB\MADB.mdb"

        strSQL = "SELECT * FROM MADB"

        Try Add the work order information to the database

            Dim oledb As New OleDbConnection(cnStr)
            Dim oledbAdapter As OleDbDataAdapter = New OleDbDataAdapter(strSQL, oledb)
            Dim oledbCmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(oledbAdapter)
            Dim dsNewWO As New DataSet()
            oledbAdapter.Fill(dsNewWO, "MADB")
            Dim NewRow As DataRow = dsNewWO.Tables("MADB").NewRow

            Input the data into the new DataRow
            NewRow("myColumnName") = wo.myProperty

            Add the newrow to the DataSet and update the data source
            dsNewWO.Tables("MADB").Rows.Add(NewRow)
            oledbAdapter.Update(dsNewWO, "MADB")

The thing that really bothers me is that the code I posted origionally worked just fine and then, all of the sudden, stopped working. Talk about strange...
 
"procedure call or argument invalid"

When Ive gotten that in the past, its a problem with a column name or a data type for the field.
 
Step with the error...

I get the error on the DataAdapter.Update call. If I create a DataTable variable and add the new row to it instead of the DataSet, I do not get the error but, I still dont get any data entered into my DB...
 
Plug this in above the update call and see if you get a value.

Code: 
debug.writeline(CStr(dsNewWO.Tables("MADB").Rows(0).Item(0)))
 
Its 2:30 am here in the states. Just occured to me what is probably the problem....woke me up. Sick.
In the IDE, the query builder allows you to do the short cut, SELECT*. But if you read the actual select string, it includes each of the individual fields (columns).
Try changing the select statement to include each of the columns you want to include by name (i.e. SELECT Mycolumn0name, Mycolumn1name, Mycolumn2name FROM MADB).
Also, make sure your data types match and that you have write to authority on the data base.
Then run it again, and check that debug line above, if there is data in the dataset, there should be some value in the first
column ("Item(0)") of the first row ("rows(0)") of your dataset.
Now this problems keeping me awake.....
Let me know how it comes out so I can sleep.

Jon
 
Woke you up?!?

Now Im starting to feel bad! As it is 11:30 PM out here on the west coast, Ill be giving that a try tomorrow FIRST THING! Please, please get some sleep.
 
oK,
Now its the light of day and all things are clearer.
I think I found your problem....I hope.
You must let me know when this is solved so I can stop messing with it.
I think your primary key field is trying to be duplicated...you arent
allowed to do that. Look at the form below, it works but if I dont
change that textbox field each time to something new, it throws
the error youre getting. Check the design of your database and
make sure your key is autoincremeted or that the data you are
inputing is unique.
Code: 
 Imports System.Data.OleDb
Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim oledbdataadapter1 As OleDbDataAdapter
    Dim dataset1 As New DataSet()
#Region " Windows Form Designer generated code "
      
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Jon\My Documents\MADB.mdb"
        Dim strSQL As String = "SELECT FirstColumn, SecondColumn, ThirdColumn FROM MADB"
        Dim dsNewWO As New DataSet()      
        Dim oledb As New OleDbConnection(cnStr)
        Dim oledbAdapter1 As OleDbDataAdapter
        oledbAdapter1 = New OleDbDataAdapter(strSQL, oledb)
        Dim oledbCmdBuilder1 As OleDbCommandBuilder
        oledbCmdBuilder1 = New OleDbCommandBuilder(oledbAdapter1)
        oledbAdapter1.Fill(dsNewWO, "MADB")
        Debug.WriteLine(CStr(dsNewWO.Tables("MADB").Rows(0).Item(0)))

        TextBox1.Text = CStr(dsNewWO.Tables("MADB").Rows(0).Item(0))
        Dim NewRow As DataRow = dsNewWO.Tables("MADB").NewRow

        Input the data into the new DataRow
        NewRow("FirstColumn") = TextBox2.Text Since this is the primary key in my DB, it has
        to have a new value each time IF IT DOESNT I GET THE ERROR YOU ARE GETTING!!!

        Add the newrow to the DataSet and update the data source
        dsNewWO.Tables("MADB").Rows.Add(NewRow)
        oledbAdapter1.Update(dsNewWO, "MADB")

    End Sub
End Class
 
Back
Top