locked
VB.NET Custom Lookup Code-Dynamic Connection String? RRS feed

  • Question

  • Hello VB.NET experts! I'm not, but I need to create a quick table lookup to use as Custom Code in an SSRS report.

    Say I have a table that looks like this:

    tableName
    ------------
    salesRepId
    customerId
    primaryRep

    I need a VB.NET function that will take two parameters, a salesRepId (theRepParm) and a customerId (theCustParm), and return a true or false. I need to loop through tableName on salesRepId(=theRepParm) paying attention to only records with a primaryRep=1. If I find a record where customerId=theCustParm, return True and bail. If I have looped through all salesRepId(=theRepParm) records and do not find customerId=theCustParm, then return False. Seems simple enough.

    Here's a kicker though. The connection string needs to be dynamic. The data source and initial catalog will vary from customer to customer. Is this possible?

    Here is a start to what I need, can I beg for help completing the query? Thanks, all!

    Function lookupCurrentCustomerRep(ByVal theRepParm AS String, ByVal theCustParm AS String) AS Boolean

    Dim oConn As New System.Data.SqlClient.SqlConnection

    oConn.ConnectionString = "Data Source=serverName; Initial Catalog=databaseName; Integrated Security=SSPI"
    oConn.Open()

    Dim oCmd As New System.Data.SqlClient.SqlCommand

    oCmd.Connection = oConn

    oCmd.CommandText = "SELECT * FROM theTable WHERE salesRepId = @theRepParm AND PrimaryRep = 1"

    'LOOP THRU RECORDS
    ' IF customerId = @theCustParm THEN
    '  RETURN TRUE
    '  EXIT LOOP
    ' ELSE
    '  RETURN FALSE
    ' END IF

    oConn.Close()

    End Function


    "A bus station is where a bus stops. A train station is where a train stops. On my desk I have a workstation..."
    Friday, April 16, 2010 2:30 PM

Answers

  • Hi,

    From your description, I think what you want is something as follow:

    Protected Function lookupCurrentCustomerRep(ByVal strServerInstance As String, ByVal strDatabase As String, ByVal CategoryID As Int32) As Boolean
    
      Dim strConnection As String = "Data Source=" + strServerInstance + ";Initial Catalog=" + strDatabase + ";Integrated Security=True"
    
      Using conn As SqlConnection = New SqlConnection(strConnection)
    
        Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Products WHERE CategoryID = @CategoryID", conn)
        cmd.Parameters.AddWithValue("CategoryID", CategoryID)
    
        conn.Open()
    
        Dim dr As SqlDataReader = cmd.ExecuteReader()
    
        If dr.HasRows Then
          Return True
        Else
          Return False
        End If
    
      End Using
    
    End Function
    

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Monday, April 26, 2010 8:22 AM
    Monday, April 19, 2010 8:08 AM

All replies

  • Hi,

    From your description, I think what you want is something as follow:

    Protected Function lookupCurrentCustomerRep(ByVal strServerInstance As String, ByVal strDatabase As String, ByVal CategoryID As Int32) As Boolean
    
      Dim strConnection As String = "Data Source=" + strServerInstance + ";Initial Catalog=" + strDatabase + ";Integrated Security=True"
    
      Using conn As SqlConnection = New SqlConnection(strConnection)
    
        Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Products WHERE CategoryID = @CategoryID", conn)
        cmd.Parameters.AddWithValue("CategoryID", CategoryID)
    
        conn.Open()
    
        Dim dr As SqlDataReader = cmd.ExecuteReader()
    
        If dr.HasRows Then
          Return True
        Else
          Return False
        End If
    
      End Using
    
    End Function
    

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Monday, April 26, 2010 8:22 AM
    Monday, April 19, 2010 8:08 AM
  • Hi Jian,

    Thanks for the suggestion. I added the following to my report's custom code:

    Public Function lookupCurrentCustomerRep(ByVal strServerInstance As String, ByVal strDatabase As String, ByVal theRep As String, ByVal theCust As String) As Boolean
    
    Dim strConnection As String = "Data Source=" + strServerInstance + ";Initial Catalog=" + strDatabase + ";Integrated Security=True"
    
    Using conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(strConnection)
    
    Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("SELECT * FROM myTable WHERE salesRepID = @theRep AND customerID = @theCust", conn)
    
    cmd.Parameters.AddWithValue("salesRepID", theRep)
    cmd.Parameters.AddWithValue("customerID", theCust)
    
    conn.Open()
    
    Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
    
    If dr.HasRows Then
    
    	Return True
    
    Else
    
    	Return False
    
    End If
    
    End Using
    
    End Function

    Then, I added a new text box to my report with the following expression:

    =IIF((Code.lookupCurrentCustomerRep("myServer","myDatabase",CStr(Fields!salesRepID.Value),CStr(Fields!customerID.Value)))=True, "True", "False")

    But when the report renders, the new report item only displays "#Error". I don't know where the problem is, but I'll continue to play with this.

     


    "A bus station is where a bus stops. A train station is where a train stops. On my desk I have a workstation..."
    Monday, April 19, 2010 3:14 PM
  • Please make sure the connection string is valid and the function works fine.

    You can also try using this function directly without IIF as:

    Code.lookupCurrentCustomerRep("myServer","myDatabase",CStr(Fields!salesRepID.Value),CStr(Fields!customerID.Value)).ToString()


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, April 20, 2010 1:48 AM
  • I removed the variable connection string and hardcoded it and at least got the report to process, but now I get this error:

    Build complete -- 0 errors, 0 warnings
    [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox2.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
    Preview complete -- 0 errors, 1 warnings

    My custom code ended up looking like this:

    Public Function lookupCurrentCustomerRep(ByVal theRep As String, ByVal theCust As String) As Boolean
    
    Dim oPerm As New System.Data.SqlClient.SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted)
    
    oPerm.assert()
    
    Dim strConnection As String = "Data Source=myServer;Initial Catalog=myDatabase;Integrated Security=True"
    
    Using conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(strConnection)
    
    Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("SELECT * FROM myTable WHERE PrimarySalesRep = @theRep AND AccountNum = @theCust", conn)
    
    cmd.Parameters.AddWithValue("PrimarySalesRep", theRep)
    cmd.Parameters.AddWithValue("AccountNum", theCust)
    
    conn.Open()
    
    Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
    
    If dr.HasRows Then
    
    	Return True
    
    Else
    
    	Return False
    
    End If
    
    End Using
    
    End Function

    And I have a temporary report item textbox on my report with the following expression (used just to test the code, the code will eventually just be used as decision-making logic):

    =IIF((Code.lookupCurrentCustomerRep(CStr(Fields!Sales_rep_master.Value),CStr(Fields!Customer_master.Value)))=True, "True", "False")

     


    "A bus station is where a bus stops. A train station is where a train stops. On my desk I have a workstation..."
    Wednesday, April 21, 2010 8:17 PM
  • I removed the variable connection string and hardcoded it and at least got the report to process, but now I get this error:

    Build complete -- 0 errors, 0 warnings
    
    [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox2.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
    
    Preview complete -- 0 errors, 1 warnings
    

    This permission issue is related to SSRS, so please start a new thread in the SSRS forum. Thanks.

    SQL Server Reporting Services
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 22, 2010 1:27 AM