Fatman

If so, can I talk through a tech problem?

I've got an ASP.NET page with associated VB codebehind (i.e. page.aspx and page.aspx.vb). I'm calling a couple of Stored Procedures which are held on a SQL Server 2000 dB. The SPs are created already, I'm not trying to build those on the fly. Each SP called via a SqlCommand and each SqlCommand is associated (via the Properties palette of the SqlCommand) to the one and only SqlConnection.

Each SqlCommand has a Parameters collection. That was auto-filled when I dragged the SqlCommand control onto the design surface. When I inspect the Parameters Collection it looks sensible and references the Parameters I'm expecting.

I've filled each Parameter with a value as follows...
SqlCommand1.Parameters("something").Value = ControlName.Text or SqlCommand1.Parameters("something").Value = "text" for something static.

The SqlCommands are executed when a button is pressed.

Now here's the crunch. When I try to build and view the page I keep getting the same error message. The page renders fine, but when I submit the form (button press) I keep getting the message...

An SqlParameter with ParameterName 'something' is not contained by this SqlParameterCollection.

...well, I know damned well that it is, 'cos I can see it in the Parameters Collection list. So what am I missing? What else defines the Parameters Collection?

Advicve much appreciated!

Fatman

top

GaryK

fm,

well Im not a .net expert but do alot of work with ADO and TransactSQL stored procs, first thing I would do is run up query analyzer, connect to the DB using the same login you are using in your code in case its a permissions issue, then call the proc using exec <procname> <parameterlist> to make sure there are no errors in your TSQL. If that works then on the code side err dont know! apart from checking you are assigning a value to each parameter and making sure you have set any output parameters as such in your code.

Good Luck

Gary

chiark

FM,

Write dummy code to walk through and enumerate the parameters collection, just outputting the names and values. If it's there, something exceptionally strange is happening...

milo

post up the source code and i'll tell u what is wrong with it. at first guess, i would say you're not pre-pending "@" to the parameter name.

SqlCommand1.Parameters("something").Value

should be:

SqlCommand1.Parameters("@something").Value

if that's not it, post up your source and the stack trace of the error.

on a side note, if this is for a business critical application (or any kind of decent app), u are shooting yourself in the foot for using the drag-and-drop controls. furthermore, if this is an oltp style app rather than a reporting system, u shouldn't be interacting your presentation tier directly with your data tier.

SCOSaltire

i think that u have to create a new param
and then add it to the param collection on the command object.

Fatman

@ GaryK: I don't think it's a permissions issue. Initially I hadn't assigned user rights on the Stored Procedure itself - that did give a permissions error. Not seen a 'permissions' reference since. I'll try the Query Analyser idea though. Thanks

@ chiark: I'll try that, thanks. You mean write each to, say, a pop-up box? My main VB-like experience has been Access VBA. Whenever I wanted to debug a variable I would sent it to a pop-up box, or use debug.print (IIRC). What's the closest/best method for ASP.NET in Visual Studio? I'm still learning, really.

@ milo: sorry, I can't post the source code as such. I'll see if I can 'genericise' it tonight, if the above hasn't cracked it. You're right about the parameter "@" reference, I have used the SqlCommand1.Parameters("@something").Value form.

What do you mean about using drag and drop controls? By dragging/dropping I meant pulling text boxes, SqlCommands from the Tools palette to the Design Surface. Is that what you're talking about? Also, what is an "oltp style app"? What does "shouldn't be interacting your presentation tier directly with your data tier"? Apologies if these are Noddy questions - I am learning as I go.

@ SCOSaltire: But how? I've created the Parameters and I've associated them with the Command Object. Or at least, I believe I have. When I open the Parameters Collection, it lists all the Parameters I'm expecting.

chiark

I'd just dump 'em out in your ASP code with response.write...

SCOSaltire

i dont understand how it has access to the parameters already.
ive only learnt to do it in code:

myCommand.SelectCommand.Parameters.Add(New SqlParameter("@State", SqlDbType.NVarChar, 2))

myCommand.SelectCommand.Parameters("@State").Value = MySelect.Value

i.e. adding it first and then setting the value.
the error sounds like its not in the parameter collection - hence why i suggested it.

milo

by drag-and-drop, im referring to the components - such as sqlcommands. doing them in that fashion makes maintenence a nightmare, adds complexity, slows performance and (as you've just found it) makes debugging a nightmare.

oltp style is transactional processing - in other words there's some form of business logic. this should be stripped out into a business logic layer, and the data logic into a data layer. so instead of the nightmares you've got in your presentation layer (let's assume you're writing a product ordering system), your presentation layer's code-behind will look more like (in c#):

Order o = new Order();
o.ShipTo = "100 Fake Street";
// set other properties like products, payment method etc
OrderSystem.Place(o);

and that's it. read up about n-tier development, object-relational mappers, layered development etc.

however, if you're just writing reports (such as pulling back a list of orders placed in a different system), then there isn't any business logic as such, so just using ado.net is the fastest way to get things done.

i say all of this with no knowledge of your project however. i'm assuming you're writing a business critical app. u could be just trying to put up a bunch of family photos or something - so what i'm suggesting may be overkill

Fatman

@ SCOSaltire: The Parameter definition code you posted is (I'm fairly sure!) the same as is created automatically by VS. The SqlCommannd palette is, I guess, just a 'nice' GUI way of achieving the same thing.

@ milo: the application is, in fact, an online ordering system. So, yes, you could say it's "business critical". I imagine that some means of abstraction could be useful. However, the product catalogue is very small, so I hope it's not necessary. I'd rather just get this working for now!

GaryK

fm

so still haven't resolved it?

Gary

Fatman

The programming is the 'other' job! I'm at the day job currently. I'll have another crack at it tonight.