# VBForums CodeBank > CodeBank - Visual Basic .NET >  Retrieving and Saving Data in Databases

## jmcilhinney

C# version here.

There is all sorts of literature on this topic but people still keep asking the same questions.  I'm creating this thread so I can send people here to look at some example code that I know will demonstrate all the principles they need.  These principles can be extended or adjusted and applied to any data access situation.  This code uses members of the System.Data.SqlClient namespace.  If you're not using SQL Server then it's a simple matter of switching to the corresponding types of the appropriate namespace for your data source.  For example, if you're using an Access database then you'd use an OleDb.OleDbConnection rather than an SqlClient.SqlConnection.

*Retrieving a single value.*  The ExecuteScalar method returns the value from the first column of the first row of the query's result set:
vb.net Code:
Using connection As New SqlConnection("connection string here")
    Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", _
                                    connection)
        connection.Open()
         Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
         'Use totalQuantity here.
    End Using
End Using
The following example is a repeat of the previous one but including a WHERE clause to filter the results. The same principle applies to filtering any query:
vb.net Code:
Using connection As New SqlConnection("connection string here")
    Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem WHERE SupplierId = @SupplierId", _
                                    connection)
        command.Parameters.Add("@SupplierId", SqlDbType.Int).Value = supplierId
        connection.Open()
         Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
         'Use totalQuantity here.
    End Using
End Using
*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
*Retrieving multiple records for display that will not be updated.*  The DataTable.Load method will populate a DataTable with the result set exposed by a DataReader:
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()
            Dim table As New DataTable
             table.Load(reader)
             'The table can be used here to display the data.
            'That will most likely be done via data-binding but that is NOT a data access issue.
        End Using
    End Using
End Using
Note that DataTable.Load is new in .NET 2.0.  I have provided code here to populate a DataTable from a DataReader in .NET 1.x

*Retrieving multiple records for display and editing, then saving the changes.*  The DataAdapter.Fill method populates a DataTable with the contents of the result set of a query.  The DataAdapter.Update method saves the changes in a DataTable in accordance with the SQL statements contained in the DeleteCommand, InsertCommand and UpdateCommand properties:
vb.net Code:
Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                      connection)
Private table As New DataTable
 Private Sub InitialiseDataAdapter()
    Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
    Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
    Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)
     delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
     insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
     update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    update.Parameters.Add("@ID", SqlDbType.Int, 4, "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)
     '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
Note that if your query involves only one table and it has a primary key then you can take the easy option and use a CommandBuilder instead of creating the non-query commands yourself:
vb.net Code:
Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                      connection)
Private builder As New SqlCommandBuilder(adapter)
Private table As New DataTable
 Private Sub InitialiseDataAdapter()
    Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
 Private Sub GetData()
    'Retrieve the data.
    Me.adapter.Fill(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
*Saving changes directly to one or more records in the database.*  The Command.ExecuteNonQuery method will execute any SQL statement and not return a result set.  It can be used to execute a query but you'd never use it for that unless you were populating a view or temp table.  Usually you'd use ExecuteNonQuery to execute a DELETE, INSERT or UPDATE command:
vb.net Code:
Using connection As New SqlConnection("connection string here")
    Using command As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
                                    connection)
        command.Parameters.AddWithValue("@Name", someName)
        command.Parameters.AddWithValue("@Quantity", someQuantity)
        command.Parameters.AddWithValue("@Unit", someUnit)
         connection.Open()
         command.ExecuteNonQuery()
    End Using
End Using
Note that I have not provided code for disposing objects or catching exceptions.  Those are general topics that do not relate specifically to data access so they should be learned elsewhere.

Note also the DataAdapter.Fill, DataAdapter.Update and Command.ExecuteNonQuery methods are all functions.  All three return an Integer that contains the number of records that were either retrieved (Fill) or saved (Update, ExecuteNonQuery).

Finally, this code uses all the "old style" data access types.  In .NET 2.0 I suggest creating a Data Source to generate a typed DataSet and TableAdapters.  The principles there are basically the same except that you have to write less code.  All SQL statements are added via the DataSet designer and all you really have to do is create TableAdapters and call their methods.  Even the connections are hidden within the TableAdapters so it's all much easier.  Understanding what's above should help you understand how TableAdapters work internally though.

EDIT: Note that I have updated the code examples slightly in this post and post #3 to more accurately reflect real usage situations, particularly using a DataAdapter to get and save data in separate methods rather than in the same method.

----------


## jmcilhinney

To find the appropriate connection string for your ADO.NET provider visit www.connectionstrings.com.

----------


## jmcilhinney

