Error converting to DateTime/SmallDateTime

I am trying to add some datetime values into a table. However, the database keeps throwing the following error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

The code is as follows:

string start = dateCal.SelectedDate.ToString().Substring(0, 10) + " " + startTB.Text;

string end = dateCal.SelectedDate.ToString().Substring(0, 10) + " " + endTB.Text;

//DateTime starts = Convert.ToDateTime(start);

//DateTime ends = Convert.ToDateTime(end);

sqlInsert.CommandText = "INSERT INTO aspnet_reportdate VALUES ('" + refno + "', '" + start + "', '" + end + "')";

where startTB and endTB contains a time in valid format (HH:MM)

The same error would occur even if I converted the string to a DateTime object before I try to insert it into the database.

a sample value of start would be "6/19/2007 10:00"

What puzzles me is that only the error is only thrown by the database (SQL Server Express) but not C#

I have tried using both datetime and smalldatetime for the columns in question in the database also.

There is no problem with your SQL Server or C#. The problem is on your input data (mismatched date format).

I strongly recommand to change your code as follow as. It is really very dangerous code you are writing (SQL Injuction).

Don't use Dynamic Query generation on the UI. Beaware of SQL Injunction. (use parameters or Stored Procs)

The following code will work for you, (the sql server parse the param before executing it, so you can easily identify the problem)

Code Snippet

sqlInsert.CommandText = "INSERT INTO aspnet_reportdate VALUES (@.refno, @.start, @.end)";

sqlInsert.Parameters.Add(new SqlParameter("@.refno", DbType.String));

sqlInsert.Parameters.Add(new SqlParameter("@.start", DbType.DateTime));

sqlInsert.Parameters.Add(new SqlParameter("@.end", DbType.DateTime));

sqlInsert.Parameters[0].Value = refno;

sqlInsert.Parameters[1].Value = starts;

sqlInsert.Parameters[2].Value = ends;

sqlInsert.ExecuteNonQuery();

|||

Just to add to what Mani has said, if you do this kind of thing, the best way to get help is to add a print statement, messagebox, etc and print out the SQL statement that you are trying to execute. You can't use profiler with express (at least not with a license to the real tools), but if you can, using profiler you can see the statement that you are trying to execute. I would strongly consider purchasing a developer license to get the tools to work with. Like Mani says, use a parameterized statement in all cases possible, but if you are trying to build a reproducible script, that might not be possible.

The first most important step in a process like this is to figure out what you are trying to execute and take that statement to Management Studio (Express will do for this) and work out what the issue is in the query.

Date values should ideally use the standard formats. Look up "datetime data type, formats" in the index, and check out the ODBC timestamp format. It will always work.

|||

thx all for the help

just a further question. what is sql injunction and what problems does it bring about?

couldn't seem to find anything substantial on it

|||

Louis Davidson wrote:

Just to add to what Mani has said, if you do this kind of thing, the best way to get help is to add a print statement, messagebox, etc and print out the SQL statement that you are trying to execute. You can't use profiler with express (at least not with a license to the real tools), but if you can, using profiler you can see the statement that you are trying to execute. I would strongly consider purchasing a developer license to get the tools to work with. Like Mani says, use a parameterized statement in all cases possible, but if you are trying to build a reproducible script, that might not be possible.

will bear this in mind

most of what i do now is more for interest or sch work, so my choice of tools are more restricted.

error converting the char value BUT IT IS A CHAR ALREADY

SQL Server 2000 SP3a
Access 2000 SP3 (using adp)
Im going mildly loopy, hopefully someone can aid my sanity.
Table in SQL Server had a column (column_a tinyint), data contained is just
ones and zeros. In Enterprise Manager I changed the data type of column_a t
o
char(1), so that I could then go and replace the ones and zeros with 'Y' and
'N'
When I try and change one of the values, through either Access or Enterprise
Manager, it errors with:
"Syntax error converting the char value 'Y' to a column of data type int"
I don't understand, I've changed the columns data type from tinyint to
char(1), so surely these updates should work. Whats the problem with this?
I've taken the db offline, I've even tried booting the server (clutching at
straws). What might not have been updated correctly?
Any suggestions would be great!
Its these stupid things which should be a two second job, but end up taking
me hours. And yes, if I'd got it right in the first instance, then this
wouldn't have been a problem :)
Thanks for any replies.I would have thought that the logical way to proceed would have been:
1. Add a field of Char(1) to the table with a different name than the source
field
2. Update this new field to 'Y' and 'N' based on the source field's values
3. Remove the source field
4. Rename the new field to that of the source field.
I'm surprised that SQLEM would even let you change a TinyInt to a Char in th
e
first place. If it did, what values are in that field?
Thomas|||1- Be sure it has been changed. See deifnition using view
information_schema.columns
2 - Try doing the update from query analyzer.
update table1
set colB = substring('NY', cast(colB as int) + 1, 1)
AMB
"Steve'o" wrote:

