Monday, May 01, 2006 6:35 PMAnybody know how to read and write records in SQL?
I have a VB 2005 program which have a loop and generate a lot of variables
I need to read and write them rapidly so the command need to be simple and efficent
Can anybody help me?
I've tried to find all helps i can get
MSDN library, BASIC website etc...
they all have the information i need, but i just don't understand it!
Those help just introduce the method it works then pop up source code
the problem is - i don't understand the source code!
I don't know which part should be in my program and which part should be edited!
public class sqldtreader
public shared sub Main()
Dim mysqldtreader as sqldtreader
mysqldtreader = new sqldtreader()
public sub Run()
Dim myDataReader as SqlDataReader
Dim mySqlConnection as SqlConnection
Dim mySqlCommand as SqlCommand
mySqlConnection = new SqlConnection("server=(local)\NetSDK;Trusted_Connection=yes;database=northwind")
mySqlCommand = new SqlCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", mySqlConnection)
myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
Console.Write("EmployeeID" + Chr(9))
Console.Write("Name" + Chr(9))
Console.Write("Title" + Chr(9))
Console.Write("ReportsTo" + Chr(10))
' Always call Read before accessing data.
do while (myDataReader.Read())
Console.Write(myDataReader.GetInt32(0).ToString() + Chr(9))
Console.Write(myDataReader.GetString(2) + " " + myDataReader.GetString(1) + Chr(9))
Console.Write(myDataReader.GetString(3) + Chr(9))
if (myDataReader.IsDBNull(4)) then
Console.Write("N/A" + Chr(10))
Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
catch e as Exception
' Always call Close when done reading.
if Not (myDataReader is Nothing)
' Close the connection when done with it.
if (mySqlConnection.State = ConnectionState.Open)
right... i just don't know which part is reading, whcih part is writing SQL
what i need in the program is to read row (rowv) in a table with one column only
rowv is a variable which my loop will keep editing it
another loop in my program will write new data in the new row of the table (also i have a variable to keep tracking the no. of rows of data)
Thanks to anyone who read till here
More thanks to anyone who solve my problem!
Monday, May 01, 2006 7:23 PM
Before I go any further the bulk of my experience is with Access but the classes and datastructures are the same and that's why I'm ansering this.
"what i need in the program is to read row (rowv) in a table with one column only"
Let's say your table is named Table......
const rowzero as integer = 0
For each row as datarow in table.rows
foo = row(rowzero)
There you are.........
Monday, May 01, 2006 7:46 PM
As your using a SqlDataReader in this code it is sort of reading one line at a time and processing this before it gets the next line so you really only have access to reference the current line at any time.
In this code it is reading each individual line one at a time and simply writing the output for each row. You cant go back and refer to a previous row by index which is what I think you want to do.
Now if you want to be able to reference any line in your code by saying
Row( <RowNumber> )
you are going to have to either use a dataset to get all the data in which case you can reference to any row retrieved by index or modify you code to store the read lines into some other data structure such as an arraylist or collection or something which will then allow you to refer to individual lines.
The following code manually populates a dataset but you can do this from SQL very easily and this shows you can refer to the rows by index number
'//Manually create a dataset with a single table and a single column called foo
'// and add two records to this data table
Dim dt As New DataTable
'//Display the contents by using a row index to look at field
'//Can us index or Field name to refer to item
Monday, May 01, 2006 9:51 PMYour noble reply... saved my life
Just some minor problem
If my program is linked to a database called 'database01'
How can i make sure all those records in the dataset get into the database?
Secondly, in your example
Dim dt As New DataTable
name d is not declared=.=
Also, if i have some data in my database already, and i want to import it to the dataset, what code i should add?
lastly, how much data can the dataset store? coz the program will pop up numbers that around 10^8 digits (i mean quite a lot of numbers with these digits, not only 1)
That is one of the reason that i didn't choose to use array
another reason is if the computer crash, all datas in array will lost
Using SQL can save and load back before the crash and continue the work
btw... really thx for your help
but anybody help me this time please?
Monday, May 01, 2006 11:30 PM
Something like, (I'm not 100% but this is probably pretty close)
Dim mySqlConnection As SqlConnection
Dim mySqlCommand As SqlCommand
Dim myDataAdapter As New SqlDataAdapter
Dim ds As New DataSet
mySqlConnection = New SqlConnection("server=(local)\NetSDK;Trusted_Connection=yes;database=northwind")
mySqlCommand = New SqlCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", mySqlConnection)
myDataAdapter.SelectCommand = mySqlCommand
I think will populate the a dataset called ds
Tuesday, May 02, 2006 10:00 AMyes... i have seen this example...
the problem is... i don't understand wat it means.... especially the sqlconnection and sqlcommand
is this writing data in the database to the dataset?
how about write a variable in my program back to database?
Also, which part of this source i should modify?
(this thingy is just killing me>.<)
Tuesday, May 02, 2006 2:09 PM
the following are basically defining objects required to populate a dataset with data from a given database. If you want to update the database with the contents there are a few other properties/methods you would need to define.
You need to do a little reading/watching to understand some of the data concepts in vb , there is simply too much to adequately cover just a simple posting. This will show you some easy ways to do this sort of task.
some useful resources which include a free ebook, online videos covering vb concepts etc. and if you registered and are using vb express there was anough free ebook it will send you a link to
hope this helps.