?
Solved

DateTime conversion question

Posted on 2006-04-13
6
Medium Priority
?
877 Views
Last Modified: 2007-12-19
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)
0
Comment
Question by:snappy3273
  • 3
  • 3
6 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 16446193
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
 

Author Comment

by:snappy3273
ID: 16446337
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
 

Author Comment

by:snappy3273
ID: 16446419
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:TimCottee
ID: 16446423
snappy3273,

How about:

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

Tim
0
 

Author Comment

by:snappy3273
ID: 16446519
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
 
LVL 43

Accepted Solution

by:
TimCottee earned 2000 total points
ID: 16446575
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question