473,890 Members | 5,876 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to make a query call parametric

Hi all,
I'm trying to build a function that - providing the dbname and the query
name - show the results.

I don't know how to solve this problem...

when I try to insert the variable into this call

cnnSimple.x_qry rstSimple

where x_qry is the variable I get the error "Type mismatch: 'x_qry'"
obviously because the x_qry is a string...

I can't find how to cast the value in order to get the function work
correctly.
My target is to have a function like that: ShowTable(dbnam e,queryname)

Thanks
PGei
Jul 22 '05 #1
3 1996
PiGei wrote:
Hi all,
I'm trying to build a function that - providing the dbname and the
query name - show the results.

I don't know how to solve this problem...

when I try to insert the variable into this call

cnnSimple.x_qry rstSimple

where x_qry is the variable I get the error "Type mismatch: 'x_qry'"
obviously because the x_qry is a string...

I can't find how to cast the value in order to get the function work
correctly.
My target is to have a function like that: ShowTable(dbnam e,queryname)


This should answer your immediate question:
http://groups-beta.google.com/group/...d322b882a604bd

This will provide a little more information:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #2
PiGei wrote:
Hi all,
I'm trying to build a function that - providing the dbname and the
query name - show the results.

I don't know how to solve this problem...

when I try to insert the variable into this call

cnnSimple.x_qry rstSimple

where x_qry is the variable I get the error "Type mismatch: 'x_qry'"
obviously because the x_qry is a string...

I can't find how to cast the value in order to get the function work
correctly.
My target is to have a function like that: ShowTable(dbnam e,queryname)

Oops, ignore the last message. I did not recognize that x_qry was a variable
containing the name of a saved query ...

In order to do what you want (specify the name of the query in the argument
to the function), you will need to either use dynamic sql, or a Command
object. My preference is the latter, due to security concerns.

Dynamic SQL approach:

const adCmdText = 1
sSQL = "Exec " & x_qry
Set rstSimple = cnnSimple.Execu te(sSQL,,adCmdT ext)

Hopefully, if you are using this approach, you will validate that x_qry
contains a valid query name before executing it. This will mitigate the
dangers of sql injection and cross-site scripting, two techniques that
hackers can use to gain access to your system. You can use ADOX to get the
names of your views (non-parameterized saved queries) and procedures
(parameterized saved queries). You can store them in an array or xml
document (recommended) in Application (using appliction_onst art in
global.asa) so you don't have to query the database every time you want to
use this function to execute a saved query.
Command object approach:

const adCmdStoredProc = 4
Set cmd = createobject("a dodb.command")
cmd.CommandText =x_qry
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConne ction = cnnSimple
Set rstSimple = cmd.Execute

Advantage: no chance of sql injection using this approach. No need to do
extra processing to validate x_qry. Just catch the error that occurs if a
hacker attempts to pass a sql statement to this function.
More about the dynamic SQL approach:
http://www.aspfaq.com/show.asp?id=2201

And the reasons I dislike that approach:
http://tinyurl.com/jyy0

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #3
Thanks again for your help Bob
PGei

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:O3******** ******@TK2MSFTN GP10.phx.gbl...
PiGei wrote:
Hi all,
I'm trying to build a function that - providing the dbname and the
query name - show the results.

I don't know how to solve this problem...

when I try to insert the variable into this call

cnnSimple.x_qry rstSimple

where x_qry is the variable I get the error "Type mismatch: 'x_qry'"
obviously because the x_qry is a string...

I can't find how to cast the value in order to get the function work
correctly.
My target is to have a function like that: ShowTable(dbnam e,queryname)

Oops, ignore the last message. I did not recognize that x_qry was a
variable containing the name of a saved query ...

In order to do what you want (specify the name of the query in the
argument to the function), you will need to either use dynamic sql, or a
Command object. My preference is the latter, due to security concerns.

Dynamic SQL approach:

const adCmdText = 1
sSQL = "Exec " & x_qry
Set rstSimple = cnnSimple.Execu te(sSQL,,adCmdT ext)

