Click here to Skip to main content
14,328,292 members
Rate this:
Please Sign up or sign in to vote.
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-
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
v2
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...
Rate this:
Please Sign up or sign in to vote.

Solution 1

There are some problems with the code. Please modify like below and try.
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.
   
v2
Comments
Pankaj Mahor 5-May-13 23:39pm
   
I tried it also but still same problem.
   
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.
   
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?
   
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.
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Try this my friend

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");
    }
}
   
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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100