I have a Visual Basic application that uses the Visual FoxPro Driver to manage .DBF files (tables) created by Clipper. These tables are "free tables" and thus cannot have a Primary Key assigned to them. Can someone provide the code to create a Candidate Key? Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Code to Create a Candidate Key? 1
- Thread starter Sware
- Start date
- Thread starter
- #42
ilyad,
I believe you were involved in the original problem thread. Nevertheless, here's a statement of the problem.
I have a .DBF file (free table) created by Clipper. Each record in the table consists of 87 fields. One of the fields (GS_ID) is unique (not allowed to be duplicated). Using either the ODBC Visual FoxPro Driver or the VFPOLEDB Driver in a Visual Basic application I can access the data, place it in an ADO Recordset (rs1) and populate VB forms with it. The Connection Strings for both drivers are shown in a previous post in this thread.
I process the results of user actions in a string array [PA(0 to 86) As String]. Then, with the following VB code segment I try to update the Recordset:
For i = 0 to 86
If rs1.Fields(i) <> PA(i) Then
rs1.Fields(i) = PA(i)
End If
Next i
rs1.Update
I get the following errors on the rs1.Update statement:
Visual FoxPro Driver: Statement Too Long
VFPOLEDB: Multi-Step operation
I understand that these two errors are equivalent, i.e. caused by the same condition. Apparently, the length of the Update statement generated by the driver from rs1.Update is longer than allowed. A VFP Trace revealed that the WHERE clause of the generated Update statement contains all 87 fields.
Responders to this thread have been suggesting various ways to resolve the situation. Because a Primary Index cannot be placed on free tables it was suggested that a Candidate Index be created with the effect that the WHERE clause would contain only the GS_ID field. Apparently, after many false starts, this index has supposedly been created with your latest approach. However, I continue to get the same error on thhe rs1.Update statement.
Another suggestion was to abandon ADO (the rs1.Update statement) and do a direct conn1.Execute "UPDATE ...... statement. I did that with code explained in a previous post but got a "Missing Operand" error.
--------------------
By the way, how can I verify that the index you say was created was, in fact, created?
I believe you were involved in the original problem thread. Nevertheless, here's a statement of the problem.
I have a .DBF file (free table) created by Clipper. Each record in the table consists of 87 fields. One of the fields (GS_ID) is unique (not allowed to be duplicated). Using either the ODBC Visual FoxPro Driver or the VFPOLEDB Driver in a Visual Basic application I can access the data, place it in an ADO Recordset (rs1) and populate VB forms with it. The Connection Strings for both drivers are shown in a previous post in this thread.
I process the results of user actions in a string array [PA(0 to 86) As String]. Then, with the following VB code segment I try to update the Recordset:
For i = 0 to 86
If rs1.Fields(i) <> PA(i) Then
rs1.Fields(i) = PA(i)
End If
Next i
rs1.Update
I get the following errors on the rs1.Update statement:
Visual FoxPro Driver: Statement Too Long
VFPOLEDB: Multi-Step operation
I understand that these two errors are equivalent, i.e. caused by the same condition. Apparently, the length of the Update statement generated by the driver from rs1.Update is longer than allowed. A VFP Trace revealed that the WHERE clause of the generated Update statement contains all 87 fields.
Responders to this thread have been suggesting various ways to resolve the situation. Because a Primary Index cannot be placed on free tables it was suggested that a Candidate Index be created with the effect that the WHERE clause would contain only the GS_ID field. Apparently, after many false starts, this index has supposedly been created with your latest approach. However, I continue to get the same error on thhe rs1.Update statement.
Another suggestion was to abandon ADO (the rs1.Update statement) and do a direct conn1.Execute "UPDATE ...... statement. I did that with code explained in a previous post but got a "Missing Operand" error.
--------------------
By the way, how can I verify that the index you say was created was, in fact, created?
If FILETOSTR approach works, it means, that we can execute any VFP code through OLEDB. This sounds like a great vulnurability treat, so it's strange.
Do you know the field names in Clipper and their types? I guess, you can get this info by ADO approach.
Anyway, one step at a time. Assuming, filetostr hack works, let's try the following CheckIndexCreated.prg
use [myLongPathName] in 0 alias test
select test
strtofile(iif(tagno(myTagName)>0,'Index Created','Index Not created'),'c:\temp\Info.txt')
oConn.Execute "EXECSCRIPT(filetostr('c:\temp\CheckIndexCreated.prg'))"
If this works, you would have a file created in c:\temp directory called Info.txt with either Index Created or not.
========================================================
But I still have serious doubts it would work. Too simple, to be true.
Do you know the field names in Clipper and their types? I guess, you can get this info by ADO approach.
Anyway, one step at a time. Assuming, filetostr hack works, let's try the following CheckIndexCreated.prg
use [myLongPathName] in 0 alias test
select test
strtofile(iif(tagno(myTagName)>0,'Index Created','Index Not created'),'c:\temp\Info.txt')
oConn.Execute "EXECSCRIPT(filetostr('c:\temp\CheckIndexCreated.prg'))"
If this works, you would have a file created in c:\temp directory called Info.txt with either Index Created or not.
========================================================
But I still have serious doubts it would work. Too simple, to be true.
- Thread starter
- #45
- Thread starter
- #46
dim cUpdateStr as String
For i = 0 to 86
If rs1.Fields(i) <> PA(i) Then
cUpdateStr = cUpdateStr & space(1) & rs1.Fields(i).Name & "=[" & pa(i) & "]"
End If
Next i
cUpdateStr = "UPDATE MyTable SET " & cUpdateStr & _
"WHERE myKeyFld = [" & myKeyFld & "]"
conn.Execute cUpdateStr
(From the top of my head, so you may need to work it out)
=========================================================
I guess "Function is not implemented" was about FILETOSTR. Yes, you should use a folder where you put prg file, but looks like it is not going to work.
BTW, I asked your question on another forum and got an interesting answer (using VB.NET) I'm not sure how can I provide a proper reference.
For i = 0 to 86
If rs1.Fields(i) <> PA(i) Then
cUpdateStr = cUpdateStr & space(1) & rs1.Fields(i).Name & "=[" & pa(i) & "]"
End If
Next i
cUpdateStr = "UPDATE MyTable SET " & cUpdateStr & _
"WHERE myKeyFld = [" & myKeyFld & "]"
conn.Execute cUpdateStr
(From the top of my head, so you may need to work it out)
=========================================================
I guess "Function is not implemented" was about FILETOSTR. Yes, you should use a folder where you put prg file, but looks like it is not going to work.
BTW, I asked your question on another forum and got an interesting answer (using VB.NET) I'm not sure how can I provide a proper reference.
- Thread starter
- #48
Olaf Doschke
Programmer
ilyad,
filetostr(...) is another flaw in the help topics on supported and unsupported functions: It's neither in the one, nor the other list. The error Sware get's seems to be due to filetostr() not working.
Why the single quote within the double quote is needed:
Try Execscript(Select 0) in VFP and see if that works... You need Execscript('Select 0'). Do you see the need now?
Bye, Olaf.
filetostr(...) is another flaw in the help topics on supported and unsupported functions: It's neither in the one, nor the other list. The error Sware get's seems to be due to filetostr() not working.
Why the single quote within the double quote is needed:
Try Execscript(Select 0) in VFP and see if that works... You need Execscript('Select 0'). Do you see the need now?
Bye, Olaf.
Olaf Doschke
Programmer
A simple way to test the existance of the index? Is there a file GHM_V70.CDX now?
For rs1.Update to work, ADO needs to know that GS_ID is a primary key and Where GS_ID = some value is sufficient to identify the record to be updated. You also should only SET fileds=new value, where the field value changed.
Maybe you can solve the problem anyway, by allowing longer SQL, you can allow up to 2040 character of a wheere clause via:
Default is 320 Bytes.
Bye, Olaf.
For rs1.Update to work, ADO needs to know that GS_ID is a primary key and Where GS_ID = some value is sufficient to identify the record to be updated. You also should only SET fileds=new value, where the field value changed.
Maybe you can solve the problem anyway, by allowing longer SQL, you can allow up to 2040 character of a wheere clause via:
Code:
oConn.Execute("SYS(3055, 2040)")Default is 320 Bytes.
Bye, Olaf.
Olaf Doschke
Programmer
I meant
Besides that, only set the fields that changed, eg if field1 and 80 changed: Update tablename set field1='value1', field80='value80' where gs_id = someid
Don't forget the commas.
Bye, Olaf.
Code:
oConn.Execute "SYS(3055, 2040)"Besides that, only set the fields that changed, eg if field1 and 80 changed: Update tablename set field1='value1', field80='value80' where gs_id = someid
Don't forget the commas.
Bye, Olaf.
Here what I use and it works. Unfortunately I could test it only in VFP:
So I suppose in VB it should be:
Change "Data Source" in Connection string to match the path where your data resides and Change USE Test to USE [your actual table name here]
Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
Code:
oAdoConn = CREATEOBJECT([ADODB.Connection])
oAdoConn.ConnectionString = [Provider=vfpoledb.1;Data Source=D:\All_zapl_4_9_0\;Collating Sequence=machine;]
oAdoConn.Open()
oAdoComm = CREATEOBJECT([ADODB.Command])
oAdoComm.ActiveConnection = oAdoConn
oAdoComm.CommandText = [EXECSCRIPT("SELECT 0"+CHR(13)+CHR(10)+"USE Test EXCLUSIVE"+CHR(13)+CHR(10)+"INDEX ON Fld1 TAG Test")]
oAdoComm.Execute()So I suppose in VB it should be:
Code:
Dim oAdoConn AS ADODB.Connection
Dim oAdoComm AS ADODB.Command
oAdoConn = CREATEOBJECT('ADODB.Connection')
oAdoComm = CREATEOBJECT('ADODB.Command')
' or whatever is the VB syntax for creating object
oAdoConn.ConnectionString = [Provider=vfpoledb.1;Data Source=D:\All_zapl_4_9_0\;Collating Sequence=machine;]
oAdoConn.Open()
oAdoComm.ActiveConnection = oAdoConn
oAdoComm.CommandText = 'EXECSCRIPT("SELECT 0"+CHR(13)+CHR(10)+"USE Test EXCLUSIVE"+CHR(13)+CHR(10)+"INDEX ON Fld1 TAG Test")'
oAdoComm.ExecuteChange "Data Source" in Connection string to match the path where your data resides and Change USE Test to USE [your actual table name here]
Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
Hi Borislav,
You always forget, that ' is the comment in VB <g>
Olaf, yes, I figured later that we need to have a string identifier in our string. I was trying to figure out how to include single quote inside the double quote in VB. AFAIK, you can use chr(39), e.g.
cStr = chr(39) & "USE [" & MyPathToTheTable & "] alias test in 0 exclusive " & chr(39) & " + chr(13) + chr(10) + " & chr(39) & "select test" & chr(39) & " + chr(13) + chr(10) + " & chr(39) & "index on myField tag myTag" & chr(39)
You always forget, that ' is the comment in VB <g>
Olaf, yes, I figured later that we need to have a string identifier in our string. I was trying to figure out how to include single quote inside the double quote in VB. AFAIK, you can use chr(39), e.g.
cStr = chr(39) & "USE [" & MyPathToTheTable & "] alias test in 0 exclusive " & chr(39) & " + chr(13) + chr(10) + " & chr(39) & "select test" & chr(39) & " + chr(13) + chr(10) + " & chr(39) & "index on myField tag myTag" & chr(39)
- Thread starter
- #54
- Thread starter
- #55
There IS a GHM_V70.CDX file in the data folder, i.e. the folder that also has GHM_V70.DBF. Therefore, it seems that the Candidate Index was created with ilyad's code. However, despite previously expressed beliefs that the Candidate Index would solve the rs1.Update problem, it doesn't. I still get the Multi-Step error.
When I use Olaf's: conn1.Execute "SYS(3055, 2040)" statement I get the error "Insufficient Stack Space" on the statement: rs1.Open "SELECT * FROM GHM_V70.DBF..........." (i.e. when I open the Recordset).
I am going to experiment further with the direct UPDATE statement later today.
When I use Olaf's: conn1.Execute "SYS(3055, 2040)" statement I get the error "Insufficient Stack Space" on the statement: rs1.Open "SELECT * FROM GHM_V70.DBF..........." (i.e. when I open the Recordset).
I am going to experiment further with the direct UPDATE statement later today.
Olaf Doschke
Programmer
Help on Sys(3055) is a little more precise about what you should configure. Also it's not a byte size, as I thought, but some kind of measurement of complexity. It's recommended to set it to 8*number of fileds, eg 8*87=696. Maybe that is sufficient for the SQL to work and won't cause the stack overflow.
Nevertheless decreasing the complexity of the SQL you will benefit anyway.
Bye, Olaf.
Nevertheless decreasing the complexity of the SQL you will benefit anyway.
Bye, Olaf.
- Thread starter
- #57
EUREKA!!! And, hopefully, AMEN!!!
The Candidate Index approach as implemented doesn't avoid the "Statement Too Long" or "Multi-Step Operation" errors. There is a .CDX file that was created with a Candidate Key specification. But, either Candidate Key doesn't solve the problem or it isn't "activated."
Nobody has come up with what Mike Lewis thinks is a way to fool the ADO Recordset into thinking there's a Primary Key on a free table.
But, with modifications, ilyad's code for constructin a SQL string for a direct Update statement from the Connection has eliminated the errors. Here's the code:
Dim UpdStr As String
For i = 0 To 86
If rs1.Fields(i) <> PA(i) Then
UpdStr = UpdStr & Space(1) & rs1.Fields(i).NAME & "=[" & PA(i) & "]" & ","
End If
Next i
UpdStr = Left(UpdStr, Len(UpdStr) - 1) & Space(1)
UpdStr = "UPDATE GHM_V70 SET " & UpdStr & "WHERE GS_ID = [" & PA(0) & "]"
conn1.Execute UpdStr
The modifications were to add a comma after each segment of the UpdStr creation (line after the If line)and then replace that comma with a Space character at the end of the last segment (line after Next i). Note that use of the PA array values was made possible by inclusion of the [ ] characters to enclose the PA(i) and PA(0) content.
So,now all I have to do is find all the rs1.Update statements in my VB code and replace them with the code like that above.
My thanks to all who have contributed to this very lengthy thread. There were many diversions and false starts but we got to a solution. It reminds me of the saying that if 100 monkeys were put in a room with typewriters (word processors these days), they would eventually type the bible!
Now, if somebody can tell me how to include Code and Quote boxes, and color highlighting like Olaf's, in my posts I can provide more readable posts for future problems I hope I don't have.
The Candidate Index approach as implemented doesn't avoid the "Statement Too Long" or "Multi-Step Operation" errors. There is a .CDX file that was created with a Candidate Key specification. But, either Candidate Key doesn't solve the problem or it isn't "activated."
Nobody has come up with what Mike Lewis thinks is a way to fool the ADO Recordset into thinking there's a Primary Key on a free table.
But, with modifications, ilyad's code for constructin a SQL string for a direct Update statement from the Connection has eliminated the errors. Here's the code:
Dim UpdStr As String
For i = 0 To 86
If rs1.Fields(i) <> PA(i) Then
UpdStr = UpdStr & Space(1) & rs1.Fields(i).NAME & "=[" & PA(i) & "]" & ","
End If
Next i
UpdStr = Left(UpdStr, Len(UpdStr) - 1) & Space(1)
UpdStr = "UPDATE GHM_V70 SET " & UpdStr & "WHERE GS_ID = [" & PA(0) & "]"
conn1.Execute UpdStr
The modifications were to add a comma after each segment of the UpdStr creation (line after the If line)and then replace that comma with a Space character at the end of the last segment (line after Next i). Note that use of the PA array values was made possible by inclusion of the [ ] characters to enclose the PA(i) and PA(0) content.
So,now all I have to do is find all the rs1.Update statements in my VB code and replace them with the code like that above.
My thanks to all who have contributed to this very lengthy thread. There were many diversions and false starts but we got to a solution. It reminds me of the saying that if 100 monkeys were put in a room with typewriters (word processors these days), they would eventually type the bible!
Now, if somebody can tell me how to include Code and Quote boxes, and color highlighting like Olaf's, in my posts I can provide more readable posts for future problems I hope I don't have.
I'm glad you got it working. I was also discussing the problem with Borislav on another forum and found out that my version of VFPOleDB doesn't work for creating an index. However, I was unable to install newer version (installer complained and I abandoned it for now).
When you're writing a reply, look at the bottom. There is a link called Process TGML which explains various tags.
You have to use [ quote ] for quote and [ code ] for code. Unfortunately, there is no color highlighting (would be nice). In SQL Server forum someone (forgot who) posted a SP to do color highlighting.
- Thread starter
- #59
- Thread starter
- #60
- Status
Similar threads
