Retrieving and Saving Data in Databases

Quote:

---

Originally Posted by .paul.

i asked on MSDN + i was told that i could synchronize 2 datatables with an oledbTransaction but i can't find a clear example of how to do that

---

I don't know who told you that but they must not have understood the question. What a transaction will allow you to do is save the two DataTables to the database and ensure that they either both succeed or both fail.

I still don't see the need for the second DataTable. If you're making changes to the first DataTable then why don't you just save the changes from the first DataTable to both database tables? By default, calling Update on one DataAdapter would implicitly call AcceptChanges on the DataTable, so there'd be nothing left to save with the second. For that reason, you just have to set AcceptChangesDuringUpdate to False so that the changes are maintained for the second DataAdapter.

Quote:

---

Originally Posted by jmcilhinney

I don't know who told you that but they must not have understood the question. What a transaction will allow you to do is save the two DataTables to the database and ensure that they either both succeed or both fail.

I still don't see the need for the second DataTable. If you're making changes to the first DataTable then why don't you just save the changes from the first DataTable to both database tables? By default, calling Update on one DataAdapter would implicitly call AcceptChanges on the DataTable, so there'd be nothing left to save with the second. For that reason, you just have to set AcceptChangesDuringUpdate to False so that the changes are maintained for the second DataAdapter.

---

that appears to be the answer but i was + still am struggling with it because using 1 datatable with 2 dataadapters works fine for update + delete, but insert will only insert into the 1st database table. it's as if the AcceptChangesDuringUpdate is ignored for the 1st insert command

edit:that was happening... calling Update on the 1st DataAdapter was calling AcceptChanges on the DataTable
the solution is:

vb Code:

Dim tempDt As DataTable = dt.GetChanges
DataAdapter1.update(dt)
If tempDt IsNot Nothing Then
   DataAdapter2.update(tempDt)
End If

thanks for the help:thumb:

Hello jmc

if i need to batch insert into database with transaction support, so that i can roll back, how do i do it?

Quote:

---

Originally Posted by coolcurrent4u

Hello jmc

if i need to batch insert into database with transaction support, so that i can roll back, how do i do it?

---

A DataAdapter does that inherently. A single call to Update will either succeed entirely or fail entirely. If you want to call Update on multiple DataAdapters and/or call ExecuteNonQuery on multiple Commands, then you would want to use a transaction explicitly. In that case, you have two choices:

1. Use the transaction class from your ADO.NET provider, e.g.

vb.net Code:

Using connection As New SqlConnection("connection string here")
    connection.Open()
 
    Dim transaction As SqlTransaction = connection.BeginTransaction()
 
    Using command1 As New SqlCommand("INSERT statement here", connection) With {.Transaction = transaction},
          command2 As New SqlCommand("INSERT statement here", connection) With {.Transaction = transaction},
          adapter1 As New SqlDataAdapter With {.InsertCommand = command1},
          adapter2 As New SqlDataAdapter With {.InsertCommand = command2}
        'Add appropriate parameters command1 and command2 here.
 
        Try
            adapter1.Update(table1)
            adapter2.Update(table2)
 
            'All operations succeeded so commit.
            transaction.Commit()
        Catch ex As Exception
            'At least one operation failed so rollback.
            transaction.Rollback()
        End Try
    End Using
End Using

2. Use a TransactionScope object, e.g.

vb.net Code:

Using transaction As New TransactionScope
    Using connection As New SqlConnection("connection string here")
        connection.Open()
 
        Using command1 As New SqlCommand("INSERT statement here", connection),
              command2 As New SqlCommand("INSERT statement here", connection),
              adapter1 As New SqlDataAdapter With {.InsertCommand = command1},
              adapter2 As New SqlDataAdapter With {.InsertCommand = command2}
            'Add appropriate parameters command1 and command2 here.
 
            Try
                adapter1.Update(table1)
                adapter2.Update(table2)
 
                'All operations succeeded so commit.
                transaction.Complete()
            Catch ex As Exception
                'At least one operation failed.
                'There is no need to rollback explicitly as it will be done implicitly at the End of
                'the Using block that created the TransactionScope if Complete was not called.
            End Try
        End Using
    End Using
End Using

Thanks for posting the connection methods, they have been a great help. However I have run into a issue.

