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
Posted on 2011-01-13 07:16:04 by devilhorse
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.
Posted on 2011-01-18 03:52:30 by beaster