DateTime conversion question

Hello everyone,
I get the following error when submitting my form:
"Failed to convert parameter value from a String to a DateTime."

Do I need to use "parse" or "format" before I send my value off to the DB. Whensubmittimg all others the insert sp works fine, just bombs on datetime conversion.
Any suggestions would be greatly appreciated.
Thanks,
C

code for insert:

 cmdCommand.Parameters.Add("@Ident", SqlDbType.VarChar, 100).Value = txtdocident.Text
        cmdCommand.Parameters.Add("@Vol", SqlDbType.VarChar, 50).Value = txtvolnumber.Text
        cmdCommand.Parameters.Add("@Type", SqlDbType.VarChar, 255).Value = cmbType.SelectedValue
        cmdCommand.Parameters.Add("@Title", SqlDbType.VarChar, 2000).Value = txtardoctitle.Value
        cmdCommand.Parameters.Add("@Keyw", SqlDbType.VarChar, 2000).Value = txtarkeyword.Value
        cmdCommand.Parameters.Add("@Summ", SqlDbType.VarChar, 2000).Value = txtarsummary.Value
        cmdCommand.Parameters.Add("@Related", SqlDbType.VarChar, 2000).Value = txtarreldoc.Value
        cmdCommand.Parameters.Add("@Datereq", SqlDbType.DateTime).Value = txtdateappr.Text
        cmdCommand.Parameters.Add("@Assby", SqlDbType.VarChar, 50).Value = txtassby.Text
        cmdCommand.Parameters.Add("@Dateappr", SqlDbType.VarChar, 50).Value = txtdateappr.Text
        cmdCommand.Parameters.Add("@Recby", SqlDbType.VarChar, 50).Value = cmbRecBy.SelectedValue
        cmdCommand.Parameters.Add("@Location", SqlDbType.VarChar, 50).Value = cmbloc.SelectedValue
        cmdCommand.Parameters.Add("@Resp", SqlDbType.VarChar, 255).Value = txtresponsible.Text
        cmdCommand.Parameters.Add("@Author", SqlDbType.VarChar, 50).Value = txtauthor.Text
        cmdCommand.Parameters.Add("@Ref", SqlDbType.VarChar, 2000).Value = txtarref.Value
        cmdCommand.Parameters.Add("@Dept", SqlDbType.VarChar, 20).Value = cmbDept.SelectedValue
        cmdCommand.Parameters.Add("@Hardcpy", SqlDbType.VarChar, 255).Value = txthardcopy.Text
        cmdCommand.Parameters.AddWithValue("@bAuto", "1")
        cmdCommand.ExecuteNonQuery()


Insert sp:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[qparmInsertInfo]
(
@Ident varchar(100),
@Vol varchar(50),
@Type varchar(50),
@Title varchar(255),
@Keyw varchar(255),
@Summ varchar(255),
@Related varchar(255),
@Datereq datetime,
@Assby varchar(50),
@Dateappr varchar(50),
@Recby varchar(50),
@Location varchar(25),
@Resp varchar(50),
@Author varchar(50),
@Ref varchar(255),
@Dept varchar(20),
@Hardcpy varchar(50),
@bAuto bit = 1
)
as
if exists
-- You cannot register usernames already registered on the database twice.

(
select docident from tblDocument where docident = @Ident
)
return 1
else
insert tblDocument(docident,
    docvol,
    doctype,
    doctitle,
    dockeyword,
    docsumm,
    docrel,
    docdatereq,
    docassby,
    docdateappr,
    docrecby,
    docloc,
    docresp,
    docauthor,
    docref,
    docdept,
    dochardcopy,
    bAuto)
values(@Ident,
    @Vol,
    @Type,
    @Title,
    @Keyw,
    @Summ,
    @Related,
    @Datereq,
    @Assby,
    @Dateappr,
    @Recby,
    @Location,
    @Resp,
    @Author,
    @Ref,
    @Dept,
    @Hardcpy,
    @bAuto)
snappy3273Asked:
Who is Participating?
 
TimCotteeHead of Software ServicesCommented:
snappy3273,

Well that is because there is no date there, that is a different issue. You could wrap this up in a function to return a default date or if your field allows nulls then return a null

    Private Function GetMeADateOrNull(ByVal MyDateString As String) As Object
        If MyDateString.Length = 0 Then
            Return DBNull.Value
        Else
            Dim MyDate As Date
            Try
                MyDate = CDate(MyDateString)
            Catch ex As Exception
                Return DBNull.Value
            End Try
            Return MyDate.ToString("yyyy-MM-dd")
        End If
    End Function

And then use that in the assignment : cmdCommand.Parameters.Add("@Datereq", SqlDbType.DateTime).Value = GetMeADateOrNull(txtDateAppr.Text)


Tim
0
 
TimCotteeHead of Software ServicesCommented:
Hi snappy3273,

>         cmdCommand.Parameters.Add("@Datereq", SqlDbType.DateTime).Value = txtdateappr.Text.ToString("yyyy-mm-dd")

Always works for me, formatting it into a sql friendly date.


Tim Cottee
0
 
snappy3273Author Commented:
Hey Tim,
The format you provided is correct but i get this error:
Unable to cast object of type 'System.String' to type 'System.IFormatProvider'.

Any suggestions?
Thanks,
S
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
snappy3273Author Commented:
The format in my textfield is : 4/13/2006. THe calendar control manages the input into the field.
What is does your input field format look like?
S
0
 
TimCotteeHead of Software ServicesCommented:
snappy3273,

How about:

cmdCommand.Parameters.Add("@Datereq", SqlDbType.DateTime).Value = String.Format("{0:yyyy-MM-dd}", CDate(txtDateAppr.Text))

Tim
0
 
snappy3273Author Commented:
You know, intellisense was pushing me this direction(string.format), and what you gave me looks structured correctly but it gave me a new exception:

Conversion from string "" to type 'Date' is not valid.

i'm gonna take my box outside and beat it down like "office space" argh!!
S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.