473,834 Members | 2,255 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with the Legacy ASP files and the Sql Server Express

Hi all,

I posted this question in the sqlserver.newus ers group but I am not getting
any response there so I am going to try it on the fine folks here:).

I inherited some legacy ASP codes in my office. The original code's backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveConne ction= dbConnection
cmd.CommandText = "NewCompany "
cmd.CommandType = adCmdStoredProc

cmd.Parameters( "@varCompanyNam e") = Request.Form("C OMPANYNAME")
:
:
cmd.Parameters( "@RETURNVAL UE") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecord set
ID = cmdCom.Paramete rs("@RETURNVALU E")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created correctly
with the proper id. It is just the return value received at the client web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important as a
lot of our customers are migrating their SQL servers from 2000 to the 2005.

TIA
Mar 30 '07 #1
12 2268
More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared memory.
After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN @value, or
similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into the
sproc code.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************** *************** ***************
Think outside the box!
*************** *************** ***************
"Light" <a@a.comwrote in message
news:ex******** ******@TK2MSFTN GP02.phx.gbl...
Hi all,

I posted this question in the sqlserver.newus ers group but I am not
getting any response there so I am going to try it on the fine folks
here:).

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveConne ction= dbConnection
cmd.CommandText = "NewCompany "
cmd.CommandType = adCmdStoredProc

cmd.Parameters( "@varCompanyNam e") = Request.Form("C OMPANYNAME")
:
:
cmd.Parameters( "@RETURNVAL UE") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecord set
ID = cmdCom.Paramete rs("@RETURNVALU E")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created correctly
with the proper id. It is just the return value received at the client web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important as
a lot of our customers are migrating their SQL servers from 2000 to the
2005.

TIA
Mar 30 '07 #2
Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I couldn't
find the configuration for non-local conenctions. Anyway, it doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY
from SQLDataSource and DetailsView) in this newsgroup yesterday , which
actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..

"Cowboy (Gregory A. Beamer)" <No************ @comcast.netNoS pamMwrote in
message news:48******** *************** ***********@mic rosoft.com...
More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared memory.
After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN @value,
or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into the
sproc code.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************** *************** ***************
Think outside the box!
*************** *************** ***************
"Light" <a@a.comwrote in message
news:ex******** ******@TK2MSFTN GP02.phx.gbl...
>Hi all,

I posted this question in the sqlserver.newus ers group but I am not
getting any response there so I am going to try it on the fine folks
here:).

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveConn ection= dbConnection
cmd.CommandTex t = "NewCompany "
cmd.CommandTyp e = adCmdStoredProc

cmd.Parameters ("@varCompanyNa me") = Request.Form("C OMPANYNAME")
:
:
cmd.Parameters ("@RETURNVALUE" ) = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecord set
ID = cmdCom.Paramete rs("@RETURNVALU E")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set
the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created correctly
with the proper id. It is just the return value received at the client
web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important
as a lot of our customers are migrating their SQL servers from 2000 to
the 2005.

TIA

Mar 30 '07 #3
as you are using the com based adodb library, its not asp.net based. you
are only reading one result set before trying to access the return
parameter values. check that your 2005 sp's only return one, though a
better approach is to actually read all result (NextResult) sets before
accessing parameter values.

you should switch to ado.net, as you current code a bunch of memory
leaks (you are not releasing com objects).
-- bruce (sqlwork.com)

Light wrote:
Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I couldn't
find the configuration for non-local conenctions. Anyway, it doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY
from SQLDataSource and DetailsView) in this newsgroup yesterday , which
actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..

"Cowboy (Gregory A. Beamer)" <No************ @comcast.netNoS pamMwrote in
message news:48******** *************** ***********@mic rosoft.com...
>More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared memory.
After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN @value,
or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into the
sproc code.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

************** *************** *************** *
Think outside the box!
************** *************** *************** *
"Light" <a@a.comwrote in message
news:ex******* *******@TK2MSFT NGP02.phx.gbl.. .
>>Hi all,

I posted this question in the sqlserver.newus ers group but I am not
getting any response there so I am going to try it on the fine folks
here:).

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveCon nection= dbConnection
cmd.CommandTe xt = "NewCompany "
cmd.CommandTy pe = adCmdStoredProc

cmd.Parameter s("@varCompanyN ame") = Request.Form("C OMPANYNAME")
:
:
cmd.Parameter s("@RETURNVALUE ") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecord set
ID = cmdCom.Paramete rs("@RETURNVALU E")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set
the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created correctly
with the proper id. It is just the return value received at the client
web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important
as a lot of our customers are migrating their SQL servers from 2000 to
the 2005.

TIA

Mar 30 '07 #4
Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript code
for ccreating ADODB.Command and its parameters (you omitted some key part of
code in your first post), so that one could not tell how the parameter is
created for the ADODB.Command object. Of course this is assume that you do
have a connection that can reach SQL Server Express. I do not think your
problem is due to difference of SQL Server2000 and SQL Server2005, unless
your stored procedure has some thing that only works in SQL Server2000, not
SQL Server 2005.
"Light" <a@a.comwrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I
couldn't find the configuration for non-local conenctions. Anyway, it
doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY
from SQLDataSource and DetailsView) in this newsgroup yesterday , which
actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..

"Cowboy (Gregory A. Beamer)" <No************ @comcast.netNoS pamMwrote in
message news:48******** *************** ***********@mic rosoft.com...
>More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared memory.
After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN @value,
or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into the
sproc code.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

************** *************** *************** *
Think outside the box!
************** *************** *************** *
"Light" <a@a.comwrote in message
news:ex******* *******@TK2MSFT NGP02.phx.gbl.. .
>>Hi all,

I posted this question in the sqlserver.newus ers group but I am not
getting any response there so I am going to try it on the fine folks
here:).

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveCon nection= dbConnection
cmd.CommandTe xt = "NewCompany "
cmd.CommandTy pe = adCmdStoredProc

cmd.Parameter s("@varCompanyN ame") = Request.Form("C OMPANYNAME")
:
:
cmd.Parameter s("@RETURNVALUE ") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecord set
ID = cmdCom.Paramete rs("@RETURNVALU E")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set
the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created correctly
with the proper id. It is just the return value received at the client
web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important
as a lot of our customers are migrating their SQL servers from 2000 to
the 2005.

TIA


Mar 30 '07 #5
Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep
it here for now.

The new company table is created correctly.

The connection string:
Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[NewCompany]') and OBJECTPROPERTY( id, N'IsProcedure') =1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,
:
:
@RETURN_VALUE int OUTPUT
)
As
INSERT INTO tblCompany(varC ompanyName)
values
(@varCompanyNam e )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE
Norman Yuan wrote:
Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript code
for ccreating ADODB.Command and its parameters (you omitted some key part of
code in your first post), so that one could not tell how the parameter is
created for the ADODB.Command object. Of course this is assume that you do
have a connection that can reach SQL Server Express. I do not think your
problem is due to difference of SQL Server2000 and SQL Server2005, unless
your stored procedure has some thing that only works in SQL Server2000, not
SQL Server 2005.
"Light" <a@a.comwrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
>>Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I
couldn't find the configuration for non-local conenctions. Anyway, it
doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY
from SQLDataSource and DetailsView) in this newsgroup yesterday , which
actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..

"Cowboy (Gregory A. Beamer)" <No************ @comcast.netNoS pamMwrote in
message news:48******** *************** ***********@mic rosoft.com...
>>>More likely, you have to set the Express product to accept non-local
connection s and enable some other connection protocol than shared memory.
After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN @value,
or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into the
sproc code.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

************ *************** *************** ***
Think outside the box!
************ *************** *************** ***
"Light" <a@a.comwrote in message
news:ex***** *********@TK2MS FTNGP02.phx.gbl ...

Hi all,

I posted this question in the sqlserver.newus ers group but I am not
getting any response there so I am going to try it on the fine folks
here:).

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveC onnection= dbConnection
cmd.Command Text = "NewCompany "
cmd.Command Type = adCmdStoredProc

cmd.Paramet ers("@varCompan yName") = Request.Form("C OMPANYNAME")
:
:
cmd.Paramet ers("@RETURNVAL UE") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecord set
ID = cmdCom.Paramete rs("@RETURNVALU E")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set
the
RETURNVAL UE to -5 before the execute,
the new value remains -5. And I could see the table is created correctly
with the proper id. It is just the return value received at the client
web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important
as a lot of our customers are migrating their SQL servers from 2000 to
the 2005.

TIA


Mar 31 '07 #6
From your SP, I do not see your issue is caused by this SP, however, the SP
is not well written, IMO.

Firstly, do not use @@Idehtity, use SCOPE_IDENTITY( ) instead. There are
quite some discussion on this topic. In your case, @@Identity may not be
guranteed as the identity of the record you just inserted into Compony
table, if another inserting occurs at the nearly exactly the same moment,
say right after your inserting but before your SELECT @Return_Value is
executed.

Secondly, you have already define @return_Value as OUTPUT paramter, there is
no need to pass it as return value. in SP, RETURN somevalue is meant for
return a value to indicate the SP execution status (succeeded, failed, or
partially executed...). Do not confuse it with OUTPUT parameter. In most SP,
you can simply leave it as

Create Procedure
As
....
....
RETURN

SQL Server will pass it a default value.

However, these should not affect your VBScript getting unexpected result, as
your OP said.

As I mentioned, you need post the VBScript code to show how do you create
ADODB.Command and its parameter, which you omitted in your OP. I strongly
suspect your VB code is wrong on how you build ADODB.Command, its Parameters
and how you get the OUTPUT value from the parameters. Until seeing that part
of VB code, I can say more.

"Light" <a@a.comwrote in message
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..
Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep it
here for now.

The new company table is created correctly.

The connection string:
Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[NewCompany]') and OBJECTPROPERTY( id, N'IsProcedure') =1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,
:
:
@RETURN_VALUE int OUTPUT
)
As
INSERT INTO tblCompany(varC ompanyName)
values
(@varCompanyNam e )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE
Norman Yuan wrote:
>Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript
code for ccreating ADODB.Command and its parameters (you omitted some key
part of code in your first post), so that one could not tell how the
parameter is created for the ADODB.Command object. Of course this is
assume that you do have a connection that can reach SQL Server Express. I
do not think your problem is due to difference of SQL Server2000 and SQL
Server2005, unless your stored procedure has some thing that only works
in SQL Server2000, not SQL Server 2005.
"Light" <a@a.comwrote in message
news:%2******* *********@TK2MS FTNGP02.phx.gbl ...
>>>Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I
couldn't find the configuration for non-local conenctions. Anyway, it
doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY
from SQLDataSource and DetailsView) in this newsgroup yesterday , which
actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..

"Cowboy (Gregory A. Beamer)" <No************ @comcast.netNoS pamMwrote in
message news:48******** *************** ***********@mic rosoft.com...

More likely, you have to set the Express product to accept non-local
connectio ns and enable some other connection protocol than shared
memory. After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN
@value, or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into
the sproc code.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********** *************** *************** ****
Think outside the box!
*********** *************** *************** ****
"Light" <a@a.comwrote in message
news:ex**** **********@TK2M SFTNGP02.phx.gb l...

>Hi all,
>
>I posted this question in the sqlserver.newus ers group but I am not
>getting any response there so I am going to try it on the fine folks
>here:).
>
>I inherited some legacy ASP codes in my office. The original code's
>backend
>is using the SQL Server 2000 and I am testing to use it on the Express
>edition.
>
>And I run into the following problem.
>
>One of the pages is using a stored procedure to create a new table and
>return the value of the new table ID.
>
>The codes are written in VBScript, briefly, it is as follows:
>
>set cmd = Server.CreateOb ject("ADODB.Com mand")
>cmd.Active Connection= dbConnection
>cmd.Comman dText = "NewCompany "
>cmd.Comman dType = adCmdStoredProc
>
>cmd.Parame ters("@varCompa nyName") = Request.Form("C OMPANYNAME")
:
:
>cmd.Parame ters("@RETURNVA LUE") = 0
>
>Set rsID = cmdCom.Execute
>Set rsID = rsID.NextRecord set
>ID = cmdCom.Paramete rs("@RETURNVALU E")
>
>On the SQL Server 2000, the ID is returned correctly, but the Express
>doesn't. In fact, it didn't fill in the vat all. For example, If I set
>the
>RETURNVALU E to -5 before the execute,
>the new value remains -5. And I could see the table is created
>correctl y
>with the proper id. It is just the return value received at the client
>web
>browser is never set.
>
>So my question is, is there any change I need to make for the Express's
>stored procedure?
>
>Any suggestion is highly appreciated. This problem is kind of important
>as a lot of our customers are migrating their SQL servers from 2000 to
>the 2005.
>
>TIA
>

Mar 31 '07 #7
Hi Norman,

Again, thank.

Sp aside, I thought the 1st post already give you my VBScript.

I tried the Scope_Identify( ), but that didn't help. As for the return,
Gregory in his reply also said it needed.

If the problem is not with the SQL server Exp, then how come the SQL
Sever 2000 works?

That is somethng I can't understand.

Norman Yuan wrote:
From your SP, I do not see your issue is caused by this SP, however, the SP
is not well written, IMO.

Firstly, do not use @@Idehtity, use SCOPE_IDENTITY( ) instead. There are
quite some discussion on this topic. In your case, @@Identity may not be
guranteed as the identity of the record you just inserted into Compony
table, if another inserting occurs at the nearly exactly the same moment,
say right after your inserting but before your SELECT @Return_Value is
executed.

Secondly, you have already define @return_Value as OUTPUT paramter, there is
no need to pass it as return value. in SP, RETURN somevalue is meant for
return a value to indicate the SP execution status (succeeded, failed, or
partially executed...). Do not confuse it with OUTPUT parameter. In most SP,
you can simply leave it as

Create Procedure
As
...
...
RETURN

SQL Server will pass it a default value.

However, these should not affect your VBScript getting unexpected result, as
your OP said.

As I mentioned, you need post the VBScript code to show how do you create
ADODB.Command and its parameter, which you omitted in your OP. I strongly
suspect your VB code is wrong on how you build ADODB.Command, its Parameters
and how you get the OUTPUT value from the parameters. Until seeing that part
of VB code, I can say more.

"Light" <a@a.comwrote in message
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..
>>Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep it
here for now.

The new company table is created correctly.

The connection string:
Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N '[dbo].[NewCompany]') and OBJECTPROPERTY( id, N'IsProcedure') =1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyNa me varchar (50) = null,
:
:
@RETURN_VAL UE int OUTPUT
)
As
INSERT INTO tblCompany(varC ompanyName)
values
(@varCompanyN ame )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE
Norman Yuan wrote:
>>>Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript
code for ccreating ADODB.Command and its parameters (you omitted some key
part of code in your first post), so that one could not tell how the
parameter is created for the ADODB.Command object. Of course this is
assume that you do have a connection that can reach SQL Server Express. I
do not think your problem is due to difference of SQL Server2000 and SQL
Server2005 , unless your stored procedure has some thing that only works
in SQL Server2000, not SQL Server 2005.
"Light" <a@a.comwrote in message
news:%2***** ***********@TK2 MSFTNGP02.phx.g bl...
Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I
couldn't find the configuration for non-local conenctions. Anyway, it
doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY

from SQLDataSource and DetailsView) in this newsgroup yesterday , which

actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..