*Inserting multiple records into a table.*  This situation is much like the fourth example above, except you don't need to retrieve any data to start with and you obviously don't need the UpdateCommand and DeleteCommand:
vb.net 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 UsingEnd Using
I should also point out that you don't actually need the SelectCommand and the FillSchema call either.  You can simply build the DataTable schema yourself if you like.

Here's the above code adapted to use the SqlBulkCopy class, which allows for more efficient insertion of large amounts of data into SQL Server:
vb.net Code:
Using connection As New SqlConnection("connection string here"), _      adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _                                        connection), _      bulkCopy As New SqlBulkCopy(connection)    bulkCopy.DestinationTableName = "StockItem"     'This is unnecessary in this case because the column positions match but is included for completeness.    bulkCopy.ColumnMappings.Add("Name", "Name")    bulkCopy.ColumnMappings.Add("Quantity", "Quantity")    bulkCopy.ColumnMappings.Add("Unit", "Unit")     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.    bulkCopy.WriteToServer(table)End Using

----------


## bishnoi

hi jmcilhinney,
Its a nice documentation.
(This is my first post.)
Still i would request a good class/module for db connection in access.
Particularly i need to access ms access though local network.what would be the best class code. 
I appreciate your view. 
I think you will help me and people like me. I m trying to work in .net.i have done  little programmings in vb6 and .net 05. i have been on this site for last 4-6 hrs. got excellent helping examples and links.
Thanks again.

----------


## jmcilhinney

> hi jmcilhinney,
> Its a nice documentation.
> (This is my first post.)
> Still i would request a good class/module for db connection in access.
> Particularly i need to access ms access though local network.what would be the best class code. 
> I appreciate your view. 
> I think you will help me and people like me. I m trying to work in .net.i have done  little programmings in vb6 and .net 05. i have been on this site for last 4-6 hrs. got excellent helping examples and links.
> Thanks again.


The code is basically no different for Access.  As I said in my first post:


> For example, if you're using an Access database then you'd use an OleDb.OleDbConnection rather than an SqlClient.SqlConnection.

----------


## bizzy_e23

Hi, I found your compilation verry usefull in my project... Thank you... Since I'm a new user of VB Express Edition 2008, can you help me in my problem regarding retrieving a recordset and afterwards editing one of its field.

Additional Info:

in the first scenario, im going to create a new data, and because this is a Time keeping System, First Im just going to get the Time IN and leave the TimeOUT Column blank.. what i want to do is i want to retrive this recordset and update the TimeOUT column. Thanks.

Table:  TIME_IN_OUT

EmpNo      TimeIN      TimeOUT

00001      8:30 AM     N/A

----------


## jmcilhinney

> Hi, I found your compilation verry usefull in my project... Thank you... Since I'm a new user of VB Express Edition 2008, can you help me in my problem regarding retrieving a recordset and afterwards editing one of its field.
> 
> Additional Info:
> 
> in the first scenario, im going to create a new data, and because this is a Time keeping System, First Im just going to get the Time IN and leave the TimeOUT Column blank.. what i want to do is i want to retrive this recordset and update the TimeOUT column. Thanks.
> 
> Table:  TIME_IN_OUT
> 
> EmpNo      TimeIN      TimeOUT
> ...


This thread already shows you how to retrieve the data from the database and save it back again.  Editing the data in between is beyond the scope of this thread.  Start your own thread in the VB.NET forum.

----------


## venuspcs

Okay I am using the code below:



```
        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, Date, 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 (Date, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances) VALUES (@Date, @Driver, @Truck, @Customer, @Rate, @Where_From, @Where_To, @Pit_Ticket, @PO_Number, @Yards_Tons, @Description, @QP_Fee, @Fuel, @Expenses, @Advances)", connection)

        insert.Parameters.Add("@Date", 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("Date") = 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)
```

I show no errors in VS.net but when I run the debug and click on the Submit button I get the following error:

Syntax error in INSERT INTO statement.

I have been trying to get this to work for like HOURS. Please help!

----------


## jmcilhinney

> Okay I am using the code below:
> 
> 
> 
> ```
>         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, Date, 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 (Date, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances) VALUES (@Date, @Driver, @Truck, @Customer, @Rate, @Where_From, @Where_To, @Pit_Ticket, @PO_Number, @Yards_Tons, @Description, @QP_Fee, @Fuel, @Expenses, @Advances)", connection)
> 
> ...


When you get a syntax error in what seems to be valid SQL code it's often because one of your column names is a reserved word.  In your case the most likely culprit is Date.  Wrap the identifier in brackets, i.e. [Date], to force it to be interpreted correctly.

----------


## 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:



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

----------


## MaslowB

row("mmddyy") = mmddyy
row("Driver") = Driver

So mmddyy in this instance would be a text box? you need to do this instead if that's the case:

row("mmddyy")=mmddyy.text

----------


## venuspcs

Thanks so much MaslowB and jmcilhinney that code is working now.

Now I have another question....I need it to select which table to write to based on the user selection in the "Company" field on my form. There are three divisions to this company and thus three different databases. Those divisions are: "Shenandoah", "Shenandoahs_Covered_Wagon" and "Shenandoahs_Heavy_Haul" each with tables in the db named the same. All fields submitted to the tables are the same, it just needs to know which table to submit (input) to.

----------


## MaslowB

Dim insert As New OleDb.OleDbCommand("INSERT INTO "+company.text+"(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)

How about that?

----------


## venuspcs

That would probably work a lot easier and shorten my code a tad, but I figured it out using this:

If Company.Text = "Shenandoah" Then

original code here

ElseIf Company.Text = "Shenandoahs_Covered_Wagon" Then

revised code here

ElseIf Company.Text = "Shenandoahs_Heavy_Haul" Then

more revised code here

End If

Not nearly as pretty or as short but it worked flawlessly. I will remember your suggestion and when I go through and revise (shorten my code) I will implement it.

----------


## MaslowB

mark your thread resolved, rate those users that helped =)

----------


## venuspcs

Okay I rated both of you who helped me going to mark my original thread on this issue resolved....I have posted a new thread with a new issue if you guys can help again it would be greatly appreciated. Thanks!

----------


## jmcilhinney

Note that I have updated the code examples slightly in posts #1 and #3 to more accurately reflect real usage situations, particularly using a DataAdapter to get and save data in separate methods rather than in the same method.

----------


## cuterita7

vb Code:
Using connection As New SqlConnection("connection string here") 
Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", 
_connection)  
connection.Open()
Dim totalQuantity As Double = CDbl(command.ExecuteScalar())  
'Use totalQuantity here. 
End Using

Why in this code snippet you don't close the connection?and how do you handle a exception(database exception)?

thanks

----------


## jmcilhinney

> vb Code:
> Using connection As New SqlConnection("connection string here") 
> Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", 
> _connection)  
> connection.Open()
> Dim totalQuantity As Double = CDbl(command.ExecuteScalar())  
> 'Use totalQuantity here. 
> End Using
> 
> ...


I don't close the connection explicitly because it gets disposed at the End Using line, which implicitly closes it.  I have recently changed the code to make use of a Using block.  My original code did close the connection explicitly.

As I said in my first post, I haven't included any exception handling because that's a separate topic that isn't related to data access specifically.  In a real situation, yes, you would provide appropriate exception handling.  For this example I didn't want to draw attention away from the actual topic of the thread, which is the data access itself.  In a real situation you might put the exception handler inside the Using block(s) or outside, depending on the circumstances and your preference.  One of the reasons you should use Using blocks is that they ensure that an object is disposed even if an exception is thrown, so you don't have to do anything extra to dispose the data access objects.
vb.net Code:
Try
    Using connection As New SqlConnection("connection string here")
        Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", _
                                        connection)
            connection.Open()
             Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
             'Use totalQuantity here.
        End Using
    End Using
Catch ex As SqlException
    For Each er As SqlError In ex.Errors
        MessageBox.Show(er.Message)
    Next
End Try

----------


## LuxCoder

@JM

Hi i tried your codes to retrieve and update data in my textbox which is bound to a field in database. When i launch the application the textbox does not display the data?? can you tell me what could be possibly wrong??


vb Code:
Private connection As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")    Private adapter As New SqlClient.SqlDataAdapter("SELECT kmsrun FROM machineprofile", connection)    Private table As New DataTablePrivate Sub InitialiseDataAdapter()Dim update As New SqlClient.SqlCommand("UPDATE machineprofile SET kmsrun = '" & TextBox10.Text & "' WHERE MachineID = '" & TextBox1.Text & "'", Me.connection)Me.adapter.UpdateCommand = update        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey Private Sub GetData()               Me.adapter.Fill(Me.table) End Sub Private Sub SaveData()               Me.adapter.Update(Me.table)End Sub

----------


## LuxCoder

anyone plz help!! y is this not working?

----------


## LuxCoder

I tried this piece of code to update the datarow where value of the MachineID is textbox1.text


vb Code:
Private Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click        Using connection As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")            Using command As New SqlClient.SqlCommand("INSERT INTO MachineProfile ([MachineID], [Category], [kmsrun]) VALUES (@MachineID, @Category, @kmsrun)  where [MachineID] = '" & TextBox1.Text & "'", connection)                command.Parameters.AddWithValue("@MachineID", TextBox1.Text)                command.Parameters.AddWithValue("@Category", ComboBox2.Text)                command.Parameters.AddWithValue("@kmsrun", TextBox10.Text)                connection.Open()                command.ExecuteNonQuery()             End Using        End Using    End Sub

And i am getting this error:




> Incorrect syntax near the keyword 'where'.


What's wrong??

----------


## jmcilhinney

@LuxCoder

With regards to post #20, are you actually calling those methods at all, i.e. are you actually getting any data?  Even if you are, are you binding your DataTable to your TextBox?  If you don't retrieve data and put it into the TextBox then the TextBox won't show any data.

With regards to post #21, please do not EVER bump a thread like that but especially not someone else's CodeBank thread.  Please don't clutter up the useful information with pointless bumps that are against forum rules.

With regards to post #22, you've got a syntax error in your SQL because you've got an INSERT statement with a WHERE clause.  Have you read the information provided in this thread?  Look at the syntax used for INSERT and UPDATE statements.  INSERT is used to add a new record while UPDATE is used to edit an existing record.  Work out which one it is that you want to do and use the appropriate SQL syntax.

----------


## riechan

Question, in this line of code:



```
dbAdpPatronType.Fill(dbDsetPatron, "PatronType")
```

Is the syntax like this:
[adaptername].fill([datasetname],[datatablename?])

----------


## jmcilhinney

> Question, in this line of code:
> 
> 
> 
> ```
> dbAdpPatronType.Fill(dbDsetPatron, "PatronType")
> ```
> 
> Is the syntax like this:
> [adaptername].fill([datasetname],[datatablename?])


Strictly, no.  It's:
vb.net Code:
dataAdapter.Fill(dataSet, "DataTableName")
"Name" implies a string containing a name and in that case the adapter and the data set are not the names of objects, but rather references to those objects.

----------


## riechan

So then:



```
    Dim dbConn As New SqlConnection
    Dim dbAdp As SqlDataAdapter
    Dim dbDset As DataSet

        dbConn.ConnectionString = "Integrated Security=SSPI; Initial Catalog=DB; Persist Security Info=False;"
        dbConn.Open()

        dbAdp = New SqlClient.SqlDataAdapter("SELECT * FROM table", dbConn)
        dbDset = New DataSet
        dbAdp.Fill(dbDset, "Table")
