asked on 6/26/2015

Using SQL Stored Procedure to insert records into SQL Server table but have error "Error converting data type nvarchar to datetime"

What is wrong with my VB.net 2010 syntax?

I am trying to insert records into SQL Server 2009 R2 table using SQL Stored Procedure but it keeps getting error "Error converting data type nvarchar to datetime".

Here's my VB.net code:

 Dim cn As New SqlConnection("Data Source=Main;Initial Catalog=Main;Persist Security Info=True;User ID=sa;Password=hello")
        Dim command As New SqlCommand
        Dim exec As SqlDataReader
        command.Connection = cn

        cn.Open()
        Dim cmd As SqlCommand = New SqlCommand("[HeaderInsertCommand]", cn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(New SqlParameter("@hdr", "[hdr]"))
        cmd.Parameters.Add(New SqlParameter("@VendorID", "[VendorID]"))
        cmd.Parameters.Add(New SqlParameter("@InvoiceDate", "[InvoiceDate]"))
        cmd.Parameters.Add(New SqlParameter("@InvoiceNo", "[InvoiceNo]"))
        cmd.Parameters.Add(New SqlParameter("@StoreNo", "[StoreNo]"))
        cmd.Parameters.Add(New SqlParameter("@ItemNo", "[ItemNo]"))
        cmd.Parameters.Add(New SqlParameter("@AmtDue ", "[AmtDue]"))
        cmd.Parameters.Add(New SqlParameter("@TotalAmtDue", "[TotalAmtDue]"))
        cmd.Parameters.Add(New SqlParameter("@SeqNo", "[SeqNo]"))

        exec = cmd.ExecuteReader()
         cn.Close()

Open in new window

When I manually execute my Stored Procedure does not run with error but when using in VB.net it gives an error.

Please help.

Thank you.

Microsoft SQL Server 2008 Visual Basic.NET

Last Comment

Queennie L

6/29/2015

Deepak Chauhan

6/26/2015

Is all paramaeters passed in the above code in same order as declared in SQL SP?

ASKER CERTIFIED SOLUTION

Éric Moreau

6/26/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Queennie L

6/26/2015

ASKER

@Eric Moreau:
I want to use my created Stored Procedure.

@deepakChauhan:

To answer your question is YES. I believe the parameters are in the same order.

USE [Main]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Description:	Insert data to Header table
-- ================================================
ALTER PROCEDURE [dbo].[HeaderInsertCommand]
	
	@hdr varchar(5) = NULL,	
	@VendorID varchar(5) = NULL,	
	--@InvoiceDate datetime = '2015-01-01 00:00:00.000',
	@InvoiceDate nvarchar = NULL,	
	@InvoiceNo int = NULL,	
	@StoreNo nvarchar(255) = NULL,	
	@ItemNo	int = NULL,	
	@AmtDue varchar(50) = NULL,	
	@TotalAmtDue money = NULL,	
	@SeqNo varchar(1) = NULL
		
	
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT INTO AcadianaPepsiHeader (hdr, VendorID, InvoiceDate, InvoiceNo, StoreNo, ItemNo, AmtDue, TotalAmtDue, SeqNo )
	VALUES (@hdr, @VendorID, @InvoiceDate, @InvoiceNo, @StoreNo, @ItemNo, @AmtDue, @TotalAmtDue, @SeqNo);		
	

	SELECT
	'hdr' As [hdr]
	,'1008' As [VendorID]
                 , CONVERT(DATETIME, [Invoice Date], 103) As [InvoiceDate]
	,[Invoice Number] As [InvoiceNo]
	,[Store Number] As [StoreNo]
	,[Customer Number] As [ItemNo]
	,SUM(ISNULL([Total], 2)) As [AmtDue]
	,SUM(ISNULL([Total], 2)) As [TotalAmtDue]
	,'1' As [SeqNo]
	FROM [Pizza].[dbo].[AcadianaPepsiExcel]
	where [Invoice Number]  is not null
	Group by 
	[Invoice Date]
	,[Invoice Number]
	,[Store Number] 
	,[Customer Number]
	,[Total]

	Order by [Store Number], [Total] DESC
	
END

Open in new window

Thank you again.

Éric Moreau

6/26/2015

You can use the above code with a SP. In the code you have provided (1st snippet), you are not providing real values, you are passing "[InvoiceDate]" as the value.

SOLUTION

Anil Golamari

6/29/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Queennie L

6/29/2015

ASKER

Thank you.

It worked.

Using SQL Stored Procedure to insert records into SQL Server table but have error &quot;Error converting data type nvarchar to datetime&quot;

Using SQL Stored Procedure to insert records into SQL Server table but have error "Error converting data type nvarchar to datetime"