HELP dont know where to begin

[coldfusion]

My first post :)

 

I have a small problem I have an Access Database with several tables. One of these tables is a list of sites in one column and in each sites row is list of infomation stored in one cell but in delimited format. how do i get VB to search for a pericular site then pull all the delimted information in that row into my application (dataset or other) so i can display it in a data grid or preform a SQL search on it.

 

sorry not very clear hope this helps a little comma being the delimiter

 

table is called sites

 

---------------------------------------------------------------------------------

0 | 1 | 2 | 3 |

---------------------------------------------------------------------------------

London | 1,2,3,4, | mon,tues,wed,thur | 320,400,300,200 |

---------------------------------------------------------------------------------

Exeter | 1,2,3,4 | mon,tues,wed,thur | 450,500,630,230 |

---------------------------------------------------------------------------------

Hull | 1,2,3,4 | mon,tues,wed,thur | 340,450,500,300 |

---------------------------------------------------------------------------------

 

 

what i want is to be able to select either london,exeter,hull from a dropbox and then it builds a dataset/recordset for example

called london that looks like this

 

 

-------------------------------------

0 | 1 | 2 |

-------------------------------------

1 | mon | 320 |

--------------------------------------

2 | tues | 400 |

--------------------------------------

3 | wed | 300 |

--------------------------------------

4 | thur | 200 |

--------------------------------------

 

does that make sence sorry for not being clearer

 

thanks in advance you'll be saving my bacon :)

[pcf108]

Unless I'm mistaken, The only provider that reads delimited data is the ODBC Text-File Driver...

 

That however requires a file path instead of a stream, and to write every column to a file then re-read each one into a dataset would be ridiculous.

 

I'd say your best bet is manual generation of a datatable, by creating a column for each field and parse the comma-delimited strings to populate the row.

[Nerseus]

Why in the world would you want your relational database to hold flat-file information? I would think you'd want all that data in the Access database in separate columns. Or if you already have it in Access, then create a separate table to hold the "split" data - then you could delete the original pipe-delimited data if you don't need it for archival purposes.

 

Once in a relational format, you can easily get each column separately and do the search you want.

 

If for some crazy reason you don't want to do it that way, you could run a search like:

SELECT SubString(field, CharAt(field, '|')) ...

 

That's SQL Server syntax (the SubString and CharAt functions) - I'm sure Access has equivalents (probably Right or Mid and IndexOf) if you need them.

 

-Ner

[coldfusion]

Unfortunatley this is how the access database comes and its not an option to change this file as this file is continualy updated and would mean every time it is updated I would have to recreate the tables before sending back out to the people using the program :(

 

complete bummer i know

 

how would the select statement you gave Nerseus work to be able me to populate text boxes with all the corresponding values?

 

ie when i do a search in london say for value mon i get the corresponding values 1 and 320. makes sence?