Problems Inserting DateTimeStamp into database

Here's the error I get trying to run the code posted below..."Prepared statement '(@.datestamp datetime,@.PrevValue real,@.NewValue real,@.IPaddress r' expects parameter @.datestamp, which was not supplied."

I've tried it without the quotes around the DateTime.Now and also adding the # around them. Without the quotes, I do get a different error because it doesn't like the spaces in the DateTime text. Also, the datestamp data in the database is formatted as a datetime (MS SQL Server 2005).

Here's my code:

PrivateSub UpdateRefresh()

UpdateConnection.Open()

UpdateDataAdapter.InsertCommand.CommandText = "INSERT INTO dbo.tbDeploy(datestamp," & _

"PrevValue, NewValue, IPaddress, HighCapability, LowCapability, Share) VALUES" & _

"('" & DateTime.Now & "', '" & lblCurrent.Text & "', '" & lblCurrent.Text & _

"', '" & lblIP.Text & "', '" & lblHigh.Text & "', '" & lblLow.Text & "', '" & lblShare.Text & "')"

UpdateDataAdapter.InsertCommand.ExecuteNonQuery()

UpdateConnection.Close()

EndSub

Use parameterized queries. Your problem will be solved. You can also prevent SQL Injection attacks.

Problems inserting datetime object into sql server 2005

hey everyone,

Im trying run a stored procedure, but I am getting the following message when trying to execute it.

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.

It should also be noted that when I execute this sproc from within sql management studio, it executes fine.

This is the stored procedure im trying to execute...

CREATE PROCEDURE [dbo].[sp_UpdateRequest]

@.request_id INT,
@.request_client_id INT,
@.request_description VARCHAR(150), @.request_date DATETIME,
@.request_edit_date DATETIME, @.request_status VARCHAR(25), @.request_approve_date DATETIME,
@.request_activity_code VARCHAR(7), @.request_department_code VARCHAR(3), @.request_participants INT,
@.request_activity_date DATETIME, @.request_activity_due_date DATETIME,
@.request_company_id INT, @.request_company_code INT,
@.request_notes VARCHAR(2000)

AS
BEGIN

UPDATE invoice_requests

SET request_client_id = @.request_client_id, request_description = @.request_description,
request_date = @.request_date, request_edit_date = @.request_edit_date,
request_status = @.request_status, request_approve_date = @.request_approve_date,
request_activity_code = @.request_activity_code,
request_department_code = @.request_department_code,
request_participants = @.request_participants,
request_activity_date = @.request_activity_date,
request_activity_due_date = @.request_activity_due_date,
request_company_code = @.request_company_code, request_company_id = @.request_company_id,
request_notes = @.request_notes

WHERE request_id = @.request_id

END
GO

in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...

please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...

exec sp_executesql
N'EXECUTE sp_UpdateRequest
@.request_id,
@.request_client_id,
@.request_description,
@.request_date,
@.request_edit_date,
@.request_status,
@.request_approve_date,
@.request_activity_code,
@.request_participants,
@.request_activity_date,
@.request_activity_due_date,
@.request_company_id,
@.request_company_code,
@.request_notes',

N'@.request_id int,
@.request_client_id int,
@.request_description varchar(8000),
@.request_date datetime,
@.request_edit_date datetime,
@.request_status varchar(5),
@.request_approve_date datetime,
@.request_activity_code varchar(8000),
@.request_department_code varchar(8000),
@.request_participants int,
@.request_activity_date datetime,
@.request_activity_due_date datetime,
@.request_company_id int,
@.request_company_code int,
@.request_notes varchar(8000)',

@.request_id=5,
@.request_client_id=1,
@.request_description='',
@.request_date=''2007-11-22 16:34:32:997'',
@.request_edit_date=''2007-11-22 16:34:32:997'',
@.request_status='Draft',
@.request_approve_date=''1970-01-01 00:00:00:000'',
@.request_activity_code='',
@.request_department_code='',
@.request_participants=0,
@.request_activity_date=''2007-11-29 00:00:00:000'',
@.request_activity_due_date=''2007-11-23 00:00:00:000'',
@.request_company_id=0,
@.request_company_code=1,
@.request_notes=''

any help on this would be greatly appreciated.
Cheershey everyone, when i try to run the the following trace result as a query, i get an error near 2007...

exec sp_executesql N'EXECUTE sp_UpdateRequest @.request_id, @.request_client_id, @.request_description, @.request_date, @.request_edit_date, @.request_status, @.request_approve_date, @.request_activity_code, @.request_participants,
@.request_activity_date, @.request_activity_due_date, @.request_company_id, @.request_company_code, @.request_notes',N'@.request_id int,@.request_client_id int,@.request_description varchar(6),@.request_date datetime,@.request_edit_date
datetime,@.request_status varchar(5),@.request_approve_date datetime,@.request_activity_code varchar(4),@.request_department_code varchar(3),@.request_participants int,@.request_activity_date datetime,@.request_activity_due_date
datetime,@.request_company_id int,@.request_company_code int,@.request_notes varchar(11)',@.request_id=1,@.request_client_id=1,@.r equest_description='Justin',@.request_date=''2007-11-23 08:59:04:250'',@.request_edit_date=''2007-11-23
08:59:04:250'',@.request_status='Draft',@.request_ap prove_date=''1970-01-01 00:00:00:000'',@.request_activity_code='0000',@.requ est_department_code='022',@.request_participants=50 ,@.request_activity_date=''2007-11-23
00:00:00:000'',@.request_activity_due_date=''2007-11-30 00:00:00:000'',@.request_company_id=5292,@.request_c ompany_code=1,@.request_notes='Other Notes'

are there meant to be the double single quotes('') before and after the date?

Cheers,|||are there meant to be the double single quotes('') before and after the date?No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP|||No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP

I'm not sure because it's already surrounded by single quotes.

maybe you can try '''2007-11-23 08:59:04:250'''|||Sorry, I should have looked at your whole example.

There are multiple ways that I could parse what you've posted. The one that makes the most sense to me at the moment is:exec sp_executesql N'EXECUTE sp_UpdateRequest
@.request_id, @.request_client_id, @.request_description
, @.request_date, @.request_edit_date, @.request_status
, @.request_approve_date, @.request_activity_code, @.request_participants
, @.request_activity_date, @.request_activity_due_date, @.request_company_id
, @.request_company_code, @.request_notes'', N''@.request_id int
, @.request_client_id int, @.request_description varchar(6), @.request_date datetime
, @.request_edit_date datetime, @.request_status varchar(5), @.request_approve_date datetime
, @.request_activity_code varchar(4), @.request_department_code varchar(3), @.request_participants int
, @.request_activity_date datetime, @.request_activity_due_date datetime, @.request_company_id int
, @.request_company_code int, @.request_notes varchar(11)''
, @.request_id = 1, @.request_client_id = 1, @.request_description = ''Justin''
, @.request_date = ''2007-11-23 08:59:04:250'', @.request_edit_date = ''2007-11-23 08:59:04:250''
, @.request_status = ''Draft'', @.request_approve_date = ''1970-01-01 00:00:00:000''
, @.request_activity_code = ''0000'', @.request_department_code = ''022'', @.request_participants = 50
, @.request_activity_date = ''2007-11-23 00:00:00:000'', @.request_activity_due_date = ''2007-11-30 00:00:00:000''
, @.request_company_id = 5292, @.request_company_code = 1, @.request_notes = ''Other Notes''
'See if that represents what you think you meant, and let me know how it works for you.

-PatPsql

Problems importing a datetime from a flat file

I have a flat file with a datetime field as follows

12419,1,'T','P',229.72,2,'N',2004/may/05 19:47:42.546001
12419,1,'T','R',605.38,76,'N',2004/may/05 19:47:42.546000
12419,2,'T','P',110.49,2,'N',2004/may/05 19:47:42.546003
12419,2,'T','R',215.53,11,'N',2004/may/05 19:47:42.546002
12419,3,'T','F',9.29,1,'N',2004/may/05 19:47:42.546005
12419,3,'T','R',696,38,'N',2004/may/05 19:47:42.546004

I can NOT get last field i.e. the date time field to import into SQL werver 2005 using import wizard.

I can manually enter the value in the table so I know it can handle data in the format it is in.. (table field is date time) but I cant get import wizard to read the file and bring in the data.. It bombs at coloumn 7... which is the date field.. It works fine if I delete the milisecond portion i.e. anything after the dot but I need to keep that information.

Any help will be greatly appreciated.

What type does the SSIS package think the column is? DT_DBTIMESTAMP? DT_DATE? DT_DBDATE? DT_STR? DT_WSTR?

What errors so you get?

Please provide more information.

I strongly suspect that the value is not in a format that SSIS can understand. Your ability to type the value into a column is irrelevant - the informaiton is not stored this way

-Jamie

|||

I have tried with all column options i.e. DT_DBTIMESTAMP? DT_DATE? DT_DBDATE? DT_STR? DT_WSTR?

In every instance it comes back with error message saying

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 4" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

When I remove the miliseconds i.e. anything after the decimal point in date time field it works fine.. It also works fine if I have the datetime in the following format

'2004-05-05 19:47:42.547000000' Instead of 2004/may/05 19:47:46.562003

Hope this helps

Thx

Amit

|||

This behaviour is as expected. SSIS will not recognise yyyy/mon/dd hh:mi:ss:XXXXXX. That is not a valid format.

You will need to import it as a string and do a manual conversion using the Derived Column component.

-Jamie

|||

Thank you for your response Jamie. You confirmed my fears. I find it rather surprising that SSIS will read upto seconds but will not read milliseconds portion.. Anyway.. weird are the ways of microsoft and/or other software vendors.. :) Every software has its quirks.. Wish they didnt..

