August 20, 2014 at 9:25 am
I am working with a Classic ASP file upload application which uses the Recordset.AddNew functionality to insert to a SQL Server 2008 database.
There is a form that submits data to this application, and the application (using Recordset object) gets the form variables, like below:
Fig A.
Set RS=Server.CreateObject("ADODB.Recordset")
RS.AddNew
RS("A_Category")=Form("strA_Category")
RS("SP_VersionYear")=Form("strSP_VersionYear")
The Form("strSP_VersionYear") is a hidden variable that holds a string variable, set in the form page as:
strSP_VersionYear="2014"
When this variable is inserted into the database, it is into a column of DateTime data type. Inserting the year only, in a string just like the above, has worked with a SQL statement (in an Insert statement), in which it "defaults" to 2014-01-01 00:00:00.000, but, for some reason, it throws an error (below) when trying to insert via a Recordset (as illustrated above, in Fig A).
Provider error '80020005' Type mismatch
Is there a way that this can be formatted to successfully insert into a DateTime column? Thanks for any help.
(Below is the table in which the Insert has worked successfully (Fig B), followed by the table in which the Recordset AddNew is not working (Fig C) - the column is named A_Date_Created in each):
Fig B
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AMS_StrategicPlan_2014_A](
[A_ID] [int] IDENTITY(1,1) NOT NULL,
[A_Desc] [varchar](8000) NULL,
[A_Num] [varchar](50) NULL,
[A_GUID] [varchar](200) NULL,
[A_Category] [varchar](50) NULL,
[A_SP_VersionYear] [datetime] NULL,
[A_Date_Created] [datetime] NULL,
[LastUpdate] [datetime] NULL,
CONSTRAINT [PK_AMS_StrategicPlan_2014_A] PRIMARY KEY CLUSTERED
(
[A_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Fig C
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AMS_StrategicPlan_2014_A_Docs](
[A_DocsID] [int] IDENTITY(1,1) NOT NULL,
[A_Num] [nvarchar](50) NULL,
[A_Category] [varchar](50) NULL,
[image_blob] [image] NULL,
[filename] [varchar](200) NULL,
[filesize] [decimal](18, 0) NULL,
[DocumentType] [varchar](50) NULL,
[ContentType] [varchar](200) NULL,
[FolderPath] [varchar](200) NULL,
[A_Docs_A_GUID] [varchar](200) NULL,
[A_Docs_GUID] [varchar](200) NULL,
[Date_Created] [datetime] NULL,
[SP_VersionYear] [datetime] NULL,
CONSTRAINT [PK_AMS_StrategicPlan_2014_A_Docs] PRIMARY KEY CLUSTERED
(
[A_DocsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
August 20, 2014 at 9:32 am
Why not just change your hidden field to be strSP_VersionYear="2014-01-01"? That should work for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2014 at 9:44 am
Sean Lange (8/20/2014)
Why not just change your hidden field to be strSP_VersionYear="2014-01-01"? That should work for you.
Thanks.
Why does the SQL INSERT statement succeed where the Recordset AddNew fails?
August 20, 2014 at 9:49 am
cajun_sql (8/20/2014)
Sean Lange (8/20/2014)
Why not just change your hidden field to be strSP_VersionYear="2014-01-01"? That should work for you.Thanks.
Why does the SQL INSERT statement succeed where the Recordset AddNew fails?
It is about implicit conversion. An insert statement is a bit more flexible in how it can interpret 2014. It converts that to a datetime. I suspect the Recordset object can't convert 2014 to a datetime.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2014 at 9:56 am
Sean Lange (8/20/2014)
cajun_sql (8/20/2014)
Sean Lange (8/20/2014)
Why not just change your hidden field to be strSP_VersionYear="2014-01-01"? That should work for you.Thanks.
Why does the SQL INSERT statement succeed where the Recordset AddNew fails?
It is about implicit conversion. An insert statement is a bit more flexible in how it can interpret 2014. It converts that to a datetime. I suspect the Recordset object can't convert 2014 to a datetime.
Thanks very much! 🙂
August 20, 2014 at 10:02 am
cajun_sql (8/20/2014)
Sean Lange (8/20/2014)
cajun_sql (8/20/2014)
Sean Lange (8/20/2014)
Why not just change your hidden field to be strSP_VersionYear="2014-01-01"? That should work for you.Thanks.
Why does the SQL INSERT statement succeed where the Recordset AddNew fails?
It is about implicit conversion. An insert statement is a bit more flexible in how it can interpret 2014. It converts that to a datetime. I suspect the Recordset object can't convert 2014 to a datetime.
Thanks very much! 🙂
You are welcome. Did changing your hidden field value work?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 20, 2014 at 12:13 pm
Sean Lange (8/20/2014)
cajun_sql (8/20/2014)
Sean Lange (8/20/2014)
cajun_sql (8/20/2014)
Sean Lange (8/20/2014)
Why not just change your hidden field to be strSP_VersionYear="2014-01-01"? That should work for you.Thanks.
Why does the SQL INSERT statement succeed where the Recordset AddNew fails?
It is about implicit conversion. An insert statement is a bit more flexible in how it can interpret 2014. It converts that to a datetime. I suspect the Recordset object can't convert 2014 to a datetime.
Thanks very much! 🙂
You are welcome. Did changing your hidden field value work?
Yes - I added the secondary variable (with the "-01-01" suffix concatenated onto the SP_VersionYear variable) for working with Recordsets, and it works fine.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply