Bugger ME!!! DataAdapter Update help...

[Mothra]

I am having megga trouble with this bit of code and I just can not see why it's not working. Can someone PLEASE take a look and open my eyes so I may see the light?!?

 

The code is as follows:

 

'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...
                    ' It's 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 I'm getting comes when I call the DataAdapter.Update method and is an...

 

"Invalid argument or procedure call"

 

I just can't see what's not right...

 

Thanks!

[Robby]

Where are you setting the Update (statement) command to the OleDbCommand ?

[Mothra]

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

 

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

 

           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, it's just not adding the record to the database. Sorry about that...

[Robby]

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 ?

[jfackler]

Working with the dataadapter you don't 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.

[jfackler]

 
 
       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.

[jfackler]

Give this a try....should work.

 

   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")

[Mothra]

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 I've got...

 

       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...

[jfackler]

"procedure call or argument invalid"

 

When I've gotten that in the past, it's a problem with a column name or a data type for the field.

[jfackler]

When you step through with the debug, where is the error occuring?

[Mothra]

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 don't get any data entered into my DB...

[jfackler]

Plug this in above the update call and see if you get a value.

 

 
debug.writeline(CStr(dsNewWO.Tables("MADB").Rows(0).Item(0)))

[jfackler]

It's 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

[Mothra]

Woke you up?!?

 

Now I'm starting to feel bad! As it is 11:30 PM out here on the west coast, I'll be giving that a try tomorrow FIRST THING! Please, please get some sleep.

[jfackler]

oK,

Now it's 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 aren't

allowed to do that. Look at the form below, it works but if I don't

change that textbox field each time to something new, it throws

the error you're getting. Check the design of your database and

make sure your key is autoincremeted or that the data you are

inputing is unique.

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