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!
For example
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic
namespace HowTo.Samples.ADONET
public class sqldtreader
public shared sub Main()
Dim mysqldtreader as sqldtreader
mysqldtreader = new sqldtreader()
mysqldtreader.Run()
end sub
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)
try
mySqlConnection.Open()
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))
else
Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
end if
loop
catch e as Exception
Console.Write(e.ToString())
finally
' Always call Close when done reading.
if Not (myDataReader is Nothing)
myDataReader.Close()
end if
' Close the connection when done with it.
if (mySqlConnection.State = ConnectionState.Open)
mySqlConnection.Close()
end if
end try
end sub
end class
end namespace
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!
BASIC program to read/write SQL
SteveDyte
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)
mySqlConnection.Open()
myDataAdapter.SelectCommand = mySqlCommand
myDataAdapter.Fill(ds)
I think will populate the a dataset called ds
Aguila
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
Next
There you are.........
TimDev
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
thx!
(this thingy is just killing me>.<)
stan_siu
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
dt.Columns.Add("Foo")
d.Tables.Add(dt)
dt.Rows.Add("a")
dt.Rows.Add("s")
'//Display the contents by using a row index to look at field
'//Can us index or Field name to refer to item
MsgBox(d.Tables(0).Rows(0).Item(0).ToString)
MsgBox(d.Tables(0).Rows(1).Item("foo").T
Damon Tivel MSFT
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
dt.Columns.Add("Foo")
d.Tables.Add(dt)
dt.Rows.Add("a")
dt.Rows.Add("s")
d.Tables.Add(dt)
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
MylesR
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
http://msdn.microsoft.com/vstudio/express/vb/learning/
http://msdn.microsoft.com/vbasic/learning/introtovb2005/
hope this helps.