"Cowboy (Gregory A. Beamer)" <No************ @comcast.netNoS pamMwrote in
message news:48******** *************** ***********@mic rosoft.com...
>More likely, you have to set the Express product to accept non-local
>connection s and enable some other connection protocol than shared
>memory. After that, examine the connection string.
>
>Then, check the stored procedure and make sure it is using RETURN
>@value, or similar. Otherwise the return value is returning nothing.
>
>Therer is probably more I could do, but would need a deeper dive into
>the sproc code.
>
>--
>Gregory A. Beamer
>MVP; MCP: +I, SE, SD, DBA
>http://gregorybeamer.spaces.live.com
>
>********** *************** *************** *****
>Think outside the box!
>********** *************** *************** *****
>"Light" <a@a.comwrote in message
>news:ex*** ***********@TK2 MSFTNGP02.phx.g bl...
>
>
>>Hi all,
>>
>>I posted this question in the sqlserver.newus ers group but I am not
>>getting any response there so I am going to try it on the fine folks
>>here:).
>>
>>I inherited some legacy ASP codes in my office. The original code's
>>backend
>>is using the SQL Server 2000 and I am testing to use it on the Express
>>edition .
>>
>>And I run into the following problem.
>>
>>One of the pages is using a stored procedure to create a new table and
>>return the value of the new table ID.
>>
>>The codes are written in VBScript, briefly, it is as follows:
>>
>>set cmd = Server.CreateOb ject("ADODB.Com mand")
>>cmd.Activ eConnection= dbConnection
>>cmd.Comma ndText = "NewCompany "
>>cmd.Comma ndType = adCmdStoredProc
>>
>>cmd.Param eters("@varComp anyName") = Request.Form("C OMPANYNAME")
>>:
>>:
>>cmd.Param eters("@RETURNV ALUE") = 0
>>
>>Set rsID = cmdCom.Execute
>>Set rsID = rsID.NextRecord set
>>ID = cmdCom.Paramete rs("@RETURNVALU E")
>>
>>On the SQL Server 2000, the ID is returned correctly, but the Express
>>doesn't . In fact, it didn't fill in the vat all. For example, If I set
>>the
>>RETURNVAL UE to -5 before the execute,
>>the new value remains -5. And I could see the table is created
>>correct ly
>>with the proper id. It is just the return value received at the client
>>web
>>browser is never set.
>>
>>So my question is, is there any change I need to make for the Express's
>>stored procedure?
>>
>>Any suggestion is highly appreciated. This problem is kind of important
>>as a lot of our customers are migrating their SQL servers from 2000 to
>>the 2005.
>>
>>TIA
>>
>
Apr 1 '07 #8
Here is your code in OP (note what I added):

set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveConne ction= dbConnection
cmd.CommandText = "NewCompany "
cmd.CommandType = adCmdStoredProc

'============== ===============
'There MUST be some code in this part to add ADODB.Parameter s to the cmd
object!
In your case, you MUST create two parameters and append them to the cmd
Object.
'This code would be like:

Set pmt=cmd.CreateP arameter("@varC ompanyName",adV arChar,
adParameterInpu t,100)
pmt.Value=Reque st.Form("COMPAN YNAME")
cmd.Parameter.A ppend pmt

Set pmt=cmd.CreateP arameter("@Retu rn_Value",adInt eger, adParameterOutp ut)
cmd.Parameter.A ppend pmt

'Then you can execute the command and then retrieve the ID
cmd.Execute
ID=cmd.Paramert ers("@Return_Va lue")

I am 100% sure this code works against any version of SQL Server (6.5, 7,
2000, 2005), as long as the connection is OK and the SP has two parameters
@varCompany as Input and @Return_Value as Output (not Return, though).

'============== ===============

'So, the code between "******" lines should be removed
'************** *************
cmd.Parameters( "@varCompanyNam e") = Request.Form("C OMPANYNAME")
:
:
cmd.Parameters( "@RETURNVAL UE") = 0
'************** *************** **********

'I do not know what rsID is (a RecordSet?) and why you call
rsID.NextRecord set
'Your SP does not return a set record, nor more than one set data is queried
in the SP, so why?
Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecord set
ID = cmdCom.Paramete rs("@RETURNVALU E")
"Light" <a@a.comwrote in message
news:uO******** ******@TK2MSFTN GP03.phx.gbl...
Hi Norman,

Again, thank.

Sp aside, I thought the 1st post already give you my VBScript.

I tried the Scope_Identify( ), but that didn't help. As for the return,
Gregory in his reply also said it needed.

If the problem is not with the SQL server Exp, then how come the SQL Sever
2000 works?

That is somethng I can't understand.

Norman Yuan wrote:
>From your SP, I do not see your issue is caused by this SP, however, the
SP is not well written, IMO.

