I have a program that creates a SQlite DB with several tables. The create function creates all the tables except one the "Patients" table. I can not see anything wrong in the code. Can you see any problem in the "Create Patient" section?
Function FORM1_COMMAND1_BN_CLICKED ( _
ControlIndex As Long, _ ' index in Control Array
hWndForm As Dword, _ ' handle of Form
hWndControl As Dword, _ ' handle of Control
idButtonControl As Long _ ' identifier of button
) As Long
'.........Create Apothesoft SQL Database..........
Local lErr As String
Local t1 As String
Local lDgF As String
Local lDrF As String
Local lInsF As String
Local lMCAF As String
Local lDCMF As String
Local lDgMsgF As String
Local lPriF As String
Local lPtF As String
Local lPrcF As String
Local lRfF As String
Local lRxF As String
Local lSgF As String
Local lZipF As String
Local lCfgF As String
Local lNRecF As String
slOpen "ApotheSQL.db3", "CE0"
lErr = slGetError
If Val(lErr) Then
MsgBox lErr & " In CreateDB Function", 0, "Database Open Error"
Function = Val(lErr)
Dialog End HWND_FORM1, 0 'End program
'Exit Function
End If
'--------------------------------------------------------------------------------
lCfgF = "CfNum, CfName, CfStreet, CfCity, CfState, CfZIP, CfPhone, CfFax, CfNCPDP, CfStCSN, " & _
"CfUseDDI, CfLblType, CfLblPath, CfPrtPath, CfFaxPath, CfClmPath, CFChkQOH, " & _
"CfUsrLogn, CfUsrAudLog, CfEmail, CfMstPW, CfTecPW, CfAutoSSL, CfASAPtyp, CfASAPbin, " & _
"CfASAPid, CfASAPpw, CfASAPex, CfASAPc2Lic, CfASAPopt1, CfASAPopt2, CfStaxPct, " & _
"CfBlnkFld, CfFlag1, CfFlag2, CfFlag3, CfFlag4, CfFlag5"
t1 = "CREATE TABLE CfgRec (" & lCfgF & ");"
slExe "Begin" 'This starts the transaction
slExe t1, "E0"
slExe "CREATE INDEX CfNum ON CfgRec (CfNum);", "E0"
slExe "CREATE INDEX CFName ON CfgRec (CfName);", "E0"
slExe "End"
MsgBox "Config Created"
'--------------------------------------------------------------------------------
lDCMF = "DgMsgNum, DgMsg"
t1 = "CREATE TABLE DgMsgs (" & lDCMF & ");"
slExe "Begin" 'This starts the transaction
slExe t1, "E0"
slExe "CREATE INDEX DgMsgNum ON DgMsgs (DgMsgNum);", "E0"
slExe "End"
MsgBox "Drug Message Created"
'--------------------------------------------------------------------------------
lDgF = "DgNum, DgName, DgIDQ, DgID, DgMfgName, DgGenFor, DgGenBrnd, DgUnitTyp, DgCScode, " & _
"DgType, DgAAC, DgAWP, DgMAC, DgUnPkg, DgUDind, DgSordN, DgXMsg, DgPrcTbl, DgDefSIG, " & _
"DgLstUpDt, DgQtyOH, DgInfoLnk, DgMsg1C, DgMsg2C, DgMsg3C, " & _
"DgMsg4C, DgMsg5C, DgMsg6C, DgLockSKU, DgLockAAC, DgLockAWP, DgLockMAC, DgNote, " & _
"DgRouteAdm, DgDispUnit, DgDoseForm, DgCmpFormTL, DgCmpFormUM, DgCmpDir, " & _
"DgCmpNam0, DgCmpIDQ0, DgCmpID0, DgCmpQty0, DgCmpUM0, DgCmpCost0, DgCmpBOC0, " & _
"DgCmpNam1, DgCmpIDQ1, DgCmpID1, DgCmpQty1, DgCmpUM1, DgCmpCost1, DgCmpBOC1, " & _
"DgCmpNam2, DgCmpIDQ2, DgCmpID2, DgCmpQty2, DgCmpUM2, DgCmpCost2, DgCmpBOC2, " & _
"DgCmpNam3, DgCmpIDQ3, DgCmpID3, DgCmpQty3, DgCmpUM3, DgCmpCost3, DgCmpBOC3, " & _
"DgCmpNam4, DgCmpIDQ4, DgCmpID4, DgCmpQty4, DgCmpUM4, DgCmpCost4, DgCmpBOC4, " & _
"DgCmpNam5, DgCmpIDQ5, DgCmpID5, DgCmpQty5, DgCmpUM5, DgCmpCost5, DgCmpBOC5, " & _
"DgCmpNam6, DgCmpIDQ6, DgCmpID6, DgCmpQty6, DgCmpUM6, DgCmpCost6, DgCmpBOC6, " & _
"DgCmpNam7, DgCmpIDQ7, DgCmpID7, DgCmpQty7, DgCmpUM7, DgCmpCost7, DgCmpBOC7, " & _
"DgCmpNam8, DgCmpIDQ8, DgCmpID8, DgCmpQty8, DgCmpUM8, DgCmpCost8, DgCmpBOC8, " & _
"DgCmpNam9, DgCmpIDQ9, DgCmpID9, DgCmpQty9, DgCmpUM9, DgCmpCost9, DgCmpBOC9"
'We are doing a transaction, sending several SQL statements to the server
'Build tables and indexes
t1 = "CREATE TABLE Drugs (" & lDgF & ");"
slExe "Begin" 'This starts the transaction
slExe t1, "E0"
slExe "CREATE INDEX DgNum ON Drugs (DgNum);", "E0"
slExe "CREATE INDEX DgName ON Drugs (DgName);", "E0" 'DgName-DrNum must be unique
slExe "CREATE INDEX DgID ON Drugs (DgID);", "E0" 'NDC must be made unique
slExe "End"
MsgBox "Drug Table Created"
'--------------------------------------------------------------------------------
lDrF = "DrNum, DrName collate NoCase, DrStreet, DrCity, DrState, DrZip, DrPhoneOff, " & _
"DrFax, DrSpec, DrBnkCrd, DrID1specQ, DrID1spec, DrID2specQ, DrID2spec, DrDEA, DrMedicaid, " & _
"DrNPI, DrStateLic, DrTIN, DrMedicare, DrLocCode, DrEmail, DrMsg"
t1 = "CREATE TABLE Doctors (" & lDrF & ");"
slExe "Begin" 'This starts the transaction 'Note All Index Fields have Unique Number added
slExe t1, "E0"
slExe "CREATE INDEX DrNum ON Doctors (DrNum);", "E0"
slExe "CREATE INDEX DrName ON Doctors (DrName);", "E0" 'Lname-Fname-DrNum
slExe "CREATE INDEX DgDEA ON Doctors (DrDEA);", "E0" 'DrDEA-DrNum
slExe "CREATE INDEX DgNPI ON Doctors (DrNPI);", "E0" 'DrNPI-DrNum
slExe "CREATE INDEX DgPhone ON Doctors (DrPhoneOff);", "E0" 'DrPhoneOff-DrNum
slExe "End"
MsgBox "Doctor Table Created"
'--------------------------------------------------------------------------------
lInsF = "InsNum, InsName, InsBin, InsPhIDQ, InsPhID, InsPCN, InsVndrCert, InsDefGrp, " & _
"InsVrsnFrmt, InsHelpPh, InsDrIDQ, InsPTnumR, InsPTnumG, InsLstDtRR, InsCobRule, " & _
"InsSegExc, InsBOC"
t1 = "CREATE TABLE Insurances (" & lInsF & ");"
slExe "Begin" 'This starts the transaction 'Note All Index Fields have Unique Number added
slExe t1, "E0"
slExe "CREATE INDEX InsNum ON Insurances (InsNum);", "E0"
slExe "CREATE INDEX InsName ON Insurances (InsName);", "E0" 'InsName-InsNum
slExe "CREATE INDEX InsBin ON Insurances (InsBin);", "E0" 'InsBin-InsNum
slExe "End"
MsgBox "Insurance Table Created"
'--------------------------------------------------------------------------------
lMCAF = "maPtNum, maQ, msKey, maCode, maDesc, maClass"
t1 = "CREATE TABLE MedCondAllgs (" & lMCAF & ");"
slExe "Begin" 'This starts the transaction
slExe t1, "E0"
slExe "CREATE INDEX maPtNum ON MedCondAllgs (maPtNum);", "E0" 'Pt-Num is unique
slExe "End"
MsgBox "MedConAllgs Table Created"
'--------------------------------------------------------------------------------
lPrcF = "PrcNum, PrcDesc, PrcDsal, PrcRound, PrcForm, PrcMin"
t1 = "CREATE TABLE PriceTbls (" & lPrcF & ");"
slExe "Begin" 'This starts the transaction 'Note All Index Fields have Unique Number added
slExe t1, "E0"
slExe "CREATE INDEX PrcNum ON PriceTbls (PrcNum);", "E0"
slExe "CREATE INDEX PrcDesc ON PriceTbls (PrcDesc);", "EO"
slExe "End"
MsgBox "Price Tables Created"
'--------------------------------------------------------------------------------
lPtF = "PtNum, PtName collate NoCase, PtMI, PtDOB, PtSex, PtStreet, PtCity, PtState, PtZip, " & _
"PtPhoneH, PtPhoneC, PtPhoneW, PtLOC, PtSmoke, PtPregC, PtDlvr, PtEZO, PtPrcTbl, PtHippa, " & _
"PtEmail, PtIDQ, PtSSN, PtAltID, PtCSID, PtBnkCrd, PtSigLng, PtPrimCareDrQ, PtPrimCareLN, " & _
"PtPrimCareDrID, PtIns1Act, PtIns1PlanN, PtSpecial, PtIns1HomPl, PtIns1GrpC, PtIns1CrdN, PtIns1EmpID, PtIns1PCode, " & _
"PtIns1CdHldRel, PtIns1CdHldLN, PtIns1CdHldFN, PtIns1EligClrC, " & _
"PtIns2Act, PtIns2PlanN, PtSpecial, PtIns2HomPl, PtIns2GrpC, PtIns2CrdN, PtIns2EmpID, PtIns2PCode, " & _
"PtIns2CdHldRel, PtIns2CdHldLN, PtIns2CdHldFN, PtIns2EligClrC, " & _
"PtRecGrp, PtSpecCond, PtDisc, PtLastDr, PtMsg"
t1 = "CREATE TABLE Patients (" & lPtF & ");"
slExe "Begin" 'This starts the transaction 'Note All Index Fields have Unique Number added
slExe t1, "E0"
slExe "CREATE INDEX PtNum ON Patients (PtNum);", "E0"
slExe "CREATE INDEX PtName ON Patients (PtName);", "E0" 'LName-Fname-PtNum
slExe "CREATE INDEX PtPhone ON Patients (PtPhone);", "E0" 'Phone must be made unique
slExe "End"
MsgBox "Patient Created"
'--------------------------------------------------------------------------------
lRfF = "RfStatus, RfNum, RfInsPlanN, RfFillDt, RfQtyFilled, RfDaysSup, RfAAC, RfTotAmtRcd, RfPtPayAmt, " & _
"RfClmRcnC, RfRPhI, RfTecI, RfClm1InsN, RfClm1Rsp, RfClm1AuthNum, RfInsPay1, " & _
"RfClm2Ins, RfClm2Rsp, RfClm2AuthNum, RfInsPay2, " & _
"RfUseByDt, RfDispStatus, RfDurReason, RfDurProf, RfDurResult, RfDurLevEff"
t1 = "CREATE TABLE Refills (" & lRfF & ");"
slExe "Begin" 'This starts the transaction 'Note All Index Fields have Unique Number added
slExe t1, "E0"
slExe "CREATE INDEX RfNum ON Refills (RfNum);", "E0"
slExe "CREATE INDEX RfDtNum ON Refills (RfFillDt);", "E0" 'RfFillDate-RfNum
slExe "End"
MsgBox "RfRec Table Created"
'--------------------------------------------------------------------------------
lRxF = "RxStatus, RxUpdtC, RxNum, RxPtNum, RxDrNum, RxDgNum, RxSgNum, RxPrcNum, RxTotAmtAuth, RxTotAmtAuth2, " & _
"RxTolAmtDisp, RxLblCnt, RxPayLock, RxDAW, RxDtWrtn, RxSig1, RxSig2, RxSig3, RxSigX, RxPAuthQ, RxAuth, " & _
"RxBtyp, RxOrgnC, RxOthrCovC, RxSchdRxID, RxMsg"
t1 = "CREATE TABLE RxRecs (" & lRxF & ");"
slExe "Begin" 'This starts the transaction
slExe t1, "E0"
slExe "CREATE INDEX RxNum ON RxRecs (RxNum);", "E0" 'Unique
slExe "CREATE INDEX RxPtNum ON RxRecs (RxPtNum);", "E0" 'Unique
slExe "CREATE INDEX RxDtNum ON RxRecs (RxDtNum);", "E0" 'Unique
slExe "CREATE INDEX RxDgNum ON RxRecs (RxDgNum);", "E0" 'Unique
slExe "End"
MsgBox "RxRex Table Created"
'--------------------------------------------------------------------------------
lSgF = "SgNum, SgCode, SgLine1, Sgline2, SgLine3, " & _
"SgLine1a, Sgline2a, SgLine3a, " & _
"SgLine1b, Sgline2b, SgLine3b, " & _
"SgLine1c, Sgline2c, SgLine3c, "
t1 = "CREATE TABLE SigRecs (" & lSgF & ");"
slExe "Begin" 'This starts the transaction
slExe t1, "E0"
slExe "CREATE INDEX SgNum ON SigRecs (SgNum);", "E0" 'Unique
slExe "CREATE INDEX SgCode ON SigRecs (SgCode);", "E0" 'Unique
slExe "End"
MsgBox "Sig Table Created"
'--------------------------------------------------------------------------------
lZipF = "ZipCode, ZipCity, ZipSate"
t1 = "CREATE TABLE ZipRecs (" & lZipF & ");"
slExe "Begin" 'This starts the transaction
slExe t1, "E0"
slExe "CREATE INDEX ZipCode ON ZipRecs (ZipCode);", "E0" 'Unique
slExe "End"
MsgBox "ZipCode Table Created"
'--------------------------------------------------------------------------------
' lNRecF = "LastDgN, LastDrN, LastPtN, LastSgN, LastPrcN, LastInsN, LastDgMsgN"
'
' t1 = "CREATE TABLE LastNumRec (" & lNRecF & ");"
' slExe "Begin" 'This starts the transaction
' slExe t1, "E0"
' slExe "CREATE INDEX Lnum ON LastNumRec (ROWID);", "E0" 'No index Required just 1 record
' slExe "End"
'--------------------------------------------------------------------------------
slClose
End Function
[\code]
I have attached a small FF3 project that demonstrates the problem. Run the program the look at the database with SQLiteExpert. You will find that the "Patients" table is
missing.
I'll look over it tonight, Marty.
Here is how I would handle such a problem.... I would create the database with all of its indexes, etc using a data manager program like SQLiteExpert. I would then ship that database with my application and use it as a template anytime that I needed to have a new database created. You would do that by simply copying the template database to the name of the new database. Doing it this way takes all of the tedious details (and extra code) out of your application.
(1) Create database using SQLiteExpert.
(2) Save the database in the application's folder (e.g. dbtemplate.bin)
(3) Copy the database when needed (e.g. FILECOPY "dbtemplate.bin", "ApotheSQL.db3")
Simple, Easy, and way less complicated.
This is the approach that I am using with the accounting application that i am writing. Whenever I create new company files, i simply copy and rename the company database based on an empty template database.
Rolf, Thanks. I would appreciate that. I am very curous as to why this code fails. I sure can't see any errors in the code. Must be something I am over looking.
Paul, The reason I am using this approach to to better understand the workings of SQLitening/SQLite. When I get to production, I think your method will be the way I will handle it.
Hi Marty,
check line 4 and line 6 of your var 'lPtF'. You have the field 'PtSpecial' defined twice.
See SQLite error attached.
hey everybody:
sqlite expert is 50% off until new years with this coupon HOLIDAY2009
paste in the coupon code box
ho ho ho
pb
Quote from: Rolf Brandt on December 23, 2009, 06:03:00 PM
Hi Marty,
check line 4 and line 6 of your var 'lPtF'. You have the field 'PtSpecial' defined twice.
See SQLite error attached.
Rolf, Thanks... How did you turn on that error checking?
The simple way:
slExe t1 ', "E0"
The program will show the error and terminate.
Better:
slExe t1, "E0"
lErr = slGetError
if val(lErr) then
MsgBox(lErr)
end if
This shows the error, program continues.