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.

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

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.

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

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.

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.

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