Why can't I insert data into my database?

Question

I want to do a very simple INSERT INTO operation, but nothing happens. I see no errors and also I see no changes in the database.

I suspect my connection string is wrong, but how can I know that? If so how can I fix it? (local host)

protected void RegButton_Click(object sender, EventArgs e)
{
   string connString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Bodin\\Desktop\\FullFart\\App_Data\\database.mdf;Integrated Security=True;User Instance=True";
   string sql = "INSERT INTO student (navn, etternavn)  " + "VALUES('" + NavnTextBox.Text + "', '" + EtterNavnTextBox.Text + "');";
   SqlConnection conn = new SqlConnection(connString);

   SqlCommand myCommand = new SqlCommand(sql,conn);

   try
   {
      conn.Open();
      myCommand.ExecuteNonQuery();
   }
   catch (Exception ex)
   {
      Console.WriteLine("Error: " + ex);
   }
   finally
   {
      conn.Close();
   }        
}

Any ideas why the above code does nothing? (It should add new rows every time executed)

Update

Related to SQL injection. This is a demo program for some homework, that is why i don't use parameters. It is my friends laptop and i only have installed Visual studion 2008 and SQL server 2005. I don't have any other gadgets I can manually add values to the database from visual studio, so that means the DB works. But how can i correctly configure the connection String?

Run a trace with SQL Server Profiler so you can see if the query executes at all. — sventevit, Apr 24 '11 at 16:06
Sorry i am very new to .NET i only have Visual Studio 2008. Where can i have the option there to execute that query? — javing, Apr 24 '11 at 16:07
You should also use parameters to avoid sql injection (http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — sventevit, Apr 24 '11 at 16:08
@sfrj - SQL Server Profiler is a part of SQL Server, you should find it in start menu under SQL Server -> Performance Tools. An example how to use it: http://www.symantec.com/business/support/index?page=content&id=HOWTO3965&key=55263&actp=LIST — sventevit, Apr 24 '11 at 16:11
@_simon_: SQL Server Profiler is not part of SQL Server **Express** if I'm not mistaken (which is what sfrj seems to be using when looking at the connection string) — , Apr 24 '11 at 16:13
`ExecuteNonQuery` returns number of affected records. What number returns it in your case? — Ladislav Mrnka, Apr 24 '11 at 16:14
Is the database created? Creating user instances must be allowed. — Ladislav Mrnka, Apr 24 '11 at 16:19

score 3 · Answer 1 · answered Apr 24 '11 at 16:13

Not sure why your connection string would be wrong - I personally don't like the "AttachDbFile" approach. Also: you should a) avoid concatenating together your SQL command strings (because of SQL injection attacks), and b) you should put your SqlConnection and SqlCommand objects into using blocks to avoid memory issues.

My recommendation would be: fire up SQL Server Mgmt Studio Express and attach that database to your SQL Server Express server - then use something like this:

string connString = "Server=.\\SQLEXPRESS;database=YourDatabase;Integrated Security=True";

string query = "INSERT INTO dbo.Student(navn, etternavn) " + 
   "VALUES(@navn, @etternvan)";

using(SqlConnection conn = new SqlConnection(connString))
using(SqlCommand myCmd = new SqlCommand(query, conn))
{
   // set up parameters
   myCmd.Parameters.Add("@navn", SqlDbType.VarChar, 100).Value = NavnTextBox.Text.Trim();
   myCmd.Parameters.Add("@etternavn", SqlDbType.VarChar, 100).Value = EtterNavnTextBox.Text.Trim();

   try
   {
       conn.Open();
       myCmd.ExecuteNonQuery();
       conn.Close();
   }
   catch (Exception ex)
   {
      Console.WriteLine("Error: " + ex);
   }
}

The SQL query as such is looking OK from my point of view - it should work just fine.

The problem with the AttachDbFile= and UserInstance=true is (amongst other things) that they often initialize a database from scratch, each time your app runs. It's absolutely possible your insert really actually worked, but next time you run your app, it's being initialized back to a default state again and makes you believe it's not done anything.....

score 1 · Answer 2 · edited Aug 13 '12 at 09:00

I know I'm late here but I was facing this problem now and found a solution. I want to share it so that if anybody comes here to seek some help on this matter....

So what I did was as written:

You can leave the data file outside the project and create a connection to it in Database Explorer. When the IDE asks you to bring the file into the project, just say no. This way, both the design-time and the run-time will be using the same data file but the downside is that the path in the connection string will be hard coded and therefore it’ll be harder to share the project and deploy the app. Before deploying the app, just make sure to replace the full path in the settings with a relative path.

You can see it here

score 1 · Accepted Answer · answered Apr 24 '11 at 16:17

 SqlConnection con = new SqlConnection("Server=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Trusted_Connection=True;User Instance=yes");    
 con.Open();
 SqlCommand cmd = new SqlCommand("Insert into pro_details values('" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "')", con);
 cmd.ExecuteNonQuery();

Use Connection string in this way

con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Documents and Settings\\Administrator\\My Documents\\Visual Studio 2008\\WebSites\\WebSitelab\\App_Data\\Database.mdf;Integrated Security=True;User Instance=True");

Please check your connection string

string connString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Bodin\\Desktop\\FullFart\\App_Data\\database.mdf;Integrated Security=True;User Instance=True";

its not good to write a insert statement like that , it can lead to sql injection and someone can get your database down with in few minutes... — kobe, Apr 24 '11 at 16:21
I never use these statements i always love to work in 3 tier architecture and pass the parameters to method and will fire a command. As he is very new in VS so i have suggested to use in this way. Atleast he'll start to learn whats the problems going ? — sobby01, Apr 24 '11 at 16:26

score 0 · Answer 4 · edited Apr 24 '11 at 16:21

If you are writing your code in some company, don't code as below , it can lead to SQL Injection.

string sql = "INSERT INTO student (navn, etternavn)  " + "VALUES('" + NavnTextBox.Text + "', '" + EtterNavnTextBox.Text + "');";

change to...

nonqueryCommand.CommandText = "INSERT INTO MyTable VALUES (@MyName, @MyNumber)";
nonqueryCommand.Parameters.Add("@MyName", SqlDbType.VarChar, 30);
nonqueryCommand.Parameters.Add("@MyNumber", SqlDbType.Int);

coming to why it's not workig, you can easily find out by breaking into visual Studio, use debugging options. Use Ctrl + Alt + E and say break on exceptions and select checkBox runtime errors, it can tell you what the error is.

other wise , paste your sql directly i sequel server ad give a try

Why can't I insert data into my database?

4 Answers4