Quick database question

[Magnus53]

Hi,

I'm getting a Type Mismatch error when trying to execute a function that'll update the database, and get the unqiue id number of the new record.


These are the tools I'm using:
SQL Server 2000
VB 6

I'm trying to add a new address record into the database. I need the query to return the unique id of the record, so I can use it later to update the consultant record.

I have this stored procedure:

CREATE PROCEDURE usp_CreateNewAddress (	   
					@addr_type 		CHAR(1),
                                 	@addr_str 		VARCHAR(60),
                                 	@addr_city 		VARCHAR(40),
                                 	@addr_post_zip_code 	VARCHAR(10),
                                 	@prst_code 		CHAR(2),
                                 	@ctry_code 		VARCHAR(20),
                                 	@addr_ph 		VARCHAR(14)
                                	)
AS
BEGIN

    SET NOCOUNT ON
    
      BEGIN
   
   	    INSERT INTO Address (addr_type,
           	                 addr_str,
                   	         addr_city,
                           	 addr_post_zip_code,
               	             	 prst_code, 
                   	         ctry_code,
                           	 addr_ph)
      	    VALUES (@addr_type,
              	    @addr_str,
              	    @addr_city,
              	    @addr_post_zip_code,
              	    @prst_code, 
             	    @ctry_code,
              	    @addr_ph)
   
      	    SELECT  @@IDENTITY -- MAX(addr_num) FROM Address
   
	END
    
        RETURN 1
    
    ErrorHandler:
    
        /* set the return value to -1 (failure) */
        RETURN -1
    
    END /* usp_CreateNewAddress */
    
    
    GO
    
    GRANT EXECUTE ON usp_CreateNewAddress TO public
GO

This procedure works properly in the query analyzer. But it won't work in the VB data enviroment.

This is how i'm trying to use the sp:

Public Function createNewAddress() As Long

 On Error GoTo ExitHandler
 
 Dim denvDB As New denvOpsDB

 createNewAddress = denvDB.createNewAddress("C", "199 Huntley Street", "Victoria", "q1w2e3", "BC", "CANADA", "5556792")
   
ExitHandler:
 
If Err Then
        createNewAddress = -1
        gobjErrManager.HandleError Err.Number, Err.Description, veErrSevError
    End If
    
    Set denvDB = Nothing

End Function

If anyone can help I'd appreciate it.

Thanks

 

[hmckillop]

Do you have the code for the object denvOpsDB, specifically the method createNewAddress.
If you are calling a stored proc with parameters it should include code something like

cmdObject.connection = cnnObject
cmdObject.type=vbStoredProc
cmdObject.text="CreateNewAddress"
'loop through adding all your parameter values
e.g. cmdObject.Parameters.Add prm1 ' with prm1 being setup with your corresponding values
Create recsetObject
recsetObject=cmdObject.execute

But without the code cant tell you whats going on.

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.