VoiceGuide IVR Software Main Page
Jump to content

Saving Data In Access

Recommended Posts

Our database query module keeps "failing", and the data is not being saved in Access.

 

I have attached the script, and the Access file.

 

The query module being used is the "Database Query Parent".

 

Here is the relevant portion of the trace log:

 

114945.75 6 rv lg add [bDI - question 20 B]{1}

114945.75 6 timer clear

114945.77 6 state [Database Query Parent] DB Query

114945.77 6 db ODBC mode used

114945.77 6 rv replace start (strlen>500)

114945.78 6 rv ns [PathSysVoice]{C:\Program Files\VoiceGuide\system\voice\}[PathApp]{C:\Program Files\VoiceGuide\}[PathDataVm]{C:\Program Files\VoiceGuide\data\}[PathVgSys]{C:\Program Files\VoiceGuide\system\}[DNIS]{0000}[scriptsPath]{C:\Program Files\VoiceGuide\Scripts\}[scriptPath]{C:\Program Files\VoiceGuide\Scripts}[$RV_STARTTIME]{5/5/2006 11:38:24 AM}[$RV_DEVICEID]{6}[$RV_CIDNAME]{}[PathApp]{C:\Program Files\VoiceGuide\}[$RV_CIDNUMBER]{2404268959}[$RV_DNIS]{0000}[DNIS]{0000}[Welcome]{1}[Welcome 2]{1}[iD]{1234}[iD_PathTaken]{success}[Parent/Child]{1}[Parent Instructions]{1}[Parent example question]{1}[if 1 is answer to example question]{1}[bDI - question 1]{timeout}[bDI - question 1 B]{2}[bDI - question 2]{timeout}[bDI - question 2 B]{4}[bDI - question 3]{timeout}[bDI - question 3 B]{3}[bDI - question 4]{timeout}[bDI - question 4 B]{2}[bDI - question 5]{timeout}[bDI - question 5 B]{3}[bDI - question 6]{timeout}[bDI - question 6 B]{2}[bDI - question 7]{timeout}[bDI - question 7 B]{1}[bDI - question 8]{timeout}[bDI - question 8 B]{1}[bDI - question 9]{timeout}[bDI - question 9 B]{1}[bDI - question 10]{timeout}[bDI - question 10 B]{1}[bDI - question 11]{timeout}[bDI - question 11 B]{2}[bDI - question 12]{timeout}[bDI - question 12 B]{2}[bDI - question 13]{timeout}[bDI - question 13 B]{2}[bDI - question 14]{timeout}[bDI - question 14 B]{3}[bDI - question 15]{timeout}[bDI - question 15 B]{3}[bDI - question 16]{timeout}[bDI - question 16 B]{3}[bDI - question 17]{timeout}[bDI - question 17 B]{3}[bDI - question 18]{timeout}[bDI - question 18 B]{2}[bDI - question 18b]{2}[bDI - question 19]{timeout}[bDI - question 19 B]{2}[bDI - question 20]{timeout}[bDI - question 20 B]{1}

114945.78 6 db About to execute a Modify type operation [iNSERT INTO ProjectChatLogParent (ID, BDIQ1, BDIQ2, BDIQCONNECTEDQ5, BDIQ6, BDIQ7, BDIQ8, BDIQ9, BDIQ10, BDIQ11, BDIQ12, BDIQ13, BDIQ14, BDIQ15, BDIQ16, BDIQ17, BDIQ18, BDIQ18b, BDIQ19, BDIQ20) VALUES ('1234', '2', '4', '3', '2, '3, '2', '1', '1', '1', '1', '2', '2', '2', '3', '3', '3', '3', '2', '2', '2', '1')]

114945.78 6 ERROR 5.2.5026 DB Execute: 3146,ODBC--call failed.

114945.80 6 rv lg add [Database Query Parent_RowCount]{0}

114945.80 6 db DBEngine.Error nbr:[-3100] desc:[37000: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''2, '3'.] src:[ODBC.Database]

114945.80 6 db DBEngine.Error nbr:[3146] desc:[ODBC--call failed.] src:[DAO.Database]

 

 

I don't know very much about computers, so I can not interpret what is going on here. Can you please help?

ProjectChat.zip

Share this post


Link to post

The error message pretty much tells you what the problem with the SQL statement is:

tHE Syntax error (missing operator) in query expression ''2, '3'.]

This is your SQL expression:

 

INSERT INTO ProjectChatLogParent (ID, BDIQ1, BDIQ2, BDIQCONNECTEDQ5, BDIQ6, BDIQ7, BDIQ8, BDIQ9, BDIQ10, BDIQ11, BDIQ12, BDIQ13, BDIQ14, BDIQ15, BDIQ16, BDIQ17, BDIQ18, BDIQ18b, BDIQ19, BDIQ20) VALUES ('1234', '2', '4', '3', '2, '3, '2', '1', '1', '1', '1', '2', '2', '2', '3', '3', '3', '3', '2', '2', '2', '1')

 

See how it's missing a single quote in to places...

 

Here is what it should be:

 

INSERT INTO ProjectChatLogParent (ID, BDIQ1, BDIQ2, BDIQCONNECTEDQ5, BDIQ6, BDIQ7, BDIQ8, BDIQ9, BDIQ10, BDIQ11, BDIQ12, BDIQ13, BDIQ14, BDIQ15, BDIQ16, BDIQ17, BDIQ18, BDIQ18b, BDIQ19, BDIQ20) VALUES ('1234', '2', '4', '3', '2', '3', '2', '1', '1', '1', '1', '2', '2', '2', '3', '3', '3', '3', '2', '2', '2', '1')

