QueriesTableAdapter returning 0 value

wstevens · #1 (**permalink**) 11-10-2008, 6:23 AM

Hi All

I have researched this problem and found info but none have proper solutions. I have a stored procedure. The stored procedure returns a value of 1 if the column exists in a specific table and 0 if not.

I created a QueriesTableAdapter in a Dataset telling it to use the existing stored procedure. I select it to return Single value.

If I test the query in dataset design by preview data it returns the required value correctly, however if I call it from VB.Net it always returns 0.

Here is my code

Code:

Dim scalarQueriesTableAdapter As DSMetricsTableAdapters.QueriesTableAdapter

        scalarQueriesTableAdapter = New DSMetricsTableAdapters.QueriesTableAdapter

        Dim returnValue As Integer

        returnValue = CType(scalarQueriesTableAdapter.CheckMetrics("Department", "tbl_Metrics"), Integer)

Here is my stored procedure

Code:

@ColumnName varchar(100),
@TableName varchar(100)

AS
	
IF EXISTS(SELECT 1 FROM Syscolumns WHERE NAME= @ColumnName AND id=OBJECT_ID(@TableName))
		
RETURN 1

wstevens · #2 (**permalink**) 11-11-2008, 12:49 AM

Hi all "Solution"

I hope this helps everybody else having the same problem. ScalarQueriesTableAdapter returning 0 value from a stored procedure

Create your stored procedure with a RETURN value as normal. Then open dataset designer and drag a QueriesTableAdapter onto the design screen. Create a query and use existing stored procedure you created.

Then edit the Partial class for the named query and add the code below.

Code:

  Public Function GetReturnValue(ByVal commandIndex As Integer) As Object

            Return Me._commandCollection(commandIndex).Parameters(0).Value

        End Function

Your VB code will now look like this.

Code:

Dim scalarQueriesTableAdapter As DSMetricsTableAdapters.QueriesTableAdapter
        scalarQueriesTableAdapter = New DSMetricsTableAdapters.QueriesTableAdapter
        scalarQueriesTableAdapter.CheckMetrics("Department", "tbl_Metrics")
        Dim returnValue As Integer
        returnValue = scalarQueriesTableAdapter.GetReturnValue(0)

I hope this helps somebody

jmcilhinney · #3 (**permalink**) 11-11-2008, 1:21 AM

Please post in the appropriate forum for your topic. Moved from GDI+.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)