I am having problems with this func 1

[PCSAARON]

I am having problems with this function. I call it upon a datagrid "Delete". I am passing in:
DBConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.10.1;DATABASE=TEST;USER=TestUser;PASSWORD=;OPTION=35;"

SelectQuery = "SELECT * FROM SecurityForms WHERE FormID=13 and UserID=152"

When the user pushes delete on the datagrid for the second time, the code locks up the whole software on the "myRS.Source = SelectQuery". This function works GREAT on the first delete, then the second time around it locks tighter than a drum and the program has to be "end tasked".

I am using visual basic v6 and MySQL ODBC 3.51 Driver.

Anyone got any clues? I have never had a problem like this before.

--------------------------------------------------------

Function DeleteRecords(DBConnection As Variant, SelectQuery As Variant)
On Error GoTo Err_DeleteRecords
Dim myConn As New ADODB.Connection
Dim myRS As New ADODB.Recordset

myConn.Open DBConnection
myRS.Source = SelectQuery
Set myRS.ActiveConnection = myConn
myRS.CursorLocation = adUseClient
myRS.Open , , adOpenDynamic, adLockOptimistic

With myRS
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
While Not .EOF
.Delete
.MoveNext
Wend
End If
.Close
End With
myConn.Close
Set myRS.ActiveConnection = Nothing
Exit Function
Err_DeleteRecords:
MsgBox Err.Number & ": " & Err.Description
End Function

 

[PCSAARON]

I found the problem. Very wierd I must say. This coding was called on the DELETE property of the datagrid I was using. It would lock up on the form opening as well. I took the function call out of the datagrid before delete, and called it on the KEYDOWN procedure on the delete key instead and it works like a charm. Must be a "Microshaft" bug.

Aaron

 

[x508]

Aaron

I know this is late, but how did you het your datagrid to work with adUseServer

When I use adUseServer cursor with my connection, the datagrid say "Rowset not bookmarkable", and the .RecordCount val = -1

When I change to adUseClient, it works but it takes about 20 seconds to open the recordset, and the .RecordCount val is right

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[PCSAARON]

x508:

Are you using ADO?

Aaron

 

[x508]

yip

and this is my conn string

dbAse.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.4.33;DATABASE=ucosystems;USER=Louis;PASSWORD=;OPTION=3;"

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[PCSAARON]

Use a datagrid object with a ADODC data source object on the form. Then choose the Adodc1 (or whatever name you choose) as the datasource property of the datagrid on the form. Then via coding, setup the connection (Example):

Me.Adodc1.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.1.1;DATABASE=temp;USER=root;PASSWORD=temp;OPTION=35;"

'------Set ADO Datasource / Columns------
With Me.Adodc1
.RecordSource = "SELECT EmployeeID, EmployeeName FROM Employees WHERE EmployeeID = '" & Me.txtEmpID & "'" ORDER BY EmployeeName DESC"
.Refresh
Me.DataGrid1.Columns(0).Width = 955 'EmployeeID
Me.DataGrid1.Columns(1).Width = 1830 'PunchInDateTime
End With


You can try the option 35 to see if that fixes anything, but I never have that problem of a -1 as a record count this way. I ran into that when I first started with MySQL, but since I use this type of connection, it never fails. Hope this helps.

Aaron

 

[x508]

Thanks PCSAARON

I appreciate your time

*****************************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)