How to convert date and/or time from character string from gridview cell?

Question

1.00/5 (1 vote)

See more:

hi
I have an error when insert data into mssql table using asp.net
error is

 'Conversion failed when converting date 
and/or time from character string.
The data for table-valued parameter "@MODetails_TYPE" doesn't conform to the table 
type of the parameter. SQL Server error is: 241, state: 1
The statement has been terminated.'

.

Where did I mistake ?

Pls advice me
Thank you

Maideen

What I have tried:

Data table column is

VB

CREATE TYPE [dbo].[MODetails_TYPE] AS TABLE(
[DocNo] [nvarchar](120) NULL,
[DocDate] [datetime] NULL,
[PubDate] [datetime] NULL,
[MainSection] [nvarchar](150) NULL,
[SubSection] [nvarchar](150) NULL,
[Position] [nvarchar](150) NULL,
[PageNumber] [numeric](18, 0) NULL,
[SizeCode] [nvarchar](120) NULL,
[Remarks] [nvarchar](500) NULL,
[CreateOn] [datetime] NULL)

       Dim MoDetails As New DataTable()
	   MoDetails.Columns.Add("docno", GetType(String))
	   MoDetails.Columns.Add("docdate", GetType(String))
       MoDetails.Columns.Add("PubDate", GetType(DateTime))
       MoDetails.Columns.Add("mainsection", GetType(String))
       MoDetails.Columns.Add("subsection", GetType(String))
       MoDetails.Columns.Add("position", GetType(String))
	   MoDetails.Columns.Add("pagenumber", GetType(Decimal))
       MoDetails.Columns.Add("SizeCode", GetType(String))
       MoDetails.Columns.Add("Remarks", GetType(String))
       MoDetails.Columns.Add("CreateOn", GetType(DateTime))

	' MO details from GridView

        Dim pubdate_value As DateTime = DateTime.ParseExact(gv.Rows(i).Cells(0).Text.ToString(), "yyyy-MM-dd", CultureInfo.InvariantCulture)
        Dim format_pubdate = pubdate_value.ToString("yyyy-MM-dd")
        For Each row As GridViewRow In gv.Rows
            Dim pubdate As DateTime = format_pubdate
            Dim mainsection As String = row.Cells(1).Text
            Dim subsection As String = row.Cells(2).Text
            Dim position As String = row.Cells(3).Text
            Dim SizeCode As String = row.Cells(4).Text
            Dim Remarks As String = row.Cells(5).Text
            Dim pagenumber As Decimal = Convert.ToDecimal(row.Cells(6).Text)
            Dim Docno As String = Me.txtDocNo.Text
            Dim Docdate As String = format_Docdate
            Dim CreateOn As DateTime = Date.Now

            MoDetails.Rows.Add(pubdate, mainsection, subsection, position, SizeCode, Remarks, pagenumber, Docno, docdate,CreatOn)
        Next

        'table-valued parameter
        Dim MoDetailsParam As New SqlParameter("@MODetails_TYPE", SqlDbType.Structured)
        MoDetailsParam.Value = MoDetails
        MoDetailsParam.TypeName = "dbo.MODetails_TYPE"

        ' SqlCommand object
        command.Parameters.Add(MoDetailsParam)
        command.ExecuteNonQuery()
        conn.Close()

Posted 27-Apr-23 18:19pm

Maideen Abdul Kader

Updated 27-Apr-23 20:49pm

Add a Solution

Comments

Member 15627495 28-Apr-23 2:42am

the error 241 for Sql Server is about : dateTime error...

DateTime have hours Minutes seconds integrated.
the value you want to process have the Date only.

fix your field with a reliable value, the Date Only.

2 solutions

Solution 1

You have defined DocDate, PubDate and CreateOn as DateTime types, so you must pass them DateTime values, not strings. So use the DateTime.TryParse Method (System) | Microsoft Learn[^] to get the correct value types.

Posted 27-Apr-23 20:45pm

Richard MacCutchan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

M-Badger · Accepted Answer · 2023-04-27T20:49:00

In the table type the docdate column is defined as a datetime data type - [DocDate] [DateTime] NULL - and then in the DataTable definition, the docdate column is defined as a string data type - MoDetails.Columns.Add("docdate", GetType(String)).

In the For Each loop the last two variables in the Rows.Add command are spelt wrong.

In the For Each loop you create a string variable, format_pubdate and then assign it to the DateTime variable pubdate before using it in the Rows.Add command, this may be causing an issue.

Your code extract does not include a declaration nor an assignment for the format_Docdate variable.

Some suggestions.
Change this line :

VB

MoDetails.Columns.Add("docdate", GetType(String))

To :

VB

MoDetails.Columns.Add("docdate", GetType(DateTime))

If the pubdate field should be the same in every row then change this line :

VB

Dim pubdate As DateTime = format_pubdate

To:

VB

Dim pubdate As DateTime = pubdate_value

and delete this line (change dd-MM-yyyy to match your needs) :

VB

Dim format_pubdate = pubdate_value.ToString("dd-MM-yyyy")

If the pubdate field should be the different in every row then change it to :

VB

Dim pubdate As DateTime = DateTime.ParseExact(row.Cells(0).Text.ToString(), "yyyy-MM-dd", CultureInfo.InvariantCulture)

The above also probably applies to Docdate and format_Docdate where you need to check variable types and ensure the formating is correct.