Database Manager SQL commands

[bagbyci408]

I want to use the SQL INSERT command to partially populate a new installation personal profiles but I always get a syntax error message. Is this command disabled?

Carl

[NeilZ]

I want to use the SQL INSERT command to partially populate a new installation personal profiles but I always get a syntax error message. Is this command disabled?

Carl

I believe it is, as there are programmatic dependencies on the data in the tables.

[bagbyci408]

Thanks Neil. I can modify the table data using SQL but have not been able to append records.

Carl

[NeilZ]

Thanks Neil. I can modify the table data using SQL but have not been able to append records.

Carl

I suspect some table dependencies are there also.

[Jeff]

The insert statement is not disbaled. SQL in VFP sometimes has a slight variation from normal SQL statements. What is the command you are trying to use?

[NeilZ]

The insert statement is not disbaled. SQL in VFP sometimes has a slight variation from normal SQL statements. What is the command you are trying to use?

I was wondering, but since I've not had a good experience when I played around with it, I just figured that there was a few reasons it wouldn't work, one of which it was not enabled, the other was that there were either programmatic, or database dependencies that disallowed my adding a row to a table.

[bagbyci408]

I tried several variations of the following syntax

INSERT INTO ME (Mail_no,Pers_no,Status,Lastname,Firstname,Title,Mephn1_typ,Mephn2_typ,Mephn3_typ,Mephn4_typ,Mephn5_typ,E_mailbulk,E_mailstmt,Adult,Env_no,Marital,Meid)
VALUES
(3,"1","MA","Bagby",”Carl”,"Mr.",1,2,3,4,5,T,T,Y,101,”MM”,0)

I also tried filling all of the fields with blanks. When I attached the VFP table to a MS Access database I was able to successfully attach a record but it complained about null values along the way.

Carl

[Jeff]

Just a couple of problems, pers_no is a numeric field in values you had it in quotes. IN VFP syntax literal logicals are enter .T. or .F. this is a hold over from its dbase roots. The adult field is a string and needed to be quoted.

I also dropped the MEID field, this field is currently not used in version 10.4. We were planning a service that would use this field, but its implementation has been delayed until the launch of version 11.

The other problem I found was that some of the quotes were ASCI 148 not ASCI 34 this was causing a problem. This might have been caused by the html conversion. The difference is ” (ASCI 148) vs. " (ASCI 34)

This insert statement should work.

Code: Select all

INSERT INTO ME (MAIL_NO, PERS_NO, STATUS, LASTNAME, FIRSTNAME, TITLE, MEPHN1_TYP, MEPHN2_TYP, MEPHN3_TYP, MEPHN4_TYP, MEPHN5_TYP, E_MAILBULK, E_MAILSTMT, ADULT, ENV_NO, MARITAL)  VALUES (3, 1, "MA", "BAGBY", "CARL", "MR.", 1, 2, 3, 4, 5, .T., .T., "Y", 101,"MM") 

One side effect about working in the database manager is all statements are converted to upper case. In this example the person's name is made upper case. This is probably not the desired results here.

There is a work around.

In the data base manager type:
Modify Command DoInsert.prg <Enter>

This opens an editing window. Enter you command. When done close the editing window.

Enter:
Compile DoInsert.prg <Enter>
DO DoInsert <Enter>

This compiles and runs the statements in DoInsert.prg.

Hope this helps.

NOTE FOR OTHER READERS: The database manager provides direct access to the PowerChurch data files. Any changes you make here bypass all the built in checks in PowerChurch. It is primarily for PowerChurch Tech Support to help diagnose problems you may be having. PowerChurch Support does not assist customers with commands to use in the database manager.

Obviously Carl knows what he is doing all I was helping him with specificaly why his command was not working.

[bagbyci408]

Jeff,

Thanks. The code works like a charm. I don't know how I overlooked the numerics. Also, I was aware of the caps and can live with them.

Again, thanks.

Carl