datarows not returning anything

[ldCycler]

I am a beginner in the .net environment but have background in programming. I have the following code and the dataset does not return any rows. When I do a count of rows after dataset is filled it shows 3 rows (the number in the table. Can anyone see anything wrong?
Thank you

Option Strict On
Imports System.Data.OleDb

Public Class PasswordForm
Dim sUser As String

Private objconn As OleDbConnection
Private ds As DataSet
Private da As OleDb.OleDbDataAdapter
Private bldrCommandBuilder As OleDb.OleDbCommandBuilder
Private bndBindingSource As BindingSource

Private Sub passwordform_Load(ByVal sendera As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load

End Sub

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
Dim sPassword As String = Me.txtPassword.Text
sUser = txtUserName.Text

Dim strPath, sConnectionString As String
Dim selectStatement As String _
= "Select * FROM tblPasswords"

strPath = "C:\Documents and Settings\Wally\Desktop\Index Project VB\IndexRevised.mdb"
sConnectionString = "Provider=Microsoft.jet.oledb.4.0;Data Source=" & strPath

Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)

'initialize the dataadapter
da = New OleDb.OleDbDataAdapter(selectStatement, objConn)

bldrCommandBuilder = New OleDb.OleDbCommandBuilder(da)

'create new dataset
ds = New DataSet("Passwords")

'fill the dataset with data from the Passwords
da.Fill(ds, "Passwords")

'count rows in dataset
Dim intCount As Integer
intCount = ds.Tables("Passwords").Rows.Count
MessageBox.Show(CStr(intCount))

'set up the binding source - which handles the navigation issues
bndBindingSource = New BindingSource

Using programming method for dataset
Dim rows() As DataRow = ds.Tables("Passwords").Select("UserName = '" & sUser & _
"Password = " & sPassword & "'")
If rows.Length <> 0 Then 'we have a value it is not blank
Dim s As String = CStr(rows(0)("User"))
If s = "Admin" Then
'open form
Else
'open form
End If
End If

End Sub

 

[jbenson001]

You say you have 3 rows returned from your query. But have you checked the rowcound after you execute the .Select() method on the table in the dataset? My guess is that no rows are returned because no rows meet your critera.

Jim

 

[ldCycler]

After the SQL statement I do not get any row count. I simplified the data in the table and the username and password are both "S". When I process the SQL statement the variables read "S".
Any other thoughts?

 

[woogoo]

I'm no expert on this but shouldn't this line :

Dim rows() As DataRow = ds.Tables("Passwords").Select("UserName = '" & sUser & "Password = " & sPassword & "'")

be

Dim rows() As DataRow = ds.Tables("Passwords").Select("UserName = '" & sUser & "' Password = '" & sPassword & "'")

With that said I'd be inclined to prepare my select statement first using the credentials passed and then execute my select statement. If I get a result I have a valid user I can then access other values of the returned result i.e. Full Name, eMail etc.

HTH

 

[jbenson001]

You need and AND in your .Select method

.Select("UserName = '" & sUser & _
                "[b]AND[/b] Password = " & sPassword & "'")