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
'0123456789012345678901234567890123456789012345678901234567890123456789012345678
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][posted and mailed, please reply in news]
Jan Schmidt (histery@.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
>
'012345678901234567890123456789012345678901234567890123456789012345678901234
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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"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:
[code]
Declare @.TopicTitle varchar(40),
@.TopicContent varchar(200)
set
@.TopicContent="12345678901234567890123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567890"
set @.TopicTitle="test3";
INSERT INTO Themen(Themen_Name, Themen_Content) VALUES(@.TopicTitle,
@.TopicContent)
[/code]
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]|||You're using double quotes. You need to use single quotes. Double quotes are
used for identifiers like brackets are.
--
David Gugick
Imceda Software|||found my mistake ,-)
the " was wrong|||"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|||Jan Schmidt (histery@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"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|||Jan Schmidt (histery@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||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.CreateParameter("@.RETURN_VALUE", 3, 4));
Command1.Parameters.Append(Command1.CreateParameter("@.SubscriberID", 3,
1,4,local_var_SubscriberID));
Command1.Parameters.Append(Command1.CreateParameter("@.Email", 200,
1,50,local_var_Email));
Command1.Parameters.Append(Command1.CreateParameter("@.Name", 200,
1,50,local_var_Name));
var Recordset1 = Command1.Execute();
You'll notice of course the CommandType = 4
HPH
Andy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.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
