Add New Record

[Sware]

In the thread "How To Create a Candidate Key?" the following problem was discussed:
- Visual Basic application using ADO
- VFPOLEDB Connection
- Free Table (GHM_V70.DBF) with 87 Fields in Recordsets
- Recordset updates via rs1.Update failed because the
generated Update command is too long
- Can't use Primary Key on Free Tables
- Attempt to establish Candidate Key did not resolve

The problem was resolved by using a direct Update statement (versus rs1.Update) with the following code:

UpdStr = ""
For i = 0 To 86
   UpdStr = UpdStr & Space(1) & rs1.Fields(i).NAME & "=[" & PA(i) & "]" & ","
Next i
UpdStr = Left(UpdStr, Len(UpdStr) - 1) & Space(1)
UpdStr = "UPDATE GHM_V70 SET " & UpdStr & "WHERE GS_ID = [" & PA(0) & "]"
conn1.Execute UpdStr

PA is an array that holds the field values. Because VFP has no knowledge of the array, an initial problem in specifying its use was resolved by using the [ ] characters in the above code for creating the UpdStr string.

I am now trying to add new records to the recordset. Hoping to do so easily I used the following code:

AddStr = "INSERT INTO GHM_V70 FROM ARRAY PA
conn1.Execute AddStr

I get a Syntax Error on the conn1.Execute statement, presumably because VFP has no knowledge of the PA array. Is there (like there was for the Update statemnt) a way to use the array? Or, am I stuck with using the other syntax for INSERT INTO where I have to include all 87 Field names and their Values? Ala:

INSERT INTO dbf_name [(FieldName1 [,FieldName2, ...])]
VALUES (eExpression1 [, eExpression2, ...])

 

[ilyad]

I believe you're stuck with the latest unless you create an XML and try to do your inserts using execscript, something like:

lcStr = "'XMLTOCursor([my xml file],[curInsert],512)' + chr(13)+chr(10)+'insert into myTable select * from curInsert'"

I'm not sure, though, that it would be easier to prepare XML first.

 

[bborissov]

Sware,
What is the result of:

UpdStr = ""
For i = 0 To 86
   UpdStr = UpdStr & Space(1) & rs1.Fields(i).NAME & "=[" & PA(i) & "]" & ","
Next i
UpdStr = Left(UpdStr, Len(UpdStr) - 1) & Space(1)
UpdStr = "UPDATE GHM_V70 SET " & UpdStr & "WHERE GS_ID = [" & PA(0) & "]"
MsgBox UpdStr
MsgBox LEN$(UpdStr)

Also with direct UPDATE I think you should pass the full path to the table:

UpdStr = "UPDATE c:\whatever\GHM_V70 SET ....

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ilyad]

The UPDATE works, as I understood, the same way we can construct an Insert Command.

Note, what you can ommit fields if that element is empty in pa (though may be problems with NULL)

 

[Sware]

Borislav, there is no problem with the Update code. It successfully updates the recordset. What I'm looking for is a way to add new records via INSERT INTO.

 

[Olaf Doschke]

Hi Sware,

glad you opened a new thread.

You don't need to specify all field names and values. You can limit the statement to those fields you want to set and the rest will stay empty.

Also, with the ADODB.Command there are methods to specify parameters, maybe it's possible to define an array parameter.

In general we might look into another alternative in establishing a VFP database with views to the free tables. That could make things much easier and additionally you would be able to use stored procs and call them.

Bye, Olaf.

 

[ilyad]

Hi Olaf,

I think if you don't specify all fields, then depending on SET NULL setting you may have different results (e.g. the system may try to insert NULLs). I'm wondering what is the default for VFPOleDB?

 

[bborissov]

Oops,
I missed the INSERT part
Try:
UpdFields = ""
UpdValues = ""
For i = 0 To 86
if (UpdFields) <> ''
UpdStr = UpdStr & ','
End If
UpdStr = UpdStr & rs1.Fields(i).NAME

if (UpdValues) <> ''
UpdValues = UpdValues & ','
End If
UpdValues = UpdValues & "[" & PA(i) & "]"
Next i
UpdStr = "INSERT INTO GHM_V70 (" & UpdStr & ") VALUES (" & UpdValues & ")"
[/code]