Firstly, do not use @@Idehtity, use SCOPE_IDENTITY( ) instead. There are
quite some discussion on this topic. In your case, @@Identity may not be
guranteed as the identity of the record you just inserted into Compony
table, if another inserting occurs at the nearly exactly the same moment,
say right after your inserting but before your SELECT @Return_Value is
executed.

Secondly, you have already define @return_Value as OUTPUT paramter, there
is no need to pass it as return value. in SP, RETURN somevalue is meant
for return a value to indicate the SP execution status (succeeded,
failed, or partially executed...). Do not confuse it with OUTPUT
parameter. In most SP, you can simply leave it as

Create Procedure
As
...
...
RETURN

SQL Server will pass it a default value.

However, these should not affect your VBScript getting unexpected result,
as your OP said.

As I mentioned, you need post the VBScript code to show how do you create
ADODB.Comman d and its parameter, which you omitted in your OP. I strongly
suspect your VB code is wrong on how you build ADODB.Command, its
Parameters and how you get the OUTPUT value from the parameters. Until
seeing that part of VB code, I can say more.

"Light" <a@a.comwrote in message
news:%2******* *********@TK2MS FTNGP04.phx.gbl ...
>>>Thanks Norman.

Since it is too late for me to move this post to the other NG, I'l keep
it here for now.

The new company table is created correctly.

The connection string:
Here is the stored procedure, with some mod to make it short:

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id( N'[dbo].[NewCompany]') and OBJECTPROPERTY( id,
N'IsProcedur e')=1)
drop procedure [dbo].[NewCompany]
GO

CREATE Procedure NewCompany
(
@varCompanyN ame varchar (50) = null,
:
:
@RETURN_VALU E int OUTPUT
)
As
INSERT INTO tblCompany(varC ompanyName)
values
(@varCompany Name )
SELECT @RETURN_VALUE = @@identity
return @RETURN_VALUE
Norman Yuan wrote:

Although this is not a NG for COM/ADO, but...

Could you post your store procedure and the whole portion of VBScript
code for ccreating ADODB.Command and its parameters (you omitted some
key part of code in your first post), so that one could not tell how the
parameter is created for the ADODB.Command object. Of course this is
assume that you do have a connection that can reach SQL Server Express.
I do not think your problem is due to difference of SQL Server2000 and
SQL Server2005, unless your stored procedure has some thing that only
works in SQL Server2000, not SQL Server 2005.
"Light" <a@a.comwrote in message
news:%2**** ************@TK 2MSFTNGP02.phx. gbl...
>Hi Gregory,
>
>Thanks for the prompt response.
>
>Just for trying, I turned on all the protocols (like tcp/ip) and I
>couldn't find the configuration for non-local conenctions. Anyway, it
>doesn't work.
>
>The connection string is fine as I could make connection to the DB and
>create the new table.
>
>And yes, the stored procedure did do a
>return @RETURNVALUE or the SQL Server 2000 version won't work either.
>
>Anyway, I also see another post by David Lozzi (Returning
>SCOPE_IDEN TITY

>from SQLDataSource and DetailsView) in this newsgroup yesterday ,
>which

>actually is the same problem as mine (except his is using ASP.Net, so
>my problem is not related to legacy ASP), so this problem is pretty
>common?
>
>Thanks again..
>
>
>
>"Cowboy (Gregory A. Beamer)" <No************ @comcast.netNoS pamMwrote
>in message news:48******** *************** ***********@mic rosoft.com...
>
>
>>More likely, you have to set the Express product to accept non-local
>>connectio ns and enable some other connection protocol than shared
>>memory. After that, examine the connection string.
>>
>>Then, check the stored procedure and make sure it is using RETURN
>>@value, or similar. Otherwise the return value is returning nothing.
>>
>>Therer is probably more I could do, but would need a deeper dive into
>>the sproc code.
>>
>>--
>>Gregory A. Beamer
>>MVP; MCP: +I, SE, SD, DBA
>>http://gregorybeamer.spaces.live.com
>>
>>********* *************** *************** ******
>>Think outside the box!
>>********* *************** *************** ******
>>"Light" <a@a.comwrote in message
>>news:ex** ************@TK 2MSFTNGP02.phx. gbl...
>>
>>
>>>Hi all,
>>>
>>>I posted this question in the sqlserver.newus ers group but I am not
>>>gettin g any response there so I am going to try it on the fine folks
>>>here:) .
>>>
>>>I inherited some legacy ASP codes in my office. The original code's
>>>backen d
>>>is using the SQL Server 2000 and I am testing to use it on the
>>>Expres s
>>>editio n.
>>>
>>>And I run into the following problem.
>>>
>>>One of the pages is using a stored procedure to create a new table
>>>and
>>>return the value of the new table ID.
>>>
>>>The codes are written in VBScript, briefly, it is as follows:
>>>
>>>set cmd = Server.CreateOb ject("ADODB.Com mand")
>>>cmd.Acti veConnection= dbConnection
>>>cmd.Comm andText = "NewCompany "
>>>cmd.Comm andType = adCmdStoredProc
>>>
>>>cmd.Para meters("@varCom panyName") = Request.Form("C OMPANYNAME")
>>>:
>>>:
>>>cmd.Para meters("@RETURN VALUE") = 0
>>>
>>>Set rsID = cmdCom.Execute
>>>Set rsID = rsID.NextRecord set
>>>ID = cmdCom.Paramete rs("@RETURNVALU E")
>>>
>>>On the SQL Server 2000, the ID is returned correctly, but the Express
>>>doesn' t. In fact, it didn't fill in the vat all. For example, If I
>>>set the
>>>RETURNVA LUE to -5 before the execute,
>>>the new value remains -5. And I could see the table is created
>>>correctl y
>>>with the proper id. It is just the return value received at the
>>>client web
>>>browse r is never set.
>>>
>>>So my question is, is there any change I need to make for the
>>>Express' s
>>>stored procedure?
>>>
>>>Any suggestion is highly appreciated. This problem is kind of
>>>importan t as a lot of our customers are migrating their SQL servers
>>>from 2000 to the 2005.
>>>
>>>TIA
>>>
>>

Apr 1 '07 #9
Did you turn on the SQL Browser service? That is part of the requirement. By
default, SQL Server, except, I think, Enterprise (perhaps Standard) installs
only allowing local connections. You need both a protocol and the browser
service to attach to it.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************** *************** ***************
Think outside the box!
*************** *************** ***************
"Light" <a@a.comwrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
Hi Gregory,

Thanks for the prompt response.

Just for trying, I turned on all the protocols (like tcp/ip) and I
couldn't find the configuration for non-local conenctions. Anyway, it
doesn't work.

The connection string is fine as I could make connection to the DB and
create the new table.

And yes, the stored procedure did do a
return @RETURNVALUE or the SQL Server 2000 version won't work either.

Anyway, I also see another post by David Lozzi (Returning SCOPE_IDENTITY
from SQLDataSource and DetailsView) in this newsgroup yesterday , which
actually is the same problem as mine (except his is using ASP.Net, so my
problem is not related to legacy ASP), so this problem is pretty common?

Thanks again..

"Cowboy (Gregory A. Beamer)" <No************ @comcast.netNoS pamMwrote in
message news:48******** *************** ***********@mic rosoft.com...
>More likely, you have to set the Express product to accept non-local
connections and enable some other connection protocol than shared memory.
After that, examine the connection string.

Then, check the stored procedure and make sure it is using RETURN @value,
or similar. Otherwise the return value is returning nothing.

Therer is probably more I could do, but would need a deeper dive into the
sproc code.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

************** *************** *************** *
Think outside the box!
************** *************** *************** *
"Light" <a@a.comwrote in message
news:ex******* *******@TK2MSFT NGP02.phx.gbl.. .
>>Hi all,

I posted this question in the sqlserver.newus ers group but I am not
getting any response there so I am going to try it on the fine folks
here:).

I inherited some legacy ASP codes in my office. The original code's
backend
is using the SQL Server 2000 and I am testing to use it on the Express
edition.

And I run into the following problem.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateOb ject("ADODB.Com mand")
cmd.ActiveCon nection= dbConnection
cmd.CommandTe xt = "NewCompany "
cmd.CommandTy pe = adCmdStoredProc

