• Welcome, Guest. Please login.
June 22, 2021, 12:09:41 PM


Welcome to the SQLitening support forums!

Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Topics - Gary Stout

I am trying to experiment with a remote database to get my feet wet. I have moved a database file to a web server, I went in and modified the server config file for the web address, but I am not connecting and when I check the log file, I see "Invalid IP Address".
Do I need to include the full path in the config file, such as "http://www.mywebserver.com/test/sql_database.db3"; or just the domain information without the path to the actual database file?

I have been using local databases for a while and things are working great, but I wanted to play with remote data to test for speed.

Thanks in advance,
I have the following snippet which makes changes to the Contribution table based on a first or last name being changed in the Members table. Right now the code takes maybe as long as 7-8 minutes to make all of the updates to Contributions. If I make the change in SQLite Expert, the change is almost immediate, so I am sure that there is a better way to do it...I just don't know the best way to do it.

Thanks for your help,

'Update all contributions with head of house name change
slSel "Select * FROM Contributions WHERE FamilyID = '" & Trim$(CurrentFamilyID$) & "'", %rs1

Do While slGetRow(%rs1)
   slExe "Begin Immediate"
   SQL$ = "UPDATE Contributions SET Last = '" & Trim$(NewLast$) & "', First = '" & Trim$(NewFirst$) & "' WHERE FamilyID = '" & Trim$(CurrentFamilyID$) & "'"
   slExe Sql$
   slExe "End"
I have a table that currently only has around 92 items. I have one column called ListName and I am trying to create various mailing list based on the value in the ListName column. Right now, the values in the ListName column are 'CONCERT', 'MEMBERS' and 'TEST'

My query looks like this:

Select * FROM MailingList WHERE ListName = 'TEST' AND RowID = 2

Currently, there are only 2 records where ListName = 'TEST'. If I do the same query using ListName = 'CONCERT', which are the first 36 items in the table, using RowID works fine, but returns nothing beyond the first 36 items in the table. I have specific reasons for using RowID.
If there is not a simple solution why the above is not working, I may have to add another field to keep count, but I can see sorting issues with that approach.

Thanks for any ideas!
I am not sure if JOIN is my answer, but the description seems to fit.

Basically, I have 2 separate tables...DonatedItems and Members. Both of these tables share a common column called MemberID. I am running a query on DonatedItems, but I need some field/column information from Members that DonatedItems doesn't contain (ie. Address, City,State,Zip).

Can someone tell me if JOIN is what I need and give me a basic idea of how to use it in the above scenario?

I am having some issues trying to count records in SQLitening.
The following query always returns 24 in SQLiteining (total number of all records for FamilyID), but in SQLite Expert, returns 4, which is the correct number (that satisfies the query). The GenFund column is defined as REAL 7,2. I have tried putting just a zero in the single quotes and still get the same results. SQLite Expert gave the result of 4 regardless whether I used '0' ot '0.00'.

SQLitening query:

Test& = Val(slSelStr("SELECT COUNT (*) From Contributions WHERE FamilyID = '100120' and GenFund <> '0.00'"))

I have tried various ways of doing this and none seem to produce the correct answer.

Can anyone see what I might be doing wrong or have a better way to count the results of a query?

You've got Questions? We've got Answers! / Double Loop
October 08, 2012, 04:43:02 PM
Is there any problem retrieving records this way?? This is more or less psuedo code, but this is the scenario...while one loop is retrieving members, the inner loop is calculating annual contributions. So far , I am not retrieving all of the records, but those code is from a Tsunami conversion and complicated and not real clean. Sqlitening is going to simplify greatly, but I am trying to get my test working before removing too much :-)


'Get first member here
slSel "Select * FROM Members WHERE MemberType ='Head of House'", %rs1

Do While slGetRow(%rs1)

        'Get annual contribution records
        slSel "Select * FROM Contributions WHERE FamilyID = '" + Trim$(slFN("FamilyID",%rs1)) + "' and Date between '" + StartDate$ + "' and '" + EndDate$ + "'", %rs2

        Do While slGetRow(%rs2)
              'retrieve contribution records here


Is there a way to work around backing up a database while in remote mode from code that would over ride the Access Denied error.

I have a program/service running in C:\PRS and if I select the backup option in the program and try to copy the database to say C:\PRS_BU, of course, I get the Access Denied error. Is it possible to stop the service from code, do the backup and then restart it? This all being done on a local peer to peer network with one computer acting as the server.

I just rolled out a version of a 5yr old program originally written with Tsunami as the database and now converted to SQLitening. This program is run on a 2 computer peer-to-peer type network. I am using the latest SQLitening version. The customer is complaining of a "Locked database error" and then "supposedly" both computers locked up. I am going in the morning to take a look, but is there anything in particular that I should look for or be checking for?
I have been working on this conversion for a long time now and all of my testing has mostly been on a single non-networked computer but I have not received any lock database errors.

Just curious if anyone has any ideas or pointers?

I am still in the process of converting several programs from Tsunami to SQLitening. I am close to putting one of the programs into "normal" use, but in the converting/testing stage, I frequently will do a query and not all records are returned. I can do the same query in SQL Expert and all records are returned and I also found that I can "repair" the database in SQL Expert and my program will then retrieve all records. It makes no sense to me how SQL Expert will retrieve the record but the same query in my program doesn't, but works fine after a repair.
Is there anything that I could be doing wrong that would cause this behaviour?


