code1994
asked on
sqldatareader over dataset
hi,
im wondering which is the best to use sqldatareader or dataset
what im doing is that, im populating my dropdownlist only, please advise.
the second question i have is:
im getting sqldatareader as arraylist whcih means its returning the arrylist, how can i use the arrylist to populate my ddl?
thank you.
im wondering which is the best to use sqldatareader or dataset
what im doing is that, im populating my dropdownlist only, please advise.
the second question i have is:
im getting sqldatareader as arraylist whcih means its returning the arrylist, how can i use the arrylist to populate my ddl?
thank you.
ASKER
thanks BM
but we have global function that use everybody and the function is returns the arrylist instead of sqldatareader now its returning my data in arrylist but how can i grab my data from arrylist and populate ddl ?
thanks
but we have global function that use everybody and the function is returns the arrylist instead of sqldatareader now its returning my data in arrylist but how can i grab my data from arrylist and populate ddl ?
thanks
code1994,
what is the type of data in arraylist ?
Regards!
B..M
mmarinov
what is the type of data in arraylist ?
Regards!
B..M
mmarinov
ASKER
here is the function that returns arrylist:
Public Overloads Shared Function ExecuteReader(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Object
'create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
Dim aryFileNames As New ArrayList
Dim strFileName As String
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters)
dr = cmd.ExecuteReader
'detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
'return the results
Do While dr.Read()
strFileName = dr(0)
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa me)
End If
Loop
Return aryFileNames
End Function 'ExecuteReader
Public Overloads Shared Function ExecuteReader(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As Object
'create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
Dim aryFileNames As New ArrayList
Dim strFileName As String
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters)
dr = cmd.ExecuteReader
'detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
'return the results
Do While dr.Read()
strFileName = dr(0)
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa
End If
Loop
Return aryFileNames
End Function 'ExecuteReader
ASKER
or please let me knwo if i need to modify or something the above reader function or returns that as sqldatareader instead of array? may be?
or what is the best approach - thanks
or what is the best approach - thanks
code1994,
i'm not quite sure but may be you can directly bind the arraylist to the dropdown - try it and tell what is the result. i think that because the contents of the arraylist is string object
Regards!
B..M
mmarinov
i'm not quite sure but may be you can directly bind the arraylist to the dropdown - try it and tell what is the result. i think that because the contents of the arraylist is string object
Regards!
B..M
mmarinov
Try this if it can help u
public DropDownListCell(ref string [] stArray, string IDName, string SelectVal,EventHandler DropDownEvents)
{
DropDownList DDLBuyerName = new DropDownList();
DDLBuyerName.ID = IDName;
ListItem LstSubInv;
for(int i = 0; i < stArray.Length; i++)
{
LstSubInv = new ListItem();
LstSubInv.Text = stArray[i];
LstSubInv.Value = stArray[i];
if (stArray[i].ToString().Tri m().ToUppe r() == SelectVal) LstSubInv.Selected = true;
DDLBuyerName.Items.Add(Lst SubInv);
}
DDLBuyerName.Enabled = false;
DDLBuyerName.AutoPostBack = true;
DDLBuyerName.SelectedIndex Changed += new System.EventHandler(DropDo wnEvents);
}
public DropDownListCell(ref string [] stArray, string IDName, string SelectVal,EventHandler DropDownEvents)
{
DropDownList DDLBuyerName = new DropDownList();
DDLBuyerName.ID = IDName;
ListItem LstSubInv;
for(int i = 0; i < stArray.Length; i++)
{
LstSubInv = new ListItem();
LstSubInv.Text = stArray[i];
LstSubInv.Value = stArray[i];
if (stArray[i].ToString().Tri
DDLBuyerName.Items.Add(Lst
}
DDLBuyerName.Enabled = false;
DDLBuyerName.AutoPostBack = true;
DDLBuyerName.SelectedIndex
}
ASKER
>>directly bind the arraylist to the dropdown
how? can u give an example pls?
thanks
how? can u give an example pls?
thanks
ASKER
kollu :
i don't think i wanna make this more complicated to just populate the data into ddl
do u think i should modify the above function of datareader ? if yes do you hae any suggestions or advise on the above function?
im a vb.net guy :)
thanks
i don't think i wanna make this more complicated to just populate the data into ddl
do u think i should modify the above function of datareader ? if yes do you hae any suggestions or advise on the above function?
im a vb.net guy :)
thanks
code1994,
Dim dt as DropDownlist
dt.DataSource = ExecuteReader ( connection object, command type, command text, param array );
dt.DataBind()
where in the executereader method you have to filled the appropriate values of parameters
also you have to put the class where executereader is based - i think it is SqlHelper.ExecuteReader
Regards!
B..M
mmarinov
Dim dt as DropDownlist
dt.DataSource = ExecuteReader ( connection object, command type, command text, param array );
dt.DataBind()
where in the executereader method you have to filled the appropriate values of parameters
also you have to put the class where executereader is based - i think it is SqlHelper.ExecuteReader
Regards!
B..M
mmarinov
ASKER
im getting the error:
Object reference not set to an instance of an object.
here is my code:
strSQL = "mysp"
Dim ddl As DropDownList
ddl.DataSource = SqlHelper.ExecuteReader(st rConnStrin g, strSQL, "mypar")
lstReseller.DataValueField = "ID"
lstReseller.DataTextField = "Name"
lstReseller.DataBind()
Object reference not set to an instance of an object.
here is my code:
strSQL = "mysp"
Dim ddl As DropDownList
ddl.DataSource = SqlHelper.ExecuteReader(st
lstReseller.DataValueField
lstReseller.DataTextField = "Name"
lstReseller.DataBind()
the ddl has to be a dropdownlist control placed on your web form
also you don't need to specified the datavalue and textfield because in the arraylist you have only strings
B..M
mmarinov
also you don't need to specified the datavalue and textfield because in the arraylist you have only strings
B..M
mmarinov
ASKER
still getting the same error message after i change what you said.....
can you please post your code so can check what is the problem ?
B..M
mmarinov
B..M
mmarinov
I think this link will be help ful to you
http://it.maconstate.edu/tutorials/ASPNET/ASPNET11/aspnet11-04.aspx
<td><asp:DropDownList id="ItemType" runat="server"
DataSource='<%# ItemTypes %>'
SelectedIndex='<%# SetIndex(Container.DataIte m("ItemTyp e")) %>'/>
</td>
Function SetIndex(TheItem As String)
Dim i As Integer
For i = 0 To ItemTypes.Count - 1
If TheItem = ItemTypes(i) Then
Return i
End If
Next
End Function
http://it.maconstate.edu/tutorials/ASPNET/ASPNET11/aspnet11-04.aspx
<td><asp:DropDownList id="ItemType" runat="server"
DataSource='<%# ItemTypes %>'
SelectedIndex='<%# SetIndex(Container.DataIte
</td>
Function SetIndex(TheItem As String)
Dim i As Integer
For i = 0 To ItemTypes.Count - 1
If TheItem = ItemTypes(i) Then
Return i
End If
Next
End Function
ASKER
here is my code BM:
dim getddl as dropdownlist
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim strSQL As String
If Not Page.IsPostBack Then
strSQL = "mysp"
str1 = SqlHelper.ExecuteReader(st rConnStrin g, strSQL, "mypara")
ddl.source = getddl
ddl.DataValueField = "ID"
ddl.DataTextField = "Name"
ddl.DataBind()
End If
End Sub
dim getddl as dropdownlist
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim strSQL As String
If Not Page.IsPostBack Then
strSQL = "mysp"
str1 = SqlHelper.ExecuteReader(st
ddl.source = getddl
ddl.DataValueField = "ID"
ddl.DataTextField = "Name"
ddl.DataBind()
End If
End Sub
i think that your code should be
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim strSQL As String
If Not Page.IsPostBack Then
strSQL = "mysp"
str1 = SqlHelper.ExecuteReader(st rConnStrin g, strSQL, "mypara")
ddl.source = str1
ddl.DataBind()
End If
End Sub
B..M
mmarinov
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim strSQL As String
If Not Page.IsPostBack Then
strSQL = "mysp"
str1 = SqlHelper.ExecuteReader(st
ddl.source = str1
ddl.DataBind()
End If
End Sub
B..M
mmarinov
ASKER
B.M - thank you bu its returning the id's but i want to get the desc
in my sql statement i have select id,name from table
its poupulating all the ids in my listbox
i even debug like
?str1(0)
its giving me number
?str1(1)
its still giving me number
any thing im missing here?
thanks
in my sql statement i have select id,name from table
its poupulating all the ids in my listbox
i even debug like
?str1(0)
its giving me number
?str1(1)
its still giving me number
any thing im missing here?
thanks
ASKER
here is the code:
'return the results
Do While dr.Read()
strFileName = dr(0) //do i have to change or something ?
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa me)
End If
Loop
Return aryFileNames
'return the results
Do While dr.Read()
strFileName = dr(0) //do i have to change or something ?
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa
End If
Loop
Return aryFileNames
try
dr("name") instead of dr(0)
B..M
mmarinov
dr("name") instead of dr(0)
B..M
mmarinov
ASKER
this function is global function to everyone and i don't think i can change unless i have strong case
is there way i can change from my side like when i calling this function and manupulate and display?
is there way i can change from my side like when i calling this function and manupulate and display?
unfortunatelly the arraylist is filled by this, so you can not do anything if you can not change it :(
B..M
mmarinov
B..M
mmarinov
ASKER
hmm... i see what you mean now....
is there a way i can have one global datareader function? like i want to create my own?
PS: is it true that once the datareader gives you the recordset and if you close the db connection the datareader data lost ?
thanks
is there a way i can have one global datareader function? like i want to create my own?
PS: is it true that once the datareader gives you the recordset and if you close the db connection the datareader data lost ?
thanks
you can create your own function and use dataset and dataadapter and don't use the sqlhelper
for the second question:
when the dataconnection is closed you can not retrieve anymore data from db. this is the difference between dataset and datareader - dataset keeps the records in memory and user want can edit/delete/add new and finally post this to the db and do all work at once
B..M
mmarinov
for the second question:
when the dataconnection is closed you can not retrieve anymore data from db. this is the difference between dataset and datareader - dataset keeps the records in memory and user want can edit/delete/add new and finally post this to the db and do all work at once
B..M
mmarinov
ASKER
i understand and thank you so much for your help.
do you have a datareader function handy? please :)
the datareader function that anybody can call and return the data
im using vb.net
thank you so much
do you have a datareader function handy? please :)
the datareader function that anybody can call and return the data
im using vb.net
thank you so much
Unfortunately i don't have it at home, but will try to manage something for you
you can use something like this
Public Function GetReader(mySelectQuery as String, connectionString as String, ByVal ParamArray commandParameters() As SqlParameter) as Object
Dim aryFileNames as New ArrayList
Dim strFileName as String
Dim myConnection as SqlConnection = New SqlConnection(connectionSt ring)
Dim myCommand As SqlCommand = New SqlCommand(mySelectQuery, myConnection)
myCommand.Parameters.Add(P aramArray)
myConnection.Open()
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
Try
Do While myReader .Read()
strFileName = myReader ("name")
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa me)
End If
Loop
Return aryFileNames
End Function
HTH
B..M
mmarinov
you can use something like this
Public Function GetReader(mySelectQuery as String, connectionString as String, ByVal ParamArray commandParameters() As SqlParameter) as Object
Dim aryFileNames as New ArrayList
Dim strFileName as String
Dim myConnection as SqlConnection = New SqlConnection(connectionSt
Dim myCommand As SqlCommand = New SqlCommand(mySelectQuery, myConnection)
myCommand.Parameters.Add(P
myConnection.Open()
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
Try
Do While myReader .Read()
strFileName = myReader ("name")
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa
End If
Loop
Return aryFileNames
End Function
HTH
B..M
mmarinov
ASKER
hmm if i want to return all the table instead of just "name" what should i do in other words whatever the sql statement i select instead of just "name"
the reason is that, for now i need "name" but for other form i may be need some other field u know so i just want to make this as a generic or global function the user will get anything they want.
i hope im not bugging ;)
thank you again.
the reason is that, for now i need "name" but for other form i may be need some other field u know so i just want to make this as a generic or global function the user will get anything they want.
i hope im not bugging ;)
thank you again.
you can do it simply by adding a parameter to the definition :-)
Public Function GetReader(mySelectQuery as String, connectionString as String, ByVal ParamArray commandParameters() As SqlParameter, ColumnName as string) as Object
Dim aryFileNames as New ArrayList
Dim strFileName as String
Dim myConnection as SqlConnection = New SqlConnection(connectionSt ring)
Dim myCommand As SqlCommand = New SqlCommand(mySelectQuery, myConnection)
myCommand.Parameters.Add(P aramArray)
myConnection.Open()
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
Try
Do While myReader .Read()
strFileName = myReader (ColumnName)
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa me)
End If
Loop
Return aryFileNames
End Function
Public Function GetReader(mySelectQuery as String, connectionString as String, ByVal ParamArray commandParameters() As SqlParameter, ColumnName as string) as Object
Dim aryFileNames as New ArrayList
Dim strFileName as String
Dim myConnection as SqlConnection = New SqlConnection(connectionSt
Dim myCommand As SqlCommand = New SqlCommand(mySelectQuery, myConnection)
myCommand.Parameters.Add(P
myConnection.Open()
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
Try
Do While myReader .Read()
strFileName = myReader (ColumnName)
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa
End If
Loop
Return aryFileNames
End Function
ASKER
BM:
I modify the function you paste above but still getting error:
Public Function GetReader1(ByVal mySelectQuery As String, ByVal connectionString As String, ByVal ColumnName As String, ByVal ParamArray1 commandParameters() As SqlParameter) As Object
Dim aryFileNames As New ArrayList
Dim strFileName As String
Dim myConnection As SqlConnection = New SqlConnection(connectionSt ring)
Dim myCommand As SqlCommand = New SqlCommand(mySelectQuery, myConnection)
myCommand.Parameters.Add(P aramArray1 )
myConnection.Open()
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
Try
Do While myReader.Read()
strFileName = myReader(ColumnName)
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa me)
End If
Loop
Return aryFileNames <---<<<< getting error can not be converted to sqlparameter
End Function
I modify the function you paste above but still getting error:
Public Function GetReader1(ByVal mySelectQuery As String, ByVal connectionString As String, ByVal ColumnName As String, ByVal ParamArray1 commandParameters() As SqlParameter) As Object
Dim aryFileNames As New ArrayList
Dim strFileName As String
Dim myConnection As SqlConnection = New SqlConnection(connectionSt
Dim myCommand As SqlCommand = New SqlCommand(mySelectQuery, myConnection)
myCommand.Parameters.Add(P
myConnection.Open()
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
Try
Do While myReader.Read()
strFileName = myReader(ColumnName)
If Trim(strFileName) <> "" Then
aryFileNames.Add(strFileNa
End If
Loop
Return aryFileNames <---<<<< getting error can not be converted to sqlparameter
End Function
how exaclty you cal this method ? because if it fails like this the problem is with the calling
B..M
B..M
ASKER
im not calling yet i just copy ur code and paste on class.vb
and when i compile i get one more new error:
Public Function GetReader1(ByVal mySelectQuery As String, ByVal connectionString As String, ByVal ColumnName As String, ByVal ParamArray1 commandParameters() As SqlParameter) As Object
class.vb(13): All parameters must be explicitly typed if any are.
and the error is pointing to ( ByVal ParamArray1 commandParameters())
and when i compile i get one more new error:
Public Function GetReader1(ByVal mySelectQuery As String, ByVal connectionString As String, ByVal ColumnName As String, ByVal ParamArray1 commandParameters() As SqlParameter) As Object
class.vb(13): All parameters must be explicitly typed if any are.
and the error is pointing to ( ByVal ParamArray1 commandParameters())
ASKER
next error im getting here:
Return aryFileNames <<<<<<<<--- here
Class.vb(30): Value of type 'System.Collections.ArrayL ist' cannot be converted to 'System.Data.SqlClient.Sql Parameter' .
Return aryFileNames <<<<<<<<--- here
Class.vb(30): Value of type 'System.Collections.ArrayL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks BM
it is better to use datareader instead of dataset, because the dataset is put in memory, but datareader is not
also you don't need to return it as arraylist just bind directly to the dropdownlist
Regards!
B..M
mmarinov