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
6  1775 
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
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
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
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
> 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
> 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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: bearclaws |
last post by:
I am trying to send URL parameters from an HTML page to an XSL page.
I used this script to pass parameters manually to the XSL and it works
great. I just modified the "input for XSL-processor"...
|
by: Andrew Thompson |
last post by:
I have written a few scripts to parse the
URL arguments and either list them or allow
access to the value of any parameter by name.
<http://www.physci.org/test/003url/index.html>...
|
by: Fatih BOY |
last post by:
Hi,
I want to send a report from a windows application to a web page like
'report.asp'
Currently i can send it via post method with a context like
local=En&Username=fatih&UserId=45&Firm=none...
|
by: VB Programmer |
last post by:
I have a function that returns a datareader. The problem is that in the
function's FINALLY I close the datareader and set it to nothing. But,
BEFORE the Finally I "Return" the datareader (in the...
|
by: Jim Owen |
last post by:
The following code does not operate properly, and neither I nor the three
..Net experts can figure out why. It's a seeming mystery. The code is simple.
A SqlCommand is executed against a stored...
|
by: tinman |
last post by:
Hi....
Assume Function A in an application calls Function GetSomeData in another
assembly..... which then is the prefered method to return the SqlDatareader
object back to Function A (and why...
|
by: simon |
last post by:
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 =...
|
by: Chris |
last post by:
Hi,
How can I send the parameter value for a stored procedure in ASP.NET
2.0 ?
Dim sds_reader As New SqlDataSource
sds_reader.SelectCommand = "STP_select_by_Type"
the line below produces...
|
by: sxwend |
last post by:
I am trying to use the following post results (http://www.thescripts.com/forum/thread189759.html) and add another requirement. I need to send the results to just the email addresses that the query...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| | |
