Generating SQL on the fly?

[Ross Contino]

Howdy:

I am an experienced VB 6.0 database programmer trying to write first VS 2005
application in VB 2005. I want to create an end user defined query and pass
it to the program. Specifically, I want to pass a date range, ie:

SELECT * FROM MYDATES WHERE ((theDate) Between #8/1/2006# And #8/7/2006#);

However, the user wants to enter the date range on his own. In the past I
would generate the SQL on the fly and pass it to a datacontrol as the
datasource. Now it seems Queries are made with the Query Builder and stored
in "WhateverDataSe t.xsd" file.

How do I retrieve the current "Text" that defineds the query at runtime,
change it, and refresh it to display a new date range?

Eventually, I want to pass this same range to a report. Is that possible?

Thanks,
Ross

[Smokey Grindel]

Re: Generating SQL on the fly?

easiest way to do this is make a command object like this

dim dbConnection as SqlClient.SqlCo nnect(connStrin g)
using cmd as SqlClient.SqlCo mmand("SELECT * FROM MYDATES WHERE ((theDate)
BETWEEN @Start AND @End)",dbConnec tion)

cmd.CommandType = StoredProcedure
' this writes out the sql query text if needed
debug.writeline (cmd.CommandTex t)
cmd.paramaters. addwithvalue("@ Start",#8/1/2006#)
cmd.paramaters. addwithvalue("@ End",#9/1/2006#)

using dt as new datatable("MyIt ems")
dt.Load(cmd.exe cutereader)
for each dr as datarow in dt.rows
debug.writeline (dr("columnname ").tostring )
next
end using
end using

this will take the dates in as paramaters, execute them on the given
connection and load the results into a data table, then print them out one
at a time for the given column

[Robert Porter]

Re: Generating SQL on the fly?

[Ross Contino]

Re: Generating SQL on the fly?

I tried this - but this is how you store a specific query into the program. In this way the end user cannot change the query on the fly - or am I not understanding.

Thanks,
Ross
"Robert Porter" <robertporter@n ospam.rp2c.comw rote in message news:eIJiXrp1GH A.4796@TK2MSFTN GP06.phx.gbl...
A better solution would be to parameterize the query that builds the DataSet, you can either right-click the existing Dataset and then select Add Query or select Configure to modify the existing query.



--

Cheers,



Robert Porter

[Ross Contino]

Re: Generating SQL on the fly?

Hey Smokey:

Thanks for the reply, but when I tried this SqlClient has not SqlConnect
parameter. I tried SqlConnection but this will not take a connection string
parameter. Sorry to be such a newbie.


"Smokey Grindel" <nospam@nospam. comwrote in message
news:%23$svqfp1 GHA.4484@TK2MSF TNGP02.phx.gbl. ..

easiest way to do this is make a command object like this
>
dim dbConnection as SqlClient.SqlCo nnect(connStrin g)
using cmd as SqlClient.SqlCo mmand("SELECT * FROM MYDATES WHERE ((theDate)
BETWEEN @Start AND @End)",dbConnec tion)
>
cmd.CommandType = StoredProcedure
' this writes out the sql query text if needed
debug.writeline (cmd.CommandTex t)
cmd.paramaters. addwithvalue("@ Start",#8/1/2006#)
cmd.paramaters. addwithvalue("@ End",#9/1/2006#)
>
using dt as new datatable("MyIt ems")
dt.Load(cmd.exe cutereader)
for each dr as datarow in dt.rows
debug.writeline (dr("columnname ").tostring )
next
end using
end using
>
this will take the dates in as paramaters, execute them on the given
connection and load the results into a data table, then print them out one
at a time for the given column
>

[Robert Porter]

Re: Generating SQL on the fly?

[phonl]

Re: Generating SQL on the fly?

Try using ".FilterExpress ion" and see if it will work for you.

ado2.net is much more complicated than the ado we were using. Some vb6
programmers think it is hard to learn the vb.net language. I have found
that it is easy compared to ado2.net. Why they even call it ado any longer
is strange. ado2.net is for web programmers who use disconnected data.




"Ross Contino" <rosscontino@co mcast.netwrote in message
news:fYSdnfwd68 anc5vYnZ2dnUVZ_ u-dnZ2d@suscom.co m...

Howdy:
>
I am an experienced VB 6.0 database programmer trying to write first VS
2005
application in VB 2005. I want to create an end user defined query and
pass
it to the program. Specifically, I want to pass a date range, ie:
>
SELECT * FROM MYDATES WHERE ((theDate) Between #8/1/2006# And #8/7/2006#);
>
However, the user wants to enter the date range on his own. In the past I
would generate the SQL on the fly and pass it to a datacontrol as the
datasource. Now it seems Queries are made with the Query Builder and
stored
in "WhateverDataSe t.xsd" file.
>
How do I retrieve the current "Text" that defineds the query at runtime,
change it, and refresh it to display a new date range?
>
Eventually, I want to pass this same range to a report. Is that possible?
>
Thanks,
Ross
>
>
>

[Ross Contino]

Re: Generating SQL on the fly?

Do you have to have MS Sql Server installed to use these commands? The
machine I am programming on is also set to develop PHP/MySQL and is running
Apache. Hence, I did not install MS Sql Server when installing VS 2005.
The following is not available to me while programming:

using cmd as SqlClient.SqlCo mmand("SELECT * FROM MYDATES WHERE ((theDate)
BETWEEN @Start AND @End)",dbConnec tion)

SqlCommand is an invalid property and when I search MSDN it is referenced to
SQL Server.

I think I am just a newbie - but cannot imagine that there is not a simple
equivalent to the way we used to just pass a SQL statement to a Data Control
and refresh!! I was always able to create end user generated queries on the
fly. (Enough wining)

Thanks,
Ross



"phonl" <phonl@newsgrou ps.nospamwrote in message
news:eF4CrS21GH A.4264@TK2MSFTN GP05.phx.gbl...

Try using ".FilterExpress ion" and see if it will work for you.
>
ado2.net is much more complicated than the ado we were using. Some vb6
programmers think it is hard to learn the vb.net language. I have found
that it is easy compared to ado2.net. Why they even call it ado any
longer is strange. ado2.net is for web programmers who use disconnected
data.
>
>
>
>
"Ross Contino" <rosscontino@co mcast.netwrote in message
news:fYSdnfwd68 anc5vYnZ2dnUVZ_ u-dnZ2d@suscom.co m...

>Howdy:
>>
>I am an experienced VB 6.0 database programmer trying to write first VS
>2005
>application in VB 2005. I want to create an end user defined query and
>pass
>it to the program. Specifically, I want to pass a date range, ie:
>>
>SELECT * FROM MYDATES WHERE ((theDate) Between #8/1/2006# And
>#8/7/2006#);
>>
>However, the user wants to enter the date range on his own. In the past
>I
>would generate the SQL on the fly and pass it to a datacontrol as the
>datasource. Now it seems Queries are made with the Query Builder and
>stored
>in "WhateverDataSe t.xsd" file.
>>
>How do I retrieve the current "Text" that defineds the query at runtime,
>change it, and refresh it to display a new date range?
>>
>Eventually, I want to pass this same range to a report. Is that
>possible?
>>
>Thanks,
>Ross
>>
>>
>>

>
>