Results 1 to 4 of 4

Thread: Not Getting "created" values from Stored Procedure with Dataset

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    38

    Not Getting "created" values from Stored Procedure with Dataset

    I am using VB.Net 2010 and calling a stored procedure with a dataset
    to get values in a table plus "created" values. (Please see the
    procedure below to see what I mean by created, such as "as
    Security_AuditsReadFlag".)

    The values for the "created" values do not come across from the stored
    procedure. They all show as zero in the IDE and False for the
    variables. I know I am getting the record because the first five
    values come across from the stored procedure into the variables
    correctly.

    When I step through a VB6 program using ADO the values are pulled
    across from the stored procedure.

    The problem seems to be in the interface between a dataset and "created"
    fields in a stored procedure.

    Does anywone know how to resolve this?


    Code:
    1.
        Public Structure UserRecord
            Dim User_ID As Integer
            Dim User_NTDomain As String
            Dim User_Login As String
            Dim User_FirstNm As String
            Dim User_LastNm As String
            Dim Security_AuditsReadFlag As Boolean
            Dim Security_AuditsUpdateFlag As Boolean
            Dim Security_AuditsDeleteFlag As Boolean
            Dim Security_DataAdminFlag As Integer
            Dim Security_ARSAdminFlag As Boolean
        End Structure
    
        Public gCurrentUser As UserRecord   
    
    2.  Code to get values from dataset
                With gCurrentUser
                    .User_ID = System.Convert.ToInt32(DSet.Tables(0).Rows(0).Item("User_Id"))
                    .User_NTDomain = DSet.Tables(0).Rows(0).Item("User_NTDomain").ToString
                    .User_Login = DSet.Tables(0).Rows(0).Item("User_Login").ToString
                    .User_FirstNm = DSet.Tables(0).Rows(0).Item("User_FirstNm").ToString
                    .User_LastNm = DSet.Tables(0).Rows(0).Item("User_LastNm").ToString
                    .Security_AuditsReadFlag = System.Convert.ToBoolean(DSet.Tables(0).Rows(0).Item("Security_AuditsReadFlag"))
                    .Security_AuditsUpdateFlag = System.Convert.ToBoolean(DSet.Tables(0).Rows(0).Item("Security_AuditsUpdateFlag"))
                    .Security_AuditsDeleteFlag = System.Convert.ToBoolean(DSet.Tables(0).Rows(0).Item("Security_AuditsDeleteFlag"))
                    '.Security_DataAdminFlag = System.Convert.ToBoolean(DSet.Tables(0).Rows(0).Item("Security_DataAdminFlag"))
                    .Security_ARSAdminFlag = System.Convert.ToBoolean(DSet.Tables(0).Rows(0).Item("Security_ARSAdminFlag"))
    
                    .Security_DataAdminFlag = CInt(DSet.Tables(0).Rows(0).Item("Security_DataAdminFlag"))
                End With
    
    3. Code to fill dataset
            Dim connectionString As String
            Dim connection As SqlClient.SqlConnection
            Dim command As SqlClient.SqlCommand
            Dim adapter As New SqlClient.SqlDataAdapter
            Dim DSet As New DataSet
            Dim sql As String
    
            connectionString = GetConnectionString
    
            sql = "Exec spARSSelectCurrentUserInfo"
    
            connection = New SqlClient.SqlConnection(connectionString)
    
            Try
                connection.Open()
                command = New SqlCommand(sql, connection)
                adapter.SelectCommand = command
                adapter.Fill(DSet)
    
    4.  Definition of stored procedure - SQL Server 2005
    CREATE Procedure spARSSelectCurrentUserInfo
    as
    Select *, 
    	Is_Member('ARSAuditRUDARole') | Is_Member('ARSAuditRUDRole') | Is_Member('ARSAuditRURole') | Is_Member('ARSAuditRRole') as Security_AuditsReadFlag,
    	Is_Member('ARSAuditRUDARole') | Is_Member('ARSAuditRUDRole') | Is_Member('ARSAuditRURole') as Security_AuditsUpdateFlag,
    	Is_Member('ARSAuditRUDARole') | Is_Member('ARSAuditRUDRole') as Security_AuditsDeleteFlag,
    	Is_Member('ARSAuditRUDARole') as Security_DataAdminFlag,
    	Is_Member('ARSAdminsRole') as Security_ARSAdminFlag,
    	Is_Member('ARSOnlineFormsURole') as Security_ARSOnlineFormsUpdateFlag
    From Users
    Where User_NTDomain + '\' + User_Login = sUser_sName()

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Not Getting "created" values from Stored Procedure with Dataset

    A couple things... 1) set yoru sql to jsut the name of the sproc... drop the "exec" 2) after creating your command object, set the command type to stored procedure. 3) are you getting an error or what? how do you know it's not brining over the "created fields?" I do this stuff all the time, never a problem... start with 1 & 2 above, see if that resolves the issue. If not, then more info is needed. Error messages, what ever. How are you validating the data? Have you tried debugging and stepping through the code and seeing what you're getting in your dataset?

    something else that might help... seeing where your variables are being dimmed ... it's possible your DSet isn't scoped properly... but it's hard to tell from the way you presented the code.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    38

    Re: Not Getting "created" values from Stored Procedure with Dataset

    Thanks for the ideas.

    I double-checked that the VB6 app was able to get the "created" values and realized it was not looking at the same database as the new program.

    The database I have now pointed it to is local on my PC. I am the dbo in it and the DBA just told me Is_Member(), which the stored procedure I am using uses to test for roles, always returns 0 for the dbo.

    Do you know of another way for a VB.Net program to find out the current role in SQL Server 2000?

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    38

    Re: Not Getting "created" values from Stored Procedure with Dataset

    FYI
    We have modified our stored procedure which uses Is_Member(...) to set all our involved roles to true for is_member("db_owner") = 1.

    This procedure is only needed in my local database, where I am the db_owner. This does not happend in our UAT database or in Production.

    Thanks for your ideas.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width