VB Connect to Database Table and Pass View to String

[Stuugie]

Hi all,

I have a .NET form where I want to connect to a table and then pass each defined (by query) string from the table into a string within the code. I have created a connection to the database already via Data Sources but I'm unsure about the syntax required to loop through the rows in that particular table and concatenate each output into my string variable in the code block. The server name is "SQL2008T1" and the table name is "dbo.tblVSeriesList".

All I really need is how to declare the variables to connect in the code block and then how to iterate through each record. If anyone can push me the right way I would really appreciate it.

[Stuugie]

I've got the following so far and am hopefully progressing in the right direction:

Dim ServerName As String = "SQL2008T1"
Dim lpNum As Integer = 0
Dim dbName As String = "EconAnalysis"
Dim myCommand As String = "SELECT vSeries_Number FROM(tblVSeriesList) where vSeries_Table_Number = '3260020'"
Dim myCon As SqlConnection = New SqlConnection(ServerName)
Dim myTable = myCon.Database(dbName)

[Stuugie]

OK I've gotten this part of my requirements done using ideas from about 5 different examples out there on the interwebs!

        ConnString = "Data Source=SQL2008T1;Initial Catalog=EconAnalysis;Integrated Security=True"
        SCqryStr = ""
        SQLConn.ConnectionString = ConnString
        SQLConn.Open()
        SQLStr = "SELECT vSeries_Number FROM tblVSeriesList"
        SQLCmd.Connection = SQLConn
        SQLCmd.CommandText = SQLStr
        SQLdr = SQLCmd.ExecuteReader
        tpCnt = 0
        x = ""
        'If SQLdr.HasRows Then
        If SQLdr.HasRows Then
            While SQLdr.Read()
                SCqryStr = SCqryStr + SQLdr.GetString(0) 'SQLdr(SQLdr(0))
                SCqryStr = SCqryStr + ","
            End While
        End If
        SQLdr.Close()
        SQLConn.Close()

[Reverend Jim]

I'll have a look at the SQLConn format in a bit but in ADO.net you fetch the records and step through them as follows

Imports ADODB

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New Connection
        Dim rec As New Recordset

        con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
        rec.Open("select uname from test2", con, CursorTypeEnum.adOpenStatic)

        Do Until rec.EOF
            Debug.WriteLine(rec("uname").Value)
            rec.MoveNext()
        Loop

        rec.Close()
        con.Close()

    End Sub

End Class

[Reverend Jim]

And the SQLConn version is (to the best of my limited knowledge with non ADO access)

Imports System.Data.SqlClient

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim rdr As SqlDataReader

        con.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True"
        con.Open()

        cmd.Connection = con
        cmd.CommandText = "select uname from test2"

        rdr = cmd.ExecuteReader()

        Do While rdr.Read()
            Debug.WriteLine(rdr.GetString(0))
        Loop

        rdr.Close()
        con.Close()

    End Sub

End Class

All in all I prefer the ADO version. As I understand it, ADO lies underneath the other intrfaces anyway so you are using it whether you know it or not.

[Stuugie]

Thanks Reverend Jim for that. There always seem to be many different ways to approach a problem which is good! I'm going to give your idea a go for learning purposes.