Database Problem

[Guest Guest_zackrspv]

Hello. I will attach my VG script to this as well as the d/b so that you can better serve my question. We are trying to do the following:

 

Customer Calls In.

Hears Main Menu.

Selects Doctor Section (8)

Doctor menu plays

Caller selects (1) new patient or (2) returning patient

Caller Selects (1)

New Registration plays

Customer is asked to create PIN Number

Customer is asked what they are calling about

 

From here the vgs script will run a database query to resolve what the customer is calling about (if they chose item 1) then it will pull up 'homeopathic' from the database and store that in a RV variable.

 

After this, another database command is used to insert the customer into the dtabase; it inserts the customers phone number, time/date of call, subject they called about, and their pin number, as well as their name.

 

From here it will give the caller a chance to record a message for the doctor

 

If caller does not wish to record message, it will proceed to email the doctor the callers details; ie, it will email the callers phone number, name, subject called about, date/time.

 

If the caller wishes to leave a message, a record message module is used to record their message; and then it is sent to the email where it will email the doctor the callers name, phone number, time/date of call, subject called about and finally the recorded message.

 

After this, the caller is told to wait for the call back from the doctor and the call is ended.

 

Here is the event trace log:

 

 

130242.73 10 linedevstate 2048 0 0

130242.73 10 callstate OFFERING 65896 0 4

130242.75 10 Lev_CallerID [5418998788,COPPER CAULDRON]

130242.76 10 callinfo CALLEDID

130242.76 10 callinfo ORIGIN

130242.76 10 ring 0

130242.76 10 Answer the call at 2/10/2004 1:02:42 PM

130242.78 10 lineAnswer(65896) => 65913

130243.31 10 callstate CONNECTED 65896,1,0

130243.31 10 WorkingModeTAPI@Connected=

130243.31 10 WorkingModeScript@Connected=

130243.32 10 Inband detection not enabled

130243.32 10 StartLoadedVgs at 2/10/2004 1:02:43 PM

130243.32 10 tapi Reply (LineEvReply) ok 65913 0

130243.32 10 [Main Menu] Playing

130243.32 10 [Main Menu] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\mainnew.wav)

130243.34 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\mainnew.wav]

130243.34 10 RunModule PLAY end

130245.37 10 dtmf 8 (65896,56,2)

130245.37 10 LsPlayMsg 8

130245.43 10 PlaySoundStop ok

130245.43 10 [DoctorMenu] Playing

130245.43 10 [DoctorMenu] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\doctormenu.wav)

130245.43 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\doctormenu.wav]

130245.45 10 RunModule PLAY end

130248.50 10 dtmf 1 (65896,49,2)

130248.50 10 LsPlayMsg 1

130248.56 10 PlaySoundStop ok

130248.56 10 [MakePin] Playing

130248.56 10 [MakePin] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\pininformation.wav)

130248.57 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\pininformation.wav]

130248.57 10 RunModule PLAY end

130307.21 10 wb(2443700)

130307.26 10 Play End line[10] (id=24437)

130307.26 10 LsPlayMsg EV_PLAY_FINISHED

130307.26 10 LsPlayMsgFinished EV_TIMEOUT_GOTOMODULE

130307.28 10 [Makepin2] Number Input

130307.28 10 [Makepin2] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\getpin.wav)

130307.29 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\getpin.wav]

130310.17 10 dtmf 1 (65896,49,2)

130310.17 10 LsGetNbrsPlayWelcMsg 1

130310.21 10 PlaySoundStop ok

130310.23 10 [Makepin2] Number Input 1

130310.23 10 path {1} not found

130310.48 10 dtmf 2 (65896,50,2)

130310.48 10 LsGetNbrsRxDigits 2

130310.48 10 [Makepin2] Number Input 12

130310.48 10 path {12} not found

130310.81 10 dtmf 3 (65896,51,2)

130310.82 10 LsGetNbrsRxDigits 3

130310.82 10 [Makepin2] Number Input 123

130310.82 10 path {123} not found

130311.17 10 dtmf 4 (65896,52,2)

130311.17 10 LsGetNbrsRxDigits 4

130311.17 10 [Makepin2] Number Input 1234

130311.17 10 path {1234} not found

130311.46 10 dtmf 5 (65896,53,2)

130311.46 10 LsGetNbrsRxDigits 5

130311.46 10 [Makepin2] Number Input 12345

130311.48 10 path {12345} not found

130312.25 10 dtmf # (65896,35,2)

130312.25 10 LsGetNbrsRxDigits #

130312.25 10 fn PlaySoundStartNumbers C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav, C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav, 12345, Digits

130312.25 10 sVbsFunctionToUse=Digits

130312.31 10 Started SayNumber VBScript OK. (C:\Program Files\VoiceGuide\data\vbs_10_SayNumber.vbs)

130312.39 10 cl Play_Start C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav,1.wav,2.wav,3.wav,4.wav,5.wav,C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav

130312.40 10 PlaySoundStart ok [C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav,C:\Program Files\VoiceGuide\system\voice\1.wav,C:\Program Files\VoiceGuide\system\voice\2.wav,C:\Program Files\VoiceGuide\system\voice\3.wav,C:\Program Files\VoiceGuide\system\voice\4.wav,C:\Program Files\VoiceGuide\system\voice\5.wav,C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav]

130318.18 10 dtmf 1 (65896,49,2)

130318.18 10 LsGetNbrsPlayConfirm 1

130318.25 10 PlaySoundStop ok

130318.25 10 path {12345} not found

130318.25 10 [Get Subject] Number Input

130318.25 10 tts generate start[Please selet the subject of this call. For Homeopathic Medicine, press 3, For Meditation press 1. ]

130318.31 10 tts generate end

130318.31 10 [Get Subject] Playing (C:\Program Files\VoiceGuide\data\tts10.wav)

130318.32 10 PlaySoundStart ok [C:\Program Files\VoiceGuide\data\tts10.wav]

130325.17 10 dtmf 3 (65896,51,2)

130325.17 10 LsGetNbrsPlayWelcMsg 3

130325.23 10 PlaySoundStop ok

130325.23 10 [Get Subject] Number Input 3

130325.23 10 path {3} not found

130325.23 10 LsGetNbrsRxDigits #

130325.23 10 path {3} not found

130325.23 10 [subjectResolve] DB Query

130325.25 10 ODBC mode used

130325.34 10 RVreplace start: [sELECT Subject FROM joblist WHERE Job='$RV[Get Subject]']

130325.34 10 RVreplace end: [sELECT Subject FROM joblist WHERE Job=3]

130325.34 10 About to execute Retrieve Operation [sELECT Subject FROM joblist WHERE Job=3]

130325.39 10 ERROR 5.1.7001 OpenRecordset: 3146:ODBC--call failed.

130325.39 10 [DoctorBarbara] DB Query

130325.39 10 ODBC mode used

130325.40 10 RVreplace start (strlen>100)

130325.40 10 About to execute Action Query

130325.40 10 ERROR 5.1.7001 DB Execute: 3146,ODBC--call failed.

130325.40 10 [faildv] Playing

130325.42 10 [faildv] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\dberr.wav)

130325.43 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\dberr.wav]

130325.43 10 RunModule PLAY end

130328.53 10 wb(6129600)

130328.59 10 Play End line[10] (id=61296)

130328.59 10 LsPlayMsg EV_PLAY_FINISHED

130328.59 10 Hanging up call... [No Paths leaving Play module [faildv]]

130328.59 10 RecSoundStop ok

130328.61 10 PlaySoundStop ok

130328.61 10 fnHangupCall end

130328.64 10 linedevstate 2048 0 0

130328.65 10 callstate IDLE 65896 0 0

130328.65 10 WorkingMode@Idle=

130328.65 10 tapi Reply (LineEvReply) ok 65998 0

130329.70 10 LsAwaitingCalls EV_TIMEOUT_TIMETOREINITLINE

130329.70 10 ReinitTelephony due to IDLE start

130329.70 10 tapic lineDeallocateCall(MainCall:65896) 0

130329.76 10 lineOpen(10) => 0

130329.76 10 Waiting for a call...

130329.76 10 lineOpen(10)LineHandle=66118

 

I first tried to use the database query and insert w/o the use of the ODBC by just specifiing the database name; however that didn't work.

 

I then searched this forum and found out how to do the ODBC side of it, and unfortunatly that did not work as well.

 

I may just have VG design the script for me lol but i really would like to learn how to do this.

 

Thanks!

vgs.zip

[Guest Guest_zackrspv]

This is what it looks like w/o the ODBC connect string and just specifying the database:

 

 

131506.86 10 linedevstate 2048 0 0

131506.86 10 callstate OFFERING 65777 0 4

