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.
Queennie LAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Is all paramaeters passed in the above code in same order as declared in SQL SP?
0
Éric MoreauSenior .Net ConsultantCommented:
where do you set the values?

Your code should look like this (taken from http://vbnetsample.blogspot.ca/2007/10/using-sqlparameter-class.html):
param(3) = New SqlParameter("@TestDate", SqlDbType.DateTime)
 param(3).Value = DateTime.Now
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Queennie LAuthor Commented:
@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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Éric MoreauSenior .Net ConsultantCommented:
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.
0
Anil GolamariCommented:
As Eric mentioned above you are trying to pass a string value and not actual value that you are planning to do so.

 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

0
Queennie LAuthor Commented:
Thank you.

It worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.