May 24, 2022, 02:58:45 AM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Access DB Save Problem

Started by Bruce Peaslee, June 02, 2013, 10:50:08 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Bruce Peaslee

I have an application that uses an Access database. I want the user to be able to create a new database. Using the commands I can create the database and its columns, but I cannot save any records. I do not get any error messages. The puzzling part is that I use the same routine to save records to existing files without any trouble. Or if I create the first record manually, through Access, everyting is fine and new records can be added. The first record is auto increment, if that matters.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

LarryMc

When the user creates the database (and before any records are added) close the database and then reopen it and see if that makes any difference.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

Example of Create, open and Insert, please.  I have usually found that when doing insert, if the number of fields don't match then it gives weird results.

Bill
When all else fails, get a bigger hammer.

Bruce Peaslee

Quote from: LarryMc on June 02, 2013, 11:04:57 AM
When the user creates the database (and before any records are added) close the database and then reopen it and see if that makes any difference.


That works. Any idea why?

Thanks.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

Bruce Peaslee

I noticed that when the file is first created, its size is 64KB. After closing the first time (before any records), it goes to 196KB. I guess it does something at that time to allow it to insert records.
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

billhsln

The following code is set to create a table from scratch and insert into it right after defining it.  This code works.

'=========================
SUB OpenDatabase(),POINTER
'=========================
'DEF dbH, ci, cj, cl, cfnd:INT
DEF dbH, cj, cl, cfnd:INT
DEF pReturn, pdbx:POINTER
DEF flds[16]:STRING
DEF pFlds[16]:INT
DEF dbName = GETSTARTPATH + "Pokemon.mdb":STRING

cname = ""
fname = ""
baseset = ""
cardset = ""
hp = ""
rarity = ""
ctype = ""
natno = ""
fullname = ""
abbrev = ""
setno = ""
setcnt = ""
deck = ""

/* Check to see if Data Base file exists, if not then load 'cards' table */
pReturn = null
IF FileExists(dbName) = 0
dbH = dbCreateMDB(dbName)
pdbx = dbConnect("Microsoft Access Driver (*.mdb)",dbName,";")
IF pdbx = 0
MESSAGEBOX d1,"("+str$(__LINE__)+") Error connecting","Error"
RETURN pReturn
ENDIF
'create table and fields for cards
stmt = "CREATE TABLE cards (recno smallint PRIMARY KEY,cname varchar(70),fname varchar(70),"
stmt += "hp smallint,rarity char(3),ctype char(2),natno char(3),"
stmt += "setno char(4),setcnt char(4),sno byte,vc byte,deck smallint,stage char(1))"
hStmt = dbExecSQL(pdbx,stmt)
IF LEN(dbGetErrorCode(hStmt))
dbFreeSQL(hStmt)
hStmt = 0
dbDisconnect(pdbx)
pdbx = NULL
MESSAGEBOX d1,"("+str$(__LINE__)+") Failed to create DB","Error"
co = 1
OPENCONSOLE
PRINT stmt
RETURN pReturn
ENDIF
dbFreeSQL(hStmt)
hStmt = 0
RecNo = 0
IF OPENFILE(myfile,GETSTARTPATH + "!Pokemon_cards.txt","R") = 0
' Burn header record
READ(myfile,recin)
WHILE (READ(myfile,recin) = 0)
RecNo ++

cfnd = 0
WHILE (cfnd = 0)
cl = INSTR(recin,"'")
IF cl = 0
cfnd = 1
ELSE
recin = MID$(recin,1,cl-1) + "~" + MID$(recin,cl+1)
ENDIF
ENDWHILE

recin += t
cj = Split(recin,t,pFlds)
FOR i = 0 to cj
flds[i] = *<STRING>(pFlds[i])
NEXT i

FOR cl = 6 TO 12
IF LEN(flds[cl]) = 0 THEN flds[cl] = "-"
NEXT cl

RecNo$ = flds[0]
cname = flds[1]
fname = flds[2]
hp = flds[3]
IF hp = "" THEN hp = "0"
rarity = flds[4]
ctype = flds[5]
natno = flds[6]
setno = flds[7]
setcnt = flds[8]
IF hp = "---" THEN hp = "0"
sno = flds[9]
vc = flds[10]
deck = flds[11]
stage = flds[12]

stmt = "INSERT INTO cards (recno,cname,fname,hp,rarity,ctype,natno,setno,setcnt,sno,vc,deck,stage)"
stmt += " VALUES(" + RecNo$ + ",'" + cname + "','" + fname + "'," + hp + ",'" + rarity + "','"
stmt += ctype + "','" + natno + "','" + setno + "','" + setcnt + "'," + sno + "," + vc + ","
stmt += deck + ",'" + stage +"')"
hStmt = dbExecSQL(pdbx,stmt)
IF LEN(dbGetErrorCode(hStmt))
MESSAGEBOX d1,"("+str$(__LINE__)+") Error INSERT " + dbGetErrorCode(hStmt) + " Record " + RecNo$,"Error"
CLOSEFILE(MyFile)
co = 1
OPENCONSOLE
PRINT "Error INSERT ",dbGetErrorCode(hStmt)," Error Record ",RecNo$
dbFreeSQL(hStmt)
dbDisconnect(pdbx)
PRINT
PRINT stmt
RETURN pReturn
ENDIF
dbFreeSQL(hStmt)
ENDWHILE
CLOSEFILE(MyFile)
ENDIF
dbFreeSQL(hStmt)
hStmt = 0
dbDisconnect(pdbx)
ENDIF

RETURN pReturn
ENDSUB



Hope you can use this,
Bill
When all else fails, get a bigger hammer.