Updated : Simple ASP.Net Login Page - Page 4

Question

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

bagi 0 Newbie Poster · Answer 1 · 2006-03-03T19:25:54+00:00

Hi, when login successfully, how do I get the data from SQL Server based on the user's identity? (eg. User.Identity.Name or username) I need help in getting the user's data from SQL Server and display it on the webpage. What procedures to include?

Hello everybody, since i am novice am not good at .net...So i want to learn more about this.....You guys can help me i hope.....I have same problem what miragefigther.....Could anyone help me solve this????????? :?:

Miragefighter did you find out????????

please understand my poor english.......~~

i appreciate.....

Coconut 0 Newbie Poster · Answer 2 · 2006-03-09T06:11:49+00:00

Hello! I've been going through your code from a previous forum (http://www.daniweb.com/techtalkforums/thread6028.html). I'm trying to get a login page for myself to work. I was wondering if you could provide a simple explanation of this line

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="Login.aspx.vb" Inherits="NorthLogin.WebForm1"%>

This is in the Login.aspx.

I understand the page language and partially the code behind, but I'm not sure what the NorthLogin.WebForm1 is and why the page must inherit it! This is my real confusion and I'm sure it is quite simple, but I'm just not getting it. I think if I could understand this the rest of your code (with some tweaking) would fall right into place. Any help from anyone is much appreciated.
Also, feel free to email a response to ctblake@csustudent.net and put Inherits Help in the subject line.

Thanks. :cool: :!:

bigyeti 0 Newbie Poster · Answer 3 · 2006-03-10T23:35:29+00:00

Hi,
So far, this has been a great help, it checks username and password and redirects to the correct page.

However, when I came to this section,

Continue from the existing code in this tutorial and add the following.
Open the Global.asax file and view the code. This file contains many elements, and the one we are going to focus on is the Session_Start subroutine.
Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
		' Fires when the session is started
	End Sub

I'm getting the error

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 5: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Line 6: Response.Cache.SetCacheability(HttpCacheability.NoCache)
Line 7: If Session("Logged_IN").Equals("No") Then
Line 8: Response.Redirect("Login.aspx")
Line 9: End If

It's as if it's not acknowledging the global.asax

Am I missing something really simple here?

Thanks
Mark

technicalItch 0 Newbie Poster · Answer 4 · 2006-03-14T03:06:38+00:00

Hi

Could someone look at my thread about this login code http://www.daniweb.com/techtalkforums/showthread.php?p=196279#post196279

I'm stuck and feel someone experienced will be able to see the problem rightaway. :?:

Paladine 138 Master Poster Team Colleague · Answer 5 · 2006-04-03T00:35:46+00:00

What does your Session_Start section of your Global.asax code look like... that may help me determine your problem.

Hi,
So far, this has been a great help, it checks username and password and redirects to the correct page.
However, when I came to this section,
I'm getting the error
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 5: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Line 6: Response.Cache.SetCacheability(HttpCacheability.NoCache)
Line 7: If Session("Logged_IN").Equals("No") Then
Line 8: Response.Redirect("Login.aspx")
Line 9: End If
It's as if it's not acknowledging the global.asax
Am I missing something really simple here?
Thanks
Mark

Paladine 138 Master Poster Team Colleague · Answer 6 · 2006-04-03T00:36:44+00:00

I have replied to this on that link....

This tutorial code is for ASP.Net 1.0 & ASP.Net 1.1 NOT ASP.net 2.0 which is what you will be using with Visual Studio 2005.

Hi
Could someone look at my thread about this login code http://www.daniweb.com/techtalkforums/showthread.php?p=196279#post196279
I'm stuck and feel someone experienced will be able to see the problem rightaway. :?:

angelicjess 0 Newbie Poster · Answer 7 · 2006-05-16T12:25:40+00:00

Sorry, I need some help urgently. I follow the steps u told me but it show:->> C:\Inetpub\wwwroot\OPCWebClient\Login.aspx.vb(42): Name 'SqlConnection' is not declared.

How should I declare this variable?

I still can't connect to the database. Any wrong Username and Password can still use to login. What is wrong?

Paladine 138 Master Poster Team Colleague · Answer 8 · 2006-05-16T18:47:16+00:00

Sorry, I need some help urgently. I follow the steps u told me but it show:->> C:\Inetpub\wwwroot\OPCWebClient\Login.aspx.vb(42): Name 'SqlConnection' is not declared.
How should I declare this variable?
I still can't connect to the database. Any wrong Username and Password can still use to login. What is wrong?

