After connecting to a database, no data available.

[computerguy2]

The following code returns -1 in the text box when I do a recordcound, and other errors when I try to update. I'm trying to connect a FoxPro table. Can someone tell me what I have done wrong, and how to correct the problem

Private Sub Command1_Click()


Set conn = New ADODB.Connection
Let conn.ConnectionString = "Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBF;SourceDB=c:\Inventory;Exclusive=No;BackgroundFetch=no;Collate=Machine;Null=Yes;Deleted=Yes;"
conn.Open

Set rs1 = New ADODB.Recordset
rs1.Open "select * from stock", conn, adOpenDynamic, adLockOptimistic

rs1.MoveFirst

Text1.Text = rs1.RecordCount

Text1.Text = rs1.Fields("BarcodeNo&quot

rs1.Fields("qoh&quot = 55

rs1.Update


End Sub

 

[SonOfEmidec1100]

With reference to the recordcount being -1 heres a quote from MSDN "The RecordCount property depends on the capabilities of the provider and the type of cursor. The RecordCount property will return -1 for a forward-only cursor, the actual count for a static or keyset cursor, and either -1 or the actual count for a dynamic cursor, depending on the data source."

Dont know about FoxPro, but the Update would work on SQL server, with the appropriate connection string, even with a dynamic cursor.

Hope this helps.

 

[aata]

set the connections cursorlocation as aduseclient

after

conn.open
conn.CursorLocation=adUseClient

then recordcount of the recordset will give the exact no: of records in the recordset. otherwise, it gives -1

 

[aata]

Set conn = New ADODB.Connection
Let conn.ConnectionString = "Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBF;SourceDB=c:\windows\datadir;Exclusive=No;BackgroundFetch=No;Collate=Machine;Null=Yes;Deleted=Yes;"
conn.Open

hasconnected = True

Set rs1 = New ADODB.Recordset
rs1.Open "select * from rvtable", conn, adOpenDynamic, adLockOptimistic
rs1.Requery
rs1.MoveFirst
text1.text = rs1("rv_name&quot
Do While rs1.EOF = False
msgbox rs1("rv_name&quot
rs1.MoveNext
Loop

this is how you retrieve values from foxpro table

 

[computerguy2]

Hi..The above worked, but when I do a rs1.update I get a run time error.......Statement too long. What do I need to do for an update command?

 

[aata]

i've dealt with only selecting and reading records from foxpro so i don't know exaclty whether this will work. it's actually for oracle database. try it anyway.

sqlstatement = "update mytablename set columnname1 ='" & Trim(txtcode.Text) & "' , columnname2 ='" & Trim(txttype.Text) & "' "
conn.Execute sqlstatement, adCmdText
conn.Execute "commit"


whatever is the command that you need to update a foxpro table, just give in the sqlstatement and try.

 

[computerguy2]

Thanks for everyones input...tereschisty idea worked however the record does not commit until I leave the function, even with a begintrans, and commitTrans. Is there another way to commit the transaction within the function?

 

[aata]

which "sql command" do we use to commit a transaction in foxpro? is there any specific thing? if there is.... try to give it in the execute statement

conn.Execute "..(here)...."

just guessing.

 

[computerguy2]

I still can not write the transaction to the database unless I leave the function. I'm using Visual Fox Pro as my database. Does anyone have any other ideas? Thanks

 

[jebenson]

Well, I created a VFP 6.0 table with fields named BarcodeNo and qoh and ran your code against it - worked fine, no problems. It also worked whether I set the connection's CursorLocation to asUseClient or adUseServer (the default). That said, you might want to try using SQL to update your table:

conn.Execute("Update inventory set qoh=55 where barcodeno=" & text1.text)

Note that the above assumes that barcodeno is actually a numeric field, if it is not you will need to enclose the value in single quotes.

If barcodeno is not unique for each record, you will need to add other criteria to the where clause in order to affect only the record you want.

I actually work quite a bit with VB and VFP tables, and I would recommend using SQL to update your tables - its faster and much more flexible.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[computerguy2]

Thanks....Now how do you do error handeling when using conn.execute("sql statement&quot in vb accessing vfp 6. If this statement fails how do you know? If you do retval = conn.execute ("sql statement&quot you get an error, if you try capturing err or error this dosen't work. Help again would be great.

 

[computerguy2]

How do you retreive success and failures from the SQL code below, and in the second SQL how would I get the QOH value from the SQL.
Thanks again for your help

Set conn = New ADODB.Connection
Let conn.ConnectionString = "Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBF;SourceDB=c:\Inventory;Exclusive=No;BackgroundFetch=no;Collate=Machine;Null=Yes;Deleted=Yes;"
conn.Open

Set rs1 = New ADODB.Recordset
rs1.Open "select * from stock", conn, adOpenDynamic, adLockOptimistic

Set rs2 = New ADODB.Recordset
rs2.Open "Select * from John", conn, adOpenDynamic, adLockOptimistic


rs1.ActiveConnection.BeginTrans
sqlstatement = "update stock set qoh = 33 where trim(barcodeno) = '5190600014' "
conn.Execute sqlstatement, adCmdText
rs1.ActiveConnection.CommitTrans

sqlstatement = "Select qoh from stock where trim(barcodeno) = '5190600014' "
conn.Execute sqlstatement, adCmdText

 

[jebenson]

Here is how I do error handling:

First, put "On Error Resume Next" at the beginning of your sub/function. Then...

rs1.ActiveConnection.BeginTrans
sqlstatement = "update stock set qoh = 33 where trim(barcodeno) = '5190600014' "
conn.Execute sqlstatement, adCmdText

If Err.Number <> 0 then
rs1.ActiveConnection.RollbackTrans
'add any other error handling here
Else
rs1.ActiveConnection.CommitTrans
End If


As for retrieving the QOH value, the SQL you have should work, just use it to open a recordset:

sqlstatement = &quot;Select qoh from stock where trim(barcodeno) = '5190600014'&quot;

rs3.Open sqlstatement, conn, adOpenDynamic, adLockOptimistic

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[computerguy2]

Hi: The above email still does not work. If I put a invalid barcode number in, no error is returned and always goes to CommitTrans. Any other ideas? Thanks...John

 

[jebenson]

Well, inputting an invalid barcode number in the where clause won't generate an error - the transaction will process, its just that there are 0 records affected. To the database connection, that's not an error, it is just 0 rows affected by the update.

However, all is not lost. You can include a parameter in the call to the Execute method that will return the number of rows affected by the command. The format is:

connection.Execute CommandText, RecordsAffected, Options

Here is what MSDN says about this method:

Parameters

CommandText A String containing the SQL statement, table name, stored procedure, or provider-specific text to execute.

RecordsAffected Optional. A Long variable to which the provider returns the number of records that the operation affected.

Options Optional. A Long value that indicates how the provider should evaluate the CommandText argument.


Thus, you could include a Long variable in the RecordsAffected parameter, then check it after the Execute to determine if any records have been affected. You can do it like so:

If Err.Number <> 0 Or RecordsAffected = 0 Then
rs1.ActiveConnection.RollbackTrans
'add any other error handling here
Else
rs1.ActiveConnection.CommitTrans
End If



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson