Absolute Beginner Question

Hi,

My apologies if this is a simple question but I'm having a hard time making
any progress so...

I'm looking at VB ADO.net, moving from ADO / Access development.

I've created a bog standard windows app in VB2005 Express and having read
some of the basics am attempting to create a connection to a SQLS2000 db on
my network and display the contents of a table in a listbox on a my form.

As I understand it, the steps involved are:
* create a connection to the database (done, I think)
* create a command object containing the action that i wish to perform
(SELECT statement)
* execute the command object and return the reults set to a DataReader
object as read-only which I can use to populate the listbox on my form.

I can create the connection and the command object but I'm not quite sure
where the datareader object comes in and how exactly I use it...

Please see code below.

I appreciate that this is a very simple question, however any and all advice
is gratefully received - be that links, pointers or so on.

Thanks

Chris.

**************************
Imports System.Data.SqlClient

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim TLCConn As SqlClient.SqlConnection
Dim contCMD As SqlClient.SqlCommand

TLCConn = New SqlConnection
TLCConn.ConnectionString = _
"Data Source=SQL;Integrated Security=SSPI;Initial
Catalog=TLCBackup"
TLCConn.Open()

contCMD = New SqlCommand
contCMD.Connection = TLCConn
contCMD.CommandText = "SELECT MovementNo, ContainerNo, DateIn,
DateOut " & _
"FROM Stock " & _
"WHERE dateout is null " & _
"ORDER BY MovementNo"
End Sub
End Class

Stuart
Tue Nov 29 09:26:25 CST 2005

Dim reader as SqlDataReader

..
..

contCMD.Connection = TLCConn
contCMD.CommandText = "SELECT MovementNo, ContainerNo, DateIn, DateOut "
& _
"FROM Stock " & _
"WHERE dateout is null " & _
"ORDER BY MovementNo"

reader = contCMD.ExecuteReader()
Dim text As String

While reader.Read()
text = text + reader(0) + vbCrLf
End While

End Sub

Note: this is an example of reading the first column "reader(0)". You
should handle nulls and also should use StringBuilder for larger amounts of
concatenation. This serves only as an example.


"Chris Strug" <hotmail@solace1884.com> wrote in message
news:uQOasAP9FHA.2576@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> My apologies if this is a simple question but I'm having a hard time
> making any progress so...
>
> I'm looking at VB ADO.net, moving from ADO / Access development.
>
> I've created a bog standard windows app in VB2005 Express and having read
> some of the basics am attempting to create a connection to a SQLS2000 db
> on my network and display the contents of a table in a listbox on a my
> form.
>
> As I understand it, the steps involved are:
> * create a connection to the database (done, I think)
> * create a command object containing the action that i wish to perform
> (SELECT statement)
> * execute the command object and return the reults set to a DataReader
> object as read-only which I can use to populate the listbox on my form.
>
> I can create the connection and the command object but I'm not quite sure
> where the datareader object comes in and how exactly I use it...
>
> Please see code below.
>
> I appreciate that this is a very simple question, however any and all
> advice is gratefully received - be that links, pointers or so on.
>
> Thanks
>
> Chris.
>
> **************************
> Imports System.Data.SqlClient
>
> Public Class Form1
>
> Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
>
> Dim TLCConn As SqlClient.SqlConnection
> Dim contCMD As SqlClient.SqlCommand
>
> TLCConn = New SqlConnection
> TLCConn.ConnectionString = _
> "Data Source=SQL;Integrated Security=SSPI;Initial
> Catalog=TLCBackup"
> TLCConn.Open()
>
> contCMD = New SqlCommand
> contCMD.Connection = TLCConn
> contCMD.CommandText = "SELECT MovementNo, ContainerNo, DateIn,
> DateOut " & _
> "FROM Stock " & _
> "WHERE dateout is null " & _
> "ORDER BY MovementNo"
> End Sub
> End Class
>
>

VenkatKL
Tue Nov 29 09:52:20 CST 2005

Hi Dear Chris Strug,

here is one sample:

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


Dim oSQLConn As SqlConnection = New SqlConnection
oSQLConn.ConnectionString = "Data Source=(local);Initial
Catalog=pubs;Integrated Security=SSPI;"
Dim oSQLCommand As SqlCommand
Try
If oSQLConn.State = ConnectionState.Closed Then
oSQLConn.Open()
End If

oSQLCommand = New SqlCommand("Select * from Authors", oSQLConn)

Dim oSQLDataReader As SqlDataReader
oSQLDataReader = oSQLCommand.ExecuteReader

While (oSQLDataReader.Read())

Me.ListBox1.Items.Add(oSQLDataReader("au_lname") & " " &
oSQLDataReader("au_fname"))
End While
Catch

Finally

oSQLCommand.Dispose()

If oSQLConn.State = ConnectionState.Open Then
oSQLConn.Close()
''oSQLConn.Dispose()
End If

End Try

End Sub


here are some useful links
==================

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassConnectionStringTopic.asp


http://support.microsoft.com/default.aspx?scid=kb;en-us;313482

Chris
Wed Nov 30 03:52:21 CST 2005

Many thanks for the replies gents - thats cleared up a few things.

Next step is getting my head around the data adapter and dataset objects!

Thanks again

Chris