You must have this as a result:

INSERT INTO GHM_V70 (FielName1, FieldName2,..., FieldName87)
VALUES ([value1],[value2], ..., [value87])

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ilyad]

Why did you name it UpdFields and UpdValues if it's an insert? <g>

Just kidding.

 

[Sware]

I got it to work with a variation of the code Iylad provided for the direct Update statement, as follows:

AddStr = "INSERT INTO GHM_V70 ("
For i = 0 To 86
   AddStr = AddStr & rs1.Fields(i).NAME & ", "
Next i
AddStr = Left(AddStr, Len(AddStr) - 2) & ") "
AddStr = AddStr & "VALUES ("
For i = 0 To 86
   AddStr = AddStr & "[" & PA(i) & "]" & ", "
Next i
AddStr = Left(AddStr, Len(AddStr) - 2) & ")"
conn1.Execute AddStr

The result of AddStr must be correct because a new record is added successfully.

Now I have another problem (will it ever end?). I am using ADO's rs1.Delete to delete records. That results in a Multi-Step error. I'll do some VFP reasearch for an alternative way to delete.

 

[bborissov]

yliad,
Just to make sure all values comes to the right fields

Sware,
Delete command is easier:

UpdStr = "DELETE FROM GHM_V70 WHERE GS_ID = [" & PA(0) & "]"

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Olaf Doschke]

delete is also sql: "Delete From GHM_V70 WHERE GS_ID = [" & pa[0] &"]"

Bye, Olaf.

 

[ilyad]

Hi Sware,

I think Borislav's solution is better since it uses only one loop, though 2 variables.

 

[Sware]

Thanks guys - DELETE FROM works like a charm! That one was easy.

 

[Olaf Doschke]

Now you have the ingredients. But with other field types than char fields you need to make sure to prepare the SQL correcty. I'm sure ou figure out numeric fields for yourself, with date fields you can use CTOD([MM/DD/YYY]) or even better {^YYYY-MM-DD}.

Bye, Olaf.

 

[Sware]

Olaf,

Fortunately, all of the fields in our application's .DBFs are character fields. For whatever reason the original Clipper developer chose that approach; he used CTOD and DTOC for date calculations within the program.

With respect to the future you say:

In general we might look into another alternative in establishing a VFP database with views to the free tables.

Can you provide some general guidance on how that might be done.

There's one new situation/problem that I've just begun to explore. When I was using rs1.Update and rs1.AddNew I followed them with closing the recordset and setting it to Nothing, and then reopening it (crude, but I guess I wanted to ensure that I had a fresh recordset with the new data). Now that I'm using the direct UPDATE and INSERT INTO commands I'm getting the following error on rs1.Close:

Error 3219: Operation is not allowed in this context

Any ideas?

 

[Olaf Doschke]

There are just 2 commands needed to set up a database and views:

a) CREATE DATABASE [path/name.dbc]
b) CREATE SQL VIEW viewName AS SELECT * FROM [path/name.dbf]
c) DBSETPROP(...)

You can define the selects as needed. Then you can define some properties of the views (with c), for example you can make the view updatable, make fields updatable, define the primary key field and set up how the sql, which is run behind the scenes is created, especially that the WHERE-clause should only filter for the primary key field.

Then I guess you could create a recordset by rs.open("use viewname") and work with those recordsets the way you are used to. If that does not work a combination of con.execute("USE viewname") and rs=con.execute("setresultset([viewname]") should work as needed.

Bye, Olaf.

 

[Sware]

There's one new situation/problem that I've just begun to explore. When I was using rs1.Update and rs1.AddNew I followed them with closing the recordset and setting it to Nothing, and then reopening it (crude, but I guess I wanted to ensure that I had a fresh recordset with the new data). Now that I'm using the direct UPDATE and INSERT INTO commands I'm getting the following error on rs1.Close:

Error 3219: Operation is not allowed in this context

Situation resolved for now (subject to additional testing). For whatever reason, the error was happening in some cases and not in others. I resolved it by eliminating the rs1.Close statement where the error occurred. I left the Set to Nothing and Reopen without problem.

Call it an "Engineering Solution." I'm still not sure what "in this context" means.