Populating a combo using n-Tier datasets

  • Thread starter Thread starter Bruce Parr
  • Start date Start date
B

Bruce Parr

All,

I would love an answer to this issue...

I am trying to populate a combo using DataSets in an n-tier model. I have
the function in the form called FillCombo send the SQL paramters to a
support DLL. The support DLL in turn calls the data layer to actually
retrieve the data from the database. What I need to be able to do is pass
the DataSet from the data layer through the support layer to the
presentation layer. I am stepping through the code and see the data populate
at the data layer. I cannot see if it is getting through from there though.

ComboBox1.DataSource = objSupport.FillCtl(sSQL) (returns a dataset)

Public Function FillCtl(sSQL...) as Dataset
...
ds = objDB.RecordGet(sSQL)
FillCtl = ds.copy

...


Anyone have any examples or suggestions? I have tried populating the DataSet
using a DataAdapter, then using .copy to copy the dataset to the function so
it gets returned. This is not working...

Thanks,
Bruce
 
Two things that I can think of..

1) do you have the line : ComboBox1.DataBind() ?

2) if your SQL statement returns more that one table than you need to tell the combobox which table you want to bind..

I this helps..


Wayne
All,

I would love an answer to this issue...

I am trying to populate a combo using DataSets in an n-tier model. I have
the function in the form called FillCombo send the SQL paramters to a
support DLL. The support DLL in turn calls the data layer to actually
retrieve the data from the database. What I need to be able to do is pass
the DataSet from the data layer through the support layer to the
presentation layer. I am stepping through the code and see the data populate
at the data layer. I cannot see if it is getting through from there though.

ComboBox1.DataSource = objSupport.FillCtl(sSQL) (returns a dataset)

Public Function FillCtl(sSQL...) as Dataset
...
ds = objDB.RecordGet(sSQL)
FillCtl = ds.copy

...


Anyone have any examples or suggestions? I have tried populating the DataSet
using a DataAdapter, then using .copy to copy the dataset to the function so
it gets returned. This is not working...

Thanks,
Bruce
 
Hello Bruce



Yes you can pass a dataset across tiers. I have been doing so for some time
now, including through tcp remoting.





Be aware that you do not have to use a dataset, you could use the lighter
DataTable for the same purpose.





Here is an example of my data tier code:



Public Overloads Function GetPersons(ByVal PersonFilter As clsPersonsFilter)
As DataSet



Dim objAdapter As New SqlClient.SqlDataAdapter

Dim objMyData As New DataSet

Dim objCommand As SqlClient.SqlCommand



Try

'======================================================

'always make sure the connection is open regardless of

'who owns the connection object

'======================================================

If Me._objConnect.State = ConnectionState.Closed Then

Me._objConnect.Open()

End If

objCommand = Me._objDataCMDs.GetPersonCMD(PersonFilter)

objCommand.Connection = Me._objConnect

objAdapter.SelectCommand = objCommand

objAdapter.Fill(objMyData)

Return objMyData



Catch ex As Exception

Throw ex

Finally

'=====================================================

'Make sure all local objects are properly disposed of

'=====================================================

objAdapter.Dispose()

objAdapter = Nothing

objCommand.Dispose()

objCommand = Nothing

objMyData.Dispose()

objMyData = Nothing

End Try



End Function





The function above is called by a middle tier function below. The
clsPersons class contains the function being called.



Public Overloads Function GetPersons(ByVal PersonFilter As clsPersonsFilter)
As DataSet



Try

PersonsData = New clsPersons

Return PersonsData.GetPersons(PersonFilter, Me._Connect)



Catch ex As Exception

Throw ex

Finally

PersonsData = Nothing

End Try



End Function





The above function is in turn called by any other middle tier class or
presentation layer class to get a dataset filled with rows. In your case,
to just populate a combo, use DataTable objects in place of DataSet objects.
And yes, the code above is part of a system that uses net remoting over a
tcp connection.





Ibrahim Malluf
 
OK guys,

I have two replies, both of which I really appreciate.

Wayne, there is no databind since I am using this for win32, not web.
Ibrahim, I am following almost exactly whayt you are doing, but can't seem
to get the combo to populate. Not sure why yet...Code is all below

