Failed to convert parameter message : The Official Microsoft ASP.NET Forums

Reply

Failed to convert parameter message

Oct 16, 2010 05:45 PM|LINK

I'm trying to pass values in a SessionState from one page to another and then post them to a SQL database. When I click on the submit button on my submit page, I get this error:

Failed to convert parameter value from a String to a DateTime.

Here is my submit page:

Imports System.Data.SqlClient
Imports System.Data


Partial Class Default2
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim opnumber As String = CType(Session.Item("opnumber"), String)
        Dim exceptiondate As DateTime = CType(Session.Item("exceptiondate"), DateTime)
        Dim stime As DateTime = CType(Session.Item("starttime"), DateTime)
        Dim etime As DateTime = CType(Session.Item("endtime"), DateTime)
        Dim hours As String = CType(Session.Item("hours"), String)
        Dim minutes As String = CType(Session.Item("minutes"), String)
        Dim exceptioncode As String = CType(Session.Item("exceptioncode"), String)
        Dim submittedby As String = CType(Session.Item("submittedby"), String)
        opnumLabel.Text = opnumber
        exceptdateLabel.Text = exceptiondate
        starttimeLabel.Text = stime
        endtimeLabel.Text = etime
        hourdurLabel.Text = hours
        mindurLabel.Text = minutes
        codeLabel.Text = exceptioncode
        approvedbyLabel.Text = submittedby
    End Sub


    Protected Sub sqlsubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles sqlsubmitButton.Click
        InsertRow()
    End Sub
    Public Sub InsertRow()
        Dim myConnectionString As String = "Initial Catalog=mdr;Data Source=xxxxxx;uid=xxxxx;password=xxxxxxxx;"


        Dim myConnection As New SqlConnection(myConnectionString)
        Dim commandText As String = _
        "insert into Exceptions (employeenumber, exceptiondate, starttime, endtime, duration, code, approvedby) " & _
        "values(@employeenumber, @exceptiondate, @starttime, @endtime, @duration, @code, @approved)"


        Using connection As New SqlConnection(myConnectionString)
            Dim command As New SqlCommand(commandText, connection)


            command.Parameters.Add("@employeenumber", SqlDbType.VarChar)
            command.Parameters("@employeenumber").Value = "opnumber"


            command.Parameters.Add("@exceptiondate", SqlDbType.DateTime)
            command.Parameters("@exceptiondate").Value = "exceptiondate"


            command.Parameters.Add("@starttime", SqlDbType.DateTime)
            command.Parameters("@starttime").Value = "starttime"


            command.Parameters.Add("@endtime", SqlDbType.DateTime)
            command.Parameters("@endtime").Value = "endtime"


            command.Parameters.Add("@duration", SqlDbType.VarChar)
            command.Parameters("@duration").Value = "hours, minutes"


            command.Parameters.Add("@code", SqlDbType.VarChar)
            command.Parameters("@code").Value = "exceptioncode"


            command.Parameters.Add("@approved", SqlDbType.VarChar)
            command.Parameters("@approved").Value = "submittedby"


            Try


                connection.Open()
                Dim rowsAffected As Integer = command.ExecuteNonQuery()


            Catch ex As Exception
                myerrormessageLabel.Text = ex.Message
            End Try
            thankyouLabel.Text = "Your Data Has Been Submitted"
        End Using


    End Sub


    Protected Sub cancelinputButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cancelinputButton.Click
        Response.Redirect("Default.aspx")
    End Sub
End Class

Can anyone help me to figure out what I'm not seeing. I've verified that all of the input matches what I'm looking for on the SQL side.

Moved by SGWellens on Oct 16, 2010 05:53 PM

Reply

limno

All-Star

117320 Points

8000 Posts

Moderator

MVP

Re: Failed to convert parameter message

Oct 16, 2010 06:14 PM|LINK

