Hi, last night I was data dreaming. Yes, dreaming that all the data in
world was at my disposal with only one key. And that this one key could open
data door imaginable, and that this one key could be used by any 10 year old
Ok, so I woke up, and although it was only a dream, I could not help but
think how the Visual Basic Data Environment Object could evolve into such a
How can you use this super simple data access object? Specially if your
10 years old? Lets find out! This project will have you using 1
Object quickly doing such things as…
- Connecting to SQL Server 7(1 of MANY Databases)
- Viewing a table
- Executing a stored procedure
- Executing a Query on the fly
- Executing a stored procedure with a parameter
- Returning a recordset
- and whatever else I can think of…
Visual Basic 6 Application
SQL 7 Server
set of hands (to pinch yourself, when you start thinking that you’ve got to
now let’s start:
make sure you have a table, or view in your SQL 7 Server that you want to
For those of us that have no data available, open the ‘Query Analyzer’ and
copy-paste & execute the following:
those who did the chart project, this is actually the same table and
if exists (select * from sysobjects where id =
object_id(N’[dbo].[DataTable]’) and OBJECTPROPERTY(id, N’IsUserTable’) =
drop table [dbo].[DataTable]
CREATE TABLE [dbo].[DataTable] (
[Data_Date] [datetime] NOT NULL ,
[Data_Value] [int] NOT NULL ,
[Data_Name] [char] (20) NOT NULL
) ON [PRIMARY]
INSERT [dbo].[DataTable] VALUES (’01/01/1999′, 10, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’02/01/1999′, 15, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’03/01/1999′, 20, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’04/01/1999′, 13, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’05/01/1999′, 7, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’06/01/1999′, 18, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES
(’07/01/1999′, 19, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’08/01/1999′, 24, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’09/01/1999′, 26, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’10/01/1999′, 32, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’11/01/1999′, 38, ‘SuperGuyToy’)
INSERT [dbo].[DataTable] VALUES (’12/01/1999′, 42, ‘SuperGuyToy’)
now that we all have some data in SQL 7, we can open Visual Basic 6 and
getting ready to pinch yourself…
you open visual basic, you’ll have lot’s of projects to choose from like in
worry if you don’t have all of these, all you really need to open is the
"Standard EXE" Project.
the "Standard EXE" project.
now that you have selected the Standard exe project, lets look for the
DataEnvironment Object and add it! First go to your components section:
Component & Data Environment selection:
1-) under components we will look for the
"Controls" Tab on the top of this window and select it, and
for the Microsoft DataGrid control.
2-) Then under components we will look for the
"Designers" Tab on the top of this window and select it, and
select the Data Environment designer.
we will add the Data Environment to the project by selecting it from the
dropdown menu as shown:
Now we have just added the Data Environment to our project!! You’ll notice
it will be listed with your first form:
now that we have selected all the items that we need, Lets place 4 command
buttons on the Form, a textbox, and our DataGrid Control:
is Great! your form is now looking good, and we can start connecting our
Environment object to the SQL Server!!
our Data Environment:
a new VB person is probably thinking something along these lines…
we connect? Do we use a crazy looking connection string? And how do I know
all that connection string gibberish means? What the heck am I going to do?
does this have to be soooo difficult?? This is a nightmare…"
don’t worry, It’s easy to connect your data environment. How? I’ll show
double click on your DataEnvironment:
a window will pop up and show you the items for your Data Environment. Right
click on "Connection1" and select it’s properties:
1-) In the provider properties, select the provider for SQL
Server and click on the next button, this takes you to
2-) In the Connection properties, type in your server name,
your user name, password, and the database that you have your data in.
After words just click to test the connection. Guess what… your
You have just made a connection to SQL SERVER 7!
is the crazy looking connection string? What Happened???
connection string is still in there, but the data environment took care of
details! This is just too easy!
Now what do we do? Well I want to be able to see the data on my
DataTable. To do this I have to give the Data Environment a command, or
command for it.
Adding a command:
To Add a command we simply go back to the Data Environment Window we used
get into the connection1 properties, here we click on the "Add
Notice that command1 is placed below connection1. We will be using this
object to tell the Data Environment what we want to do.
Now right click the new command1 object, and select properties:
Under the command1 properties we will do the following:
|Under database object, select "Table". Under
object name, select your SQL Server Table Name,
you finished you’re command!
That’s it, you have just added a command that tells SQL Server that you
to see the Table "dbo.DataTable".
— and the crowd is silent —
You better pinch yourself, what you still don’t believe it? You say you
to see if it’s for real? You want to see it in action? You want to see the
the Data Grid:
put the command to work, and we’ll use the data grid to view the results.
first thing to do is attach the Data grid to this command, here’s how you do
select the datagrid on your form, the go to the control properties
it. you have attached the command to the data grid! What? You still don’t
data? You want data? Where the heck is the data? How do I see the data?
this is super simple… just run your program, and pinch yourself…
where’s the code? And what do I do with these command buttons? How do I
stored procedures and all that?
first part was to get your feet wet with the Data Environment Object.
be putting in the next part in a few days!
Loera has been goofing around VB for more than 5 years. Has coded for
Atlantic, & Americatel Telecom companies. Currently contracted on a
projects for a small unknown company called Microsoft, and doing a side
for another small pharmaceutical company, which would kill him if it’s name
ever divulged. 😉