update column in a table

Question

hi i have a situation where-in i want to update a column in the database with one of the fields from the excel sheet. My excel sheet looks something like this : Excel file : ID Name Desc Colourful-Books 1 red hot 2 blue cool 3 black Dark Non-Colourful 1 white pale 2 white1 serene 3 white2 calm after importing the excel file the sql table Book looks like this: ID BOOKNAME DESCRIPTION TYPE 1 Colourful-Books NULL 2 red hot 3 blue cool 4 black dark 5 NULL NULL 6 Non-Colourful NULL 7 white pale 8 white1 serene 9 white2 calm what i want to do is update the type column of the table Book: ID BOOKNAME DESCRIPTION TYPE 1 Colourful-Books NULL NULL 2 red hot Colourful-Books 3 blue cool Colourful-Books 4 black dark Colourful-Books 5 NULL NULL NULL 6 Non-Colourful NULL NULL 7 white pale Non-Colourful 8 white1 serene Non-Colourful 9 white2 calm Non-Colourful can someone please give me some suggestions. thanks.. (sorry tried formatting the data in a table format..wasnt successful.... :( )

david 0 · Answer

Vic... Did you READ my previous post [and please stop posting multiple threads!] [...assuming...] DataTable myTable; [...then...] myTable.Columns.Add(new DataColumn(myType)); string val = ; foreach (DataRow row in myTable.Rows) { if (row[1] != null) value = row[1].ToString(); else row[2]=value; }

viktors · Answer

can someone please help...

viktors · Answer

hi TheCPUWizard what i have been doing till now is importing the contents from an excel sheet and dumping into a sql table. i am using a stored procedure to insert and using the dataset. SqlConnection conn1 = new SqlConnection(ConnStr); SqlCommand cmdinsert = new SqlCommand (sp_insertbooksconn1); cmdinsert.CommandType = CommandType.StoredProcedure; try { conn1.Open(); foreach( DataRow dr in myDataSet.Tables[0].Rows) { SqlParameter param1 = new SqlParameter(@bookname SqlDbType.VarChar255); param1.Value = dr[1]; cmdinsert.Parameters.Add(param1); SqlParameter param2 = new SqlParameter(@bookdesc SqlDbType.VarChar255); param2.Value = dr[2]; cmdinsert.Parameters.Add(param2); SqlParameter param3 = new SqlParameter(@booktype SqlDbType.VarChar255); param3.Value = ; cmdinsert.Parameters.Add(param3); cmdinsert.Parameters.Remove(param1); cmdinsert.Parameters.Remove(param2); cmdinsert.Parameters.Remove(param3); } Response.Write( Record Added ); } catch (Exception ex) { Response.Write(ex.ToString()); } finally { cmdinsert.Connection.Close(); } Now what i dont understand is how do i update the column booktype depending on the type that it is. for eg. redhotblueblack need to be updated to colourful and white needs to be updated to non-colorful. i am new to .NET and not able to proceed here. and thanx for ur help.

david 0 · Answer

The code you need is contained in the brackets in my previous post. Please post the code you have and I will take a look at it. Remember it is no fun if somebody else does ALL the work!

viktors · Answer

i dont know how to : add a column to a table with one of the rows in the table. eg. 1 colourful null null 2 red hot colourful 3 blue cool colourful 4 black dark colourful 5 non-colourful null null 6 white pale non-colourful 7 white serene non-colourful 8 white white2 non-colourful 1) Adding a column dynamicly to a table [e.g. myTable.Columns.Add(new DataColumn(...))]. 4) storing the contents of of a column from a specific row in a variable [e.g. myVar = row[...]]

david 0 · Answer

So what is the problem? 1) Adding a column dynamicly to a table [e.g. myTable.Columns.Add(new DataColumn(...))]. 2) Looping through a set of rows [e.g. foreach (DataRow row in myTable.Rows) { ... } ] 3) Checking a column [e.g. if (row[... == null)] 4) storing the contents of of a column from a specific row in a variable [e.g. myVar = row[...]] The list goes on.....

viktors · Answer

yeah something like that..

david 0 · Answer

Sound like you want to: a) Add a column to the table b) Iterate through the table b1) If column 1 [the second one] is null latch the value in column 0 b2) If column 1 is not null place the latched value in column 2