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 "WhateverDataSet.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]

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

dim dbConnection as SqlClient.SqlConnect(connString)
using cmd as SqlClient.SqlCommand("SELECT * FROM MYDATES WHERE ((theDate)
BETWEEN @Start AND @End)",dbConnection)

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

using dt as new datatable("MyItems")
dt.Load(cmd.executereader)
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

 

[Ross Contino]

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
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]

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.

 

[phonl]

Try using ".FilterExpression" 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]

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.SqlCommand("SELECT * FROM MYDATES WHERE ((theDate)
BETWEEN @Start AND @End)",dbConnection)

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