CODEWITHSUNDEEP
c#sql-servervisual-studioado.net
javing
javing

Reputation: 12413

Why can't I insert data into my database?

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?

Upvotes: 1

Views: 6886

Answers (4)

Muhammad Faizan
Muhammad Faizan

Reputation: 11

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

Upvotes: 1

kobe
kobe

Reputation: 15835

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

Upvotes: 0

sobby01
sobby01

Reputation: 2154

 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";

Upvotes: 1

marc_s
marc_s

Reputation: 754200

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.....

Upvotes: 3

Related Questions