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

Excel data inserted into Pervasive Table using a VB Script

Excel data inserted into Pervasive Table using a VB Script

(OP)
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.    

RE: Excel data inserted into Pervasive Table using a VB Script

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:

CODE

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

to this series of lines:

CODE

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:

CODE

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

RE: Excel data inserted into Pervasive Table using a VB Script

(OP)
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

RE: Excel data inserted into Pervasive Table using a VB Script

(OP)
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.

RE: Excel data inserted into Pervasive Table using a VB Script

(OP)
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)

RE: Excel data inserted into Pervasive Table using a VB Script

This query should work:

CODE

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

This query won't work:

CODE

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:

CODE

MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
to something like:

CODE

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

RE: Excel data inserted into Pervasive Table using a VB Script

(OP)
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.

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