We help IT Professionals succeed at work.

Convert a string to oledbparameter

1,418 Views
Last Modified: 2010-04-21
Hello,

If possible, how can I take a string and use it in a call to a stored procedure that is expecting an oledbparameter() array?  Actually, the subroutine that calls the stored procedure is expecting an oledbparameter() array but I only need to send in the string name of a property.  Please help, I've been breaking my head for two days now!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
I don't understand the exact problem, but to set a parameter value you should write :
      Dim parameter As New OLEDBParameter("parameterName", SqlDbType.String)
      parameter.Value = value 

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
can you clarify a bit?
because, a stored procedure by itsef does not know what a oledbparameter is, so you probably have some helper class/function that expects an oledbparameter array.

anyhow:
why not just declare a simply array of type oledbparameter objects (if typed array), and create a new oledbparameter object with the value you want to pass, and add that object to the array..
Top Expert 2007

Commented:
I have (perhaps) the same sub in a few of my applications.  Here's how you call it, and the sub is below.

'Call the sub, passing the name of the Stored Procedure, and Nothing as the ParamArray:

    Load("spStoredProcedureName", Nothing)


'And the sub itself:

    Public Shared Function Load(ByVal storedProcedureName As String, _
        ByVal ParamArray params() As SqlClient.SqlParameter) As DataTable

        Dim table As New DataTable()

        Dim adapter As New SqlClient.SqlDataAdapter()
        adapter.SelectCommand = New SqlClient.SqlCommand()
        With adapter.SelectCommand
            .CommandText = storedProcedureName
            .CommandType = CommandType.StoredProcedure
            .Connection = New SqlClient.SqlConnection(mConnectionString)

            With .Parameters
                If params IsNot Nothing Then
                    'Loop through each parameter and add it to the collection
                    For Each param As SqlClient.SqlParameter In params
                        If param IsNot Nothing Then
                            .Add(param)
                        End If
                    Next
                End If
            End With

        End With


        'Execute the StoredProcedure
        adapter.Fill(table)

        'Cleanup
        adapter.Dispose()

        Return table

    End Function

Author

Commented:
Hi,  First of all thank you both sognoct and angellll!  I figured I was being a bit hazy.  Let me see if this helps:  I have a documetn object with a property whose value I can store in a string.  But I need to then pass that string or property value to a stored procedure so I can retrieve another field.  Sognoct I think you seem to be going in the right direction.  Is this what you, angellll, are also referring to?

Thanks!

Author

Commented:
Just how much do I need to populate the oledbparameter before I can pass it on.  Refer to snippet below.  Also I am STILL getting the "cannot convert oledbparameter object to 1-dimensional array of oledbparameter".  I am paraphrasing
Dim l_strClass As String = CStr(doc.Properties("F_DOCCLASSNAME").Value)
Dim paramFNDocClass As New OleDb.OleDbParameter("FNDocClassName", doc.Properties.Item("F_DOCCLASSNAME").Value)
 
clsRetakeBus.GetDocProperties(paramFNDocClass
========================================================
Public Function GetDocProperties(ByVal docclass As System.Data.OleDb.OleDbParameter()) As DataSet
        Dim dsProperties As DataSet
        Dim l_strFNDoc_Class As System.Data.OleDb.OleDbParameter()
 
        l_strFNDoc_Class = docclass
        Me.sqlLib.Connect()
        Try
            dsProperties = sqlLib.ExecQuery("USP_SELECT_RETAKE_PROPERTIES", l_strFNDoc_Class)
 
        Catch ex As Exception
            ErrorLog.WriteErrorLog(System.Environment.MachineName, DateTime.Now.ToString, "Retake_GetDocProperties", "??", ex.Message, "SSRetakeProcess")
        End Try
        Return dsProperties
    End Function
=================================================================================
Public Function ExecQuery(ByVal procName As String, ByVal oleDBParams As OleDbParameter()) As DataSet
        Try
            Dim ds As New DataSet
            Dim cmd As OleDbCommand = New OleDbCommand(procName, Me.cn)
            cmd.CommandType = CommandType.StoredProcedure
            For Each parm As OleDbParameter In oleDBParams
                cmd.Parameters.Add(parm)
            Next
            Dim da As OleDbDataAdapter
            da = New OleDbDataAdapter(cmd)
            da.Fill(ds)
            cmd.Parameters.Clear()
            Return ds
        Catch ex As OleDbException
            System.Diagnostics.EventLog.WriteEntry("ssExport", "SQL Error in Procedure: " + procName)
            Me.SSErrorLog.WriteErrorLog(System.Environment.MachineName, DateTime.Now.ToString, "SQL_Query: " + procName, ex.ErrorCode.ToString, ex.Message, "SSBO_SQL")
        End Try
    End Function

Open in new window

Author

Commented:
OKay, the ExecQuery is expecting an array of oledbparameter object, but I don't need to pass it an array, I only have one parameter to pass it.  How can I convert one oledbparameter object into an array of oledbparameter objects?  Sorry guys, I am new to VB.net
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:

Dim oleDBParams As OleDbParameter()
Dim oleDBParam As OleDbParameter
oleDBParam  =new OleDbParameter("parameterName", SqlDbType.String)
oleDBParam.Value = yourstring
oleDBParams.Add oleDBParam  

Open in new window

Author

Commented:
Hi angellll,

Instead of SqlDbType.String I had to do SqlDbType.NVarChar since .String was not available to me.  Any ideas?  Also, the .Add method is not available to me, least not through Intellisense.

        paramFNDocClass = New OleDb.OleDbParameter("FNDocClassName", SqlDbType.NVarChar)
        paramFNDocClass.Value = l_strClass
        paramFNDocClasses.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi,  Sorry I sort of left this question neglected.  I'll have to review the code at work tomorrow so I can post how I ended up doing it based on your input and then close out the question.  Thank you fellas.

Author

Commented:
OKay, so after all is said and done the relevant code is as follows:

        Dim paramDLN(0) As OleDb.OleDbParameter
        paramDLN(0) = New OleDb.OleDbParameter("DLN", SqlDbType.NVarChar)
        paramDLN(0).Value = l_strDLN
        dsPLN = clsRetakeBus.GetPLNfromDLN(paramDLN)
        l_strPLN = CStr(dsPLN.Tables(0).Rows(0).Item("PLN"))
which is more in line with what sognoct had suggested, well at least more direct so I am awarding him/her the points.  Thank you very much!

Author

Commented:
Sognoct, thank you for taking the time to help me out and sorry it took me a while to get back to you...been learning about Datagrids now. Ciao!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.