wrong values being input to the database

Question

User-1779368810 posted

Hello,

I have been writing a customer based application\web form.  In the case "the button" takes information from textboxes and "updates" the Access database with the input.  The problem I am running into is that for "LName, Zip, and Phone" -- the program is returning values of zero for only these 3.  Here is the code.

protected void Button1_Click(object sender, EventArgs e)
    {
        string ss = TextBox1.Text;
        string fName= TextBox2.Text;
        string mName= TextBox3.Text;
        string lName = TextBox4.Text;
        string add1= TextBox5.Text;
        string add2= TextBox6.Text;
        string city= TextBox7.Text;
        string state= TextBox8.Text;
        string zip= TextBox9.Text;
        string phone= TextBox10.Text;
        string balance = TextBox11.Text;

        string sel = DropDownList1.SelectedValue;
        string query = "UPDATE Customer SET C_SS=@SSValue, C_FName=@FNameValue, C_MName=@MNameValue, C_LName=@LNameValue, C_Address1 = @Add1Value, C_Address2 = @Add2Value, C_City = @CityValue, C_Zip = @ZipValue,
            C_Phone = @PhoneValue, C_AccountBalance = @BalValue WHERE Customer_ID=@SelValue";

        System.Data.OleDb.OleDbCommand ocmd =
             new System.Data.OleDb.OleDbCommand(query,
             new System.Data.OleDb.OleDbConnection(CSTR));

        
        ocmd.Parameters.AddWithValue("@SSValue", ss);
        ocmd.Parameters.AddWithValue("@FNameValue", fName);
        ocmd.Parameters.AddWithValue("@MNameValue", mName);
        ocmd.Parameters.AddWithValue("@LNameValue", lName);
        ocmd.Parameters.AddWithValue("@Add1Value", add1);
        ocmd.Parameters.AddWithValue("@Add2Value", add2);
        ocmd.Parameters.AddWithValue("@CityValue", city);
        ocmd.Parameters.AddWithValue("@ZipValue", zip);
        ocmd.Parameters.AddWithValue("@PhoneValue", phone);
        ocmd.Parameters.AddWithValue("@BalValue", balance);
        ocmd.Parameters.AddWithValue("SelValue", sel);

        ocmd.Connection.Open();
        ocmd.ExecuteNonQuery();
        ocmd.Connection.Close();

    }
}

Thanks in ADVANCE

Answer 1

User-1946294156 posted

Try using breakpoints to see the value that are being passed at the time the script is running. 

Answer 2

User-1779368810 posted

Thanks!!! I got it to work but now I am having a different issue.  My goal is to have the max Customer ID + 1 added to the database with each new addition of a customer.  I am getting an "UNSPECIFIED ERROR" on the execute non.query -- I know that there is an issue with string customerID = .............. -- and now I am wondering if I should be reading the customerID first and then passing it back through the data connection to the database -- I would like to make it possible with one SQL statement that I have below.

Here is the new code

protected void createButton_Click(object sender, EventArgs e)
    {
        string customerID = DropDownList1.SelectedValue;
        string ss = TextBox1.Text;
        string fName = TextBox2.Text;
        string mName = TextBox3.Text;
        string lName = TextBox4.Text;
        string add1 = TextBox5.Text;
        string add2 = TextBox6.Text;
        string city = TextBox7.Text;
        string state = TextBox8.Text;
        string zip = TextBox9.Text;
        string phone = TextBox10.Text;
        string balance = TextBox11.Text;
        
        string query = "INSERT INTO Customer (Customer_ID, C_SS, C_FName, C_MName, C_LName, C_Address1, C_Address2, C_City, C_State, C_Zip, C_Phone, C_AccountBalance)" +
            "VALUES ((Select MAX(Customer_ID) + 1 FROM Customer), @IDValue, @SSValue,@FNameValue, @MNameValue, @LNameValue, @Add1Value, @Add2Value, @CityValue, @StateValue, @ZipValue, @PhoneValue, @BalValue)";
        System.Data.OleDb.OleDbCommand ocmd =
             new System.Data.OleDb.OleDbCommand(query,
             new System.Data.OleDb.OleDbConnection(CSTR));

        ocmd.Parameters.AddWithValue("@IDValue", customerID);
        ocmd.Parameters.AddWithValue("@SSValue", ss);
        ocmd.Parameters.AddWithValue("@FNameValue", fName);
        ocmd.Parameters.AddWithValue("@MNameValue", mName);
        ocmd.Parameters.AddWithValue("@LNameValue", lName);
        ocmd.Parameters.AddWithValue("@Add1Value", add1);
        ocmd.Parameters.AddWithValue("@Add2Value", add2);
        ocmd.Parameters.AddWithValue("@CityValue", city);
        ocmd.Parameters.AddWithValue("@StateValue", state);
        ocmd.Parameters.AddWithValue("@ZipValue", zip);
        ocmd.Parameters.AddWithValue("@PhoneValue", phone);
        ocmd.Parameters.AddWithValue("@BalValue", balance);
 

        ocmd.Connection.Open();
        ocmd.ExecuteNonQuery();
        ocmd.Connection.Close();
    }
}

Answer 3

User-1199946673 posted

My goal is to have the max Customer ID + 1 added to the database with each new addition of a customer

 

You better use a autonumber field, which will insert the next value when inserting a new record