By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,711 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,711 IT Pros & Developers. It's quick & easy.

How send SP parameters to the function, which creates dataReader

P: n/a
Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function
It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon
Jul 21 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList[i]);

}
"simon" <si*********@stud-moderna.si> wrote in message
news:ut**************@TK2MSFTNGP12.phx.gbl...
Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function
It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with parameters and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon

Jul 21 '05 #2

P: n/a
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList[i]);

}
"simon" <si*********@stud-moderna.si> wrote in message
news:ut**************@TK2MSFTNGP12.phx.gbl...
Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function
It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with parameters and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon

Jul 21 '05 #3

P: n/a
well I create an array of parameters and send it to the function which
creates the dataReader:

Dim arrayOfPar As ArrayList
Dim par1 As SqlParameter
Dim par2 As SqlParameter
par1 = New SqlParameter("@mediaId", SqlDbType.Int)
par1.Value = 1
arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE
par2 = New SqlParameter("@datumStart", SqlDbType.DateTime)
par2.Value = date.Now
arrayOfPar.Add(par2)

rdr = createDataReader(sql, False,arrayOfPar)

How I declare the array of arameters in my function?

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False, Optional ByVal arrayOfPar As ArrayList = Nothing) As
SqlDataReader

Dim oCmd As SqlCommand
Dim myReader As SqlDataReader
Dim i As Int16
oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
If Not arrayOfPar Is Nothing Then
For i = 0 To arrayOfPar.Count() - 1
oCmd.Parameters.Add(arrayOfPar(i))
Next
End If
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function

What is wrong here?
I get an error message:
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Thank you,
Simon
"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:uF****************@TK2MSFTNGP10.phx.gbl...
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList[i]);

}
"simon" <si*********@stud-moderna.si> wrote in message
news:ut**************@TK2MSFTNGP12.phx.gbl...
Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function
It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with

parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon


Jul 21 '05 #4

P: n/a
well I create an array of parameters and send it to the function which
creates the dataReader:

Dim arrayOfPar As ArrayList
Dim par1 As SqlParameter
Dim par2 As SqlParameter
par1 = New SqlParameter("@mediaId", SqlDbType.Int)
par1.Value = 1
arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE
par2 = New SqlParameter("@datumStart", SqlDbType.DateTime)
par2.Value = date.Now
arrayOfPar.Add(par2)

rdr = createDataReader(sql, False,arrayOfPar)

How I declare the array of arameters in my function?

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False, Optional ByVal arrayOfPar As ArrayList = Nothing) As
SqlDataReader

Dim oCmd As SqlCommand
Dim myReader As SqlDataReader
Dim i As Int16
oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
If Not arrayOfPar Is Nothing Then
For i = 0 To arrayOfPar.Count() - 1
oCmd.Parameters.Add(arrayOfPar(i))
Next
End If
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function

What is wrong here?
I get an error message:
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Thank you,
Simon
"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:uF****************@TK2MSFTNGP10.phx.gbl...
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList[i]);

}
"simon" <si*********@stud-moderna.si> wrote in message
news:ut**************@TK2MSFTNGP12.phx.gbl...
Always when I need data reader in my programs, I simply have functions,
which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function
It works fine, but what if my stored procedure has parameters. You don't
know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x
parameters of x type?

I think that in program you create some array type, fill it with

parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon


Jul 21 '05 #5

P: n/a
> arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE

You have not created an instace of arrayOfPar ...
Dim arrayOfPar As ArrayList
Should read:

Dim arrayOfPar As ArrayList = New ArrayList

You can't add something to something that doesn't exist!

"simon" <si*********@stud-moderna.si> wrote in message
news:uS**************@TK2MSFTNGP10.phx.gbl... well I create an array of parameters and send it to the function which
creates the dataReader:

Dim arrayOfPar As ArrayList
Dim par1 As SqlParameter
Dim par2 As SqlParameter
par1 = New SqlParameter("@mediaId", SqlDbType.Int)
par1.Value = 1
arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE
par2 = New SqlParameter("@datumStart", SqlDbType.DateTime)
par2.Value = date.Now
arrayOfPar.Add(par2)

