I made a small ASP.NET (C#) site on my local machine that connects to SQL 2000. When I test the application it runs great. Everything gets stored into the database and the tables that it is suppose to.
I upload the site to a hosting company and when I run it, I get all sorts of error with the database. (I also copied the database from my local machine to the server via Enterprise Manager.)
For example, when I run the registration page - I get the error message:
"Cannot insert the value NULL into column 'user_id', table 'xxx.xxxx.tbl_users'; column does not allow nulls. INSERT fails. The statement has been terminated. "
I checked the table properties and my columns are set to allow null values. With the exception of having a primary key set to a column for indentity purpose (user_id).
The weird part is that I checked to see if the data are being stored into the column... and it is! Everything that was sent from the form is being stored into the table.
So I don't understand the problem I am having. Can anyone shed some light for me?
Below is the function I've written that inserts the data into the table.
===========================
C# Function to insert data
===========================
// Store Data to String
string strFirst = reg_fname.Text.ToString().Trim();
string strLast = reg_lname.Text.ToString().Trim();
string strEmail = reg_email.Text.ToString().Trim();
string strZip = reg_zip.Text.ToString().Trim();
string strCountry = reg_country.Text.ToString().Trim();
// Open Connection to Database
string strConnection = ConfigurationSettings.AppSettings["xxxxx"];
string strSQL = "INSERT INTO tbl_registration (first_name, last_name, email, zip, country) VALUES ('"+ strFirst.Replace("'", "") +"', '"+ strLast.Replace("'", "") +"', '"+ strEmail.Replace("'", "") +"', '"+ strPassword.Replace("'", "") +"', '"+ strZip.Replace("'", "") +"', '"+ strCountry.Replace("'", "") +"')";
// Execute Connection and INSERT Data
SqlConnection oConnection = new SqlConnection(strConnection);
oConnection.Open();
SqlCommand oCommand = new SqlCommand(strSQL, oConnection )oCommand.ExecuteNonQuery();
oConnection.Close();How is the user_id column populated? You mentioned "identity purposes." Is the column marked as an IDENTITY in SQL?
Regarding your command, consider using parameterized commands:
string sqlStmt;
SqlCommand cmd;sqlStmt = "INSERT INTO (first_name, last_name, email, zip, country) VALUES (@.first_name, @.last_name, @.email, @.zip, @.country)";
cmd = new SqlCommand(sqlStmt, oConnection);
cmd.Parameters.Add("@.first_name", strFirst);
cmd.Parameters.Add("@.last_name", strLast);
cmd.Parameters.Add("@.email", strPassword);
cmd.Parameters.Add("@.zip", strZip);
cmd.Parameters.Add("@.country", strCountry);
cmd.ExecuteNonQuery();
Note that if any of the parameters contain any illegal characters, such as a single quote, the parameter will be "fixed", so the str.Replace("''", "'") is not required.|||Also, are there triggers involved? Sometimes, the error message is referring to something that your trigger was trying to do, and not the proc you were explicitly executing... :)