Ok, What version are you using? ASP.NET 1.0 / 1.1 or ASP.NET 2.0 ? This code will ONLY work on ASP.NET 1.0 & 1.1.

It is saying the SQLConnection, a known Key word is NOT declared, this tells me their is a missed line of code. Have you put in break points and walked through it to see where exactly the error is occuring?

Provide more details or a capture of the exact error message I may be able to help you a little more.

angelicjess 0 Newbie Poster · Answer 9 · 2006-05-18T07:27:28+00:00

SQL Variation on the above Simple Login Page in ASP.Net (w/ VB.Net)

8. As in the AccessDB Example of this login page you need to create the DBConnection Function
- You will have to modify the code from the AccessDB example to work for SQL server.
- You will declare a new parameter, add it to the parameter colleciton, and have it set to receive the return value from the stored procedure.
- Determine if the return value is greater than 0 (meaning the SQL DB found the user based on the creditenials provided).

DBConnection Function:

Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As Boolean
                 '<sumamry>
                 '   |||||   Declare Required Variables
             ' ||||| Access appSettings of Web.Config for Connection String (Constant)
                 '</summary>
                 '   |||||   This is the Connections Object for an SQL DB
                 Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
 
                 '<sumamry>
                 '   |||||   Create a OleDb Command Object
                 '   |||||   Pass in Stored procedure
                 '   |||||   Set CommandType to Stored Procedure
                 '</summary>
 
          ' ||||| To Access a Stored Procedure in SQL Server - Requires a Command Object
                 Dim MyCmd As New SqlCommand("sp_ValidateUser", MyConn)
 
                 MyCmd.CommandType = CommandType.StoredProcedure
                 '   |||||   Create Parameter Objects for values passed in
                 Dim objParam1, objParam2 As SqlParameter
                 '   |||||   Create a parameter to store your Return Value from the Stored Procedure
                 Dim objReturnParam As SqlParameter
                 '<sumamry>
                 '   |||||   Add the parameters to the parameters collection of the
            ' ||||| command object, and set their datatypes (OleDbType in this case)
                 '</summary> 
                 objParam1 = MyCmd.Parameters.Add("@UserName", SqlDbType.VarChar)
                 objParam2 = MyCmd.Parameters.Add("@Password", SqlDbType.VarChar)
                 objReturnParam = MyCmd.Parameters.Add("@Num_of_User", SqlDbType.Int)
 
                 '   |||||   Set the direction of the parameters...input, output, etc
                 objParam1.Direction = ParameterDirection.Input
                 objParam2.Direction = ParameterDirection.Input
                 objReturnParam.Direction = ParameterDirection.ReturnValue   '   Note RETURNVALUE
        '' ||||| Set the value(s) of the parameters to the respective source controls
                 objParam1.Value = txtUserName.Text
                 objParam2.Value = txtPassword.Text
 
                 '   |||||   Try, catch block!
                 Try
                     '   |||||   Check if Connection to DB is already open, if not, then open a connection
                     If MyConn.State = ConnectionState.Closed Then
                         '   |||||   DB not already Open...so open it
                         MyConn.Open()
                         MyCmd.ExecuteNonQuery()
                     End If
                     '   |||||   Was the return value greater than 0 ???
                     If objReturnParam.Value < 1 Then
                         lblMessage.Text = "Invalid Login!"
                     Else
                         Return True
                     End If
 
                     '   |||||   Close the Connection Closes with it
                     MyConn.Close()
 
 
                 Catch ex As Exception
              lblMessage2.Text = "Error Connecting to Database!"
                 End Try
 
 
             End Function

Hi, I've used the above code in Access Database for my Login.aspx. However, there's some variables not defined. The variables are:
- SqlConnection
- SqlCommand
- SqlParameter

How can I solve this problem? Thank you for your help!:mrgreen:

angelicjess 0 Newbie Poster · Answer 10 · 2006-05-18T09:04:58+00:00

Ok, What version are you using? ASP.NET 1.0 / 1.1 or ASP.NET 2.0 ? This code will ONLY work on ASP.NET 1.0 & 1.1.
It is saying the SQLConnection, a known Key word is NOT declared, this tells me their is a missed line of code. Have you put in break points and walked through it to see where exactly the error is occuring?
Provide more details or a capture of the exact error message I may be able to help you a little more.

Sorry, I'm kinda noob in this... I do not know how to find out my version of ASP.NET.

I insert Imports.System.Data.SqlClient on top, although the KeyName is no longer showing error but when I used breakpoint, this statement show error >>> Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))

Is the information enough?

Paladine 138 Master Poster Team Colleague · Answer 11 · 2006-05-18T11:57:50+00:00

Sorry, I'm kinda noob in this... I do not know how to find out my version of ASP.NET.
I insert Imports.System.Data.SqlClient on top, although the KeyName is no longer showing error but when I used breakpoint, this statement show error >>> Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Is the information enough?

Ok be sure you have all of the following imports:

When keywords are not recognized, that is the first thing to check.
**Copy and Paste doesn't always solve this** :)

Imports System.Web.Security     '   ||||||   Required Class for Authentication
Imports System.Data             '   ||||||   DB Accessing Import
Imports System.Data.SqlClient   '   ||||||   SQL Server Import
Imports System.Configuration    '   ||||||   Required for Web.Config appSettings |||||

And be sure you have this line in your Web.Config file.

<appSettings>
    <add key="strConn" value="Network Library=DBMSSOCN;Data Source=192.168.0.100,1433;database=Northwind;User id=;Password=;"/>
    </appSettings>

Let me know how it goes..:cool:

angelicjess 0 Newbie Poster · Answer 12 · 2006-05-18T15:12:26+00:00

Sorry, let me give you a clearer picture first. I'm using Access Database and I named my Database as User.mdb. My table is called tblUser. Inside the table got U_id, U_Name and U_Password. I haven't create any Query because the SQL statement you wrote on the first page will always prompt me the value of the parameter. So in the end I deleted it. Is it purpose to be this way?

I also create a Login Page named Login.aspx. My form have:
- txtUsername
- txtPassword
- cmdSubmit
- lblMessage

Thus, I would like to learn from you what should I put for my query and how can I connect to the Access Database so that my login page is workable? I really appreciate your help. =) Thank you in advance!

Paladine 138 Master Poster Team Colleague · Answer 13 · 2006-05-19T05:12:19+00:00

Sorry, let me give you a clearer picture first. I'm using Access Database and I named my Database as User.mdb. My table is called tblUser. Inside the table got U_id, U_Name and U_Password. I haven't create any Query because the SQL statement you wrote on the first page will always prompt me the value of the parameter. So in the end I deleted it. Is it purpose to be this way?
I also create a Login Page named Login.aspx. My form have:
- txtUsername
- txtPassword
- cmdSubmit
- lblMessage
Thus, I would like to learn from you what should I put for my query and how can I connect to the Access Database so that my login page is workable? I really appreciate your help. =) Thank you in advance!

Ok, I can definitely help.

First off I highly recommend you READ through the tutorial again. I am being serious, and not in a bad way.

Why? Your statement:

I haven't create any Query because the SQL statement you wrote on the first page will always prompt me the value of the parameter. So in the end I deleted it. Is it purpose to be this way?

This demonstrates that you may not understanding what the purpose each line of code I have provided is doing. So let me see if I can better clarify what is happening.

To begin this SQL statement used in Access is correct:

SELECT COUNT(*) AS Num_of_User
         FROM tblUser
         WHERE (((tblUser.U_Name)=[@UserName]) AND ((tblUser.U_Password)=[@Password]));

The query is suppose to prompt you; it will in Access as the @UserName & @Password are parameters the query is expecting to do the processing against the database on. What you are doing wanting to do is pass in the values entered into the ASP.NET control (i.e. username & password textboxes) into these paramters. Otherwise how would you provide the query with the values entered? And how else would you qualify the query? Meaning; how would you find out if the password entered is correct for the username entered?

Here is another important point. Your initial error was with the SQLConnection object not being recognized, but you are now saying you are using an Access Database.

Which are you using? An SQL database or Access?

If it is truly Access you are using then the imports you need are those directed at the start of this tutorial:

Imports System.Web.Security     '   ||||||   Required Class for Authentication
Imports System.Data             '   ||||||   DB Accessing Import
Imports System.Data.SqlClient   '   ||||||   SQL Server Import
Imports System.Configuration    '   ||||||   Required for Web.Config appSettings |||||

Link to the start of this tutorial - using an Access Database

Hope this helps.:cool:

angelicjess 0 Newbie Poster · Answer 14 · 2006-05-20T23:23:18+00:00

I'm Sorry... The login page is still not working. I do not know what went wrong. I'm using Access DB.

Imports System.Web.Security ' |||||| Required Class for Authentication
Imports System.Data ' |||||| DB Accessing Import
Imports System.Data.SqlClient ' |||||| SQL Server Import
Imports System.Configuration ' |||||| Required for Web.Config appSettings |||||

I've changed the above code from Imports.System.Data.SqlClient to Imports.System.Data.OleDBConnection. Because I'm not using SQL Server. Am I right to do so? I've also create a new databse which I had followed the codes on the first page. So could you paste the rest of the codes which I need for me? The first page is kinda confusing for me. I'm really new at this. This is the first time I'm doing this. Please...

E_Hobbie_Coder 0 Newbie Poster · Answer 15 · 2006-06-06T04:10:28+00:00

Greetings all. I am very green at .net, learning as I go at this point. Is there a way to "adapt" the code here to be usable on the 2.0 Framework? I am building a basic webform for a project at work that pulls a dropdown list from an SQL DB, and I have an editing page to manage it, but of course it needs to be protected so anyone can't just go in and make changes. I build the entire login script based on the notes here, with the usual couple tweaks, but get the error "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll" when I run in debug mode after entering the name/password. The form always returns the "invalid login!" message regardless as well. I am writing in vb.net 2k5 at this time, but haven't learned all the features as of yet either. -- Thanks for any assistance anyone can provide! --

Paladine 138 Master Poster Team Colleague · Answer 16 · 2006-06-07T19:09:52+00:00

Greetings all. I am very green at .net, learning as I go at this point. Is there a way to "adapt" the code here to be usable on the 2.0 Framework? I am building a basic webform for a project at work that pulls a dropdown list from an SQL DB, and I have an editing page to manage it, but of course it needs to be protected so anyone can't just go in and make changes. I build the entire login script based on the notes here, with the usual couple tweaks, but get the error "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll" when I run in debug mode after entering the name/password. The form always returns the "invalid login!" message regardless as well. I am writing in vb.net 2k5 at this time, but haven't learned all the features as of yet either. -- Thanks for any assistance anyone can provide! --

Hi E_Hobbie_Coder,

You say you are using VB.Net 2K5? Well this code only works with ASP.NET 1.0 or 1.1. It will not function (do to several Framework changes) in ASP.NET 2.0, or VB.NET 2k5 (which would require major code changes to from this tutorial).

Provide the code you are having issues with and I can see what I can do to help.

shingrey 0 Newbie Poster · Answer 17 · 2006-06-17T19:53:18+00:00

Hi,
I'm new to ASP.net and SQl server,
I'm using VS 2003 and SQL 2005 express edition,
I followed thru out ur tutorial , but still ve problem with building my first login page..

After I click the submit button..lblmessage2.= become visible which is
"Error Connecting to Database!"

I tried playing around with my connection string but its still the same..
my current connection string

<appSettings>
<addkey="strConn"value="Data Source=(local);database=fypDB;User id=;Password=;"/>
</appSettings>

I'm using window authentication...tried several connection string...but i still cannot connect to the db..other then the connection string..others codes are the same as ur tutorial..

Paladine 138 Master Poster Team Colleague · Answer 18 · 2006-06-17T22:04:33+00:00

try

<add name="strConn" connectionString="Data Source=P4C800E\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=True"
   providerName="System.Data.SqlClient" />

shingrey 0 Newbie Poster · Answer 19 · 2006-06-17T23:37:46+00:00

try

<add name="strConn" connectionString="Data Source=P4C800E\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=True"
   providerName="System.Data.SqlClient" />

I tried the above but error message came out bout missing key, so i replace name with key,..and then another error bout value came out so i replace it,...and now its this error..all this are after the on submit

[B]Parser Error Message: [/B]Unrecognized attribute 'providerName'
 
[B]Source Error:[/B] 
 
Line 2:  <configuration>
Line 3:      <appSettings>
Line 4:    <add key="strConn" value="Data Source=SHINGREY2\SQLEXPRESS;database=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
Line 5:      </appSettings>Line 6:    <system.web>

Paladine 138 Master Poster Team Colleague · Answer 20 · 2006-06-17T23:43:11+00:00

So your datasource is SQLEXPRESS and not some other name?

Also, you have left left the Database as Northwind, and your previous post showed that your database is fypDB So make sure you taylor the string for you specific instance.

Also, the providerName is not required, that was out of ASP.NET 2.0. My apologies, bu the error message tells you what is wrong, so remove the portion that is unrecognized.

The error will occur on the Submit, because that is when the application makes a call to the Database.

Should be: (at least try this)
<addkey="strConn"value="Data Source=(local);Initial Catalog=fypDB;Integrated Security=True""/>

Paladine 138 Master Poster Team Colleague · Answer 21 · 2006-06-18T00:19:41+00:00

Acutally here is a major better explaination.

When using SQL Express you need to also consider the new security. I recommend that you download and install the Microsoft SQL Server Management Studio Express CTP which will give you a GUI control app for SQL Express. Then go to secuirty --> Logins folder in the Object Explorer in this app and set the following up for you ASP.NET login (not the login for the asp.net app you are building).

Set the login up like have shown here for my SQL server and Northwind database:

[IMG]http://www3.telus.net/public/tmlohnes/Images/SQLSecurity.jpg[/IMG]

Once that is setup. (note: not all steps are shown in the above image).

Then the SQL Connection string (using my example) would look like this:

"Server=P4C800E;Database=Northwind;Trusted_Connection=True"

Hope this helps

shingrey 0 Newbie Poster · Answer 22 · 2006-06-18T11:33:29+00:00

Finally I got it to work...
The problem is I'm not familiar with Sql server,
never used it, prior to this tutorial,

anyway, I got it to work, with the Sql Management Express,
I set up the ASP.Net Login..followed ur steps..tried and tried..
but it only works when I played with the db_owner checkbox on your shown page and also the security page for users Under my northwind DB.....

Many Thanks for your time...now I can move on to session

Frehley 0 Newbie Poster · Answer 23 · 2006-06-21T10:57:44+00:00

Very good guide! The only problem I have is that I'm using MYSQL, and I can not use store procedures with it. So my question is:
How can I make the Function DBConnection to work without store procedures? Is this possible?

Thanks!

Paladine 138 Master Poster Team Colleague · Answer 24 · 2006-06-22T05:43:24+00:00

Sure, use the SQL statements in the Store Procedures in the code_behind, just like you would any other SQL statement.

Dim sqlstring as String = "SELECT Count(*) FROM tblUsers WHERE...."

Hope this helps.

Frehley 0 Newbie Poster · Answer 25 · 2006-06-22T06:34:26+00:00

Thanks for the answer, I made this query, but it keep saying "Invalid user"

Public storecons As String
    Function DBConnection(ByVal strUserName As String, ByVal strPassword As String) As Boolean
        storecons = _
        "SELECT COUNT(*) AS Num_of_User " & _
        "FROM tblUser " & _
        "WHERE U_Name = @UserName AND U_Password = @Password " & _
        "RETURN  @Num_of_User "


        Dim MyConn As MySqlConnection = New MySqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn"))
        Dim MyCmd As New MySqlCommand(storecons, MyConn)
        MyCmd.CommandType = Data.CommandType.Text
        Dim objParam1, objParam2 As MySqlParameter
        Dim objReturnParam As MySqlParameter

        objParam1 = MyCmd.Parameters.Add("@UserName", MySqlDbType.VarChar)
        objParam2 = MyCmd.Parameters.Add("@Password", MySqlDbType.VarChar)
        objReturnParam = MyCmd.Parameters.Add("@Num_of_User", Data.SqlDbType.Int)

        objParam1.Direction = Data.ParameterDirection.Input
        objParam2.Direction = Data.ParameterDirection.Input
        objReturnParam.Direction = Data.ParameterDirection.ReturnValue

        objParam1.Value = txtUserName.Text
        objParam2.Value = txtPassword.Text

        Try
            If MyConn.State = Data.ConnectionState.Closed Then
                MyConn.Open()
                MyCmd.ExecuteNonQuery()
            End If

            If objReturnParam.Value < 1 Then
                lblMessage.Text = "Invalid Login!"
            Else
                Return True
            End If
            MyConn.Close()
        Catch ex As Exception
            lblMessage.Text = "Error Connecting to Database!"
        End Try
    End Function

I hope you can give me a hand.
Thanks!

Paladine 138 Master Poster Team Colleague · Answer 26 · 2006-06-24T11:10:01+00:00

Hi there

You dont need this: RETURN @Num_of_User a select statement by default returns a value already.

And you don't require this:
objReturnParam = MyCmd.Parameters.Add("@Num_of_User", Data.SqlDbType.Int)

I can't stress this enough. You can not just cut and paste and hope it works Go through the logic, and understand what each line of code is doing.