cmd.Parameter s("@varCompanyN ame") = Request.Form("C OMPANYNAME")
:
:
cmd.Parameter s("@RETURNVALUE ") = 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecord set
ID = cmdCom.Paramete rs("@RETURNVALU E")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set
the
RETURNVALUE to -5 before the execute,
the new value remains -5. And I could see the table is created correctly
with the proper id. It is just the return value received at the client
web
browser is never set.

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important
as a lot of our customers are migrating their SQL servers from 2000 to
the 2005.

TIA

Apr 2 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
569
by: fripper | last post by:
I posted this problem a couple of days ago but felt I might have better luck re-stating the problem. Apparently I messed up IIS (v. 5) somehow because I am suddenly unable to load web forms! A simple example will help. I created a simple web project that contains two simple forms ... WebForm1 and WebForm2. WebForm1 has a button which when clicked simply does a Response.Redirect("WebForm2.aspx"). When I run the program and click the...
8
9771
by: Sarah | last post by:
I need to access some data on a server. I can access it directly using UNC (i.e. \\ComputerName\ShareName\Path\FileName) or using a mapped network drive resource (S:\Path\FileName). Here is my problem: my vb.net program has problems with UNC. If the UNC server is restarted or goes off-line, my VB.net program crashes. The code for UNC access to the file is included below and is put in the tick event of a form timer control running every...
2
7831
by: stuart.d.jones | last post by:
Hi, I'm trying to write an application which will eventually use SQL Express running on a server, with a client app running on several client machines. I'm developing it with an instance of SQL Express running on my local machine, however. I want to use the built-in data functionality of VB.NET 2005, but how do I set this up to work in VB.NET Express 2005 for future deployment - or can't I? If I can't, is VB the limitation, or SQL...
1
1359
by: lord.zoltar | last post by:
Hi, I'm wondering how I deploy a database with an application. I know that I can require SQLServer Express to be installed, using the ClickOnce deployment system, but I also need to know how I can include the database files and have them installed into SQLServer Express as a part of the installation process. I'm running VBExpress, SQLServer Express (but I also have access to SQL Server 2000, full version). thanks.
2
1821
by: TTT | last post by:
Hi, I have developed a website from the model "Starter kits Classified". Well, the application works good, no problem in my development environment. It uses a SqlServer 2005 express database (using *.MDF files). But, when i try to copy it to a IIS website, it dosent work. - I created a website called "MyApp" under IIS (windows xp pro) - I copied all the files from my "Visual Studio Express 2005 Web dev" to the IIS/Default...
1
3267
by: Marvinq | last post by:
I'm a newbie to asp.net, but I have been a programmer for years. I have a question that I'm hoping someone can give me a good answer for, I have been trying to set up a site remotely and I've been using the login control that comes with Visual Web Developer 2005 Express, this comes with SQL 2005 Express. The remote site is not a company computer, but basically a host that I'm paying for. When I deploy on this remote site and try to log...
9
5765
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just call it Express for simplicity). I have, to try to simplify things, put the exact same DB on two systems, one running MSDE and one running Express. Both have 2 Ghz processors (one Intel, one AMD), both have a decent amount of RAM (Intel system...
5
14437
by: Glen Buell | last post by:
Hi all, I have a major problem with my ASP.NET website and it's SQL Server 2005 Express database, and I'm wondering if anyone could help me out with it. This site is on a webhost (WebHost4Life) and was running fine and dandy, until I decided I needed to add some additional stored procedures to the database.
2
6231
by: raylopez99 | last post by:
I am trying to program a database from inside C++.NET via Visual Studio 2005 using the ADO.NET set of classes, but this I believe is a SQL Server 2005 Express permissions question under Windows XP Professional OS. In another thread, http://tinyurl.com/373lzx, I was able to open a database successfully using VS2005 (thanks to a suggestion by Erland Sommarskog). But subsequent to this I played around with the local policy permissons...
0
9796
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10782
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10500
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10543
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7753
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6951
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5624
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5789
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3078
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.