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_insertbooks",conn1);
cmdinsert.CommandType = CommandType.StoredProcedure;
try
{
conn1.Open();
foreach( DataRow dr in myDataSet.Tables[0].Rows)
{
SqlParameter param1 = new SqlParameter("@bookname", SqlDbType.VarChar,255);
param1.Value = dr[1];
cmdinsert.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@bookdesc", SqlDbType.VarChar,255);
param2.Value = dr[2];
cmdinsert.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter("@booktype", SqlDbType.VarChar,255);
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. red,hot,blue,black 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