Re: Insert Error: Column name or number of supplied values does not match table definition.
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 15 May 2007 22:12:29 +0000 (UTC)
Jason (JasonJason@xxxxxxxxxxxxx) writes:
dFrom="03/01/2006"
dTo="05/01/2007"
sDatePart="m"
sTempTableName = "#TempChartData_410"
dbSQL = "set nocount on; " & _
"if object_id('tempdb.." & sTempTableName & "','U') is not null " & _
" Drop Table " & sTempTableName & "; " & _
"Create Table " & sTempTableName & " (FromDate datetime, ToDate
datetime); " & _
A possible reason for the error is that the table existed prior to
submitting the batch, but with a different definition. In such case,
compilation of the batch will fail. The rememedy would be to run the
DROP thing separately. Then again, since you connect here and now,
the table could not exist at all.
"declare @Diff int; " & _
"declare @BeginDate datetime; " & _
"declare @EndDate datetime; " & _
"set @BeginDate = cast('" & dFrom & "' as datetime); " & _
"set @EndDate = cast('" & dTo & "' as datetime); " & _
You need to learn about parameterised statements. There are several
problems with interpolating input values into the query string. The
most serious thing in a web app is SQL injection, by which a malicious
user can cause your code to do something you did not intend it to.
There are also performance considerations.
In this particular case, there is also the problem with date literals.
Is 03/01/2006, Jan 3rd 2006 or something else? If you use parameterised
statements, that will be interpreted according to regional settings
on the web host, which is probably less surprising than having SQL
Server to do it.
Note that to use parameterised statements, you need to create temp table
in a separate, non-parameterised batch.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- References:
- Insert Error: Column name or number of supplied values does not match table definition.
- From: Jason
- RE: Insert Error: Column name or number of supplied values does not match table definition.
- From: Walter Wang [MSFT]
- Re: Insert Error: Column name or number of supplied values does not match table definition.
- From: Jason
- Insert Error: Column name or number of supplied values does not match table definition.
- Prev by Date: Re: Insert Error: Column name or number of supplied values does not match table definition.
- Next by Date: Re: Insert Error: Column name or number of supplied values does not match table definition.
- Previous by thread: Re: Insert Error: Column name or number of supplied values does not match table definition.
- Next by thread: Re: Insert Error: Column name or number of supplied values does not match table definition.
- Index(es):
Relevant Pages
|
