Data Not added to SQL Database?

[berwick53]

Hi, I'm currently learning C# and I'm currently learning how to do C# and Database's. Does anyone know why it isn't adding the data in the text boxes to the db.

Cheers Sam

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace SQL_ATTEMPT_2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cs = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True");
            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = new SqlCommand("INSERT INTO tblUsers VALUES(@USERNAME, @PASSWORD)", cs);
            da.InsertCommand.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = textBox1.Text;
            da.InsertCommand.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = textBox2.Text;
            MessageBox.Show(textBox1.Text);
            cs.Open();
            da.InsertCommand.ExecuteNonQuery();
            cs.Close();
        }
    }
}

[gusano79]

Hi, I'm currently learning C# and I'm currently learning how to do C# and Database's. Does anyone know why it isn't adding the data in the text boxes to the db.

Do you get any error messages when you try to run it, or does it just fail silently? I was able to get your code to run against a SQL server with my own connection string, and everything inserted fine.

A few notes:

Your connection string doesn't specify a database name. You should probably add a "Database=somedatabasename" bit in there.

You don't need a SqlDataAdapter for what you're doing here; you can just create a SqlCommand object and use it directly.

It's considered good practice to create connections in a using block, like this:

using(SqlConnection cs = new SqlConnection("connection string"))
{
    cs.Open();

    // Your code here
}

The using block automatically closes the connection for you, even if an exception is thrown. Much safer that way.

[Momerath]

IIRC, while using SQLExpress and running your program in debug mode, it makes a copy of the database file. This copy is used during the running of the program, then deleted when the run is over. This is so you can start debugging again from exact same conditions you started with each time.

So you won't see any changes persisted to the database.

[nick.crane]

So you won't see any changes persisted to the database.

Actually, if the database file in the project is marked as "Copy if newer" then it will only overwrite the copy in the Debug folder if you make changes to the database structure.

Also, the changes you make while running the program are written to the copy of your DB in the ..\bin\Debug folder.
You can examine this if you add this DB to your Server Explorer window; but always remember to close the connection before testing your program otherwise your program might not be able to open the DB.

[berwick53]

Thanks for the help but because I'm a student I found out that I can get free software off of Microsoft using Dreamspark, so I downloaded SQL Server 2008 R2 and It works fine.

[thines01]

If you're still interested in this one, I've always found specifically naming the columns will prevent problems with SQL Server

"INSERT INTO tblUsers (USERNAME, PASSWORD) VALUES(@USERNAME, @PASSWORD)"