PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:4.0,5.0,6.0
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Enterprise Edition for Windows, versions 4.0,
5.0, 6.0
---------------------------------------------------------------------

SUMMARY
=======

By default, the SQL Server ODBC driver automatically closes cursor after a
call to commit or rollback. If you reference the RDO resultset afterwards,
for example, rs.MoveNext, Debug.Print rs(0), the following error occurs:

Run-time error '40088':
No open cursor or cursor closed.

This article demonstrates, in Visual Basic 6.0, how you can keep the cursor
open by setting a driver-specific statement option, using the
SQLSetConnectOption API before establishing the connection. This option is
documented in the SQL Server ODBC Driver Help file, which you can also
obtain when installing the SQL Server Books Online.

However, this approach does not work in Visual Basic 5.0 using RDO
(Msrdo20.dll version 5.xx.xxxx) due to a known RDO bug. To reference the
resultset after the transaction, you must either Requery the resultset or
use the Server-side cursor driver and the rdExecDirect option of the
connection object.

In Visual Basic 4.0, the resultset remains open after transaction.

MORE INFORMATION
================

Visual Basic 6.0 Step-by-Step Example
-------------------------------------

1. Start a new project in Visual Basic and choose "Standard EXE." Form1
is created by default.

2. On the Project menu, click References, and then select Microsoft Remote
Data Object 2.0.


3. Add a CommandButton to Form1.

4. Paste the following code in the General Declaration section of Form1:

Option Explicit

Const SQL_PRESERVE_CURSORS As Long = 1204
Const SQL_PC_ON As Long = 1
Private Declare Function SQLSetConnectOption Lib "odbc32.dll" (ByVal
hdbc&, ByVal fOption%, ByVal vParam As Any) As Integer
Dim WithEvents cn As rdoConnection
Dim rs As rdoResultset

Private Sub cn_BeforeConnect(ConnectString As String, Prompt As
Variant)
Dim intRet As Integer
intRet = SQLSetConnectOption(cn.hdbc, SQL_PRESERVE_CURSORS,
SQL_PC_ON)
End Sub

Private Sub Command1_Click()
Dim strConnect As String
Set cn = New rdoConnection
strConnect = "DRIVER={SQL
Server};SERVER=MyServer;DATABASE=pubs;UID=sa;PWD="
cn.Connect = strConnect
cn.EstablishConnection
Set rs = cn.OpenResultset("SELECT * FROM authors", rdOpenKeyset,
rdConcurValues)

cn.BeginTrans
rs.Edit
rs(1) = "Vermont"
rs.Update
cn.CommitTrans
Debug.Print rs(1)
End Sub

Visual Basic 5.0 Step-by-Step Example
-------------------------------------

1. Start a new project in Visual Basic and choose "Standard EXE." Form1 is
created by default.

2. On the Project menu, click References, and then select Microsoft Remote
Data Object 2.0.

3. Add a CommandButton to Form1.

4. Paste the following code in the General Declaration section of Form1:

Option Explicit

Dim cn As rdoConnection
Dim rs As rdoResultset

Private Sub Command1_Click()
Dim strConnect As String
Set cn = New rdoConnection
strConnect = "Driver={SQL
Server};Server=yourserver;Database=Pubs;Uid=sa;Pwd=;"
With cn
.CursorDriver = rdUseServer
.Connect = strConnect
.EstablishConnection
End With
Set rs = cn.OpenResultset("Select * from Authors", rdOpenKeyset, _
rdConcurValues)

cn.Execute "Begin Transaction", rdExecDirect
Debug.Print rs(1)
rs.Edit
rs(1) = "Vermont"
rs.Update
cn.Execute "Commit Transaction", rdExecDirect
Debug.Print rs(1)
End Sub

Note that Authors table in SQL Server Pubs database is used here for
testing purposes. You must change your Server, Uid, and Pwd parameters in
the connect string.
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:WINDOWS winnt
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic for Windows Learning, Professional, and
Enterprise Editions, version 6.0
- Microsoft Visual Basic Professional and Enterprise Editions for
Windows, version 5.0
- The DAO SDK
---------------------------------------------------------------------

SUMMARY
=======

The Excel ISAM driver is limited in the sense that it does not dynamically
convert datatypes.

MORE INFORMATION
================

If there is a column in your Excel spreadsheet that contains both text and
numbers, the ISAM will not be able to correctly interpret which datatype it
should be. Please make sure that all the cells in a column are formatted to
be the same datatype. For example, you might have following data in four
columns in an Excel sheet:

male female children teens
11 cc 78 ee
22 xx 33 ff
45 uu 56 oo

If you try to read the data through ISAM driver against the whole sheet,
you will get the null values for first row. If you want to avoid this,
create named ranges; one containing only the header information and another
one containing the data information, such as:

named range 'myRange1' :
male female children teens

named range 'myRange2' :
11 cc 78 ee
22 xx 33 ff
45 uu 56 oo

Now you can connect to Excel and request information only from the
particular named range. However, in one range, one particular column can
contain only one type of data.

Creating a Range
----------------

Highlight the data. From the menu, select Insert->Name->Define->rangename.
Note that the "refers to" box below will refer to your highlighted range;
this should grow and shrink as data is inserted and deleted. To retrieve
your data, use the range name you just created for the table name in your
select statement.

Excel Steps
-----------

1. Create the Excel file, test.XLS, with following data in sheet1:

excel File : test.xls with the following entries:

male female children teens
11 cc 78 ee
22 xx 33 ff
45 uu 56 oo

2. Create the named range, myRange1 and myRange2, in the sheet containing
the appropriate data.

named range : myRange1

male female children teens

named range : myRange2

11 cc 78 ee
22 xx 33 ff
45 uu 56 oo

Visual Basic Steps
------------------

1. Create a new standard EXE project called "DAO_EXCEL."

2. Select References from the project menu and check Microsoft
DAO 3.5 Library.

3. Place a CommandButton on the form.

4. Paste the following code in the form code window:

private Sub Command_click1

im dbtmp As DAO.Database
im tblObj As DAO.TableDef
im rs As DAO.Recordset

et dbtmp = OpenDatabase_
("<complete path>\test.xls", False, True, "Excel 8.0;")

DoEvents

Set rs = dbtmp.OpenRecordset("select * from `myRange2`")
While Not rs.EOF
For x = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(x).Value
Next
rs.MoveNext
Wend
End Sub

Note the reverse apostrophe "`" while specifying the range
name(myrange2).

The following results are as expected:

11
cc
78
ee
22
xx
33
ff
45
uu
56
oo

REFERENCES
==========

Please refer to the documentation for how to connect to Excel through DAO.
Another good reference will be the upcoming ADO->Excel reference.

Additional query words: kbDAO350 kbIISAM kbDatabase kbDSupport kbdse
kbVBp500 kbVBp600
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:6.0
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Learning, Professional, and Enterprise Editions
for Windows, version 6.0
---------------------------------------------------------------------

SUMMARY
=======

When the Microsoft Chart Control 6.0 is databound using the ADO DataControl
or the Data Environment, the first column in the resulting recordset must
be of datatype Text (or a corresponding datatype based on the database in
use.) The Chart control attempts to plot the data in the first column if it
contains non-text values. The result of this can be a Chart that does not
plot at all, or a Chart that contains additional (and unwanted) plots. This
requirement is not discussed in the Visual Basic documentation.

MORE INFORMATION
================

The following example provides an illustration of a Chart that contains an
additional data plot and provides a solution.

Step by Step Example
--------------------

1. Create a new Standard EXE project in Visual Basic. Form1 is created by
default.

2. Choose Components from the Project menu, and check "Microsoft ADO
DataControl 6.0 (OLEDB)" and "Microsoft Chart Control 6.0." Click OK.
These controls are now in the Toolbox.

3. Place an instance of the Chart Control, MSChart1, on Form1.

4. Place an instance of the ADO DataControl, Adodc1, on Form1.

5. For this step, it may be necessary to adjust the path information for
the Biblio.mdb database. Set the ConnectionString property of the ADO
DataControl to:

Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data
Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb

6. Set the RecordSource property of the ADO DataControl to:

SELECT Titles.[Year Published], Count(Titles.Title) AS CountOfTitle
FROM Titles GROUP BY Titles.[Year Published];

