Replacing values in table with other vaules

[DeltaWolf7]

Hi,

I have a dataset with two tables.
One contains a list of products similar to

model | name | description | price | c1 | c2
------------------------------------------
   x    |    y    |       z        | 99.99 | 1 | 1

etc..

The other contains a list of categories like this.

id | name
1 | something
2 | something else

etc.

My question is how can I go through the columns c1 and c2, replacing the numbers with the name from the other table?

Does that make sence?

Thanks

 

[cjard]

It depends on which database system you are using. Please say which db is in use, then a moderator can move this thread to the appropriate database related forum. I will be able to answer if it is Oracle, Access or SQLS (in order of experience within my skillset, greatest first)

 

[DeltaWolf7]

Ah.. Sorry.

I am working with csv files which I have imported into a dataset.
I want to modify the data layout and then export back to csv for upload to my site.
I am building a util that helps me sort items into the right categories.

Thanks

 

[cjard]

Something like this:

Dim matches() as DataRow
For Each ro as ProductsDataRow in myProductsDataTable.Rows
 
  matches = myCategories.Select("ID = " & ro.c1);
  If matches.Length = 0 Then 
    MessageBox.Show("Unknown ID " & ro.c1)
  Else
    ro.c1 = DirectCast(matches, CategoriesDataRow).name
  End If
 
  'repeat code here for c2, starting with matches = Select...
Next ro

 

[DeltaWolf7]

Thank you for your reply. I am still new to VB.net and dont quite understand. From you code I have this but the parts in bold I am getting errors. Please could you explain them to me?

ERRORS
-------
1. c1 is not a member of system.data.datarow
2. c1 is not a member of system.data.datarow
3. c1 is not a member of system.data.datarow
4. value of type 1-dimensional array of system.data.datarow cannot be converted to system.data.datarow

[LEFT]Dim matches() as DataRow
For Each ro as DataRow in dataset1.tables("NEWDATA").Rows
 
  matches = dataset1.tables("CATEGORIES").Select("ID = " & [B]ro.c1[/B])
  If matches.Length = 0 Then 
    MsgBox("Unknown ID " & [B]ro.c1[/B])
  Else
    [B]ro.c1[/B] = DirectCast([B]matches[/B], DataRow).name
  End If
 
Next ro[/LEFT]

 

[cjard]

You didnt really paste my code directly; you changed it to non-typed form.

1) you have a typed dataset called Dataset1
2) it contains a typed datatable called NEWDATA
3) because you accessed this in non-typed fashion:
For Each ro as DataRow in dataset1.tables("NEWDATA").Rows
3.1) and you declared the ro as a generic DataRow srather than a specific, typed data row from the categories table:
For Each ro as DataRow in dataset1.tables("NEWDATA").Rows
3.2) then it means you wont have property based access to the row elements

When working with typed data tables, you should ALWAYS avoid reverting them to untyped mode

Typed:
For Each ro as NEWDATADataRow in dataset1.NEWDATA.Rows

Untyped:
For Each ro as DataRow in dataset1.tables("NEWDATA").Rows


Can you see the difference?


Similarly on this line:
matches = dataset1.tables("CATEGORIES").Select("ID = " & ro.c1)

this is the typed version:
matches = dataset1.CATEGORIES.Select("ID = " & ro.c1)

-

[B]ro.c1[/B] = DirectCast([B]matches[/B], DataRow).name

4. value of type 1-dimensional array of system.data.datarow cannot be converted to system.data.datarow

This one was my bad - i made a small syntax error that is very easy to correct. I'm going to leave it as an excercise for you to correct, with the following advice:

matches is an ARRAY of datarow, and you cannot cast an array of rows into being a single row any more than you can assert a 6-pack of beer is a single beer.
Presumably it is the first row in matches that you will want - how will you get it out?

-

Sorry for the mistakes - I dont always get it 100% right when typing blind, but its usually a good thing because it keeps you thinking about the problem

 

[DeltaWolf7]

Oh my god you are a genius and your description are wicked. I cant thank you enough. I will post my results back and again many, many thanks for your help and the exercise.

 

[DeltaWolf7]

Ok, also got it.

My code so far..

[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] TextBox2.Text <> [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]DataSet1.Tables.Add(CSVtoDatatable(TextBox2.Text, [/SIZE][SIZE=2][COLOR=#800000]"CATEGORIES"[/COLOR][/SIZE][SIZE=2]))
DataSet1.Tables.Add(CSVtoDatatable(TextBox1.Text, [/SIZE][SIZE=2][COLOR=#800000]"PRODUCTS"[/COLOR][/SIZE][SIZE=2]))
 
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] matches() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow
[/SIZE][B][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] ro [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] productsDataRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][/B][SIZE=2][B] DataSet1.products.Rows[/B]
matches = DataSet1.CATEGORIES.Select([/SIZE][SIZE=2][COLOR=#800000]"ID = "[/COLOR][/SIZE][SIZE=2] & ro.c1)
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] matches.Length = 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#800000]"Unknown ID "[/COLOR][/SIZE][SIZE=2] & ro.c1)
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]ro.c1 = [/SIZE][SIZE=2][COLOR=#0000ff]DirectCast[/COLOR][/SIZE][SIZE=2](matches(ro.c1), DataRow).name
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE][SIZE=2] ro
[/SIZE]

The error I am getting now is "Type 'productsDataRow' is not defined" and "'products' is not a member of 'system.data.dataset'"
In respect to this error, is it because my dataset is declared visually on the form designer? Oh and by the way I have renamed NEWDATA to products and changed all the references to it.

I am going to read up on typed and untyped data source in hope to beat you to the solution.

I think I managed to do the exercise you gave me. Correct me if I am wrong.

 

[DeltaWolf7]

Out for interest I came accross this artical on Google while search for typed datsets. In your professional opinion, do you think its a good way to do things because I was planning to use it to try and create two tables to import my csv and then use relation ships and keys from table to table to join products to categories.

Possible?

 

[cjard]

Which article on google? Your post didnt seem to contain a link. Additionally, your datatables dont appear to be typed - for that youhave to design them - youre using an untyped dataset with 2 untyped tables called products and categories.. Have you tried using the dataset designer to design a typed dataset?