'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Question

I am trying to use Sql in visualstudio using c# but it throws an unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

 comm = new SqlCommand("INSERT INTO HafizwalaTable(DistrictName, TownName, FarmerName, " +
 "Area,  VarietyOfCrop, SowingDate, VisitDate, PestPopulation1, " + 
 "PestPopulation2, PestPopulation3, PestPopulation4, PestPopulation5, " + 
 "PestPopulation6, PestPopulation7, PestPopulation8, PestPopulation9, " + 
 "PestPopulation10, PestPopulation11, PestPopulation12, PesticideUsed, " + 
 "PesticideSprayDate, PesticideDosage, CLCV, PlantHeight) " + 
 "VALUES @p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11, " +
 "@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24)", con);

comm.Parameters.AddWithValue("@p1",FileReaderDataArray[RowNo, 0]);

comm.Parameters.AddWithValue("@p2",FileReaderDataArray[RowNo, 1]);

comm.Parameters.AddWithValue("@p3",FileReaderDataArray[RowNo, 2]);

comm.Parameters.AddWithValue("@p4", FileReaderDataArray[RowNo, 3]);

comm.Parameters.AddWithValue("@p5", FileReaderDataArray[RowNo, 4]);

comm.Parameters.AddWithValue("@p6", FileReaderDataArray[RowNo, 5]);

comm.Parameters.AddWithValue("@p7", FileReaderDataArray[RowNo, 6]);

comm.Parameters.AddWithValue("@p8", FileReaderDataArray[RowNo, 7]);

comm.Parameters.AddWithValue("@p9", FileReaderDataArray[RowNo, 8]);

comm.Parameters.AddWithValue("@p10", FileReaderDataArray[RowNo, 9]);

comm.Parameters.AddWithValue("@p11", FileReaderDataArray[RowNo, 10]);

comm.Parameters.AddWithValue("@p12", FileReaderDataArray[RowNo, 11]);

comm.Parameters.AddWithValue("@p13", FileReaderDataArray[RowNo, 12]);

comm.Parameters.AddWithValue("@p14", FileReaderDataArray[RowNo, 13]);

comm.Parameters.AddWithValue("@p15", FileReaderDataArray[RowNo, 14]);

comm.Parameters.AddWithValue("@p16", FileReaderDataArray[RowNo, 15]);

comm.Parameters.AddWithValue("@p17", FileReaderDataArray[RowNo, 16]);

comm.Parameters.AddWithValue("@p18", FileReaderDataArray[RowNo, 17]);

comm.Parameters.AddWithValue("@p19", FileReaderDataArray[RowNo, 18]);

comm.Parameters.AddWithValue("@p20", FileReaderDataArray[RowNo, 19]);

comm.Parameters.AddWithValue("@p21", FileReaderDataArray[RowNo, 20]);

comm.Parameters.AddWithValue("@p22", FileReaderDataArray[RowNo, 21]);

comm.Parameters.AddWithValue("@p23", FileReaderDataArray[RowNo, 22]);

comm.Parameters.AddWithValue("@p24", FileReaderDataArray[RowNo, 23]);


comm.ExecuteNonQuery();

Plus all the columns have the data type nvarchar

actually i am new it i dont any other way to write the query . — user1056466, Nov 14 '13 at 20:22
it looks Okay from your sql command please share your connection string — Sudhakar Tillapudi, Nov 14 '13 at 20:33
SqlConnection con = new SqlConnection("Data Source=(localdb)\\Projects;Initial Catalog=master;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False"); — user1056466, Nov 14 '13 at 20:46
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll — user1056466, Nov 14 '13 at 21:17

score 0 · Answer 1 · edited May 23 '17 at 12:28

You should use a parameterized query to avoid that mess. Something like this

try
{

    string cmdText = "INSERT INTO HafizwalaTable(DistrictName, " + 
        "TownName, FarmerName, Area, VarietyOfCrop, SowingDate, VisitDate, PestPopulation1, " + 
        "PestPopulation2, PestPopulation3, PestPopulation4, PestPopulation5, " + 
        "PestPopulation6, PestPopulation7, PestPopulation8, PestPopulation9, " + 
        "PestPopulation10, PestPopulation11, PestPopulation12, PesticideUsed, " + 
        "PesticideSprayDate, PesticideDosage, CLCV, PlantHeight) " + 
        "VALUES(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14, "
        "@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24)";

    using(SqlConnection con = new SqlConnection(GetConnectionString())
    using(SqlCommand comm = new SqlCommand(cmdText, con);
    {
        comm.Parameters.AddWithValue("@p1",FileReaderDataArray[RowNo, 0]);
        comm.Parameters.AddWithValue("@p2",FileReaderDataArray[RowNo, 1]);
        comm.Parameters.AddWithValue("@p3",FileReaderDataArray[RowNo, 2]);

        ..... and so on, add the other parameters. all 24 if I have counted them well

        comm.ExecuteNonQuery();
     }
 }
 catch(Exception ex)
 {
     MessageBox.Show(ex.Message);
 }

You should use a parameterized query to avoid problem in parsing values (what happen if one or more of your FileReaderDataArray strings contains a single quote?) and avoid problems with Sql Injection, and finally you get a cleaner command string without the quoting mess required by string concatenation

ok i have parametrized the query as you said but can you find the issue in it ..why tool is throwing the exception — user1056466, Nov 14 '13 at 20:41
I need to know the exact error message. Try to add a try/catch and tell us the exception message. Will show in the answer above — Steve, Nov 14 '13 at 20:45
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Invalid object name 'HafizwalaTable'. — user1056466, Nov 14 '13 at 21:23
It seems pretty clear right? You don't have a table named `HafizwalaTable` — Steve, Nov 14 '13 at 21:58
i had the issue in connection string ... thanks for teaching parameterized queries ty — user1056466, Nov 14 '13 at 22:37

'System.Data.SqlClient.SqlException' occurred in System.Data.dll

1 Answers1