0 Replies - 5279 Views - Last Post: 11 August 2011 - 04:51 PM Rate Topic: -----

#1 LarsB  Icon 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 - 04:51 PM

I tried to post this earlier today but there was a server error and I do not find it when I search with Google, so I am trying again.


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?


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()





Is This A Good Question/Topic? 0
  • +

Page 1 of 1