Need Help Inserting Record to MS-Access

[RTEK]

Hi,

I need help with inserting a record into an MS-Access table.
All fields for the table were defined as text fields.
My program would not insert a record into the MS-Access table.
Portions of the code were copied from postings found here in the forum.
I am currently using PB v4.1

Shown below is the insert routine I'm using

Code: Select all

Procedure RecordResults()  
  ;**********************
  ;* Open History Table *
  ;**********************
  File$ = "StudentRecord.mdb" 
  EXT.s=UCase(GetExtensionPart(File$)) 
  dbStatus=Makeconnection("Microsoft Access Driver (*.mdb)","Server=SomeServer; Description=Description For Purebasic MDB-ODBC;DSN=PureBasic_DSN;DBQ="+File$+";UID=Rings;PWD=Siggi;") 

  If UseODBCDatabase() = 0 
    MessageRequester("Error", "Can't initialize Database (ODBC v3 or better) environment", 0) 
    End 
  EndIf 

  ; Open an ODBC database 
  User$="" 
  Password$="" 
  #Database=1

  dbStatus = OpenDatabase(#Database, "PureBasic_DSN", User$, Password$)   
  
  ;*********************
  ;* Insert New Record *
  ;*********************
  If dbStatus 
    catNum.s = Str(categoryNumber)
    If Len(catNum) = 1
      catNum = "0" + catNum
    EndIf
    testDate.s = FormatDate("%yyyy-%mm-%dd %hh:%mm", Date())
    modeStr.s = Str(mode)
    itemCtStr.s = Str(itemCt)
    scoreStr.s = Str(correct)
  
    SQLstr.s ="insert into history (category, testdate, testmode, itemct, score) values ("    
    SQLstr = SQLstr + Chr(34) + catNum + Chr(34) + "," + Chr(34) + testdate + Chr(34) + "," + Chr(34) + modeStr + Chr(34) + "," + Chr(34) + itemCtStr + Chr(34) + "," + Chr(34) + scoreStr + Chr(34) + ");"
    
    Debug SQLstr
    DatabaseQuery(#Database, SQLstr)                                                    
  EndIf 
  
  ;***********************
  ;* Close History Table *
  ;***********************
  DeleteConnection("Microsoft Access Driver (*.mdb)","PureBasic_DSN")     
EndProcedure

when I run the program the debug statement show the content of SQLstr as follows:

Code: Select all

insert into history (category, testdate, testmode, itemct, score) values ("22","2008-06-15 23:06","0","150","0")

If I copy this statement and run this SQL within MS-Access a new record with the given values are added to the table.
I had also tried using

Code: Select all

DatabaseUpdate(#Database, SQLstr) 

with the same result - no record added to the history table.

Anyone have an idea of what's wrong with my code and how to solve this problem?

[Foz]

A few things:

You are using DatabaseQuery() rather than DatabaseUpdate() in your code to update the database. That's a no no.

Secondly, if the DatabaseUpdate() fails, it returns 0. Now if it returns 0, you can then call DatabaseError() which will then tell you want the problem is

Thirdly, and I think this is the answer to your error: strings are defined with SINGLE quotes, not double. A Double Quote means that it is referencing a column.
Sure it might work in access, but that isn't using the ODBC to do it's work on its own file format - it's using it's own method of executing sql, which is slightly different to "standard" sql. If you want to use "standard" sql, you "have" to use sql server.

[Marco2007]

Well, I have no problems with DatabaseQuery + Insert.

Chr(34) is wrong. You need chr(39).

I´ll use e.g. this and it works:

Code: Select all

If GetGadgetText(#Stamm_psp)<>""
    pspname.s="Kunde, PSP, Ma, Datum, pspnummer, flag, Kundennummer"
    pw.s="('"+GetGadgetText(#Stamm_Name)+"', '"+GetGadgetText(#Stamm_psp)+"', '"+getusername()+"', '"+"Datum"+"', "+RemoveString(GetGadgetText(#Stamm_psp), "S")+", '"+"0"+"', '"+GetGadgetText(#Stamm_Kundennummer)+"')"
      If DatabaseQuery(#dbStamm, "Insert into Datentest ("+pspname+") values "+pw)
      Else
      MessageRequester("Achtung", "Kann die folgende Abfrage 'PSP - Stammpsp' nicht ausführen: "+DatabaseError())  
      EndIf
  EndIf

lg
Marco

[RTEK]

@Marco and Foz

It's working now. Thanks a lot for the help.


....my use of chr(34) is a carry-over from VB

[Rook Zimbabwe]

You are using DatabaseQuery() rather than DatabaseUpdate() in your code to update the database. That's a no no.

Well... really... If you construct the query correctly there is no real difference whichever one you use... I do think DatabaseUpdate() is picky at times!


UPDATE {{ table name}} SET ([ column name1 ], [ column name2 ]) VALUES (' value1 ', ' value2 ') WHERE ID = {{ Autonumber INDEX of record }}

I always return the ID (autonumber INDEX) with all my data reads... it is far easier to update a row of set of rows that way... ID is what access usually calls the INDEX but you can rename it!