Re: Insert Error: Column name or number of supplied values does not match table definition.

Hi Walter,

Here's my code:

dim sTempTableName
dim dbSQL
dim dbRS
dim dbConn
dim dFrom
dim dTo
dim sDatePart

set dbConn=server.createobject("adodb.connection")
dbConn.open "Provider=SQLOLEDB;Data Source=127.0.0.1;Initial
Catalog=database;User Id=userid;Password=password"

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); " & _
"declare @Diff int; " & _
"declare @BeginDate datetime; " & _
"declare @EndDate datetime; " & _
"set @BeginDate = cast('" & dFrom & "' as datetime); " & _
"set @EndDate = cast('" & dTo & "' as datetime); " & _
"set @Diff = datediff(" & sDatePart & ",@BeginDate,@EndDate); " & _
"while @Diff > 0 " & _
"begin " & _
" set @EndDate = dateadd(" & sDatePart & ", 1, convert(varchar(10),
@BeginDate, 101)); " & _
" insert into " & sTempTableName & " values (@BeginDate, @EndDate); " &
_
" set @BeginDate = @EndDate; " & _
" set @Diff = @Diff-1; " & _
"end "

set dbRS = dbConn.execute(dbSQL)

Jason



"Walter Wang [MSFT]" <wawang@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:9opHOUulHHA.5432@xxxxxxxxxxxxxxxxxxxxxxxxx
Hi Jason,

I've tested your code in a vbscript file (.vbs, ran with cscript.exe):

dim db
set db = CreateObject("ADODB.Connection")
db.Open "Driver={SQL Native
Client};Server=myserver;Database=AdventureWorks;Trusted_Connection=yes;"

dim fso
set fso = CreateObject("Scripting.FileSystemObject")
dim file
set file = fso.OpenTextFile("c:\1.txt")
dim text
text = file.ReadAll()

dim rs
set rs = db.Execute(text)

while not rs.EOF
WScript.Echo rs(0), rs(1)
rs.MoveNext
wend


(To ease testing, I've saved the sql in c:\1.txt and added a "Select *
from
#TempChartData_410" to the file to return a recordset)


Above code works on my side and correctly prints all records.

Would you please post your ASP code here? Thanks.


Regards,
Walter Wang (wawang@xxxxxxxxxxxxxxxxxxxx, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.



.

Relevant Pages

  • RE: Insert Error: Column name or number of supplied values does not match table definition.
    ... I've tested your code in a vbscript file ... dim fso ... #TempChartData_410" to the file to return a recordset) ...
    (microsoft.public.data.oledb)
  • Re: Connection with an SQL Server 2005 database
    ... Multiple-Step OLE DB operation generated errors. ... Dim Dbcon As New ADODB.Connection ... Dim DbRs As New ADODB.Recordset ...
    (microsoft.public.outlook.program_vba)
  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)
  • Re: Calculating Percentiles
    ... It seems that what you want then is not the percentile, ... If you want to call it repeatedly on the same recordset, ... Dim rsPercentile as DAO.Recordset ... ' Ascertain the quantile rank for a given score from the numeric ...
    (microsoft.public.access.modulesdaovba)
  • Re: A simple problem with MoveFirst
    ... update the table (rather than using the recordset) means that from ADO's ... Dim tbDataToBeEmailed As ADODB.Recordset ... tbDataToBeEmailed.Open "tbDataToBeEmailed", cnCurrent, adOpenKeyset, ... "You have not entered time into the ACIS ...
    (microsoft.public.access.modulesdaovba)
Loading