Question about ExecuteScalar() function in ASP.NET

Discussion in 'ASP .Net' started by bienwell, May 24, 2005.

  1. bienwell

    bienwell Guest

    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, May 24, 2005
    #1
    1. Advertising

  2. bienwell

    Karl Seguin Guest

    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 Seguin, May 24, 2005
    #2
    1. Advertising

  3. bienwell

    bienwell Guest

    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, May 24, 2005
    #3
  4. bienwell

    bienwell Guest

    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, May 24, 2005
    #4
  5. bienwell

    Karl Seguin Guest

    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 Seguin, May 24, 2005
    #5
  6. bienwell

    bienwell Guest

    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.
    > >> >
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
    bienwell, May 27, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. buran

    executescalar() method

    buran, Jul 22, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    3,349
    Cowboy \(Gregory A. Beamer\)
    Jul 22, 2003
  2. Chad Dittmer via .NET 247

    problems inserting into sql using SqlHelper.ExecuteScalar

    Chad Dittmer via .NET 247, Sep 15, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    2,525
    Hermit Dave
    Sep 15, 2004
  3. Max
    Replies:
    6
    Views:
    3,290
  4. =?Utf-8?B?TWlrZSBLcmFqZXdza2k=?=

    Intermittent SQL corruption from ExecuteScalar

    =?Utf-8?B?TWlrZSBLcmFqZXdza2k=?=, Nov 17, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    419
    =?Utf-8?B?TWlrZSBLcmFqZXdza2k=?=
    Nov 19, 2004
  5. Neven Klofutar

    SqlHelper.ExecuteScalar

    Neven Klofutar, Dec 7, 2004, in forum: ASP .Net
    Replies:
    7
    Views:
    6,360
    Neven Klofutar
    Dec 8, 2004
Loading...

Share This Page