Newbie Database Question 2

[zmcanally]

I apologize if this question has been asked before, but I did not see it anywhere. I am trying to create a login webform in ASP.NET. The user information is stored in a Sybase database (tblLogin). I need some help creating the login script. Here's what I need help with:

Private Function CheckLogin()
SiteID = txtSiteID.Text
Password = txtPassword.Text
Dim dbPassword
Dim strSql As String
strSql = "Select * From tblLogin Where Client_ID = " & SiteID & ""
Dim connLogin As Odbc.OdbcConnection = New Odbc.OdbcConnection("DSN=SybaseTest;UID=dba;PWD=SQL&quot
connLogin.Open()
Dim adapterLogin As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(strSql, connLogin)
Dim TestDataSet As DataSet = New DataSet
adapterLogin.Fill(TestDataSet, "Password&quot
dbPassword = TestDataSet.Tables("Password&quot.Rows(0)("Password&quot

If Password = dbPassword Then
'redirect to main screen
else
'msgbox stating bad password
End If
End Function

This function works fine if the site id is inthe database, but if it is not, it blows up. I'm sure there is a simple answer (and probably a simpler way of verifying the login information) - Thanks

Zack

 

[bradlkm]

Zack,

You simply need a way to determine if there are any rows in the dataset (before trying to access the row at index 0). You can do this by putting an if statement in your code like this:

If TestDataSet.Tables("Password&quot.Rows.Count > 0 then
dbPassword = TestDataSet.Tables("Password&quot.Rows(0)("Password&quot

If Password = dbPassword Then
'redirect to main screen
else
'msgbox stating bad password
End If
Else
'Error msgbox
End If


That being said, using a dataset to retrieve a single answer is definitely overkill. Why not do it using the "ExecuteScalar" functionality an OleDbCommand, like this:

SiteID = txtSiteID.Text
Password = txtPassword.Text
Dim dbPassword
Dim strSql As String
strSql = "Select * From tblLogin Where Client_ID = " & SiteID & ""
Dim connLogin As Odbc.OdbcConnection = New Odbc.OdbcConnection("DSN=SybaseTest;UID=dba;PWD=SQL&quot
dim passwordCmd as new OleDbCommand(strSql, connLogin)
connLogin.Open()
dbPassword = cStr(passwordCmd.ExecuteScalar() & "&quot
connLogin.Close

HTH!



Kevin B.
.Net Programmer

 

[zmcanally]

Kevin - Thanks for the quick response. I changed the code to use the ExecuteScalar function for ODBC commands (not OLE DB) and it worked great if the site id exists in the database, but how would I determine if there was a record or not with it?

 

[bradlkm]

You could do this by simplying seeing if the "dbPassword" variable is an empty string:

If dbPassword = "" then
'it is an empty record
Else
'there is a password
End If

That being said, you probably noticed in my code that I did a string conversion on the result appended to an empty string:

dbPassword = cStr(passwordCmd.ExecuteScalar() & "&quot

This is because an empty record being returned does not return an empty string, but instead a "DBNull" value. Therefore if you changed the code to this:

dbPassword = passwordCmd.ExecuteScalar()

Then you would check if the record is empty in this way:

if not IsDBNull(dbPassword) then
'it is an empty record
Else
'there is a password
End If

I hope that makes sense. If you are still confused, let me know!


Kevin B.
.Net Programmer

 

[zmcanally]

Kevin - That got me going in the right direction - all is well now. Thanks again for your help

Zack