```

"Table" in the dbAdp.fill creates a datatable named "Table", using the results of the query in dbAdp, and places the datatable into the dbDset, right? Meaning, it automatically creates a datatable name "Table" in the dbDset?

----------


## jmcilhinney

> So then:
> 
> 
> 
> ```
>     Dim dbConn As New SqlConnection
>     Dim dbAdp As SqlDataAdapter
>     Dim dbDset As DataSet
> 
> ...


That's correct.

----------


## riechan

So then, how can I clear the contents of a single datatable in a dataset? I believe the dataset.clear() method clears the whole dataset, is there something like a datatable.clear()? And what would be the syntax for clearing a specific datatable, if there is such a method in .net?

----------


## jmcilhinney

> So then, how can I clear the contents of a single datatable in a dataset? I believe the dataset.clear() method clears the whole dataset, is there something like a datatable.clear()? And what would be the syntax for clearing a specific datatable, if there is such a method in .net?


Please don't clutter up Codebank threads with every question you may have that's remotely related to the topic.  This stuff belongs in a new thread of your own in the VB.NET forum.  Only post to a CodeBank thread if you have a question that relates specifically to the information originally posted; otherwise CodeBank threads become less useful because finding relevant information is harder.

As for the question, have you read the documentation?  That should ALWAYS be your first source of information.  If you want to do something with a DataTable then read the documentation for the DataTable class.  That will tell you definitively whether there's a DataTable.Clear method.

----------


## dethredic

I am having some problems:


vb Code:
Using connection As New OleDbConnection(myConnectionString)            Using adapter As New OleDbDataAdapter("SELECT NameFirst, NameLast FROM Employees", _                                              connection)                Dim insert As New OleDbCommand("INSERT INTO Employees (NameFirst, NameLast) VALUES (@NameFirst, @NameLast)", _                                           connection)                adapter.InsertCommand = insert                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey                 Dim table As New DataTable                adapter.FillSchema(table, SchemaType.Source)                Dim row As DataRow = table.NewRow()                 row("NameFirst") = aName(0)                row("NameLast") = aName(1)                table.Rows.Add(row)                 adapter.Update(table)            End Using        End Using

