I read an excel sheet into a datagrid.From there , I have managed to read the grid's rows into a DataTable object.The DataTable object has data because when I make equal a grid's datasource to that table object , the grid is populated.
My Problem : I want to use the table object and manipulate its values using SQL server,(i.e. I want to store it as a temporary table and manipulate it using SQL queries from within C# code and , I want it to return a different result inte a grid.(I don't know how to work with temporary tables in C#)
Here's code to execute when clicking button....
SqlConnection conn = new SqlConnection("server = localhost;integrated security = SSPI");
//is connection string incorrect?
SqlCommand cmd = new SqlCommand();
//!!The method ConvertFPSheetDataTable Returns a DataTable object//
cmd.Parameters.AddWithValue("#table",ConvertFPSheetDataTable(12,false,fpSpread2_Sheet1));
//I am trying to create temporary table
//Here , I do a query
cmd.CommandText = "Select col1,col2,SUM(col7) From #table group by col1,col2 Drop #table";
SqlDataAdapter da = new SqlDataAdapter(cmd.CommandText,conn);
DataTable dt = new DataTable();
da.Fill(dt); ***// I get an error here 'Invalid object name '#table'.'***
fpDataSet_Sheet1.DataSource = dt;
//**NOTE:** fpDataSet_Sheet1 is the grid control
Change your temp table from #table to ##table in both places.
Using ## means a global temp table that stays around. You'll need to Drop it after you have completed your task.
Command = " Drop Table ##table"