Click here to Skip to main content
15,792,397 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have an error when insert data into mssql table using
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


What I have tried:

Data table column is
[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)

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

        ' SqlCommand object
Updated 27-Apr-23 21:49pm
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.

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 :
MoDetails.Columns.Add("docdate", GetType(String))

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

If the pubdate field should be the same in every row then change this line :
Dim pubdate As DateTime = format_pubdate

Dim pubdate As DateTime = pubdate_value

and delete this line (change dd-MM-yyyy to match your needs) :
Dim format_pubdate = pubdate_value.ToString("dd-MM-yyyy")

If the pubdate field should be the different in every row then change it to :
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.
Share this answer
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.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900