First Post, First Question, Dynamic Queries

[Tigermilk]

Hi all,

I've been poking around for a while but can't seem to find what I'm looking for.

I'm in the planning phase of a new application where I will fetch data from an Access Database and do some calculation.

The tricky part is that the users might want to exclude a dynamic amount of entries from one day to another.

Here's a simplified example:

Cust_ID   Cust_Name  Package_No     Batch_No
1             Alice          1245            
2             John          B144            14
3             Eric           B145            13
1             Alice          1246            
1             Alice          1247            13
2             John           B146           
.                .
.                .

(The Batch_No field can be empty)

Now, Let's say that day 1 I want to exclude rows where Batch_No = 14 and Package_No = 1247 and 1245.

On day 2 I want to exclude rows where Batch_No = 15 and Package_No = B146 and 1247 and where Cust_ID = 1

Does anyone know where i might find examples, tutorials or just point me in the right direction on how to create the dynamic queries that make it possible to exclude the rows?

[disruptivehair]

Hi all,

I've been poking around for a while but can't seem to find what I'm looking for.

I'm in the planning phase of a new application where I will fetch data from an Access Database and do some calculation.

The tricky part is that the users might want to exclude a dynamic amount of entries from one day to another.

Here's a simplified example:

Cust_ID   Cust_Name  Package_No     Batch_No
1             Alice          1245            
2             John          B144            14
3             Eric           B145            13
1             Alice          1246            
1             Alice          1247            13
2             John           B146           
.                .
.                .

(The Batch_No field can be empty)

Now, Let's say that day 1 I want to exclude rows where Batch_No = 14 and Package_No = 1247 and 1245.

On day 2 I want to exclude rows where Batch_No = 15 and Package_No = B146 and 1247 and where Cust_ID = 1

Does anyone know where i might find examples, tutorials or just point me in the right direction on how to create the dynamic queries that make it possible to exclude the rows?

My application uses dynamic SQL. What I do is I build the parameter...in this case, the entire WHERE or WHERE/AND clause...that is passed to a stored procedure where it is concatenated to a base SQL query that pulls out the data I want. In Access you can do this by having it write out all the SQL you need, then modifying the querydef...or by using it as the source for a recordset.

Does that make any sense?

[Tigermilk]

My application uses dynamic SQL. What I do is I build the parameter...in this case, the entire WHERE or WHERE/AND clause...that is passed to a stored procedure where it is concatenated to a base SQL query that pulls out the data I want. In Access you can do this by having it write out all the SQL you need, then modifying the querydef...or by using it as the source for a recordset.

Does that make any sense?

To be honest...
Nope ;o)

I'm not sure if I follow you...
I'm looking for a way to exclude user defined rows but I can't see how I can do that by creating the queries in advance.

[wadep]

Here's some VB6 coding that might help:

'declare the database connection and the recordset
Dim databaseConnection01 As ADODB.Connection, stockRecords01 As ADODB.Recordset
Set databaseConnection01 = New ADODB.Connection
Set stockRecords01 = New ADODB.Recordset
 
'open the database connection change your datasource ID and password
databaseConnection01.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=c:\yourfolder\yourdatabase.mdb;" & _
        "User Id=admin;" & _
        "Password="
 
'set your SQL search query string
searchString = "SELECT * FROM inventory WHERE status='STOCK' AND stock='" & searchString1 & "'" & " OR stock='" & searchString2 & "'" & " OR stock='" & searchString3 & "'" & " OR stock='" & searchString4 & "'" & " OR stock='" & searchString5 & "'" & " OR stock='" & searchString6 & "'" & " OR stock='" & searchString7 & "'" & " OR stock = '" & searchString8 & "'"
 
'execute the searchstring with the database connection and set it to your recordset you created
Set stockRecords01 = databaseConnection01.Execute(searchString)

For help with SQL statements I found this website to be the most helpful:
http://www.w3schools.com/sql/sql_select.asp

you'll want to use the select & where with the <> not equal to parameters.

hope this helps a bit. if you're using vb2005 or .net I'm not sure how much of this will really assist you.

[Tigermilk]

I'm using VB 2005, but I think I can manage to tweak the database connection to make it fit my situation.

How does the query handle empty search strings?
I mean, perhaps only the first three strings contains values and the rest have a value of NULL(?) won't there be an error or will SQL just let the empty strings pass?

[disruptivehair]

I'm using VB 2005, but I think I can manage to tweak the database connection to make it fit my situation.

How does the query handle empty search strings?
I mean, perhaps only the first three strings contains values and the rest have a value of NULL(?) won't there be an error or will SQL just let the empty strings pass?

Depending on how the string is structured it could cause problems, yes.

Tomorrow I'll post how I deal with these, both client and server-side. I can't access my code right now; I'm at home.