***Presentation***
Private Sub FillStudents()

Dim objSupport As EGI.Support
Dim ds As New DataSet()

ComboBox1.ValueMember = "StudentID"
ComboBox1.DisplayMember = "FullName"
objSupport = New EGI.Support(True, "", gs_SQLConnString)
ds = objSupport.FillCtlCbo("STU_Students", "FullName", "StudentID",
ComboBox1)
ComboBox1.DataSource = ds
objSupport = Nothing

End Sub

***Support***

Public Function FillCtlCbo(ByVal sTblName As String, ByVal sFldName As
String, ByVal sIDName As String, ByVal sCboName As ComboBox, Optional ByVal
sUserID As String = "") As DataSet
Dim objCtl As DataHandler
Dim strSQl As String
Dim ds As New DataSet()

objCtl = New DataHandler(cblnIsSQL, cstrAccessDB, cstrSQLConnectionString)

strSQl = ""
If sTblName = "STU_Students" Then
strSQl = "SELECT [" & sIDName & "], [" & sFldName & "], MiddleName FROM
" & sTblName
strSQl = strSQl & " ORDER BY [" & sFldName & "]"
Else
strSQl = "SELECT [" & sIDName & "], [" & sFldName & "] FROM " & sTblName
strSQl = strSQl & " ORDER BY [" & sFldName & "]"
End If

ds = objCtl.RecordGetDS(strSQl)
If Not objCtl Is Nothing Then
objCtl.Dispose()
objCtl = Nothing
End If

If Not rstCtl Is Nothing Then rstCtl = Nothing
Return ds

End Function

***Data***

Public Function RecordGetDS(ByVal sSQL As String) As DataSet
Dim objConn As OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet()

'**** Connect to Database

objConn = New OleDb.OleDbConnection()
If Not cblnIsSQL Then
objConn.ConnectionString = GetConnectionString(cstrAccessDB)
objConn.Open()
Else
objConn.ConnectionString = cstrSQLConnectionString
sSQL = Replace(sSQL, "= True", "= 1", 1)
sSQL = Replace(sSQL, "=True", "=1", 1)
sSQL = Replace(sSQL, "= False", "= 0", 1)
sSQL = Replace(sSQL, "=False", "=0", 1)
sSQL = Replace(sSQL, "=#", "='", 1)
sSQL = Replace(sSQL, "<#", "<'", 1)
sSQL = Replace(sSQL, ">#", ">'", 1)
sSQL = Replace(sSQL, "= #", "= '", 1)
sSQL = Replace(sSQL, "< #", "< '", 1)
sSQL = Replace(sSQL, "> #", "> '", 1)
sSQL = Replace(sSQL, ",#", ",'", 1)
sSQL = Replace(sSQL, ", #", ", '", 1)
sSQL = Replace(sSQL, "#,", "',", 1)
sSQL = Replace(sSQL, "# ", "' ", 1)

If Right(sSQL, 1) = "#" Then
sSQL = Replace(sSQL, "#", "'")
End If
End If



da = New OleDb.OleDbDataAdapter(sSQL, objConn.ConnectionString)

da.Fill(ds)

If Not objConn Is Nothing Then
objConn.Close()
objConn.Dispose()
objConn = Nothing
End If

If Not da Is Nothing Then
da.Dispose()
da = Nothing
End If

Return ds

End Function
 
In my code that I use to populate Comboboxes I have the following lines :

ComboBox1.BeginUpdate()
ComboBox1.DataSource = ....
ComboBox1.ValueMember = ...
ComboBox1.DisplayMember = ...
ComboBox1.EndUpdate()

Are you not able to see the contents of your dataset when it comes out of FillCtlCbo?

Wayne


OK guys,

I have two replies, both of which I really appreciate.

Wayne, there is no databind since I am using this for win32, not web.
Ibrahim, I am following almost exactly whayt you are doing, but can't seem
to get the combo to populate. Not sure why yet...Code is all below

***Presentation***
Private Sub FillStudents()

Dim objSupport As EGI.Support
Dim ds As New DataSet()

ComboBox1.ValueMember = "StudentID"
ComboBox1.DisplayMember = "FullName"
objSupport = New EGI.Support(True, "", gs_SQLConnString)
ds = objSupport.FillCtlCbo("STU_Students", "FullName", "StudentID",
ComboBox1)
ComboBox1.DataSource = ds
objSupport = Nothing

End Sub

***Support***

Public Function FillCtlCbo(ByVal sTblName As String, ByVal sFldName As
String, ByVal sIDName As String, ByVal sCboName As ComboBox, Optional ByVal
sUserID As String = "") As DataSet
Dim objCtl As DataHandler
Dim strSQl As String
Dim ds As New DataSet()

objCtl = New DataHandler(cblnIsSQL, cstrAccessDB, cstrSQLConnectionString)

strSQl = ""
If sTblName = "STU_Students" Then
strSQl = "SELECT [" & sIDName & "], [" & sFldName & "], MiddleName FROM
" & sTblName
strSQl = strSQl & " ORDER BY [" & sFldName & "]"
Else
strSQl = "SELECT [" & sIDName & "], [" & sFldName & "] FROM " & sTblName
strSQl = strSQl & " ORDER BY [" & sFldName & "]"
End If

ds = objCtl.RecordGetDS(strSQl)
If Not objCtl Is Nothing Then
objCtl.Dispose()
objCtl = Nothing
End If

If Not rstCtl Is Nothing Then rstCtl = Nothing
Return ds

End Function

***Data***

Public Function RecordGetDS(ByVal sSQL As String) As DataSet
Dim objConn As OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet()

'**** Connect to Database

objConn = New OleDb.OleDbConnection()
If Not cblnIsSQL Then
objConn.ConnectionString = GetConnectionString(cstrAccessDB)
objConn.Open()
Else
objConn.ConnectionString = cstrSQLConnectionString
sSQL = Replace(sSQL, "= True", "= 1", 1)
sSQL = Replace(sSQL, "=True", "=1", 1)
sSQL = Replace(sSQL, "= False", "= 0", 1)
sSQL = Replace(sSQL, "=False", "=0", 1)
sSQL = Replace(sSQL, "=#", "='", 1)
sSQL = Replace(sSQL, "<#", "<'", 1)
sSQL = Replace(sSQL, ">#", ">'", 1)
sSQL = Replace(sSQL, "= #", "= '", 1)
sSQL = Replace(sSQL, "< #", "< '", 1)
sSQL = Replace(sSQL, "> #", "> '", 1)
sSQL = Replace(sSQL, ",#", ",'", 1)
sSQL = Replace(sSQL, ", #", ", '", 1)
sSQL = Replace(sSQL, "#,", "',", 1)
sSQL = Replace(sSQL, "# ", "' ", 1)

If Right(sSQL, 1) = "#" Then
sSQL = Replace(sSQL, "#", "'")
End If
End If



da = New OleDb.OleDbDataAdapter(sSQL, objConn.ConnectionString)

da.Fill(ds)

If Not objConn Is Nothing Then
objConn.Close()
objConn.Dispose()
objConn = Nothing
End If

If Not da Is Nothing Then
da.Dispose()
da = Nothing
End If

Return ds

End Function
 
Wayne,

I found the issue. I was trying to assign a dataset directly instead of
passing a dataset to a dataview and assigning the dataview to the
datasource. It took me a while to figure it out, but once I found the way to
output the dataset to an xml file to see if it was being passed in each
place, I got it running.

Now if I can just figure out how to disconnect the connection to the
database between calls, I'll be REALLY happy.

Thanks for your help!

Take care,
Bruce

In my code that I use to populate Comboboxes I have the following lines :

ComboBox1.BeginUpdate()
ComboBox1.DataSource = ....
ComboBox1.ValueMember = ...
ComboBox1.DisplayMember = ...
ComboBox1.EndUpdate()

Are you not able to see the contents of your dataset when it comes out of
FillCtlCbo?

Wayne


OK guys,

I have two replies, both of which I really appreciate.

