Error when using Stored Procedure and DateTime

[UncleCake]

I am using VB6 and a SP in SQL Server 2000. It works for me before I add the InvDate line so I know the problem is there. It gives me the error of "Optional feature not implemented". My field is set up as a Name: InvDate, DataType: datetime, Size: 7.

I know it is in the InvDate part, but I don't know where it is going wrong

Here is my code in VB6.

Private Sub Command1_Click()
    Dim cnConn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim prm As New ADODB.Parameter
    
     cnConn.Open "Driver={SQL Server};Server=hhb-intranet;Database=HHbShippers;uid=jk;pwd=jk;"
      cmd.ActiveConnection = cnConn
    ' If cnConn.State = adStateOpen Then MsgBox "Here"
     
     cmd.CommandText = "sp_ScannerInsertInvoice"
     cmd.CommandType = adCmdStoredProc

      '   cmd.Parameters.Append cmd.CreateParameter("ScannerID", adInteger, adParamInput, , 2)
     cmd.Parameters.Append cmd.CreateParameter("Invoice", adChar, adParamInput, 7, "G10008")
     cmd.Parameters.Append cmd.CreateParameter("InvDate", adDBDate, adParamInput, , "6/1/2005 8:53:27 AM")
     cmd.Parameters.Append cmd.CreateParameter("EmployeeID", adInteger, adParamInput, , 1001)
     
     cmd.Execute
     Set cmd = Nothing
     
End Sub

Here is my SP.

CREATE PROCEDURE dbo.sp_ScannerInsertInvoice (

	--Parameters
	@Invoice char (7),
	@EmployeeID int,
	@InvDate DateTime

)

AS

INSERT INTO Invoices
(Invoice, EmployeeID, Complete, InsertDateTime, InvDate)
VALUES
(@Invoice, @EmployeeID, 0, GetDate(), @InvDate)
GO

 

[jbenson001]

Try removing the "AM" in your parameter code. Maybe SQL Server doesn't like this for some reason.

 

[UncleCake]

I tried that also. From the SQL Query Analyzer it works when I run it, but when I call it from my VB app it has that error.

 

[jbenson001]

Does the error occur on the VB line, or once you call the SP?

 

[jdgonzalez]

try changing:

cmd.Parameters.Append cmd.CreateParameter("InvDate", adDBDate, adParamInput, , "6/1/2005 8:53:27 AM")

to

cmd.Parameters.Append cmd.CreateParameter("InvDate", adDBDate, adParamInput, 25, "6/1/2005 8:53:27 AM")

It may be because the length is not specified.

 

[UncleCake]

It errors on cmd.Execute. If I remove the InvDate line from the VB and the SP it works fine. Since the SP works in the Query Analyzer I thought it would the VB code.

 

[jdgonzalez]

Can you print the exact error message? I remember I had something similar happen.

 

[SQLBill]

I thought it would the VB code

Have you posted this in a VB forum? If it is in the VB code, you might get a quicker solution from a forum dedicated to VB.

Just a thought...

-SQLBill

Posting advice: FAQ481-4875

 

[UncleCake]

No, I haven't. I didn't know if the programming part of forum would be the proper place. I will try the VB forum.

Thanks for your help.

-Uncle Cake

 

[vongrunt]

Order of declared parameters is different than order of input arguments in sproc - and .namedParameters is not used.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)