How can I build a dynamic function...

[ByNary010101]

I am absolutely stumped and am not even sure how to word this question but here goes:

I am building an application that needs to be VERY dynamic. How can I make a Function receive a dynamic table name as well as dynamic field names to be used in Select, Insert, and Update statements??

Some background:
My application needs to let end users create different installation types (Desktop, Laptop, Printer, Scanner, etc) and enter the different criteria that is required to be filled in by other end users:

(As an example)
Desktop and Laptop would need something along the lines of:
ECN (Equipment Control Number)
MultiUser Password
UserId
Monitor Type
Keyboard
Mouse

Printer would need:
ECN
ECN of Attached PC
IP Address
Unix Queue

Scanner would need:
ECN
ECN of Attached PC

If down the road they need to create an Intall type for PDA's the user would need to define criteria such as:
ECN
ECN of Users PC
Bluetooth
Cradle

I want to be able to use 1 Select function, 1 Update function, and 1 Insert function to handle all situations; I need them to be able to take in a table name as well as all of the defined fields to be used in the DML statements...can anyone help??

 

[MichaelRed]

"I want to be able to use 1 Select function, 1 Update function, and 1 Insert function to handle all situations"

That last curve was a real zinger! I sort-of followed thill we got there ... lost in a fog of disbelief!





MichaelRed

 

[BobRodes]

I understand Michael's reaction! I would NOT use one select function. I'd use one for each type of machine that you're working with. After all, if you use one function, you have to write a bunch of code to determine which machine you're working with, and that code has to execute every time, creating unnecessary overhead. So, you are having "cohesion problems" at the conceptual level.

In general, when you're conceptualizing how you are going to accomplish a task in code, try to come up with ways to minimize decision making constructs, because they represent overhead without accomplishing real work. If a given unit of code tries to do too many things, it gets bogged down in trying to determine which of those things it's supposed to be doing at any given point in time.

That said, I'm getting that what you're trying to do is dynamically change SQL statements to plug in data specified at runtime. Let's use as a sample an Update function for the scanner, since it has the fewest fields. This code assumes that what you are wanting to update is the ECN of the attached PC, that the ECN functions as the primary key of the table, and that you don't allow the ECN to be changed (I never allow primary key values to be changed as a matter of practice; if I need to do it, I delete the original and reinsert it with a new key):

Function UpdateScanner(cECN as String, cPC_ECN as String) As Boolean
dim mySQLString as String
(code to set up connection, etc.)
mySQLString = "UPDATE Scanner_to_PC SET PC_ECN = " & cPC_ECN & " WHERE ECN = " & cECN
(code to execute above string as sql command)
(code to return true if successful, false if not)
End Function

Basically, all you're doing in all cases is passing the variable data as arguments to your function (or sub, if you don't want to return a value) and plugging the passed values into a SQL string, which you then execute. A more robust way to do this is to create stored procedures that take arguments. You call the stored procedure in your function, and pass as arguments the arguments that are passed to your function. I would start the first way, and once you have solidified your understanding of what you want to do, move the code into stored procedures.

HTH

Bob