Thank you though for all your help

Thx

Amit

|||

Well those aren't milliseconds. A millisecond is a thousandth of a second hence it will read to hh:mi:ss:XXX but not hh:mi:ss:XXXXXX and I dare say you won't find any other tool that will either!

-Jamie

problems converting dates as varchar to datetime data type

Hi all,

I need to migrate from a DB with a column containing dates as varchar to a new DB which will have a column with the same content as the source DB but in datetime format.

I have tried to use the cast function but i always get a message saying that the conversion result in an out of range error.

Someone can help me? PLEASE

Thanks

In what format do you have your dates at the moment?

You can try using SET DATEFORMAT dmy before running the statment.

Or look at using CONVERT with the relevant style.

eg CONVERT(DATETIME, YourDateField, 103)

Check Books Online for more details

HTH!

|||Try CONVERT function, make sure the style parameter matches your varchar date format
|||

If your date is in a recognizable format SQL Server uses an implicit conversion from varchar to datetime, so no CONVERT or CAST function is necessary. If the date is in an unrecognizable format you can use the T-SQL string functions (listed in BOL) to reconstruct it into a recognizable format and you should get no errors.

|||

Hi,

I will post some more information about my problem.

In the source DB i have the "Beginning_Date" column, as varchar, with values with this format "dd/mm/yyyy HH.MM.SS".

