About SQL command

hoailing22 · May 18th, 2005, 09:24 PM

Hi,

In the 2nd line of the Function, the sql command
        SqlCommand1.CommandText = "SELECT * FROM [User]"
     "WHERE
         User_id = '" + UserID + "'" have error.

I still can't get what is the error, can somebody point it out?

Thanks!

Irene

Protected Function EnsureUnique(ByVal UserID As String) As Boolean
        SqlConnection1.Open()
        SqlCommand1.CommandText = "SELECT * FROM [User]"
        "WHERE
             User_id = '" + UserID + "'"

  SqlDataAdapter1.SelectCommand = SqlCommand1
        SqlDataAdapter1.Fill(DsUser1, "User")
        SqlConnection1.Close()

        If (DsUser1.Tables(0).Rows.Count = 0) Then
            DsUser1.Reset()
            Return True
        Else
            DsUser1.Reset()
            Return False
        End If
    End Function
[/code]

planoie · May 18th, 2005, 10:44 PM

This string concatenation:

"SELECT * FROM [User]"
        "WHERE
             User_id = '" + UserID + "'"

will result in this string:

SELECT * FROM [User]WHERE
             User_id = '<userid>'

There is no space before the WHERE. That is breaking the query.

-Peter

spacy · May 18th, 2005, 11:08 PM

What error are you getting? It would be easier to
help you if you specify the error.

Few things to consider about the above code:
1) SqlCommand1.CommandText = "SELECT * FROM [User]"
    "WHERE User_id = '" + UserID + "'"
    needs to be written as..

   SqlCommand1.CommandText = "SELECT * FROM User WHERE User_id = '" + UserID + "'"

2) You have not specified the connection for the command object.
    SqlCommand1.Connection = SqlConnection1

Cheers
Spacy

hoailing22 · May 19th, 2005, 01:33 AM

I solved that problem already, using spacy's method. Thanks!

But now I face problem in the Login Page, which the connection to database hv problem. Even though I use correct id n password, still cnt login. Can help me check out?

Code:

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click

        Dim con As SqlConnection
        Dim sql As String
        Dim cmd As SqlCommand
        Dim id As String
        Dim Administrator As String
        Dim FirstTime As String

        con = New SqlConnection( _
             "data source=(local)\NetSdk; initial    catalog=E-Library Records; user id=sa")
        sql = "SELECT User_id FROM [User] WHERE IsFirstTime='{0}' and Password='{1}'"
        sql = String.Format(sql, txtUserID.Text, txtPassword.Text)
        cmd = New SqlCommand(sql, con)
        con.Open()

        Try
            id = CType(cmd.ExecuteScalar(), String)

        Catch ex As Exception

        Finally
            con.Close()
        End Try

        If Not id Is Nothing Then
            If CType(Context.User.IsInRole(Administrator), Boolean) Then
                Response.Redirect("Admin1.aspx")
            Else
                If Context.User Is FirstTime Then
                    Response.Redirect("UserPassword.aspx")
                Else
                    Response.Redirect("mainMenu.aspx")
                End If
            End If
        Else
            ValidationSummary1.Visible = True
        End If
    End Sub

my User Table is as below:

User_id User_Password IsFirstTime IsAdministrator
admin1 elibsys 0 1

Note that IsAdministrator have default value 0. I not sure whether the problem is with IsFirstTime.

Thanks!

Irene

spacy · May 19th, 2005, 02:26 AM

1) sql = "SELECT User_id FROM [User] WHERE IsFirstTime='{0}' and Password='{1}'"
    Password should be replaced with User_Password

2)
    Line1 : sql = "SELECT User_id FROM [User] WHERE IsFirstTime='{0}' and Password='{1}'"
    Line2 : sql = String.Format(sql,txtUserID.text, txtPassword.Text)

    In Line 1, {0} and {1} are placeholders, which will be replaced by values that you have passed
    in the String.Format
    Line 2 can be interpreted as :
        sql=String.Format(sql,argument0,argument1)

   Whatever value is passed in argument0 (txtUserID.text) will replace {0} in sql (Line1)
   Similarly, the password entered in txtPassword will replace {1}

You probably will have to change the code to:
sql = "SELECT User_id FROM [User] WHERE IsFirstTime=0 and Password='{0}'"
sql = String.Format(sql,txtPassword.Text)

hoailing22 · May 19th, 2005, 03:46 AM

I try out ur method, but the error message still displayed. I suppose can go to admin1 page.

Is it the problem may in the condition checking section to redirect user to another page?
My mechanism is like this:
1) check is user authorised to go to the system
2) if yes, check the id is admin o user id
3) if admin, redirect to admin1 page
4) if user, check is it 1st time login
5) if yes, redirect to UserPasssword page
6) Else, go to main menu page

Thanks!

spacy · May 19th, 2005, 07:13 AM

What error are you getting?

hoailing22 · May 19th, 2005, 07:12 PM

I mean The error message that i set to display if user enter wrong user id or password, will display even though i enter correct user id n password.

there is no syntax error, but maybe is the logical error.

planoie · May 19th, 2005, 07:31 PM

You are validating the user login information thru the database, but you aren't doing anything with it. You need to do something that establishes that the user is actually logged in. If you are using forms authentication you need to call one of the methods that sets the forms authentication cookie. If you aren't using it, then you need to establish your own login state that you check manually.

-Peter