rdr = createDataReader(sql, False,arrayOfPar)

How I declare the array of arameters in my function?

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False, Optional ByVal arrayOfPar As ArrayList = Nothing) As
SqlDataReader

Dim oCmd As SqlCommand
Dim myReader As SqlDataReader
Dim i As Int16
oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
If Not arrayOfPar Is Nothing Then
For i = 0 To arrayOfPar.Count() - 1
oCmd.Parameters.Add(arrayOfPar(i))
Next
End If
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function

What is wrong here?
I get an error message:
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Thank you,
Simon
"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:uF****************@TK2MSFTNGP10.phx.gbl...
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList[i]);

}
"simon" <si*********@stud-moderna.si> wrote in message
news:ut**************@TK2MSFTNGP12.phx.gbl...
Always when I need data reader in my programs, I simply have functions, which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function
It works fine, but what if my stored procedure has parameters. You don't know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x parameters of x type?

I think that in program you create some array type, fill it with

parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon



Jul 21 '05 #6

P: n/a
> arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE

You have not created an instace of arrayOfPar ...
Dim arrayOfPar As ArrayList
Should read:

Dim arrayOfPar As ArrayList = New ArrayList

You can't add something to something that doesn't exist!

"simon" <si*********@stud-moderna.si> wrote in message
news:uS**************@TK2MSFTNGP10.phx.gbl... well I create an array of parameters and send it to the function which
creates the dataReader:

Dim arrayOfPar As ArrayList
Dim par1 As SqlParameter
Dim par2 As SqlParameter
par1 = New SqlParameter("@mediaId", SqlDbType.Int)
par1.Value = 1
arrayOfPar.Add(par1) ' HERE I GET AN ERROR MESSAGE
par2 = New SqlParameter("@datumStart", SqlDbType.DateTime)
par2.Value = date.Now
arrayOfPar.Add(par2)

rdr = createDataReader(sql, False,arrayOfPar)

How I declare the array of arameters in my function?

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As
Boolean = False, Optional ByVal arrayOfPar As ArrayList = Nothing) As
SqlDataReader

Dim oCmd As SqlCommand
Dim myReader As SqlDataReader
Dim i As Int16
oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
If Not arrayOfPar Is Nothing Then
For i = 0 To arrayOfPar.Count() - 1
oCmd.Parameters.Add(arrayOfPar(i))
Next
End If
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function

What is wrong here?
I get an error message:
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Thank you,
Simon
"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:uF****************@TK2MSFTNGP10.phx.gbl...
You can create an ArrayList or any collection of Parameter objects, then
just loop through the collection and add them to the Command object's
parameters collection
SqlCommand cmd = new SqlCommand("SomeSelectStatement", SomeConnection);
for(int i = 0; i < ArrayList.Count-1; i++){
cmd.Parameters.Add((SqlParameter)myArrayList[i]);

}
"simon" <si*********@stud-moderna.si> wrote in message
news:ut**************@TK2MSFTNGP12.phx.gbl...
Always when I need data reader in my programs, I simply have functions, which creates it for me:

Dim rdr As SqlDataReader
dim sql as string

sql="myStoredProcedure"
rdr = createDataReader(sql, False)

And the functions are:

Function createDataReader(ByVal sqlStr As String, Optional ByVal type As Boolean = False) As SqlDataReader
Dim oCmd As SqlCommand
Dim myReader As SqlDataReader

oCmd = New SqlCommand(sqlStr, createConnection)
If type = False Then
oCmd.CommandType = CommandType.StoredProcedure
End If
myReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection )
Return myReader
End Function

Function createConnection() As SqlConnection
Dim myConn As SqlConnection
myConn = New
SqlConnection(ConfigurationSettings.AppSettings("a ppStrConnection"))
myConn.Open()
createConnection = myConn
End Function
It works fine, but what if my stored procedure has parameters. You don't know how many and you don't know the type of them.
Does anybody know how to change this function, that it will except the x parameters of x type?

I think that in program you create some array type, fill it with

parameters
and then send this array to function and in function create as many
parameters as array size is or something similar. Any idea?

Thank you for your help,
Simon



Jul 21 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.