Execute stored proc fails from vb.net
-
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???
-
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