Lesson 3: ODBC Tutorials with Visual Basic

Selecting using SQL

Get the appropriate tools to run this example.

(In order to run this program you must have included the Microsoft Data Bound Grid Control 5.0 and the Microsoft RemoteData Control 5.0 in Projects -> Components. Also include the Microsoft Remote Data Object 2.0, Remote Data Bound Grid Control 5.0 , and Microsoft RemoteData Control 5.0 in Projects -> References.)

Note
In order to use the Remote Data Controls you must be using Microsoft Visual Basic Enterprise Edition 5.0.

Bind a MSRDC control, bind text boxes or bind a DBGrid. To bind to a MSRDC control, add the RDO control to your toolbox.

Right-click the Toolbox window and click Components.

Check Microsoft RemoteData Control 2.0 and click OK.

Click Projects and then References.

Check Microsoft Remote Data Object 2.0 and click OK.

Click on the MSRDC control in the Toolbox. Then, drag your cursor in the Form1 window to create an MSRDC control.

In the Connect field of the Properties window, type database=Employees. This field sets the connection string for data access via ODBC and specifies the table to which you want to connect. You do not have to explicitly type ODBC in this field; ODBC is implied by the fact that you did not choose a listed database type. Then in the DataSourceName field, type Pvideo. This field specifies the name of the data source you created. In the SQL field type the following:

Select * from Employees

This extracts all the data from the Employee database.

Alternately, you can bind a DB grid.

Add a DBGrid to the form by clicking Projects and then Components.

Set the DataSource field of the Properties window to the name of the MSRDC control.

Right-click the DBGrid and choose Retrieve Fields. This command redefines DBGrid, using the column names, lengths, and data types already defined for the MSRDC table.

Once you have bound a MSRDC control, text box, or DBGrid, you are ready to select.

Example

Dim Qry As String 
'Build the query 
Qry = "SELECT * FROM Employees" 
Qry = Qry + " WHERE (LastName = '" + txtFind.Text + "')" 
'Refresh the data control 
MSRDC1.SQL = Qry 
MSRDC1.Refresh 
DBGrid1.Refresh

Inserting using SQL

A row may be inserted by calling BINSERT with the row to be inserted in the Data Buffer.

Convert structure to a packed row.

StructToRow custrow.buf, CustFldMap, custrec, 
LenB(keyrec)

Call Btrieve Insert function.

status% = BTRCALL(BINSERT, CustPosBlk$, custrow, _ 
			LenB(custrow), 0, 0, -1)

Verify status.

If status% <> 0 Then 
MsgBox "Error on Insert: " & status% 
End If

Inserting using MSRDC

Adds a record or multiple records to a table.

Bind your text boxes to the MSRDC control

Add a Text Box to the form

Set the DataSource field of the Properties window to the name of the MSRDC control

Set the DataField field of the Properties window to the name of the desired field from the database.

Example

'temporarily hold the text 
text1temp$ = Text1.Text 
text2temp$ = Text2.Text 
text3temp$ = Text3.Text 
'If your text box is bound to a  
'datasource you need this next  
'line of code so that the bound  
'controls know that you have  
'changed the data 
Text1.DataChanged = False 
Text2.DataChanged = False 
Text3.DataChanged = False 
'you should check to see if these  
'initials already exist.

Updating using SQL

The UPDATE statement allows you to modify column values in a database. If you do not want to change all the values in the specified column, you can include a WHERE clause to define which rows in the table to modify.

This example replaces the current last name in the grid with the text in the Text3 text box.

Get the appropriate tools to run this example. See page 5-19, "Get the appropriate tools to run this example."

Bind a MSRDC control, bind text boxes and/or bind a DBGrid.

Dim strSQLChange As String 
Dim qdfChange As rdoQuery 
Dim rstEmployees As rdoResultset 
'If your text box is bound to a  
'datasource you need this next line of  
'code so that the bound controls know  
'that you have changed the data  
Text3.DataChanged = False 
'Replace the last name that is in the grid. 
Last$ = DBGrid1.Columns(2).Text 
'Define a SQL statements for action queries. 
strSQLChange = "UPDATE Employees SET LastName = " & _ 
"'" + Text3.Text + "' WHERE LastName = '" + Last$ + "'" 
Set qdfChange = MSRDC1.Connection.CreateQuery("", 
strSQLChange) 
Set rstEmployees = MSRDC1.Connection.OpenResultset( _ 
  "SELECT * FROM Employees", _ 
  dbOpenForwardOnly) 