Wayne, there is no databind since I am using this for win32, not web.
Ibrahim, I am following almost exactly whayt you are doing, but can't seem
to get the combo to populate. Not sure why yet...Code is all below

***Presentation***
Private Sub FillStudents()

Dim objSupport As EGI.Support
Dim ds As New DataSet()

ComboBox1.ValueMember = "StudentID"
ComboBox1.DisplayMember = "FullName"
objSupport = New EGI.Support(True, "", gs_SQLConnString)
ds = objSupport.FillCtlCbo("STU_Students", "FullName", "StudentID",
ComboBox1)
ComboBox1.DataSource = ds
objSupport = Nothing

End Sub

***Support***

Public Function FillCtlCbo(ByVal sTblName As String, ByVal sFldName As
String, ByVal sIDName As String, ByVal sCboName As ComboBox, Optional ByVal
sUserID As String = "") As DataSet
Dim objCtl As DataHandler
Dim strSQl As String
Dim ds As New DataSet()

objCtl = New DataHandler(cblnIsSQL, cstrAccessDB, cstrSQLConnectionString)

strSQl = ""
If sTblName = "STU_Students" Then
strSQl = "SELECT [" & sIDName & "], [" & sFldName & "], MiddleName FROM
" & sTblName
strSQl = strSQl & " ORDER BY [" & sFldName & "]"
Else
strSQl = "SELECT [" & sIDName & "], [" & sFldName & "] FROM " & sTblName
strSQl = strSQl & " ORDER BY [" & sFldName & "]"
End If

ds = objCtl.RecordGetDS(strSQl)
If Not objCtl Is Nothing Then
objCtl.Dispose()
objCtl = Nothing
End If

If Not rstCtl Is Nothing Then rstCtl = Nothing
Return ds

End Function

***Data***

Public Function RecordGetDS(ByVal sSQL As String) As DataSet
Dim objConn As OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet()

'**** Connect to Database

objConn = New OleDb.OleDbConnection()
If Not cblnIsSQL Then
objConn.ConnectionString = GetConnectionString(cstrAccessDB)
objConn.Open()
Else
objConn.ConnectionString = cstrSQLConnectionString
sSQL = Replace(sSQL, "= True", "= 1", 1)
sSQL = Replace(sSQL, "=True", "=1", 1)
sSQL = Replace(sSQL, "= False", "= 0", 1)
sSQL = Replace(sSQL, "=False", "=0", 1)
sSQL = Replace(sSQL, "=#", "='", 1)
sSQL = Replace(sSQL, "<#", "<'", 1)
sSQL = Replace(sSQL, ">#", ">'", 1)
sSQL = Replace(sSQL, "= #", "= '", 1)
sSQL = Replace(sSQL, "< #", "< '", 1)
sSQL = Replace(sSQL, "> #", "> '", 1)
sSQL = Replace(sSQL, ",#", ",'", 1)
sSQL = Replace(sSQL, ", #", ", '", 1)
sSQL = Replace(sSQL, "#,", "',", 1)
sSQL = Replace(sSQL, "# ", "' ", 1)

If Right(sSQL, 1) = "#" Then
sSQL = Replace(sSQL, "#", "'")
End If
End If



da = New OleDb.OleDbDataAdapter(sSQL, objConn.ConnectionString)

da.Fill(ds)

If Not objConn Is Nothing Then
objConn.Close()
objConn.Dispose()
objConn = Nothing
End If

If Not da Is Nothing Then
da.Dispose()
da = Nothing
End If

Return ds

End Function
 
Glad you got it running...

I think if you return a datatable from the FillCtlCbo function then you could disconnect easily.. something like this...

function FillCtlCbo (......., byref tblResult as datatable) as boolean

' ....... some stuff here

try
'..... do your SQL stuff here

tblResult = yourDataSet.Tables(0).Copy()

FillCtlCbo = True
catch

end try

yourDataSet.Dispose()
yourDataSet = nothing
end function



Wayne
Wayne,

I found the issue. I was trying to assign a dataset directly instead of
passing a dataset to a dataview and assigning the dataview to the
datasource. It took me a while to figure it out, but once I found the way to
output the dataset to an xml file to see if it was being passed in each
place, I got it running.