Using connection As New SqlConnection(myConnectionString)
            Dim command As New SqlCommand(commandText, connection)
  

            command.Parameters.Add("@employeenumber", SqlDbType.VarChar)
            command.Parameters("@employeenumber").Value = opnumbe
  

            command.Parameters.Add("@exceptiondate", SqlDbType.DateTime)
            command.Parameters("@exceptiondate").Value = exceptiondate
  

            command.Parameters.Add("@starttime", SqlDbType.DateTime)
            command.Parameters("@starttime").Value = starttime
  

            command.Parameters.Add("@endtime", SqlDbType.DateTime)
            command.Parameters("@endtime").Value = endtime
  

            command.Parameters.Add("@duration", SqlDbType.VarChar)
            command.Parameters("@duration").Value = hours & " " &  minutes
  

            command.Parameters.Add("@code", SqlDbType.VarChar)
            command.Parameters("@code").Value = exceptioncode
  

            command.Parameters.Add("@approved", SqlDbType.VarChar)
            command.Parameters("@approved").Value = submittedby

Limno

Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Reply

Re: Failed to convert parameter message

Oct 16, 2010 06:49 PM|LINK

Limno,

If I take the quotations off (as you suggest) for the command parameters, I get an error that the "value" is not declared. I thought that I had to keep the quotation marks on to read the values from the sesssion.state.

Reply

limno

All-Star

117320 Points

8000 Posts

Moderator

MVP

Re: Failed to convert parameter message

Oct 16, 2010 11:05 PM|LINK

Try AddWithValue:

Using connection As New SqlConnection(myConnectionString)
            Dim command As New SqlCommand(commandText, connection)
  

            command.Parameters.AddWithValue("@employeenumber", opnumbe)
            command.Parameters.AddWithValue("@exceptiondate", exceptiondate )
            command.Parameters.AddWithValue("@starttime",  starttime)
            command.Parameters.AddWithValue("@endtime",  endtime)
            command.Parameters.AddWithValue("@duration",  hours & " " &  minutes)
            command.Parameters.AddWithValue("@code",  exceptioncode)
            command.Parameters.AddWithValue("@approved", submittedby)

Limno

Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Reply

dougancil

Member

71 Points

166 Posts

Re: Failed to convert parameter message

Oct 17, 2010 01:40 AM|LINK

Limno,

Here's the code as it's written with your suggestion:

Imports System.Data.SqlClient
Imports System.Data


Partial Class Default2
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim opnumber As String = CType(Session.Item("opnumber"), String)
        Dim exceptiondate As DateTime = CType(Session.Item("exceptiondate"), DateTime)
        Dim stime As DateTime = CType(Session.Item("starttime"), DateTime)
        Dim etime As DateTime = CType(Session.Item("endtime"), DateTime)
        Dim hours As String = CType(Session.Item("hours"), String)
        Dim minutes As String = CType(Session.Item("minutes"), String)
        Dim exceptioncode As String = CType(Session.Item("exceptioncode"), String)
        Dim submittedby As String = CType(Session.Item("submittedby"), String)
        opnumLabel.Text = opnumber
        exceptdateLabel.Text = exceptiondate
        starttimeLabel.Text = stime
        endtimeLabel.Text = etime
        hourdurLabel.Text = hours
        mindurLabel.Text = minutes
        codeLabel.Text = exceptioncode
        approvedbyLabel.Text = submittedby
    End Sub


    Protected Sub sqlsubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles sqlsubmitButton.Click
        InsertRow()
    End Sub
    Public Sub InsertRow()
        Dim myConnectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxx;password=xxxxxxx;"


        Dim myConnection As New SqlConnection(myConnectionString)
        Dim commandText As String = _
        "insert into Exceptions (employeenumber, exceptiondate, starttime, endtime, duration, code, approvedby) " & _
        "values(@employeenumber, @exceptiondate, @starttime, @endtime, @duration, @code, @approved)"


        Using connection As New SqlConnection(myConnectionString)
            Dim command As New SqlCommand(commandText, connection)


            command.Parameters.AddWithValue("@employeenumber", opnumber)
            command.Parameters.AddWithValue("@exceptiondate", exceptiondate)
            command.Parameters.AddWithValue("@starttime", starttime)
            command.Parameters.AddWithValue("@endtime", endtime)
            command.Parameters.AddWithValue("@duration", hours & " " & minutes)
            command.Parameters.AddWithValue("@code", exceptioncode)
            command.Parameters.AddWithValue("@approved", submittedby)


            Try


                connection.Open()
                Dim rowsAffected As Integer = command.ExecuteNonQuery()


            Catch ex As Exception
                myerrormessageLabel.Text = ex.Message
            End Try
            thankyouLabel.Text = "Your Data Has Been Submitted"
        End Using


    End Sub


    Protected Sub cancelinputButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cancelinputButton.Click
        Response.Redirect("Default.aspx")
    End Sub
