Page 1 of 3 123 LastLast
Results 1 to 40 of 104

Thread: Retrieving and Saving Data in Databases

  1. #1

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Retrieving and Saving Data in Databases

    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:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", _
    3.                                     connection)
    4.         connection.Open()
    5.  
    6.         Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
    7.  
    8.         'Use totalQuantity here.
    9.     End Using
    10. 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:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem WHERE SupplierId = @SupplierId", _
    3.                                     connection)
    4.         command.Parameters.Add("@SupplierId", SqlDbType.Int).Value = supplierId
    5.         connection.Open()
    6.  
    7.         Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
    8.  
    9.         'Use totalQuantity here.
    10.     End Using
    11. 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:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
    3.                                     connection)
    4.         connection.Open()
    5.  
    6.         Using reader As SqlDataReader = command.ExecuteReader()
    7.             While reader.Read()
    8.                 MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.", _
    9.                                               reader("Quantity"), _
    10.                                               reader("Unit"), _
    11.                                               reader("Name")))
    12.             End While
    13.         End Using
    14.     End Using
    15. 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:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
    3.                                     connection)
    4.         connection.Open()
    5.  
    6.         Using reader As SqlDataReader = command.ExecuteReader()
    7.             Dim table As New DataTable
    8.  
    9.             table.Load(reader)
    10.  
    11.             'The table can be used here to display the data.
    12.             'That will most likely be done via data-binding but that is NOT a data access issue.
    13.         End Using
    14.     End Using
    15. 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:
    1. Private connection As New SqlConnection("connection string here")
    2. Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
    3.                                       connection)
    4. Private table As New DataTable
    5.  
    6. Private Sub InitialiseDataAdapter()
    7.     Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
    8.     Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
    9.     Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)
    10.  
    11.     delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
    12.  
    13.     insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    14.     insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    15.     insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    16.  
    17.     update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    18.     update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    19.     update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    20.     update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
    21.  
    22.     Me.adapter.DeleteCommand = delete
    23.     Me.adapter.InsertCommand = insert
    24.     Me.adapter.UpdateCommand = update
    25.  
    26.     Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    27. End Sub
    28.  
    29. Private Sub GetData()
    30.     'Retrieve the data.
    31.     Me.adapter.Fill(Me.table)
    32.  
    33.     'The table can be used here to display and edit the data.
    34.     'That will most likely involve data-binding but that is not a data access issue.
    35. End Sub
    36.  
    37. Private Sub SaveData()
    38.     'Save the changes.
    39.     Me.adapter.Update(Me.table)
    40. 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:
    1. Private connection As New SqlConnection("connection string here")
    2. Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
    3.                                       connection)
    4. Private builder As New SqlCommandBuilder(adapter)
    5. Private table As New DataTable
    6.  
    7. Private Sub InitialiseDataAdapter()
    8.     Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    9. End Sub
    10.  
    11. Private Sub GetData()
    12.     'Retrieve the data.
    13.     Me.adapter.Fill(Me.table)
    14.  
    15.     'The table can be used here to display and edit the data.
    16.     'That will most likely involve data-binding but that is not a data access issue.
    17. End Sub
    18.  
    19. Private Sub SaveData()
    20.     'Save the changes.
    21.     Me.adapter.Update(Me.table)
    22. 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:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
    3.                                     connection)
    4.         command.Parameters.AddWithValue("@Name", someName)
    5.         command.Parameters.AddWithValue("@Quantity", someQuantity)
    6.         command.Parameters.AddWithValue("@Unit", someUnit)
    7.  
    8.         connection.Open()
    9.  
    10.         command.ExecuteNonQuery()
    11.     End Using
    12. 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.

  2. #2

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

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

  3. #3

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

    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:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
    3.                                         connection)
    4.         Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
    5.                                      connection)
    6.  
    7.         insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
    8.         insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
    9.         insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
    10.  
    11.         adapter.InsertCommand = insert
    12.         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    13.  
    14.         Dim table As New DataTable
    15.  
    16.         'Retrieve the schema.
    17.         adapter.FillSchema(table, SchemaType.Source)
    18.  
    19.         'Add the new rows to the DataTable, e.g.
    20.         Dim row As DataRow = table.NewRow()
    21.  
    22.         row("Name") = someName
    23.         row("Quantity") = someQuantity
    24.         row("Unit") = someUnit
    25.         table.Rows.Add(row)
    26.  
    27.         'Save the changes.
    28.         adapter.Update(table)
    29.     End Using
    30. 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.

    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:
    1. Using connection As New SqlConnection("connection string here"), _
    2.       adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
    3.                                         connection), _
    4.       bulkCopy As New SqlBulkCopy(connection)
    5.     bulkCopy.DestinationTableName = "StockItem"
    6.  
    7.     'This is unnecessary in this case because the column positions match but is included for completeness.
    8.     bulkCopy.ColumnMappings.Add("Name", "Name")
    9.     bulkCopy.ColumnMappings.Add("Quantity", "Quantity")
    10.     bulkCopy.ColumnMappings.Add("Unit", "Unit")
    11.  
    12.     Dim table As New DataTable
    13.  
    14.     'Retrieve the schema.
    15.     adapter.FillSchema(table, SchemaType.Source)
    16.  
    17.     'Add the new rows to the DataTable, e.g.
    18.     Dim row As DataRow = table.NewRow()
    19.  
    20.     row("Name") = someName
    21.     row("Quantity") = someQuantity
    22.     row("Unit") = someUnit
    23.     table.Rows.Add(row)
    24.  
    25.     'Save the changes.
    26.     bulkCopy.WriteToServer(table)
    27. End Using
    Last edited by jmcilhinney; Jul 4th, 2011 at 01:54 AM. Reason: Added SqlBulkCopy example

  4. #4
    New Member
    Join Date
    Jan 2008
    Posts
    2

    Re: Retrieving and Saving Data in Databases

    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.

  5. #5

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

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

  6. #6
    Lively Member
    Join Date
    Jun 2008
    Location
    Philippines
    Posts
    70

    Re: Retrieving and Saving Data in Databases

    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

  7. #7

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

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

  8. #8
    New Member
    Join Date
    Oct 2008
    Posts
    13

    Angry Re: Retrieving and Saving Data in Databases

    Okay I am using the code below:

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

  9. #9

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by venuspcs
    Okay I am using the code below:

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

  10. #10
    New Member
    Join Date
    Oct 2008
    Posts
    13

    Re: Retrieving and Saving Data in Databases

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

    WTH - Here is the complete revised code:

    Code:
        Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
    
            Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Richard Colbert\Documents\Visual Studio 2008\Projects\WindowsApplication1\WindowsApplication1\shenandoah.accdb'")
            Dim adapter As New OleDb.OleDbDataAdapter("SELECT ID, mmddyy, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances FROM Shenandoahs", connection)
            Dim insert As New OleDb.OleDbCommand("INSERT INTO Shenandoahs (mmddyy, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances) VALUES (@mmddyy, @Driver, @Truck, @Customer, @Rate, @Where_From, @Where_To, @Pit_Ticket, @PO_Number, @Yards_Tons, @Description, @QP_Fee, @Fuel, @Expenses, @Advances)", connection)
    
            insert.Parameters.Add("@mmddyy", OleDb.OleDbType.VarChar, 20, "mmddyy")
            insert.Parameters.Add("@Driver", OleDb.OleDbType.VarChar, 20, "Driver")
            insert.Parameters.Add("@Truck", OleDb.OleDbType.VarChar, 10, "Truck")
            insert.Parameters.Add("@Customer", OleDb.OleDbType.VarChar, 10, "Customer")
            insert.Parameters.Add("@Rate", OleDb.OleDbType.VarChar, 10, "Rate")
            insert.Parameters.Add("@Where_From", OleDb.OleDbType.VarChar, 100, "Where_From")
            insert.Parameters.Add("@Where_To", OleDb.OleDbType.VarChar, 100, "Where_To")
            insert.Parameters.Add("@Pit_Ticket", OleDb.OleDbType.VarChar, 20, "Pit_Ticket")
            insert.Parameters.Add("@PO_Number", OleDb.OleDbType.VarChar, 20, "PO_Number")
            insert.Parameters.Add("@Yards_Tons", OleDb.OleDbType.VarChar, 20, "Yards_Tons")
            insert.Parameters.Add("@Description", OleDb.OleDbType.VarChar, 100, "Description")
            insert.Parameters.Add("@QP_Fee", OleDb.OleDbType.VarChar, 20, "QP_Fee")
            insert.Parameters.Add("@Fuel", OleDb.OleDbType.VarChar, 20, "Fuel")
            insert.Parameters.Add("@Expenses", OleDb.OleDbType.VarChar, 20, "Expenses")
            insert.Parameters.Add("@Advances", OleDb.OleDbType.VarChar, 20, "Advances")
    
            adapter.InsertCommand = insert
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
            Dim table As New DataTable
    
            'Retrieve the data.
            adapter.FillSchema(table, SchemaType.Source)
    
            'Add the new rows to the DataTable, e.g.
            Dim row As DataRow = table.NewRow()
    
            row("mmddyy") = mmddyy
            row("Driver") = Driver
            row("Truck") = Truck
            row("Customer") = Customer
            row("Rate") = Rate
            row("Where_From") = Where_From
            row("Where_To") = Where_To
            row("Pit_Ticket") = Pit_Ticket
            row("PO_Number") = Po_Number
            row("Yards_Tons") = Yards_Tons
            row("Description") = Description
            row("QP_Fee") = QP_Fee
            row("Fuel") = Fuel
            row("Expenses") = Expenses
            row("Advances") = Advances
    
            table.Rows.Add(row)
    
            'Save the changes.
            adapter.Update(table)
    
        End Sub

  11. #11
    Addicted Member
    Join Date
    Sep 2008
    Location
    Jacksonville, Florida
    Posts
    147

    Re: Retrieving and Saving Data in Databases

    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

  12. #12
    New Member
    Join Date
    Oct 2008
    Posts
    13

    Re: Retrieving and Saving Data in Databases

    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.

  13. #13
    Addicted Member
    Join Date
    Sep 2008
    Location
    Jacksonville, Florida
    Posts
    147

    Re: Retrieving and Saving Data in Databases

    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?

  14. #14
    New Member
    Join Date
    Oct 2008
    Posts
    13

    Re: Retrieving and Saving Data in Databases

    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.

  15. #15
    Addicted Member
    Join Date
    Sep 2008
    Location
    Jacksonville, Florida
    Posts
    147

    Re: Retrieving and Saving Data in Databases

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

  16. #16
    New Member
    Join Date
    Oct 2008
    Posts
    13

    Thumbs up Re: Retrieving and Saving Data in Databases

    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!

  17. #17

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

    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.

  18. #18
    Junior Member
    Join Date
    Sep 2008
    Posts
    21

    Re: Retrieving and Saving Data in Databases

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

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

    thanks

  19. #19

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by cuterita7
    vb Code:
    1. Using connection As New SqlConnection("connection string here")
    2. Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem",
    3. _connection)  
    4. connection.Open()
    5. Dim totalQuantity As Double = CDbl(command.ExecuteScalar())  
    6. 'Use totalQuantity here.
    7. 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.
    vb.net Code:
    1. Try
    2.     Using connection As New SqlConnection("connection string here")
    3.         Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", _
    4.                                         connection)
    5.             connection.Open()
    6.  
    7.             Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
    8.  
    9.             'Use totalQuantity here.
    10.         End Using
    11.     End Using
    12. Catch ex As SqlException
    13.     For Each er As SqlError In ex.Errors
    14.         MessageBox.Show(er.Message)
    15.     Next
    16. End Try

  20. #20
    Hyperactive Member
    Join Date
    Apr 2007
    Posts
    362

    Re: Retrieving and Saving Data in Databases

    @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:
    1. Private connection As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")
    2.     Private adapter As New SqlClient.SqlDataAdapter("SELECT kmsrun FROM machineprofile", connection)
    3.     Private table As New DataTable
    4. Private Sub InitialiseDataAdapter()
    5. Dim update As New SqlClient.SqlCommand("UPDATE machineprofile SET kmsrun = '" & TextBox10.Text & "' WHERE MachineID = '" & TextBox1.Text & "'", Me.connection)
    6. Me.adapter.UpdateCommand = update
    7.         Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    8.  
    9. Private Sub GetData()
    10.        
    11.         Me.adapter.Fill(Me.table)
    12.  
    13. End Sub
    14.  
    15. Private Sub SaveData()
    16.                Me.adapter.Update(Me.table)
    17. End Sub

  21. #21
    Hyperactive Member
    Join Date
    Apr 2007
    Posts
    362

    Re: Retrieving and Saving Data in Databases

    anyone plz help!! y is this not working?

  22. #22
    Hyperactive Member
    Join Date
    Apr 2007
    Posts
    362

    Re: Retrieving and Saving Data in Databases

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

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

    And i am getting this error:

    Incorrect syntax near the keyword 'where'.
    What's wrong??

  23. #23

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

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

  24. #24
    Addicted Member riechan's Avatar
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    254

    Re: Retrieving and Saving Data in Databases

    Question, in this line of code:

    Code:
    dbAdpPatronType.Fill(dbDsetPatron, "PatronType")
    Is the syntax like this:
    [adaptername].fill([datasetname],[datatablename?])
    ====================
    ほんとにどもありがとう!

    Rie Ishida

  25. #25

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by riechan View Post
    Question, in this line of code:

    Code:
    dbAdpPatronType.Fill(dbDsetPatron, "PatronType")
    Is the syntax like this:
    [adaptername].fill([datasetname],[datatablename?])
    Strictly, no. It's:
    vb.net Code:
    1. 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.

  26. #26
    Addicted Member riechan's Avatar
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    254

    Re: Retrieving and Saving Data in Databases

    So then:

    Code:
        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?
    ====================
    ほんとにどもありがとう!

    Rie Ishida

  27. #27

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by riechan View Post
    So then:

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

  28. #28
    Addicted Member riechan's Avatar
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    254

    Re: Retrieving and Saving Data in Databases

    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?
    ====================
    ほんとにどもありがとう!

    Rie Ishida

  29. #29

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by riechan View Post
    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.

  30. #30
    Member
    Join Date
    Mar 2010
    Posts
    58

    Re: Retrieving and Saving Data in Databases

    I am having some problems:

    vb Code:
    1. Using connection As New OleDbConnection(myConnectionString)
    2.             Using adapter As New OleDbDataAdapter("SELECT NameFirst, NameLast FROM Employees", _
    3.                                               connection)
    4.                 Dim insert As New OleDbCommand("INSERT INTO Employees (NameFirst, NameLast) VALUES (@NameFirst, @NameLast)", _
    5.                                            connection)
    6.                 adapter.InsertCommand = insert
    7.                 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    8.  
    9.                 Dim table As New DataTable
    10.                 adapter.FillSchema(table, SchemaType.Source)
    11.                 Dim row As DataRow = table.NewRow()
    12.  
    13.                 row("NameFirst") = aName(0)
    14.                 row("NameLast") = aName(1)
    15.                 table.Rows.Add(row)
    16.  
    17.                 adapter.Update(table)
    18.             End Using
    19.         End Using

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

  31. #31
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Retrieving and Saving Data in Databases

    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

  32. #32
    Fanatic Member
    Join Date
    Jul 2007
    Posts
    617

    Re: Retrieving and Saving Data in Databases

    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?

  33. #33
    Member cops's Avatar
    Join Date
    Dec 2007
    Posts
    45

    Re: Retrieving and Saving Data in Databases

    useful

  34. #34
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    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:
    1. Using connection As New SqlConnection("server=*****;Port=3306; User ID=*****; password=****; database=*****")
    2.  
    3.             Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';")
    4.  
    5.                 connection()
    6.                 connection.Open()
    7.                 Dim Online As Double = CDbl(command.ExecuteScalar())
    8.  
    9.             End Using
    10.         End Using

  35. #35
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: Retrieving and Saving Data in Databases

    Hey,

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

    Gary

  36. #36

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by Dsnowdon View Post
    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:
    Code:
    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.

  37. #37
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    so i should delete the conection() Stetment

  38. #38
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: Retrieving and Saving Data in Databases

    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

  39. #39
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    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
    Last edited by Dsnowdon; Dec 24th, 2010 at 12:03 PM.

  40. #40
    Junior Member
    Join Date
    Sep 2010
    Posts
    21

    Re: Retrieving and Saving Data in Databases

    I dont kn

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width