Page 1
Page 2
Page 3
Page 4
Now lets look at the code that will
query the SQL server and put the results into a eVB listview
control.
If executeQuery("northwind",
"", "", "SELECT employeeID, LastName,
FirstName, Title FROM Employees") = False
Then
If MsgBox("An error has occured. Would
you like to see it?", vbYesNo, "Error") =
vbYes Then
Form2.refreshMessage
'Form2 shows the error message and call to function refreshMessage
updates the labels with the current errormessage XML doc
node values.
Form2.Show
Form1.Hide
End If
Exit Sub
End If
ListViewCtrl1.ColumnHeaders.Clear
ListViewCtrl1.ColumnHeaders.Add , , "Last Name"
ListViewCtrl1.ColumnHeaders.Add , , "First Name"
ListViewCtrl1.ColumnHeaders.Add , , "Title"
ListViewCtrl1.ListItems.Clear
While Not EOF()
Set objListItem = ListViewCtrl1.ListItems.Add(, , recordSet("FirstName"))
objListItem.SubItems(1) = recordSet("LastName")
objListItem.SubItems(2) = recordSet("Title")
moveNext
Wend
ListViewCtrl1.SelectedItem = ListViewCtrl1.ListItems(1)
The result of the above code looks
like this:
The parameters for the "executeQuery"
function are DSN, user name, password and the SQL query.
Since I am using Windows NT authentication to connect my
gateway to the MS SQL Server 7, I am not supplying any user
name and password. If you use login name and password to
connect to the SQL Server, then supply the values. The DSN
name "northwind" is configured at the gateway
end so that I do not need to know what is the appropriate
DB driver for that. The gateway simply processes the SQL
statement with the specified DSN, login and password and
returns me the result in the XML format.
Let's do another query. When you tap
on the "Show details" button, it calls the following
function:
executeQuery("northwind", "",
"", "SELECT LastName, FirstName, Address,
City, Region, PostalCode, Country, HomePhone FROM Employees
where EmployeeID = " + recordSetAt("employeeID",
ListViewCtrl1.SelectedItem.Index - 1) )
So, the program again connects to the
SQL Sever via the gateway and builds the XML document that
was returned from the server.
Using our XML mechanism, this chunk
of code can display the data:
lblAddress.Caption = recordSet("Address")
lblCity.Caption = recordSet("City")
lblName.Caption = recordSet("LastName") + ",
" + recordSet("FirstName")
lblPhone.Caption = recordSet("HomePhone")
lblState.Caption = recordSet("Region")
lblZip.Caption = recordSet("PostalCode")
from the XML document returned from
the server.
Since the XML document has only one
record, I do not need to call the function BOF, the
row pointer will be automatically set to row no 0.
You can also use this concept for UPDATE,
INSERT or DELETE operation on the server.
Basically, whatever SQL statement you send, the gateway
needs to understand that. You can even call the SQL Server
stored procedures with this concept. And if you want to
go deep, i.e. transaction type facility just change the
ASP script on the server to suit your needs.
So, there we go, we have successfully
created a database application for MS SQL Server using eMbedded
Visual Basic. Before I finish, I have got a simple question
for you though. How many lines of code do you think need
to be changed if you want to change the database from SQL
Server to Oracle or something like that provided that you
get the same "northwind" database on those servers?
Cool, isn't it?
Previous Page