Select (ms Access) Statement In Vbs

[Guest almahmeed]

Dears,

 

I have not yet given up, but thought it would be faster if I consult you. I am receiving an error with the following details:

 

Error: No Value given for one or more required parameters

Code: 80040E10

Source: Microsoft JET Database Engine

 

The VBScript is not able to evalue the experssion with the SELECT statement:

 

rs.Open "SELECT FileNo, mobileno FROM appfile WHERE FileNo = ModuleData", cn, adOpenStatic

 

I have placed a msgbox() before and after the statement to make sure that the connection to the database was established. The Variable ModuleData contacins the value that was returned from a previous Database Quert Module and the msgbox() function displays the contents.

 

I have tried placing the $RV[GetFileNumber] with no luck, and tried placing it as '$RV[GetFileNumber]' but it returned an empty string.

 

Any comments or suggestions? I am attaching the script to this message:

 

'------------------------------------------

Const adOpenForwardOnly = 0

Const adOpenKeyset = 1

Const adOpenDynamic = 2

Const adOpenStatic = 3

'------------------------------------

'Const vbGeneralDate = 0

'Const vbLongDate = 1

'Const vbShortDate = 2

'Const vbLongTime = 3

'Const vbShortTime = 4

'------------------------------------

 

DIM PrevModuleName

DIM ModuleData

DIM FNo, MNo, HNo

 

PrevModuleName = "$RV_PreviousModule"

 

set vg = CreateObject("VoiceGuide.CommandLink")

set cn = CreateObject("ADODB.Connection")

set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=$RV[scriptPath]\appdb.mdb"

 

if cn.State = 1 then

vg.Admin_TraceLogAdd $RV_LineID, 5, "vbs connection to APPDB.MDB made ok"

set rs.ActiveConnection = cn

'msgbox("Successfull Connection to the Database was established")

SELECT Case PrevModuleName

Case "QryFileNo"

ModuleData = "$RV[File Number]"

msgbox(ModuleData & " " &PrevModuleName)

rs.Open "SELECT FileNo, mobileno FROM appfile WHERE FileNo = ModuleData", cn, adOpenStatic

msgbox("[" & MobileNo & "]")

Case "QryMobileNo"

msgbox(ModuleData & PrevModuleName)

Case "QryHomeNo"

msgbox(ModuleData & PrevModuleName)

End SELECT

else

msgbox("I am sorry, but I was not able to connect to your database")

End if

 

cn.Close

 

Set rs = nothing

Set cn = nothing

Set vg = nothing

'------------------------------------------

[SupportTeam]

Instead of

 

"SELECT FileNo, mobileno FROM appfile WHERE FileNo = ModuleData"

 

use:

 

"SELECT FileNo, mobileno FROM appfile WHERE FileNo = " & ModuleData

 

ie:

 

rs.Open "SELECT FileNo, mobileno FROM appfile WHERE FileNo = " & ModuleData, cn, adOpenStatic

 

ModuleData is the variable containing your data...

 

If you still have problems please post your entire script.

[Guest almahmeed]

Hi There,

 

I tried the solution, which made the case better, but I am still not getting the result I am looking for.

 

The ModuleData contains that result variable of a "Get Numbers" Module that is an integer variant. I have converted it using the cSTR function, but then received an error that it is not quoted using (") <= CHR(34) or (') <= CHR(39) to when I have added those using ModuleData = CHR(34) & CSTR($RV[FILE NUMBER]) & CHR(34)

 

Now, the script is executing, but the msgbox is returing an empty result that tells me that the condition in the SELECT statement is not becoming TRUE.

 

I am attaching the VoiceGuide Script for your review and comments. (I have attached it to my previous post, but the forum didn't show it )

 

PS. The MS Access DB details is as follows:

 

DB Name: APPDB

Table Name: APPFILE

Table Structure:

 

FileNo : TEXT, Size 15

MobileNo: TEXT, Size 15

HomeNo: TEXT, Size 15

[SupportTeam]

The script still did not get attached.

 

Please email the script to support@voiceguide.com (refrenceing this post in the email) and we will look at it.

[SupportTeam]

In the script there is first query made using a DBQuery module and then another query is made inside the VBScript module as well.

 

The queries are the same - so the one made in the DBQuery module should be sufficient - do the DBQuery modules return the right data for you?

 

You may need to specify the column and table names exacly as well - Mobileno and MobileNo may not mean the same thing and maybe that's why you are getting errros.

 

Could you please post a copy of VoiceGuide's Debug Printout which captures the call, this will allow us to see what happens.

 

When running the script click on VoiceGuide's View menu and select 'Event Trace Log' option - any log information will then appear in this window. You must open the trace window before making the call. You can enable the automatic saving of the logs to files in \log\ directory as well.

 

Also, in DBQuery modules the database is specified as residing in:

 

C:\Documents and Settings\Aziz\Desktop\Jobs Phone\Appointment System\APPDB.mdb

 

Where is the APPDB.mdb actually located?

 

Please also post the APPDB.mdb file (or email it to us and we will post it).

The_Automated_Appointement_System_v2.1.vgs

[Guest almahmeed]

Hi,

 

I have made the following changes to avoid the need for the VBS with MS access which worked just fine with me.

 

Thank you for your help and efforts.

 

Regards

 

AA

The_Automated_Appointment_System_v3.0.vgs

[SupportTeam]

Glad to hear it's all resolved.

 

Looks like you have changed the fields to match the caps with the names in database.