-
May 20th, 2007, 08:45 PM
#1
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:
Using connection As New SqlConnection("connection string here")
Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", _
connection)
connection.Open()
Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
'Use totalQuantity here.
End Using
End Using
The following example is a repeat of the previous one but including a WHERE clause to filter the results. The same principle applies to filtering any query:
vb.net Code:
Using connection As New SqlConnection("connection string here")
Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem WHERE SupplierId = @SupplierId", _
connection)
command.Parameters.Add("@SupplierId", SqlDbType.Int).Value = supplierId
connection.Open()
Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
'Use totalQuantity here.
End Using
End Using
Retrieving multiple records that will be read and discarded. The ExecuteReader method provides read-only, forward-only access to the entire result set:
vb.net Code:
Using connection As New SqlConnection("connection string here")
Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
connection)
connection.Open()
Using reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.", _
reader("Quantity"), _
reader("Unit"), _
reader("Name")))
End While
End Using
End Using
End Using
Retrieving multiple records for display that will not be updated. The DataTable.Load method will populate a DataTable with the result set exposed by a DataReader:
vb.net Code:
Using connection As New SqlConnection("connection string here")
Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
connection)
connection.Open()
Using reader As SqlDataReader = command.ExecuteReader()
Dim table As New DataTable
table.Load(reader)
'The table can be used here to display the data.
'That will most likely be done via data-binding but that is NOT a data access issue.
End Using
End Using
End Using
Note that DataTable.Load is new in .NET 2.0. I have provided code here to populate a DataTable from a DataReader in .NET 1.x
Retrieving multiple records for display and editing, then saving the changes. The DataAdapter.Fill method populates a DataTable with the contents of the result set of a query. The DataAdapter.Update method saves the changes in a DataTable in accordance with the SQL statements contained in the DeleteCommand, InsertCommand and UpdateCommand properties:
vb.net Code:
Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
connection)
Private table As New DataTable
Private Sub InitialiseDataAdapter()
Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)
delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
Me.adapter.DeleteCommand = delete
Me.adapter.InsertCommand = insert
Me.adapter.UpdateCommand = update
Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
Private Sub GetData()
'Retrieve the data.
Me.adapter.Fill(Me.table)
'The table can be used here to display and edit the data.
'That will most likely involve data-binding but that is not a data access issue.
End Sub
Private Sub SaveData()
'Save the changes.
Me.adapter.Update(Me.table)
End Sub
Note that if your query involves only one table and it has a primary key then you can take the easy option and use a CommandBuilder instead of creating the non-query commands yourself:
vb.net Code:
Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
connection)
Private builder As New SqlCommandBuilder(adapter)
Private table As New DataTable
Private Sub InitialiseDataAdapter()
Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
Private Sub GetData()
'Retrieve the data.
Me.adapter.Fill(Me.table)
'The table can be used here to display and edit the data.
'That will most likely involve data-binding but that is not a data access issue.
End Sub
Private Sub SaveData()
'Save the changes.
Me.adapter.Update(Me.table)
End Sub
Saving changes directly to one or more records in the database. The Command.ExecuteNonQuery method will execute any SQL statement and not return a result set. It can be used to execute a query but you'd never use it for that unless you were populating a view or temp table. Usually you'd use ExecuteNonQuery to execute a DELETE, INSERT or UPDATE command:
vb.net Code:
Using connection As New SqlConnection("connection string here")
Using command As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
connection)
command.Parameters.AddWithValue("@Name", someName)
command.Parameters.AddWithValue("@Quantity", someQuantity)
command.Parameters.AddWithValue("@Unit", someUnit)
connection.Open()
command.ExecuteNonQuery()
End Using
End Using
Note that I have not provided code for disposing objects or catching exceptions. Those are general topics that do not relate specifically to data access so they should be learned elsewhere.
Note also the DataAdapter.Fill, DataAdapter.Update and Command.ExecuteNonQuery methods are all functions. All three return an Integer that contains the number of records that were either retrieved (Fill) or saved (Update, ExecuteNonQuery).
Finally, this code uses all the "old style" data access types. In .NET 2.0 I suggest creating a Data Source to generate a typed DataSet and TableAdapters. The principles there are basically the same except that you have to write less code. All SQL statements are added via the DataSet designer and all you really have to do is create TableAdapters and call their methods. Even the connections are hidden within the TableAdapters so it's all much easier. Understanding what's above should help you understand how TableAdapters work internally though.
EDIT: Note that I have updated the code examples slightly in this post and post #3 to more accurately reflect real usage situations, particularly using a DataAdapter to get and save data in separate methods rather than in the same method.
Last edited by jmcilhinney; Aug 4th, 2020 at 04:36 AM.
-
Jun 7th, 2007, 12:09 AM
#2
Re: Retrieving and Saving Data in Databases
To find the appropriate connection string for your ADO.NET provider visit www.connectionstrings.com.
-
Jan 17th, 2008, 09:47 PM
#3
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:
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
I should also point out that you don't actually need the SelectCommand and the FillSchema call either. You can simply build the DataTable schema yourself if you like.
Here's the above code adapted to use the SqlBulkCopy class, which allows for more efficient insertion of large amounts of data into SQL Server:
vb.net Code:
Using connection As New SqlConnection("connection string here"), _ adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _ connection), _ bulkCopy As New SqlBulkCopy(connection) bulkCopy.DestinationTableName = "StockItem" 'This is unnecessary in this case because the column positions match but is included for completeness. bulkCopy.ColumnMappings.Add("Name", "Name") bulkCopy.ColumnMappings.Add("Quantity", "Quantity") bulkCopy.ColumnMappings.Add("Unit", "Unit") Dim table As New DataTable 'Retrieve the schema. adapter.FillSchema(table, SchemaType.Source) 'Add the new rows to the DataTable, e.g. Dim row As DataRow = table.NewRow() row("Name") = someName row("Quantity") = someQuantity row("Unit") = someUnit table.Rows.Add(row) 'Save the changes. bulkCopy.WriteToServer(table) End Using
Last edited by jmcilhinney; Jul 4th, 2011 at 01:54 AM.
Reason: Added SqlBulkCopy example
-
Jan 22nd, 2008, 09:12 AM
#4
New Member
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.
-
Jan 22nd, 2008, 06:06 PM
#5
Re: Retrieving and Saving Data in Databases
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.
-
Jun 12th, 2008, 03:02 AM
#6
Lively Member
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
-
Jun 12th, 2008, 05:46 AM
#7
Re: Retrieving and Saving Data in Databases
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.
-
Oct 22nd, 2008, 10:45 PM
#8
New Member
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!
-
Oct 23rd, 2008, 05:25 AM
#9
Re: Retrieving and Saving Data in Databases
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.
-
Oct 23rd, 2008, 08:32 AM
#10
New Member
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
-
Oct 23rd, 2008, 08:38 AM
#11
Addicted Member
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
-
Oct 23rd, 2008, 10:28 AM
#12
New Member
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.
-
Oct 23rd, 2008, 11:27 AM
#13
Addicted Member
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?
-
Oct 23rd, 2008, 01:18 PM
#14
New Member
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.
-
Oct 23rd, 2008, 01:24 PM
#15
Addicted Member
Re: Retrieving and Saving Data in Databases
mark your thread resolved, rate those users that helped =)
-
Oct 23rd, 2008, 01:36 PM
#16
New Member
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!
-
Nov 8th, 2008, 07:27 PM
#17
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.
-
Nov 14th, 2008, 07:06 AM
#18
Junior Member
Re: Retrieving and Saving Data in Databases
vb Code:
Using connection As New SqlConnection("connection string here")
Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem",
_connection)
connection.Open()
Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
'Use totalQuantity here.
End Using
Why in this code snippet you don't close the connection?and how do you handle a exception(database exception)?
thanks
-
Nov 14th, 2008, 05:42 PM
#19
Re: Retrieving and Saving Data in Databases
Originally Posted by cuterita7
vb Code:
Using connection As New SqlConnection("connection string here")
Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem",
_connection)
connection.Open()
Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
'Use totalQuantity here.
End Using
Why in this code snippet you don't close the connection?and how do you handle a exception(database exception)?
thanks
I don't close the connection explicitly because it gets disposed at the End Using line, which implicitly closes it. I have recently changed the code to make use of a Using block. My original code did close the connection explicitly.
As I said in my first post, I haven't included any exception handling because that's a separate topic that isn't related to data access specifically. In a real situation, yes, you would provide appropriate exception handling. For this example I didn't want to draw attention away from the actual topic of the thread, which is the data access itself. In a real situation you might put the exception handler inside the Using block(s) or outside, depending on the circumstances and your preference. One of the reasons you should use Using blocks is that they ensure that an object is disposed even if an exception is thrown, so you don't have to do anything extra to dispose the data access objects.
vb.net Code:
Try
Using connection As New SqlConnection("connection string here")
Using command As New SqlCommand("SELECT SUM(Quantity) FROM StockItem", _
connection)
connection.Open()
Dim totalQuantity As Double = CDbl(command.ExecuteScalar())
'Use totalQuantity here.
End Using
End Using
Catch ex As SqlException
For Each er As SqlError In ex.Errors
MessageBox.Show(er.Message)
Next
End Try
-
Oct 31st, 2009, 10:34 AM
#20
Hyperactive Member
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:
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
-
Oct 31st, 2009, 01:49 PM
#21
Hyperactive Member
Re: Retrieving and Saving Data in Databases
anyone plz help!! y is this not working?
-
Oct 31st, 2009, 03:51 PM
#22
Hyperactive Member
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:
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??
-
Oct 31st, 2009, 07:35 PM
#23
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.
-
Dec 29th, 2009, 11:25 AM
#24
Addicted Member
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
-
Dec 29th, 2009, 11:40 AM
#25
Re: Retrieving and Saving Data in Databases
Originally Posted by riechan
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:
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.
-
Dec 29th, 2009, 11:48 AM
#26
Addicted Member
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
-
Dec 29th, 2009, 12:11 PM
#27
Re: Retrieving and Saving Data in Databases
Originally Posted by riechan
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.
-
Dec 29th, 2009, 12:17 PM
#28
Addicted Member
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
-
Dec 29th, 2009, 09:12 PM
#29
Re: Retrieving and Saving Data in Databases
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.
-
Mar 14th, 2010, 06:39 PM
#30
Member
Re: Retrieving and Saving Data in Databases
I am having some problems:
vb Code:
Using connection As New OleDbConnection(myConnectionString) Using adapter As New OleDbDataAdapter("SELECT NameFirst, NameLast FROM Employees", _ connection) Dim insert As New OleDbCommand("INSERT INTO Employees (NameFirst, NameLast) VALUES (@NameFirst, @NameLast)", _ connection) adapter.InsertCommand = insert adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey Dim table As New DataTable adapter.FillSchema(table, SchemaType.Source) Dim row As DataRow = table.NewRow() row("NameFirst") = aName(0) row("NameLast") = aName(1) table.Rows.Add(row) adapter.Update(table) End Using End Using
I am getting an error with this line:
adapter.Update(table)
error:
No value given for one or more required parameters.
-
Mar 15th, 2010, 05:11 AM
#31
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
-
Jul 17th, 2010, 12:57 AM
#32
Fanatic Member
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?
-
Sep 16th, 2010, 04:03 AM
#33
Member
Re: Retrieving and Saving Data in Databases
-
Dec 23rd, 2010, 11:48 PM
#34
Junior Member
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:
Using connection As New SqlConnection("server=*****;Port=3306; User ID=*****; password=****; database=*****") Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';") connection() connection.Open() Dim Online As Double = CDbl(command.ExecuteScalar()) End Using End Using
-
Dec 24th, 2010, 03:45 AM
#35
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
-
Dec 24th, 2010, 07:15 AM
#36
Re: Retrieving and Saving Data in Databases
Originally Posted by Dsnowdon
When i have enterd your code i have got a eroor on Connection() Saying "EXpression i not a method" any help on how to fix please
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: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.
-
Dec 24th, 2010, 11:51 AM
#37
Junior Member
Re: Retrieving and Saving Data in Databases
so i should delete the conection() Stetment
-
Dec 24th, 2010, 11:55 AM
#38
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
-
Dec 24th, 2010, 11:58 AM
#39
Junior Member
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.
-
Dec 24th, 2010, 11:59 AM
#40
Junior Member
Re: Retrieving and Saving Data in Databases
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|