logon script using executescalar

[paulhudson]

I have a logon page which has three textboxes. On clicking submit the following procedure is called.

 

Needless to say it fails. Essentially I am trying to count a number of records that match username / password etc from within an access database. If the count is one then the logon is a success. Perhaps there is a better way of doing this?

 

(PS what tags do you use to distinguish code in posts to this forum?)

The problem occurs here:

Count = Convert.ToInt32(oCommand.ExecuteScalar())

With the following error message "Data type mismatch in criteria expression"

 

Here is the code:

 

sub Submit(obj as Object, e as EventArgs)
Dim RedirectPage As String
If tbTeam.text = "Club" then
	RedirectPage = "ClubAdmin.aspx"
Else
	RedirectPage = "TeamAdmin.aspx"	
End If

Dim sSQL = "select count(*) from tbusers where username='" & tbUsername.text & "' AND password='" & tbPassword.text & "' AND TeamType='" & tbTeam.text & "';"

Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString"))

Dim oCommand As New OleDbCommand(sSQL, oConnect)

Dim Count As Int32

Count = "0"

oConnect.Open()

Count = Convert.ToInt32(oCommand.ExecuteScalar())

oConnect.Close()

Dim LoginCount As Label

LoginCount.Text = Count.ToString()

If LoginCount.Text = "1" Then
	Session("Authorized") = "yes"
	Session("TeamType") = tbTeam.Text
	Session("Username") = tbUsername.Text
Else
 		lblmessage.text = "Please verify your login information."
End If
End Sub

EDIT: added vb tags - see here for code formatting details.

Edited February 1, 2005 by PlausiblyDamp

[karthikeyan_r01]

I am not sure whether you could use a semicolon at the end of the SQL Command but anyway this piece of code works fine for me. I tested this using the following code

 

Dim RedirectPage As String

Dim sSQL = "select count(*) from SRVPROCSTATUS where SEQNO > 400"

Dim oConnect As New OleDbConnection("Provider=msdaora;Data Source=MyData;User Id=me;Password=test;")

Dim oCommand As New OleDbCommand(sSQL, oConnect)

Dim Count As Int32

Count = "0"

Try

oConnect.Open()

Count = Convert.ToInt32(oCommand.ExecuteScalar())

oConnect.Close()

Catch ex As Exception

console.writeline("Error : "+ex.Message)

End Try

[tate]

What is the purpose of the RedirectPage string variable? I don't see it referenced in the code anywhere?

 

Normally I use the RedirectPage that is part of the Authentication object.

[karthikeyan_r01]

Actually I did not implement the entire functionality of the issue. The database access alone was tried. The rest is functioning well and I thought the logic need not be implemented for that.

 

--

Karthikeyan Raghuraman

[paulhudson]

Tate,

 

This is the code with redirectpage:

 

sub Submit(obj as Object, e as EventArgs)
   Dim RedirectPage As String
   If tbTeam.text = "Club" then
       RedirectPage = "ClubAdmin.aspx"
   Else
       RedirectPage = "TeamAdmin.aspx"    
   End If
   
   Dim sSQL = "select count(*) from tbusers where username='" & tbUsername.text & "' AND password='" & tbPassword.text & "' AND TeamType='" & tbTeam.text & "';"
   
   Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString"))
   
   Dim oCommand As New OleDbCommand(sSQL, oConnect)

   Dim Count As Int32

   Count = "0"

   oConnect.Open()

   Count = Convert.ToInt32(oCommand.ExecuteScalar())

   oConnect.Close()

   Dim LoginCount As Label

   LoginCount.Text = Count.ToString()

   If LoginCount.Text = "1" Then
       Session("Authorized") = "yes"
       Session("TeamType") = tbTeam.Text
       Session("Username") = tbUsername.Text
       Response.redirect(redirectpage)
   Else
         lblmessage.text = "Please verify your login information."
   End If
End Sub 

[paulhudson]

Without the semi-colon it still did not work.

 

Regards

[karthikeyan_r01]

yeah I am able to simulate your error.

There is a problem with one of the values in the where clause.

Something like where there should be an integer you have put string or something vice versa.

Ex:

I used this sql on table TestTable

(-UserID Number

,-UserDesc text)

"select count(*) from TestTable where userdesc='KK' and userid > '10';"

Here I changed the user id value from no to text. Then I got the error u had stated earlier.

So the problem shud lie somewhere in those lines.

 

Reg the semicolon thing in my previous post , I am sorry i overlooked your question and was working with Oracle tables.

 

--

Karthikeyan Raghuraman

[Optikal]

What is the datatype of username, password and TeamType fields as defined in your database table? I'm guessing one of them is defined as numeric when it should be a string.