I have a small application that browses the tables of a SQL databases. I have a combo box that lists all the tables, and then based upon the selection, a data grid is populated.
Here is the problem.
I created a new database and a new table with a few new fields. When I try to browse this database, I get an error: "The rowset is not bookmarkable." With some tinkering, I got it to work by removing the adLockOptimistic from my open command:
RS.Open strSQL, conSQL, adOpenStatic, adLockOptimistic.
What will this do? Where can I get a reference for all of these "ad" flags so that I can stop bothering all of you?
Thanks in advance for the help!
Code Confusion : What Does Adlockoptimistic = 3 Mean?
i have this line of code causing problems in my script:
rsrows.Open strSQL, strCn, adOpenDynamic, adLockOptimistic
and gave this explanation:
adlockOptimistic = 3
for this error msg:
the connection cannot be used to perform this operation. it is either closed or invalid in this context
does anyone know why vb would give ( adlockOptimistic = 3 ) as an explanation... why would it have to = 3.
AdOpenStatic Vs AdOpenDynamic
ok, If I'm reading this right, adOpenStatic means you get a copy of whatever is in the data right now. If you keep this copy for 30 minutes without refresh, what you have might easily not be what is in the source table.
adOpenDynamic allows you to see additions and changes as they are completed by other uses.
What is the time hit (in human sense) of using static vs dynamic. Obviously this will depend in great part on the network traffic, but generally speaking, will the end user be able to really see the difference in the data?
AdOpenStatic Vs AdOpenDynamic Vs AdOpenForwardOnly
I recently changed some of my code to use the Open nethod on the recordset rather than the Execute method on the connection.
I noticed that when I used the adOpenStatic cursor type, the result returned is incorrect as opposed to using adForwardOnly or adOpenDynamic.
Below is the code that I use.
sSQL = "SELECT Ord_Committed_Qty, Ord_Unit_Price "
sSQL = sSQL & "FROM Orders5 WHERE "
sSQL = sSQL & "Ord_Order_No = '" & p_OrderNo & "' "
sSQL = sSQL & "AND Ord_Rec_No BETWEEN '01' AND '99' "
Dim rs As New ADODB.Recordset
' Set rs = m_Connection.Execute(sSQL, , 1)
rs.Open sSQL, m_Connection, adOpenDynamic, adLockOptimistic
'dangerous cursor types --result is incorrect most of the time.
' rs.Open sSQL, m_Connection, adOpenStatic, adLockOptimistic
' rs.Open sSQL, m_Connection, adOpenKeyset, adLockOptimistic
Dim cItem As clsOrderItem
Do Until rs.EOF
Set cItem = New clsOrderItem
cItem.QtyCommitted = rs("Ord_Committed_Qty")
cItem.UnitPrice = IIf(rs("Ord_Unit_Price") <> 0, rs("Ord_Unit_Price"), 0.01)
gdTotalInvoiceAmount = gdTotalInvoiceAmount + IIf(rs("Ord_Unit_Price") <> 0, (cItem.UnitPrice * cItem.QtyCommitted), 0)
In my example, record count is 9, during debugging record count shows 9 all the way through the loop, and it explodes to 272 on the last pass.
Does anyone know why?
It works well when using adOpenForwardOnly or adOpenDynamic, so I am not too worried. Should I though, sometimes speed is critical.