ADO update

[hmcheung]

Hi,

I'm using VB6. I used the following code to connect to a foxpro dbf using ADO. I've added a new column "Status" to it using SQL, and the initial value of the whole column is .NULL. . I tried to update this column row by row but I failed. It turns out that I get an error. I found out that when I do the first update the whole column (rather than only one cell) is updated. So when it comes to the 2nd update, an error occurs because the cursor is already at the bottom and can't movenext anymore.

so how can I make it so that it won't update the whole column at one time? thank you very much.



the code:
-----------------------------------------------------------
Private Sub cmdUpdateDBF_Click()

Dim m As Integer 'for looping

Set cnMain = New ADODB.Connection
strconnect = "Driver={Microsoft Visual FoxPro Driver}; SourceType=DBF; SourceDB=" & path_name
cnMain.Open strconnect

cnMain.Execute "alter table " & table_name & " add column Status char(20)"

Set rsMain = New ADODB.Recordset
rsMain.Open "select " & "Status" & " from " & arrayDBFPath(1), cnMain, adOpenStatic, adLockOptimistic

rsMain.MoveFirst
rsMain("Status")= "status A"
rsMain.Update '1st update

rsMain.MoveNext
rsMain("Status")= "status B"
rsMain.Update '2nd update

rsMain.MoveNext
rsMain("Status")= "status C"
rsMain.Update '3rd update

end sub

[Chris]

May be you can try to use the

UPDATE TblName SET <Field Name> WHERE <Field Name> = <Your Criteria>;"

[hmcheung]

well if I use the SQL, my code would be something like:

cnMain.execute "UPDATE table_name SET Status WHERE Status=MyCriteria"

but I've got questions:

1. should I use the execute method for the connection object cnMain or for the recordset object rsMain?

2. How should I write the criteria? suppose I want the Status field of the
first row = "Status A"
second row = "Status B"
third row = "Status C"

3. How about if the status are from another object in the form? for example I've got an MSFlexGrid1, and I'd like the Status field of the
first row = MSFlexGrid1.textMatrix(1,0)
second row = MSFlexGrid1.textMatrix(2,0)
third row = MSFlexGrid1.textMatrix(3,0)
and so on?

Thank you very much for your reply!

[JasonS]

I don't know much about Visual FoxPro but I thought when you added a field to a table you could assign whether or not to allow nulls.
Maybe that's why you get the error because the field cannot be null.

[hmcheung]

ic..
when the field "Status" is first added, the value for the whole column is .NULL., but all the values that I append to this column are not .NULL. so what should be the connection.execute statement be in order to set the field to something other than .NULL.?
thanks..

[JasonS]

You can use the NULL | NOT NULL to allow/preclude null values. Try this...

"alter table " & table_name & " add column Status char(20) NULL"

If the modification has already been made you could modify the structure of the table in VFP.

[hmcheung]

Hello,

I've tried this

"alter table " & table_name & " add column Status char(20) NULL"

it is the same as

"alter table " & table_name & " add column Status char(20)"

i've also tried

"alter table " & table_name & " add column Status char(20) NOT NULL"

but it returns an error saying that can't add NULL field to a foxpro database.

thanks.