Hi Everybody,

I have a complicated question that I need to find an answer to. Please help! I have seen this done before, but can't figure out how.
Here is the setup:
I have a plain text file(called QueryString.txt) with SQL queries; looks something like this:

1
AUTO ALLOWANCE
select FormattedNm as 'Employee Name', EmplSocSecNo as SSN, EmplmtStatCd as Status, TitleCd as Title, SubTitleCd as Sttl, FROM EmployeeTable
2
HAVE ALLOWANCE AND ELIGIBLE
where ( PayParm1 like 'AUTO%' or PayParm2 like 'AUTO%' or PayParm3 like 'AUTO%' or PayParm4 like 'AUTO%' )
AUTO ALLOWANCE/NOT ELIGIBLE
where ( PayParm1 like 'AUTO%' or PayParm2 like 'AUTO%' or PayParm3 like 'AUTO%' or PayParm4 like 'AUTO%' )
ALL EMPLOYEES WITH AUTO ALLOWANCE
where ( PayParm1 like 'AUTO%' or PayParm2 like 'AUTO%' or PayParm3 like 'AUTO%' or PayParm4 like 'AUTO%' )
3
Name Only
ORDER BY Name
Agency/Alpha
ORDER BY Agency, FormattedNm

As you are aware, these are selection criterias(for a report). Each of these(1...2...3) represent a single drop-down list(with a number of selections in each). For instance, I want a report of all employees who "AUTO ALLOWANCE/NOT ELIGIBLE," sorted by Name.

Now here is my question. How can I use VBScript and ASP to read only the line/lines that I want(from what users selected), and then use that particular string/strings as a query string to query a database?
In other words, I have 3 drop-down menus; in the first drop-down, the user select "AUTO ALLOWANCE." In the second menu, he selected "AUTO ALLOWANCE/NOT ELIGIBLE." In third menu, he selected "Name Only." How can I grab these information from the text file(QueryString.txt) and use them as a query string for SQL 2000?

Thank you.

Leon

I assume you are storing your queries in a text file because of some unique configuration for each install or maybe you have another reason. However, I am going to suggest that your use an ini file. I think it will be much easier to manage and I am going to provide you some code for a class module that you can use to read/write/etc. to the ini file. I like it because it is much more explicit in the code not to mention concise. Some other advantages are you can have default values, the ini file can be edited with notepad, and it is less cryptic. Here is the code for the class module. If you have any question about it let me know.

Hope this helps.


' Class name clsIniFile.cls

Option Explicit

'=================================================
' Note: Use as example for ini path
'
' Used for the ini file
' Private Const gIniFilePath As String = "C:\Program Files\Common\Ini\App.ini"
'
'=================================================

' Declare Win 32 API Functions
Private Declare Function WritePrivateProfileString Lib "kernel32" _
Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As String, ByVal lpString As String, ByVal lpFileName As String) As Long

Private Declare Function GetPrivateProfileString Lib "kernel32" _
Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, _
ByVal nSize As Long, ByVal lpFileName As String) As Long

Public Function WriteToIniFile(ByVal strFileName As String, _
ByVal strSection As String, ByVal strKey As String, ByVal strValue As String) As Boolean

    ' Write a section, key, and value to an .ini file.
    ' Pass in name of section, key, key value, and file name.
    If WritePrivateProfileString(strSection, strKey, _
            strValue, strFileName) Then
        WriteToIniFile = True
    Else
        MsgBox "Error writing to .ini file: " & Err.LastDllError
        WriteToIniFile = False
    End If

End Function

Public Function DeleteIniSection(strSection As String, strFileName As String) As Boolean

    ' Delete a section and all of its keys from an .ini file.
    If WritePrivateProfileString(strSection, vbNullString, _
            vbNullString, strFileName) Then
        DeleteIniSection = True
    Else
        MsgBox "Error deleting section from .ini file: " _
            & Err.LastDllError
        DeleteIniSection = False
    End If

End Function

Public Function DeleteIniKey(strSection As String, strKey As String, strFileName As String) As Boolean

    ' Delete a key and its value from an .ini file.
    If WritePrivateProfileString(strSection, strKey, _
            vbNullString, strFileName) Then
        DeleteIniKey = True
    Else
        MsgBox "Error deleting section from .ini file: " _
            & Err.LastDllError
        DeleteIniKey = False
    End If

