Results 1 to 3 of 3

Thread: QueriesTableAdapter returning 0 value

  1. #1
    wstevens is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    May 2008
    Location
    South Africa PTA
    Posts
    16
    Reputation
    75

    QueriesTableAdapter returning 0 value

    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
    Last edited by JohnH; 11-19-2008 at 7:38 PM. Reason: formatted post for readability

  2. #2
    wstevens is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    May 2008
    Location
    South Africa PTA
    Posts
    16
    Reputation
    75
    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
    Last edited by JohnH; 11-19-2008 at 7:38 PM. Reason: formatted post for readability

  3. #3
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    12,517
    Reputation
    1609
    Please post in the appropriate forum for your topic. Moved from GDI+.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Harvest time tracking