I am getting an error with this line:
adapter.Update(table)
error:
No value given for one or more required parameters.

----------


## si_the_geek

If code from an example doesn't work for you, the first thing you should do is check that you haven't missed anything (either code, or instructions of what to do).

Take another at the original code (post #3), you are missing lines like _insert.Parameters.Add_

----------


## jlbantang

Hi team, I find the post extremely useful not only for beginners but also to experienced developer. I am thinking of what will be the difference when doing a parameterized INSERT,UPDATE,DELETE tsql command coded in the application(your samples above) and having split the tsql command in the server. In term of speed, security and maintenance which one will you prefer?

----------


## cops

useful

----------


## Dsnowdon

When i have enterd your code i have got a eroor on Connection() Saying "EXpression i not a method" any help on how to fix please

this is the code

vb Code:
Using connection As New SqlConnection("server=*****;Port=3306; User ID=*****; password=****; database=*****")             Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';")                 connection()                connection.Open()                Dim Online As Double = CDbl(command.ExecuteScalar())             End Using        End Using

----------


## gep13

Hey,

Using port 3306 would suggest that you are connecting to a MySql Database.  Is that correct?

Gary

----------


## jmcilhinney

> When i have enterd your code i have got a eroor on Connection() Saying "EXpression i not a method" any help on how to fix please


As I said in post #1:


> If you're not using SQL Server then it's a simple matter of switching to the corresponding types of the appropriate namespace for your data source. For example, if you're using an Access database then you'd use an OleDb.OleDbConnection rather than an SqlClient.SqlConnection.


If, as Gary suggests, you are using MySQL, then you can't use a SqlConnection, which is for SQL Server.  In that case, you should download and install Connector/Net from the MySQL web site, add a reference to MySql.Data.dll to your project and then use a MySql.Data.MySqlConnection, etc.

Also, this line:

```
connection()
```

appears to be unnecessary and, in fact, invalid syntax.

Also, your SQL code retrieves every field from a single row but then you use ExecuteScalar, which gets only a single value.  If you only want a single value then your SQL code should reflect that.  If you want an entire record then you shouldn't be using ExecuteScalar.

Finally, don't use string concatenation to insert variables into SQL code.  Many examples demonstrate how to use parameters to insert variables.  Do it that way.

----------


## Dsnowdon

so i should delete the conection() Stetment

----------


## gep13

The first thing you should do is answer the questions that have been asked of you.

What sort of Database are you using? MySql? SQL Server? Or what?

Gary

----------


## Dsnowdon

Or Sorry MySQL idont know weather it makes it diffrent but its online MySQl.
i am also only trying to recive One Please of data From the table users, i want "Online" Were username = TheUsersName and for password

thanks dale

----------


## Dsnowdon

I dont kn

----------


## gep13

In which case, go back and read jmcilhinney's post, #36.  It tells you exactly what you need to do.

Download MySql Connector for .Net.
Reference the assembly in your project.
Update the Imports statements at the top of your code file.
Change all SqlConnection to MySqlConnection, all SqlCommand to MySqlCommand etc.
Update your code to use Parameters.

Gary

----------


## gep13

> I dont kn


Hold on.  Are you saying you are, or aren't using MySql?  Your posts seems contradictory?!?!

Gary

----------


## Dsnowdon

No i am uing MYSQL and im using 
Imports MySql.Data.MySqlClient
Imports System.Data.SqlClient
at the top of my code

I need help with this statment i have changed my code and i get the error on

vb Code:
Online = CDbl(command.ExecuteScalar())

This is the whole code


vb Code:
Dim MySQLConnection As MySqlConnection        MySqlConnection = New MySqlConnection        MySqlConnection.ConnectionString = "server=dddd.net;Port=3306; User ID=dddddd; password=ddddd; database=dddddd"        MySqlConnection.Open()        Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';")             Online = CDbl(command.ExecuteScalar())         End Using

Sorry to be a pain

Thanks dale

----------


## gep13

Hey,

Since you are using MySql, you will not need this:



```
Imports System.Data.SqlClient
```

Okay, you are going to have to help us here...

What is the error that you are getting?

Gary

----------


## gep13

I am going to guess that is has to do with this:



```
Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';")
```

You are still using SqlCommand.

Also, you haven't still not using Parameters.  I HIGHLY recommend that you start using them.  There is a link in my signature explaining why you should use them.

Gary

----------


## Dsnowdon

OK so what should i change SQLCommand to?

----------


## gep13

> OK so what should i change SQLCommand to?


Did you read post #41?  :Smilie:

----------


## Dsnowdon

OK i have changed that but when it runs it still get a error with




The Error is


```
System.InvalidOperationException was unhandled
  Message=Connection must be valid and open.
  Source=MySql.Data
```

----------


## Dsnowdon

It is connected becaue i have or the user has already loged in before this code is ran

----------


## gep13

Hey,

Ok, a slight suggestion, using MySqlConnection as your variable name is a poor choice, since this is the name of the class that you are instantiating.

Try this:



```
Using connection As New MySqlConnection("server=dddd.net;Port=3306; User ID=dddddd; password=ddddd; database=dddddd")
    Using command As New MySqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';", connection)
        command.CommandType = CommandType.Text
        connection.Open()
        Online = CDbl(command.ExecuteScalar())
    End Using
End Using
```

Notice that I have passed the MySqlConnection object as the second parameter to the constructor for the MySqlCommand object.

P.S. Bear in mind that the above was written outside of Visual Studio, and I can't guarantee that it is 100&#37; correct.

Gary

----------


## Dsnowdon

I think that worked but now i have another
sorry about this its the first timei have used MySQl qith vb.net

The error i have it


```
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'online FROM users WERE username='dale' AND password = '*******'' at line 1
```

----------


## gep13

Hey,

This is basically an error with your actual SQL query.  You need to first make sure that the query works directly against the database, before trying to run it through your code.  Do you have the MySql Tools installed, so that you can run the query in Query Browser?  If not, I would highly recommend that you do this.  Make sure you can run the query, and get the result you expect, before trying to run your code.

Also, this would be a good opportunity to change you MySqlCommand object to use Parameters.

I am away to sign off now, but I will try and check back in the next couple days to see how you are getting on.

As a quick clue though, I think this is going to be part of your problem:



```
WERE
```

Gary

----------


## Mousa

> I am having some problems:
> 
> 
> vb Code:
> Using connection As New OleDbConnection(myConnectionString)
            Using adapter As New OleDbDataAdapter("SELECT NameFirst, NameLast FROM Employees", _
                                              connection)
                Dim insert As New OleDbCommand("INSERT INTO Employees (NameFirst, NameLast) VALUES (@NameFirst, @NameLast)", _
                                           connection)
                adapter.InsertCommand = insert
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                 Dim table As New DataTable
                adapter.FillSchema(table, SchemaType.Source)
                Dim row As DataRow = table.NewRow()
                 row("NameFirst") = aName(0)
                row("NameLast") = aName(1)
                table.Rows.Add(row)
                 adapter.Update(table)
            End Using
        End Using
> 
> I am getting an error with this line:
> adapter.Update(table)
> error:
> No value given for one or more required parameters.


Hi,
I'm using DotNetNuke CMS and working on some modules using VB.Net. I have a Datagrid "telerik:RadGrid" and I'm trying to add textbox to filter the rows base on the value that is written in the text box:
I defined the following:
Dim dv As DataView = New DataView()
dv.Table = ?

dv.RowFilter = "Name like '" & TextBox2.Text & "%'"

RadGrid1.DataSource = dv
I have a problem in setting the value of the dv.Table=?

Can you please guide me to the value that should be there
Thanks

----------


## ksuwanto8ksd

hi all expert, i need help to fill in textbox1 and textbox2 with sendernumber and textdecoded, but when run, both textbox is fill-n with last record(not from the beginning record)
what command should i do? here is the code is working with messagebox, thanks !


```
populate textbox from database
'this working
    Private Sub cmdLogin_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) Handles cmdLogin.Click
        Using conn As New MySqlConnection("server=" & txtServer.Text & ";" _
          & "user id=" & txtUsername.Text & ";" _
           & "password=" & txtPassword.Text & ";" _
            & "database=smsd_telkom")
            Using command As New MySqlCommand("SELECT sendernumber,textdecoded FROM inbox", _
            conn)
                conn.Open()
                Using reader As MySqlDataReader = command.ExecuteReader()
                    While reader.Read()
                        'TextBox10.Text = reader("sendernumber")
                        'TextBox11.Text = reader("textdecoded")

                        MessageBox.Show(String.Format("There are {0} {1}in stock.", _
                        reader("sendernumber"), _
                        reader("textdecoded")))
                    End While
                End Using
            End Using
        End Using
    end sub
```

----------


## jmcilhinney

> hi all expert, i need help to fill in textbox1 and textbox2 with sendernumber and textdecoded, but when run, both textbox is fill-n with last record(not from the beginning record)
> what command should i do? here is the code is working with messagebox, thanks !


It's displaying the data from the last record because you are using a loop.  Every iteration of the loop, what's currently in the TextBoxes is replaced with the data from the current record.  The only data that doesn't get replaced is the data from the last row, so that's what you see.

If you only want data from the first row then why are you using a loop at all.  Loops are for doing something multiple times.  If you only want to do something once then why would you use a loop?  Presumably because you have copied and pasted code without considering what it actually does.  I would question why you're executing a query that can return multiple records at all if all you want is one record.

Anyway, if all you want is to read one record then first test the HasRows property to make sure that there is at least one row present, then call Read once and once only, to read just one row.

----------


## .paul.

i followed your advice + followed the link to this thread, hoping to find how to update 2 tables using 2 adapters + 2 datatables, but there's nothing here that answers how to keep both tables synchronized.
here's my code... can you help?


vb Code:
Public Class dataLayer
     Private Shared connection As OleDb.OleDbConnection
    Private Shared updatableAdapter(1) As OleDb.OleDbDataAdapter
    Private Shared transaction As OleDb.OleDbTransaction
     Private Shared Sub connect()
        connection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & IO.Path.Combine(My.Application.Info.DirectoryPath, "details.accdb") & ";Persist Security Info=False;")
        connection.Open()
    End Sub
     Private Shared Sub disConnect()
        connection.Close()
    End Sub
     Public Shared Function getJoinedTable() As DataTable
        connect()
        updatableAdapter(0) = New OleDb.OleDbDataAdapter("SELECT a.id, a.number, a.subjectName, a.years, a.birthYear, b.note FROM Table1 AS a INNER JOIN Table2 AS b ON a.id = b.id", connection)
        updatableAdapter(1) = New OleDb.OleDbDataAdapter("SELECT * FROM Table2", connection) 'contains id, note
         ' Create the InsertCommand.
        Dim command As New OleDb.OleDbCommand("INSERT INTO Table1 VALUES(@id, @number, @subjectName, @years, @birthYear)", connection)
         ' Add the parameters for the InsertCommand.
        command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
        command.Parameters.Add("@number", OleDb.OleDbType.VarChar, 50, "number")
        command.Parameters.Add("@subjectName", OleDb.OleDbType.VarChar, 50, "subjectName")
        command.Parameters.Add("@years", OleDb.OleDbType.Integer, 3, "years")
        command.Parameters.Add("@birthYear", OleDb.OleDbType.Integer, 4, "birthYear")
        'command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
         updatableAdapter(0).InsertCommand = command
         command = New OleDb.OleDbCommand("INSERT INTO Table2 VALUES(@id, @note)", connection)
        ' Add the parameters for the InsertCommand.
        command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
        command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
         updatableAdapter(1).InsertCommand = command
         ' Create the UpdateCommand.
        command = New OleDb.OleDbCommand( _
        "UPDATE Table1 SET id = @id, [number] = @number, subjectName = @subjectName, years = @years, " & _
            "birthYear = @birthYear WHERE id = @oldID", connection)
         ' Add the parameters for the UpdateCommand.
        command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
        command.Parameters.Add("@number", OleDb.OleDbType.VarChar, 50, "number")
        command.Parameters.Add("@subjectName", OleDb.OleDbType.VarChar, 50, "subjectName")
        command.Parameters.Add("@years", OleDb.OleDbType.Integer, 3, "years")
        command.Parameters.Add("@birthYear", OleDb.OleDbType.Integer, 4, "birthYear")
        Dim parameter As OleDb.OleDbParameter = command.Parameters.Add( _
            "@oldID", OleDb.OleDbType.Integer, 5, "id")
        parameter.SourceVersion = DataRowVersion.Original
         updatableAdapter(0).UpdateCommand = command
         ' Create the UpdateCommand.
        command = New OleDb.OleDbCommand( _
        "UPDATE Table2 SET id = @idnote = @note WHERE id = @oldID", connection)
         ' Add the parameters for the UpdateCommand.
        command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
        command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
        parameter = command.Parameters.Add( _
            "@oldID", OleDb.OleDbType.Integer, 5, "id")
        parameter.SourceVersion = DataRowVersion.Original
         updatableAdapter(1).UpdateCommand = command
         ' Create the DeleteCommand.
        command = New OleDb.OleDbCommand( _
            "DELETE FROM Table1 WHERE id = @id", connection)
         ' Add the parameters for the DeleteCommand.
        parameter = command.Parameters.Add( _
            "@id", OleDb.OleDbType.Integer, 5, "id")
        parameter.SourceVersion = DataRowVersion.Original
         updatableAdapter(0).DeleteCommand = command
         ' Create the DeleteCommand.
        command = New OleDb.OleDbCommand( _
            "DELETE FROM Table2 WHERE id = @id", connection)
         ' Add the parameters for the DeleteCommand.
        parameter = command.Parameters.Add( _
            "@id", OleDb.OleDbType.Integer, 5, "id")
        parameter.SourceVersion = DataRowVersion.Original
         updatableAdapter(1).DeleteCommand = command
         Dim dt As New DataTable
        updatableAdapter(0).Fill(dt)
         Return dt
     End Function
     Public Shared Sub update(ByVal dt As DataTable, ByVal adapterIndex As Integer)
        updatableAdapter(adapterIndex).Update(dt)
        connection.Close()
    End Sub
 End Class

----------


## jmcilhinney

> i followed your advice + followed the link to this thread, hoping to find how to update 2 tables using 2 adapters + 2 datatables, but there's nothing here that answers how to keep both tables synchronized.
> here's my code... can you help?


I'm not sure I understand.  Do you actually mean two database tables, two DataAdapters and ONE DataTable?  If it's 2, 2 and 2 then that's just 1, 1 and 1 twice, so it's no different to what you normally do.

----------


## .paul.

it's 2 database tables, two DataAdapters and (should be) 2 DataTables

the 1st dataadapter queries both tables + fills the 1st datatable which i then bind to a dgv
the 2nd dataadapter should fill a 2nd datatable with just 2 fields (the id field is the primarykey in the 1st database table + the foreign key in the 2nd database table. the 2 fields are id, + note)

the problem is, changing values in the dgv alters the 1st datatable but the 2nd datatable remains the same. i need to synchronize the 2 datatables so when i update the 2nd dataadapter, it has the latest values

----------


## jmcilhinney

> it's 2 database tables, two DataAdapters and (should be) 2 DataTables
> 
> the 1st dataadapter queries both tables + fills the 1st datatable which i then bind to a dgv
> the 2nd dataadapter should fill a 2nd datatable with just 2 fields (the id field is the primarykey in the 1st database table + the foreign key in the 2nd database table. the 2 fields are id, + note)
> 
> the problem is, changing values in the dgv alters the 1st datatable but the 2nd datatable remains the same. i need to synchronize the 2 datatables so when i update the 2nd dataadapter, it has the latest values


I don't think there's any way to do that other than to handle events of the first DataTable and update the second DataTable manually.

----------


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

----------


## jmcilhinney

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

----------


## .paul.

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

----------


## 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?

----------


## jmcilhinney

> 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

----------


## BLUMONKEY

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.

----------


## .paul.

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

----------


## .paul.

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

----------


## BLUMONKEY

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

----------


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

----------


## gep13

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

----------


## coolcurrent4u

@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

----------


## BLUMONKEY

> @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!

----------


## jmcilhinney

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

----------


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

----------


## jmcilhinney

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

----------


## BLUMONKEY

No problem... thanks again.  :Smilie:

----------


## dhilip81

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

----------


## dhilip81

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

----------


## Jack Tomy

nice and helpful

----------


## Jack Tomy

worked perfect, thank you

----------


## 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?

----------


## jmcilhinney

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

----------


## AC1982

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

----------


## gep13

> 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

----------


## DavesChillaxin

> 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:
> 
> 
> 
> ```
>     Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
> 
> ...


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

----------


## Avatar_GW

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  :Smilie: 

My Post

----------


## cooljoe04

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

----------


## ADQUSIT

Very very nice data you've provided.



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

```
adapter.missingschema........
```

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

----------


## jmcilhinney

> Hi John. I don't understand this 
> 
> ```
> 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.

----------


## ADQUSIT

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

----------


## awweather

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

----------


## jmcilhinney

> Do you simply mean that this property automatically adds PK to the DataTable? Am I right?
> 
> 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.

----------


## jmcilhinney

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

----------


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

----------


## jmcilhinney

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

----------


## 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:



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

----------


## jmcilhinney

> 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:
> 
> 
> 
> ```
> 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 " & _
> ...


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.

----------


## bodylojohn

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

----------


## jmcilhinney

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

----------


## LuckyLuke82

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:



```
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:



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

----------


## jmcilhinney

> And this is my VB.NET code, starting from your example:
> 
> 
> 
> ```
> Private Sub InicialiseDataAdapter()
> 
>       Dim insert As New OracleCommand("MYSCHEMA.UPDATE_TABLE", Myconn)
> 
> ...


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.

----------


## LuckyLuke82

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



```
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 ?

----------


## jmcilhinney

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

----------


## LuckyLuke82

Ok, no problem.

----------