vb Code:

Try
            Using Command As New OleDbCommand("INSERT INTO USERTABLE " _
                + "(UserInitials, Ulastname, UFirstName, UMidInit, UPassw, UsecLevel," _
                + " UDept, Userstatus, UValid,UPhone, SuperID, UserName) VALUES " _
                + "(@UserInit,@ULastName,@UFirstName,@UMidInit,@UPass, " _
                + " @UsecLvl,@UDept,@UStatus,@UValid,@UPhone,@SuperID, @UserName)", cnn)
                '+ "('" + strFirstName.Substring(0, 1) + strMiddle + strLastName.Substring(0, 1) _
                '+ "','" + strLastName + "','" + strFirstName + "','" + strMiddle + "','" _
                '+ strPass + "','" + strSecLvl + "','" + strDept + "','" + strUserStatus + _
                '"','" + strValid + "','" + strPhone + "','" + strSuper + "','" + strUserName + "')", cnn)
 
                Command.Parameters.AddWithValue("@UserInit", strFirstName.Substring(1, 1) + strMiddle + strFirstName.Substring(1, 1))
                Command.Parameters.AddWithValue("@ULastName", strLastName)
                Command.Parameters.AddWithValue("@UFirstName", strFirstName)
                Command.Parameters.AddWithValue("@UMidInit", strMiddle)
                Command.Parameters.AddWithValue("@UsecLvl", strSecLvl)
                Command.Parameters.AddWithValue("@UPass", strPass)
                Command.Parameters.AddWithValue("@Dept", strDept)
                Command.Parameters.AddWithValue("@UStatus", strUserStatus)
                Command.Parameters.AddWithValue("@UValid", strValid)
                Command.Parameters.AddWithValue("@Uphone", strPhone)
                Command.Parameters.AddWithValue("@USuperid", strSuper)
                Command.Parameters.AddWithValue("@UserName", strUserName)
                cnn.Open()
                Command.ExecuteNonQuery()
            End Using
            cnn.Close()
            fAddUSer = True
        Catch ex As Exception
            MsgBox("Couldnt connect.")
            cnn.Close()
            fAddUSer = False
        End Try

When I use the code with the commented out string it works, but when I try to use it with the parameters, I end up with and error message ORA-00936: missing expression. I obiously have something wrong, but since it works with the commented out section, I am a bit stumped.

Any help is greatly appreciated.

try listing your AddWithValue statements in the order you use them in the INSERT statement

also have you noticed your commented out string has 13 values + your parameters only 12?

Thanks for trying to help. I did move them in order and also made sure they all have the right name @..... but I still get the same error.

In regards to the 13 value vs 12, I only found 12, you may have counted the first line as 2 values as I'm combining the last, first and middle into the initials or the person.

Also I'm connecting to Oracle db, and I know they are picking in regards to qhoutatins for strings etc... so that 'Super' would work, while just Super would not... I don't know if the parameters automatically does that or not.

Thanks

@BLUMONKEY:

Oracle doesn't use '@' as a parameter prefix. You need to use ':' as a parameter prefix for Oracle.

Also, rather than using OleDb, I would suggest installing the Oracle-specific ADO.NET provider from Oracle.

As a starting point for John's recommendation to use ODP.Net, you might want to take a look here:

http://www.stanford.edu/dept/itss/do...7/intro003.htm

Specifically:

http://www.stanford.edu/dept/itss/do...2.htm#i1010814

Gary

@Jmc thanks see the code am using already, is there any issue in operformance with it, am using sqlite.net

vb Code:

Dim conn As New SQLiteConnection("Data Source=" & dbPath)
        'conn.SetPassword(dbPassword)
        conn.Open()
        Dim cmd As New SQLiteCommand(conn)
        Try
 
            mytransaction = conn.BeginTransaction()
            '// insert directory //
            For i = 0 To UBound(oDir)
                If oDir(i).Url IsNot Nothing And DirExist(oDir(i).Url) = False Then
                    cmd = New SQLiteCommand("INSERT INTO directory ([name]," & _
                    "[category],[url],[type]) VALUES (@name,@category,@url,@type)")
                    cmd.Parameters.AddWithValue("@name", oDir(i).Name.ToLower)
                    cmd.Parameters.AddWithValue("@category", oDir(i).Category.ToLower)
                    cmd.Parameters.AddWithValue("@type", oDir(i).Type.ToLower)
                    cmd.Parameters.AddWithValue("@url", oDir(i).Url.ToLower)
                    'Debug.Print(cmd.CommandText)
                    cmd.ExecuteNonQuery()
                    With tmpDir
                        .Category = oDir(i).Category.ToLower
                        .Name = oDir(i).Name.ToLower
                        .Type = oDir(i).Type.ToLower
                        .Url = oDir(i).Url.ToLower
                    End With
                    lstDirs.Add(tmpDir)
                End If
            Next
            mytransaction.Commit()
            ListDirectories.AddRange(lstDirs)
            Return True
        Catch ex As Exception
            strLastError = ex.Message
            Debug.Print(strLastError)
            Return False
        Finally
            cmd.Dispose()
            conn.Dispose()
        End Try

Quote:

---

Originally Posted by jmcilhinney

@BLUMONKEY:

Oracle doesn't use '@' as a parameter prefix. You need to use ':' as a parameter prefix for Oracle.

Also, rather than using OleDb, I would suggest installing the Oracle-specific ADO.NET provider from Oracle.

---

Thank you! that did the trick.

In regards to the Oracle Specific ADO.NET (this may be a newbie question too) would that have to be installed on every machine that will be using this app? The reason I am asking is that technology may have to approve something like that, while the oledb is already on the machines.

Thanks again!

Quote:

---

Originally Posted by BLUMONKEY

Thank you! that did the trick.

In regards to the Oracle Specific ADO.NET (this may be a newbie question too) would that have to be installed on every machine that will be using this app? The reason I am asking is that technology may have to approve something like that, while the oledb is already on the machines.

Thanks again!

---

You can install the provider on each machine, or you can just deploy the required DLLs with your EXE.

Thank you again!

If you have the time... what is the biggest difference between oledb and the oracle driver? Will there be a performance gain?

Thank you for all the help you have given me!
Ivar

Quote:

---

Originally Posted by BLUMONKEY

Thank you again!

If you have the time... what is the biggest difference between oledb and the oracle driver? Will there be a performance gain?

Thank you for all the help you have given me!
Ivar

---

That is well beyond the scope of this CodeBank thread. Please keep discussion in the CodeBank specifically to the topic of that thread. If you have questions on other topics then you can start a new thread in the appropriate form. Otherwise CodeBank threads get cluttered and less useful.

No problem... thanks again. :)

JMC you are a Champion............

hello JMC sir can you update this codes with LinQ and LambdA ...
.sorry for this asking because i'm trying to learn this stuffs
thanks........

nice and helpful

worked perfect, thank you

If I have a dgv with some rows being new requiring an Insert query and some requiring an update and others requiring deletion

Is it possible to use insert, update and delete commands all in the same code? How could this be done?

Quote:

---

Originally Posted by AC1982

If I have a dgv with some rows being new requiring an Insert query and some requiring an update and others requiring deletion

Is it possible to use insert, update and delete commands all in the same code? How could this be done?

---

Did you even bother to read my posts in this thread?

Quote:

---

Originally Posted by jmcilhinney

Did you even bother to read my posts in this thread?

---

I have read the posts and I tried implementing the code but I came up with an error. It was due to a mistake in my Update sql query which I have now fixed. Thanks for creating this thread and writing about ado.net parameters on your blog the information has proven very helpful.

Quote:

---

Originally Posted by AC1982

I have read the posts and I tried implementing the code but I came up with an error. It was due to a mistake in my Update sql query which I have now fixed. Thanks for creating this thread and writing about ado.net parameters on your blog the information has proven very helpful.

---

For future reference, if you are having a particular problem with a piece of code, it would make sense to ask a question about it in the main forum, rather than in the CodeBank, which is intended for code samples. By all means reference a thread like this to say that you are using this code, but posting it in the main forum means that everyone who visits the forum are more likely to see it, and you will get more responses. In addition, if you are getting an error/exception in your code, remember to tell people about that in your post, including the stack trace. This is how people will be able to help you.

Gary

Quote:

---

Originally Posted by venuspcs

Okay you where so write about the date....I renamed all my fields in the database and in my code to mmddyy and it started working, well sort of....it is writing to the database now but it is not filling the database with the information from the form, instead all my fields say either: "System.Windows.Forms", "System.Win", or "System.Windows.Forms.TextBox,Text:"

WTH - Here is the complete revised code:

Code:

---

    Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click

        Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Richard Colbert\Documents\Visual Studio 2008\Projects\WindowsApplication1\WindowsApplication1\shenandoah.accdb'")
        Dim adapter As New OleDb.OleDbDataAdapter("SELECT ID, mmddyy, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances FROM Shenandoahs", connection)
        Dim insert As New OleDb.OleDbCommand("INSERT INTO Shenandoahs (mmddyy, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances) VALUES (@mmddyy, @Driver, @Truck, @Customer, @Rate, @Where_From, @Where_To, @Pit_Ticket, @PO_Number, @Yards_Tons, @Description, @QP_Fee, @Fuel, @Expenses, @Advances)", connection)

        insert.Parameters.Add("@mmddyy", OleDb.OleDbType.VarChar, 20, "mmddyy")
        insert.Parameters.Add("@Driver", OleDb.OleDbType.VarChar, 20, "Driver")
        insert.Parameters.Add("@Truck", OleDb.OleDbType.VarChar, 10, "Truck")
        insert.Parameters.Add("@Customer", OleDb.OleDbType.VarChar, 10, "Customer")
        insert.Parameters.Add("@Rate", OleDb.OleDbType.VarChar, 10, "Rate")
        insert.Parameters.Add("@Where_From", OleDb.OleDbType.VarChar, 100, "Where_From")
        insert.Parameters.Add("@Where_To", OleDb.OleDbType.VarChar, 100, "Where_To")
        insert.Parameters.Add("@Pit_Ticket", OleDb.OleDbType.VarChar, 20, "Pit_Ticket")
        insert.Parameters.Add("@PO_Number", OleDb.OleDbType.VarChar, 20, "PO_Number")
        insert.Parameters.Add("@Yards_Tons", OleDb.OleDbType.VarChar, 20, "Yards_Tons")
        insert.Parameters.Add("@Description", OleDb.OleDbType.VarChar, 100, "Description")
        insert.Parameters.Add("@QP_Fee", OleDb.OleDbType.VarChar, 20, "QP_Fee")
        insert.Parameters.Add("@Fuel", OleDb.OleDbType.VarChar, 20, "Fuel")
        insert.Parameters.Add("@Expenses", OleDb.OleDbType.VarChar, 20, "Expenses")
        insert.Parameters.Add("@Advances", OleDb.OleDbType.VarChar, 20, "Advances")

        adapter.InsertCommand = insert
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        Dim table As New DataTable

        'Retrieve the data.
        adapter.FillSchema(table, SchemaType.Source)

        'Add the new rows to the DataTable, e.g.
        Dim row As DataRow = table.NewRow()

        row("mmddyy") = mmddyy
        row("Driver") = Driver
        row("Truck") = Truck
        row("Customer") = Customer
        row("Rate") = Rate
        row("Where_From") = Where_From
        row("Where_To") = Where_To
        row("Pit_Ticket") = Pit_Ticket
        row("PO_Number") = Po_Number
        row("Yards_Tons") = Yards_Tons
        row("Description") = Description
        row("QP_Fee") = QP_Fee
        row("Fuel") = Fuel
        row("Expenses") = Expenses
        row("Advances") = Advances

        table.Rows.Add(row)

        'Save the changes.
        adapter.Update(table)

    End Sub

---

---

You can also just put brackets "[]" around your columns to avoid this. I pretty much do this all the time now.

I am trying to use this for what I assumed was a relatively simple insert into query, taking the values from a DataTable, but I am encountering issues. Any advise if you have time would be gratefully received - I assume by now you are sick of the questions and pleas for help :)

My Post

I have based my code off of the following by jmci which is on page 1 of this thread:
Retrieving multiple records that will be read and discarded. The ExecuteReader method provides read-only, forward-only access to the entire result set:

vb.net Code:

Using connection As New SqlConnection("connection string here")
    Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
                                    connection)
        connection.Open()
 
        Using reader As SqlDataReader = command.ExecuteReader()
            While reader.Read()
                MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.", _
                                              reader("Quantity"), _
                                              reader("Unit"), _
                                              reader("Name")))
            End While
        End Using
    End Using
