C# insert data from datatable to SQL Server database

Question

I have tried nearly every solution on this website, but I can't solve this problem. I have data that has been retrieved from a database through an ODBC connection. The data is there. It will go into a Data Grid View just fine, but I can't get this data to go into my local SQL database. Please tell me what I'm doing wrong.

    public partial class frmNorth : Form
{
        // variables for the connections 
        private OdbcConnection epnConnection = new OdbcConnection();
        private SqlConnection tempDbConnection = new SqlConnection();
public frmNorth()
{
    InitializeComponent();
    // This is for the ePN DB
    epnConnection.ConnectionString = @"Dsn=ePN; uid=username; pwd=myPa$$Word";
    // This is for the local DB
    tempDbConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\TempDB.mdf;Integrated Security=True";
}
private void btnLoadData_Click(object sender, EventArgs e)
{
    try
        {
            //===This part works just fine===============================================================
            epnConnection.Open();
            string epnQuery =   "SELECT FNCL_SPLIT_REC_ID, PROJ_ID, SALES_SRC_PRC " +
                                "FROM PROJ_FNCL_SPLIT " +
                                "WHERE PROJ_ID=" + textBox1.Text + "";
            OdbcCommand epnCommand = new OdbcCommand(epnQuery, epnConnection);
            epnCommand.CommandTimeout = 0;

            //This connects the data to the data table
            OdbcDataAdapter da = new OdbcDataAdapter(epnCommand);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            //===========================================================================================


            //======The part below is the part that wont work. The data wont go into the SQL database====
            tempDbConnection.Open();
            string tempSql = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tempSql =   "INSERT INTO tblTemp (FNCL_SPLIT_REC_ID, PROJ_ID, SALES_SRC_PRC) VALUES ('"
                            + dt.Rows[i]["FNCL_SPLIT_REC_ID"].ToString().Trim() + "','"
                            + dt.Rows[i]["PROJ_ID"].ToString().Trim() + "','"
                            + dt.Rows[i]["SALES_SRC_PRC"].ToString().Trim() + "');";
                SqlCommand tempCommand = new SqlCommand(tempSql, tempDbConnection);
                tempCommand.ExecuteNonQuery();
            }
                // There are no errors. The data just doesn't save to the database.
            //===========================================================================================

            epnConnection.Close();
            tempDbConnection.Close();

        }
        catch (Exception ex)
        {
            epnConnection.Close();
            tempDbConnection.Close();
            MessageBox.Show("Error " + ex);
        }
    }
}
}

    //+++++++++++++++++++This is what the table looks like+++++++++++++++++++++++++++++++++++++++++++++++
    CREATE TABLE [dbo].[tblTemp] (
[FNCL_SPLIT_REC_ID] INT        NOT NULL,
[PROJ_ID]           NCHAR (10) NULL,
[SALES_SRC_PRC]     MONEY      NULL,
PRIMARY KEY CLUSTERED ([FNCL_SPLIT_REC_ID] ASC)

Like I said no errors come up. The data just doesn't save to the database.

You need to step through your code line by line and observe it running. Then you need to use SQL Profiler to observe the query being executed — Nick.Mc
– Nick.Mc, Commented Nov 10, 2016 at 4:58
As you do not get error and verified execution! Are you checking the correct database and table for insertion? — Adil
– Adil, Commented Nov 10, 2016 at 5:01

Mohit S · Accepted Answer · 2016-11-10 04:54:13Z

2

"INSERT INTO tblTemp (FNCL_SPLIT_REC_ID, PROJ_ID, SALES_SRC_PRC) VALUES ("
                    + dt.Rows[i]["FNCL_SPLIT_REC_ID"].ToString().Trim() + ",'"
                    + dt.Rows[i]["PROJ_ID"].ToString().Trim() + "',"
                    + dt.Rows[i]["SALES_SRC_PRC"].ToString().Trim() + ");";

Removed the ' ' between FNCL_SPLIT_REC_ID as it is int and SALES_SRC_PRC since it is money.

answered Nov 10, 2016 at 4:54

Mohit S

14.1k6 gold badges36 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PNP · Accepted Answer · 2016-11-10 05:54:57Z

I found no error on code you implemented. I found that connection definition for mdf file was wrong.

|DataDirectory| setting path to the folder where application run. In this case if we run in Debug mode, it will create separate application exe in Debug\bin folder with application resources like .mdf files. or in Release mode it will create particular folders inside release folder. So you need to change Database File name for database connection or you need to give entire directory path for connection string. Example

tempDbConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\TempDB.mdf;Integrated Security=True";
}

replace

tempDbConnection.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Promod\Documents\Visual Studio 2012\Projects\Contribution1\Contribution1\bin\Debug\TempDB.mdf;Integrated Security=True";

Collectives™ on Stack Overflow

C# insert data from datatable to SQL Server database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related