[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Must implement Iconvertible

Posted on 2011-04-22
26
Medium Priority
?
556 Views
Last Modified: 2012-06-27
Hello experts,
I am having a hard time with this problem.
I am simply trying to update some fields from a Details View control to the "Main" table in a database.

Below is the code with the Sql connection and  Sql command.
The code fails to convert the parameter value from a TextBox to a DateTime
Or object must implement IConvertible

Any suggestions??

Thanks
Dim con As SqlConnection
        Dim com As SqlCommand
        con = New SqlConnection("Server=Adam\; Database=HelpDesk;Integrated Security=True")
        com = New SqlCommand("Update Main set[dDate]=@dDate,[tTime]=@ttime,[nName]=@nName,[sSubject]=@sSubject,[tType]=@tType,[mMonth]=@mMonth,[Remarks]=@Remarks where ID=@ID", con)
        com.Parameters.Add("@dDate", System.Data.SqlDbType.Date)
        com.Parameters("@dDate").Value = DateTime.Parse(txtNewDate.Text)
        com.Parameters.Add("@tTime", System.Data.SqlDbType.Time)
        com.Parameters("@tTime").Value = DateTime.Parse(txtNewTime.Text)
        com.Parameters.Add("@nName", System.Data.SqlDbType.NVarChar)
        com.Parameters("@nName").Value = txtNewName.Text
        com.Parameters.Add("@sSubject", System.Data.SqlDbType.NVarChar)
        com.Parameters("@sSubject").Value = txtNewSubject.Text
        com.Parameters.Add("@tType", System.Data.SqlDbType.NVarChar)
        com.Parameters("@tType").Value = txtNewType.Text
        com.Parameters.Add("@mMonth", System.Data.SqlDbType.NVarChar)
        com.Parameters("@mMonth").Value = txtNewMonth.Text
        com.Parameters.Add("@Remarks", System.Data.SqlDbType.VarChar)
        com.Parameters("@Remarks").Value = txtNewRemarks.Text
        com.Parameters.Add("@ID", System.Data.SqlDbType.Int)
        com.Parameters("@ID").Value = currentID
        con.Open()
        com.ExecuteNonQuery()
        con.Close()
        DataDetails.ChangeMode(DetailsViewMode.ReadOnly)
        getGridData()
        BindDetails()

Open in new window

0
Comment
Question by:adamtrask
  • 15
  • 6
  • 4
  • +1
26 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 668 total points
ID: 35450795
May be because you are using Date in sql but assigning it the DateTime value


try changing

com.Parameters("@dDate").Value = DateTime.Parse(txtNewDate.Text)

to

com.Parameters("@dDate").Value = Date.Parse(txtNewDate.Text)
0
 

Author Comment

by:adamtrask
ID: 35451041
Thank you CodeCruiser:

I did as you suggested, but got this error:

Failed to convert parameter value from a DateTime to a TimeSpan
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35451059
What is the type of dDate in stored procedure and what is in txtNewDate.text?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:adamtrask
ID: 35451112
dDate is of type date in the database table
txtNewDate.Text is  a Text Box which  gets its text value from a  Details View control template.
It was created in the following way:


Dim txtNewDate As TextBox = DataDetails.FindControl("txtEditDate")
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35451119
But what text is actually contained in the textbox? I suspect its nothing!
0
 

Author Comment

by:adamtrask
ID: 35451193
It should contains a date.

Ok, let me clarif:

I have a gridView which is actually displaying several records in the database. Ech record has dDate filed which contains a date

The gridView contains a Select link which, when clicked,  displays the Details View control. This control displays the data of a single record from the Grid View.

 To access the data field in the Details View I created Templates in the form of textBoxes.

 So txtNewDate should contain the date that's actually being displayed in the Details View control.



 
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35451230
> So txtNewDate should contain the date that's actually being displayed in the Details View control.

It should. But have you checked that it does? By stepping through the code?
0
 

Author Comment

by:adamtrask
ID: 35451238
Let me try
0
 

Author Comment

by:adamtrask
ID: 35451273
Yes it does contain the date: I don't know how to step through the code so I did this instead:
I created a new label named "lblTesting" and placed it immediately after the line of code which creates
txtNewDate in the following way:


Dim txtNewDate As TextBox = DataDetails.FindControl("txtEditDate")
        Me.lbltesting.Text = txtNewDate.Text


I deleted all the code that comes after these two lines
Sure enough the date which I changed in the Details View controls was displayed in the new label.
0
 
LVL 16

Assisted Solution

by:Imran Javed Zia
Imran Javed Zia earned 1332 total points
ID: 35451301
if you are entering date in us format then use
com.Parameters("@dDate").Value = DateTime.ParseExact(txtNewDate.Text, "MM/dd/yyyy", Globalization.CultureInfo.CurrentCulture)

if you are entering date in uk format then use
com.Parameters("@dDate").Value = DateTime.ParseExact(txtNewDate.Text, "dd/MM/yyyy", Globalization.CultureInfo.CurrentCulture)




0
 

Author Comment

by:adamtrask
ID: 35451428
IJZ:  Thanks for you comment, but I still get the following error:

"String was not recognized as a valid Datetime"

This is what I did:
Instead of making the  textbox the parameter's value, I first created a date variable and tried to place the date into the variable then use the variable as the parameter's value in the following way:


Dim stringDate As Date = Date.ParseExact(txtNewDate.Text, "MM/dd/yyyy", Globalization.CultureInfo.CurrentCulture)
com.Parameters.Add("@dDate", System.Data.SqlDbType.Date)
com.Parameters("@dDate").Value = stringDate:

0
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35451474
if you are getting exception on
Dim stringDate As Date = Date.ParseExact(txtNewDate.Text, "MM/dd/yyyy", Globalization.CultureInfo.CurrentCulture)




or
com.Parameters("@dDate").Value = stringDate:
0
 

Author Comment

by:adamtrask
ID: 35451500
The top one

The exception is on this one:


Dim stringDate As Date = Date.ParseExact(txtNewDate.Text, "MM/dd/yyyy", Globalization.CultureInfo.CurrentCulture)
0
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35451516
can you tell what value you can see in txtNewDate.Text in debugger?
0
 

Author Comment

by:adamtrask
ID: 35451535
as I explained to  CodeCruiser: I don't know how to step through the code using the debugger
so I did this instead:
I created a new label named "lblTesting" and placed it immediately after the line of code which creates
txtNewDate in the following way:

Dim txtNewDate As TextBox = DataDetails.FindControl("txtEditDate")
        Me.lbltesting.Text = txtNewDate.Text

the label displayed the following date:
4/20/2011 11:00:00 AM
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35451788
Hi,

Please check all DAte Textbox have value other than blank. If you convert those values have blank to Date will give you exception.

Please first check for blank if yes then pass DBNull value else convert to DAte and send to parameter.
0
 

Author Comment

by:adamtrask
ID: 35451920
Thank you PatelAlpesh. I did check for blanks and confirmed that there are no blanks.

Yet I get the exception:

Invalid cast from 'System.DateTime' to 'System.TimeSpan'.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35452555
>Invalid cast from 'System.DateTime' to 'System.TimeSpan'.

I dont see any TimeSpan anywhere in your code yet the exception is about timespan. Can you show us the method signature of the stored procedure?
0
 

Author Comment

by:adamtrask
ID: 35453076

Codecruiser,

I am not using a stored procedure. I am updating the values from the web form to the table Main.
When get rid of the dDate and tTime parts the remaining values are updated without a problem.

The following contains the code for the whole DataDetails_ItemUpdating procedure:



   Protected Sub DataDetails_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DataDetails.ItemUpdating
        Dim currentID As Integer = DataDetails.DataKey.Value
        Dim txtNewDate As TextBox = DataDetails.FindControl("txtEditDate")
        Dim varDate As Date
        If Date.TryParse(txtNewDate.Text, varDate) Then
            Me.lbldate.Text = varDate.ToShortDateString()
        Else
            Me.lbldate.Text = "Sorry"
        End If
        Dim txtNewTime As TextBox = DataDetails.FindControl("txtEditTime")
        Dim varTime As DateTime
        If Date.TryParse(txtNewTime.Text, varTime) Then
            Me.lblTime.Text = varTime.ToShortTimeString
        Else
            Me.lblTime.Text = "Sorry"
        End If

        Dim txtNewName As TextBox = DataDetails.FindControl("txtEditName")
        Dim txtNewSubject As TextBox = DataDetails.FindControl("txtEditSubject")
        Dim txtNewType As TextBox = DataDetails.FindControl("txtEditType")
        Dim txtNewMonth As TextBox = DataDetails.FindControl("txtEditMonth")
        Dim txtNewRemarks As TextBox = DataDetails.FindControl("txtEditRemarks")
        '---------
        Dim con As SqlConnection
        Dim com As SqlCommand
        con = New SqlConnection("Server=Tariq\; Database=HelpDesk;Integrated Security=True")
        com = New SqlCommand("Update Main set[dDat]=@dDate,[tTime]=@tTime,[nName]=@nName,[sSubject]=@sSubject,[tType]=@tType,[mMonth]=@mMonth,[Remarks]=@Remarks where ID=@ID", con)
        com.Parameters.Add("@dDate", System.Data.SqlDbType.Date)
        com.Parameters("@dDate").Value = varDate
        com.Parameters.Add("@tTime", System.Data.SqlDbType.Time)
        com.Parameters("@tTime").Value = varTime
        com.Parameters.Add("@nName", System.Data.SqlDbType.NVarChar)
        com.Parameters("@nName").Value = txtNewName.Text
        com.Parameters.Add("@sSubject", System.Data.SqlDbType.NVarChar)
        com.Parameters("@sSubject").Value = txtNewSubject.Text
        com.Parameters.Add("@tType", System.Data.SqlDbType.NVarChar)
        com.Parameters("@tType").Value = txtNewType.Text
        com.Parameters.Add("@mMonth", System.Data.SqlDbType.NVarChar)
        com.Parameters("@mMonth").Value = txtNewMonth.Text
        com.Parameters.Add("@Remarks", System.Data.SqlDbType.VarChar)
        com.Parameters("@Remarks").Value = txtNewRemarks.Text
        com.Parameters.Add("@ID", System.Data.SqlDbType.Int)
        com.Parameters("@ID").Value = currentID
        con.Open()
        com.ExecuteNonQuery()
        con.Close()
        DataDetails.ChangeMode(DetailsViewMode.ReadOnly)
        getGridData()
        BindDetails()

0
 

Author Comment

by:adamtrask
ID: 35453124
The lblDate and lblTime labels were just for testing. I used them to see if the varDate and varTime variables contained values. They did.

0
 

Author Comment

by:adamtrask
ID: 35453431
Since the Details View control gets its data from the GridView (myGrid) control, below is the code that shows how the gridview gets its data:

Private Sub getGridData()
        Dim dt As Date
        dt = Me.calendar1.SelectedDate
        Me.txtDate.Text = dt.ToShortDateString
        Dim con As SqlConnection
        Dim com As SqlCommand
        Dim reader As SqlDataReader
        con = New SqlConnection("Server=Tariq\; Database=HelpDesk;Integrated Security=True")
        com = New SqlCommand("SELECT * FROM Main where dDate=@dDate", con)
        com.Parameters.AddWithValue("@dDate", System.Data.SqlDbType.Date)
        com.Parameters("@dDate").Value = Me.txtDate.Text

        con.Open()
        reader = com.ExecuteReader

        myGrid.DataSource = reader
        myGrid.DataKeyNames = New String() {"ID"}
        myGrid.DataBind()

        reader.Close()
        con.Close()

    End Sub
0
 
LVL 16

Assisted Solution

by:Imran Javed Zia
Imran Javed Zia earned 1332 total points
ID: 35454416
Hi, you are also getting time in string 4/20/2011 11:00:00 AM so use following

Date.ParseExact(txtNewDate.Text, "M/d/yyyy hh:mm:ss tt", Globalization.CultureInfo.InvariantCulture)
0
 

Author Comment

by:adamtrask
ID: 35454575
thanks

I tried ParseExact and it seems to work ok with the following line of code:
 varDate = Date.ParseExact(txtNewDate.Text, "M/d/yyyy hh:mm:ss tt", Globalization.CultureInfo.InvariantCulture).ToShortDateString

But it generates "formatException was unhandled by user code
String was not recognized as a valid Datetime" when used witht the next
line of code:

 varTime = Date.ParseExact(txtNewTime.Text, "M/d/yyyy hh:mm:ss tt", Globalization.CultureInfo.InvariantCulture).ToShortTimeString

formatException was unhandled by user code
String was not recognized as a valid Datetime
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35456312
Is the txtNewTime.Text in the format  "M/d/yyyy hh:mm:ss tt" as well?
0
 

Author Comment

by:adamtrask
ID: 35457475
yes it is....
0
 

Author Closing Comment

by:adamtrask
ID: 35460514
Thank you guys for your help
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

872 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