problem exectuing storded procedure

[Jan Schmidt]

Hi NG,

I've got an ASP Script (useing Javascript) to which i send data to by POST.
This data stored in variables will be transferred to SQL stored Procedure.
As shown at bottom...
welches eine Variable übergeben
There is coming up an error...
I don't know how to solve it,
please help or give me a hint...
[asp code line 665 and + from Functions.asp]
var TopicName=String(Request.Form("TopicName"));
var TopicContent=String(Request.Form ("TopicContent"));
var strSQL="EXECUTE Topic @TopicTitle=["+TopicName+"],
@TopicContent=["+TopicContent+"]";
cmd.CommandText = strSQL;
cmd.Execute;
oDB_connect.close;
[/asp code]

[error page]
Fehlertyp:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
'0123456789012345678901234567890123456789012345678 901234567890123456789012345678
9012345678901234567890123456789012345678901234567' ) ist zu lang. Die
Maximallänge beträgt 128.
/Functions.asp, line 669
[/error page]

[snip from Stored Procedure]
CREATE PROCEDURE dbo.Topic @TopicTitle VARCHAR(20), @TopicContent VARCHAR(200)
AS INSERT INTO Table.........etc
[/snip from Stored Procedure]

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jan Schmidt (hi*****@gmx.net) writes:

[asp code line 665 and + from Functions.asp]
var TopicName=String(Request.Form("TopicName"));
var TopicContent=String(Request.Form ("TopicContent"));
var strSQL="EXECUTE Topic @TopicTitle=["+TopicName+"],
@TopicContent=["+TopicContent+"]";
cmd.CommandText = strSQL;
cmd.Execute;
oDB_connect.close;
[/asp code]

[error page]
Fehlertyp:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
'0123456789012345678901234567890123456789012345678 90123456789012345678901234
5678 9012345678901234567890123456789012345678901234567' ) ist zu lang. Die
Maximallänge beträgt 128.
/Functions.asp, line 669
[/error page]

Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.

For some reason you are putting brackets around the parameters to your
stored procedure. In Transact-SQL, brackets are used to delimit
identifiers; this is to permit you have table and column names with
special charcters such as space in them. Thus, in this case your parameters
are parsed as identifiers, and obviously at least one of them is longer
than 128 which is the maximum length for the optimizer.

Unless you are into something special, you should use ' instead to delimit
the parameters, but don't rush and change this, read on instead. You
cannot pass user input directly into an SQL string like this. Say that you
actually replaced the brackets with single quotes instead. Say then that
the user enters data with a single quote in it. The result: a syntax
error. Maybe. A malicious user can use this to enter a completely
different SQL command than you had intended. Thus, you have a big security
hole. And, no, don't laugh. SQL injection is a very common means of attack
on the web today.

A simple way out is to run the intput through a procedure that double
all single quotes in the input. That is, if the user enters "O'Brien",
you pass "O''Brien" to SQL Server. This is then parsed as O'Brien.
However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jan Schmidt]

"Erland Sommarskog" schrieb

Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.

You've got a special Webpage to read the differences between this two methods?
I tried to make a simple SQL request via Query Analyzer, like this:

Declare @TopicTitle varchar(40),
@TopicContent varchar(200)
set
@TopicContent="12345678901234567890123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567890"
set @TopicTitle="test3";
INSERT INTO Themen(Themen_Name, Themen_Content) VALUES(@TopicTitle,
@TopicContent)
 

and also getting the same error:
[error]
Server: Nachr.-Nr. 103, Schweregrad 15 ...
Identifier (begins with '1234567...') is too long. The Maximum length is 128.
[/error]

[used table]

CREATE TABLE [dbo].[Themen] (
[Thema_Nr] [int] IDENTITY (1, 1) NOT NULL ,
[Themen_Name] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[Themen_Content] [varchar] (200) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Themen] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Thema_Nr]
) ON [PRIMARY]
GO

[/used table]

[David Gugick]

You're using double quotes. You need to use single quotes. Double quotes are
used for identifiers like brackets are.

--
David Gugick
Imceda Software

[Jan Schmidt]

found my mistake ,-)
the " was wrong

[Jan Schmidt]

"Erland Sommarskog" schrieb

However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.

