«

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.

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" <> wrote in message
news:%...
> 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.
>
>
>

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


"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:...
> 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" <> wrote in message
> news:%...
> > 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.
> >
> >
> >
>
>

Kark,

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


"bienwell" <> wrote in message
news:%...
> 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
>
>
> "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
> wrote in message news:...
> > 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" <> wrote in message
> > news:%...
> > > 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.
> > >
> > >
> > >
> >
> >
>
>

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" <> wrote in message
news:%...
> 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
>
>
> "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
> wrote in message news:...
>> 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" <> wrote in message
>> news:%...
>> > 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,

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.

"Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:...
> 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" <> wrote in message
> news:%...
> > 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
> >
> >
> > "Karl Seguin" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME net>
> > wrote in message news:...
> >> 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" <> wrote in message
> >> news:%...
> >> > 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.
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>