• Welcome, Guest. Please login.
 
September 17, 2019, 10:24:52 am

News:

Welcome to the SQLitening support forums!


Another question about the AdBook example project

Started by Marty Francom, November 22, 2009, 03:18:32 pm

Previous topic - Next topic

Marty Francom

November 22, 2009, 03:18:32 pm Last Edit: November 22, 2009, 03:37:11 pm by Marty Francom
Fred looking at the  AdBook project.    If I want to index using 2 fields   How do I do that?

In the AdBook project I have replaced the "Name" field with   FName and LName  fields.   This is how I change the Create Statement:



   slExe "Begin"  'This starts the transaction

      slExe  "Create Table Customers (Company collate NoCase,  NCPDP, UserCount, FName, LName, Add1, Add2, " & _
        "City, State, Zip, Email, Phone, ax, Cell, NPI, Optional, AExp, LExp, OExp, ALic, LLic, OLic, Note, " & _
        "Unique(Company, NCPDP, FName, LName, State, AExp, Lexp, OExp))"   
      slExe "CREATE INDEX NCPDP ON Customers (NCPDP)"
      slExe "CREATE INDEX Company ON Customers  (Company)"
      slExe "CREATE INDEX AExp ON Customers (AExp)"
      slExe "CREATE INDEX LExp ON Customers (LExp)"
      slExe "CREATE INDEX OExp ON Customers (OExp)"
      slExe "CREATE INDEX State ON Customers (State)"
      slEXE "CREATE INDEX Name ON Customers (LName JOIN FName)
   slExe "End"


Did I do that correctly?   By using the JOIN     

When I run the program I get a syntax error  on the  JOIN   so I must not be doing this correctly.
How should this be done?
 

Fred Meier

Join is only uesd in Select statments.  Just seperate the column names with a comma.

slEXE "Create Index Name On Customers (LName, FName)

see doc at http://www.sqlite.org/lang_createindex.html

Marty Francom

Quote from: Fred Meier on November 22, 2009, 03:33:22 pm
Join is only uesd in Select statments.  Just seperate the column names with a comma.

slEXE "Create Index Name On Customers (LName, FName)

see doc at http://www.sqlite.org/lang_createindex.html


Thanks... I should of thought of that. 

Marty Francom

November 22, 2009, 09:02:30 pm #3 Last Edit: November 22, 2009, 09:12:09 pm by Marty Francom
I deleted the database to create a new one.  Program starts but when I try to add a record I get and error message:

    1 = No Such Table: Customers

Did I do something wrong in creating the Table?

Fred Meier

All positive error number are returned from SQLite while all negitive error numbers are returned from SQLitening.

Error # 1 is returned from SQLite.   SQLite is reporting that "Customers" is not a table in the current database.

Marty Francom

Quote from: Fred Meier on November 23, 2009, 02:52:09 pm
All positive error number are returned from SQLite while all negitive error numbers are returned from SQLitening.

Error # 1 is returned from SQLite.   SQLite is reporting that "Customers" is not a table in the current database.


Yes, I understand that is what SQLite is reporting.  What I don't understand is why is it reporting that.  I cannot see anything the matter with the code.  Can you?



Function CreateDB()As Long
   Local lErr       As String
   Local t1       As String
   
   slOpen "AdBook.sld", "CE0"
     
   lErr = slGetError
   If Val(lErr) Then
      MsgBox lErr, 0, "Database Error"
      Function = Val(lErr)
      Exit Function
   End If
       
   'We are doing a transaction, sending several SQL statements to the server
   'Build tables and indexes
   t1 = "Create Table Customers (Company collate NoCase,  NCPDP, UserCount, FName, LName, Add1, Add2, " & _
        "City, State, Zip, Email, Phone, Fax, Cell, NPI, Optional, AExp, LExp, OExp, ALic, LLic, OLic, Note, " & _
        "Unique(Company, NCPDP, FName, LName, State, AExp, Lexp, OExp));"   

   slExe "Begin"  'This starts the transaction
      slExe t1
      slExe "CREATE INDEX NCPDP ON Customers (NCPDP)"
      slExe "CREATE INDEX Company ON Customers (Company)"
      slExe "CREATE INDEX AExp ON Customers (AExp)"
      slExe "CREATE INDEX LExp ON Customers (LExp)"
      slExe "CREATE INDEX OExp ON Customers (OExp)"
      slExe "CREATE INDEX State ON Customers (State)"
      slEXE "CREATE INDEX Name ON Customers (LName, FName)"
   slExe "End"
   
   slClose
End Function

Function RecNewSave() As Long
   Local t       As String 
   Local tmp     As String 
   Local un      As String 
   Dim stDate    As SYSTEMTIME
   Local fStr    As String 
   
   fStr = "Company, NCPDP, UserCount, FName, LName, Add1, Add2, " & _
        "City, State, Zip, Email, Phone, Fax, Cell, NPI, Optional, AExp, LExp, OExp, ALic, LLic, OLic, Note"


   'Prepare fields data string           
