Wrox Programmer Forums
|
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 18th, 2005, 09:24 PM
Authorized User
 
Join Date: Apr 2005
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default About SQL command

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]

 
Old May 18th, 2005, 10:44 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 
Old May 18th, 2005, 11:08 PM
Authorized User
 
Join Date: Jul 2004
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



 
Old May 19th, 2005, 01:33 AM
Authorized User
 
Join Date: Apr 2005
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old May 19th, 2005, 02:26 AM
Authorized User
 
Join Date: Jul 2004
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)


 
Old May 19th, 2005, 03:46 AM
Authorized User
 
Join Date: Apr 2005
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

 
Old May 19th, 2005, 07:13 AM
Authorized User
 
Join Date: Jul 2004
Posts: 88
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What error are you getting?

 
Old May 19th, 2005, 07:12 PM
Authorized User
 
Join Date: Apr 2005
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old May 19th, 2005, 07:31 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Sql Command payammoradshahi BOOK: Beginning C# 2005 Databases 0 November 26th, 2006 11:28 AM
Sql command for query prashar SQL Language 0 December 12th, 2005 10:10 AM
executing sql command redface Crystal Reports 0 April 6th, 2005 05:14 PM
SQL "where" command question module0000 VB Databases Basics 7 October 5th, 2004 12:46 PM
Sql Command hanzahalka ADO.NET 0 July 8th, 2003 09:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.