Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-12-27
2
Medium Priority
?
308 Views
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:

objCmd.Parameters.Add(parameter)

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"
  ParamName="value"
  Source="System.Data"
  StackTrace:
       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)
  InnerException:
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
		objCmd.Parameters.Add(parameter)
	Next

	strConn.Open()

	Dim dr As SqlDataReader = objCmd.ExecuteReader()
	dt.Load(dr)
	strConn.Close()
	Return dt
End Function

Open in new window

0
Comment
Question by:penlandt
2 Comments
 
LVL 6

Accepted Solution

by:
hehdaddy earned 2000 total points
ID: 26128337
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.
0
 
LVL 1

Author Closing Comment

by:penlandt
ID: 31670240
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!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
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…
Loops Section Overview
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

578 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