qdfChange.Execute 
rstEmployees.Requery 
MSRDC1.Refresh 
rstEmployees.Close 
DBGrid1.Refresh 
'Add a new record 
MSRDC1.Resultset.AddNew 
Text1.Text = text1temp$ 
Text2.Text = text2temp$ 
Text3.Text = text3temp$ 
'Update the Resultset 
 MSRDC1.Resultset.Update

Updating using MSRDC

After you update your data, execute this code to apply the changes.

Example

MSRDC1.UpdateRow

Using a Parameterized query

The rdoQuery object is used to execute SQL queries with one or more parameters in the WHERE clause. The rdoQuery object manages the parameters for each execution. This is useful when executing queries that are run frequently. A parameter query simply substitutes user-supplied or application-supplied parameters into an ordinary query.

Using a Parameterized insert statement

Get the appropriate tools to run this example. See page 5-19, "Get the appropriate tools to run this example."

Bind a MSRDC control, bind text boxes or/ and bind a DBGrid. See page 5-20, "Bind a MSRDC control, bind text boxes or bind a DBGrid. To bind to a MSRDC control, add the RDO control to your toolbox."

Enter the following code:

Dim Qry As String 
Dim qdfChange As rdoQuery 
Dim rstEmployees As rdoResultset 
'If your text box is bound to a  
'datasource you need this next line of  
'code so that the bound controls know  
'that you have changed the data. 
Text1.DataChanged = False 
Text2.DataChanged = False 
Text3.DataChanged = False 
'Build the query. 
'Put in question marks as place holders  
'for the parameters. 
Qry = "INSERT INTO Employees(Initials, LastName, 
FirstName)" 
Qry = Qry + " VALUES (?, ?, ?)" 
'You can give the query a name so that  
'you can access it again, but when you  
'put "" it is not added to the Query  
'collection. If you put a name there it  
'will 'be added to the Query collection. 
Set qdfChange = MSRDC1.Connection.CreateQuery("", Qry) 
'Set the parameters. 
qdfChange.rdoParameters(0) = Text1.Text 
qdfChange.rdoParameters(1) = Text2.Text 
qdfChange.rdoParameters(2) = Text3.Text 
Set rstEmployees = MSRDC1.Connection.OpenResultset( _ 
  "SELECT * FROM Employees", _ 
  dbOpenForwardOnly) 
'Execute the SQL statement. 
qdfChange.Execute 
'Refresh the data control. 
MSRDC1.Refresh 
rstEmployees.Close 
DBGrid1.Refresh

Using a Parameterized update statement

Get the appropriate tools to run this example. See page 5-19, "Get the appropriate tools to run this example."

Bind a MSRDC control, bind text boxes or/ and bind a DBGrid. See page 5-20, "Bind a MSRDC control, bind text boxes or bind a DBGrid. To bind to a MSRDC control, add the RDO control to your toolbox."

Enter the following code:

Dim strSQLChange As String 
Dim qdfChange As rdoQuery 
Dim rstEmployees As rdoResultset 
'If your text box is bound to a  
'datasource you need this next line of  
'code so that the bound controls know  
'that you have changed the data. 
Text3.DataChanged = False 
'The 3rd column in the DBGrid is the  
'LastName Column. This is the search  
'criteria for the Update statement. 
Last$ = DBGrid1.Columns(2).Text 
'Define a SQL statements for action  
'queries. 
'This statement replaces the lastname  
'in the grid with the text in the  
'Text3  Put in question marks as place 
'holders for the parameters. 
strSQLChange = "UPDATE Employees SET LastName = " & _ 
  "? WHERE LastName = '" + Last$ + "'" 
'You can give the query a name so that  
'you can access it again, but when you  
'put "" it is not added to the collection. 
Set qdfChange = MSRDC1.Connection.CreateQuery("", 
strSQLChange) 
'Set the parameter 
'This replaces the ? in the Update statement 
qdfChange.rdoParameters(0) = Text3.Text 
Set rstEmployees = MSRDC1.Connection.OpenResultset( _ 
  "SELECT * FROM Employees", _ 
  dbOpenForwardOnly) 
'Execute the SQL statement with the parameter. 
qdfChange.Execute 
MSRDC1.Refresh 
rstEmployees.Close