Retrieving and Saving Data in Databases

[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

        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.

[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

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:

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:

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?

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.