Post reply

Execute stored proc fails from vb.net

  • June 10, 2004 at 5:54 pm

    #111558

    I am trying to open a VB.net 2003 dataset via a stored procedure.  The stored procedure is retrieving data from an Access 97 database that is set up as a linked server.  Believe me, if I had a choice, the data would not be in Access 97 anymore, but....I don't.

    So, I have my SP set up and I can execute it in query analyser (SQL Server 2000) and it works - all the correct rows are returned.  I set ANSI_NULLS and ANSI_WARNINGS on when I created the SP. 

    However, when I try to open the dataset from VB, I get a System Exception error.  Suggestions??

    Here is the code that created the stored procedure:

    ****************************************

    SET ANSI_NULLS ON

    GO

    SET ANSI_WARNINGS ON

    GO

    CREATE PROCEDURE dbo.spGet2004HalibutEmTripData

    @TripID nvarchar(8)

    AS

    SELECT

     [HailOut] As HailOutNumber,

     [Setnum] As StringNumber,

     [StartDt] As StartDate,

     Convert(Decimal(8,4),Round([Lat_Start],-2, 1)/100) + Convert(Decimal(8,4),([Lat_Start]-Round([Lat_start],-2,1)))/60 as StartLatitude,

     Convert(Decimal(8,4),Round([Long_Start],-2, 1)/100) + Convert(Decimal(8,4),([Long_Start]-Round([Long_Start],-2,1)))/60 as StartLongitude,

     Strt_Depth as StartDepth,

     [EndDt] as EndDate,

     Convert(Decimal(8,4),Round([Lat_Fin],-2, 1)/100) + Convert(Decimal(8,4),([Lat_Fin]-Round([Lat_Fin],-2,1)))/60 as EndLatitude,

     Convert(Decimal(8,4),Round([Long_Fin],-2, 1)/100) + Convert(Decimal(8,4),([Long_Fin]-Round([Long_Fin],-2,1)))/60 as EndLongitude,

     Fin_Depth as EndDepth,

     MajorArea As GMUArea,

     PFMAArea,

     PFMASubArea

    FROM OpenQuery(EMP_2004HalibutEmTripData, 'Select * from tblEmSet')

    WHERE [HailOut]  Like @TripID

    GO

    ************************************

    If I do this in QA, I get the correct result set:

    ************************************

    DECLARE @rc int

    DECLARE @TripID nvarchar(8)

    -- Set parameter values

    SET @TripID = '%'

    EXEC @rc = [FMP].[dbo].[spGet2004HalibutEmTripData] @TripID

    ************************************

    BUT!!! Trying this in VB.net 2003 does not work.

    ************************************

    'Open the Fishing Event Data Set; sConnString defines the SQL connection path, security etc

    Dim conSQL As New SqlConnection(sConnString)

    'Open the connection

    conSQL.Open()

    Dim parmSQLTripID As New SqlParameter("@TripID", "%")

    parmSQLTripID.Direction = ParameterDirection.Input

    Dim cmdSQL As New SqlCommand("spGet2004HalibutEmTripData", conSQL)

    cmdSQL.CommandType = CommandType.StoredProcedure

    cmdSQL.Parameters.Add(parmSQLTripID)

    Dim daFishingEvent As New SqlDataAdapter(cmdSQL)

    Dim dsFishingEvent As DataSet

    daFishingEvent.Fill(dsFishingEvent)

    **********************************

    The code fails on the Fill command with the following error message:

    **********************************

    An unhandled exception of type 'System.ArgumentNullException' occurred in system.data.dll

    Additional information: Value cannot be null.

    **********************************

    Any ideas???

     

  • June 11, 2004 at 1:40 pm

    #510142

    Oops.  I found the problem myself - persistence is the key.  I omitted the "New" keyword when I declared the DataSet variable.

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply