Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Question about ExecuteScalar() function in ASP.NET

Reply
Thread Tools

Question about ExecuteScalar() function in ASP.NET

 
 
bienwell
Guest
Posts: n/a
 
      05-24-2005
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.



 
Reply With Quote
 
 
 
 
Karl Seguin
Guest
Posts: n/a
 
      05-24-2005
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>
>
>



 
Reply With Quote
 
 
 
 
bienwell
Guest
Posts: n/a
 
      05-24-2005
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:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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.
> >
> >
> >

>
>



 
Reply With Quote
 
bienwell
Guest
Posts: n/a
 
      05-24-2005
Kark,

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


"bienwell" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > 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.
> > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Karl Seguin
Guest
Posts: n/a
 
      05-24-2005
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > 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.
>> >
>> >
>> >

>>
>>

>
>



 
Reply With Quote
 
bienwell
Guest
Posts: n/a
 
      05-27-2005
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:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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:(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >> > 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.
> >> >
> >> >
> >> >
> >>
> >>

> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function versus pointer to function, in context of std::function,huh? Alf P. Steinbach C++ 10 07-27-2011 05:51 AM
Function pointer to void function and int function Giannis Papadopoulos C Programming 5 09-05-2005 09:06 PM
How override ALL function calls? (Is there a "function call function"?) seberino@spawar.navy.mil Python 2 08-01-2005 12:38 PM
write a function such that when ever i call this function in some other function .it should give me tha data type and value of calling function parameter komal C++ 6 01-25-2005 11:13 AM
Passing a C++ object's member function to a C function expecing a function pointer! James Vanns C++ 7 01-21-2004 02:39 AM



Advertisments