Data Access Dreams with VB’s Data Environment! (Part 1)

Download & Install Codec(128
KB) to see Video(1,772
KB) if slow, save video to hard drive first, then run!

Hi, last night I was data dreaming. Yes, dreaming that all the data in
the
world was at my disposal with only one key. And that this one key could open
any
data door imaginable, and that this one key could be used by any 10 year old
out
there…

Ok, so I woke up, and although it was only a dream, I could not help but
to
think how the Visual Basic Data Environment Object could evolve into such a
key…  

How can you use this super simple data access object? Specially if your
only
10 years old? Lets find out! This project will have you using 1
DataEnvironment
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… 

You’ll need:

1
Visual Basic 6 Application

1
SQL 7 Server

1
set of hands (to pinch yourself, when you start thinking that you’ve got to
be
dreaming…)

Great,
now let’s start:

First
make sure you have a table, or view in your SQL 7 Server that you want to
chart.
For those of us that have no data available, open the ‘Query Analyzer’ and
copy-paste & execute the following:

(For
those who did the chart project, this is actually the same table and
data)

—Start
SQL commands—

use
pubs

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’)

—End
SQL commands—

Ok,
now that we all have some data in SQL 7, we can open Visual Basic 6 and
start
getting ready to pinch yourself…

Visual
Basic 6:

When
you open visual basic, you’ll have lot’s of projects to choose from like in
the
picture below:

Don’t
worry if you don’t have all of these, all you really need to open is the
"Standard EXE" Project.

• Open
  the "Standard EXE" project.

Ok,
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
look
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.

Lastly
we will add the Data Environment to the project by selecting it from the
Project
dropdown menu as shown:

Woohoo!!
Now we have just added the Data Environment to our project!! You’ll notice
that
it will be listed with your first form:

Ok,
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:

This
is Great! your form is now looking good, and we can start connecting our
Data
Environment object to the SQL Server!!

Connecting
our Data Environment:

Here
a new VB person is probably thinking something along these lines…
"How do
we connect? Do we use a crazy looking connection string? And how do I know
what
all that connection string gibberish means? What the heck am I going to do?
Why
does this have to be soooo difficult?? This is a nightmare…"

But
don’t worry, It’s easy to connect your data environment. How? I’ll show
you!

First
double click on your DataEnvironment:

Here
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
"Connection"

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
finished!

You have just made a connection to SQL SERVER 7!

Where
is the crazy looking connection string? What Happened???

The
connection string is still in there, but the data environment took care of
the
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
create a
command for it.

Adding a command:

To Add a command we simply go back to the Data Environment Window we used
to
get into the connection1 properties, here we click on the "Add
command" button:

Notice that command1 is placed below connection1. We will be using this
new
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, "dbo.DataTable", you finished you’re command!

That’s it, you have just added a command that tells SQL Server that you
want
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
want
to see if it’s for real? You want to see it in action? You want to see the
Data?
Well…   ok.

Binding
the Data Grid:

Let’s
put the command to work, and we’ll use the data grid to view the results.
The
first thing to do is attach the Data grid to this command, here’s how you do
it:

First
select the datagrid on your form, the go to the control properties
window:

First, Under DataSource, select "DataEnvironment1" Second, under DataMember, select "Command1", that’s it you are done!!

That’s
it. you have attached the command to the data grid! What? You still don’t
see
data? You want data? Where the heck is the data? How do I see the data?

Well
this is super simple… just run your program, and pinch yourself…

So
where’s the code? And what do I do with these command buttons? How do I
execute
stored procedures and all that?

This
first part was to get your feet wet with the Data Environment Object.

I’ll
be putting in the next part in a few days!

Happy
Data Dreaming!

About
Me:

Anthony
Loera has been goofing around VB for more than 5 years. Has coded for
Bell
Atlantic, & Americatel Telecom companies. Currently contracted on a
couple
projects for a small unknown company called Microsoft, and doing a side
project
for another small pharmaceutical company, which would kill him if it’s name
was
ever divulged. 😉