Re: Adding date to parameter
- From: "William \(Bill\) Vaughn" <billvaRemoveThis@xxxxxxxxxx>
- Date: Thu, 25 Jan 2007 09:59:42 -0800
Yes, setting the ParameterName "names" the parameter object, but
unfortunately, the OLE DB driver does not know how to enable named
parameters as required by the low-level interfaces. This was only turned on
in very later versions of ADO classic and is another reason to use the
SqlClient provider which is specifically written for SQL Server. The OLE DB
interface is what I call an "OSFA" (one-size-fits-all) interface. It's a lot
slower and does not expose all of the features featured by the "native"
managed providers like SqlClient.
I explain all of this in detail in my book.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"T McDonald" <TMcDonald@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:34330A8F-0F87-4356-BEE9-90A41BB5D9F0@xxxxxxxxxxxxxxxx
I made the assumption that Parameter(x).ParameterName = "@parameter"
"named"
the parameter. I didn't know what you meant. Actually I didn't really
understand what W.G. Ryan meant either. However, he said just enough to
make
me think it was the 'ordering'.
So is my assumption correct that using oledb 'parameters' follows the
ordering of the variables in the stored procedure then?
"William (Bill) Vaughn" wrote:
Thus my comment about named parameters.... ;)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"T McDonald" <TMcDonald@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5C23680E-6DBB-44D5-B148-7E80F8CE7BD6@xxxxxxxxxxxxxxxx
You were right on the mark. Originally the code has @index as
parameter(2),
which was ok when the date didn't exist. I changed the order of the
parameters so that @ClaimedDate was paramter(2) and @index was
parameter(3).
I made the assumption that the stored procedure went by the
@paramtername
not
the order.
Thank you for that insight!
"W.G. Ryan [MVP]" wrote:
Check your indexes. 0 is the first param, so 3 maps to the @index
variable
which is an int. I think this is your issue, but if not, let me know.
"T McDonald" <TMcDonald@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DF708DCB-FDE9-4742-B3F2-67DC356876FD@xxxxxxxxxxxxxxxx
I'm having a problem inserting a date into a SQL server database
using
a
parameter. I keep getting the following error:
"Implicit conversion from data type datetime to int is not allowed.
Use
the
CONVERT function to run this query."
ClaimedDate is declared as a String. When I run through the
debugger,
ClaimedDate does appear to have a properly formatted date.
Parameters(3) = New Data.OleDb.OleDbParameter
Parameters(3).ParameterName = "@WhenClaimed"
Parameters(3).OleDbType = OleDbType.Date
I've tried adding the value with both of the following with no
success:
Parameters(3).Value = DateTime.Parse(ClaimedDate)
Parameters(3).Value = CDate(ClaimedDate)
I am using the following stored procedure:
ALTER PROCEDURE [dbo].[spUpdateClaimedBy]
-- Add the parameters for the stored procedure here
@ClaimedBy varchar(50) = 0,
@ClaimedByName varchar(250) = 0,
@WhenClaimed datetime = date,
@Index int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE dbo.Accounts
SET ClaimedBy = @ClaimedBy, ClaimedByName = @ClaimedByName,
WhenClaimed
=
@WhenClaimed
WHERE [Index] = @Index
END
Any help would be greatly appreciated.
.
- References:
- Re: Adding date to parameter
- From: W.G. Ryan [MVP]
- Re: Adding date to parameter
- From: T McDonald
- Re: Adding date to parameter
- From: William \(Bill\) Vaughn
- Re: Adding date to parameter
- From: T McDonald
- Re: Adding date to parameter
- Prev by Date: DefaultView.RowFilter
- Next by Date: Re: Simple ADO tasks
- Previous by thread: Re: Adding date to parameter
- Next by thread: Re: Adding date to parameter
- Index(es):
Relevant Pages
|
