No error and data not inserted to sql server table?

Question

0.00/5 (No votes)

See more:

I am trying to add data to database. Its running with no error and when i check no data is there in table.
My code-

C#

private void button1_Click(object sender, EventArgs e)
{
    try
    {

        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ProTestDB.mdf;Integrated Security=True;User Instance=True");
        string name = textBox1.Text;
        string sqlstr = ("INSERT INTO Users VALUES('" + textBox1.Text + "')");
        SqlCommand cmd = new SqlCommand(sqlstr, conn);
        cmd.Parameters.AddWithValue("Name", name);

        conn.Open();
        cmd.ExecuteNonQuery();

        conn.Close();
    }
    catch
    {
        MessageBox.Show("error");
    }
}

Posted 5-May-13 9:00am

Pankaj Mahor

Updated 10-Jun-19 12:58pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

Pankaj Mahor 7-May-13 11:03am

After viewing youtube tutorials, i have found that they are using sql server management studio to create database and i am using c#.net to create it. Is this make any difference. I can easily do this in vb.ne but not working in c#.net. Do i also have to use management studio? Plz reply...

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 1 · 2013-05-05T09:21:00

Solution 1

There are some problems with the code. Please modify like below and try.

C#

string sqlstr = "INSERT INTO Users(Name) VALUES(@NameParameter)";
cmd.Parameters.AddWithValue("@NameParameter", name);

Here Name is the column present in Users table.

[Update]
1. Stored procedure - INSERT query does not work[^].
2. How to insert record into a sql server express database table?[^].
3. insert problem in C#, using sqlcommand[^].

So, basically the problem is ...

Quote:
The whole User Instance and AttachDbFileName= approach is flawed - at best! Visual Studio will be copying around the .mdf file and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

Posted 5-May-13 9:21am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Updated 5-May-13 21:04pm

v2

Comments

Pankaj Mahor 5-May-13 23:39pm

I tried it also but still same problem.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 6-May-13 3:02am

Ok. This is related to connection problem.

See my updated answer under heading "Update".

Pankaj Mahor 6-May-13 3:58am

I have not created database in sql management studio. I created it in c#.net- by adding new item to project and selected a service based database (.mdf) file.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 6-May-13 4:06am

As you might know from those answers that it is a flawed approach, so try those solutions.
Otherwise as suggested in the first link, put a break point on con.Close() and inspect the .mdf file with SQL Server Mgmt Studio Express.

Pankaj Mahor 6-May-13 4:56am

Ok after put breakpoint how to inspect .mdf file?

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 6-May-13 6:50am

I guess as suggested there you need to install Microsoft SQL Server Management Studio Express and use that for inspecting.

Pankaj Mahor 6-May-13 7:21am

I have already installed management studio and can connect easily.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 6-May-13 7:22am

So, have you tried to inspect that file while debugging ?

Pankaj Mahor 6-May-13 7:45am

I have not touch management studio. I am doing it only in c#.net like creating db, getting connection string all in c#.net.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 6-May-13 8:02am

Oh ok...

Bikash Prakash Dash · Answer 2 · 2013-05-05T23:05:00

Solution 2

Try this my friend

C#

private void button1_Click(object sender, EventArgs e)
{
    try
    {
 
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ProTestDB.mdf;Integrated Security=True;User Instance=True");
        string sqlstr = "INSERT INTO [Users] VALUES(@value)";
        conn.Open();
        SqlCommand cmd = new SqlCommand(sqlstr, conn);
        cmd.Parameters.Add("@value",SqlDbType.VarChar).Value=textBox1.Text.Trim();
       
        cmd.ExecuteNonQuery();
 
        conn.Close();
    }
    catch
    {
        MessageBox.Show("error");
    }
}

Posted 5-May-13 23:05pm

Bikash Prakash Dash

Updated 5-May-13 23:45pm

v2

Comments

Pankaj Mahor 6-May-13 5:29am

Tried, same problem- no error and empty table.

Bikash Prakash Dash 6-May-13 5:39am

Can u plzz tell me how many fields are there in your table?

Pankaj Mahor 6-May-13 6:12am

One table named 'Users' and two columms named 'UserID' and 'Name'.

Bikash Prakash Dash 6-May-13 6:17am

if your inserting values to all columns you need not specify column names in INSERT query.
if your inserting values to one column , you have to specify.
eg->INSERT INTO [Users] (UserID) VALUES(@value)

Pankaj Mahor 6-May-13 6:46am

I am trying to insert textbox value to only one column 'Name'.

Bikash Prakash Dash 6-May-13 6:49am

so the query will be
INSERT INTO [Users] (Name) VALUES(@value)

if UserID field is a primary key, then it will through error, in that case you have to insert some value for UserID.

Pankaj Mahor 6-May-13 7:04am

Yes, UserID is a primary key and i just want to add value to Name column not UserID.

Bikash Prakash Dash 6-May-13 7:23am

so it is not possible primary key field cant be NULL.

Pankaj Mahor 6-May-13 7:41am

Ok so how to add data to all columns.

Bikash Prakash Dash 6-May-13 7:54am

Try this
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ProTestDB.mdf;Integrated Security=True;User Instance=True");
string sqlstr = "INSERT INTO [Users] VALUES(@userid,@name)";
conn.Open();
SqlCommand cmd = new SqlCommand(sqlstr, conn);
cmd.Parameters.Add("@userid",SqlDbType.VarChar).Value=textBox1.Text.Trim(); //For User ID it cant be null
cmd.Parameters.Add("@name",SqlDbType.VarChar).Value=textBox2.Text.Trim(); // For Name
cmd.ExecuteNonQuery();

conn.Close();

Pankaj Mahor 6-May-13 8:05am

Thanx but when i run it throws error which i have set it in try catch? Now what to do?

Bikash Prakash Dash 6-May-13 8:10am

use catch like this.
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
you can see which type of error you are getting.Please mention what error you are getting.

Pankaj Mahor 6-May-13 8:11am

And when i run code without try catch, i get this error-
An explicit value for the identity column in table 'Users' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Bikash Prakash Dash 6-May-13 8:14am

You must be entering a value that has entered before in UserID field. As it is Primary Key,it's value in different rows should be unique.