Hopefully, if you are using this approach, you will validate that x_qry
contains a valid query name before executing it. This will mitigate the
dangers of sql injection and cross-site scripting, two techniques that
hackers can use to gain access to your system. You can use ADOX to get the
names of your views (non-parameterized saved queries) and procedures
(parameterized saved queries). You can store them in an array or xml
document (recommended) in Application (using appliction_onst art in
global.asa) so you don't have to query the database every time you want to
use this function to execute a saved query.
Command object approach:

const adCmdStoredProc = 4
Set cmd = createobject("a dodb.command")
cmd.CommandText =x_qry
cmd.CommandType = adCmdStoredProc
Set cmd.ActiveConne ction = cnnSimple
Set rstSimple = cmd.Execute

Advantage: no chance of sql injection using this approach. No need to do
extra processing to validate x_qry. Just catch the error that occurs if a
hacker attempts to pass a sql statement to this function.
More about the dynamic SQL approach:
http://www.aspfaq.com/show.asp?id=2201

And the reasons I dislike that approach:
http://tinyurl.com/jyy0

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jul 22 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
5095
by: rednexgfx_k | last post by:
All, Problem Summary: I've running about 30 make table queries via VBA in Access 2000, and my database goes from 14,000k to over 2,000,000k. In addition, the longer the procedure runs, the bigger the performance hit VBA takes. I'm wondering how to prevent or reduce this. Details: I have a database table of queries I want to run. This table contains the query name, the SQL text of the query, the name of the target table, and whether...
4
4345
by: Gilberto Campos | last post by:
Hi all. I am having a strange problem. I am developping an application that acceses an Access db through Jet (.UDL files). I have writen parametric INSERT queries that work fine. I am now trying to write a parametric UPDATE query but I always get a return error code that the language manual translates to:
4
2137
by: Chris F Clark | last post by:
Please excuse the length of this post, I am unfortunately long-winded, and don't know how to make my postings more brief. I have a C++ class library (and application generator, called Yacc++(r) and the Language Objects Library) that I have converted over to C#. It works okay. However, in the C# version, one has to build the class library into the generated application, because I haven't structured this one thing right. I would like to...
13
2879
by: forbes | last post by:
Hi, I have a user that used the Query Wizard to create a query in Access. Now she claims that her master table is missing all the data that was excluded from the query. Can you create anything other than a select query using the Wizard? What do you think happened to her data? I am working remotely until Friday, so I can't get down to her office and check out what she did.
6
5952
by: Ian Boyd | last post by:
Every time during development we had to make table changes, we use Control Center. Most of the time, Control Center fails. If you try to "undo all", it doesn't, and you end up losing your identity seed, or your constraints, or your triggers, or your table. Talking to developers at other companies who have had the misfortune of using DB2, they are adamant that you cannot use the tools; they are buggy and you just have to resign yourself to...
0
1353
by: sorin.lerner | last post by:
********************************************************************* * ACM SIGPLAN-SIGACT Symposium * * on * * Principles of Programming Languages * * * * January 17-19, 2007 * * ...
0
1043
by: jml1988 | last post by:
I am trying to open a parametric query in VB 6 from a Access 2000 file. I am trying a ADODC file using the SQL but am having problems, if anyone could help me it would be appricated. JML
0
1267
by: Dexter | last post by:
This new applet in MathEasy series allows computing length of an arc represented by parametric curve x(t) , y(t). It shows the resulting graph and the numerical answer for curve length Visit http://www.britishcomputercolleges.com/vu/ArcLengthParam.html Asad "The term Majesty suits America and these Islam lands"
0
1409
by: tkip | last post by:
Gents.. I am just looking for ideas as to how to approach the query of DB I am working on. Basically, it's a database of welding procedures and depending on the metal you choose, appropriate procedure(s) will show up as a link. I am using parametric query and the search itself is working just fine. I just didn't think about one of the obvious problems. Overview:
0
9823
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10924
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9637
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7170
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5854
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6049
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4681
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4275
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3281
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.