131506.87 10 Lev_CallerID [5418998788,COPPER CAULDRON]

131506.89 10 callinfo CALLEDID

131506.89 10 callinfo ORIGIN

131506.89 10 ring 0

131506.89 10 Could not obtain handle to kill process. (new call) 4008, 0

131506.89 10 Answer the call at 2/10/2004 1:15:06 PM

131506.89 10 lineAnswer(65777) => 65811

131507.43 10 callstate CONNECTED 65777,1,0

131507.43 10 WorkingModeTAPI@Connected=

131507.43 10 WorkingModeScript@Connected=

131507.45 10 Inband detection not enabled

131507.45 10 StartLoadedVgs at 2/10/2004 1:15:07 PM

131507.45 10 tapi Reply (LineEvReply) ok 65811 0

131507.45 10 [Main Menu] Playing

131507.45 10 [Main Menu] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\mainnew.wav)

131507.46 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\mainnew.wav]

131507.46 10 RunModule PLAY end

131509.40 10 dtmf 8 (65777,56,2)

131509.40 10 LsPlayMsg 8

131509.46 10 PlaySoundStop ok

131509.46 10 [DoctorMenu] Playing

131509.46 10 [DoctorMenu] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\doctormenu.wav)

131509.46 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\doctormenu.wav]

131509.46 10 RunModule PLAY end

131510.81 10 dtmf 1 (65777,49,2)

131510.81 10 LsPlayMsg 1

131510.87 10 PlaySoundStop ok

131510.87 10 [MakePin] Playing

131510.87 10 [MakePin] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\pininformation.wav)

131510.87 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\pininformation.wav]

131510.87 10 RunModule PLAY end

131512.90 10 dtmf 1 (65777,49,2)

131512.90 10 LsPlayMsg 1

131512.90 10 path {1} not found

131513.14 10 dtmf 2 (65777,50,2)

131513.14 10 LsPlayMsg 2

131513.14 10 path {2} not found

131513.39 10 dtmf 3 (65777,51,2)

131513.39 10 LsPlayMsg 3

131513.39 10 path {3} not found

131529.50 10 wb(76675000)

131529.56 10 Play End line[10] (id=766750)

131529.56 10 LsPlayMsg EV_PLAY_FINISHED

131529.56 10 LsPlayMsgFinished EV_TIMEOUT_GOTOMODULE

131529.56 10 [Makepin2] Number Input

131529.56 10 [Makepin2] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\getpin.wav)

131529.56 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\getpin.wav]

131531.17 10 dtmf 1 (65777,49,2)

131531.17 10 LsGetNbrsPlayWelcMsg 1

131531.21 10 PlaySoundStop ok

131531.23 10 [Makepin2] Number Input 1

131531.23 10 path {1} not found

131531.34 10 dtmf 2 (65777,50,2)

131531.34 10 LsGetNbrsRxDigits 2

131531.36 10 [Makepin2] Number Input 12

131531.36 10 path {12} not found

131531.59 10 dtmf 3 (65777,51,2)

131531.61 10 LsGetNbrsRxDigits 3

131531.61 10 [Makepin2] Number Input 123

131531.61 10 path {123} not found

131532.67 10 dtmf # (65777,35,2)

131532.67 10 LsGetNbrsRxDigits #

131532.67 10 fn PlaySoundStartNumbers C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav, C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav, 123, Digits

131532.67 10 sVbsFunctionToUse=Digits

131532.73 10 Started SayNumber VBScript OK. (C:\Program Files\VoiceGuide\data\vbs_10_SayNumber.vbs)

131532.81 10 cl Play_Start C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav,1.wav,2.wav,3.wav,C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav

131532.81 10 PlaySoundStart ok [C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmIntro.wav,C:\Program Files\VoiceGuide\system\voice\1.wav,C:\Program Files\VoiceGuide\system\voice\2.wav,C:\Program Files\VoiceGuide\system\voice\3.wav,C:\Program Files\VoiceGuide\system\voice\GetNbrsConfirmMenu.wav]

131534.21 10 dtmf 1 (65777,49,2)

131534.21 10 LsGetNbrsPlayConfirm 1

131534.28 10 PlaySoundStop ok

131534.28 10 path {123} not found

131534.28 10 [Get Subject] Number Input

131534.28 10 tts generate start[Please selet the subject of this call. For Homeopathic Medicine, press 3, For Meditation press 1. ]

131534.34 10 tts generate end

