Excel data inserted into Pervasive Table using a VB Script 1

[leftee]

I am working on a project where i am trying to get rows of data in a Excel sheet inserted into my Pervasive Database on my network server. I have found a VB script that will connect to an Access databse and insert data into it. It works great. I thought i might be able to change the code a bit and maybe make it work with Pervasive. I know i have it connecting properly to my remote Pervasie Databse. I created a seperate script where i just did a Select statement and pulled table data from Pervasive into Excel and it worked fine. The problem my insert script has is when it gets to the Insert statement... i get an error saying the update failed. I was hoping maybe the code for the Insert Statement is all that may need to be modified.

Question? will the code that works with Access be compatible with pervasive at all or does a whole new script have to be written.

I attached a file containing the current code i am trying to use. Thanks for any help that may be offered.

 

[mirtheil]

What's the exact error that's returned?
What version of PSQL are you using?
In your script, your connection string has a Location= parameter that's set to a variable called dbPath. What's the value of that variable?

I would change the following line:

rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt

to this series of lines:

dim sSQL as string
sSQL = "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail
rst.Open sSQL, cnt

That way you can troubleshoot the query to make sure it's correct. For example:

dim sSQL as string
sSQL = "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail
msgbox "Query: " & sSQL
rst.Open sSQL, cnt

Post the query that's actually being generated. We might see something that's wrong (or right).




Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[leftee]

Thanks for the response.

I entered those lines you suggested and i get a error saying "and end of statement is expected" with the = sign right after the strindsSQL being highlited. any ideas ?




The error message: There was an error. Update was not succesful
this is generated from the error checking in the code a few lines below the Insert Statement.


Version: PSQL 10

 

[leftee]

nevermind the end of statement error i figured out what that was.. it was a typo error on my part.


the dbPath is = 192.168.1.70

it pulls that from cell B1 in the excel sheet.

 

[leftee]

here is the result of the query:

Query: Insert INTO (Hours) Values('12')


i get the same update error.

i may be wrong but that number 12 should not have the '' around it.


here is what the insert statement should read based on the column headings i am using


Insert INTO Import(Hours,Major,Minor,gpa) VALUES(33,'pe','music',4.0)

 

[mirtheil]

This query should work:

Insert INTO Import(Hours,Major,Minor,gpa) VALUES(33,'pe','music',4.0)

This query won't work:

Insert INTO (Hours) Values('12')

The problem with this is that it doesn't specify a table name. The '12' may be correct if the field is a string. I tested a quick query on PSQL v11 and even specifying '12' on an integer field and it worked.

You can also change the error message from:

MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"

to something like:

MsgBox "There was an error.  Update was not succesful!" & vbcrlf & "Error Number: " & cstr(Err.Number) & vbcrlf & "Error Description: " & err.description, vbCritical, "Error!"

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[leftee]

YoooHooo , it is now working !! i figured out why the insert statement was not adding my other columns .... i had my name range defined wrong in the excel sheet.

Thanks so much for the help on the insert statement code , i have worked and worked and was about to give up.

i can tell you this i have learned alot about pervasive doing this.