Why do I get "Input string was not in a correct format"????

[Joe C.]

Why does this code produce the message:

"System.FormatException: Input string was not in a correct format."



Dim dbCommand As SqlCommand
Dim SQLServer As String =
"Server=sqlServerName;uid=userID;pwd=password"
Dim DataConn As SqlConnection
DataConn = New SqlConnection(SQLServer)

'for output from the stored procedure
Dim workParam As SqlParameter = Nothing

'call to store procedure which will be executed in a moment
dbCommand = New SqlCommand("SP_InsertRecord", DataConn)

'our command type is for a store procedure. Not a string SQL
statement
dbCommand.CommandType = CommandType.StoredProcedure



dbCommand.Parameters.Add(New SqlParameter("@CarType",
SqlDbType.NVarChar, 9))
If TextBox0.Text = "" Then
dbCommand.Parameters("@CarType").Value = System.DBNull.Value
Else
dbCommand.Parameters("@CarType").Value =
Replace(TextBox0.Text.Trim(), "'", "''")
End If


dbCommand.Parameters.Add(New SqlParameter("@CarCount",
SqlDbType.SmallInt, 30))
dbCommand.Parameters("@CarCount").Value = 23451

dbCommand.Parameters.Add(New SqlParameter("@DATE_ENTERED",
SqlDbType.SmallDateTime))
dbCommand.Parameters("@DATE_ENTERED").Value = Date.Now




'create parameter to return a Entry number
workParam = dbCommand.Parameters.Add(New SqlParameter("@NEWID",
SqlDbType.Int))

workParam.Direction = ParameterDirection.Output

'open connection and execute. This is not a query so
ExecuteNonQuery is used
DataConn.Open()
LabelMsg.Text = dbCommand.CommandText()

dbCommand.ExecuteNonQuery() <<<< I get the message when this line
is executed



the Stored procedure code is:


CREATE PROCEDURE [insert_FYEntries]

@CarType [nvarchar](9),

@CarCount [smallint],
@DATE_ENTERED [smalldatetime],

@NEWID int output

AS

INSERT INTO [Staff_Projections].[dbo].[FYEntries]

( [CarType],
[CarCount],
[[Date_Entered],

[FY_Entered],
[Client ID])

VALUES

( @CarType,
@CarCount,
@Date_Entered)

Select
@NEWID = @@identity
GO

 

[Vasco Veiga [MS]]

Hi Joe,

Not sure if related, but the SP (SP_InsertRecord) you're passing to the
command constructor is not the one you sent in your post with t-sql
(insert_FYEntries). Typo or really different sp ?
Still, there are some issues with the T-SQL code you sent [see inline]
You don't need to replace ' with '' (SqlParameter will take care of that for
you)

Eventually setup SQL Profiler to see if and what is hiting SQL Server.

HTH,

--
Vasco

Patterns & Practices
http://www.microsoft.com/resources/practices/

This posting is provided "AS IS" with no warranties, and confers no rights.

Why does this code produce the message:

"System.FormatException: Input string was not in a correct format."



Dim dbCommand As SqlCommand
Dim SQLServer As String =
"Server=sqlServerName;uid=userID;pwd=password"
Dim DataConn As SqlConnection
DataConn = New SqlConnection(SQLServer)

'for output from the stored procedure
Dim workParam As SqlParameter = Nothing

'call to store procedure which will be executed in a moment
dbCommand = New SqlCommand("SP_InsertRecord", DataConn)

'our command type is for a store procedure. Not a string SQL
statement
dbCommand.CommandType = CommandType.StoredProcedure



dbCommand.Parameters.Add(New SqlParameter("@CarType",
SqlDbType.NVarChar, 9))
If TextBox0.Text = "" Then
dbCommand.Parameters("@CarType").Value = System.DBNull.Value
Else
dbCommand.Parameters("@CarType").Value =
Replace(TextBox0.Text.Trim(), "'", "''")
End If


dbCommand.Parameters.Add(New SqlParameter("@CarCount",
SqlDbType.SmallInt, 30))
dbCommand.Parameters("@CarCount").Value = 23451

dbCommand.Parameters.Add(New SqlParameter("@DATE_ENTERED",
SqlDbType.SmallDateTime))
dbCommand.Parameters("@DATE_ENTERED").Value = Date.Now




'create parameter to return a Entry number
workParam = dbCommand.Parameters.Add(New SqlParameter("@NEWID",
SqlDbType.Int))

workParam.Direction = ParameterDirection.Output

'open connection and execute. This is not a query so
ExecuteNonQuery is used
DataConn.Open()
LabelMsg.Text = dbCommand.CommandText()

dbCommand.ExecuteNonQuery() <<<< I get the message when this line
is executed



the Stored procedure code is:


CREATE PROCEDURE [insert_FYEntries]

@CarType [nvarchar](9),

@CarCount [smallint],
@DATE_ENTERED [smalldatetime],

@NEWID int output

AS

INSERT INTO [Staff_Projections].[dbo].[FYEntries]

( [CarType],
[CarCount],
[[Date_Entered], There's an extra [ here

[FY_Entered],
[Client ID])

VALUES

( @CarType,
@CarCount,
@Date_Entered)

You've specified 5 input fields, but only supplied 3Check if there was an error (@@errorcount) before returning @@identity