reading data fromSQL server into a data??? something 2

[DougP]

I don't want to drag something on my form I want to open a recordset programmatically and move back and forth through it. I would like to make a connection to a SQL database on my WEB site, then open a table or recordset with a select statement then be able to movefirst or movenext through that recordset and also be able to extract the columns data textbox = somefieldname.
I seem to get a lot of code written only to find out a data reader can't movelast or movenext or this can't use a field name or something. driving me nuts!


here is my vs2005 code so far

        Dim conn As SqlConnection
        Dim da As SqlDataAdapter = New SqlDataAdapter()
        Dim cmd As SqlCommand

        conn = New SqlConnection
        conn.ConnectionString = "server=000.000.000.000;uid=user;pwd=pass;database=myDB"
        cmd = New SqlCommand("Select * from [TimeSheet Employees]", conn)
        da.SelectCommand = cmd
        Dim custDS As DataSet = New DataSet
        da.Fill(custDS, "Select * from [TimeSheet Employees]")
        'move to first record
        'or move to last record
        'or read all records one at a time
        'be able to move back to first record 
         me.txtName = da?????  ("Name")
        me.txtAddress = da????? ( "Address")
       'etc
        conn = Nothing
        da = Nothing
        cmd = Nothing

DougP
< I Built one

http://www.robots2000.com

 

[RiverGuy]

You can kind of MoveNext with a DataReader. But to have control like you want, use a DataTable. Here's a crude example to demonstrate this. Create a Windows Form project, create a TextBox called TextBox1 and a button called btnMoveNext. Change the connection string and try it out. The same data principles would apply to Web Forms as well.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As New SqlClient.SqlConnection("Data Source=SomeServer;Initial Catalog=SomeDatabase;Integrated Security=SSPI;")
        Dim da As New SqlClient.SqlDataAdapter
        Dim cmd As New SqlClient.SqlCommand
        Dim dt As New DataTable
        dt.TableName = "COLUMNS"
        With cmd
            .Connection = con
            .CommandType = CommandType.Text
            .CommandText = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"
        End With
        With da
            .SelectCommand = cmd
            .Fill(dt)
        End With

        Me.TextBox1.DataBindings.Add("Text", dt, "COLUMN_NAME")
    End Sub

    Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
        Dim cm As CurrencyManager = CType(Me.BindingContext(Me.TextBox1.DataBindings(0).DataSource), CurrencyManager)
        Try
            cm.Position += 1
        Catch ex As Exception

        End Try
    End Sub

 

[RiverGuy]

I forgot to mention--here is some code for "Move Last"

    Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        Dim cm As CurrencyManager = CType(Me.BindingContext(Me.TextBox1.DataBindings(0).DataSource), CurrencyManager)
        Try
            cm.Position = CType(Me.TextBox1.DataBindings(0).DataSource, DataTable).Rows.Count - 1
        Catch ex As Exception

        End Try
    End Sub

 

[DougP]

and I need to movefirst
I have a recordset I am moving through and want to move it back to the top and movenext though it again.

also what is CurrencyManager?
Dim cm As CurrencyManager = CT


DougP
< I Built one

http://www.robots2000.com

 

[PhysicsGuy]

A data reader can move only forwards through the records:

Do While dr.Read
txtbox.value = dr("Surname")
'etc
Loop

 

[DougP]

RiverGuys example uses a dataTABLE
I don't want a datareadeer for that reason
Dim dt As New DataTable

plus I said that in the first post

I seem to get a lot of code written only to find out a data reader can't movelast or movenext or

and Is there anything like th old ADO control in VB6 which did movefirst, movelast, next, and prev?

DougP
< I Built one

http://www.robots2000.com

 

[RiverGuy]

and I need to movefirst

cm.Position = 0 would move first.

also what is CurrencyManager?

CurrencyManager definition:

http://msdn.microsoft.com/en-us/library/system.windows.forms.currencymanager.aspx

and Is there anything like th old ADO control in VB6 which did movefirst, movelast, next, and prev?

I don't know. I rarely used the ADO control, and have never looked into using an equivalent in .Net.

 

[DougP]

Fanatastic RiverGuy,

I gave your 3 stars since I was seeing so many stars trying to figure it out and trying to get help for this for a LONG LONG LONG LONG time

DougP
< I Built one

http://www.robots2000.com

 

[PRPhx]

Also do a google search for vb.net binding manager.
Here is a link to a good example:

http://msdn.microsoft.com/en-us/library/system.windows.forms.bindingmanagerbase(vs.71).aspx

It shows how to move next, previous, first and last. It uses a binding manager

 

[mobi2012]

PRPhx thanks, good link