INSERT a variable of type int in a tale

[ZZippo]

Hi,

I have a database built in Sql Express Edition 2005, LabWindows 2010 and Sql Toolkit.

In the data base a have a table called “Students” with the following columns:

“StudentID”- of type int

“FirstName” – of type varchar(50)

“SecondName” – of type varchar(50)  

The target is to INSERT a new record in the table.

I am able to do that if I use:

status = DBImmediateSQL(g_database_handle,"INSERT Students (StudentID,FirstName,SecondName) VALUES (22, 'first_name', 'second_name')");

where “first_name” and “second_name” are local variables;

However, when I substitute 22 (which represents the student id) with a local variable then I get an error.

Here is the code:

status = DBImmediateSQL(g_database_handle,"INSERT Students (StudentID,FirstName,SecondName) VALUES (student_id , 'first_name', 'second_name')");

where student_id is a local integer;

Here is the error message:

NON-FATAL RUN-TIME ERROR:   "write_device.c", line 151, col 21, thread id 0x0000170C:   Function DBImmediateSQL: (return value == -10 [0xfffffff6]). Native error code -2147217900 0x80040e14 Microsoft OLE DB Provider for ODBC Drivers:     [Microsoft][ODBC SQL Server Driver][SQL Server]The name "student_id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Please note that I need the student_id to be a local integer variable!

Can anybody suggest a fix for this?

Thanks!!!

[jrmyers]

I'm not an SQL expert but I would build the command string programmatically with sprintf and pass the string as a function parameter as follows:

char sql_cmd[256];

sprintf (sql_cmd, "INSERT Students (StudentID,FirstName,SecondName) VALUES (%d, 'first_name', 'second_name')", student_id);
status = DBImmediateSQL(g_database_handle, sql_cmd);

Hope this helps...

[ZZippo · Accepted solution]

Hi jrmyers,

Great idea! Why did I not come up with this? I cannot tell you how  I feel now…….

With I couple of minor changes it worked great.

Here is the final solution for who might have the same problem:

char first_name[30];

char second name [30];

char sql_ sql_command[250];

int student_id;

sprintf(sql_command,"INSERT Students (StudentID,FirstName,SecondName) VALUES (%d, '%s', '%s')", student_id,first_name, second_name);

status = DBImmediateSQL(g_database_handle,sql_command);

 if (status != DB_SUCCESS)

 {

     g_show_error();

      return -1;

  }

Thank you very much!

Kindest Regards,

ZZippo