Now if I can just figure out how to disconnect the connection to the
database between calls, I'll be REALLY happy.

Thanks for your help!

Take care,
Bruce

In my code that I use to populate Comboboxes I have the following lines :

ComboBox1.BeginUpdate()
ComboBox1.DataSource = ....
ComboBox1.ValueMember = ...
ComboBox1.DisplayMember = ...
ComboBox1.EndUpdate()

Are you not able to see the contents of your dataset when it comes out of
FillCtlCbo?

Wayne


OK guys,

I have two replies, both of which I really appreciate.

Wayne, there is no databind since I am using this for win32, not web.
Ibrahim, I am following almost exactly whayt you are doing, but can't seem
to get the combo to populate. Not sure why yet...Code is all below

***Presentation***
Private Sub FillStudents()

Dim objSupport As EGI.Support
Dim ds As New DataSet()

ComboBox1.ValueMember = "StudentID"
ComboBox1.DisplayMember = "FullName"
objSupport = New EGI.Support(True, "", gs_SQLConnString)
ds = objSupport.FillCtlCbo("STU_Students", "FullName", "StudentID",
ComboBox1)
ComboBox1.DataSource = ds
objSupport = Nothing

End Sub

***Support***

Public Function FillCtlCbo(ByVal sTblName As String, ByVal sFldName As
String, ByVal sIDName As String, ByVal sCboName As ComboBox, Optional ByVal
sUserID As String = "") As DataSet
Dim objCtl As DataHandler
Dim strSQl As String
Dim ds As New DataSet()

objCtl = New DataHandler(cblnIsSQL, cstrAccessDB, cstrSQLConnectionString)

strSQl = ""
If sTblName = "STU_Students" Then
strSQl = "SELECT [" & sIDName & "], [" & sFldName & "], MiddleName FROM
" & sTblName
strSQl = strSQl & " ORDER BY [" & sFldName & "]"
Else
strSQl = "SELECT [" & sIDName & "], [" & sFldName & "] FROM " & sTblName
strSQl = strSQl & " ORDER BY [" & sFldName & "]"
End If

ds = objCtl.RecordGetDS(strSQl)
If Not objCtl Is Nothing Then
objCtl.Dispose()
objCtl = Nothing
End If

If Not rstCtl Is Nothing Then rstCtl = Nothing
Return ds

End Function

***Data***

Public Function RecordGetDS(ByVal sSQL As String) As DataSet
Dim objConn As OleDb.OleDbConnection
Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet()

'**** Connect to Database

objConn = New OleDb.OleDbConnection()
If Not cblnIsSQL Then
objConn.ConnectionString = GetConnectionString(cstrAccessDB)
objConn.Open()
Else
objConn.ConnectionString = cstrSQLConnectionString
sSQL = Replace(sSQL, "= True", "= 1", 1)
sSQL = Replace(sSQL, "=True", "=1", 1)
sSQL = Replace(sSQL, "= False", "= 0", 1)
sSQL = Replace(sSQL, "=False", "=0", 1)
sSQL = Replace(sSQL, "=#", "='", 1)
sSQL = Replace(sSQL, "<#", "<'", 1)
sSQL = Replace(sSQL, ">#", ">'", 1)
sSQL = Replace(sSQL, "= #", "= '", 1)
sSQL = Replace(sSQL, "< #", "< '", 1)
sSQL = Replace(sSQL, "> #", "> '", 1)
sSQL = Replace(sSQL, ",#", ",'", 1)
sSQL = Replace(sSQL, ", #", ", '", 1)
sSQL = Replace(sSQL, "#,", "',", 1)
sSQL = Replace(sSQL, "# ", "' ", 1)

If Right(sSQL, 1) = "#" Then
sSQL = Replace(sSQL, "#", "'")
End If
End If



da = New OleDb.OleDbDataAdapter(sSQL, objConn.ConnectionString)

da.Fill(ds)

If Not objConn Is Nothing Then
objConn.Close()
objConn.Dispose()
objConn = Nothing
End If

If Not da Is Nothing Then
da.Dispose()
da = Nothing
End If

Return ds

End Function
 
Back
Top