7. Set the following properties for the MS Chart Control:

chartType: VtChChartType2dLine
datasource: Adodc1

8. Run the project. You should see a green line that is the actual plot of
the data, and an additional red line that is the Chart Control's
attempt to chart the non-text value of the first column of data.

9. To resolve this problem, it is necessary to convert the first column of
data to text. This can be accomplished by modifying the ADO Control's
RecordSource property. The CStr function will be used for this example.
This may require a different function depending on the type of database
in use. Change the RecordSource property of the ADO DataControl to:

SELECT cstr(Titles.[Year Published]), Count(Titles.Title) AS
CountOfTitle FROM Titles GROUP BY Titles.[Year Published];

10. Run the project. You should now see one Red data plot that represents
the data in the Recordset.

Additional query words: kbDSupport kbDSD kbADO kbCtrl kbVBp kbVBp600
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:6.0
OPER/SYS:WINDOWS
KEYWORDS:kbVBp600 kbADO200 kbcode

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Professional and Enterprise Editions for
Windows, version 6.0
---------------------------------------------------------------------

SUMMARY
=======

The DataReport is a powerful tool and it's easy to build complex reports by
dragging and dropping fields out of the DataEnvironment window. However,
there are times when you may want to bind the DataReport directly to an
ActiveX Data Objects (ADO) recordset rather than to the DataEnvironment.
For example, you may have built a hierarchical query with ADO, or you may
have an n-tier application that receives a recordset from a business
object.

This article helps you understand how to bind a DataReport directly to an
ADO recordset.

MORE INFORMATION
================

First, build a hierarchical query with the DataEnvironment. Next, create a
simple DataReport that is based on your query and bound to the
DataEnvironment.

Use the DataEnvironment to connect to the Northwind database (NWind.mdb)
that is included with Visual Basic by following these steps:

1. Create a new Standard EXE project in Visual Basic.

2. Add a DataEnvironment to that project and rename it deCustomerOrders.

3. Rename the initial connection to cnNWind

3. Set the connection to use the Microsoft.Jet.OLEDB.3.51 OLE DB provider.

4. Locate the Northwind database on your machine.

5. Add a command to the connection and rename it Customers.

6. Set the Customers command to query the Customers table.

7. Add a child command to the Customers command and rename it Orders.

8. Set the Orders command to query the Orders table.

9. Relate the two commands on the CustomerID field on the Relation tab.

10. Add a DataReport to the project and rename it rptCustomerOrders.

11. Set the DataSource property of the DataReport to deCustomerOrders.

12. Set the DataMember property of the DataReport to Customers.

13. Right-click on the DataReport and clear "Show Report Header/Footer".

14. Right-click on the DataReport and clear "Show Page Header/Footer".

15. Right-click on the DataReport and select "Insert Group Header/Footer".

16. Drag the CustomerID and CompanyName fields from the Customers command
in the DataEnvironment onto the Group Header section.

17. Drag the OrderID and OrderDate fields from the Orders command in the
DataEnvironment onto the Detail section.

18. Add a CommandButton to your form.

19. Add the following code to your form:

Private Sub Command1_Click()
rptCustomerOrders.Show
End Sub

20. Run the project, click on the CommandButton and you should see the
report with the customer and order information.

21. To bind the DataReport directly to the hierarchical recordset generated
by the DataEnvironment, add the following code:

Private Sub Form_Load()
Dim intCtrl As Integer
With rptCustomerOrders
Set .DataSource = Nothing
.DataMember = ""
Set .DataSource = deCustomerOrders.rsCustomers
With .Sections("Section2").Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptTextBox Or _
TypeOf .Item(intCtrl) Is RptFunction Then
.Item(intCtrl).DataMember = ""
End If
Next intCtrl
End With
.Show
End With
End Sub

Note: If you omit steps 13 and 14, you need to change "Section2" to
"Section6" in the preceding code.

22. Run the project, and you should see the report with the customer and
order information.


The DataReport uses the DataSource and DataMember properties to find the
top-level command on which the report is based. For example, if you have a
hierarchical query in the DataEnvironment containing Customers, Orders, and
Order Details information but you only want to show the Orders and Order
Details information, then you should set the DataSource property to be the
DataEnvironment, and the DataMember property to be the Orders command.

