|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Generating SQL on the fly?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 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 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. Show quoteHide quote "Smokey Grindel" <nospam@nospam.com> wrote in message news:%23$svqfp1GHA.4484@TK2MSFTNGP02.phx.gbl... > 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 > 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@nospam.rp2c.com> wrote in message news:eIJiXrp1GHA.4796@TK2MSFTNGP06.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 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. Show quoteHide quote "Ross Contino" <rosscont***@comcast.net> wrote in message news:fYSdnfwd68anc5vYnZ2dnUVZ_u-dnZ2d@suscom.com... > 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 > > > 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 Show quoteHide quote "phonl" <phonl@newsgroups.nospam> wrote in message news:eF4CrS21GHA.4264@TK2MSFTNGP05.phx.gbl... > 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" <rosscont***@comcast.net> wrote in message > news:fYSdnfwd68anc5vYnZ2dnUVZ_u-dnZ2d@suscom.com... >> 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 >> >> >> > > 
Other interesting topics
(newbie) - reading data from a file - in VB 2005 EE
Access the type from a shared method udpclient receive port# ? GUI for Charts Web login problem HTML Data generated by Server.Controls Greyed out controls ComboBox Question? Embedding a device in an application? dAdapter.FillSchema vs dAdapter.Fill with dataset.clear? |
|||||||||||||||||||||||