Here is a bit of a hint (actually more than that):

objParam2.Value = txtPassword.Text
        Dim objReader As OleDbDataReader
        '   |||||   Try, catch block!
        Try
            '   |||||   Check if Connection to DB is already open, if not, then open a connection
            If MyConn.State = ConnectionState.Closed Then
                '   |||||   DB not already Open...so open it
                MyConn.Open()
            End If

            '   |||||   Create OleDb Data Reader
            
            objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)
            '   |||||   Close the Reader and the Connection Closes with it

            While objReader.Read()
                If CStr(objReader.GetValue(0)) <> "1" Then
                    lblMessage.Text = "Invalid Login!"
                Else
                    objReader.Close()   '   |||||   Close the Connections & Reader
                    Return True
                End If
            End While
        Catch ex As Exception
            lblMessage.Text = "Error Connecting to Database!"
        End Try

danae 0 Newbie Poster · Answer 27 · 2006-07-03T14:11:01+00:00

Hi Paladine. I m new in dis forum and also a VB.NET beginner. Thanks alot for dat post for the connection to SQL server 2000 database, it was exactly wat i was looking for. Anyway, i hav a question here. Can't i juz match the value of password to the user ID? I mean, without going through the stored procedure and all? Is using stored procedure a more efficient way? Thanks. I used parameters for the password and it worked so far. Hope you can give some advice. Thank you.

Paladine 138 Master Poster Team Colleague · Answer 28 · 2006-07-03T19:54:45+00:00

Hi there glad I could help.

At the start of this thread, page 1, there is the steps to using a store procedure (preferred methodology) for this.

CREATE PROCEDURE sp_ValidateUser /* How it would appear in QUERY ANALYZER */
             (
                 @UserName VARCHAR(50) = NULL,
                 @Password VARCHAR(50) = NULL,
                 @Num_of_User INT = 0
             )
         AS
             SET @Num_of_User = (SELECT COUNT(*) AS Num_of_User
             FROM NorthWindUsers
             WHERE UserName = @UserName AND Password = @Password)
         RETURN  @Num_of_User

Let me know if you need something more specific.

mathborg 0 Newbie Poster · Answer 29 · 2006-08-30T07:16:45+00:00

uhm...I have been checking all of your tutorials Paladine...they are great, I've tried the C# example and it worked quite well...nonetheless, as I am a newbie in asp.net :( I've been tryng to do the login page using mysql and hell I've been having lots and lots of trouble... wonder if you could guide me pls hehe

btw..my problems start with the stored procedure, which I think I kinda adapted in mysql 5, but, I don't think it's working.....also may have problems with the connection... so bad...

as I said, I tried to adapt the C# with sql server example you posted before....here are fragments of the code I think are wrong:

[in the code behind]

.......

private bool DBConnection(string tUser, string tPass)
    {
//I couldn't make it work in the web.configure :(        
OdbcConnection myConn = new OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=database;UID=uid;PASSWORD=password;OPTION=3;");
        OdbcCommand myCmd = new OdbcCommand("sp_ValidateUser", myConn);
        myCmd.CommandType = CommandType.StoredProcedure;

.......

returnParam = myCmd.Parameters.Add("@Num_of_User", OdbcType.Int);

    objParam1.Direction = ParameterDirection.Input;
    objParam2.Direction = ParameterDirection.Input;
    returnParam.Direction = ParameterDirection.Output;

......

now, this is the stored procedure I made (got a table named "users"):

CREATE PROCEDURE `sp_ValidateUser`(IN UserName Varchar(50), IN Wachtwoord Varchar(50), OUT Num_of_User INT)

BEGIN
     SELECT COUNT(*) 
     INTO Num_of_User 
     FROM users
     WHERE user = UserName 
     AND password = Wachtwoord;
END

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

and finally the error I got:

System.Data.Odbc.OdbcException: ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.24-community-nt]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sp_ValidateUser' at line 1 at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteNonQuery() at _Default.DBConnection(String tUser, String tPass)

I think I really need help hehe :P..... thanx in advance :)

mathborg 0 Newbie Poster · Answer 30 · 2006-09-01T08:22:14+00:00

doesn't matter anymore, I managed to do the page using the login control, and, using the mysql connector instead of odbc... sorry for the inconvenience... anyways, if some other newbie or not want some help just let me know... bye then

P.S. I forgot, I haven't done it with the stored procedure though, so, as soon as I am able to do it, I'll be telling you pals