Only one parameter passed to function but For Each...Next keeps going after first pass.  Can't understand why.

Posted on 2009-12-27
Last Modified: 2012-05-08
Can someone help me understand why this code isn't working?  I have used this function (RunProcedureDReader) repeatedly in my application with no problems but always (until now) passing multiple parameters to it.  Now for the first time (Function: AddressTypeReadAll) I'm passing only one parameter.  Ror some reason the "For Each...Next" loop doesn't stop after the parameter is processed.  On the second time through it generates an error (not surprising since there is only one parameter).  I don't even know how to begin debugging this but maybe I'm overlooking something simple.  Thanks for your help.

P.S.  The error occurs on the second execution of this line of code:


Here is the error message:

System.ArgumentNullException was unhandled by user code
  Message="The SqlParameterCollection only accepts non-null SqlParameter type objects. Parameter name: value"
       at System.Data.SqlClient.SqlParameterCollection.ValidateType(Object value)
       at System.Data.SqlClient.SqlParameterCollection.Add(Object value)
       at System.Data.SqlClient.SqlParameterCollection.Add(SqlParameter value)
       at PETSAppeal.DBUtil.RunProcedureDReader(String storedProcName, IDataParameter[] parameters, String tableName) in C:\Users\Public\Documents\Visual Studio 2008\Websites\PETSAppeal_2_01\App_Code\DBUtil.vb:line 940
       at PETSAppeal.DBUtil.AddressTypeReadAll(String _langCult) in C:\Users\Public\Documents\Visual Studio 2008\Websites\PETSAppeal_2_01\App_Code\DBUtil.vb:line 354
       at Members_Profile.FillAddrTypeList() in C:\Users\Public\Documents\Visual Studio 2008\Websites\PETSAppeal_2_01\Members\Profile.aspx.vb:line 16
       at Members_Profile.Page_Load(Object sender, EventArgs e) in C:\Users\Public\Documents\Visual Studio 2008\Websites\PETSAppeal_2_01\Members\Profile.aspx.vb:line 493
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Public Function AddressTypeReadAll(ByVal _langCult As String) As DataTable

	Dim dtAddrTypes As New DataTable

	Dim prmList(1) As SqlParameter

	prmList(0) = New SqlParameter("@LangCult", SqlDbType.VarChar, 15)

	prmList(0).Value = _langCult

	dtAddrTypes = RunProcedureDReader("dbo.procAddressType_GetAll", prmList, "tblAddrTypes")

	Return dtAddrTypes

End Function

Public Function RunProcedureDReader(ByVal storedProcName As String, _

									ByVal parameters As IDataParameter(), _

									ByVal tableName As String) As DataTable

	Dim dt As New DataTable

	Dim objCmd As New SqlCommand(storedProcName, strConn)

	Dim parameter As SqlParameter

	objCmd.CommandType = CommandType.StoredProcedure

	For Each parameter In parameters




	Dim dr As SqlDataReader = objCmd.ExecuteReader()



	Return dt

End Function

Open in new window

Question by:penlandt
    LVL 6

    Accepted Solution

    It appears that you are declaring an array that holds two values:

    Dim prmList(1) As SqlParameter

    When your function runs, it tries to process the 2nd parameter which is NULL.

    I am not versed in VB.NET, so that is just a guess.
    LVL 1

    Author Closing Comment

    OK that's embarrassing.  Sure enough, it was something simple.  I changed it to:

    Dim prmList(0) as SqlParameter

    and end of problem.  Can't believe I overlooked that...  Thanks!

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    734 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

    24 Experts available now in Live!

    Get 1:1 Help Now