hmm, can you give me an example code please?
i searched around a bit for asStoredProcedure but didn't get a result for it.
perhaps it's the best and easiest way for me to handle my problem

regards

Jan

[Erland Sommarskog]

Jan Schmidt (hi*****@gmx.net) writes:

"Erland Sommarskog" schrieb

However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.
hmm, can you give me an example code please? i searched around a bit for
asStoredProcedure but didn't get a result for it. perhaps it's the
best and easiest way for me to handle my problem

Sorry, the name of the constant is adCmdStoredProc.

I can't give a example in ASP, since I don't know ASP. This snippet is
Visual Basic, and shows how to changes the password for the user abc:

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=MyServer" & _
"Initial Catalog='tempdn'; _
"Integrated Security='SSPI';"
cnn.ConnectionTimeout = 5
cnn.Open
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "master.dbo.sp_password"
cmd.Parameters.Append _
cmd.CreateParameter("@old", adVarChar, adParamInput, 10, "nisse")
cmd.Parameters.Append _
cmd.CreateParameter("@new", adVarChar, adParamInput, 10, "pelle")
cmd.Parameters.Append
cmd.CreateParameter("@login", adVarChar, adParamInput, 10, "abc")
cmd.Execute
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
"Erland Sommarskog" schrieb

Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.

You've got a special Webpage to read the differences between this two
methods?

The section Deprecated Components in the MDAC Books Online (this material
is also in MSDN Library) says:
ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the
OLE DB NET Data Provider.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jan Schmidt]

"Erland Sommarskog" schrieb

cmd.CommandType = adCmdStoredProc

Yes, i did what you said,
everything seams to be good, except, as ai said, i use asp with Javascript not
VB,
so it says there isn't any function called adCmdStoredProc
i also tried adCmdStoredProcedure and adStoredProc and adStoredProcedure and
StoredProc and StoredProcedure.
Why?

regards
Jan

[Erland Sommarskog]

Jan Schmidt (hi*****@gmx.net) writes:

"Erland Sommarskog" schrieb

cmd.CommandType = adCmdStoredProc

Yes, i did what you said, everything seams to be good, except, as ai
said, i use asp with Javascript not VB, so it says there isn't any
function called adCmdStoredProc i also tried adCmdStoredProcedure and
adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.

adCmdStoredProc is not a procedure but a constant.

Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.

If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.

A quick searh on Google, reveals that the value for adCmdStoredProc is 4,

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ultradiv]

I have Macromedia Dreamweaver and this is the code it produced to run an ADO
command for a stored procedure:

var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = Yuor_Connection_String;
Command1.CommandText = "sp_yourStoredProc";
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParamete r("@RETURN_VALUE", 3, 4));
Command1.Parameters.Append(Command1.CreateParamete r("@SubscriberID", 3,
1,4,local_var_SubscriberID));
Command1.Parameters.Append(Command1.CreateParamete r("@Email", 200,
1,50,local_var_Email));
Command1.Parameters.Append(Command1.CreateParamete r("@Name", 200,
1,50,local_var_Name));
var Recordset1 = Command1.Execute();

You'll notice of course the CommandType = 4

HPH
Andy

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Jan Schmidt (hi*****@gmx.net) writes:

"Erland Sommarskog" schrieb

cmd.CommandType = adCmdStoredProc

Yes, i did what you said, everything seams to be good, except, as ai
said, i use asp with Javascript not VB, so it says there isn't any
function called adCmdStoredProc i also tried adCmdStoredProcedure and
adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.

adCmdStoredProc is not a procedure but a constant.

Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.

If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.

A quick searh on Google, reveals that the value for adCmdStoredProc is 4,

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Daniel Crichton]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Jan Schmidt (hi*****@gmx.net) writes:

"Erland Sommarskog" schrieb

cmd.CommandType = adCmdStoredProc

Yes, i did what you said, everything seams to be good, except, as ai
said, i use asp with Javascript not VB, so it says there isn't any
function called adCmdStoredProc i also tried adCmdStoredProcedure and
adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.

adCmdStoredProc is not a procedure but a constant.

Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.

If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.

Try the file adojavas.inc in C:\Program Files\Common Files\System\ado

The VBScript include is in the same dir (adovbs.inc)

:)

Dan