Error converting data type nvarchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error converting data type nvarchar to numeric

Author

Topic

rtbmil
Starting Member

2 Posts

Posted - 2014-12-12 : 10:15:17

I know this has been asked numerous times, but they all involved statements much more complicated than mine. I couldn't figure out my problem from the other posts.

I am about as close to a newbie as you can get...

I am using MS SQL Server 2008R2 along with VB 2010.

The first question is: why is it even trying to convert anything to numeric? I have NO numeric data types. And I don't have any nvarchar data types either. I'm very confused.
What am I doing wrong? Doesn't nchar include any and all characters, in any combination? Should I change everything to text data type? Maybe something else?

Some values are going to be blank. The Lab/Source Lots have numbers, letters and dashes.

My stored procedure:


ALTER PROCEDURE dbo.MChemsInsert
	(
	@LabLot nchar(10),
	@Chem nchar(50),
	@Source nchar(50),
	@SourceLot nchar(10),
	@ExpireDt date,
	@OpenDt nchar(10),
	@Opener nchar(10),
	@DisposeDt nchar(10),
	@Disposer nchar(10),
	@RecdDt date
	)

	AS
	SET NOCOUNT OFF; 

INSERT INTO [MChems]
([LabLot], [Chem], [Source], [SourceLot], [ExpireDt], [OpenDt], [Opener], [DisposeDt], [Disposer], [RecdDt])
VALUES (@LabLot, @Chem, @Source, @SourceLot, @ExpireDt, @OpenDt, @Opener, @DisposeDt, @Disposer, @RecdDt) 

SELECT LabLot, Chem, Source, SourceLot, ExpireDt, OpenDt, Opener, DisposeDt, Disposer, RecdDt
FROM  [MChems]
WHERE (LabLot = SCOPE_IDENTITY())

I believe the query builder came up with that code.
I have tried entering "NULL" for all the blank values, but the error converting data type nvarchar to numeric always shows.

The VB code I am using, if that is helpful:


        sqlCon = New SqlConnection(strConn)
        Using (sqlCon)

            Dim sqlComm2 As New SqlCommand()
            sqlComm2.Connection = sqlCon

            sqlComm2.CommandText = "MChemsInsert"
            sqlComm2.CommandType = CommandType.StoredProcedure

            sqlComm2.Parameters.AddWithValue("LabLot", txtLabLot.Text)
            sqlComm2.Parameters.AddWithValue("Chem", txtChem.Text)
            sqlComm2.Parameters.AddWithValue("Source", txtSource.Text)
            sqlComm2.Parameters.AddWithValue("SourceLot", txtSourceLot.Text)
            sqlComm2.Parameters.AddWithValue("ExpireDt", txtExp.Text)
            sqlComm2.Parameters.AddWithValue("OpenDt", txtOpen.Text)
            sqlComm2.Parameters.AddWithValue("Opener", cboOpen.Text)
            sqlComm2.Parameters.AddWithValue("DisposeDt", txtDispose.Text)
            sqlComm2.Parameters.AddWithValue("Disposer", cboDispose.Text)
            sqlComm2.Parameters.AddWithValue("RecdDt", txtRecd.Text)

            sqlCon.Open()
            sqlComm2.ExecuteNonQuery()
            sqlCon.Close()

        End Using

Thank you so much for any and all help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-12 : 10:20:56

1. Why do you think it is "trying to convert anything to numeric". Do you get an error message about it? HINT: Post the error message(s).
2. You DO have a numeric type: SCOPE_IDENTITY(). SEE: http://msdn.microsoft.com/en-us/library/ms190315.aspx

rtbmil
Starting Member

2 Posts

Posted - 2014-12-12 : 10:55:21

I'm sorry, I thought the title was enough. The error is: error converting data type nvarchar to numeric.

I commented out the

WHERE (LabLot = SCOPE_IDENTITY()

line, and it works!

Thank you so very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources