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

Today we are going to look at the great VB Data Environment again. Why? Well
because we have yet to do all the nifty things we can do with it! This a
wonderfully exciting data object that is just toooo EASY to use! Specially if
your only 10 years old! What’s so cool about talking to a server when your 10
years old??? 

Hey have you guys seen this cool game called Allegiance? Yeah, so it’s a
space war simulator and dozens of gamers can be playing on the internet at the
same time!! Did you guys know that SQL Server 7 was used to have every real-time
space battle between everybody at the same time? Hey, If you 10 year olds can
start communicating with SQL servers, I think we’ll have MUCH cooler looking
games!! 

What? You want e-commerce stuff? Business Analyst stuff?  Geez your a
boring 10 year old, but I guess VB Data Environment will work for that
too… 

Ok, so… get ready to drop you’re jaw, because were going to be doing some
super simple Data Access Dream stuff…


In Part 1 we used the Data Environment to talk to the SQL Server, and command
the server to show us a table. What else can you do with this super simple data access object? Specially if your
only
10 years old? Lets find out! This project will have you using 1 Data Environment
Object to do these things… 

You’ll need:

The dinky application that we built in Part 1(see part 1 first if ya don’t
have it!) and the Part 1’s data already in SQL Server. 

Ok,
got part 1’s stuff? Great! let’s continue then!! Oh yeah you also need:

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


Great,
now let’s start:

 

Visual
Basic 6:

 

Open the application in visual basic, it will look like the picture below:

 

 

Once you load the VB application, you’ll notice the Data Environment in our
project!

 

Ok,
now that we loaded the application, check your server connection by first double
clicking on the "DataEnvironment1" on the window shown above. A new
window will open up (see below), here just right click on
"Connection1" and select it’s properties.

Great, now In the Connection properties, Check your server name, your
user name, password, and the database that you have your data in. After
everything looks good, just click to test the connection to see if you are
properly connected!

 

This
is Great! You have checked your SQL Server connection, and we can start
executing a stored procedure using the DataEnvironment object!!


Executing a stored
procedure

Creating the stored procedure for the project:

 

First let’s create s stored procedure, just open the ‘Query Analyzer’ and
copy-paste & execute the following:

(it creates a simple stored procedure, that will later show us the data,
ordered by the ‘Data_value’)

 

—Start SQL commands—
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = ‘my_storedProcedure’ AND type = ‘P’)
DROP PROCEDURE my_storedProcedure
GO

USE pubs
GO

create procedure my_storedProcedure
as
select Data_date, Data_Value, Data_name
from Datatable
order by data_value

—End SQL commands—

 

Alrighty then, we now have a Stored Procedure in SQL Server! But how the heck
do we tell the DataEnvironment Object about it? and How the heck do we use the
darn thing? No problema dudes, just HAng in their for the simple answers!!


Adding a Stored Procedure Command: 

 

To
Add a Stored Procedure Command we simply go back to the Data Environment Window
we used to get into the connection1 properties, first select the
"Connection1" to highlight it, then we click on the "Add
command" button:

 

Now right click on your new command ("Command2"), and select
properties. Here
a window will pop up and show you the items for your command2 properties:

 


When this is opened, you simply select "Stored Procedure" in the
Database object dropdown list, then simply select the name of your brand new
stored procedure in the Object Name dropdown!! (This is JUST TOO EASY!!) 

You have just made a Stored Procedure command for your DataEnvironment!!!

What!!?
That’s it? No way, I must have read over some really hard and disgusting details
somewhere, where’s the sweat and tears? Where’s the hard labor? Where’s the 8
hour long code debugging?? Well, if your masochistic, you can dive into some
really nasty stuff in nightmare land, but not here ok, so stop yer
blathering!  

Now what do we do?  Well I want to be able to use my stored procedure of
course! To do this I have to assign this DataEnvironment command to one of my
buttons! (O-oh, we are actually going to type a piece of code…and, HEY, their
is no fainting allowed!)


Using the command button:

To use the command button we will need to type in some code on it’s click
event, an easy way to to this is to double click on the button (use command1
button for now…) you want to use. You will then see the "Code
Window" pop up, here you would just add the following super easy and cheesy
code:

Since the DataGrid control was already assigned to the DataEnvironment object
in part one, we only need to tell it to use the stored procedure command we just
created, and to refresh the grid. Wahoooo!! Is this easy or WHAT? We are done
with this! 

REALLY!! We are finished and we have now created the ability to have the
application use a stored procedure!! What? You want to check it out for
yourself? Ok, run your application, and then press the command1 button! You’ll
see that the grid changes to reflect our stored procedure!

You are now allowed to pinch yourself !


You Want More??? 

Ok, but from here on out, we’ll be using more and more code! (So
beware!)  😉


Executing a Query on the fly

Here were going to create a way so that you can enter your own SQL statement,
and have your SQL Server give you 10 year olds what you want:

How do we do something like this? Is it super hard? Will it harm me? Will the
silly rabbit ever eat Trix?    Hmmm… all good questions indeed!
Hopefully I can provide you with the easy answers you are looking for!


Create SQL Command:

First thing is to create another command, to do this we simply go back to the
Data Environment Window we used to get into the connection1 properties, first
select the "Connection1" to highlight it, then we click on the
"Add command" button:

 

Next, you select the "SQL Statement" under the command3 properties,
and type in a simple SQL statement. (You can’t leave the box blank)

 

That’s it, you have just added a command that tells SQL Server that you
want
to use an SQL Statement. Cool Beans!

Ok, so how is this going to help me do an on the fly Query? Do I have to do
this all the time if I need to change this SQL statement? Why do I feel like I’m
gong to be coding some more?? 

Don’t worry too much! First of all, since you are only 10, just worry about Saturday
morning "Pokemon and the Johto Journeys" for now, I’ll help you with
the simple code for the command button … here we go! 


Using the command Button for SQL:

 

To use the command button with the SQL Command we will need to type in some
code on it’s click event, an easy way to to this is to double click on the
button (use command2 button for now…). You will then see the "Code
Window" pop up, here you would just add the following simple easy code:

 

Now, what we told the application to do is this: Tell SQL Server to execute
whatever I type in the text1 box. So if you type gibberish in the text1 box,
then the server will give you an error, and you’ll feel like something is not
working. Just remember that you need to use SQL Commands in the text1 box. 

 

Oh, one last thing… change the text1 property called
"Multiline" to true, also change the "scrollbars" property
to "2-Verticle". That way you can type in really long commands into
the text1 box easily.

 

Ok, guess what!! You are Finished!! You are kicking major but! Now you can
easily do Queries On the Fly, and without getting all crazy. 

(Way too easy right?!) OK!  You want to run it? Go ahead! Just remember
to type in a correct SQL statement in the text1 BOX!!!


More? Whew! Ok, ok! here’s more…


Executing a stored procedure with a parameter

 

Wow, a stored procedure with a parameter. This sounds complicated!!! (It’s
not, but you can sure impress 9 year olds with it!)

 

Ok first, we create the stored procedure with a parameter, just drop it in
Query Analyzer and execute the SQL Stuff below: 

(skip this part if you know about parameters)

(This stored procedure would bring back only the data that has a particular
Data_value. The parameter ‘@value’ only accepts numbers(integers))

You would normally execute this by typing in " exec my_storedprocedure2
10 " in the Query Analyzer after creating this procedure. The Parameter in
this command is the number 10. But you can put in any old number you want here,
because the parameter accepts numbers. If it does not bring back any data, it
just means that their is nothing with your number in the table. (ok enough of
the Basics..)

 

—Start SQL commands—
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = ‘my_storedProcedure2’ AND type = ‘P’)
DROP PROCEDURE my_storedProcedure2
GO

USE pubs
GO

create procedure my_storedProcedure2 
@value integer
as
select Data_date, Data_Value, Data_name
from Datatable
where data_value = @value

 

—End SQL commands—

 


Creating the stored procedure command:

(This might feel like Deja-vu for some of you…)

 

Create another command in the DataEnvironment by clicking the "Add
Command" button, and assign it the new Stored Procedure in the new
command’s properties: 

 

 

(man this is Too EASY!!!) 

Now YOU MUST go and put in something for the value of the parameter, you
cannot leave the following part blank. To do this, select the
"Parameter" Tab in the Properties window where you assign the stored
procedure to the command (see above). Then Select the name of your parameter in
the "Parameters" box (see Below) once it’s highlighted, just type in
any old number in the bottom "Value" box… (I Typed the number 9 here
just because I thought 9 is a cool number…that’s all)

 

Now (whew!) you are done telling the DataEnvironment about the parameter. (If
you use more than one parameter in a stored procedure, the same thing applies to
them.)

 

Isn’t this great? You are almost done with the silly parameter stuff!!


Using the command Button for this Stored Procedure:

 

To use the command button with this Stored Procedure we will need to type in
some code on it’s click event, an easy way to to this is to double click on the
button (use command3 button for now…). You will then see the "Code
Window" pop up, here you would just add the following (Yawn) code:

 

 

Now, what we told the application to do is this: Tell SQL Server to execute
the new stored procedure with anything I type in the text1 box as the parameter.
So if you type gibberish in the text1 box, then the server will give you an
error, and you’ll feel like something is not working. Just remember that you
need to use a proper parameter in the text1 box. (For this Stored procedure you
can only enter numbers, maybe in some other stored procedure you can only enter
a name, or letters, or something else…) 

 

 

YOU
ARE NOW FINISHED!! Yep, you can now use the DataEnvironment to run a stored
procedure with this thing called a parameter!! Why would you use this? Stored
procedures are simply faster to run than SQL on the fly!

 

 

Well that was super simple… just run your programs wipe the cheeto crumbs
off your face, and if you still think your dreaming… pinch yourself.!!


This was the second part of Data Access Dreams with the Data Environment Object.

 

 

I hope you all can see why I like this Data Environment Object so much. I
hope that the more and more you use it… you’ll start noticing and wanting to
do other things. Eventually you will start learning how to use other advanced
magical objects, like disconnected recordsets, Active X object creation and the
MS XML objects. But even if you don’t get into these caffeine inducing areas,
just knowing how to use the Data Environment Object will give you a good nights
rest when you have to whip up some Data Access Application fast.

 

Happy
Data Dreaming!

 

 

About
Me:

 

 

Anthony
Loera
has been working with VB for more than 5-6 years. Has coded for
Bell
Atlantic, Merck pharmaceuticals & Americatel Telecom companies. Currently contracted on a
couple
projects for a small unknown company called Microsoft, and will be open to
accept offers for late November 2000 using advanced VB/SQL on projects or for a
PM position.

Latest Articles