Assign sql query result to variable

[Jollywg]

I'm trying to make a page where the user can view his/her user info. If they have not entered any info they should be able to click the button and their info is stored in an access table named Users. If they already have data in the table then it should update with what is in the text boxes.

For some reason usersRemaining always stays equal to 0 and never moves to 1 even if there is a user in the database.

Attached is the code that I am using.

Thanks for any and all replies!
Jolly

[Frinavale]

Jollywg, I cannot open the attachment.
Could you pleas post your code in thread (remember to use [code] tags)

-Frinny

[Jollywg]

Wow thats weird sorry about that.

String user = Convert.ToString(Page.User.Identity.Name);
        String address = Convert.ToString(txtStreet.Text);
        String name = Convert.ToString(txtName.Text);
        String city = Convert.ToString(txtCity.Text);
        String state = Convert.ToString(txtState.Text);
        int zip = Convert.ToInt32(txtZip.Text);
        int phone = Convert.ToInt32(txtPhone.Text);
        int ccnum = Convert.ToInt32(txtCCN.Text);
        int usersRemaining = 0;

        OleDbConnection objConnection = null;
        OleDbCommand objCmd = null;
        OleDbCommand objCmd1 = null;
        OleDbCommand objCmd2 = null;
        String strConnection, strSQL;

        strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
        strConnection += @"Data Source= |DataDirectory|FinalProj.mdb";

        objConnection = new OleDbConnection(strConnection);
        objConnection.ConnectionString = strConnection;

        objConnection.Open();

        strSQL = "SELECT username FROM Users WHERE username = '" + user + "'";
        objCmd = new OleDbCommand(strSQL, objConnection);

        usersRemaining = objCmd.ExecuteNonQuery();

        Label10.Text = Convert.ToString(usersRemaining);
        if (usersRemaining == 1)
        {

            strSQL = "UPDATE Users SET name = '" + name + "', address='" + address + "', city = '" + city + "', state = '" + state + "', zip = '" + zip + "', phonenumber = '" + phone + "', ccnumber='" + ccnum + "' WHERE username = '" + user + "'";
            objCmd1 = new OleDbCommand(strSQL, objConnection);
            objCmd1.ExecuteNonQuery();
        }
        if (usersRemaining == 0)
        {
            strSQL = "INSERT INTO Users (username, name, address, City, state, zip, phonenumber, ccnumber) VALUES('" + user + "','" + name + "', '" + address + "', '" + city + "', '" + state + "', '" + zip + "', '" + phone + "', '" + ccnum + "')";
            objCmd2 = new OleDbCommand(strSQL, objConnection);
            objCmd2.ExecuteNonQuery();

[Jollywg]

code

I'm pretty sure all my syntax is correct, with the exception of usersRemaining = objCmd.ExecuteN onQuery();

[semomaniz]

Your code

String user = Convert.ToString(Page.User.Identity.Name);
        String address = Convert.ToString(txtStreet.Text);
        String name = Convert.ToString(txtName.Text);
        String city = Convert.ToString(txtCity.Text);
        String state = Convert.ToString(txtState.Text);
        int zip = Convert.ToInt32(txtZip.Text);
        int phone = Convert.ToInt32(txtPhone.Text);
        int ccnum = Convert.ToInt32(txtCCN.Text);
        int usersRemaining = 0;

        OleDbConnection objConnection = null;
        OleDbCommand objCmd = null;
        OleDbCommand objCmd1 = null;
        OleDbCommand objCmd2 = null;
        String strConnection, strSQL;

        strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
        strConnection += @"Data Source= |DataDirectory|FinalProj.mdb";

        objConnection = new OleDbConnection(strConnection);
        objConnection.ConnectionString = strConnection;

        objConnection.Open();

        strSQL = "SELECT username FROM Users WHERE username = '" + user + "'";
        objCmd = new OleDbCommand(strSQL, objConnection);

        usersRemaining = objCmd.ExecuteNonQuery();

        Label10.Text = Convert.ToString(usersRemaining);
        if (usersRemaining == 1)
        {

            strSQL = "UPDATE Users SET name = '" + name + "', address='" + address + "', city = '" + city + "', state = '" + state + "', zip = '" + zip + "', phonenumber = '" + phone + "', ccnumber='" + ccnum + "' WHERE username = '" + user + "'";
            objCmd1 = new OleDbCommand(strSQL, objConnection);
            objCmd1.ExecuteNonQuery();
        }
        if (usersRemaining == 0)
        {
            strSQL = "INSERT INTO Users (username, name, address, City, state, zip, phonenumber, ccnumber) VALUES('" + user + "','" + name + "', '" + address + "', '" + city + "', '" + state + "', '" + zip + "', '" + phone + "', '" + ccnum + "')";
            objCmd2 = new OleDbCommand(strSQL, objConnection);
            objCmd2.ExecuteNonQuery();


        }

you declared a integer and assigned command execution to it this is a no no.

 usersRemaining = objcmd.ExecuteNonQuery();

First of all ExecuteNonQuery is only used with Update, Delete or Insert sql statements not with a Select statement.

Here you need to declare a Data reader and then assign the returned value of the sql statement to a string.
This is what you need to do Check the code between the asterisks.

String user = Convert.ToString(Page.User.Identity.Name);
String address = Convert.ToString(txtStreet.Text);
String name = Convert.ToString(txtName.Text);
String city = Convert.ToString(txtCity.Text);
String state = Convert.ToString(txtState.Text);
int zip = Convert.ToInt32(txtZip.Text);
int phone = Convert.ToInt32(txtPhone.Text);
int ccnum = Convert.ToInt32(txtCCN.Text);
int usersRemaining = 0;

OleDbConnection objConnection = null;
OleDbCommand objCmd = null;
OleDbCommand objCmd1 = null;
OleDbCommand objCmd2 = null;
String strConnection, strSQL;

strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source= |DataDirectory|FinalProj.mdb";

objConnection = new OleDbConnection(strConnection);
objConnection.ConnectionString = strConnection;

//***********************************************************************************************
OleDbDataReader reader; //declaring a data reader

//always a good idea to put your code inside a try catch block
try
{
    objConnection.Open();

    strSQL = "SELECT username FROM Users WHERE username = '" + user + "'";
    objCmd = new OleDbCommand(strSQL, objConnection);

    reader = objCmd.ExecuteReader();

    if (reader.HasRows)
    {
        if (reader.Read())
        {
            Label10.Text = reader["username"].ToString();

            // Put your code to update the data 
        }
    }
    else
    {
        //put your code to create the user.
    }
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    objConnection.Close();
}

//**********

Hope this helps

[Frinavale]

Semomaniz's right, you should be using ExecuteReader.
The other thing that I noticed is that you aren't using parameters to form your SQL query. This can leave you open to an SQL injection attack.

Check out the article about how to use a database for examples of how to use parameters to create your SQL queries.

-Frinny

[Jollywg]

Thanks for the posts semomaniz and frinny! I'll give them a try and let you know how it works out!

[semomaniz]

you can uses parameter with the inline sql statements or use stored procedure to prevent sql injection. Also never trust the data input by user so using HttpUtility.Hml Encode() is handy too

[Jollywg]

Thanks for the posts you both are lifesavers!