Creating a datasource from a query

[Windsailor]

???? this may get moved again, but here we go.........sigh....

I have a custom sql query that I would like to bind my datagrid too.

In the past I have just used basically simple table objects (joins etc.) to create datasets to bind to my datagrids etc (going through the dataset designer).
Now I am looking into expanding those sql statements or simply writing a custom query and binding it to a datagrid...
What is the best way to do this and what should I be looking for?

 

[mentalhard]

The best approach i know about is using dataadapter object.

Say you want to execute those queries agains MSSQL database you need a native SQLClient dataadapter:

Dim da As New SqlDataAdapter("SELECT blah, blah .. FROM Table...", connectionobject)
Dim ds As New DataSet()
da.Fill(ds, "Table")

datagridcontrol.DataSource = ds.Tables(0)

Certainly in this way you can fetch two and more tables and fill the dataset and then bind your datagrid to ..

HTH

 

[Windsailor]

Oh yes...

That was too good...
Do you know how long I have been going through that darn designer and trying to get everything to fly... with custom (sub query) statements?

Oh my...

 

[mentalhard]

Ok i am glad i can be of help to someone. Usually i come here looking for help myself.
Btw, you can click that reputation icon if i was of help. For n00b like me it would ( i guess) mean something. maybe not? haha

 

[cjard]

Do you know how long I have been going through that darn designer and trying to get everything to fly... with custom (sub query) statements?

What was the actual problem? If you can write this query in code here you can write it into the designer..

Right click table adapter
Add query
"SELECT that fetches rows"
SELECT * FROM table WHERE column = @param1
Give it a logical name

in your form:

myTableAdapter.FillByLogicalName(myDataTable, "this is my parameter1 value")

and now your dt contains the data.. very easy!

 

[cjard]

In the past I have just used basically simple table objects (joins etc.)

A join isnt a simple table object

to create datasets to bind to my datagrids etc (going through the dataset designer).
Now I am looking into expanding those sql statements

but you dont say how.. so I dont really know how to help you

or simply writing a custom query and binding it to a datagrid...
What is the best way to do this and what should I be looking for?

When most people ask for a custom query they mean they want to offer the user 5 text boxes to fill in some all or no value and have it return data in a fixed fashion

For these cases its best to use the designer to make a query like:

SELECT
*
FROM
table
WHERE
(@param1 IS NULL OR col1 = @param1) AND
(@param2 IS NULL OR col2 = @param2) AND
(@param3 IS NULL OR col3 = @param3) AND
(@param4 IS NULL OR col4 = @param4) AND
(@param5 IS NULL OR col5 = @param5)


Now whatever combination of values you give will be used.. if you pass a null for any param, then it becomes a wildcard on that column because "@paramX IS NULL" always evaluates true for that column
If they really do type "hello" into box 2 and that goes to param2, then you end up with a query of:

SELECT * FROM table WHERE
(TRUE) AND
(col2 = "hello") AND
(TRUE) AND
(TRUE) AND
(TRUE)


I've simplified the logic of the other params all null. . hopefully you can see how this works

 

[Windsailor]

Hello cjard...

Originally I was 'learning' how to work with or create subqueries in a SQL statement and I really got locked in on the 'visual' part of it and totally forgot about the manual side of it.

The other thing I realized is that I can assign a dataset (originally just basically naming it without assigning a datasource) and then right clicking and adding a table adapter or SQL statement finishing the dataset at a later point of time. Initially I thought upon creation of a dataset (in the designer) I had to assign an object to it and then later modify it... which was causing me fits... in my basic reports etc.

I do have more questions... but I will have to ask them a little later...

 

[cjard]

Hello cjard...

Originally I was 'learning' how to work with or create subqueries in a SQL statement and I really got locked in on the 'visual' part of it and totally forgot about the manual side of it.

learn complex queries in a query tool, not the ide.. we rarely use complex queries for typed dataset stuff becausse they arent ususally editable queries

If you mean manual side as in "how to write sql" again, use a query tool
If you mena manual side as in "how to write a query in code and run it" -> we tend not to need to these days, though last night I didnt have any luck parameterizing an oracle ALTER SYSTEM KILL SESSION :sessionID command so I had to revert to old/string concat method.. there are some query types that oracle really expects just as plain text.. I could have put it in a sproc and EXECUTE IMMEDIATE it but it still would have been string concat..