Hello,

I love the book.
I am using the examples in the book to build applications.
One wall Ive run into though....
I am entering users based on last name, first name into a SQL database. Im using a stored procedure to do this and in the sp i check if the last name, first name exist already somewhere in the db.
If so I want to be able to pass back an output parameter and also a resultset of the lastname, firstname row that exist and their id(autoidentity). I have succeded in doing this. I then want to inform the user that this name already exists in the database...but i want to take it a bit further and display the row with that firstname, last name combination and give the user the option to edit that existing row. How can I go about retrieving the output parameter AND the resultset in a vb.net function?? This is what puzzles me. Im hoping if the insert is unsuccessful (meaning the name alrady exists) that i can return a value (-1) and a select query of the existing name. How can I handle this with a vb.net function in a DAL?
The reason for this is I want to keep track of students and what bus they are riding to/from school. One student can only exist on one bus. So if the secretary is entering a student i want them to be informed if a student already exists on another bus, and i want to retrieve that record and give the secretary the option to edit that row....any suggestions?
let me know if i need to do more explaining

Hi cf2006,

Glad you like the book.

Quick question before I give you a longer answer (which may take some time, kinda busy at the moment). Are you using a specific application from the book for your own project? Are you basing this on the Appointment Booking app?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Imar,

Thanks for the quick reply. No, im not basing it off of that...should I be looking at that one? Im just kind of using bits and pieces of ch 12 and 5....I noticed in Ch5 that you inform the user when they enter a category that already exists. i just want to go a little further than that. Im stumped though... :(
My eyes hurt and all the words are beginning to run together
thanks
justin

Hi justin,

No, you don't necessarily have to look at the Appointment booking app. However, if you were basing this on an existing app, it would be easier for me to show you code that plugs right into your application.

Basically what you need to do is this:

1. Create a stored procedure with an OUTPUT param. This sproc should select the result set AND fill in the OUTPUT param. You can use IF EXISTS in the query to see if the record already exists.

2. Next, you need to set up this param in the DAL and pass it into the Command object.

3. When the query is executed, get the resultset (a DataSet or a SqlDataReader) and get the value from the output param.

4. Give the method that calls this code a return type of the initial data (e.g. a User object, or a DataSet, or a List (Of Users), whatever is appropriate.) Also, if necessary, give it a ByRef param to indicate if the record already exists.

Now, this is the hard way to do things. You want to return an OUTPUT param AND a resultset at the same time. But maybe you can do this much simpler? What about this (pseudo code):

Public Function CheckUser(ByVal userId As Integer)
  ' Go into database
  If the user exists in the database
    Return New User(FirstName from database, LastName from database)
  Else
    Return Nothing
  End If
End Function

Then in your calling code you can do this:

If CheckUser(someId) Is Nothing Then
  ' User is not on the bus
Else
  Sorry, this user is already on the bus. Would you like to edit it?
End If

If you need help with the first scenario (or with the second), let me know. A verbose answer may take until the weekend as I don't much time earlier.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Hi justin,

Are you making any progress with this? Did my general outline help you to get started?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.