Invalid CastException with SqlDBType

[adams114]

I am having a strange problem with invalid type casts. I am trying to
update a MS SQL Database with a stored procedure. When I setup the
parameters collection for the command object I get a invalid cast
exception error:

Compiler Error Message: BC30311: Value of type 'Date' cannot be
converted to 'Integer'.

The real problem here is that the type in the database and the stored
prcedure aren't integers at all rather they are datetime types. No
matter what date format or information I pass to the parameter setting
up the date I get the error or a varient of it but all are trying to
cast it to an integer.

Here's my Code:
Dim ConnectionString As String =
"server=(local);database=poolMaint;trusted_connection=true"
dim CommandText as string = "insert_Ph_Calibrations_1"

dim myConnection as new SqlConnection(ConnectionSTring)
dim myCommand as new SqlCommand(CommandText, myConnection)
dim workParam as new SqlParameter()

myCommand.CommandType = CommandType.StoredProcedure

' setup the parameters for the stored procedure

myCommand.Parameters.Add("@PhCalibration_ID_1",
SqlDBType.nchar, 1, "PhCalibration_ID_1")
myCommand.Parameters.Add("@DateRecord_2", SqlDBType.DateTime,
datetime.now, "DateRecord_2")
myCommand.Parameters.Add("@Tech_3", SqlDBType.nchar,
txtTech.text, "Tech_3")



myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()


Here's My Stored Procedure:

CREATE PROCEDURE [insert_PhCalibrations_1]
(@PhCalibration_ID_1 [uniqueidentifier],
@DateRecord_2 [datetime],
@Tech_3 [char](16))

AS INSERT INTO [PoolMaint].[dbo].[PhCalibrations]
( [PhCalibration_ID],
[DateRecord],
[Tech])

VALUES
( @PhCalibration_ID_1,
@DateRecord_2,
@Tech_3)
GO



Any help you can offer me will be greatly appreciated as I have no
clue about this casting problem

jeremiah

 

[adams114]

Thanks for your post, but I did figure out the problem eventually. You
are correct, the problem was iwith my parameters.add method.

I assumed there was a way to get SQL to generate the ID automatically
but I didn't know what it was. I will look into setting up the column
as an identity.

thanks for the help

 

[William F. Robertson, Jr.]

I am a C# coder, so I will try my best a vb syntax.

Your myCommand.Parameters.Add needs some work

First the overloads:

http://msdn.microsoft.com/library/d...clientsqlparametercollectionclassaddtopic.asp

You are trying to call the Add method passing
("@PhCalibration_ID_1",SqlDBType.nchar, 1, "PhCalibration_ID_1")
since you are passing 4 parameters it is trying to match it up to the
compiler is matching it to:
Add( string*, SqlDbType, int, string** )
* is the parameter name
**is the column name.

Now typically I have only seen the ** string used with the SqlDataAdapter
and its update command, so I don't think this is the one you are intending
to use.

The problem I believe you are having is the Add("@DateRecord_2",
SqlDBType.DateTime, datetime.now, "DateRecord_2")

The DateTime.Now is trying to cast into a int. This isn't going to work.

What I think you need to do is:

dim myParam = new SqlParameter( "@DateRecord_2", SqlDbType.DateTime )
myParam.Value = DateTime.Now
myCommand.Parameters.Add( myParam )

myParam = new SqlParameter( "@Tech_3", SqlDbType.NChar, 16 )
myParamer.Value = txtTech.Text
myCommand.Parameters.Add( myParam )

Also it appears as though you are trying to place an ID on the table. Why
not in Sql set up the column as identity and let it seed itself. You won't
know what identity to pass it and you might run into concurrency issues if
you don't let sql create its own identity.

HTH,

bill

I am having a strange problem with invalid type casts. I am trying to
update a MS SQL Database with a stored procedure. When I setup the
parameters collection for the command object I get a invalid cast
exception error:

Compiler Error Message: BC30311: Value of type 'Date' cannot be
converted to 'Integer'.

The real problem here is that the type in the database and the stored
prcedure aren't integers at all rather they are datetime types. No
matter what date format or information I pass to the parameter setting
up the date I get the error or a varient of it but all are trying to
cast it to an integer.

Here's my Code:
Dim ConnectionString As String =
"server=(local);database=poolMaint;trusted_connection=true"
dim CommandText as string = "insert_Ph_Calibrations_1"

dim myConnection as new SqlConnection(ConnectionSTring)
dim myCommand as new SqlCommand(CommandText, myConnection)
dim workParam as new SqlParameter()

myCommand.CommandType = CommandType.StoredProcedure

' setup the parameters for the stored procedure

myCommand.Parameters.Add("@PhCalibration_ID_1",
SqlDBType.nchar, 1, "PhCalibration_ID_1")
myCommand.Parameters.Add("@DateRecord_2", SqlDBType.DateTime,
datetime.now, "DateRecord_2")
myCommand.Parameters.Add("@Tech_3", SqlDBType.nchar,
txtTech.text, "Tech_3")



myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()


Here's My Stored Procedure:

CREATE PROCEDURE [insert_PhCalibrations_1]
(@PhCalibration_ID_1 [uniqueidentifier],
@DateRecord_2 [datetime],
@Tech_3 [char](16))

AS INSERT INTO [PoolMaint].[dbo].[PhCalibrations]
( [PhCalibration_ID],
[DateRecord],
[Tech])

VALUES
( @PhCalibration_ID_1,
@DateRecord_2,
@Tech_3)
GO



Any help you can offer me will be greatly appreciated as I have no
clue about this casting problem

jeremiah