In the target DB i have the "Beginning_Date" column again, but must be only with the the date part (dd/mm/yyyy) as datetime type, and the "Beginning_Time" column, storing the time part (HH:MMS) as nchar type.

I have no problems with the time part of the records, but the date part always fail.

I use the substring function in order to get only the date part of the records and try to insert it to the target DB both with "dd/mm/yyyy" and "mm/dd/yyyy" date formats. I always get the same error (out of range). I have tried also the "set datetime dmy" .......

Thanks for your help,

Bob

|||
Are all your dates in Beginng_Date in the same format? Are you sure there isn't a rogue value in there which is causing the whole statement to crash?

Can you post your DDL and DML statements and some sample data for us to help troubleshoot further?

I had a guess at what you were trying to do:

Code Snippet

CREATE TABLE tblDateTime
(d1 DATETIME, t1 NCHAR(10))

DECLARE @.String NVARCHAR(30)
SET @.String = '21/12/2007 14:15:15'

SET DATEFORMAT dmy

INSERT INTO tblDateTime
SELECT SUBSTRING(@.String, 1, 10), REVERSE(SUBSTRING(REVERSE(@.String), 0, 9))

Is this anything like what you're doing?

|||

Hi,

The problem is solved now.

richbrownesq, you were right. My problem was only one (is enough...) record wrong.

Thanks a lot for ALL!!!!