SQL data access layer helper class using SqlParameterCollection

Posted on 2006-06-07
Last Modified: 2008-01-09
I'm trying to create a canned function to handle all non-query, parameterized SQL statements.  I've come across the following function, however, I can't seem to use it without receiving errors.

        Sub ExecuteNonQuery(ByVal sqlString As String, ByVal sqlParams As SqlParameterCollection, ByVal connectionString As String)
            Dim dbCon As New SqlConnection(connectionString)

            Dim cmdExp As SqlCommand
            cmdExp = New SqlCommand(sqlString, dbCon)

            Dim sqlParam As SqlParameter
            For Each sqlParam In sqlParams

        End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim oDA As New Globals.DataAccess.SQL
        Dim strSQL As String = "[Some SQL String]"
        Dim strConn As String = "[Some Connection String]"

        'This is where I'm stuck. I don't know how to add parameters to the collection.
        'Dim sqlParams As SqlParameterCollection

        oDA.ExecuteNonQuery(strSQL, sqlParams, strConn)
    End Sub

Question by:RobinsRL
    LVL 27

    Accepted Solution

    You need an SQLCommand to add the parameters to
    Dim objSqlCommand as SqlCommand=New SqlCommand
    With ObjSqlCommand
    .Parameters.Add("@SomeParam", SqlDbType.Int).Value = 0
    End With

    If you are using 2.0 you have to use
    AddWithValue("@SomeParam", object Value)

    Good luck
    LVL 11

    Expert Comment


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on could not pull conte…
    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now