> SQL Server 2000 SP3a
> Access 2000 SP3 (using adp)
> Im going mildly loopy, hopefully someone can aid my sanity.
> Table in SQL Server had a column (column_a tinyint), data contained is jus
t
> ones and zeros. In Enterprise Manager I changed the data type of column_a
to
> char(1), so that I could then go and replace the ones and zeros with 'Y' a
nd
> 'N'
> When I try and change one of the values, through either Access or Enterpri
se
> Manager, it errors with:
> "Syntax error converting the char value 'Y' to a column of data type int"
> I don't understand, I've changed the columns data type from tinyint to
> char(1), so surely these updates should work. Whats the problem with this
?
> I've taken the db offline, I've even tried booting the server (clutching a
t
> straws). What might not have been updated correctly?
> Any suggestions would be great!
> Its these stupid things which should be a two second job, but end up takin
g
> me hours. And yes, if I'd got it right in the first instance, then this
> wouldn't have been a problem :)
> Thanks for any replies.|||> Table in SQL Server had a column (column_a tinyint), data contained is
just
> ones and zeros. In Enterprise Manager I changed the data type of column_a
to
> char(1), so that I could then go and replace the ones and zeros with 'Y'
and
> 'N'
> When I try and change one of the values, through either Access or
Enterprise
> Manager, it errors with:
> "Syntax error converting the char value 'Y' to a column of data type int"
While I don't have any obvious suggestion to help with EM, Access has a bit
of odd behavior. If you "Link" a SQL Server table into an Access database,
Access "remembers" the schema of the table, so that if you then do something
to the schema of the SQL Server table, Access is not aware of the change.
The workaround that I have used is to delete the linked table in Access and
re-link to it. I also like to rename it to get rid of the stupid "dbo_"
prefix that Access adds.
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||Wow, several replies, thanks.
Thomas
I did not mention this, but there are several triggers and check consraints,
basically several dependencies to the column_a. I did try creating a new
column_a_new (char1), then tried renaming column_a to column_a_old, but it
wasn't having it because of the dependencies. In an Access .mdb you can get
away with this, but sql server appears a bit more rigid in this type of
workaround.
Alejandro
I made the changes a few days ago (I have been trying to fix it in that
time) and the server and cient machine have been booted a few times.
I double checked information_scheme.columns, great suggestion thanks, and
got this which seems a little odd to me as I set it to char(1), which is doe
s
display as in Access and EM.
ORDINAL_POSITION
16
COLUMN_DEFAULT
('N')
IS_NULLABLE
No
DATA_TYPE
nvarchar
CHARACTER_MAXIMUM_LENGTH
50
CHARACTER_OCTET_LENGTH
100
QA gives the same error when doing an update set
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'N' to a column of data type int.
Mike
Yeah, there are lots of little tricks which need to be remembered with
Access (especially 2000) and SQL Server :)
I am using an .adp which is a bit different to an .mdb with linked tables,
but F5 is definitely your freind :)
I have booted the client and server, as I've been messing around for a few
days, so it should be refreshed. And it occurs in EM+QA too.
Thanks to all replies, its sounding horribly like Im going to have to drop
all dependencies, then re-create them after deleting the old column and
adding a new one. Maybe use EM to create a .sql script of the objects
including drop_all first, then alter the script to use char, then run
it....What a hassle, certainly won't make this mistake again, hopefully ;)
PS, is it better to reply to each individual post, or top post with a msg
like this, or bottom post with a msg like this ie including replies to
several people?
Or does it not really matter?
"Mike Labosh" wrote:

> just
> to
> and
> Enterprise
> While I don't have any obvious suggestion to help with EM, Access has a bi
t
> of odd behavior. If you "Link" a SQL Server table into an Access database
,
> Access "remembers" the schema of the table, so that if you then do somethi
ng
> to the schema of the SQL Server table, Access is not aware of the change.
> The workaround that I have used is to delete the linked table in Access an
d
> re-link to it. I also like to rename it to get rid of the stupid "dbo_"
> prefix that Access adds.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>|||> I did not mention this, but there are several triggers and check consraints,d">
> basically several dependencies to the column_a. I did try creating a new
> column_a_new (char1), then tried renaming column_a to column_a_old, but it
> wasn't having it because of the dependencies. In an Access .mdb you can g
et
> away with this, but sql server appears a bit more rigid in this type of
> workaround.
The obvious answer is to drop the triggers and check constraints before you
run
through the steps I suggested and then re-add the triggers and check constra
ints
afterwards. It's not that difficult. You can use the query analyzer to scrip
t
the creation of the appropriate triggers and check constraints as well as sc
ript
the drop scripts for these. Besides, it is likely that these triggers and ch
eck
constraints need to change anyway.

> PS, is it better to reply to each individual post, or top post with a msg
> like this, or bottom post with a msg like this ie including replies to
> several people?
> Or does it not really matter?
Doubt it matters much.
Thomas|||On Mon, 11 Apr 2005 10:00:01 -0700, Steve'o wrote:

>PS, is it better to reply to each individual post, or top post with a msg
>like this, or bottom post with a msg like this ie including replies to
>several people?
>Or does it not really matter?
Hi Steve'o,
Trying to start a flame-war? <grin>
Top-posting vs bottom-posting can turn into a heated debate. I believe
that most Usenet regulars prefer bottom-posting or inline reply (quote,
reply, quote, reply, ...). However, MS Outlook defaults to top-posting,
as does (if I recall correctly) MS' internet portal to the MS related
groups.
Since this group is about an MS product, a relatively large number of
users use Outlook or MS' internet portal for access. As a result, you'll
see lots of top-posting here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)