using insert sql statement in c/c++

[VishalNikiSharma]

Dear Gurus
Hi
I am using sql statement in my c++ code
i am facing a small problem....
i require to insert some value into my sql table
the values are stored in variable which i like to insert into my table data
so please help me in doing so
i am using something like this.....
RecSet = Con->Execute("Insert into data values('8',firstName,secondName,ageValue)",RecordsAffected,1)
here firstName and secondName are
char *firstName,*secondName
RecordsAffected is
VARIANT *RecordsAfected
RecSet is recordset i am usng
and con is connection pointer.
hope for your help
thanks
visal

[muthuis]

What is the error you are getting? You did not write it!!

[VishalNikiSharma]

The error which is coming is

Runtime Error!

//then there is path of my program and then

abnormal program termination

[muthuis]

Can u cut paste the code here? That way it'll be easier tracing the problem, if anyone can help u.

Because from your code, I can't understand

The way insert string is formatted
Connection object initialisation etc
If there is any other problem

[VishalNikiSharma]

ok so the code is.....
****************************************************
#import "C:\Program Files\Common Files\System\ado\msado15.dll" rename("EOF","EOFile")

#include<stdio.h>
#include<iostream.h>
#include<stdlib.h>

void main()
{

int ageValue;
char *firstName,*secondName;

ADODB::_ConnectionPtr Con = NULL;
ADODB::_RecordsetPtr RecSet = NULL;
VARIANT *RecordsAffected = NULL;


CoInitialize(NULL);

if(Con.CreateInstance(__uuidof(ADODB::Connection),NULL)!= 0)
{
cout<<"Sorry no connection"<<endl;
}
try
{
Con->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = db1.mdb","","",0);
}
catch(_com_error &e)
{
cout<<(char*)e.Description();
}


firstName = (char*)malloc(sizeof(char));
secondName = (char*)malloc(sizeof(char));


cout<<"Enter your first name"<<endl;
cin>>firstName;
cout<<"Enter your surname"<<endl;
cin>>secondName;
cout<<"Enter your age"<<endl;
cin>>ageValue;
cout<<firstName;
RecSet = Con->Execute("INSERT INTO Info VALUES('8',firstName,secondName,ageValue)",RecordsAffected,1);
::CoUninitialize();

}
****************************************************

[VictorN]

Hi!
RecSet = Con->Execute("Insert into data values('8',firstName,secondName,ageValue)",RecordsAffected,1)
try something like this:
CString strSQL;
strSQL.Format(("Insert into data values(\\'8\\',%s,%s,%d)",firstName,secondName,ageValue);
RecSet = Con->Execute(strSQL,RecordsAffected,1)
I've suggested, that your ageValue is integer, if not then use other format specification (not %d).

Regards,
Victor

[muthuis]

I can see two mistakes upfront!

1. You have allocated size of a single character for firstname and second name. If you have more than 1 character in these variables, then u ought to lose the data
2. You have put the variable names inside the insert statement string.
("INSERT INTO Info VALUES('8',firstName,secondName,ageValue)"

So, now your program will try to insert the values "firstname" "secondname" and the "agevalue".

What you actually have to do is this..


char insertstring[MAXIMUMSIZEPOSSIBLE];
sprintf(insertstring,"INSERT INTO Info VALUES('8', \'%s\', \'%s\', %d )", firstname, secondName, ageValue);

RecSet = Con->Execute(insertstring)",RecordsAffected,1);

Hope now you understand.

[VishalNikiSharma]

Hi Victor and Muthuis
Thanks for your help but still i am not able to insert the data

Victor in your case
there is an error

CString is an undeclared identifier
and other follows from this above error



Muthuis in your case
there is same error which was coming earlier.
so please help me again...
hope for the favorable response
vishal

[VictorN]

OK!
I haven't looked at the top of thread, you don't use MFC:(
In this case you should change a little the code of Muthuis :
1. replace MAXIMUMSIZEPOSSIBLE by the number being more than length of your SQL string + 1;
2. replace ' to \\'
You will get something like: ..

char insertstring[_the_lenght_of_SQL_];
sprintf(insertstring,"INSERT INTO Info VALUES(\\'8\\', \\'%s\\', \\'%s\\', %d )", firstname, secondName, ageValue);
RecSet = Con->Execute(insertstring,RecordsAffected,1);

Regards,
Victor

[VishalNikiSharma]

Dear Victor and Muthuis
Thank u for your kind help
atlast i was able to solve the problem without u both it was not possible

Victor, Muthuis code was also working correct but what i was doing wrong is that there was an entry with 8 as primary key so the compiler was firing error

victor why did u ask me to change ' to \'
i am not able to get this...


muthuis thanks for ur cooperation also



hope to meet again in this discussion board
vishal