End Using

Here is my code:

vb.net Code:

Using con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Joey.JPNET.000\Local Settings\Application Data\Temporary Projects\WindowsApplication1\Daycare.accdb;")
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand( _
                       "SELECT * FROM Daycare.Guardians WHERE GuardianName = '" & _
                       txtUsername.Text & "' AND [AccessCode] = '" & txtPassword.Text & "' ", con)
                con.Open()
                Using reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
                    If (reader.Read() = True) Then
                        Form2.Show()
                        Me.Hide()
                    Else
                        MessageBox.Show("Invalid username or password!")
                    End If
                End Using
            End Using
        End Using

I currently have a pair of text boxes and a Go/Cancel button on my form. Ultimately it will be a combo box and a "access code" box where parents select their name and type in a code. Once the person is authenticated, they will then be able to check in/check out their child and/or simply unlock the door. I am having a problem with my VB code talking to the MS Access database. I keep getting the error on the line "Using reader As OleDb.OleDbDataReader = cmd.ExecuteReader()" that it "Could not find file 'C:\Documents and Settings\Joey.JPNET.000\Local Settings\Application Data\Temporary Projects\WindowsApplication1\bin\Debug\Daycare.mdb'." This is because I am using the newer .aacdb format database and not the older .mdb format database. How do I format it such that it connects to the correct database which is actually located in that folder?

Thank you for your help!
~Joey

Very very nice data you've provided.

Code:

---

    Using connection As New SqlConnection("connection string here")
        Using adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                            connection)
            Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
                                        connection)
   
            insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
            insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
            insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
   
            adapter.InsertCommand = insert
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
   
            Dim table As New DataTable
   
            'Retrieve the schema.
            adapter.FillSchema(table, SchemaType.Source)
   
            'Add the new rows to the DataTable, e.g.
            Dim row As DataRow = table.NewRow()
   
            row("Name") = someName
            row("Quantity") = someQuantity
            row("Unit") = someUnit
            table.Rows.Add(row)
   
            'Save the changes.
            adapter.Update(table)
        End Using
    End Using

---

Hi John. I don't understand this

Code:

---

adapter.missingschema........

---

. Isn't there any way, in which i don't use this line of code and insert multiple values? Please guide me.

Quote:

---

Originally Posted by ADQUSIT

Hi John. I don't understand this

Code:

---

adapter.missingschema........

---

. Isn't there any way, in which i don't use this line of code and insert multiple values? Please guide me.

---

The reason for using the MissingSchemaAction property is to ensure that primary key information is added to the DataTable when you call FillSchema. You can build the DataTable schema yourself and not call FillSchema and, either way, you can set the PrimaryKey of the DataTable yourself. If you're going to call Fill or FillSchema though, you may as well let it set the PrimaryKey for you to, which it will do if you set MissingSchemaAction to AddWithKey.

Quote:

---

The reason for using the MissingSchemaAction property is to ensure that primary key information is added to the DataTable when you call FillSchema

---

Do you simply mean that this property automatically adds PK to the DataTable? Am I right?:confused:

And What is Schema John?

Do you need to use a connection string if you link the DB with the server explorer?

Quote:

---

Originally Posted by ADQUSIT

Do you simply mean that this property automatically adds PK to the DataTable? Am I right?:confused:

And What is Schema John?

---

If MissingSchemaAction is set to Add then, when calling Fill, any columns that exist in the result set of the query will be created in the DataTable if they don't already exist, but the PrimaryKey property of the DataTable will not be set. Using AddWithKey will also set the PrimaryKey property to the same column(s) as the database if the query involves only one table and includes that table's PK column(s).

Schema is basically the structure of the database, in this case the number of columns, their names and data types, etc.

Quote:

---

Originally Posted by awweather

Do you need to use a connection string if you link the DB with the server explorer?

---

The only way to connect to a database is with a connection string. If you use the Server/Database Explorer then that's where the connection string comes from. Select a data connection in the Server/Database Explore and open the Properties window and you'll see the connection string for that connection.

How would one save a data from a bound data table to SQL Server CE, using ADO.NET?

I havent worked with Sql Server Compact much at all, but I noticed there there is no equivalent to SQLDbType, so I am having trouble defining parameters.

Quote:

---

Originally Posted by AC1982

How would one save a data from a bound data table to SQL Server CE, using ADO.NET?

I havent worked with Sql Server Compact much at all, but I noticed there there is no equivalent to SQLDbType, so I am having trouble defining parameters.

---

Using SQL Server CE is pretty much exactly as I've shown above, as it is for pretty much any data source when using ADO.NET. You need to add the SQL Server CE ADO.NET provider, which you do by referencing the System.Data.SqlServerCe.dll assembly. The relevant classes can then be found in the System.Data.SqlServerCe namespace. When working with parameters, you use the exact same System.Data.SqlDbType enumeration to specify the data type as you do for SQL Server.

Dear JMC,

I am fairly new to updating, inserting and deleting data from a datagridview.
After reading this topic I have build the following code:

Code:

---

Dim sql As String = String.Format("SELECT        R.Datum,R.Chauffeur_ID,Wagen_ID,R.Beginstand,R.Eindstand,R.Liter,R.Dieselkoeling,R.Stops,R.StopsDachser,R.BrutoUren,R.Maut,R.Eurovignet " & _
                                    "FROM        tblRIT AS R ")


Private connection As New OleDbConnection(_Connectionstring)
Private adapter As New OleDbDataAdapter(sql, connection)
Private table As New DataTable

Private Sub InitialiseDataAdapter()
    ' tblRIT has a combined key [Datum, Chauffeur_ID, Wagen_ID]
    Dim delete As New OleDbCommand("DELETE FROM tblRIT WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)
    Dim insert As New OleDbCommand("INSERT INTO tblRIT (Datum, Chauffeur_ID, Wagen_ID) VALUES (@Datum, @Chauffeur_ID, @Wagen_ID)", Me.connection)
    Dim update As New OleDbCommand("UPDATE tblRIT SET Datum = @Datum, Chauffeur_ID = @Chauffeur_ID, Wagen_ID = @Wagen_ID WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)

    delete.Parameters.Add("@Datum", OleDbType.Date, "Datum")
    delete.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
    delete.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")

    insert.Parameters.Add("@Datum", OleDbType.Date, "Datum")
    insert.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
    insert.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")

    update.Parameters.Add("@Datum", OleDbType.Date, "Datum")
    update.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
    update.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")

    Me.adapter.DeleteCommand = delete
    Me.adapter.InsertCommand = insert
    Me.adapter.UpdateCommand = update

    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
  End Sub

Private Sub GetData()
    'Retrieve the data.
    Me.adapter.Fill(Me.table)
    dgvRitInfo.DataSource = Me.table
    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.
  End Sub

  Private Sub SaveData()
    'Save the changes.
    Me.adapter.Update(Me.table)
  End Sub

---

I call the savedata() and getData() from 2 different buttons.

When I press the button getData the datagridview gets filled with the correct data.

But when I update data and I click the saveData button I get the error message: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
But when I insert data and I click the saveData button I get the error message: "Update requires a valid InsertCommand when passed DataRow collection with modified rows."
But when I delete data and I click the saveData button I get the error message: "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

How should I solve this problem?
I am a little bit lost.

Quote:

---

Originally Posted by bodylojohn

Dear JMC,

I am fairly new to updating, inserting and deleting data from a datagridview.
After reading this topic I have build the following code:

Code:

---

Dim sql As String = String.Format("SELECT        R.Datum,R.Chauffeur_ID,Wagen_ID,R.Beginstand,R.Eindstand,R.Liter,R.Dieselkoeling,R.Stops,R.StopsDachser,R.BrutoUren,R.Maut,R.Eurovignet " & _
                                    "FROM        tblRIT AS R ")


Private connection As New OleDbConnection(_Connectionstring)
Private adapter As New OleDbDataAdapter(sql, connection)
Private table As New DataTable

Private Sub InitialiseDataAdapter()
    ' tblRIT has a combined key [Datum, Chauffeur_ID, Wagen_ID]
    Dim delete As New OleDbCommand("DELETE FROM tblRIT WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)
    Dim insert As New OleDbCommand("INSERT INTO tblRIT (Datum, Chauffeur_ID, Wagen_ID) VALUES (@Datum, @Chauffeur_ID, @Wagen_ID)", Me.connection)
    Dim update As New OleDbCommand("UPDATE tblRIT SET Datum = @Datum, Chauffeur_ID = @Chauffeur_ID, Wagen_ID = @Wagen_ID WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)

    delete.Parameters.Add("@Datum", OleDbType.Date, "Datum")
    delete.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
    delete.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")

    insert.Parameters.Add("@Datum", OleDbType.Date, "Datum")
    insert.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
    insert.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")

    update.Parameters.Add("@Datum", OleDbType.Date, "Datum")
    update.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
    update.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")

    Me.adapter.DeleteCommand = delete
    Me.adapter.InsertCommand = insert
    Me.adapter.UpdateCommand = update

    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
  End Sub

Private Sub GetData()
    'Retrieve the data.
    Me.adapter.Fill(Me.table)
    dgvRitInfo.DataSource = Me.table
    'The table can be used here to display and edit the data.
    'That will most likely involve data-binding but that is not a data access issue.
  End Sub

  Private Sub SaveData()
    'Save the changes.
    Me.adapter.Update(Me.table)
  End Sub

---

I call the savedata() and getData() from 2 different buttons.

When I press the button getData the datagridview gets filled with the correct data.

But when I update data and I click the saveData button I get the error message: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
But when I insert data and I click the saveData button I get the error message: "Update requires a valid InsertCommand when passed DataRow collection with modified rows."
But when I delete data and I click the saveData button I get the error message: "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

How should I solve this problem?
I am a little bit lost.

---

Those errors mean that the InsertCommand, UpdateCommand and DeleteCommand of your data adapter haven't been set, which means that you must not be calling your InitialiseDataAdapter method.

Should I call the InitialiseDataAdapter method on each butten click event?

Quote:

---

Originally Posted by bodylojohn

Should I call the InitialiseDataAdapter method on each butten click event?

---

You should call the method when you need to to do what it does. What does it do? It initialises the data adapter. When do you need to initialise the data adapter? That's when you should call it.

jmcilhinney,

I've read entire article many times and I'm still facing troubles with doing inserts. What I have is a stored procedure in Oracle and doing exactly what you proposed doesn't insert a record. I don't know If I'm declaring parameters wrong or something else, and my error is quite bad - "ORA-00900", which means wrong SQL statement and you can't figue out what is wrong. My debugger shows me nothing so I really can't even tell If my values are passed or not. I only know that procedure executes with no errors from Oracle directly - and It inserts record too.

this is my ORACLE procedure:

Code:

---

CREATE OR REPLACE PROCEDURE MYSCHEMA.UPDATE_TABLE (
CHOOSE_SELECT_IN    IN    NUMBER,
ID_INSTALLATIONS_IN IN MYSCHEMA.INSTALLATIONS.ID_INSTALLATIONS%TYPE,
ID_TABLE1_FK_IN IN MYSCHEMA.INSTALLATIONS.ID_TABLE1_FK%TYPE,
ID_TABLE2_FK_IN IN MYSCHEMA.INSTALLATIONS.ID_TABLE2_FK%TYPE,
DESCRIPTION_IN IN MYSCHEMA.INSTALLATIONS.DESCRIPTION%TYPE)

IS
BEGIN

  CASE  CHOOSE_SELECT_IN
   
  WHEN 1 THEN 
  INSERT INTO MYSCHEMA.INSTALLATIONS (ID_INSTALLATIONS,
                                    ID_TABLE1_FK,
                                    ID_TABLE2_FK,
                                    DESCRIPTION)
        VALUES (ID_INSTALLATIONS_IN,
                ID_TABLE1_FK_IN,
                ID_TABLE2_FK_IN,
                DESCRIPTION_IN);
   
 END CASE;

END UPDATE_TABLE;
/

---

And this is my VB.NET code, starting from your example:

Code:

---

Private Sub InicialiseDataAdapter()

      Dim insert As New OracleCommand("MYSCHEMA.UPDATE_TABLE", Myconn)

        insert.CommandType = CommandType.StoredProcedure

        insert.Parameters.Add(New OracleParameter("CHOOSE_SELECT_IN", OracleDbType.Decimal, 1, ParameterDirection.Input))
        insert.Parameters.Add(New OracleParameter("ID_INSTALLATIONS_IN", OracleDbType.Decimal, 4, "ID_INSTALLATIONS"))
        insert.Parameters.Add(New OracleParameter("ID_TABLE1_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE1_FK"))
        insert.Parameters.Add(New OracleParameter("ID_TABLE2_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE2_FK"))
        insert.Parameters.Add(New OracleParameter("DESCRIPTION_IN", OracleDbType.NVarchar2, 5, "DESCRIPTION"))
   
        Me.adapter.InsertCommand = insert
        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    End Sub

    Private Sub SaveData()
        'Save the changes.
        Me.adapter.Update(table)
    End Sub

---

I initialize Datadapter when It get's filled with data. And when I try to insert using SaveData I get error I mentioned. What is wrong ? Looking at Parameters.Add method you have columns like this (parameterName,OracleDBType, Size, srcColumnName). I don't know what size is for, I tried to set It same as my field size are in DB, but no effect. What I would probably need is to send corresponding values of fields from Datatable to stored procedure parameters, but with exception of CHOOSE_SELECT_IN = 1 which tells procedure what Query needs to be executed. Please help If you know how to solve this.

Quote:

---

Originally Posted by LuckyLuke82

And this is my VB.NET code, starting from your example:

Code:

---

Private Sub InicialiseDataAdapter()

      Dim insert As New OracleCommand("MYSCHEMA.UPDATE_TABLE", Myconn)

        insert.Parameters.Add(New OracleParameter("CHOOSE_SELECT_IN", OracleDbType.Decimal, 1, ParameterDirection.Input))
        insert.Parameters.Add(New OracleParameter("ID_INSTALLATIONS_IN", OracleDbType.Decimal, 4, "ID_INSTALLATIONS"))
        insert.Parameters.Add(New OracleParameter("ID_TABLE1_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE1_FK"))
        insert.Parameters.Add(New OracleParameter("ID_TABLE2_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE2_FK"))
        insert.Parameters.Add(New OracleParameter("DESCRIPTION_IN", OracleDbType.NVarchar2, 5, "DESCRIPTION"))
   
        Me.adapter.InsertCommand = insert
        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    End Sub

    Private Sub SaveData()
        'Save the changes.
        Me.adapter.Update(table)
    End Sub

---

---

I don't use Oracle but I assume that it works the same way in this regard. When you create an ADO.NET command object, the CommandType property is set to Text by default, which means that it expects SQL code in the CommandText property. If you are going to assign the name of the stored procedure to the CommandText property then you need to set the CommandType to StoredProcedure.

Quote:

---

I don't use Oracle but I assume that it works the same way in this regard. When you create an ADO.NET command object, the CommandType property is set to Text by default, which means that it expects SQL code in the CommandText property. If you are going to assign the name of the stored procedure to the CommandText property then you need to set the CommandType to StoredProcedure.

---

I'm sorry, I forgot to add that in my post, I edited It now. I have that allready, but It doesn't work either. I'm not sure what I do wrong. Only thing that comes in my mind is that I fill DataAdapter from different stored procedure. My steps regarding this are (in Load):

Code:

---

Using cmd As New OracleCommand("MY_ANOTHER_STORED_PROCEDURE", Myconn)
             
 cmd.CommandType = CommandType.StoredProcedure

  'Adding parameters for stored procedure here 
  ...

  'Assigning adapter Select command and display data in Datagridview
  adapter.SelectCommand = cmd
  adapter.Fill(table)
  InicialiseDataAdapter()
  My_DGV.DataSource = table
 
'I accept changes because rows get manually changed by me during binding - I set different values for 1 row
  table.AcceptChanges()

End Using

---

EDIT:

Looks like I fixed It. It works now, but I'm not sure for how long. What I did is that I end edit in Datagridview first (My_DGV.EndEdit) and now It saves into DB. However I have another problem in one different Datagridview- I need to save changes only for rows where certain cell has value and ignore rows other even If It's added row, or modified - how can I do that ? Loop through Datagridview rows or datatable rows ?

Quote:

---

Originally Posted by LuckyLuke82

However I have another problem

---

This thread is not intended to be a place that anyone can ask any question related to ADO.NET. You should start a new thread dedicated to this new specific topic.

Ok, no problem.