End Class

and intellisense is still telling me that the opnumber, exceptiondate, starttime, etc is still needing to be declared.

Reply

limno

All-Star

117320 Points

8000 Posts

Moderator

MVP

Re: Failed to convert parameter message

Oct 17, 2010 03:32 AM|LINK

You need to declare these variables outside the Page_Load event in order to access them in your InsertRow event.

Dim opnumber As String
  Dim exceptiondate As DateTime
 Dim stime As DateTime
Dim etime As DateTime
        Dim hours As String
Dim minutes As String
        Dim exceptioncode As String
        Dim submittedby As String


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


         opnumber = CType(Session.Item("opnumber"), String)
         exceptiondate= CType(Session.Item("exceptiondate"), DateTime)
          stime= CType(Session.Item("starttime"), DateTime)
         etime = CType(Session.Item("endtime"), DateTime)
         hours = CType(Session.Item("hours"), String)
         minutes = CType(Session.Item("minutes"), String)
         exceptioncode = CType(Session.Item("exceptioncode"), String)
         submittedby  = CType(Session.Item("submittedby"), String)




        opnumLabel.Text = opnumber
        exceptdateLabel.Text = exceptiondate
        starttimeLabel.Text = stime
        endtimeLabel.Text = etime
        hourdurLabel.Text = hours
        mindurLabel.Text = minutes
        codeLabel.Text = exceptioncode
        approvedbyLabel.Text = submittedby
    End Sub

Limno

Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Reply

dougancil

Member

71 Points

166 Posts

Re: Failed to convert parameter message

Oct 17, 2010 06:27 PM|LINK

Limno,

Ok so then how would I tie this action to my submit button?

Reply

limno

All-Star

117320 Points

8000 Posts

Moderator

MVP

Re: Failed to convert parameter message

Oct 17, 2010 07:05 PM|LINK

You can make the change I suggested and try to run your code.

Limno

Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Reply

dougancil

Member

71 Points

166 Posts

Re: Failed to convert parameter message

Oct 17, 2010 08:31 PM|LINK

Limno,

Ok I tried running that code and didn't get the results that I need but I'm no longer getting the error that I was getting before.

So that suggests that this is on the right track. So now how do I add this to my button onclick and also instead of the correct date for my exceptiondate, I'm now just seeing a time and my "code" and "submitted by" labels are blank in my "summary" page.

Reply

dougancil

Member

71 Points

166 Posts

Re: Failed to convert parameter message

Oct 17, 2010 08:42 PM|LINK

Limno,

Sorry I got those errors figured out. Now when I try to submit my data I'm given this error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

The data that I tried to post is:

Op #: 8451
Exception Date: 11/15/2010
Start Time: 11:00:00 AM
End Time: 12:00:00 PM
Duration: 1 hours 0 minutes
Reason: Approved Technical Reason
Submitted By: User

Failed to convert parameter message RSS

31 replies

dougancil

Failed to convert parameter message

limno

Re: Failed to convert parameter message

dougancil

Re: Failed to convert parameter message

limno

Re: Failed to convert parameter message

dougancil

Re: Failed to convert parameter message

limno

Re: Failed to convert parameter message

dougancil

Re: Failed to convert parameter message

limno

Re: Failed to convert parameter message

dougancil

Re: Failed to convert parameter message

dougancil

Re: Failed to convert parameter message

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft