INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Querry Csv File

Querry Csv File

(OP)
Hy All,

I didn't know where to put this thread o I hope this topic is ok :)

I have Avaya IPOffice and Voicemail Pro 9.1.8 running on Windows (not yet an SQL thing but I'm just getting started )

For you who don't know the products above, I have a PBX and a Windows Serveice that allows me to integrate PBX with a database - i am testing an IVR sistem where the client calls, inputs a n ID , it is being searched in the database and then it does a querry based on another input.
I have tested with .mdb, .accdb and with SQL server and it works.

I also notice that VoicemailPro on Winodws allows me to use Microsoft OLE DB Provider ODBC Driver so I want to use a .csv or .xls file instead of a database.

I have created a DataSource under ODBC Data SOurce Admin (32-bit) -> System DSN -> Driver to Microsoft Excel.

And now I am doing a querry:

Select NrAb,IVechi
From [Sheet1$]
WHERE (NrAB= '$KEY');

$KEY - is the input value

The error I get is this :

07/10 10:28:29.774 vmprov5s (17,2) 65c, 6a0: Database Error: TmodClrObjects::PvtExecute[721]: Failed to "execute the query "Select NrAb,IVechi

From [Sheet1$]

WHERE (NrAB= '123');"", error -2147217913 [0x80040e07] - [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression. [source=Microsoft OLE DB Provider for ODBC Drivers] [[0]: Error number -2147217913 [80040e07] - [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression., native number -3030 - 22005].
07/10 10:28:29.774 vmprov5s (09,2) 65c, 6a0: Session: 00000064 - Database execute failed: TmodClrObjects::PvtExecute[721]: Failed to "execute the query "Select NrAb,IVechi

From [Sheet1$]

WHERE (NrAB= '123');"", error -2147217913 [0x80040e07] - [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression. [source=Microsoft OLE DB Provider for ODBC Drivers] [[0]: Error number -2147217913 [80040e07] - [Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression., native number -3030 - 22005].


It is prety self explanatory for me the Data type mistmatch error, but may there is something else here..

Any ideas, Any hints?

Thank you!

RE: Querry Csv File

HI,

Quote:

have created a DataSource under ODBC Data SOurce Admin (32-bit) -> System DSN -> Driver to Microsoft

I've always used a User DSN in the ODBC Data Source Adminstrator (32-bit) for Excel Files.

You appear to have opened the .csv using Excel and SaveAs as an Excel workbook, from the SQL that you posted, so this Excel sheet is your table in the workbook database, yes?

Select NrAb,IVechi
From [Sheet1$]
WHERE (NrAB= '$KEY');
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Querry Csv File

(OP)
Yes,

That is correct. :)

RE: Querry Csv File

(OP)
Ok,

So I used a User DSN and no more erros.. now, I see that the information is stored in my variables in an unknown format for me.
After the Select querry Voicemail is storing into $DBD0 - info from 1st column, $DBD1- info from second column.. and so on ..
When I check the information stored it looks like this :
For a querry like

Select *
From [Sheet1$]
WHERE ([NrAB]= 1001);

they return:
$DBD0 $DBD1 $DBD2 $DBD3
1001.0, 1001.1, 1001.1, 1001.3

now i'm really lost ..

RE: Querry Csv File

At this point since your query works, why not post in an Avaya forum.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Querry Csv File

(OP)
Because I wanted to jump to my next problem..

After the querry I want to modify those DBD values and then do an INSERT..

When I do the insert this is what i get :


15/10 15:14:03.416 vmprov5s (17,2) 764,1260: Database Error: TmodClrObjects::PvtExecute[721]: Failed to "execute the query "INSERT INTO [Sheet1$]

(DataInreg, INou, IVechi, NrAB, Numar )

VALUES (1001.0,1001.1,1001.1,1001.3,801);"", error -2147467259 [0x80004005] - [Microsoft][ODBC Excel Driver] Operation must use an updateable query. [source=Microsoft OLE DB Provider for ODBC Drivers] [[0]: Error number -2147467259 [80004005] - [Microsoft][ODBC Excel Driver] Operation must use an updateable query., native number -3035 - S1000].
15/10 15:14:03.416 vmprov5s (09,2) 764,1260: Session: 00000087 - Database execute failed: TmodClrObjects::PvtExecute[721]: Failed to "execute the query "INSERT INTO [Sheet1$]

(DataInreg, INou, IVechi, NrAB, Numar )

VALUES (1001.0,1001.1,1001.1,1001.3,801);"", error -2147467259 [0x80004005] - [Microsoft][ODBC Excel Driver] Operation must use an updateable query. [source=Microsoft OLE DB Provider for ODBC Drivers] [[0]: Error number -2147467259 [80004005] - [Microsoft][ODBC Excel Driver] Operation must use an updateable query., native number -3035 - S1000].
15/1

RE: Querry Csv File

(OP)
PS the querry works in the sense that i don't receive an error. but the values that are stored are incorrect..

RE: Querry Csv File

"but the values stored are incorrect"

What exact values did you have in your variables, and what were the exact incorrect values that resulted in Sheet1?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Querry Csv File

>i don't receive an error
What you show is an error.

Check whether the system account running MSSQL Server (typically that's NETWORK SERVICE) has write access to the excel file.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close