Update...It appears that newly added records are not being shown in SQL Expert queries either until "repairing" the file. I can view all data in the table with SQL Expert and the records are there (newly added at the bottom of the list), but when I do a query, the newly added records are not returned unless the file is repaired.
Is is possible to have more than one column name after the Distinct command? I need to count the total number of distinct records from the info in several columns. For instance, I need to check distinction between lastname, firstname, address, city and count the total number of different records.

Below is my basic COUNT query without any multiple distinctions.

Recs& = Val(slSelStr("SELECT COUNT(Distinct LastName) from Customer_Data  WHERE LastName ='SMITH' " ORDER BY LastName,FirstName"))

What other delimiters, besides chr$(0) will cause problems? I am trying to convert an old Tsunami file and my file converter gets to record 1480 of 63000 and stops with an error saying the table has 87 columns and 89 are trying to be inserted. I have inspected the record for any printed characters, but not seeing anything that looks like it would be a problem. Are there other unprintable characters that could cause problems?

I am getting an -14 error and I am sure it is something I am doing but I haven't been able to solve it. This program is designed for multi-user, local peer to peer. There is a timer running to constantly get the latest transaction ID from the database, so that if 2 users are both creating a new transaction, they both see the same transaction ID# until one completes the transaction. At this time, the timer is constantly getting the latest ID#, so user 2 would get an updated transID# as well as user 1. This is where the problem seems to be, The new ID# is retrieved for both users, but as soon as the ID# textbox is updated, the program stops with error -14.

I have tried adding some delays thinking maybe this was a timing issue, but so far, nothing has helped.

Anyone have any thoughts? Hopefully I have explained the steps well enough for you to get the picture of what I am doing.

How can I trap error #19 on a slBuildInsertUpdate? I have one field that has to be unique and when a user tries to INSERT another record with the same field information, I want to have a MSGBOX pop up with a message instead of the default sqlitening message and then the program exiting.

I still haven't got a good handle on how error trapping in sqlitening works yet.

Can someone tell me what the AS CustomerNilsen refers to in the Select statement? I copied this from a tutorial site, but there was no mention of AS. Is CustomerNilsen a temporary recordset or variable that holds the number of records counted?
I need to determine how many records are being returned in a recordset.

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'

I am having a slight problem with the psuedo code below. After my Select statement, I am going into  If slGetRow(%rs2) THEN to check for the presence of history. If history exists, the I EXIT IF to go into the  Do While slGetRow(%rs2)  to retrieve the records. If no history is found, then a MSGBOX is display to the user. The problem I am having is that if history does exists, the IF statements exits, but the DO LOOP never runs. The only way I can make the code work is close the recordset before exiting the IF and then issue another SELECT before the DO LOOP.

Is this normal or am I doing something wrong?

                      slExe "Begin Immediate" 
                      slSel "Select * FROM POS_Data WHERE PatientID  = '"302248"'", %rs2
                      slExe "End"
                     'check to see if any history exists
                      If slGetRow(%rs2) THEN
                         EXIT If
                         MSGBOX "No transactions were found for this Patient ID"
                         slCloseSet %rs1
                         slCloseSet %rs2
                         EXIT Sub
                      End If
                     'if history exists then get Records   
                      Do While slGetRow(%rs2)                     
                           'get records here and load listview

                      slCloseSet %rs2

I am working on a small utility that will convert an existing Tsunami database into a SQLitening table and I am getting a error 402 "Statement too long/complex". This particular data file has around 130 fields. The last 120 are point of sale items (item num, desc, qty, price) (30 items x 4 fields per). If I rem out items 28,29,30, then the code will compile with no problem. Am I hitting some line or string length issue in the compiler?
Are there any better ways to work around this type of a problem?
Could the 120 pos fields be loaded into a BLOB? If I was able to do this, how hard would it be to load the BLOB and split it back into individual items to be displayed in a listview? I have never used BLOB's, so I am not sure how they work.

I am curious how-to and the best way to handle the following.....

Say 2 users are viewing the same record and user #1 makes some changes. Is there a way to let user #2 know that changes has been made since he or she originally loaded the record. I am not sure if this is a case where a trigger could be used (haven't played with those yet).

My thoughts were to create a timer that checks for changes on a regular basis and then have a label or something appear if changes are detected so that user #2 can refresh their data. If I use this method, is there a SQLitening command that could be used to detect changes for a particular record or would it be based on changes to the entire table?

You've got Questions? We've got Answers! / Create table
February 28, 2011, 12:39:01 PM
I am making my first attempt at creating a table in code and running into a little bit of a problem. In the snippet of code below, when I try to compile, I get a missing declaration error on TransID. If I make one line of code out of the create table statement, it works ok, but I have so many columns, it is going to make it hard to read or exceed the line length. Maybe I am not continuing the line properly.

   slExe "Create Table Test (FamilyID TEXT(6) Not NULL On CONFLICT FAIL, _
                             TransID TEXT(6) Not NULL On CONFLICT FAIL)"

You've got Questions? We've got Answers! / Error Trapping
February 14, 2011, 06:01:05 PM
I am trying to figure out how to override the internal error processing and use my own, but I am not having much luck.

I have tried different ModChars E0,E1,E2, but still not getting things to work. Right now, if an error occurs, the internal error trapping displays a message box and then the program terminates.

Does anyone have a simple error trapping routine?

I assume I need to use the modchars in slOpen, slGetRow, slEXE ???? This is one area I am a bit confused.  ???

I am trying to insert some values into a table, but I am getting an error that is telling me that the column doesn't exist in the table. I have loaded the database in SQL Expert, and verified spelling, any spaces,etc and the column is definitely there. I am not sure what I am missing.  For test purposes, I have removed all of the other columns except this one particular column from the "vstr" string.

Any thoughts on how to track down what might be happening here?