t = FF_TextBox_GetText (HWND_FORM1_TEXT2) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT3) &  $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT4) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT5) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT6) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT7) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT17) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT8) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT9) &  $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT10) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT11) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT12) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT13) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT14) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT15) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT16) & $Nul
   
   DateTime_GetSystemtime(HWND_FORM1_DATETIMEPICKER1, stDate)                               
   t = t & Format$(stDate.wYear) & Format$(stDate.wMonth, "00") & Format$(stDate.wDay, "00") & $Nul
   DateTime_GetSystemtime(HWND_FORM1_DATETIMEPICKER2, stDate)
   t = t & Format$(stDate.wYear) & Format$(stDate.wMonth, "00") & Format$(stDate.wDay, "00") & $Nul
   DateTime_GetSystemtime(HWND_FORM1_DATETIMEPICKER3, stDate)
   t = t & Format$(stDate.wYear) & Format$(stDate.wMonth, "00") & Format$(stDate.wDay, "00") & $Nul

   t = t & _     
      Format$(FF_Control_GetCheck (HWND_FORM1_CHECK6)) & $Nul & _
      Format$(FF_Control_GetCheck (HWND_FORM1_CHECK7)) & $Nul & _
      Format$(FF_Control_GetCheck (HWND_FORM1_CHECK8)) & $Nul & _
      FF_TextBox_GetText (HWND_FORM1_TEXT20)     

   slExe slBuildInsertOrUpdate("Customers", t, fStr)
   
   
End Function



I delete the old database before running the program and can see that the database is created.  It's just when I try to add the first record that I get this message.  I can't see anything wrong.   

I have attached the FF project file if you would like to  take a look at that.

Rolf Brandt

I'm gonna have a look over it this afternoon.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Rolf Brandt

Hi Marty,

took me a while to find out what is going on. The solution is very simple.

Check the adbook.ini file. It still has as ip the adress of my server. You will also find in it the line:

dbcreate=1

This means ADBook.exe has created the database und will not attempt again to create one.

Easy solution; delete the adbook.ini file. Start adbook new. ADBook will create ADBook.sld and work locally on your computer.

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Marty Francom

Rolf,
   Are you sure?  The INI file that is in the ZIp file contains only:

[Settings]
dbcreate=1
;ip=


I know I can create a new database this way and have done so.  That's not the problem.  The problem is I can not add records to the database.


Rolf Brandt

Worked fine here, Marty. Records were added.

Did you try to search for the record afterwards? Or did you get an error all the time?

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Marty Francom

Quote from: Rolf Brandt on November 24, 2009, 03:54:10 pm
Worked fine here, Marty. Records were added.

Did you try to search for the record afterwards? Or did you get an error all the time?

Rolf


Wow. That does confuse me.  This is with the project I had attached?  You made no other modifications? 

You are running the project I attached in the message above?   Are you sure that you are not running an eariler version?   I ask that because you said the INI file you had still had your IP address in it.  And the INI file that is supplied in the ZIP above does not.   I can not help but think that you are not running the program that was supplied in the attached ZIP above.

Can anyone else on this board run the AdBook project attached.  Here and see if they get Rolf results or if they get my results.  I can't understand would we can get totally different results running the same program.  Only thing I can think of is that we are not running the same program.

I reattached the project  here:

Paul Squires

I wonder if you check to see if the table exists prior to trying to create it again. You could do this by wrapping your create code in the following:

   slSel "Pragma table_info(Customers)"
   if slGetRow(0, "C") = 0 then

I guess you could also set your error conditions to check for an error returned from the create call (rather than allowing SQLitening to display the error messagebox).



Function CreateDB()As Long
   Local lErr       As String
   Local t1       As String
   
   slOpen "AdBook.sld", "CE0"
     
   lErr = slGetError
   If Val(lErr) Then
      MsgBox lErr, 0, "Database Error"
      Function = Val(lErr)
      Exit Function
   End If
       
   'We are doing a transaction, sending several SQL statements to the server
   'Build tables and indexes only of the table does not exist

   slSel "Pragma table_info(Customers)"
   if slGetRow(0, "C") = 0 then

      t1 = "Create Table Customers (Company collate NoCase,  NCPDP, UserCount, FName, LName, Add1, Add2, " & _
        "City, State, Zip, Email, Phone, Fax, Cell, NPI, Optional, AExp, LExp, OExp, ALic, LLic, OLic, Note, " & _
        "Unique(Company, NCPDP, FName, LName, State, AExp, Lexp, OExp));"   

   slExe "Begin"  'This starts the transaction
      slExe t1
      slExe "CREATE INDEX NCPDP ON Customers (NCPDP)"
      slExe "CREATE INDEX Company ON Customers (Company)"
      slExe "CREATE INDEX AExp ON Customers (AExp)"
      slExe "CREATE INDEX LExp ON Customers (LExp)"
      slExe "CREATE INDEX OExp ON Customers (OExp)"
      slExe "CREATE INDEX State ON Customers (State)"
      slEXE "CREATE INDEX Name ON Customers (LName, FName)"
   slExe "End"

   End If
 
   slClose
End Function


Fred Meier

I don't have FF so was not able to look at your code but I was able to
execute it using my debug version of SQLitening. 

The problem is that you are doing a slOpen passing an empty string as the
database name. 

This tells SQLite to create a new temporary database on disk in your temp
folder which of course will have no tables in it.  Thus the error #1 when
you attempt to add a record to the Customers table.