Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET
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 5-May-13 10:00am
v2
Comments
Member 9781851 at 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: bad
good
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.
  Permalink  
v2
Comments
Member 9781851 at 5-May-13 23:39pm
   
I tried it also but still same problem.
Tadit Dash at 6-May-13 3:02am
   
Ok. This is related to connection problem.
 
See my updated answer under heading "Update".
Member 9781851 at 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 at 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.
Member 9781851 at 6-May-13 4:56am
   
Ok after put breakpoint how to inspect .mdf file?
Tadit Dash at 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.
Member 9781851 at 6-May-13 7:21am
   
I have already installed management studio and can connect easily.
Tadit Dash at 6-May-13 7:22am
   
So, have you tried to inspect that file while debugging ?
Member 9781851 at 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 at 6-May-13 8:02am
   
Oh ok...
Rate this: bad
good
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");
    }
}
  Permalink  
v2
Comments
Member 9781851 at 6-May-13 5:29am
   
Tried, same problem- no error and empty table.
Bikash Prakash Dash at 6-May-13 5:39am
   
Can u plzz tell me how many fields are there in your table?
Member 9781851 at 6-May-13 6:12am
   
One table named 'Users' and two columms named 'UserID' and 'Name'.
Bikash Prakash Dash at 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)
Member 9781851 at 6-May-13 6:46am
   
I am trying to insert textbox value to only one column 'Name'.
Bikash Prakash Dash at 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.
Member 9781851 at 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 at 6-May-13 7:23am
   
so it is not possible primary key field cant be NULL.
Member 9781851 at 6-May-13 7:41am
   
Ok so how to add data to all columns.
Bikash Prakash Dash at 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();
Member 9781851 at 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 at 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.
Member 9781851 at 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 at 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
0 Kornfeld Eliyahu Peter 169
1 Zoltán Zörgő 139
2 George Jonsson 135
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 12 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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