End Function

Function ReadFromIniFile(ByVal strFileName As String, ByVal strSection As String, _
ByVal strKey As String, Optional ByVal strDefault As String = "") As String

    ' Read a value from an .ini file, given the file
    ' name, section, key, and default value to return
    ' if key is not found.
    Dim strValue As String

    ' Fill string buffer with null characters.
    strValue = String$(255, vbNullChar)
    ' Attempt to read value. GetPrivateProfileString
    ' function returns number of characters written
    ' into string.
    If GetPrivateProfileString(strSection, strKey, _
            strDefault, strValue, Len(strValue), _
            strFileName) > 0 Then
        ' If characters have been written into string, parse string and return.
        strValue = Left(strValue, InStr(strValue, vbNullChar) - 1)
        ReadFromIniFile = strValue
    Else
        ' Otherwise, return a zero-length string.
        ReadFromIniFile = strDefault
    End If

End Function



Larry Asher

Hi Larry,
Thanks for the VERY thorough explanations and example. You are greatly appreciated! The only question i have, which I have forgot to mention in my initial posting is, how can I apply this on the web? I am trying to create a web application that does this. Where do I store the Query strings? Is the INI file by itself, or it should be coded in the application? Thanks again!

Leon

Well I have not done much on the web. But where you place the ini file depends on where the code is being processed on the client side or the server side. If the code is being processed on the client side I don't think a text file or an ini file is the right solutions. In that case you would need to look at using cookies or the like. If the code is being executed on the server you can simply supply a path to where the ini file is located.

This brings me to a question I had earlier but didn't ask. If this is a web based application why are you storing the queries outside of the application?

I have very rarely stored queries outside of the application itself. In some cases I store queries and/or query parameters in the database which I use to recall a query used to create a specific report. For instance this will allow a user to create a report, such as, First Shift Production and then simply recall it without having to specify the params again. In most other cases I have been able to build queries dynamically within the application, such as a search. But then again with SQL Server all of my queries have been moved to the server as stored procedures.

Are your queries dynamic or static?

Are you storing them in a text file because of individual configurations?

Are you building the queries dynamically and then sharing them between instances of your application? If so look at using the querydef object to create\update the query in the database.

Again, if you have any further questions about my replies and especially on using the ini file class let me know.


Larry Asher

THanks again Larry! :-)

The reason I have queries in .txt file is that I have a lot of different categories of reports(for example: books, kid toys, cloths, etc). Therefore, to make life easier, I put the Report Titles, Queries strings, Sorts, etc. in one file(individual files by categories), so I can edit them easily if I have to. Not just that, since I have drop-downs for users to choose from(in my application), I can easily import the report titles fr. the text files into the drop-downs.

In other words, this is what I have in my kid toys text file:

1
Kid Toys
select FormattedNm as 'Name', ToyID as ID, FROM KidTOys....

2
Boy toys
where ( toys like 'car%' or toys2 like 'airplane%').....
Girl toys
where toys like 'barbie%' or toys2 like 'princess%').....
Both Boys and Girls Toys
where (toys like 'car%' or toys2 like 'barbie%').....

3
ORDER BY FormattedNm

So on the form in my application, I will have 3 drop downs(category(in this case, 'KID TOYS')), second drop-down with two option: boy toys and girl toys, and 3rd drop down is sort sequece).

If the user select any combination of those, it will pick up the query strings in that same text file(please look at above). So as the coder, if i want to change one option in one of the drop-down, I can easily go into this text file, and change the wordings and queries at the same time, same place(without having to go into the application to change the words of the options, and then go into the database to change the queries strings). Does this make any sense at all?

Again, i appreciate you taking your time to help me out! I'll definitely try out your .ini method.

Leon

I may Have another solution.

When you are configuring your Comboboxes you are reading from the textfile.

Why don't you store the queries in an Array when you initialise your comboboxes

eg

Dim strQuery() as string

Dim i as integer

i = 0

redim preserve strQuery(i)
strQuery(i) = 'Querystring
combox.addItem 'titel

i = 1

redim preserve strQuery(i)
...

then when you create your query you can get the correct querystring from the array using the listindex of the combobox.

In Asp you might want to add invisible values to you ComboBox with the id's that point to the combobox.

This way you only need to open the textfile once.

This is just a different approuch

Greetz
Tom