ADO.NET Simple returning a single row (newbie)

[bencoding]

Hello, I'm a newbie to the .NET programming and need some help.

I am creating a registration page and want to be able to take the username and password that the user entered and check to the the database to make sure it doesn't already exist. If it does I want to return an error Response.Write() message and if it doesnt then I will continue with my other code. I am very new to this and it's hard finding some support forums online that are what I need.

This is what I have so far:

 SqlConnection myConn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=\"C:\\Program Files\\MyProject\\App_Data\\Database.mdf\";Integrated Security=True;Connect Timeout=30;User Instance=True");
 
SqlCommand cmdCheck = new SqlCommand("SELECT 1 AS [EXIST] FROM Users WHERE Email = '" + email.Text + "' AND IsActive = 'True'", myConn);

Can someone give me the code I would need to check that table to see there is already an email address that is active = True and return a message if it is there.

Thanks

[gpraghuram]

This should be moved to the .NET forum

Raghu

[r035198x]

What specifically do you need help with now? Does your code run as expected?

P.S Moved to .NET forum.

[Jimmy611]

I suggest you make a new field in the table with BIT data type. Set the BIT to 1 when user is already logged on. And just retrieve it for already logged in user or new user. If it gives 1 for X user then X cannot log in again coz its already logged in. If it gives 0 for X , X can log in now.

I hope i am clear.
Now as for the code, you can do it yourself its not that tough.

[rajeshpushp]

If u want to just check that whether that userid or email address exists or not in the table, then u can return a boolean value from your query. Or u can also use output variable in case you are using a stored procedure.
eg.
take an output variable @intErrorOutput, and then set the variable value in database, and then place the check on the basis of output variable value.
------------------------------------
@intErrorOutput INT OUTPUT

IF EXISTS
(
SELECT * FROM TableName
WHERE user = @userValue
)
BEGIN
SET @intErrorOutput = 1
RETURN
END
else
SET @intErrorOutput = 0

[bencoding]

Thanks for your help guys, I ended up figuring it out and did something different, so as I mentioned this is a register page and I need to check the database to make sure that the email address is not already in the database and in an active account. So I ended up using the ExecuteScalar(); which returns the first value of the first column, and then put in a if != null condition. Here is my code, feel free to critique and let me know what I could of done better. thx

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection myConn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=\"C:\\Program Files\\ContractorClientManager\\App_Data\\Database.mdf\";Integrated Security=True;Connect Timeout=30;User Instance=True");
            SqlCommand cmdCheck = new SqlCommand("SELECT 1 FROM Users WHERE Email = '" + email.Text + "' AND IsActive = 'True'", myConn);
            SqlCommand cmdInsert = new SqlCommand("INSERT Users (Email,Password) VALUES ('" + email.Text + "','" + password.Text + "')", myConn);
 
            myConn.Open();
 
            if (cmdCheck.ExecuteScalar() != null)
            {
                error_header.Text = "This account already exists click <a href=\"LoginRetrieval.aspx\">HERE</a>  to retrieve password.</a>";
            }
            else
            {
                cmdInsert.ExecuteNonQuery();
                Response.Redirect("Login.aspx?msg=user_new");
            }
 
            myConn.Dispose();
        }
        catch
        {
            Response.Write(e.ToString());
        }
 
 
    }