Each field on the DataReport has two properties that allow the
DataEnvironment to determine what information to show on the report:

- DataMember
- DataField.

Use the DataMember property to select the level of the hierarchy that
contains the information you want to display. Use the DataField property to
select the field you want to display.

For example, the CustomerID field is in both the Customers and the Orders
table. If you want to show the CustomerID field with the rest of the
customer information, set DataMember to Customers. If you want to show the
CustomerID with the rest of the Order information, set DataMember to
Orders.

When you bind directly to a recordset object as shown in step 21, the
DataSource property of the DataReport should be set to the recordset object
and the DataMember property should be set to an empty string. For the
fields on the report, the DataMember property of the top-level recordset
information (customer information in this case) should be set to an empty
string. For information other than that which is in the top-level
recordset (Order information in this case), the DataMember property of the
report TextBoxes should be set to the name of the command (Orders in this
case).
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Professional and Enterprise Editions for
Windows, versions 5.0, 6.0
- ActiveX Data Objects (ADO), version 2.0
---------------------------------------------------------------------

SUMMARY
=======

The ADO Client Cursor Engine allows you to control how it builds the action
queries that update the database according to the changes you make to the
recordset object. This article is designed to help you understand how to
control how ADO performs these updates.

MORE INFORMATION
================

When you open a recordset against the Customers table in the Northwind
database (NWind.MDB) and use a client side cursor, ADO retrieves enough
information about the structure of the table in order to use an action
query to update the table.

An action query is a query that modifies a database and does not return
data. For example, "UPDATE Customers SET CompanyName = 'Acme' WHERE
CustomerID = 17" is an action query.

ADO determines which field, or set of fields, is the primary key and uses
that information to make sure it can find the correct row in the database
to update. If you are going to perform updates with the client cursor
engine, make sure you have a primary key defined in your table. If you
don't, you may accidentally update more rows than you intended.

When you use a client side recordset, ADO exposes a property in the
recordset's Properties collection called "Update Criteria." This property
allows you to control the information in the WHERE clause in the action
query that ADO builds to update the database. The default value for this
property is 2 - adCriteriaUpdCols. By default, ADO will use the primary key
and all fields being updated in the WHERE clause of the action query. For
example:

rsCustomers.CursorLocation = adUseClient
rsCustomers.Open "SELECT * FROM Customers", cnNWind, _
adOpenStatic, adLockOptimistic, adCmdText
rsCustomers.Fields("CompanyName").Value = "Acme"
rsCustomers.Update

will cause ADO to execute the following action query

UPDATE Customers SET CompanyName = 'Acme'
WHERE CustomerID = 'ALFKI' AND CompanyName = 'Alfreds Futterkiste'

The WHERE clause contains information about the primary key and the
original value for the field to update. This ensures that if another user
has modified the value of the CompanyName field to a value other than the
value that ADO originally retrieved, ADO will not update that row and will
raise an error instead.

To change the value of this property, use code similar to the following

rsCustomers.CursorLocation = adUseClient
rsCustomers.Properties("Update Criteria").Value = adCriteriaAllCols
rsCustomers.Open "SELECT * FROM Customers", cnNWind, _
adOpenStatic, adLockOptimistic, adCmdText
rsCustomers.Fields("CompanyName").Value = "Acme"
rsCustomers.Update

This code will cause ADO to include every field in the WHERE clause. You
would use this value for the "Update Criteria" property if you want to make
sure that the update made by the current user will only succeed if no
changes have been made to any fields in that row in the table.

The available constants for this property are as follows:

adCriteriaKey = 0
Uses only the primary key

adCriteriaAllCols = 1
Uses all columns in the recordset

adCriteriaUpdCols = 2 (Default)
Uses only the columns in the recordset that have been modified

adCriteriaTimeStamp = 3
Uses the timestamp column (if available) in the recordset

NOTE: Specifying adCriteriaTimeStamp may actually use adCriteriaAllCols
method to execute the Update if there is not a valid TimeStamp field in the
table. Also, the timestamp field does not need to be in the recordset
itself.
返回

Back to top