DataReader...HELP!

[brendalisalowe]

I am trying to access data in SQL Server and pull it into the form. I am somewhat new at this. Can anyone help me out? I have one form open and you type in the case number. or lastname. Then I want it to be able to pull in data into another form. PLEASE HELP! This is what I have so far:

SqlConn.Open()
 
        Dim drSqlDataReader1 As SqlDataReader = cmdSqlCommand1.ExecuteReader()
        drSqlDataReader1.Read()
         Dim search As String
 
        If IsNumeric(txtSearchCase.Text) Then
            search = "SELECT * FROM CapRec WHERE CaseNumber LIKE '%" & txtSearchCase.Text & "%';"
 
            'txtCaseNum.Text = drSqlDataReader1.Item(0)
 
        Else
            search = "SELECT * FROM CapRec WHERE LastName LIKE '%" & txtSearchCase.Text & "%';"
        End If

[nemaroller]

Step one is to define a Connection object.
Step two is to define a Command object.
Step three is to define a Reader object.
Step four is to call the Open method of the Connection object
Step five is to call ExecuteReader on the Command object, and assigning its output to the Reader object.
Step six is to keep reading the datareader pipe until it is exhausted
Step seven, close the connection.

With a reader object, like SqlDataReader, each time you call the Read() method, the reader advances one record in its pipe of records, and assigns that record as the current record available to retrieve data from.

So after calling Read(), you can get a value out of the current assigned record, by using GetValue. But I never use GetValue for the most part, because the datareader class has a method that allows you use shorthand to retrieve the data in the column you specify ( for example: me.text = dr(0) ).

In closing, when the Read method return false, its had read all the available records returned to it by your Command object.

So, here's the code that backs this up:

'here's your code
 If IsNumeric(txtSearchCase.Text) Then
            search = "SELECT * FROM CapRec WHERE CaseNumber LIKE '%" & txtSearchCase.Text & "%';"
 
            'txtCaseNum.Text = drSqlDataReader1.Item(0)
 
        Else
            search = "SELECT * FROM CapRec WHERE LastName LIKE '%" & txtSearchCase.Text & "%';"
        End If
 
Dim SqlConnection1 As New SqlConnection(myConnectionString)
Dim SqlCommand1 As New SqlCommand
Dim dr As  SqlDataReader
SqlCommand1.Connection = SqlConnection1
SqlCommand1.CommandText = search 
SqlCommand1.CommandType = CommandType.Text
 
'open the data connection
SqlConnection1.Open
'assign the results of SqlCommand1's execute reader method to the datareader
dr= SqlCommand1.ExecuteReader()
 
'I always test for rows
If dr.HasRows Then
    'the following line will keep executing the Read method as long as there is more records to read
   While dr.Read  
      'FieldCount returns how many columns are in the current row
      'I'm just using it here to dump your returned data to the
      'console window
       For i As Integer = 0 to dr.FieldCount-1 
        'Here I am testing for DbNull.Value, making sure there is actual
        'data in the specific column index for the current record
        '  and writing the values to the Visual Studio Output window
         If Not dr(i) is DbNull.Value Then Debug.WriteLine(dr(i))
      Next
   End While
End If
'close up
dr.Close
SqlConnection1.Close

[JPicasso]

Nicely put nema,

I'm new to this .Net stuff and while I've been able to muddle my way through data access,
spelling it out that way helps a bit.

[brendalisalowe]

I am far from expert at this stuff. Do you know how to return your data to a different for then the one you used to search? My "Hasrows" is not working either. Does anyone know why? Here is my coding so far:

Dim SqlConn As New SqlConnection("data source=localhost;initial catalog=crdatabase;integrated security=SSPI;persist security info=False;workstation id=CRSERVER;packet size=4096")
        Dim cmdSqlCommand1 As SqlCommand = New SqlCommand("SELECT * FROM tblHUDdata", SqlConn)
 
        'Opens the DataReader to read data from the tblHUDdata table
        SqlConn.Open()
        Dim drSqlDataReader1 As SqlDataReader = cmdSqlCommand1.ExecuteReader()
        drSqlDataReader1.Read()
        Dim search As String
 
        If IsNumeric(txtSearchCase.Text.Substring(0)) Then
            search = "SELECT * FROM CapRec WHERE CaseNumber LIKE '" & txtSearchCase.Text & "%';"
 
            'txtCaseNum.Text = drSqlDataReader1.Item(0)
 
        Else
            search = "SELECT * FROM CapRec WHERE LastName LIKE '" & txtSearchCase.Text & "%';"
        End If
 
        Dim dr As SqlDataReader
        Dim index As Integer
        cmdSqlCommand1.Connection = SqlConn
        cmdSqlCommand1.CommandText = search
        cmdSqlCommand1.CommandType = CommandType.Text
 
        If drSqlDataReader1.HasRows Then
            While drSqlDataReader1.Read
                For index = 0 To drSqlDataReader1.FieldCount - 1
                    If Not drSqlDataReader1(index) Is DBNull.Value Then Debug.WriteLine(drSqlDataReader1(index))
                Next
            End While
        End If
        'close up
        drSqlDataReader1.Close()
        SqlConn.Close()

[nemaroller]

Thanks J....

Brenda...

Dim cmdSqlCommand1 As SqlCommand = New SqlCommand("SELECT * FROM tblHUDdata", SqlConn)
 
        'Opens the DataReader to read data from the tblHUDdata table
        SqlConn.Open()
        Dim drSqlDataReader1 As SqlDataReader = cmdSqlCommand1.ExecuteReader()
        drSqlDataReader1.Read()
        Dim search As String

This part of your code does not do anything useful as far as I can tell. Use the code exactly as I posted earlier instead. you have declared a datareader called 'dr', but don't use it at all. Further, you are using cmdSqlCommand1 again at the bottom, but never used the ExecuteReader method on it, so you won't get any results.

Also note; Connections can only have ONE datareader assigned at any time. And once they are read, they are exhausted, unless you execute another command, and assign its output back to the same reader.

Again, use the code I posted earlier verbatim, with inclusion of assigning your connectionstring to myconnectionstring.