Error Populating IDataParameter

[Venkatesh]

Hi

I have a class with one property that gets/sets
IdataParameter as shown below
-----------------------------------------------------------
-
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections


Public Class BusDataCommand

Private marrCommandParameters() As IDataParameter

Private Const mstrNotSupported As String = "Only
Stored Procedures are supported"

Public Sub New()
End Sub 'New
#Region "Properties"

' Parameters Property

Public Property Parameters() As IDataParameter()
Get
Return marrCommandParameters
End Get
Set(ByVal Value As IDataParameter())
marrCommandParameters = Value
End Set
End Property
' TableName property

#End Region
End Class
___________________________________________________________
__

I then try to populate this class as shown below
-----------------------------------------------------------

Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As DataSet
Dim busDataCommand As New BusDataCommand
Dim arlTableName As ArrayList
Dim intArrayCount As Integer
Dim strParameter As IDataParameter
Dim arlindex As Integer

busDataCommand.Parameters(0).ParameterName = CStr
("@pUser")
busDataCommand.Parameters(0).Value = CStr("test")
arlCommands.Add(busDataCommand)

I get a system.nullreferenceexception at line:
busDataCommand.Parameters(0).ParameterName = CStr("@pUser")

What am I doing wrong ?

Thanks,

Venkatesh

 

[William Ryan]

I wasn't sure exactly how you want this to work...

But instead of an array, I used an IDBCommand which stores a collection of
IDBparams. I also got the same thign to work using an ArrayList and
Add....so in the assignment instead of marrCommandParameters = Value
you could use marrCommandParater.Add(Value)
//assuming you made it a list.

Public Property Parameter() As IDbCommand

Get

Return IDb

End Get

Set(ByVal Value As IDbCommand)

IDb.Parameters.Add(Value)

End Set

End Property

 

[Venkatesh]

I want to populate an array of IDataParameters. I will
then loop through the array and concatenate the stored
procedure name with the parameters to create a string that
I can execute with DataReader or DataSet

Dim strParameter As IDataParameter

'For Each strParameter In busDataCommand.Parameters
' strCommandText = strCommandText & " "
& strParameter.ParameterName & _
' " " & "=" & " " & CStr
(strParameter.Value) & " "
'Next

 

[William Ryan]

Venkatesh:

Here is a class I wrote (pardong the long Function Signature...it was a
requirement). This will get you what you want....Let me knwo if you have
any questions.

Option Strict On

Imports System.Data

Imports System.Data.SqlClient

Imports System.Collections

Imports System.Configuration





Public Class DataAccess

'Place these enums in a module (VB) or create it in a Static Instance Class
in C#

Public Enum QueryResult As Integer

Success

ConnectionTimout

ConnectionFailure

InsuffiecientPermissions

Failure

End Enum

Public Enum QueryType As Integer

Standard

StoredProcedure

End Enum

Public Shared Function GetData(ByVal sql As String, ByVal al As ArrayList,
ByVal alName As ArrayList, ByRef dt As DataTable, ByRef da As
SqlDataAdapter, ByVal q As QueryType) As QueryResult

Dim cn As New
SqlConnection(ConfigurationSettings.AppSettings.Item("connectString").ToStri
ng)

Dim cmd As New SqlCommand(sql, cn)

If q.ToString = "StoredProcedure" Then

cmd.CommandType = CommandType.StoredProcedure

End If

da.SelectCommand = cmd

Try

'This is unnecessary per se, but since we want to Return precise data...

If cn.State <> ConnectionState.Open Then cn.Open()

Catch ex As SqlException

If ex.ToString.IndexOf("Timeout") > -1 Then

Return QueryResult.ConnectionFailure

End If

If ex.ToString.IndexOf("Permission") > -1 Then

Return QueryResult.InsuffiecientPermissions

End If

Return QueryResult.ConnectionFailure

End Try

If al.Count > 0 Then

For i As Integer = 0 To al.Count - 1

Dim param As New SqlParameter(CType(alName(i), String), CType(al(i),
String))

cmd.Parameters.Add(param)

Next

Else

'Do nothing

End If

Try

da.Fill(dt)

Return QueryResult.Success

Catch ex As SqlException

Return QueryResult.Failure

Finally

cn.Dispose()

cmd.Dispose()

End Try

End Function

End Class

 

[Michael Lang]

I then try to populate this class as shown below
-----------------------------------------------------------

Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As DataSet
Dim busDataCommand As New BusDataCommand
Dim arlTableName As ArrayList
Dim intArrayCount As Integer
Dim strParameter As IDataParameter
Dim arlindex As Integer

busDataCommand.Parameters(0).ParameterName = CStr
("@pUser")
busDataCommand.Parameters(0).Value = CStr("test")
arlCommands.Add(busDataCommand)

I get a system.nullreferenceexception at line:
busDataCommand.Parameters(0).ParameterName = CStr("@pUser")

What am I doing wrong ?

The Parameters collection of any provider's Command class does not
contain any default parameters. So there is no "Parameters(0)" until you
add it!

busDataCommand.Parameters.Add();
busDataCommand.Parameters(0).ParameterName = CStr("@pUser")

now that should work...

I just did a presentation last night at the St. Louis C# user group on
"writing database independant code". I've put the presenation powerpoint
and links to samples on the web. I hope it helps.

project site...
http://sourceforge.net/projects/genadonet

go to the downloads page for the presenation and a 'Gen' data provider.
I recommend it in place of declaring instances of IDbCommand and
IDbDataParameter. See the presentation for details. Also see related
links in my signature.


--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)

 

[Venkatesh]

I could not find an add method in VB.net

-----Original Message-----


The Parameters collection of any provider's Command class does not
contain any default parameters. So there is no "Parameters(0)" until you
add it!

busDataCommand.Parameters.Add();
busDataCommand.Parameters(0).ParameterName = CStr ("@pUser")

now that should work...

I just did a presentation last night at the St. Louis C# user group on
"writing database independant code". I've put the presenation powerpoint
and links to samples on the web. I hope it helps.

project site...
http://sourceforge.net/projects/genadonet

go to the downloads page for the presenation and a 'Gen' data provider.
I recommend it in place of declaring instances of IDbCommand and
IDbDataParameter. See the presentation for details. Also see related
links in my signature.


--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)

.

 

[Michael Lang]

I could not find an add method in VB.net

At some point your IDataParameter collection must be coming from a
provider specific implementation of IDataParameter. That provider will
have an Add method on its PrvParameterCollection. (Prv being provider
prefix).

Let's make it simple. Go download and use genadonet along with the
dbobjecter code generator using the latest code templates. Look through
that code to learn how to write database independant code. There is also
a powerpoint demonstrating all of this.

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)