locked
BASIC program to read/write SQL

    Question

  • Anybody 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!
    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!


    Monday, May 01, 2006 6:35 PM

Answers

  • 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

    Monday, May 01, 2006 7:46 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

    http://msdn.microsoft.com/vstudio/express/vb/learning/

    http://msdn.microsoft.com/vbasic/learning/introtovb2005/

    hope this helps.

     

     

    Tuesday, May 02, 2006 2:09 PM

All replies

  •  

    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)

    Next

    There you are.........

     

    Monday, May 01, 2006 7:23 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
            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

    Monday, May 01, 2006 7:46 PM
  • Your 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
            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?


    Monday, May 01, 2006 9:51 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)
            mySqlConnection.Open()

            myDataAdapter.SelectCommand = mySqlCommand
           
            myDataAdapter.Fill(ds)

     

     

    I think will populate the a dataset called ds

    Monday, May 01, 2006 11:30 PM
  • yes... 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?
    thx!
    (this thingy is just killing me>.<)
    Tuesday, May 02, 2006 10:00 AM
  • 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.

     

     

    Tuesday, May 02, 2006 2:09 PM