CODEWITHSUNDEEP
.netsql-server
Chris
Chris

Reputation: 2303

Unable to insert record into the database

I have the followind code in my .NET application

public string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Storage.mdf;Integrated Security=True;User Instance=True";
 string insertStatement = string.Empty;
            insertStatement = "INSERT INTO UserDetail(UserName, Password, IsRemember) Values('" + txtUserName.Text.Trim() + "','" + txtPassword.Text.Trim() + "','" + chkRemember.Checked + "')";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(insertStatement, con);
            con.Open();
            int result = cmd.ExecuteNonQuery();
            con.Close();

Storgae.mdf is the database which I have attached to the project. It's in the root directory.

Table structure

UserName NVarChar(50) NULL,
Password NVarChar(50) NULL,
IsRememberBit NULL

When I am running the query, it is returning 1 [No error, No exception] But when I checked into the database table, I found it empty.

Where I am wrong?

Edit1

When I changed the insertStatement to 

insertStatement = "INSERT INTO UserDetail(UserName, Password) Values('" + txtUserName.Text.Trim() + "','" + txtPassword.Text.Trim() + "')"

Than also nothing is updated into the database. and execute non query returned 1 to me.

On keeping debugger on the insertStatement, I am getting the below Statement

INSERT INTO UserDetail(UserName, Password) Values('testName','TestPassword')

Upvotes: 1

Views: 253

Answers (4)

Vasea
Vasea

Reputation: 5333

I strongly recommend you use SqlParameters, and never user string concatenations. Besides security concerns, performance reasons, it actually helps you a lot since some values cannot be represented in string so easily (inserting varbinary wouldn't be so straightforward)

var cmd = new SqlCommand("INSERT INTO UserDetail(UserName, Password, IsRemember) Values (@user, @pwd, @remember)", connection);
cmd.Parameters.AddWithValue("@user", username);
cmd.Parameters.AddWithValue("@pwd", pwd);
cmd.Parameters.AddWithValue("@remember", isremember);
cmd.ExecuteNonQuery();

Upvotes: 3

Zahir Khan
Zahir Khan

Reputation: 33

I don't see any problem with your code. Can you run SQL Profiler on the database, run the app and check what SQL queries are getting fired? Check to see if the Insert statement in your code is getting triggered. My assumption is the issue might possibly be with the Connection string or you checking the wrong database. No hard feelings but I am used to doing that occasionally :)

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7759

In respect to your Edit 1 you need to provide a default value for IsRememberBit in your database if you do not want to include it in your SQL statement.

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7759

This section '" + chkRemember.Checked + "')

should be:

" + chkRemember.Checked + ")

i.e remove the single quotes around the final bit / boolean parameter.

Upvotes: 0

Related Questions