将日期值从用户定义的表传递到SQL Server 2008存储过程

Passing date value from system defined table to stored procedure called from vb.net results in an error:

将日期值从系统定义表传递到从vb.net调用的存储过程会导致错误:

Failed to convert parameter value from a DataTable to a DateTime

无法将参数值从DataTable转换为DateTime

Code:

Dim ValueTable As New DataTable
ValueTable.Columns.Add("Value", GetType(Double))
ValueTable.Columns.Add("timestamp", GetType(DateTime))
ValueTable.Columns.Add("point", GetType(Integer))
ValueTable.Columns.Add("valueNumeric", GetType(Double))

User defined table is as

用户定义的表格为

CREATE TYPE [dbo].[ValueTable] AS TABLE
                               (
                                    OID int primary key,
                                    [Value] [float] NULL,
                                    [timestamp] [datetime]  NULL,
                                    [point] [int] NULL,
                                    [valueNumeric] [float] NULL
                               )
GO

And stored procedure as below

并存储过程如下

CREATE PROCEDURE [dbo].[Insert_Values]
      @tblValues AS ValueTable READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Value (Value, timestamp, Point, ValueNumeric)
        SELECT 
            Value, timestamp, point, valueNumeric 
        FROM 
            @tblValues
END

I need to insert multiple rows through stored procedure called from vb.net code. When run as a SqlCommand from vb, I get this error:

我需要通过从vb.net代码调用的存储过程插入多行。当从vb作为SqlCommand运行时,我收到此错误:

Database Error: Failed to convert parameter value from a DataTable to a DateTime.

数据库错误:无法将参数值从DataTable转换为DateTime。

Database is SQL Server 2008.

数据库是SQL Server 2008。

1 个解决方案

#1

In the type you have created, you have 5 columns. Where as the data table which you have defined has only 4 columns defined. I think that's causing the issue. As well as you will need to change your stored procedure to include the OID column, since it's the primary key.

在您创建的类型中,您有5列。您定义的数据表只定义了4列。我认为这导致了这个问题。同样,您需要更改存储过程以包含OID列,因为它是主键。