Use SQL statement with ADODB.Recordset ????

[ora06]

Hi everybody

I am a newcomer in VC++ developement
I work VC with Oracle database
I want to manipulate the database with ADODB
After connected , i do as follow:
------------------------
recordset->Open("INSERT INTO other_info(card_id) VALUES

(200)",connection.GetInterfacePtr(),adOpenForwardOnly,adLockReadOnly,adCmdText);
------------------------
It worked , but when i want to use the editbox or combobox to take the values from end_users to put in
the SQL statements for updating or inserting --> big problem

recordset->Open("INSERT INTO other_info(card_id) VALUES (m_editbox1) : --> error

In VB 6.0 it is allright ,How can we do it in VC++
Pls , help me to handle this error

Thanks in advance !!!!

 

[DiegoErnesto]

You have two options...

1.- creating the string correctly:

CString sSQL;
sSQL.Format("INSERT INTO other_info(card_id) VALUES ('%s')", m_editbox1);
recordset->Open(sSQL);

2.- use parameter pointer and your methods CreateParameters and Append...

Good luck...

 

[obislavu]

Diego indicated two good solutions but I will use a stored procedure which takes the string displayed in the m_editBox1 and insert this value designed table.
The client code will only prepare the parameter to be passed and make the call of the stored procedure.
-obislavu-

 

[ora06]

Thanks alot Diego and Obislavu

I have done it but it has error
CString sSQL;
sSQL.Format("INSERT INTO employe(name) VALUES ('%s')", m_name);

recordset->Open(sSQL,connection.GetInterfacePtr(),adOpenForwardOnly,adLockReadOnly,adCmdText);

error C2664: 'Open' : cannot convert parameter 1 from 'class CString' to 'const class _variant_t &'
Reason: cannot convert from 'class CString' to 'const class _variant_t'
No constructor could take the source type, or constructor overload resolution was ambiguous


The Open() has 4 parameters and the 1st is a variant so it dont work

to obislavu : Can you give an example of stored Procedures
please

 

[Cagliostro]

use _variant_t(sSQL) instead of sSQL when passing as parameter

Ion Filipski

 

[ora06]

Thanks all

It well done

How can i vote for all of you

to OBISLAVU : Can you give me an ex of stored procedure

 

[obislavu]

Here is an example of procedure which takes table name, the value of the 'name' field to be inserted and two other parameters in the case of where clause.
Evrything is done in the sp and the client code prepare the parameters and call the sp.

CREATE PROCEDURE sp_InsertName (@TableName varchar(50), @name varchar(50), @val1 varchar(50), @val2 varchar(50)) AS
DECLARE @WhereClause varchar(255)
DECLARE @Cmd varchar(1024)
DECLARE @Fields varchar(1024)
Set @Fields = 'name';
Set @WhereClause='WHERE field1=' +''''+ @val1 + ''''+ ' AND field2=' + ''''+ @valfield2 + ''''
Set @Cmd='INSERT INTO' + @TableName + '( ' + @Fields + ') ' + ' VALUES (' + @name +  ' ) ' +  @WhereClause
EXEC(@Cmd)
RETURN(@@IDENTITY)
GO

-obislavu-

 

[Cagliostro]

It id right for MSSQL. Oracle has similar possibility, but wih a bit different syntax.

Ion Filipski