DataReader Trouble

[LobsterSte]

I am stuck on a problem where I have a select statment, but the datareader can't get the string If there is no record in the table with the same intCID.

Here IS My Code:

Private Sub testPin(ByVal UserID As Integer, ByVal Password As String)
Try
Dim cmd As New SqlCommand

cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT dbo.tblCustomers.intPIN, dbo.tblCustomers.strFName, dbo.tblCustomers.strLName, dbo.tblRent.strProcess FROM dbo.tblCustomers INNER JOIN dbo.tblRent ON dbo.tblCustomers.intCID = dbo.tblRent.intCID Where dbo.tblCustomers.intCID =" & UserID & ""

cnn.Open()
cmd.Connection = cnn

Dim dr As SqlDataReader = cmd.ExecuteReader
Try
While dr.Read
If dr.GetInt32(0) = Password Then

txtFName.Text = dr.GetString(1)
txtLName.Text = dr.GetString(2)

Try
If dr.GetString(3) = "Complete" Then
UserClear = True
Else
UserClear = False
End If
Catch ex As Exception
MsgBox("ASS")
End Try
Else
ErrorProvider1.SetError(txtCustPin, "Customer Pin Wrong!")
End If

End While

dr.Close()
cnn.Close()
Catch ex As Exception
MsgBox("User Pin: " & ex.Message)
Finally
If UserClear = False Then
MsgBox("Customer Still Has Items!", MsgBoxStyle.Exclamation, "Customer")
btnFindDVD.Enabled = False
ClearForm()
Else
btnFindDVD.Enabled = True
MsgBox("Customer Credentials Checked Sucessfull", MsgBoxStyle.Information, "Customer Credentials")
End If
End Try
Catch ex As Exception
MsgBox(ex.Message)

End Try
End Sub




Any Ideas?

 

[mansii]

I use this when pulling out values from datareader:

Dim s1, s2 as String
While dr.Read
    s1 = dr(0)
    s2 = dr(1)
End While

or

Dim s1, s2 as String
While dr.Read
    s1 = dr("Field1")
    s2 = dr("Field2")
End While

Hope this helps.

 

[djj55]

You state

can't get the string If there is no record in the table with the same intCID

Are you asking how to handle no data? See HasRows for checking.
djj

 

[PGoRule]

I didn't get that point too...Which string?
Check the record count returned by your query. If the record count is > 0 then your code will display them. If it's = 0 then display some string that will say 'No record for this intCID'...
As DJJ suggested, you can implement HasRows to check whether you have any data or not...

Sharing the best from my side...

--Prashant--

 

[chrissie1]

I think you have some problems with your code.

One: a try catch block should never have that much code in it, it is not a replacement for on error goto.

Two: You should use parameters.

Three: use the hasrows like the other posters say.

Four: Use Massagebox.show instead of msgbox

Five: You should make a method somewhere that does the opening and closing of a connection so you don't have to write all this code hundreds of times.


So

Private Sub testPin(ByVal UserID As Integer, ByVal Password As String)
            Dim cmd As New SqlCommand

            cmd.CommandType = CommandType.Text
            cmd.parameters.add("@userid",sqldbtype.integer)
            cmd.parameters(0).value = UserId
            cmd.CommandText = "SELECT dbo.tblCustomers.intPIN, dbo.tblCustomers.strFName, dbo.tblCustomers.strLName, dbo.tblRent.strProcess FROM dbo.tblCustomers INNER JOIN dbo.tblRent ON dbo.tblCustomers.intCID = dbo.tblRent.intCID Where dbo.tblCustomers.intCID = @UserID"

            Try
               cnn.Open()
            Catch ex As Exception
               MessageBox.show(ex.Message)
               Exit sub
            End Try
            cmd.Connection = cnn
            Dim dr As SqlDataReader = cmd.ExecuteReader
            if dr.hasrows then  
                While dr.Read
                    If dr.GetInt32(0) = Password Then
                        txtFName.Text = dr.GetString(1)
                        txtLName.Text = dr.GetString(2)
                        If dr.GetString(3) = "Complete" Then
                           UserClear = True
                        Else
                            UserClear = False
                        End If
                    Else
                        ErrorProvider1.SetError(txtCustPin, "Customer Pin Wrong!")
                    End If
                End While
            end if
            If UserClear = False Then
                MessageBox.show("Customer Still Has Items!", MsgBoxStyle.Exclamation, "Customer")
                 btnFindDVD.Enabled = False
                 ClearForm()
             Else
                 btnFindDVD.Enabled = True
                 MessageBox.show("Customer Credentials Checked Sucessfull", MsgBoxStyle.Information, "Customer Credentials")
             End If
             dr.Close()
             cnn.Close()
    End Sub

Christiaan Baes
Belgium

"My new site" - Me

 

[djj55]

Christiaan, what is the difference between using msgbox and messagebox? I know how to use both, but do not know why messagebox is better.
Thanks
djj

 

[chrissie1]

It's in a different namespace and you will be sure that it will be there in 2007 while msgbox might be gone one day. For the rest I think it's the same. Somebody should check the IL they create to see if they really are.

Christiaan Baes
Belgium

"My new site" - Me

 

[djj55]

Thanks, I thought it might be something like that.
djj