I created my first database ("Microsoft Access Driver (*.mdb)") and am able to successfully select from and insert rows into a table. However, I cannot delete or update rows in that table. I get a null return code from the dbExecSql statement and nothing changes in the table.
Here is an example of a Delete I tried:
sqlstmt = "DELETE FROM QuestTbl WHERE RowNum = 0" /* RowNum is a field in the table */
ptr = DBEXECSQL(dbPtr,sqlstmt) /* ptr and dbPtr are pointers. */
error = dbGetErrorCode(ptr)
IF LEN(error)
MESSAGEBOX w, dbGetErrorText(ptr), "Delete of row zero failed"
dbFreeSql(ptr)
END
ENDIF
dbFreeSql(ptr)
Is there something I'm doing wrong?
In the help file it mentions using SqlGetInfo to find out info about my database but I can't figure out how to use it. Is that worth looking into, and if so, what does a SQLGETINFO command look like?
Thanks.
Your SQL statement has nothing to delete in it.
DELETE item FROM table WHERE condition
The Delete statement is trying to remove the row whose RowNum value is 0.
I have found that it works better if you select the item before you delete it.
Larry,
You're saying do a Select of the row, then follow this immediately with the Delete or Update? I can do that if that's what it takes. Is this a glitch with the MS Access Driver and EBasic?
Would I be better off using something like SQLITE?
What type of field is RowNum in the database?
The other Larry
It's an Integer type.
Is there actually a record with a 0 in the RowNum field?
Larry
Only thing I can suggest is post your code and the dbase file so I or someone else can run it and see what's going on.
Larry
Well it turns out there was a logic problem in my program. When fixed, all worked well.
Sorry for the trouble.
One other question:
My database was of type Microsoft Access Driver (*.mdb). Is there a more robust free database besides this that will work with Vista?
Stu
MySQL:
http://www.mysql.com/
Basically the most popular. It's industry standard anymore. Microsoft also supplies an excellent version of it for free called "Microsoft SQL Server Express."
-S
By the way, Sun likes to hide the free version of MySQL (Community Server) as they're always trying to push their paid version. You can find Community Server 5.1 (latest stable/tested release) here:
http://dev.mysql.com/downloads/mysql/5.1.html
-S
Thanks. I'll check it out.