Re: Non initialized Cmd Parameter Collection via ASP vs SQL Server

Hi again!

Thanks for the bad-practice recommendations, I'm all with you. (I'm
currently MCTS for web and very updated on best practices with ADO.NET)
But to my defence this is a six year old legacy application running in a
sheltered environment with a very limited amount of users, and I'm not about
to rewrite it for free in ASP 3.0. ;)

The "runCommand" function is always called with an initialized and open
connection.

One of the procedures that works and has 5 parameters after .Refresh looks
like this:
CREATE PROCEDURE [dbo].[UpdateSkiftTid]
@SkiftID Integer,
@LinjeID Integer,
@SkiftStart DateTime,
@SkiftStopp DateTime
AS
....

One of the ones that does not work (0 parameters after .Refresh) looks like
this:
CREATE PROCEDURE [dbo].[UpdateNormverdier]
@LinjeID as int,
@GradID as int,
@AntallSkift as int,
@Stopptid as int,
@AntallProd as int,
@AntallStopp as int
AS
....

None of them has any explicit return value, but as I recall, .Refresh
creates one anyway. (And the 5 returned parameters imply..)

I've tried SQL Server Native and SQL Server using ODBC via ODBC dsn's, and
I've also tried using a connection string with Provider=SQLOLEDB.1.

--
Lars-Erik


"Bob Barrows [MVP]" wrote:

Lars-Erik wrote:
Hi!

The code I provided doesn't show, but after the connection, command
type and command text are set I also call
oCmd.Parameters.Refresh

I've done a little testing at the customers server, and it seems that
Refresh fills the parameters for some, but not all of the stored
procedures.

Any way to fix it?



I don't know, and now I'm really glad I never use it.

It might help to show the beginning of the CREATE PROCEDURE script for one
of the procedures for which Refresh does not work. We just need to see the
part where the parameters are declared
create procedure myprocedure (
@parm1 int, ..., @parmN varchar(5)) AS

You're not using any SQL2005-specific datatypes are you? Are you using the
native client provider to connect to SQL 2005?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



.

Relevant Pages

  • Re: Testing for SQL Availability in ASP
    ... > In ASP is there a way to check to see if a SQL server is ... then skip the database connection on that page ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: trusted connection
    ... > I think I have trusted connection to SQL server. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • SQL Server table linked to Access thru ODBC
    ... I had to make some changes to SQL Server tables; ... Table Manager, which I guess I should have used to refresh the connection, I ...
    (microsoft.public.sqlserver.odbc)
  • Re: Non initialized Cmd Parameter Collection via ASP vs SQL Server
    ... The code I provided doesn't show, but after the connection, command ... Refresh fills the parameters for some, but not all of the stored ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !
    ... >into SQL server, the record exist anyway! ... >If you refresh the data you will see the record... ... it gets refreshed after trigger excecution automatically. ... I don't know your monitoring system, but I guess that removing the result ...
    (microsoft.public.sqlserver.programming)
Loading