Inserting date datatypes via ado.net

  • Thread starter Thread starter J. Muenchbourg
  • Start date Start date
J

J. Muenchbourg

while inserting new records into SQL, i'm using the folloinwg
sqlstatement>

Dim MySQL as string = "Insert into roster (pname, pnotes, thedate)
values (@pname, @pnotes, @thedate)"

the sql parameter I'm using is >

cmd.Parameters.Add(New SQLParameter("@thedate", thedate.text))

but i get an error : Syntax error converting datetime from character
string when trying to insert. the textbox syntax I'm using for the
textbox control is >

<asp:textbox id="thedate" runat="server"/>

not sure where to change the text coming from the textbox into a
"datetime" data type, as with old asp, you could use a poundsign
(something like "# thedate #") to make it a date object.

thanks for your help
JMuenchbourg

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
use Convert.ToDateTime(...)

cmd.Parameters.Add(New SQLParameter("@thedate",
Convert.ToDateTime(thedate.text)))
 
I get the same exact "Syntax error converting datetime from character
string" with >
cmd.Parameters.Add(New SQLParameter("@thedate",
Convert.ToDateTime(thedate.text)))

The date format I am using for 'thedate' is mm/dd/yyyy . Do I have to
explicitly use a format function to enter it as this?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
AFAIK when inserting in a datetime column, when you put
'#1/1/2004#' -- returns: syntax error covnerting datetime from
character string.
'7/1/2004' (or any combination format) -- will work fine.

and the error you're having is coming from SQL Server.

Remember a date is a DATE, and it does not have any format.
When a date is represented in string, it has be in a format. A
format could be mm/dd/yyyy or any else format.

Check this:
http://msdn.microsoft.com/library/e...mdatasqlclientsqlparameterclassvaluetopic.asp

<quote>
If the application specifies the database type, the bound value is converted
to that type when the provider sends the data to the server. The provider
attempts to convert any type of value if it supports the IConvertible
interface. Conversion errors may result if the specified type is not
compatible with the value.

Both the SqlDbType and DbType properties can be inferred by setting the
Value.
</quote>

So we know that date does not have a formatted and from
above quote we learn that SqlParameter() can/will convert frameworks
Date object to its equivalent type of Sql.

Before adding parameter confirm that thedate (TextBox) contains
a valid date (can be in any machien understandable format), by using
IsDate() method.
 
Back
Top