Share this post


Link to post

Now the error is "number of query values and destination fields are not the same" (see event log below).

 

They ARE the same as far as I can tell...

 

Why is the field name BDIquestion4 showing up as BDIquestionCONNECTEDn4, and where did BDIquestion3 go in the log? If you look at the query module in the script, BDIquestion3 and BDIquestion4 are there...

 

134039.25 6 rv lg add [bDI - question 20]{timeout}

134039.25 6 timer clear

134039.27 6 state [bDI - question 20 B] Playing

134039.27 6 state [bDI - question 20 B] Playing ()

134039.27 6 ScriptEventCode 8001 iLineState=1100

134039.27 6 LsPlayMsg EV_PLAY_FINISHED

134039.28 6 eng set timer EV_TIMEOUT_REPLAYMSG time=5

134039.28 6 timer set 5 EV_TIMEOUT_REPLAYMSG

134039.28 6 RunModule PLAY end

134041.31 6 dtmf 2 (66082,50,2)

134041.31 6 ScriptEventCode 50 iLineState=1101

134041.31 6 LsPlayMsgFinished 2

134041.33 6 rv lg add [bDI - question 20 B]{2}

134041.33 6 timer clear

134041.33 6 state [Database Query Parent] DB Query

134041.33 6 db ODBC mode used

134041.34 6 rv replace start (strlen>500)

134041.34 6 rv ns [PathSysVoice]{C:\Program Files\VoiceGuide\system\voice\}[PathApp]{C:\Program Files\VoiceGuide\}[PathDataVm]{C:\Program Files\VoiceGuide\data\}[PathVgSys]{C:\Program Files\VoiceGuide\system\}[DNIS]{0000}[scriptsPath]{C:\Program Files\VoiceGuide\Scripts\}[scriptPath]{C:\Program Files\VoiceGuide\Scripts}[$RV_STARTTIME]{5/8/2006 1:31:38 PM}[$RV_DEVICEID]{6}[$RV_CIDNAME]{}[PathApp]{C:\Program Files\VoiceGuide\}[$RV_CIDNUMBER]{2404268959}[$RV_DNIS]{0000}[DNIS]{0000}[Welcome]{1}[Welcome 2]{1}[iD]{1234}[iD_PathTaken]{success}[Parent/Child]{1}[Parent Instructions]{1}[Parent example question]{1}[if 1 is answer to example question]{1}[bDI - question 1]{timeout}[bDI - question 1 B]{4}[bDI - question 2]{timeout}[bDI - question 2 B]{1}[bDI - question 3]{timeout}[bDI - question 3 B]{1}[bDI - question 4]{timeout}[bDI - question 4 B]{2}[bDI - question 5]{timeout}[bDI - question 5 B]{2}[bDI - question 6]{timeout}[bDI - question 6 B]{2}[bDI - question 7]{timeout}[bDI - question 7 B]{2}[bDI - question 8]{timeout}[bDI - question 8 B]{3}[bDI - question 9]{timeout}[bDI - question 9 B]{2}[bDI - question 10]{timeout}[bDI - question 10 B]{1}[bDI - question 11]{timeout}[bDI - question 11 B]{3}[bDI - question 12]{timeout}[bDI - question 12 B]{3}[bDI - question 13]{timeout}[bDI - question 13 B]{2}[bDI - question 14]{timeout}[bDI - question 14 B]{1}[bDI - question 15]{timeout}[bDI - question 15 B]{2}[bDI - question 16]{timeout}[bDI - question 16 B]{4}[bDI - question 17]{timeout}[bDI - question 17 B]{4}[bDI - question 18]{timeout}[bDI - question 18 B]{3}[bDI - question 18b]{2}[bDI - question 19]{timeout}[bDI - question 19 B]{1}[bDI - question 20]{timeout}[bDI - question 20 B]{2}

134041.36 6 db About to execute a Modify type operation [iNSERT INTO ProjectChatLogParent (ID, BDIquestion1, BDIquestion2, BDIquestionCONNECTEDn4, BDIquestion5, BDIquestion6, BDIquestion7, BDIquestion8, BDIquestion9, BDIquestion10, BDIquestion11, BDIquestion12, BDIquestion13, BDIquestion14, BDIquestion15, BDIquestion16, BDIquestion17, BDIquestion18, BDIquestion18b, BDIquestion19, BDIquestion20) VALUES ('1234', '4', '1', '1', '2', '2', '2', '2', '3', '2', '1', '3', '3', '2', '1', '2', '4', '4', '3', '2', '1', '2')]

134041.36 6 ERROR 5.2.5026 DB Execute: 3146,ODBC--call failed.

134041.36 6 rv lg add [Database Query Parent_RowCount]{0}

134041.38 6 db DBEngine.Error nbr:[-3520] desc:[21S01: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.] src:[ODBC.Database]

134041.38 6 db DBEngine.Error nbr:[3146] desc:[ODBC--call failed.] src:[DAO.Database]

projectchat.zip

Share this post


Link to post
Now the error is "number of query values and destination fields are not the same" (see event log below).

 

They ARE the same as far as I can tell...

By our count there are 21 fields listed, but there are 22 values supplied...

Share this post


Link to post

Yes, but if you look at the query module in the script (see attached file in last post) you will see that there are 22 fields listed. For some reason, the field name BDIquestion4 is showing up as BDIquestionCONNECTEDn4 in the event log, and BDIquestion3 is not showing up at all...BDIquestion3 and BDIquestion4 are both in the query module in the script...why are they not showing up correctly in the log?

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×