-
Aug 11th, 2011, 03:01 PM
#1
Thread Starter
Member
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()
-
Aug 11th, 2011, 08:42 PM
#2
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
-
Aug 12th, 2011, 05:44 PM
#3
Thread Starter
Member
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?
-
Aug 15th, 2011, 10:23 AM
#4
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|