Saving Data from a gridview into a local database in asp.net

Question

I am currently writing a piece of code where the user is supposed to insert a few information about an employee and press one button populate for populating a gridview and another one to save the information in gridview into a local database. While running the what I wrote so far there is a consistent error saying "SqlExeption was unhandled by the user code. I have been trying to fix it but without success. It complains on conn.Open();

This is that specific piece of code:

protected void SaveButton_Click(object sender, EventArgs e)
{
    string StrQuery;

    try
    {
        using (SqlConnection conn = new SqlConnection(@"Data Source = C:\EmployeeWebProject\EmployeeWebProject\App_Data\EmployeeDatabase.sdf"))
        {
            using (SqlCommand comm = new SqlCommand("SELECT * FROM Employee"))
            {
                comm.Connection = conn;

                conn.Open();

                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    StrQuery = @"INSERT INTO Employee VALUES ("
                                + GridView1.Rows[i].Cells[0].ToString() + ", "
                                + GridView1.Rows[i].Cells[1].ToString() + ", "
                                + GridView1.Rows[i].Cells[2].ToString() + ", "
                                + GridView1.Rows[i].Cells[3].ToString() + ", "
                                + GridView1.Rows[i].Cells[4].ToString() + ");";
                    comm.CommandText = StrQuery;
                    comm.ExecuteNonQuery();
                }
            }
        }
    }
    finally
    {
    }
}

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Sep 14, 2016 at 13:59
You need to provide more data about the error - check inner exceptions, include full error message etc. — mason
– mason, Commented Sep 14, 2016 at 14:00
+10000000 to @marc_s comments about sql injection. Most likely you are getting an error because your query is invalid. I assume you have string values, but you don't have any single quotes in your query. Parameterizing this not only will prevent the vulnerability, it will also fix the errors. — Sean Lange
– Sean Lange, Commented Sep 14, 2016 at 14:02
Furthermore, if you're using a .sdf file, that's SQL Server CE - so you should use SqlCeConnection and SqlCeCommand - the one you're using now are for the full-fledged, desktop-/server-version of SQL Server (Express, Web, Developer, Standard, Enterprise etc. - but not Compact Edition!) — marc_s
– marc_s, Commented Sep 14, 2016 at 14:17
SqlConnection in the same code as a _Click event, makes my eyes hurt. layersample.codeplex.com or google "dotnet layers" — granadaCoder
– granadaCoder, Commented Sep 14, 2016 at 14:59

marc_s · Accepted Answer · 2016-09-14 14:23:24Z

To avoid SQL injection and use properly parametrized queries, and also use the SQL Server CE connection and command objects, try this code:

protected void SaveButton_Click(object sender, EventArgs e)
{
    string StrQuery;

    try
    {
        // define connection string and INSERT query WITH PARAMETERS
        string connectionString = @"Data Source = C:\EmployeeWebProject\EmployeeWebProject\App_Data\EmployeeDatabase.sdf";
        string insertQry = "INSERT INTO Employees(Col1, Col2, Col3, Col4, Col5) " + 
                           "VALUES(@Col1, @Col2, @Col3, @Col4, @Col5);";

        // define connection and command for SQL Server CE
        using (SqlCeConnection conn = new SqlCeConnection(connectionString))
        using (SqlCeCommand cmd = new SqlCeCommand(insertQry, conn))
        {
            // add parameters to your command - adapt those *as needed* - we don't know your table structure,
            // nor what datatype (and possibly length) those parameters are !
            cmd.Parameters.Add("@Col1", SqlDbType.Int);
            cmd.Parameters.Add("@Col2", SqlDbType.VarChar, 100);
            cmd.Parameters.Add("@Col3", SqlDbType.VarChar, 100);
            cmd.Parameters.Add("@Col4", SqlDbType.VarChar, 100);
            cmd.Parameters.Add("@Col5", SqlDbType.VarChar, 100);

            conn.Open();

            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                // set parameter values
                cmd.Parameters["@Col1"].Value = Convert.ToInt32(GridView1.Rows[i].Cells[0]);
                cmd.Parameters["@Col2"].Value = GridView1.Rows[i].Cells[1].ToString();
                cmd.Parameters["@Col3"].Value = GridView1.Rows[i].Cells[1].ToString();
                cmd.Parameters["@Col4"].Value = GridView1.Rows[i].Cells[1].ToString();
                cmd.Parameters["@Col5"].Value = GridView1.Rows[i].Cells[1].ToString();

                cmd.ExecuteNonQuery();
            }
        }
    }
    finally
    {
    }
}

Collectives™ on Stack Overflow

Saving Data from a gridview into a local database in asp.net

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related