Wrox Programmer Forums
|
BOOK: ASP.NET Website Programming Problem-Design-Solution
This is the forum to discuss the Wrox book ASP.NET Website Programming: Problem - Design - Solution, Visual Basic .NET Edition by Marco Bellinaso, Kevin Hoffman; ISBN: 9780764543869
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET Website Programming Problem-Design-Solution section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 19th, 2004, 11:39 AM
Authorized User
 
Join Date: Jul 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default stored proc --set by currentUser ?

Hi all,

I have a datagrid showing rows from a datatable--I used newsmanager.
showcategories and forums.forum as my main guides. In the page_load
sub, I have the following declaration:

Dim CurrentUser As SiteIdentity = CType(Context.User.Identity, SiteIdentity)

so that in the stored procedure, I can segregate the "get" by the
current user, thereby loading the grid with ONLY the rows that
match (pertain to) the current user. Here is my stored proc:

ALTER PROCEDURE sp_Properties_GetVendorEquipment
AS
    SELECT * FROM VendorEquipment WHERE PropName = '&CurrentUser.PropName&'

...this is not working--when I add the "WHERE" qualifier, I get
an empty grid--no rows.
Can someone suggest how I might re-write this...do I need to add
something to my page_load, or re-word the WHERE clause in the
stored proc, or other solution? Any suggestions really appreciated.
Thx much,

Reid C.
__________________
Reid C.
 
Old July 19th, 2004, 09:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What are the "&" characters used for? Don't you want to pass in PropName as a parameter of the stored procedure?

Did you add PropName as a new property of the SiteIdentity object? Or, maybe you changed Name to PropName?

I would expect something like this:

CREATE PROCEDURE sp_Properties_GetVendorEquipment
@Name varchar(50)
AS
   SELECT * FROM VendorEquipment WHERE PropName = @Name

Although I would not use an asterisk select-list in any production system (it might break this app if someone adds a new field to the table in the future).

You need to pass in the name when you call the proc.

Eric
 
Old July 20th, 2004, 01:25 PM
Authorized User
 
Join Date: Jul 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Eric,
thanks for the advice on the "*"...no, it is not a production
system, yet.
The use of "@" and declaring the parameter at the start of the
stored proc. did not work--probably because I am not bringing
this variable in with a textbox or dropdownlist. It is being
introduced through the "Dim CurrentUser..." declaration in
the 'Page_Load' sub. I tried this previously with in-line code
and it worked in the following manner:
<code>
Dim UserId as String
Dim PropName as String
UserId=cstr(session("UserId"))
Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropName]='" & session("userId") & "'"
</code>
...but with n-tier architecture and a stored proc. I know I
can't do it like this, so I am groping with my limited knowledge
of both VB.Net and SQL for a solution.
Thx,

Reid C.
 
Old July 20th, 2004, 09:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Reid,

It's hard for me to help you without teaching you a lot about SQL Server and ADO.NET. I have a hard time doing that in this kind of message forum.

The bottom line is that you do need to create a parameterized stored procedure that accepts the username as an input parameter. Before calling this proc in VB, you need to set the value of the parameter.

Since I was already looking at Categories.vb to answer another question, go to that file and examine the code to create parameters:

     Dim parameters As SqlParameter() = { _
        New SqlParameter("@CategoryName", SqlDbType.VarChar, 100), _
        New SqlParameter("@CategoryImageUrl", SqlDbType.VarChar, 100), _
        New SqlParameter("@CategoryPosition", SqlDbType.Int, 4), _
        New SqlParameter("@CategoryID", SqlDbType.Int, 4)}

      ' Set the values
      parameters(0).Value = categoryName.Trim()
      parameters(1).Value = categoryImageUrl.Trim()
      parameters(2).Value = categoryPosition
      parameters(3).Direction = ParameterDirection.Output

      ' Run the procedure
      RunProcedure("sp_Forums_InsertCategory", parameters, rowsAffected)

You really should get an intro book on VB.NET to help you understand the fundamentals. This book on ThePhile is not intended for beginners, and you'll be frustrated if you keep trying to learn the basics from this book.

Please understand that we all had to start out as beginners. I had this book sitting around for several months while I studied other material so I could come back to this book and understand it better.

I think you'd love the Mike Gunderloy book on the 70-305 ASP.NET certification. My son is using this book now and he loves it. He has no background in web programming, but he can follow this book because it has a lot of small step-by-step examples.

Eric
 
Old August 1st, 2004, 02:25 PM
Authorized User
 
Join Date: Jul 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Eric:

thanks for the advice...sorry I didn't respond quicker, been on vac.

I got WROX--Beg VB.Net and some beginning ASP.Net material...but
my problem is not yet grasping how to actually construct n-tier
applications with all the appropriate coding to make the final
part--the aspx page--work like I intended. I'm feeling my way
through trying to get to the point where the flow of how to
create from scratch will start to dawn on me. So I immersed
myself in this project hoping to learn by figuring out how to
make my customized alterations to a complicated appl.

Probably not the best way to go, but I'm trying to muddle through.
Anyway, I think you gave me a good lead on how to re-write this
in the data-tier with parameters rather than the bare-bones "Get..."
function I'm trying to work with.

I'll let you know how it goes.
Thx again,

Reid C.
 
Old August 6th, 2004, 09:24 AM
Authorized User
 
Join Date: Jul 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey, Eric,

well, I figured it out. It came down to a small syntax error
in my BindGrid() sub, using ("PropName") when I should have
instead used the non-parenthetical (PropName). Syntax is such
a ()itch ! -- Seems you can never get too familiar with small
details.

But you're right, this is my real sticking point at this stage
of the game; namely, VB.Net skills. I feel like I'm getting the
hang of it on basic rules of coding, but making the different
files work together when I have a class library in data tier
work with class library in business tier and flow through to
the subroutines in a code-behind file (also getting the syntax
right in the stored proc) can be a real challenge.

Is the book you mentioned above good for this, or is there
something better you've found when it comes to illustrating the
rules of the road on coordinating separate files?

Thx again...

Reid C.
 
Old August 9th, 2004, 05:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That book by Gunderloy is very good. You will find dozens of small example projects to work with. This will give you some "hands on" experience.

And it covers most of the important material, since it's written with certification in mind.

There's a lot of good books on ASP.NET, but it's easiest to learn with a lot of small examples, instead of a couple big examples.

Eric





Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling an insert stored proc from a select stored dzitam SQL Language 10 April 2nd, 2007 12:39 PM
How to get value from stored proc busybee ASP.NET 1.0 and 1.1 Basics 4 April 2nd, 2006 01:06 AM
Very Complicated Stored Proc monfu SQL Server 2000 7 November 29th, 2005 09:02 PM
How i can OUTPUT in Stored Proc yoord SQL Server 2000 2 July 19th, 2004 03:11 PM
Simplify the stored proc stephanel SQL Server 2000 3 August 5th, 2003 08:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.