I cannot figure out how to modify the AddData procedure below to successfully insert data into the table because it has an auto incrementing primary key field. If I remove the primary key field it works. It fails if I leave th e primary key field in the table. Anyone know the solution to this. I have Googled and even read the ODBC Programmer's Handbook and can't get a hint on what to do.
table:
ID INTEGER PRIMARY KEY
Name TEXT(50)
Address TEXT(128)
"CREATE TABLE IF NOT EXISTS table (ID INTEGER PRIMARY KEY,Name varchar(50) UNIQUE,Address varchar(128)"
Note: hdbc1 in code below is the the global connection handle already allocated prior to calling this procedure
This program is using ODBC Version 3
IDE is EasyCode for masm
AddData Proc
Local SqlBuffer[128]:Byte
Local NameBuffer[50]:Byte ;name
Local AddressBuffer[128]:Byte ;address
Local lenNameBuffer:DWord
Local lenAddressBuffer:DWord
Local dwStmt:DWord
;Get the data
Invoke GetText, ediName, Addr NameBuffer
Invoke GetText, editAddress, Addr AddressBuffer
;copy the sql statement into buffer
Invoke lstrcpy, Addr SqlBuffer, CTXT("INSERT INTO myTable VALUES (?,?);")
;Allocate a statement handle
Invoke SQLAllocHandle, SQL_HANDLE_STMT, hdbc1, Addr dwStmt
;Prepare the statement
Invoke SQLPrepare, dwStmt, Addr SqlBuffer, SizeOf SqlBuffer
;Bind the parameters
Invoke lstrlen, Addr NameBuffer
Mov lenNameBuffer, Eax
Invoke SQLBindParameter, dwStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 50, 0, Addr NameBuffer, SizeOf NameBuffer, Addr lenNameBuffer
Invoke lstrlen, Addr AddressBuffer
Mov lenAddressBuffer, Eax
Invoke SQLBindParameter, dwStmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 128, 0, Addr AddressBuffer, SizeOf AddressBuffer, Addr lenAddressBuffer
;Execute the statement
Invoke SQLExecute, dwStmt
.If Ax == SQL_SUCCESS || Ax == SQL_SUCCESS_WITH_INFO
Invoke MessageBox, NULL, TextAddr("SQLExecute Success"), TextAddr(" "), MB_OK
.Else
Invoke MessageBox, NULL, TextAddr("SQLExecute Failure"), TextAddr("ALARM!!! "), MB_OK
.EndIf
;Destroy the statement
Invoke SQLCloseCursor, dwStmt
Invoke SQLFreeHandle, SQL_HANDLE_STMT, dwStmt
Ret
AddData EndP
table:
ID INTEGER PRIMARY KEY
Name TEXT(50)
Address TEXT(128)
"CREATE TABLE IF NOT EXISTS table (ID INTEGER PRIMARY KEY,Name varchar(50) UNIQUE,Address varchar(128)"
Note: hdbc1 in code below is the the global connection handle already allocated prior to calling this procedure
This program is using ODBC Version 3
IDE is EasyCode for masm
AddData Proc
Local SqlBuffer[128]:Byte
Local NameBuffer[50]:Byte ;name
Local AddressBuffer[128]:Byte ;address
Local lenNameBuffer:DWord
Local lenAddressBuffer:DWord
Local dwStmt:DWord
;Get the data
Invoke GetText, ediName, Addr NameBuffer
Invoke GetText, editAddress, Addr AddressBuffer
;copy the sql statement into buffer
Invoke lstrcpy, Addr SqlBuffer, CTXT("INSERT INTO myTable VALUES (?,?);")
;Allocate a statement handle
Invoke SQLAllocHandle, SQL_HANDLE_STMT, hdbc1, Addr dwStmt
;Prepare the statement
Invoke SQLPrepare, dwStmt, Addr SqlBuffer, SizeOf SqlBuffer
;Bind the parameters
Invoke lstrlen, Addr NameBuffer
Mov lenNameBuffer, Eax
Invoke SQLBindParameter, dwStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 50, 0, Addr NameBuffer, SizeOf NameBuffer, Addr lenNameBuffer
Invoke lstrlen, Addr AddressBuffer
Mov lenAddressBuffer, Eax
Invoke SQLBindParameter, dwStmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 128, 0, Addr AddressBuffer, SizeOf AddressBuffer, Addr lenAddressBuffer
;Execute the statement
Invoke SQLExecute, dwStmt
.If Ax == SQL_SUCCESS || Ax == SQL_SUCCESS_WITH_INFO
Invoke MessageBox, NULL, TextAddr("SQLExecute Success"), TextAddr(" "), MB_OK
.Else
Invoke MessageBox, NULL, TextAddr("SQLExecute Failure"), TextAddr("ALARM!!! "), MB_OK
.EndIf
;Destroy the statement
Invoke SQLCloseCursor, dwStmt
Invoke SQLFreeHandle, SQL_HANDLE_STMT, dwStmt
Ret
AddData EndP
Unfortunately I have no direct solution, but I recommend the Microsoft "odbct32w.exe" tool (coming with the platform SDK). This is a graphical frontend to the ODBC API where you can call all operations manually and check detailed output.
I also recommend to switch on the ODBC data source logging, normally you can find good error descriptions there too, more than you will get out of the error code.
At least I missed sometimes the proper setup of the ODBC environment, e.g. SQLSetEnvAttr (.., SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, ..) etc, maybe you can check these too.
I also recommend to switch on the ODBC data source logging, normally you can find good error descriptions there too, more than you will get out of the error code.
At least I missed sometimes the proper setup of the ODBC environment, e.g. SQLSetEnvAttr (.., SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, ..) etc, maybe you can check these too.