Re: Invalid CastException with SqlDBType

From: William F. Robertson, Jr. (wfrobertson_at_kpmg.com)
Date: 05/26/04 

Date: Wed, 26 May 2004 17:42:41 -0500

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/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlparametercollectionclassaddtopic.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

<adams114@comcast.net> wrote in message
news:svspa01nrialges34kt63cd8m24mgt5blp@4ax.com...
> 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
>

Relevant Pages

  • Re: Error querying Access text fields if dash or minus sign in middle of text
    ... You barely see any SQL text examples and maybe casting in SQL was buried ... So you are saying Access does not support the basic SQL call, Cast as ... value to string - this sounds difficult in MS Access or am I missing ... I thought with Access you had to use Str(). ...
    (microsoft.public.access.queries)
  • Re: Error querying Access text fields if dash or minus sign in middle of text
    ... Instead of casting to string in Microsoft access 2003 I had to use the ... And for anyone who likes to look at the actual SQL in MS Access, ... I will try Format() as you say. ... > So you are saying Access does not support the basic SQL call, Cast as ...
    (microsoft.public.access.queries)
  • Re: SQL 2000: Inserting multiple rows into a single table
    ... and my question is on the order details. ... > to set up a stored procedure that essentially inserts in the orders ... I also need to do this via SQL 2000. ... > me a string of data per column for order details, ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedures
    ... Looking at your SQL statement, a "normal" stored procedure won't be ... Public Function vLookup(ByVal table As String, ... Dim StringToReturn As String ...
    (microsoft.public.dotnet.languages.vb)
  • Re: User Login via SQL Server...(ASP Pages)...
    ... Even if your code did work, it would leave you vulnerable to SQL ... Injection attacks. ... a stored procedure that returns 0 or 1 if the user is found in the ... >because my SQL string is whack? ...
    (microsoft.public.dotnet.framework.aspnet.security)