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:

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 

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 

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:

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:

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:

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:

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:

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

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.

[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]

Originally Posted by 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.

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]

Originally Posted by 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

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]

Originally Posted by 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!

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]

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]

Originally Posted by cuterita7

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

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.

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

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 DataTable
Private 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

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]

Originally Posted by riechan

Question, in this line of code:

dbAdpPatronType.Fill(dbDsetPatron, "PatronType")

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

Strictly, no. It's:

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]

Originally Posted by 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?

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]

Originally Posted by 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?

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.