0 Replies - 15125 Views - Last Post: 11 August 2011 - 12:14 PM Rate Topic: -----

#1 LarsB   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 04-August 11

Not Getting "created" values from Stored Procedure with Datase

Posted 11 August 2011 - 12:14 PM

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

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

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?

    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)

            command = New SqlCommand(sql, connection)
            adapter.SelectCommand = command

4.  Definition of stored procedure - SQL Server 2005
CREATE Procedure spARSSelectCurrentUserInfo
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()

Is This A Good Question/Topic? 0
  • +

Page 1 of 1