Question Special Access Database Query

[TomK]

First time poster, go easy please.

I'm new to using access databases in VB.NET. I'm using VS 08 express.

I have 2 databases that are provided to me by 2 different sources, i'm trying to make an interface that allows viewing of data from both at the same time, as well as comparisons of data.

I've been playing with accessing the data, and have been somewhat successful.

I'm using a function that takes a few variables..
-name of the table from which i want to pull the data
-name of the field that i want data from
-name of the field that i'm using as the index
-entry that i'm looking for in the index field


example:

table2
colmn 1 | colmn 2 | colmn 3
data 1.1 data 1.2 data 1.3
data 2.1 data 2.2 data 2.3

calling myfunction(table2 as strTableToSearch, colmn 1 as strIndexColmn, data 2.1 as strEntryToLookfor, colmn 3 as strWantedReturnField)

would return "data 2.3"


I have the function in a class so i can create an instance for each database.

In the function i'm using the .ExecuteScalar method following a OleDbCommand
I'm setting the OleDbcommand.text with the varriables passed to my function...

This all seemed to be working perfectly until I found that the database i wasn't testing with had spaces in the Table name, AND some of the colmn names.

I've done some playing with my oledbcommand.text adding and every combination i can think of [ ], ' ', {}, ` `, .. none of them seem to allow for the spaces in the Table name and the First colmn name of my oledbcommand.text string.

here's an example of my string with out the varriables
.CommandText = "Select [Call Letters],[Market] FROM [Computer] WHERE [Market] = 'DON-2007'"

this works sometimes but not always, errors occuring mainly when the table name (in that example "Computer") has spaces in it.

Any help?

If i need to provide any other information please let me know.

PS i've done quite a bit of google searching, Maybe someone else has some better keywords to search for to point me in the right direction. Maybe someone even has a class already written to handle this.


Thanks in advance - Tom

 

[cjard]

take a read of the DW2 link in my signature, section Creating a Simple Data App. Using it you will be able to link both dbs to your app

As for merging the tables so that they appear with rows interspersed.. that implies your tables have the same structure. In this case, one datset will do, set the TableAdapter's COnnectionModifier property to PUBLIC and then you can tweak the connection string at runtime to select data from each table into the one datatable

 

[Tom]

take a read of the DW2 link in my signature, section Creating a Simple Data App. Using it you will be able to link both dbs to your app

I have 2 databases that are provided to me by 2 different sources, i'm trying to make an interface that allows viewing of data from both at the same time.

cjard, the dw2 "Creating a Simple data App" displays data from two related tables in a database.

TomK seems to be indicating two different databases not two tables within a single database.

Although the walk-thru does show how to add a datasource to a project, and the same method can be used multiple times to add additional datasources to the same project.

Essentially, however you decide to create your multiple database connections, whether thru a wizard or direct coding, you will need seperate connection objects for each of the different databases.

 

[cjard]

TomK seems to be indicating two different databases not two tables within a single database.

Not something I would be too worried about, given that datasets are not databases.. it's perfectly possible to have one dataset, two datasources and have a relation.. You fill parent data from one db and child data from another db and then work with it locally.. Setting the ConnectionModifier property of the tableadapter to Public allows you to change the connectionstring used by the TA.. You can also have tables from different databases in one dataset