131534.34 10 [Get Subject] Playing (C:\Program Files\VoiceGuide\data\tts10.wav)

131534.36 10 PlaySoundStart ok [C:\Program Files\VoiceGuide\data\tts10.wav]

131538.62 10 dtmf 1 (65777,49,2)

131538.62 10 LsGetNbrsPlayWelcMsg 1

131538.67 10 PlaySoundStop ok

131538.68 10 [Get Subject] Number Input 1

131538.68 10 path {1} not found

131538.68 10 LsGetNbrsRxDigits #

131538.68 10 path {1} not found

131538.68 10 [subjectResolve] DB Query

131538.70 10 Jet mode used (To use ODBC mode specify ODBC Connect string)

131538.70 10 RVreplace start: [sELECT Subject FROM joblist WHERE Job='$RV[Get Subject]']

131538.71 10 RVreplace end: [sELECT Subject FROM joblist WHERE Job=1]

131538.71 10 About to execute Retrieve Operation [sELECT Subject FROM joblist WHERE Job=1]

131538.71 10 ERROR 5.1.7001 OpenRecordset: 3061:Too few parameters. Expected 1.

131538.71 10 [DoctorBarbara] DB Query

131538.71 10 Jet mode used (To use ODBC mode specify ODBC Connect string)

131538.73 10 RVreplace start (strlen>100)

131538.73 10 About to execute Action Query

131538.73 10 ERROR 5.1.7001 DB Execute: 3464,Data type mismatch in criteria expression.

131538.73 10 [faildv] Playing

131538.75 10 [faildv] Playing (C:\Documents and Settings\Administrator\My Documents\vgs files\new\dberr.wav)

131538.75 10 PlaySoundStart ok [C:\Documents and Settings\Administrator\My Documents\vgs files\new\dberr.wav]

131538.75 10 RunModule PLAY end

131541.89 10 Play End line[10] (id=794625)

131541.89 10 LsPlayMsg EV_PLAY_FINISHED

131541.89 10 Hanging up call... [No Paths leaving Play module [faildv]]

131541.89 10 RecSoundStop ok

131541.89 10 PlaySoundStop ok

131541.90 10 fnHangupCall end

131541.93 10 linedevstate 2048 0 0

131541.93 10 callstate IDLE 65777 0 0

131541.95 10 WorkingMode@Idle=

131541.95 10 tapi Reply (LineEvReply) ok 65879 0

131542.00 10 LsAwaitingCalls EV_TIMEOUT_TIMETOREINITLINE

131542.00 10 ReinitTelephony due to IDLE start

131542.00 10 tapic lineDeallocateCall(MainCall:65777) 0

131543.06 10 lineOpen(10) => 0

131543.06 10 Waiting for a call...

131543.07 10 lineOpen(10)LineHandle=65760

[SupportTeam]

You are using MS Access database - so all that you need to do is to specify the database filename as the database...

 

Press the "Select" button to the right of the text box where the database filename is to be specified - this opens up a "Select Database File" dialog box...

 

then you should maybe take a look at your SQL statements:

 

eg:

 

SELECT Subject FROM joblist WHERE Job='1'

 

in the database that you are accessing from module [subjectResolve] (trace shows database was found OK) is there a table called "joblist"? In table "joblist" are there fields "Subject" and "Job" ?

[Guest Guest_zackrspv]

Hello.

 

So just so i understand correctly; as i have the module looking for the e:\barbara.mdb file already, i don't need the connection string. right?

 

Also as for the syntax on the datbase lookup, in' not that good at programming on that front as i know nothing about it; however from what i see, i know where i went wrong.

 

If i want to select something from the dtabase; i do:

 

Select Subject from Joblist Where Job='1' which in my case is not correct, as all i have is a table called joblist which has the field subject; so it should only say:

 

Select Subject from Joblist where Subject='1'

 

is that correct in assumption?

 

Also, the query wouldn't work for me that well because i don't want to look for the data '1' or '2' or '3' or anything like that; i need to match that number up with a subject; ie caller press 1 for homeopathic; and the database looks for the '1' and returns 'homeopathic' back to VG. How do i do that?

 

We have it this way so that we can add an infinate number of options in the database field list and just have the user type in the number, once the SubjectResolve query runs, it will return the value that will be emailed to the doctor, ie what the caller was calling about.

 

As far as Inserting data into a Microsoft Access Database; is the format similar?

 

Or do i need to figure out another way to put the data into the table.

 

You already know what we need to do; once everything is ran, and the subject is resolved via the key pressed by the caller; it all needs to be writen to the customer table in the various fields provided, so that we can save it for future reference.

 

Also am i correct in assuming that i can make the script ask the user for their phone number and name if the caller id features were bloked?

 

thanks!

[Guest Guest]

Well i got the first databae quwery to work, as i said though, looking for the number is not what i need; but i'm sure that is a simple fix.

 

However, when trying to insert data, i get this error:

 

225716.59 10 ERROR 5.1.7001 DB Execute: 3464,Data type mismatch in criteria expression.

 

Is this is a software error?; i understand the 'Data type mismatch in criteria expression' however, the 'ERROR 5.17001 ' looks like a software problem.

 

here is the command i'm using to insert text:

 

INSERT INTO Customer (Name, Phone, Subject, PIN, DTG) VALUES ('$RV[Name]', '$RV_CIDNUMBER', '$RV[subjectResolve]', '$RV[MakePin]', '$RV_STARTTIME')

 

Is this done wrong?

 

IN the database i have a table, called Customer; which has the Name, Phone, Subject, PIN and DTG fields

 

thanks!

[SupportTeam]

However, when trying to insert data, i get this error:

 

225716.59 10 ERROR 5.1.7001 DB Execute: 3464,Data type mismatch in criteria expression.

 

Is this is a software error?; i understand the 'Data type mismatch in criteria expression' however, the 'ERROR 5.17001 ' looks like a software problem.

 

here is the command i'm using to insert text:

 

INSERT INTO Customer (Name, Phone, Subject, PIN, DTG) VALUES ('$RV[Name]', '$RV_CIDNUMBER', '$RV[subjectResolve]', '$RV[MakePin]', '$RV_STARTTIME')

 

Is this done wrong?

 

IN the database i have a table, called Customer; which has the Name, Phone, Subject, PIN and DTG fields

 

Are "Name, Phone, Subject, PIN, DTG" all defined as "String" database fields? The SQL command you use is trying to insert them all as strings...

 

If a field is not a string field then do not use the quotes around the value...

[Guest Guest_zackrspv]

String, as in specified as just text?

 

if so, then yes.

[SupportTeam]

"Data type mismatch in criteria expression" is an error that occurs when database sees a different type of information then what it expects.

 

I'd try cutting down the SQL to see where the error actually lies, try:

 

INSERT INTO Customer (Name) VALUES ('$RV[Name]')

 

then

 

INSERT INTO Customer (Phone) VALUES ('$RV_CIDNUMBER')

 

then

 

INSERT INTO Customer (Subject) VALUES ('$RV[subjectResolve]')

 

etc etc...

 

If you are still stuck after that I'd start looking around for a local person who knows how to write SQL statements and can look at your database to see how it is set up...

[Guest Guest_zackrspv]

Thanks!

 

Your help in this matter worked great!

 

I had to do a few things to resolve this, though, that i feel i should mention here.

 

We had a few ' out of place as well as one [ out of place lol have to watch the syntax there.

 

As far as the database goes, there were 2 problems; 1. the primary key was empty so it failed, had to make the primary key an autonumber, so that it didn't interfere with the lookup; and 2. had to change the PIN field to TEXT though it is a number; the program doesn't seem to like to put NUMBERS in where NUMBERS go as it stores it as a STRING OF TEXT in the VG script; that was good to know.

 

Thanks much! You'll probably hear back from me alot lol i've a big project to work, and your help is greatly appriciated!

 

Thanks!

[SupportTeam]

2. had to change the PIN field to TEXT though it is a number; the program doesn't seem to like to put NUMBERS in where NUMBERS go as it stores it as a STRING OF TEXT in the VG script;

If the PIN field was a number filed in the database after all then when specifying the SQL command you should not use 'quotes' around that field.... so the SQL command probably should have been:

 

INSERT INTO Customer (Name, Phone, Subject, PIN, DTG) VALUES ('$RV[Name]', '$RV_CIDNUMBER', '$RV[subjectResolve]', $RV[MakePin], '$RV_STARTTIME')

 

This as per the post a few articles back in the thread;

Are "Name, Phone, Subject, PIN, DTG" all defined as "String" database fields? The SQL command you use is trying to insert them all as strings...

 

If a field is not a string field then do not use the quotes around the value...

[Guest Guest_zackrspv]

Well, again! Y'all are gods!

 

Thanks!