QueriesTableAdapter returning 0 value

[wstevens]

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

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

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

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

 

[wstevens]

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.

  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.

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]

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