CODEWITHSUNDEEP
c#sqlsql-serverdatabase
Geo Baby
Geo Baby

Reputation: 1

Cannot add data into database in visual c#

As a beginner to c#, and I actaully spent a lot of time researching this: I cannot add some data into the database, I can extract data from it, but cannot add anything into the database. I use sql server as my database. 

try {    
    fname = fname_tb.Text;// first name
    sname = sname_tb.Text; // second name
    q = "insert into beforebath1(firstname,secondname) values(@fname,@sname)";
    conn_string = Properties.Settings.Default.beforebath_connection_string;
    SqlConnection co = new SqlConnection(conn_string);
    SqlCommand cmd;

    co.Open();
    cmd = new SqlCommand(q, co);
    cmd.Connection = co;
    cmd.Parameters.AddWithValue("@fname", fname_tb.Text);
    cmd.Parameters.AddWithValue("@sname", sname_tb.Text);
    cmd.ExecuteNonQuery();
    co.Close();  
}
catch(Exception err) {
    MessageBox.Show(err.toString());   
}

my sql connection string is this:

Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\beforebath_db.mdf;Integrated Security=True;Connect Timeout=30

It is automatically generated when I created the database. Please help me insert the text in the two textboxes (fname_tb.Text and sname_tb.Text) into the table called beforebath1 of the database called beforebath_db.mdf.

Is it something to do with my data directory?

Upvotes: 0

Views: 1216

Answers (3)

HaMi
HaMi

Reputation: 539

For better implementations you can use stored_procedures like bellow:

Step1: Declare Stored Procedure for your Query:

CREATE PROCEDURE [dbo].[ADD_TO_BEFORE_PATH_SP]
    /*Type of this variables should be their column types*/
    @fname varchar(MAX),
    @lname varchar(MAX)
AS
BEGIN
    INSERT INTO [dbo].[beforebath1] (fname, lname)
           VALUES (@fname,@lname)
END

Step2: Using Stored Procedure where you need:

SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand("ADD_TO_BEFORE_PATH_SP", con);
com.Parameters.AddWithValue("@fname", fname_tb.Text);
com.Parameters.AddWithValue("@lname", lname_tb.Text);
com.CommandType = CommandType.StoredProcedure;
try
{
    con.Open();
    com.ExecuteNonQuery();
}
catch (Exception)
{
    throw;
}
finally
{
    if (con.State == ConnectionState.Open)
        con.Close();
}

Upvotes: 0

Will Marcouiller
Will Marcouiller

Reputation: 24142

I see a couple of mistakes in your code.

First, why catch an exception that will only be shown in a message?

It is often best to let the exception bubble up to have the stack trace in debug. This is not the same if this is production code, which I doubt.

Second, make sure to dispose your objects adequately.

The Using Statement is the most prefered way to work with disposeable items such as a database connection and a command.

using (var cnx = new SqlConnection(connectionString)) {
    cnx.Open();

    var sql = @"insert into beforebath1 (first_name, second_name)
                values (@fname, @lname)";

    using (var cmd = new SqlCommand(sql, cnx)) {
        cmd.Parameters.AddWithValue("@fname", fname_tb.Text);
        cmd.Parameters.AddWithValue("@lname", lname_tb.Text);

        try {
            int rowsAffected = cmd.ExecuteNonQuery();
            if (0 < rowsAffected) MessageBox.Show("Success!");
            else MessageBox.Show("Failed!");
        } catch (SqlException ex) {
            // It is almost prefered to let the exception be thrown freely
            // so that you may have its full stack trace and have more 
            // details on your error.
            MessageBox.Show(ex.Message);
        } finally {
            if (cnx.State == ConnectionState.Open) cnx.Close();
        }
    }
}

This way, wrapping your disposable objects within using blocks, you make sure that everything is getting to get disposed automatically when exiting the code block.

As for your "it doesn't work" problem, I guess the problem be either at the connection string level, or at your table_name level.

You wish to insert into beforebath1, and your insert statement states table_name. Make sure you put the right table name where it belongs so that it may work properly.

Upvotes: 1

Allen
Allen

Reputation: 165

Can you change you connection string to this:

Server=(LocalDB)\v11.0;Database=beforebath_db;Trusted_Connection=True;

This means the your app and other programs using the Db will all share the same instance.

Also, as mentioned by @Will, you should wrap your SQLConnection in a using statement for garbage collection.

Upvotes: 0

Related Questions