How do I Insert variables in Pervasive 2000i with VB6 2

[MeonR]

Hello

I have been trying to get some array data into a Pervasive
Table uing ADO and VB6 I am currently using this:
fOR I = 0 TO 11
sqlStr = "INSERT INTO TABLENAME(Foo)VALUES(' " & X(I) & " ' )"
command.ActiveConnection = connection
command.CommandText = sqlStr
command.execute
Next i

This produces Syntax Error -2147217900(80040e14)
INSERT INTO TABLENAME(Foo)VALUES(<<???>>)

I am relatively new to Pervasive 2000i Workstation does anyone know the syntax for this?

Thanks in advance
MeonR

 

[mirtheil]

What happens if you use the following:
sqlStr = &quot;INSERT INTO TABLENAME (Foo) VALUES (' &quot; & X(I) & &quot; ' )&quot;

Also, what datatype is Foo? If it's not a string, then get rid of the single quotes like:
sqlStr = &quot;INSERT INTO TABLENAME (Foo) VALUES (&quot; & X(I) & &quot&quot;


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[TomKane]

What is the name of the table? Is it that tablename(foo) is a reference to an array? If it is then maybe the statement should look something like the following:

sqlStr = &quot;INSERT INTO &quot; & TABLENAME(Foo) & &quot; VALUES(' &quot; & X(I) & &quot;' )&quot;

You should put a breakpoint trap on the &quot;command.ActiveConnection = connection&quot; line and then call up your immediate window (ctrl + G if VB6) and type in &quot;? sqlStr&quot; and hit return you should take the contents and see if the sql syntax makes sense and maybe even run it against your database via PCC

Best of luck,
Tom

 

[MeonR]

mirtheil, Tom
Thanks, ran thru it again.
X1 is a string, foo is Char both are 8 bytes, Sorry about the pseudo code. It does'nt seem to work with either type of quote, single or double, So I tried running it through PCC. All it it does is insert X1(i) 12 times. I am about ready to go back to access

Thanks Again

MeonR

 

[mirtheil]

That's good to know. This should work:
sqlStr = &quot;INSERT INTO TABLENAME (Foo) VALUES (' &quot; & X(I) & &quot;')&quot;
That should generate a statement that looks like:
insert into tablename (foo) values ('somestring')

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[MeonR]

Hello,

Thanks for all the help! I finally got it to work. Not sure
what it was exactly, maybe the order of the quote marks?!?!

Again Thanks

 

[mirtheil]

Actually it wasn't the order of the quotes. It was the string generated. The original code produced a statement that looked like:
INSERT INTO TABLENAME(Foo)VALUES(' string ' )
The statement should be:
INSERT INTO TABLENAME (Foo) VALUES ('string')
Notice there is a space between the VALUES and both parenthesis. Also, the string value itself had extra spaces so you would get a string that had a space at the beginning and the end of it.


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[MeonR]

Mirtheil

Thanks for all the help! I came up against this same situation again with another function, altering as you advise did the trick. I will be more careful in the future!

MeonR


&quot;Creating the error, is'nt half as difficult as solving it&quot;

 

[tobyis7084]

Hi,
I am working on a code in VC++ 6.0 and I access an Oracle database by setting up an MFC Appwizard project with Database support. I can run any command of SQL using my VC++ code, but for insert command, I need to insert variables from the code.

For eg: A command: insert into tablename values(&quot;alpha&quot;,&quot;beta&quot;,&quot;gamma&quot; works but I need to do this using variable CString x,y,z and run the command with x,y,z. i.e. insert into tablename values(x,y,z); I need a way to do this and I would really appreciate if you could help me with this. I have tried a lot of places for help and no one seems to know how to achieve this.

I tried the below statements using single, double, their combination, but none work.


Thanks,
-Toby

 

[TomKane]

Hi,

I know practically nothing bout VC++ or Oracle for that matter. But in VB what you can do is that you can see the contents of a string prior to using that string in a SQL statement. If you do this, does the syntax look correct? It might just be case of trial and error using the debugging element of the language.

The sort of thing I do (in VB) is:

strSQL = &quot;INSERT INTO TableName (Field1, Field2, Field3) VALUES ( '&quot; & strString1 & &quot;', '&quot; & strSrting2 & &quot;', '&quot; & strString3 & &quot;')&quot;

Faiing tht have you thought about stored procedures? You could crate a stored procedure and just pass in your variables.

Best of luck,
Tom

 

[tobyis7084]

Hi,
I had already tried the syntax which you gave. But anycase, I tried something new and that worked.

I used
sprintf(message,&quot;Insert into tablename values ('%s','%s','%s')&quot;,x1,x2,x3);

message is char[100] and x1, x2, x3 can be of any type. Mine was CString.

Then I converted message to SQLCHAR to use the
executeSQL(message) function.

And thanks for your help.
-Toby

 

[eire99]

Hi There

I wonder if you can help me.

I am working in Pervasive SQL and not use to it.

this is the query I am writing
insert into CB_Box
(select ABFL.BarCodeLabel,
C.CustKey,
B.Location,
B.Activity,
B.CustBoxNo,
B.RangeFrom,
B.RangeTo,
B.StartDate,
B.EndDate,
B.DestDate,
B.UserField1,
B.UserField2,
B.UserField3,
B.UserField4,
B.Volume,
B.EntryDate,
B.WithdrawalDate
from
"Alt Box File Label" ABFL,
"Customer" C,
"Box" B
where ABFL.CustNo = C.CustNo
and C.CustNo = B.CustNo
and ABFL.DDNo = B.BoxDDNo
and ABFL.CustNo = B.CustNo)

and it has been running for over 40 minutes now and seems to hang the database and looks like no data is going in.

Any suggestions please

 

[mirtheil]

First off, you really should start a new thread when there are new questions.
Secondly, does the Select run fast outside of the insert? How many records does it return? Also, what version of Pervasive are you using? What tool are you using to issue the statement?


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician