Question about ExecuteScalar() function in ASP.NET

[bienwell]

Hi all,

I have a problem with using myCommand.ExecuteScalar(). My question is :
If the Web setup is incorrect, does it make command ExecuteScalar() work
improperly ?? In my program, I was using ExecuteScalar() to count the
number of records from the query. The return value is always 0. If number
record is 0 then it will go the Page1.aspx page by (response.redirect) . If
not, it will go to Page2.aspx page. The result is it's always go to
Page1.aspx page.

Do you have any ideas ? Thanks in advance.

 

[Karl Seguin]

Without providing any code, it's hard to guess at what the problem could be.
My guess is that your SQL is simply incorrect.

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[bienwell]

Kark,

Please take a look on my code. With this code in the first Web server, the
page redirected is correct ("FileUpload.aspx"); it means ExecuteScalar()
works fine. In another server, it redirect to wrong page
"PasswordInfo.aspx" .

(In my table, INITIAL_LOGIN=1 ==> user didn't change the intitial password
, INITIAL_LOGIN=0 ==> user already changed password and login again. The
value of initial password is 0 now )


==========================================================================
Private Sub CheckUser_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles CheckUser.Click

Dim Enc As String = EFiling_Funct.EncryptString(txtPassword.Text)
Dim myStr As String = "SELECT Count(*) FROM MyTable WHERE
INITIAL_LOGIN=1 AND Account_ID = '" & txtUserName.Text & "'" & _
" AND Password = '" & Enc &
"'"
On Error Resume Next
myConnection = New
OdbcConnection(ConfigurationSettings.AppSettings("strConn"))
myConnection.Open()
If Err.Number <> 0 Then
Session("Fail_Over") = 1
myConnection = New
OdbcConnection(ConfigurationSettings.AppSettings("strConn2"))
myConnection.Open()
End If

myCommand = New OdbcCommand(myStr, myConnection)
If myCommand.ExecuteScalar() = 1 Then 'FIRST TIME TO CHANGE
PASSWORD
myConnection.Close()
Session("UserID") = txtUserName.Text
Session("OldPassword") = txtPassword.Text
Response.Redirect("PasswordInfo.aspx") 'Change Password
Else 'USER LOGIN WITH RESET PASSWORD
Dim myStr2 As String = "SELECT Count(*) FROM tbl_EFiling_Users
WHERE INITIAL_LOGIN=0 AND Account_ID = '" & txtUserName.Text & "'" & _
" AND Password = '" & Enc & "'"
myCommand = New OdbcCommand(myStr2, myConnection)
If myCommand.ExecuteScalar() = 1 Then
myConnection.Close()
Session("UserID") = txtUserName.Text
Session("OldPassword") = txtPassword.Text
Response.Redirect("FileUpload.aspx") 'Upload File
Else
myConnection.Close()
Label_Conn.Text = " Wrong User name AND | OR Password .
Please enter again !. "
If Me.LblHold.Text > "" Then
Me.LblHold.Text = Me.LblHold.Text + 1
If Me.LblHold.Text > 2 Then
Label_Conn.Text = "Login has been failed for 3
times. Please try again ! "
Response.Redirect("Bye.aspx")
End If
Else
Me.LblHold.Text = "1"
End If
End If
End If

End Sub

 

[bienwell]

Kark,

Just let you know that I used the same table name "MyTable" in myStr,
myStr2. Thanks

 

[Karl Seguin]

BienWell:
First off, you should turn Option Strict On in vb.net...the readability
and maintainability of your code would surely improve.
Secondly, you should use structured exception handling (try/catch) instead
of On Error Resume Next.
Thirdly, using Paramaters for your SQL query instead of creating a string.

It seems to me that your code could be easily rewriten to easily require
only 1 SQL call. The following code makes more sense to me, but I
obviously only see a part of your overall code (and I'll demonstrate the
above 3 points)..I didn't compile the code below, but it shoudl give you
some ideas....

dim commandText as string = "SELECT INITIAL_LOGIN FROM MyTable WHERE
Account_ID = @AccountId AND Password = @Password"
dim connection as new
OdbcConnection(ConfigurationSettings.AppSettings("strConn"))
dim command as new OdbcCommand(commandText, connection)
command.Parameters.Add("@AccountId", OdbcType.VarChar).Value =
txtUserName.Text
command.Parameters.Add("@Password", OdbcType.VarChar).Value = enc
try
connection.Open()
dim returnObject as object = command.ExecuteScalar()
if not returnObject is nothing andalso not returnObject is DbNull.Value
then
dim initialLogin as integer = cint(returnObject)
if initialLogin = 0 then
'user already changed password
else
'user hasn't changed password
end if
else
'invalid login
end if
finally
connection.Dispose()
command.Dispose()
end try


I understand that your initial problem was that it works on one server but
not another.I honestly don't have any good answers as to why that is.
Everything seems in order. Perhaps this cleaned up code will help.

karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[bienwell]

Karl,

Thank you for reviewing my code. My old code worked fine because they
forgot to create the DSN to the second server. That's why the same
application gave different output in 2 servers.