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

CType and annoying build errors

P: n/a
I am using the following code instead of a very lengthly select case
statement.

(I have a lot of lookup tables in a settings form that are selected
from a ListBox. The data adapters are given a similar name to the
table. Rather than making a long Select Case that could become
obsolete if lookup tables are added and the source table of the
ListBox is edited I came up with this code.)

This code works but of course it gives me build errors.

Error:[Value of type 'String' cannot be converted to
'System.Data.SqlClient.SqlDataAdapter'.]

=== code snippit ===
Private Sub lstMasterLists_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles lstMasterLists.Click

'To populate the dgMasterLists with the proper table
'1. use the returned TableName to make the DataAdapter name
'2. Convert the string to the DataAdampter type

'Get the TableName from the selected item in the list box
Dim strTbl As String
strTbl = Me.lstMasterLists.SelectedItem.ToString

'Make the DataAdapter name from the table name
Dim strDa As String
strDa = strTbl.Remove(0, 5)
strDa = strDa.Insert(0, "Da")

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)

End Sub
======

The code works in debug mode.
The exe works.

Does anyone anticipate me having any problems with this after
deployment?
Does anyone suggest any other alturnatives?
Does anyone suggest a way to stop the annoying build errors?
Jul 21 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
You say that you get an error and you say that the code works? It can't be
both. Your problem is this line:

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)

Because "strDa" is an actual String object and string object can't be
converted to DataAdapter objects. I know that you want to wind up with a
DataAdapter named whatever "strDa" is named, but this command won't do it.

Somewhere else in your code you must be creating an instance of a
DataAdapter. What are you calling that one? Let's see that code.

"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72************************@posting.google.com ...
I am using the following code instead of a very lengthly select case
statement.

(I have a lot of lookup tables in a settings form that are selected
from a ListBox. The data adapters are given a similar name to the
table. Rather than making a long Select Case that could become
obsolete if lookup tables are added and the source table of the
ListBox is edited I came up with this code.)

This code works but of course it gives me build errors.

Error:[Value of type 'String' cannot be converted to
'System.Data.SqlClient.SqlDataAdapter'.]

=== code snippit ===
Private Sub lstMasterLists_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles lstMasterLists.Click

'To populate the dgMasterLists with the proper table
'1. use the returned TableName to make the DataAdapter name
'2. Convert the string to the DataAdampter type

'Get the TableName from the selected item in the list box
Dim strTbl As String
strTbl = Me.lstMasterLists.SelectedItem.ToString

'Make the DataAdapter name from the table name
Dim strDa As String
strDa = strTbl.Remove(0, 5)
strDa = strDa.Insert(0, "Da")

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)

End Sub
======

The code works in debug mode.
The exe works.

Does anyone anticipate me having any problems with this after
deployment?
Does anyone suggest any other alturnatives?
Does anyone suggest a way to stop the annoying build errors?

Jul 21 '05 #2

P: n/a
Scott,

I'm sorry. I didn't make it clear enough. I'll explain.

The code works because the DataAdapters already exists! I am just
refering to its name by the text.

The build engine informs me of build errors at the CType() statement
because all it sees is that I am trying to turn text into a
DataAdapter. The build engine doesn't compare that text to the name of
the existing DataAdapter.

This is what I did. (later... why I did it)

I have these tables...

lst01PrimaryOptions
lst02SecondaryOptions
lst03BusinsessSettings
lst04FixedOptions
....

When I made my DataAdapters I gave them these names...

DaPrimaryOptions
DaSecondaryOptions
DaBusinsessSettings
DaFixedOptions
....

So if I take the table name and replace the first 5 letters with the
letters "Da" I get the DataAdapter name.

Why would I go thorugh this crazy string manipulation to arrive at an
existing DataAdapter name?

Because it saves me a hell of a lot of code!
To replace 70 lines of code with only 5.

Example:
=== Start of lengthy code (70 lines) ===

Select Case sTableName
Case "lkp01RefSource"
da01RefSource.Fill(DsSelectionList1, "lkp01RefSource")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp01RefSource")
Case "lkp02GrpCategory"
da02GrpCategory.Fill(DsSelectionList1, "lkp02GrpCategory")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp02GrpCategory")
Case "lkp03PrgmObjective"
da03PrgmObjective.Fill(DsSelectionList1, "lkp03PrgmObjective")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp03PrgmObjective")
Case "lkp06JobTitle"
da06JobTitle.Fill(DsSelectionList1, "lkp06JobTitle")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp06JobTitle")
Case "lkp07Qualification"
da07Qualification.Fill(DsSelectionList1, "lkp07Qualification")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp07Qualification")
Case "lkp08DayOfWeek"
da08DayOfWeek.Fill(DsSelectionList1, "lkp08DayOfWeek")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp08DayOfWeek")
Case "lkp09MealType"
da09MealType.Fill(DsSelectionList1, "lkp09MealType")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp09MealType")
Case "lkp10MerchandType"
da10MerchandType.Fill(DsSelectionList1, "lkp10MerchandType")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp10MerchandType")
Case "lkp11CommResource"
da11CommResource.Fill(DsSelectionList1, "lkp11CommResource")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp11CommResource")
Case "lkp12TelephonyDevice"
da12TelephonyDevice.Fill(DsSelectionList1, "lkp12TelephonyDevice")
DgMasterLists.SetDataBinding(DsSelectionList1,
"lkp12TelephonyDevice")
Case "lkp13WwwType"
da13WwwType.Fill(DsSelectionList1, "lkp13WwwType")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp13WwwType")
Case "lkp14ModeOfContact"
da14ModeOfContact.Fill(DsSelectionList1, "lkp14ModeOfContact")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp14ModeOfContact")
Case "lkp15MsgTopic"
da15MsgTopic.Fill(DsSelectionList1, "lkp15MsgTopic")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp15MsgTopic")
Case "lkp16ScheduleType"
da16ScheduleType.Fill(DsSelectionList1, "lkp16ScheduleType")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp16ScheduleType")
Case "lkp17WeightGroup"
da17WeightGroup.Fill(DsSelectionList1, "lkp17WeightGroup")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp17WeightGroup")
Case "lkp18ProgramCategory"
da18ProgramCategory.Fill(DsSelectionList1, "lkp18ProgramCategory")
DgMasterLists.SetDataBinding(DsSelectionList1,
"lkp18ProgramCategory")
Case "lkp19Element"
da19Element.Fill(DsSelectionList1, "lkp19Element")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp19Element")
Case "enm1FoodAllergy"
daFoodAllergy.Fill(DsSelectionList1, "enm1FoodAllergy")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm1FoodAllergy")
Case "enm2EnvironAllergy"
daEnvironAllergy.Fill(DsSelectionList1, "enm2EnvironAllergy")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm2EnvironAllergy")
Case "enm3MedicalAllergy"
daMedicalAllergy.Fill(DsSelectionList1, "enm3MedicalAllergy")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm3MedicalAllergy")
Case "enm4MedConcern"
daMedConcern.Fill(DsSelectionList1, "enm4MedConcern")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm4MedConcern")
Case "enm5ActivityRequest"
daActivityRequest.Fill(DsSelectionList1, "enm5ActivityRequest")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm5ActivityRequest")
Case Else
TableErrorMessage()
End Select

=== End of lengthy code ===

=== Start of Short code (5 lines) =====

Dim strDa As String
strDa = strTbl.Remove(0, 5)
strDa = strDa.Insert(0, "Da")

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)

=== End of short code ===

(There are other advantages too)

Back to my original question:
The code works in debug mode.
The exe works.

Does anyone anticipate me having any problems with this after
deployment?
Does anyone suggest any other alturnatives?
Does anyone suggest a way to stop the annoying build errors?

Thank you,
--Doug
"Scott M." <s-***@nospam.nospam> wrote in message news:<u5**************@TK2MSFTNGP12.phx.gbl>...
You say that you get an error and you say that the code works? It can't be
both. Your problem is this line:

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)

Because "strDa" is an actual String object and string object can't be
converted to DataAdapter objects. I know that you want to wind up with a
DataAdapter named whatever "strDa" is named, but this command won't do it.

Somewhere else in your code you must be creating an instance of a
DataAdapter. What are you calling that one? Let's see that code.

"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72************************@posting.google.com ...
I am using the following code instead of a very lengthly select case
statement.

(I have a lot of lookup tables in a settings form that are selected
from a ListBox. The data adapters are given a similar name to the
table. Rather than making a long Select Case that could become
obsolete if lookup tables are added and the source table of the
ListBox is edited I came up with this code.)

This code works but of course it gives me build errors.

Error:[Value of type 'String' cannot be converted to
'System.Data.SqlClient.SqlDataAdapter'.]

=== code snippit ===
Private Sub lstMasterLists_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles lstMasterLists.Click

'To populate the dgMasterLists with the proper table
'1. use the returned TableName to make the DataAdapter name
'2. Convert the string to the DataAdampter type

'Get the TableName from the selected item in the list box
Dim strTbl As String
strTbl = Me.lstMasterLists.SelectedItem.ToString

'Make the DataAdapter name from the table name
Dim strDa As String
strDa = strTbl.Remove(0, 5)
strDa = strDa.Insert(0, "Da")

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)

End Sub
======

The code works in debug mode.
The exe works.

Does anyone anticipate me having any problems with this after
deployment?
Does anyone suggest any other alturnatives?
Does anyone suggest a way to stop the annoying build errors?

Jul 21 '05 #3

P: n/a
Doug,

I understand what you are trying to do and I will ask again that you post
ALL of your code (the short code). In what you have provided, we do not see
the code that creates the DataAdapters. What I'm getting at here is that
this:

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)

even if it didn't throw an exception would not CREATE a DataAdapter for you.
It would only create a type. You must have an already instanced DataAdapter
prior to this line that we can pass somewhere and refer to it as the value
of your string.

"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
Scott,

I'm sorry. I didn't make it clear enough. I'll explain.

The code works because the DataAdapters already exists! I am just
refering to its name by the text.

The build engine informs me of build errors at the CType() statement
because all it sees is that I am trying to turn text into a
DataAdapter. The build engine doesn't compare that text to the name of
the existing DataAdapter.

This is what I did. (later... why I did it)

I have these tables...

lst01PrimaryOptions
lst02SecondaryOptions
lst03BusinsessSettings
lst04FixedOptions
...

When I made my DataAdapters I gave them these names...

DaPrimaryOptions
DaSecondaryOptions
DaBusinsessSettings
DaFixedOptions
...

So if I take the table name and replace the first 5 letters with the
letters "Da" I get the DataAdapter name.

Why would I go thorugh this crazy string manipulation to arrive at an
existing DataAdapter name?

Because it saves me a hell of a lot of code!
To replace 70 lines of code with only 5.

Example:
=== Start of lengthy code (70 lines) ===

Select Case sTableName
Case "lkp01RefSource"
da01RefSource.Fill(DsSelectionList1, "lkp01RefSource")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp01RefSource")
Case "lkp02GrpCategory"
da02GrpCategory.Fill(DsSelectionList1, "lkp02GrpCategory")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp02GrpCategory")
Case "lkp03PrgmObjective"
da03PrgmObjective.Fill(DsSelectionList1, "lkp03PrgmObjective")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp03PrgmObjective")
Case "lkp06JobTitle"
da06JobTitle.Fill(DsSelectionList1, "lkp06JobTitle")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp06JobTitle")
Case "lkp07Qualification"
da07Qualification.Fill(DsSelectionList1, "lkp07Qualification")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp07Qualification")
Case "lkp08DayOfWeek"
da08DayOfWeek.Fill(DsSelectionList1, "lkp08DayOfWeek")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp08DayOfWeek")
Case "lkp09MealType"
da09MealType.Fill(DsSelectionList1, "lkp09MealType")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp09MealType")
Case "lkp10MerchandType"
da10MerchandType.Fill(DsSelectionList1, "lkp10MerchandType")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp10MerchandType")
Case "lkp11CommResource"
da11CommResource.Fill(DsSelectionList1, "lkp11CommResource")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp11CommResource")
Case "lkp12TelephonyDevice"
da12TelephonyDevice.Fill(DsSelectionList1, "lkp12TelephonyDevice")
DgMasterLists.SetDataBinding(DsSelectionList1,
"lkp12TelephonyDevice")
Case "lkp13WwwType"
da13WwwType.Fill(DsSelectionList1, "lkp13WwwType")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp13WwwType")
Case "lkp14ModeOfContact"
da14ModeOfContact.Fill(DsSelectionList1, "lkp14ModeOfContact")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp14ModeOfContact")
Case "lkp15MsgTopic"
da15MsgTopic.Fill(DsSelectionList1, "lkp15MsgTopic")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp15MsgTopic")
Case "lkp16ScheduleType"
da16ScheduleType.Fill(DsSelectionList1, "lkp16ScheduleType")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp16ScheduleType")
Case "lkp17WeightGroup"
da17WeightGroup.Fill(DsSelectionList1, "lkp17WeightGroup")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp17WeightGroup")
Case "lkp18ProgramCategory"
da18ProgramCategory.Fill(DsSelectionList1, "lkp18ProgramCategory")
DgMasterLists.SetDataBinding(DsSelectionList1,
"lkp18ProgramCategory")
Case "lkp19Element"
da19Element.Fill(DsSelectionList1, "lkp19Element")
DgMasterLists.SetDataBinding(DsSelectionList1, "lkp19Element")
Case "enm1FoodAllergy"
daFoodAllergy.Fill(DsSelectionList1, "enm1FoodAllergy")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm1FoodAllergy")
Case "enm2EnvironAllergy"
daEnvironAllergy.Fill(DsSelectionList1, "enm2EnvironAllergy")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm2EnvironAllergy")
Case "enm3MedicalAllergy"
daMedicalAllergy.Fill(DsSelectionList1, "enm3MedicalAllergy")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm3MedicalAllergy")
Case "enm4MedConcern"
daMedConcern.Fill(DsSelectionList1, "enm4MedConcern")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm4MedConcern")
Case "enm5ActivityRequest"
daActivityRequest.Fill(DsSelectionList1, "enm5ActivityRequest")
DgMasterLists.SetDataBinding(DsSelectionList1, "enm5ActivityRequest")
Case Else
TableErrorMessage()
End Select

=== End of lengthy code ===

=== Start of Short code (5 lines) =====

Dim strDa As String
strDa = strTbl.Remove(0, 5)
strDa = strDa.Insert(0, "Da")

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)

=== End of short code ===

(There are other advantages too)

Back to my original question:
The code works in debug mode.
The exe works.

Does anyone anticipate me having any problems with this after
deployment?
Does anyone suggest any other alturnatives?
Does anyone suggest a way to stop the annoying build errors?

Thank you,
--Doug
"Scott M." <s-***@nospam.nospam> wrote in message
news:<u5**************@TK2MSFTNGP12.phx.gbl>...
You say that you get an error and you say that the code works? It can't
be
both. Your problem is this line:

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)

Because "strDa" is an actual String object and string object can't be
converted to DataAdapter objects. I know that you want to wind up with a
DataAdapter named whatever "strDa" is named, but this command won't do
it.

Somewhere else in your code you must be creating an instance of a
DataAdapter. What are you calling that one? Let's see that code.

"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72************************@posting.google.com ...
>I am using the following code instead of a very lengthly select case
> statement.
>
> (I have a lot of lookup tables in a settings form that are selected
> from a ListBox. The data adapters are given a similar name to the
> table. Rather than making a long Select Case that could become
> obsolete if lookup tables are added and the source table of the
> ListBox is edited I came up with this code.)
>
> This code works but of course it gives me build errors.
>
> Error:[Value of type 'String' cannot be converted to
> 'System.Data.SqlClient.SqlDataAdapter'.]
>
> === code snippit ===
> Private Sub lstMasterLists_Click(ByVal sender As Object, _
> ByVal e As System.EventArgs) Handles lstMasterLists.Click
>
> 'To populate the dgMasterLists with the proper table
> '1. use the returned TableName to make the DataAdapter name
> '2. Convert the string to the DataAdampter type
>
> 'Get the TableName from the selected item in the list box
> Dim strTbl As String
> strTbl = Me.lstMasterLists.SelectedItem.ToString
>
> 'Make the DataAdapter name from the table name
> Dim strDa As String
> strDa = strTbl.Remove(0, 5)
> strDa = strDa.Insert(0, "Da")
>
> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
> DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>
> End Sub
> ======
>
> The code works in debug mode.
> The exe works.
>
> Does anyone anticipate me having any problems with this after
> deployment?
> Does anyone suggest any other alturnatives?
> Does anyone suggest a way to stop the annoying build errors?

Jul 21 '05 #4

P: n/a
Scott,

I'm not looking for a critique on the last sample code I sent. I threw
it together for illustration purposes.

Scott > I would STRONGLY recommend that you turn Option Strict ON

[Doug] I always use Option Strict in my code.

Scott > Why are your putting da in quotes here?

[Doug] For illustration purposes. Did you forget what this thread is
about? My use of...
CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")
.... was completely out of context of my original post.
I provided it in isolation for you to illustrate how it worked in the
presence of a actual SqlDataAdapter.

Scott > you seem to have moved a lot of VS.NET generated code around
and put it in different places.

[Doug] I admit I'm not familiar with making code not based on a form.
I was trying to make code that you could run without having a
drag&drop DataAdapter and an xsd file.

Scott > I understand what you are trying to do, but I think that you
are going about this entirely the wrong way.

[Doug] Exactly the point of my post. Remember my question in my
original post: "Does anyone suggest any other alternatives?"

I thought I found a way to replace 70 lines of code with 5 and wanted
to know what to expect from my approach and asked for suggested
alternatives.

Are there any alternatives - (please see my second post in this thread
if you need a refresher on the context.)

Thank you,
--Doug

"Scott M." <s-***@nospam.nospam> wrote in message news:<Of**************@TK2MSFTNGP09.phx.gbl>...
A few things here:

"Imports System.Windows.Forms" is not needed if you are using VS.NET since a
project wide imports statement to this namespace is made automatically for
you.

I would STRONGLY recommend that you trun Option Strict ON (in the Build
section of your project's properties). With OS = ON, if you have build
errors, then your code can't run and that is a good thing. This is why I
asked you to explain how you could be getting build errors and still say
that your code runs.

Now, as for this:

CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")

Why are your putting da in quotes here? This code doesn't pass the Option
Strict check. da is the programmatic name of the object, so you wouldn't
put its name in as a string. This is the exact problem that you are having
when trying to dynamically set the DataAdapter name. You CANT'T take a
string and cast it as a DataAdapter. And, maybe most importantly, why are
you trying to cast da as a DataAdapter when it already is a DataAdapter?

CType("dg", SqlDataAdapter).SetDataBinding(ds, "Employees")

And here, you are again placing the object name (dg) in quotes and then
casting it to a DataAdapter? First dg is a DataGrid, not a DataAdapter.
Second, even if it was a DataAdapter, you wouldn't put its name in quotes,
because then you are trying to cast a string as a DataAdapter.

Douglas, I understand what you are trying to do, but I think that you are
going about this entirely the wrong way. As I look at the code you posted,
I am first struck that you seem to have moved a lot of VS.NET generated code
around and put it in different places. While not illegal to do this, I
can't understand why...it just makes everything much more difficult to
follow. I'm even trying to understand why you are launching your
application the way you are:
Public Shared Sub Main()
System.Windows.Forms.Application.Run(New NortwindEmployee)
End Sub


Again, while not incorrect, wouldn't it easier to just set the form as your
project's start up object?

How many DataGrids and DataSets do you have? In your OP, you talk about
DgMasterLists and DsMasterLists1and in your last post you talk about dg and
ds? If there really are 2, you need to post ALL of your code.

So much of your code can be condensed or eliminated (which would make things
so much easier). If you use the Forms Designer to draw out your button and
datagrid and set their respective properties in the properties window, we
don't even have to look at their code (or move it around). It will all be
nicely contained in the Windows Forms Designer Generated Code and this is
all you have to add to get the button and the grid up and running:

--------------------------------------------------------------------------------
Private conStr As String = "workstation id=SEDNA;packet size=4096;" & _
"integrated security=SSPI;data
source=(local);" & _
"persist security info=False;initial
catalog=Northwind"

Private selSQL As String = "SELECT EmployeeID, LastName, FirstName, Title, "
& _
"TitleOfCourtesy, BirthDate,
HireDate, Address, City, Region, " & _
"PostalCode, Country, HomePhone,
Extension, Photo, Notes, " & _
"ReportsTo, PhotoPath FROM
Employees"

Private con As New SqlConnection(conStr)
Private da As New SqlDataAdapter(selSQL, con)
Private ds As New DataSet
--------------------------------------------------------------------------------
Private Sub btnPush_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnPush.Click
'Fill DataAdapter
da.Fill(ds, "Employees")

'Bind DataGrid
dg.SetDataBinding(ds, "Employees")
End Sub
--------------------------------------------------------------------------------

You don't even need the command object that you had made.

*********Now that we've cleaned up the initial code, let's concentrate on
the point of your original post....
I would not use the Click event of a listbox because this code would run
everytime someone clicks the list (even if they click the same entry as last
time). Instead use the SelectedIndexChanged event handler of a ComboBox so
that you only run this when it's needed (more efficient).

Private Sub lstMasterLists_SelectedIndexChanged _
(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles lstMasterLists.SelectedIndexChanged

'Get the TableName from the selected item in the list box
'and modify it to match an actual table name in the db
Dim strTbl As String = lstMasterLists.Text.Remove(0, 5)

'Each time a user selects a different table, re-populate the grid with
that data
Dim newDA As New SqlClient.SqlDataAdapter(selSQL, con)
Dim newDS As New DataSet
newDA.Fill(newDS, strTbl)
dg.SetDataBinding(newDS, strTbl)
End Sub

BOTTOM LINE: I don't see a need for you to worry about dynamically naming
the DataAdapter at all, no one ever see's this. You only need to have "a"
DataAdapter to use each time someone wants to see different table data.
Instead, the name of the table being added to the dataset is what's more
important and we can use your string to get that name.

The problem (or last remaining issue) I see is that for different tables,
you need a different SQL string. You could prepare these strings at the
module level and using a simple case statement, figure out which is needed:

Dim OrdersSQL As String = "SELECT * FROM Orders"
Dim ProductsSQL As String = "SELECT * FROM Products"
Dim RegionSQL As String = "SELECT * FROM Region"

Private Sub lstMasterLists_SelectedIndexChanged _
(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles lstMasterLists.SelectedIndexChanged

'Get the TableName from the selected item in the list box
'and modify it to match an actual table name in the db
Dim strTbl As String = lstMasterLists.Text.Remove(0, 5)

Select Case strTbl
Case "LastName"
selSQL = OrdersSQL
Case "FirstName"
selSQL = ProductsSQL
Case "Title"
selSQL = RegionSQL
End Select

'Each time a user selects a different table, re-populate the grid with
that data
Dim newDA As New SqlClient.SqlDataAdapter(selSQL, con)
Dim newDS As New DataSet
newDA.Fill(newDS, strTbl)
dg.SetDataBinding(newDS, strTbl)
End Sub
I have tested all the code I have given here and it works.
================================================== ===========
"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
Scott,

Below is code that you you can run that illustrates the use of CType()
to pass a string (with the same text as an existing DataAdapter) to
type SqlDataAdapter so that it will refer to an actual exitsing
DataAdapter of the same name.

(You need SQL Server installed locally with the Northwind database ~
Edit the connection string as needed.)

=== Start of code ===
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class NortwindEmployee
Inherits Form

'Controls
Friend WithEvents btn As New Button
Friend WithEvents dg As New DataGrid

'Connection
Private cn As New SqlConnection

'Command
Private cmSel As New SqlCommand

'DataAdapter
Private da As New SqlDataAdapter

'DataSet
Private ds As New DataSet

Public Shared Sub Main()
System.Windows.Forms.Application.Run(New NortwindEmployee)

End Sub

Public Sub New()

'Control - Button
btn.Location = New System.Drawing.Point(8, 8)
btn.Name = "btn"
btn.TabIndex = 0
btn.Text = "Button1"

'Control - DataGrid
dg.DataMember = ""
dg.HeaderForeColor = System.Drawing.SystemColors.ControlText
dg.Location = New System.Drawing.Point(8, 40)
dg.Name = "dg"
dg.Size = New System.Drawing.Size(280, 160)
dg.TabIndex = 1

'ConnectionString
cn.ConnectionString = "workstation id=SEDNA;packet
size=4096;integrated security=SSPI;data source=(local);persist
security info=False;initial catalog=Northwind"

'DataAdapter Commands
da.SelectCommand = cmSel

'Select Command
cmSel.CommandText = "SELECT EmployeeID, LastName, FirstName,
Title, TitleOfCourtesy, BirthDate, HireDa" & _
"te, Address, City, Region, PostalCode, Country, HomePhone,
Extension, Photo, Not" & _
"es, ReportsTo, PhotoPath FROM Employees"
cmSel.Connection = cn

End Sub

Private Sub InitializeComponent()

'Form1
AutoScaleBaseSize = New System.Drawing.Size(5, 13)
ClientSize = New System.Drawing.Size(292, 206)
Name = "NortwindEmployee"
Text = "NortwindEmployee"

End Sub

Public Sub btn_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)

'Fill DataAdapter
'da.Fill(Me.ds, "Employees")
CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")

'Bind DataGrid
'dg.SetDataBinding(ds, "Employees")
CType("dg", SqlDataAdapter).SetDataBinding(ds, "Employees")

End Sub

End Class

=====================
In effect the code 'CType("da", SqlDataAdapter)' is asking the build
engine to consider the string "da" as a SqlDataAdapter.

As I see it the build engine does either one of these two things;
A.) Either the build engine is telling me that "da" is text and you
need to be aware of what you are doing (puts the error on the task
list) - then goes ahead and assocaites the "da" with SqlDataAdapter.

or

B.) The build engine encounters the "da" and stops but goes on to tell
me why it stops by putting the error on the task list.

c.) If there is some other thing the build engine is doing I can't
think of it.
There are two reasons I think that the build engine does option 'A'.
1.) It works.
2.) There are other situations such as untyped datasets where code is
not recognized until it is "compiled" - (You know how intellisense
does not recognize objects that are not strongly typed?)

So.... I am open for comments on thaking this direction.
"Scott M." <s-***@nospam.nospam> wrote in message
news:<eO*************@TK2MSFTNGP11.phx.gbl>...
Doug,

I understand what you are trying to do and I will ask again that you post
ALL of your code (the short code). In what you have provided, we do not
see
the code that creates the DataAdapters. What I'm getting at here is that
this:

CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)

even if it didn't throw an exception would not CREATE a DataAdapter for
you.
It would only create a type. You must have an already instanced
DataAdapter
prior to this line that we can pass somewhere and refer to it as the
value
of your string.

"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
> Scott,
>
> I'm sorry. I didn't make it clear enough. I'll explain.
>
> The code works because the DataAdapters already exists! I am just
> refering to its name by the text.
>
> The build engine informs me of build errors at the CType() statement
> because all it sees is that I am trying to turn text into a
> DataAdapter. The build engine doesn't compare that text to the name of
> the existing DataAdapter.
>
> This is what I did. (later... why I did it)
>
> I have these tables...
>
> lst01PrimaryOptions
> lst02SecondaryOptions
> lst03BusinsessSettings
> lst04FixedOptions
> ...
>
> When I made my DataAdapters I gave them these names...
>
> DaPrimaryOptions
> DaSecondaryOptions
> DaBusinsessSettings
> DaFixedOptions
> ...
>
> So if I take the table name and replace the first 5 letters with the
> letters "Da" I get the DataAdapter name.
>
> Why would I go thorugh this crazy string manipulation to arrive at an
> existing DataAdapter name?
>
> Because it saves me a hell of a lot of code!
> To replace 70 lines of code with only 5.
>
> Example:
> === Start of lengthy code (70 lines) ===
>
> Select Case sTableName
> Case "lkp01RefSource"
> da01RefSource.Fill(DsSelectionList1, "lkp01RefSource")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp01RefSource")
> Case "lkp02GrpCategory"
> da02GrpCategory.Fill(DsSelectionList1, "lkp02GrpCategory")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp02GrpCategory")
> Case "lkp03PrgmObjective"
> da03PrgmObjective.Fill(DsSelectionList1, "lkp03PrgmObjective")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp03PrgmObjective")
> Case "lkp06JobTitle"
> da06JobTitle.Fill(DsSelectionList1, "lkp06JobTitle")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp06JobTitle")
> Case "lkp07Qualification"
> da07Qualification.Fill(DsSelectionList1, "lkp07Qualification")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp07Qualification")
> Case "lkp08DayOfWeek"
> da08DayOfWeek.Fill(DsSelectionList1, "lkp08DayOfWeek")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp08DayOfWeek")
> Case "lkp09MealType"
> da09MealType.Fill(DsSelectionList1, "lkp09MealType")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp09MealType")
> Case "lkp10MerchandType"
> da10MerchandType.Fill(DsSelectionList1, "lkp10MerchandType")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp10MerchandType")
> Case "lkp11CommResource"
> da11CommResource.Fill(DsSelectionList1, "lkp11CommResource")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp11CommResource")
> Case "lkp12TelephonyDevice"
> da12TelephonyDevice.Fill(DsSelectionList1, "lkp12TelephonyDevice")
> DgMasterLists.SetDataBinding(DsSelectionList1,
> "lkp12TelephonyDevice")
> Case "lkp13WwwType"
> da13WwwType.Fill(DsSelectionList1, "lkp13WwwType")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp13WwwType")
> Case "lkp14ModeOfContact"
> da14ModeOfContact.Fill(DsSelectionList1, "lkp14ModeOfContact")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp14ModeOfContact")
> Case "lkp15MsgTopic"
> da15MsgTopic.Fill(DsSelectionList1, "lkp15MsgTopic")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp15MsgTopic")
> Case "lkp16ScheduleType"
> da16ScheduleType.Fill(DsSelectionList1, "lkp16ScheduleType")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp16ScheduleType")
> Case "lkp17WeightGroup"
> da17WeightGroup.Fill(DsSelectionList1, "lkp17WeightGroup")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp17WeightGroup")
> Case "lkp18ProgramCategory"
> da18ProgramCategory.Fill(DsSelectionList1, "lkp18ProgramCategory")
> DgMasterLists.SetDataBinding(DsSelectionList1,
> "lkp18ProgramCategory")
> Case "lkp19Element"
> da19Element.Fill(DsSelectionList1, "lkp19Element")
> DgMasterLists.SetDataBinding(DsSelectionList1, "lkp19Element")
> Case "enm1FoodAllergy"
> daFoodAllergy.Fill(DsSelectionList1, "enm1FoodAllergy")
> DgMasterLists.SetDataBinding(DsSelectionList1, "enm1FoodAllergy")
> Case "enm2EnvironAllergy"
> daEnvironAllergy.Fill(DsSelectionList1, "enm2EnvironAllergy")
> DgMasterLists.SetDataBinding(DsSelectionList1, "enm2EnvironAllergy")
> Case "enm3MedicalAllergy"
> daMedicalAllergy.Fill(DsSelectionList1, "enm3MedicalAllergy")
> DgMasterLists.SetDataBinding(DsSelectionList1, "enm3MedicalAllergy")
> Case "enm4MedConcern"
> daMedConcern.Fill(DsSelectionList1, "enm4MedConcern")
> DgMasterLists.SetDataBinding(DsSelectionList1, "enm4MedConcern")
> Case "enm5ActivityRequest"
> daActivityRequest.Fill(DsSelectionList1, "enm5ActivityRequest")
> DgMasterLists.SetDataBinding(DsSelectionList1, "enm5ActivityRequest")
> Case Else
> TableErrorMessage()
> End Select
>
> === End of lengthy code ===
>
> === Start of Short code (5 lines) =====
>
> Dim strDa As String
> strDa = strTbl.Remove(0, 5)
> strDa = strDa.Insert(0, "Da")
>
> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
> DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>
> === End of short code ===
>
> (There are other advantages too)
>
> Back to my original question:
> The code works in debug mode.
> The exe works.
>
> Does anyone anticipate me having any problems with this after
> deployment?
> Does anyone suggest any other alturnatives?
> Does anyone suggest a way to stop the annoying build errors?
>
> Thank you,
> --Doug
>
>
> "Scott M." <s-***@nospam.nospam> wrote in message
> news:<u5**************@TK2MSFTNGP12.phx.gbl>...
>> You say that you get an error and you say that the code works? It
>> can't
>> be
>> both. Your problem is this line:
>>
>> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>>
>> Because "strDa" is an actual String object and string object can't be
>> converted to DataAdapter objects. I know that you want to wind up
>> with a
>> DataAdapter named whatever "strDa" is named, but this command won't do
>> it.
>>
>> Somewhere else in your code you must be creating an instance of a
>> DataAdapter. What are you calling that one? Let's see that code.
>>
>>
>>
>> "Douglas Buchanan" <db*********@hotmail.com> wrote in message
>> news:72************************@posting.google.com ...
>> >I am using the following code instead of a very lengthly select case
>> > statement.
>> >
>> > (I have a lot of lookup tables in a settings form that are selected
>> > from a ListBox. The data adapters are given a similar name to the
>> > table. Rather than making a long Select Case that could become
>> > obsolete if lookup tables are added and the source table of the
>> > ListBox is edited I came up with this code.)
>> >
>> > This code works but of course it gives me build errors.
>> >
>> > Error:[Value of type 'String' cannot be converted to
>> > 'System.Data.SqlClient.SqlDataAdapter'.]
>> >
>> > === code snippit ===
>> > Private Sub lstMasterLists_Click(ByVal sender As Object, _
>> > ByVal e As System.EventArgs) Handles lstMasterLists.Click
>> >
>> > 'To populate the dgMasterLists with the proper table
>> > '1. use the returned TableName to make the DataAdapter name
>> > '2. Convert the string to the DataAdampter type
>> >
>> > 'Get the TableName from the selected item in the list box
>> > Dim strTbl As String
>> > strTbl = Me.lstMasterLists.SelectedItem.ToString
>> >
>> > 'Make the DataAdapter name from the table name
>> > Dim strDa As String
>> > strDa = strTbl.Remove(0, 5)
>> > strDa = strDa.Insert(0, "Da")
>> >
>> > CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> > DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>> >
>> > End Sub
>> > ======
>> >
>> > The code works in debug mode.
>> > The exe works.
>> >
>> > Does anyone anticipate me having any problems with this after
>> > deployment?
>> > Does anyone suggest any other alturnatives?
>> > Does anyone suggest a way to stop the annoying build errors?

Jul 21 '05 #5

P: n/a
Please don't take it as a critique, but I had difficulty in understanding
where your problem was because of all the unnecessary code changes. I
mentioned Option Strict because you kept saying how you had build errors,
but the code worked. With Option Strict On, you can't even compile your
code when it has errors, so I still am confused as to how you can have
errors but your code works.

I tried to give you the benefit of my experience with .NET because I could
tell from the code that you wrote that you might gain from it. It wasn't
meant as an insult. I don't think I presented anything in a rude or
condescending way. I was simply trying to help you reach your goal of
slimming down your code.

I asked you to send me the code that you said was working. Now, you are
saying that the code you sent was for illustration purposes and you are
berating me for not knowing this? I've noticed that below you didn't say
anything about the other "critiques" I gave you to reduce your code and make
it cleaner like getting rid of the explicit command object or declaring your
string and chopping its first 5 chars off at the same time.

The point is that no one knows your application better than you do and for
someone else to help, you sometimes have to look at the whole thing, not
just the one line that is failing. I did that and, as I did, I found that I
would have written this application differently than you did. This is what
you asked for..."another alternative".

I also spent a good 2 hours of my time trying to help you solve your
problem. I ultimately provided you with code that works and meets your
criteria of being as lean as possible. I'm curious why you didn't mention
any of that in your reply. Did you try it? Does it do what you were
looking for? Did you understand my approach? Wasn't it that "other
alternative" you had asked for?

I basically wrote this piece of the application for you and tested it. And
you have no comments on that? Wow...you're welcome.
"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
Scott,

I'm not looking for a critique on the last sample code I sent. I threw
it together for illustration purposes.

Scott > I would STRONGLY recommend that you turn Option Strict ON

[Doug] I always use Option Strict in my code.

Scott > Why are your putting da in quotes here?

[Doug] For illustration purposes. Did you forget what this thread is
about? My use of...
CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")
... was completely out of context of my original post.
I provided it in isolation for you to illustrate how it worked in the
presence of a actual SqlDataAdapter.

Scott > you seem to have moved a lot of VS.NET generated code around
and put it in different places.

[Doug] I admit I'm not familiar with making code not based on a form.
I was trying to make code that you could run without having a
drag&drop DataAdapter and an xsd file.

Scott > I understand what you are trying to do, but I think that you
are going about this entirely the wrong way.

[Doug] Exactly the point of my post. Remember my question in my
original post: "Does anyone suggest any other alternatives?"

I thought I found a way to replace 70 lines of code with 5 and wanted
to know what to expect from my approach and asked for suggested
alternatives.

Are there any alternatives - (please see my second post in this thread
if you need a refresher on the context.)

Thank you,
--Doug

"Scott M." <s-***@nospam.nospam> wrote in message
news:<Of**************@TK2MSFTNGP09.phx.gbl>...
A few things here:

"Imports System.Windows.Forms" is not needed if you are using VS.NET
since a
project wide imports statement to this namespace is made automatically
for
you.

I would STRONGLY recommend that you trun Option Strict ON (in the Build
section of your project's properties). With OS = ON, if you have build
errors, then your code can't run and that is a good thing. This is why I
asked you to explain how you could be getting build errors and still say
that your code runs.

Now, as for this:

CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")

Why are your putting da in quotes here? This code doesn't pass the
Option
Strict check. da is the programmatic name of the object, so you wouldn't
put its name in as a string. This is the exact problem that you are
having
when trying to dynamically set the DataAdapter name. You CANT'T take a
string and cast it as a DataAdapter. And, maybe most importantly, why
are
you trying to cast da as a DataAdapter when it already is a DataAdapter?

CType("dg", SqlDataAdapter).SetDataBinding(ds, "Employees")

And here, you are again placing the object name (dg) in quotes and then
casting it to a DataAdapter? First dg is a DataGrid, not a DataAdapter.
Second, even if it was a DataAdapter, you wouldn't put its name in
quotes,
because then you are trying to cast a string as a DataAdapter.

Douglas, I understand what you are trying to do, but I think that you are
going about this entirely the wrong way. As I look at the code you
posted,
I am first struck that you seem to have moved a lot of VS.NET generated
code
around and put it in different places. While not illegal to do this, I
can't understand why...it just makes everything much more difficult to
follow. I'm even trying to understand why you are launching your
application the way you are:
> Public Shared Sub Main()
> System.Windows.Forms.Application.Run(New NortwindEmployee)
> End Sub


Again, while not incorrect, wouldn't it easier to just set the form as
your
project's start up object?

How many DataGrids and DataSets do you have? In your OP, you talk about
DgMasterLists and DsMasterLists1and in your last post you talk about dg
and
ds? If there really are 2, you need to post ALL of your code.

So much of your code can be condensed or eliminated (which would make
things
so much easier). If you use the Forms Designer to draw out your button
and
datagrid and set their respective properties in the properties window, we
don't even have to look at their code (or move it around). It will all
be
nicely contained in the Windows Forms Designer Generated Code and this is
all you have to add to get the button and the grid up and running:

--------------------------------------------------------------------------------
Private conStr As String = "workstation id=SEDNA;packet size=4096;" & _
"integrated security=SSPI;data
source=(local);" & _
"persist security
info=False;initial
catalog=Northwind"

Private selSQL As String = "SELECT EmployeeID, LastName, FirstName,
Title, "
& _
"TitleOfCourtesy, BirthDate,
HireDate, Address, City, Region, " & _
"PostalCode, Country,
HomePhone,
Extension, Photo, Notes, " & _
"ReportsTo, PhotoPath FROM
Employees"

Private con As New SqlConnection(conStr)
Private da As New SqlDataAdapter(selSQL, con)
Private ds As New DataSet
--------------------------------------------------------------------------------
Private Sub btnPush_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnPush.Click
'Fill DataAdapter
da.Fill(ds, "Employees")

'Bind DataGrid
dg.SetDataBinding(ds, "Employees")
End Sub
--------------------------------------------------------------------------------

You don't even need the command object that you had made.

*********Now that we've cleaned up the initial code, let's concentrate on
the point of your original post....
I would not use the Click event of a listbox because this code would run
everytime someone clicks the list (even if they click the same entry as
last
time). Instead use the SelectedIndexChanged event handler of a ComboBox
so
that you only run this when it's needed (more efficient).

Private Sub lstMasterLists_SelectedIndexChanged _
(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles lstMasterLists.SelectedIndexChanged

'Get the TableName from the selected item in the list box
'and modify it to match an actual table name in the db
Dim strTbl As String = lstMasterLists.Text.Remove(0, 5)

'Each time a user selects a different table, re-populate the grid
with
that data
Dim newDA As New SqlClient.SqlDataAdapter(selSQL, con)
Dim newDS As New DataSet
newDA.Fill(newDS, strTbl)
dg.SetDataBinding(newDS, strTbl)
End Sub

BOTTOM LINE: I don't see a need for you to worry about dynamically
naming
the DataAdapter at all, no one ever see's this. You only need to have
"a"
DataAdapter to use each time someone wants to see different table data.
Instead, the name of the table being added to the dataset is what's more
important and we can use your string to get that name.

The problem (or last remaining issue) I see is that for different tables,
you need a different SQL string. You could prepare these strings at the
module level and using a simple case statement, figure out which is
needed:

Dim OrdersSQL As String = "SELECT * FROM Orders"
Dim ProductsSQL As String = "SELECT * FROM Products"
Dim RegionSQL As String = "SELECT * FROM Region"

Private Sub lstMasterLists_SelectedIndexChanged _
(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles lstMasterLists.SelectedIndexChanged

'Get the TableName from the selected item in the list box
'and modify it to match an actual table name in the db
Dim strTbl As String = lstMasterLists.Text.Remove(0, 5)

Select Case strTbl
Case "LastName"
selSQL = OrdersSQL
Case "FirstName"
selSQL = ProductsSQL
Case "Title"
selSQL = RegionSQL
End Select

'Each time a user selects a different table, re-populate the grid
with
that data
Dim newDA As New SqlClient.SqlDataAdapter(selSQL, con)
Dim newDS As New DataSet
newDA.Fill(newDS, strTbl)
dg.SetDataBinding(newDS, strTbl)
End Sub
I have tested all the code I have given here and it works.
================================================== ===========
"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
> Scott,
>
> Below is code that you you can run that illustrates the use of CType()
> to pass a string (with the same text as an existing DataAdapter) to
> type SqlDataAdapter so that it will refer to an actual exitsing
> DataAdapter of the same name.
>
> (You need SQL Server installed locally with the Northwind database ~
> Edit the connection string as needed.)
>
> === Start of code ===
> Imports System.Data.SqlClient
> Imports System.Windows.Forms
>
> Public Class NortwindEmployee
> Inherits Form
>
> 'Controls
> Friend WithEvents btn As New Button
> Friend WithEvents dg As New DataGrid
>
> 'Connection
> Private cn As New SqlConnection
>
> 'Command
> Private cmSel As New SqlCommand
>
> 'DataAdapter
> Private da As New SqlDataAdapter
>
> 'DataSet
> Private ds As New DataSet
>
> Public Shared Sub Main()
> System.Windows.Forms.Application.Run(New NortwindEmployee)
>
> End Sub
>
> Public Sub New()
>
> 'Control - Button
> btn.Location = New System.Drawing.Point(8, 8)
> btn.Name = "btn"
> btn.TabIndex = 0
> btn.Text = "Button1"
>
> 'Control - DataGrid
> dg.DataMember = ""
> dg.HeaderForeColor = System.Drawing.SystemColors.ControlText
> dg.Location = New System.Drawing.Point(8, 40)
> dg.Name = "dg"
> dg.Size = New System.Drawing.Size(280, 160)
> dg.TabIndex = 1
>
> 'ConnectionString
> cn.ConnectionString = "workstation id=SEDNA;packet
> size=4096;integrated security=SSPI;data source=(local);persist
> security info=False;initial catalog=Northwind"
>
> 'DataAdapter Commands
> da.SelectCommand = cmSel
>
> 'Select Command
> cmSel.CommandText = "SELECT EmployeeID, LastName, FirstName,
> Title, TitleOfCourtesy, BirthDate, HireDa" & _
> "te, Address, City, Region, PostalCode, Country, HomePhone,
> Extension, Photo, Not" & _
> "es, ReportsTo, PhotoPath FROM Employees"
> cmSel.Connection = cn
>
> End Sub
>
> Private Sub InitializeComponent()
>
> 'Form1
> AutoScaleBaseSize = New System.Drawing.Size(5, 13)
> ClientSize = New System.Drawing.Size(292, 206)
> Name = "NortwindEmployee"
> Text = "NortwindEmployee"
>
> End Sub
>
> Public Sub btn_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs)
>
> 'Fill DataAdapter
> 'da.Fill(Me.ds, "Employees")
> CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")
>
> 'Bind DataGrid
> 'dg.SetDataBinding(ds, "Employees")
> CType("dg", SqlDataAdapter).SetDataBinding(ds, "Employees")
>
> End Sub
>
> End Class
>
> =====================
> In effect the code 'CType("da", SqlDataAdapter)' is asking the build
> engine to consider the string "da" as a SqlDataAdapter.
>
> As I see it the build engine does either one of these two things;
> A.) Either the build engine is telling me that "da" is text and you
> need to be aware of what you are doing (puts the error on the task
> list) - then goes ahead and assocaites the "da" with SqlDataAdapter.
>
> or
>
> B.) The build engine encounters the "da" and stops but goes on to tell
> me why it stops by putting the error on the task list.
>
> c.) If there is some other thing the build engine is doing I can't
> think of it.
>
>
> There are two reasons I think that the build engine does option 'A'.
> 1.) It works.
> 2.) There are other situations such as untyped datasets where code is
> not recognized until it is "compiled" - (You know how intellisense
> does not recognize objects that are not strongly typed?)
>
> So.... I am open for comments on thaking this direction.
>
>
> "Scott M." <s-***@nospam.nospam> wrote in message
> news:<eO*************@TK2MSFTNGP11.phx.gbl>...
>> Doug,
>>
>> I understand what you are trying to do and I will ask again that you
>> post
>> ALL of your code (the short code). In what you have provided, we do
>> not
>> see
>> the code that creates the DataAdapters. What I'm getting at here is
>> that
>> this:
>>
>> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>>
>> even if it didn't throw an exception would not CREATE a DataAdapter
>> for
>> you.
>> It would only create a type. You must have an already instanced
>> DataAdapter
>> prior to this line that we can pass somewhere and refer to it as the
>> value
>> of your string.
>>
>>
>>
>> "Douglas Buchanan" <db*********@hotmail.com> wrote in message
>> news:72**************************@posting.google.c om...
>> > Scott,
>> >
>> > I'm sorry. I didn't make it clear enough. I'll explain.
>> >
>> > The code works because the DataAdapters already exists! I am just
>> > refering to its name by the text.
>> >
>> > The build engine informs me of build errors at the CType() statement
>> > because all it sees is that I am trying to turn text into a
>> > DataAdapter. The build engine doesn't compare that text to the name
>> > of
>> > the existing DataAdapter.
>> >
>> > This is what I did. (later... why I did it)
>> >
>> > I have these tables...
>> >
>> > lst01PrimaryOptions
>> > lst02SecondaryOptions
>> > lst03BusinsessSettings
>> > lst04FixedOptions
>> > ...
>> >
>> > When I made my DataAdapters I gave them these names...
>> >
>> > DaPrimaryOptions
>> > DaSecondaryOptions
>> > DaBusinsessSettings
>> > DaFixedOptions
>> > ...
>> >
>> > So if I take the table name and replace the first 5 letters with the
>> > letters "Da" I get the DataAdapter name.
>> >
>> > Why would I go thorugh this crazy string manipulation to arrive at
>> > an
>> > existing DataAdapter name?
>> >
>> > Because it saves me a hell of a lot of code!
>> > To replace 70 lines of code with only 5.
>> >
>> > Example:
>> > === Start of lengthy code (70 lines) ===
>> >
>> > Select Case sTableName
>> > Case "lkp01RefSource"
>> > da01RefSource.Fill(DsSelectionList1, "lkp01RefSource")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp01RefSource")
>> > Case "lkp02GrpCategory"
>> > da02GrpCategory.Fill(DsSelectionList1, "lkp02GrpCategory")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp02GrpCategory")
>> > Case "lkp03PrgmObjective"
>> > da03PrgmObjective.Fill(DsSelectionList1, "lkp03PrgmObjective")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp03PrgmObjective")
>> > Case "lkp06JobTitle"
>> > da06JobTitle.Fill(DsSelectionList1, "lkp06JobTitle")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp06JobTitle")
>> > Case "lkp07Qualification"
>> > da07Qualification.Fill(DsSelectionList1, "lkp07Qualification")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp07Qualification")
>> > Case "lkp08DayOfWeek"
>> > da08DayOfWeek.Fill(DsSelectionList1, "lkp08DayOfWeek")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp08DayOfWeek")
>> > Case "lkp09MealType"
>> > da09MealType.Fill(DsSelectionList1, "lkp09MealType")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp09MealType")
>> > Case "lkp10MerchandType"
>> > da10MerchandType.Fill(DsSelectionList1, "lkp10MerchandType")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp10MerchandType")
>> > Case "lkp11CommResource"
>> > da11CommResource.Fill(DsSelectionList1, "lkp11CommResource")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp11CommResource")
>> > Case "lkp12TelephonyDevice"
>> > da12TelephonyDevice.Fill(DsSelectionList1, "lkp12TelephonyDevice")
>> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> > "lkp12TelephonyDevice")
>> > Case "lkp13WwwType"
>> > da13WwwType.Fill(DsSelectionList1, "lkp13WwwType")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp13WwwType")
>> > Case "lkp14ModeOfContact"
>> > da14ModeOfContact.Fill(DsSelectionList1, "lkp14ModeOfContact")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp14ModeOfContact")
>> > Case "lkp15MsgTopic"
>> > da15MsgTopic.Fill(DsSelectionList1, "lkp15MsgTopic")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp15MsgTopic")
>> > Case "lkp16ScheduleType"
>> > da16ScheduleType.Fill(DsSelectionList1, "lkp16ScheduleType")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp16ScheduleType")
>> > Case "lkp17WeightGroup"
>> > da17WeightGroup.Fill(DsSelectionList1, "lkp17WeightGroup")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp17WeightGroup")
>> > Case "lkp18ProgramCategory"
>> > da18ProgramCategory.Fill(DsSelectionList1, "lkp18ProgramCategory")
>> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> > "lkp18ProgramCategory")
>> > Case "lkp19Element"
>> > da19Element.Fill(DsSelectionList1, "lkp19Element")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp19Element")
>> > Case "enm1FoodAllergy"
>> > daFoodAllergy.Fill(DsSelectionList1, "enm1FoodAllergy")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm1FoodAllergy")
>> > Case "enm2EnvironAllergy"
>> > daEnvironAllergy.Fill(DsSelectionList1, "enm2EnvironAllergy")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm2EnvironAllergy")
>> > Case "enm3MedicalAllergy"
>> > daMedicalAllergy.Fill(DsSelectionList1, "enm3MedicalAllergy")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm3MedicalAllergy")
>> > Case "enm4MedConcern"
>> > daMedConcern.Fill(DsSelectionList1, "enm4MedConcern")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm4MedConcern")
>> > Case "enm5ActivityRequest"
>> > daActivityRequest.Fill(DsSelectionList1, "enm5ActivityRequest")
>> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> > "enm5ActivityRequest")
>> > Case Else
>> > TableErrorMessage()
>> > End Select
>> >
>> > === End of lengthy code ===
>> >
>> > === Start of Short code (5 lines) =====
>> >
>> > Dim strDa As String
>> > strDa = strTbl.Remove(0, 5)
>> > strDa = strDa.Insert(0, "Da")
>> >
>> > CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> > DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>> >
>> > === End of short code ===
>> >
>> > (There are other advantages too)
>> >
>> > Back to my original question:
>> > The code works in debug mode.
>> > The exe works.
>> >
>> > Does anyone anticipate me having any problems with this after
>> > deployment?
>> > Does anyone suggest any other alturnatives?
>> > Does anyone suggest a way to stop the annoying build errors?
>> >
>> > Thank you,
>> > --Doug
>> >
>> >
>> > "Scott M." <s-***@nospam.nospam> wrote in message
>> > news:<u5**************@TK2MSFTNGP12.phx.gbl>...
>> >> You say that you get an error and you say that the code works? It
>> >> can't
>> >> be
>> >> both. Your problem is this line:
>> >>
>> >> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> >>
>> >> Because "strDa" is an actual String object and string object can't
>> >> be
>> >> converted to DataAdapter objects. I know that you want to wind up
>> >> with a
>> >> DataAdapter named whatever "strDa" is named, but this command won't
>> >> do
>> >> it.
>> >>
>> >> Somewhere else in your code you must be creating an instance of a
>> >> DataAdapter. What are you calling that one? Let's see that code.
>> >>
>> >>
>> >>
>> >> "Douglas Buchanan" <db*********@hotmail.com> wrote in message
>> >> news:72************************@posting.google.com ...
>> >> >I am using the following code instead of a very lengthly select
>> >> >case
>> >> > statement.
>> >> >
>> >> > (I have a lot of lookup tables in a settings form that are
>> >> > selected
>> >> > from a ListBox. The data adapters are given a similar name to the
>> >> > table. Rather than making a long Select Case that could become
>> >> > obsolete if lookup tables are added and the source table of the
>> >> > ListBox is edited I came up with this code.)
>> >> >
>> >> > This code works but of course it gives me build errors.
>> >> >
>> >> > Error:[Value of type 'String' cannot be converted to
>> >> > 'System.Data.SqlClient.SqlDataAdapter'.]
>> >> >
>> >> > === code snippit ===
>> >> > Private Sub lstMasterLists_Click(ByVal sender As Object, _
>> >> > ByVal e As System.EventArgs) Handles lstMasterLists.Click
>> >> >
>> >> > 'To populate the dgMasterLists with the proper table
>> >> > '1. use the returned TableName to make the DataAdapter name
>> >> > '2. Convert the string to the DataAdampter type
>> >> >
>> >> > 'Get the TableName from the selected item in the list box
>> >> > Dim strTbl As String
>> >> > strTbl = Me.lstMasterLists.SelectedItem.ToString
>> >> >
>> >> > 'Make the DataAdapter name from the table name
>> >> > Dim strDa As String
>> >> > strDa = strTbl.Remove(0, 5)
>> >> > strDa = strDa.Insert(0, "Da")
>> >> >
>> >> > CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> >> > DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>> >> >
>> >> > End Sub
>> >> > ======
>> >> >
>> >> > The code works in debug mode.
>> >> > The exe works.
>> >> >
>> >> > Does anyone anticipate me having any problems with this after
>> >> > deployment?
>> >> > Does anyone suggest any other alturnatives?
>> >> > Does anyone suggest a way to stop the annoying build errors?

Jul 21 '05 #6

P: n/a
[Doug] I admit I'm not familiar with making code not based on a form.
I was trying to make code that you could run without having a
drag&drop DataAdapter and an xsd file.

My code doesn't use a DataAdapter that has been dragged and dropped either.
I said: "If you use the Forms Designer to draw out your button and datagrid
and set their respective properties in the properties window, we don't even
have to look at their code (or move it around). "

Are you saying that you don't want to create the button and toolbar from the
VS.NET toolbox? You want to manually create those in code, from scratch as
well?
Jul 21 '05 #7

P: n/a
Scott,

I apologize!!
Please don't take it as a critique
I missed your complete message - I'm using Google, Google chops off
after about fifty lines - It tells you that the that the messgae
continues - I overlooked it. Usually what is chopped off is the
history of the total thread back to the beginning - or as much as the
last writer decided to leave there. I like Google groups, but it's a
little too easy to miss the full content. [Perhaps I need to look into
other providers of groups. You may make recommendations.]

I DO appreciate critique and input, and as you can see I can benefit
from it, but what I thought was happening was that my thread had
gotton sidetracked from my request for a suggestion and alternative,
to other things NOT at the core of the thread. I was frustrated
because you seemed to be telling me that I was going about it the
wrong way while that was the whole point of the my thread.
I'm curious why you didn't mention any of that in your reply.
I will eagerly go back and read you full post. - I am very sorry for
the confusion I caused.

--Doug

"Scott M." <s-***@nospam.nospam> wrote in message news:<uy**************@TK2MSFTNGP10.phx.gbl>... Please don't take it as a critique, but I had difficulty in understanding
where your problem was because of all the unnecessary code changes. I
mentioned Option Strict because you kept saying how you had build errors,
but the code worked. With Option Strict On, you can't even compile your
code when it has errors, so I still am confused as to how you can have
errors but your code works.

I tried to give you the benefit of my experience with .NET because I could
tell from the code that you wrote that you might gain from it. It wasn't
meant as an insult. I don't think I presented anything in a rude or
condescending way. I was simply trying to help you reach your goal of
slimming down your code.

I asked you to send me the code that you said was working. Now, you are
saying that the code you sent was for illustration purposes and you are
berating me for not knowing this? I've noticed that below you didn't say
anything about the other "critiques" I gave you to reduce your code and make
it cleaner like getting rid of the explicit command object or declaring your
string and chopping its first 5 chars off at the same time.

The point is that no one knows your application better than you do and for
someone else to help, you sometimes have to look at the whole thing, not
just the one line that is failing. I did that and, as I did, I found that I
would have written this application differently than you did. This is what
you asked for..."another alternative".

I also spent a good 2 hours of my time trying to help you solve your
problem. I ultimately provided you with code that works and meets your
criteria of being as lean as possible. I'm curious why you didn't mention
any of that in your reply. Did you try it? Does it do what you were
looking for? Did you understand my approach? Wasn't it that "other
alternative" you had asked for?

I basically wrote this piece of the application for you and tested it. And
you have no comments on that? Wow...you're welcome.
"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
Scott,

I'm not looking for a critique on the last sample code I sent. I threw
it together for illustration purposes.

Scott > I would STRONGLY recommend that you turn Option Strict ON

[Doug] I always use Option Strict in my code.

Scott > Why are your putting da in quotes here?

[Doug] For illustration purposes. Did you forget what this thread is
about? My use of...
CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")
... was completely out of context of my original post.
I provided it in isolation for you to illustrate how it worked in the
presence of a actual SqlDataAdapter.

Scott > you seem to have moved a lot of VS.NET generated code around
and put it in different places.

[Doug] I admit I'm not familiar with making code not based on a form.
I was trying to make code that you could run without having a
drag&drop DataAdapter and an xsd file.

Scott > I understand what you are trying to do, but I think that you
are going about this entirely the wrong way.

[Doug] Exactly the point of my post. Remember my question in my
original post: "Does anyone suggest any other alternatives?"

I thought I found a way to replace 70 lines of code with 5 and wanted
to know what to expect from my approach and asked for suggested
alternatives.

Are there any alternatives - (please see my second post in this thread
if you need a refresher on the context.)

Thank you,
--Doug

"Scott M." <s-***@nospam.nospam> wrote in message
news:<Of**************@TK2MSFTNGP09.phx.gbl>...
A few things here:

"Imports System.Windows.Forms" is not needed if you are using VS.NET
since a
project wide imports statement to this namespace is made automatically
for
you.

I would STRONGLY recommend that you trun Option Strict ON (in the Build
section of your project's properties). With OS = ON, if you have build
errors, then your code can't run and that is a good thing. This is why I
asked you to explain how you could be getting build errors and still say
that your code runs.

Now, as for this:

CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")

Why are your putting da in quotes here? This code doesn't pass the
Option
Strict check. da is the programmatic name of the object, so you wouldn't
put its name in as a string. This is the exact problem that you are
having
when trying to dynamically set the DataAdapter name. You CANT'T take a
string and cast it as a DataAdapter. And, maybe most importantly, why
are
you trying to cast da as a DataAdapter when it already is a DataAdapter?

CType("dg", SqlDataAdapter).SetDataBinding(ds, "Employees")

And here, you are again placing the object name (dg) in quotes and then
casting it to a DataAdapter? First dg is a DataGrid, not a DataAdapter.
Second, even if it was a DataAdapter, you wouldn't put its name in
quotes,
because then you are trying to cast a string as a DataAdapter.

Douglas, I understand what you are trying to do, but I think that you are
going about this entirely the wrong way. As I look at the code you
posted,
I am first struck that you seem to have moved a lot of VS.NET generated
code
around and put it in different places. While not illegal to do this, I
can't understand why...it just makes everything much more difficult to
follow. I'm even trying to understand why you are launching your
application the way you are:

> Public Shared Sub Main()
> System.Windows.Forms.Application.Run(New NortwindEmployee)
> End Sub

Again, while not incorrect, wouldn't it easier to just set the form as
your
project's start up object?

How many DataGrids and DataSets do you have? In your OP, you talk about
DgMasterLists and DsMasterLists1and in your last post you talk about dg
and
ds? If there really are 2, you need to post ALL of your code.

So much of your code can be condensed or eliminated (which would make
things
so much easier). If you use the Forms Designer to draw out your button
and
datagrid and set their respective properties in the properties window, we
don't even have to look at their code (or move it around). It will all
be
nicely contained in the Windows Forms Designer Generated Code and this is
all you have to add to get the button and the grid up and running:

--------------------------------------------------------------------------------
Private conStr As String = "workstation id=SEDNA;packet size=4096;" & _
"integrated security=SSPI;data
source=(local);" & _
"persist security
info=False;initial
catalog=Northwind"

Private selSQL As String = "SELECT EmployeeID, LastName, FirstName,
Title, "
& _
"TitleOfCourtesy, BirthDate,
HireDate, Address, City, Region, " & _
"PostalCode, Country,
HomePhone,
Extension, Photo, Notes, " & _
"ReportsTo, PhotoPath FROM
Employees"

Private con As New SqlConnection(conStr)
Private da As New SqlDataAdapter(selSQL, con)
Private ds As New DataSet
--------------------------------------------------------------------------------
Private Sub btnPush_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnPush.Click
'Fill DataAdapter
da.Fill(ds, "Employees")

'Bind DataGrid
dg.SetDataBinding(ds, "Employees")
End Sub
--------------------------------------------------------------------------------

You don't even need the command object that you had made.

*********Now that we've cleaned up the initial code, let's concentrate on
the point of your original post....
I would not use the Click event of a listbox because this code would run
everytime someone clicks the list (even if they click the same entry as
last
time). Instead use the SelectedIndexChanged event handler of a ComboBox
so
that you only run this when it's needed (more efficient).

Private Sub lstMasterLists_SelectedIndexChanged _
(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles lstMasterLists.SelectedIndexChanged

'Get the TableName from the selected item in the list box
'and modify it to match an actual table name in the db
Dim strTbl As String = lstMasterLists.Text.Remove(0, 5)

'Each time a user selects a different table, re-populate the grid
with
that data
Dim newDA As New SqlClient.SqlDataAdapter(selSQL, con)
Dim newDS As New DataSet
newDA.Fill(newDS, strTbl)
dg.SetDataBinding(newDS, strTbl)
End Sub

BOTTOM LINE: I don't see a need for you to worry about dynamically
naming
the DataAdapter at all, no one ever see's this. You only need to have
"a"
DataAdapter to use each time someone wants to see different table data.
Instead, the name of the table being added to the dataset is what's more
important and we can use your string to get that name.

The problem (or last remaining issue) I see is that for different tables,
you need a different SQL string. You could prepare these strings at the
module level and using a simple case statement, figure out which is
needed:

Dim OrdersSQL As String = "SELECT * FROM Orders"
Dim ProductsSQL As String = "SELECT * FROM Products"
Dim RegionSQL As String = "SELECT * FROM Region"

Private Sub lstMasterLists_SelectedIndexChanged _
(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles lstMasterLists.SelectedIndexChanged

'Get the TableName from the selected item in the list box
'and modify it to match an actual table name in the db
Dim strTbl As String = lstMasterLists.Text.Remove(0, 5)

Select Case strTbl
Case "LastName"
selSQL = OrdersSQL
Case "FirstName"
selSQL = ProductsSQL
Case "Title"
selSQL = RegionSQL
End Select

'Each time a user selects a different table, re-populate the grid
with
that data
Dim newDA As New SqlClient.SqlDataAdapter(selSQL, con)
Dim newDS As New DataSet
newDA.Fill(newDS, strTbl)
dg.SetDataBinding(newDS, strTbl)
End Sub
I have tested all the code I have given here and it works.
================================================== ===========
"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
> Scott,
>
> Below is code that you you can run that illustrates the use of CType()
> to pass a string (with the same text as an existing DataAdapter) to
> type SqlDataAdapter so that it will refer to an actual exitsing
> DataAdapter of the same name.
>
> (You need SQL Server installed locally with the Northwind database ~
> Edit the connection string as needed.)
>
> === Start of code ===
> Imports System.Data.SqlClient
> Imports System.Windows.Forms
>
> Public Class NortwindEmployee
> Inherits Form
>
> 'Controls
> Friend WithEvents btn As New Button
> Friend WithEvents dg As New DataGrid
>
> 'Connection
> Private cn As New SqlConnection
>
> 'Command
> Private cmSel As New SqlCommand
>
> 'DataAdapter
> Private da As New SqlDataAdapter
>
> 'DataSet
> Private ds As New DataSet
>
> Public Shared Sub Main()
> System.Windows.Forms.Application.Run(New NortwindEmployee)
>
> End Sub
>
> Public Sub New()
>
> 'Control - Button
> btn.Location = New System.Drawing.Point(8, 8)
> btn.Name = "btn"
> btn.TabIndex = 0
> btn.Text = "Button1"
>
> 'Control - DataGrid
> dg.DataMember = ""
> dg.HeaderForeColor = System.Drawing.SystemColors.ControlText
> dg.Location = New System.Drawing.Point(8, 40)
> dg.Name = "dg"
> dg.Size = New System.Drawing.Size(280, 160)
> dg.TabIndex = 1
>
> 'ConnectionString
> cn.ConnectionString = "workstation id=SEDNA;packet
> size=4096;integrated security=SSPI;data source=(local);persist
> security info=False;initial catalog=Northwind"
>
> 'DataAdapter Commands
> da.SelectCommand = cmSel
>
> 'Select Command
> cmSel.CommandText = "SELECT EmployeeID, LastName, FirstName,
> Title, TitleOfCourtesy, BirthDate, HireDa" & _
> "te, Address, City, Region, PostalCode, Country, HomePhone,
> Extension, Photo, Not" & _
> "es, ReportsTo, PhotoPath FROM Employees"
> cmSel.Connection = cn
>
> End Sub
>
> Private Sub InitializeComponent()
>
> 'Form1
> AutoScaleBaseSize = New System.Drawing.Size(5, 13)
> ClientSize = New System.Drawing.Size(292, 206)
> Name = "NortwindEmployee"
> Text = "NortwindEmployee"
>
> End Sub
>
> Public Sub btn_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs)
>
> 'Fill DataAdapter
> 'da.Fill(Me.ds, "Employees")
> CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")
>
> 'Bind DataGrid
> 'dg.SetDataBinding(ds, "Employees")
> CType("dg", SqlDataAdapter).SetDataBinding(ds, "Employees")
>
> End Sub
>
> End Class
>
> =====================
> In effect the code 'CType("da", SqlDataAdapter)' is asking the build
> engine to consider the string "da" as a SqlDataAdapter.
>
> As I see it the build engine does either one of these two things;
> A.) Either the build engine is telling me that "da" is text and you
> need to be aware of what you are doing (puts the error on the task
> list) - then goes ahead and assocaites the "da" with SqlDataAdapter.
>
> or
>
> B.) The build engine encounters the "da" and stops but goes on to tell
> me why it stops by putting the error on the task list.
>
> c.) If there is some other thing the build engine is doing I can't
> think of it.
>
>
> There are two reasons I think that the build engine does option 'A'.
> 1.) It works.
> 2.) There are other situations such as untyped datasets where code is
> not recognized until it is "compiled" - (You know how intellisense
> does not recognize objects that are not strongly typed?)
>
> So.... I am open for comments on thaking this direction.
>
>
> "Scott M." <s-***@nospam.nospam> wrote in message
> news:<eO*************@TK2MSFTNGP11.phx.gbl>...
>> Doug,
>>
>> I understand what you are trying to do and I will ask again that you
>> post
>> ALL of your code (the short code). In what you have provided, we do
>> not
>> see
>> the code that creates the DataAdapters. What I'm getting at here is
>> that
>> this:
>>
>> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>>
>> even if it didn't throw an exception would not CREATE a DataAdapter
>> for
>> you.
>> It would only create a type. You must have an already instanced
>> DataAdapter
>> prior to this line that we can pass somewhere and refer to it as the
>> value
>> of your string.
>>
>>
>>
>> "Douglas Buchanan" <db*********@hotmail.com> wrote in message
>> news:72**************************@posting.google.c om...
>> > Scott,
>> >
>> > I'm sorry. I didn't make it clear enough. I'll explain.
>> >
>> > The code works because the DataAdapters already exists! I am just
>> > refering to its name by the text.
>> >
>> > The build engine informs me of build errors at the CType() statement
>> > because all it sees is that I am trying to turn text into a
>> > DataAdapter. The build engine doesn't compare that text to the name
>> > of
>> > the existing DataAdapter.
>> >
>> > This is what I did. (later... why I did it)
>> >
>> > I have these tables...
>> >
>> > lst01PrimaryOptions
>> > lst02SecondaryOptions
>> > lst03BusinsessSettings
>> > lst04FixedOptions
>> > ...
>> >
>> > When I made my DataAdapters I gave them these names...
>> >
>> > DaPrimaryOptions
>> > DaSecondaryOptions
>> > DaBusinsessSettings
>> > DaFixedOptions
>> > ...
>> >
>> > So if I take the table name and replace the first 5 letters with the
>> > letters "Da" I get the DataAdapter name.
>> >
>> > Why would I go thorugh this crazy string manipulation to arrive at
>> > an
>> > existing DataAdapter name?
>> >
>> > Because it saves me a hell of a lot of code!
>> > To replace 70 lines of code with only 5.
>> >
>> > Example:
>> > === Start of lengthy code (70 lines) ===
>> >
>> > Select Case sTableName
>> > Case "lkp01RefSource"
>> > da01RefSource.Fill(DsSelectionList1, "lkp01RefSource")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp01RefSource")
>> > Case "lkp02GrpCategory"
>> > da02GrpCategory.Fill(DsSelectionList1, "lkp02GrpCategory")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp02GrpCategory")
>> > Case "lkp03PrgmObjective"
>> > da03PrgmObjective.Fill(DsSelectionList1, "lkp03PrgmObjective")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp03PrgmObjective")
>> > Case "lkp06JobTitle"
>> > da06JobTitle.Fill(DsSelectionList1, "lkp06JobTitle")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp06JobTitle")
>> > Case "lkp07Qualification"
>> > da07Qualification.Fill(DsSelectionList1, "lkp07Qualification")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp07Qualification")
>> > Case "lkp08DayOfWeek"
>> > da08DayOfWeek.Fill(DsSelectionList1, "lkp08DayOfWeek")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp08DayOfWeek")
>> > Case "lkp09MealType"
>> > da09MealType.Fill(DsSelectionList1, "lkp09MealType")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp09MealType")
>> > Case "lkp10MerchandType"
>> > da10MerchandType.Fill(DsSelectionList1, "lkp10MerchandType")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp10MerchandType")
>> > Case "lkp11CommResource"
>> > da11CommResource.Fill(DsSelectionList1, "lkp11CommResource")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp11CommResource")
>> > Case "lkp12TelephonyDevice"
>> > da12TelephonyDevice.Fill(DsSelectionList1, "lkp12TelephonyDevice")
>> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> > "lkp12TelephonyDevice")
>> > Case "lkp13WwwType"
>> > da13WwwType.Fill(DsSelectionList1, "lkp13WwwType")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp13WwwType")
>> > Case "lkp14ModeOfContact"
>> > da14ModeOfContact.Fill(DsSelectionList1, "lkp14ModeOfContact")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp14ModeOfContact")
>> > Case "lkp15MsgTopic"
>> > da15MsgTopic.Fill(DsSelectionList1, "lkp15MsgTopic")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp15MsgTopic")
>> > Case "lkp16ScheduleType"
>> > da16ScheduleType.Fill(DsSelectionList1, "lkp16ScheduleType")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp16ScheduleType")
>> > Case "lkp17WeightGroup"
>> > da17WeightGroup.Fill(DsSelectionList1, "lkp17WeightGroup")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp17WeightGroup")
>> > Case "lkp18ProgramCategory"
>> > da18ProgramCategory.Fill(DsSelectionList1, "lkp18ProgramCategory")
>> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> > "lkp18ProgramCategory")
>> > Case "lkp19Element"
>> > da19Element.Fill(DsSelectionList1, "lkp19Element")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp19Element")
>> > Case "enm1FoodAllergy"
>> > daFoodAllergy.Fill(DsSelectionList1, "enm1FoodAllergy")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm1FoodAllergy")
>> > Case "enm2EnvironAllergy"
>> > daEnvironAllergy.Fill(DsSelectionList1, "enm2EnvironAllergy")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm2EnvironAllergy")
>> > Case "enm3MedicalAllergy"
>> > daMedicalAllergy.Fill(DsSelectionList1, "enm3MedicalAllergy")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm3MedicalAllergy")
>> > Case "enm4MedConcern"
>> > daMedConcern.Fill(DsSelectionList1, "enm4MedConcern")
>> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm4MedConcern")
>> > Case "enm5ActivityRequest"
>> > daActivityRequest.Fill(DsSelectionList1, "enm5ActivityRequest")
>> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> > "enm5ActivityRequest")
>> > Case Else
>> > TableErrorMessage()
>> > End Select
>> >
>> > === End of lengthy code ===
>> >
>> > === Start of Short code (5 lines) =====
>> >
>> > Dim strDa As String
>> > strDa = strTbl.Remove(0, 5)
>> > strDa = strDa.Insert(0, "Da")
>> >
>> > CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> > DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>> >
>> > === End of short code ===
>> >
>> > (There are other advantages too)
>> >
>> > Back to my original question:
>> > The code works in debug mode.
>> > The exe works.
>> >
>> > Does anyone anticipate me having any problems with this after
>> > deployment?
>> > Does anyone suggest any other alturnatives?
>> > Does anyone suggest a way to stop the annoying build errors?
>> >
>> > Thank you,
>> > --Doug
>> >
>> >
>> > "Scott M." <s-***@nospam.nospam> wrote in message
>> > news:<u5**************@TK2MSFTNGP12.phx.gbl>...
>> >> You say that you get an error and you say that the code works? It
>> >> can't
>> >> be
>> >> both. Your problem is this line:
>> >>
>> >> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> >>
>> >> Because "strDa" is an actual String object and string object can't
>> >> be
>> >> converted to DataAdapter objects. I know that you want to wind up
>> >> with a
>> >> DataAdapter named whatever "strDa" is named, but this command won't
>> >> do
>> >> it.
>> >>
>> >> Somewhere else in your code you must be creating an instance of a
>> >> DataAdapter. What are you calling that one? Let's see that code.
>> >>
>> >>
>> >>
>> >> "Douglas Buchanan" <db*********@hotmail.com> wrote in message
>> >> news:72************************@posting.google.com ...
>> >> >I am using the following code instead of a very lengthly select
>> >> >case
>> >> > statement.
>> >> >
>> >> > (I have a lot of lookup tables in a settings form that are
>> >> > selected
>> >> > from a ListBox. The data adapters are given a similar name to the
>> >> > table. Rather than making a long Select Case that could become
>> >> > obsolete if lookup tables are added and the source table of the
>> >> > ListBox is edited I came up with this code.)
>> >> >
>> >> > This code works but of course it gives me build errors.
>> >> >
>> >> > Error:[Value of type 'String' cannot be converted to
>> >> > 'System.Data.SqlClient.SqlDataAdapter'.]
>> >> >
>> >> > === code snippit ===
>> >> > Private Sub lstMasterLists_Click(ByVal sender As Object, _
>> >> > ByVal e As System.EventArgs) Handles lstMasterLists.Click
>> >> >
>> >> > 'To populate the dgMasterLists with the proper table
>> >> > '1. use the returned TableName to make the DataAdapter name
>> >> > '2. Convert the string to the DataAdampter type
>> >> >
>> >> > 'Get the TableName from the selected item in the list box
>> >> > Dim strTbl As String
>> >> > strTbl = Me.lstMasterLists.SelectedItem.ToString
>> >> >
>> >> > 'Make the DataAdapter name from the table name
>> >> > Dim strDa As String
>> >> > strDa = strTbl.Remove(0, 5)
>> >> > strDa = strDa.Insert(0, "Da")
>> >> >
>> >> > CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> >> > DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>> >> >
>> >> > End Sub
>> >> > ======
>> >> >
>> >> > The code works in debug mode.
>> >> > The exe works.
>> >> >
>> >> > Does anyone anticipate me having any problems with this after
>> >> > deployment?
>> >> > Does anyone suggest any other alturnatives?
>> >> > Does anyone suggest a way to stop the annoying build errors?

Jul 21 '05 #8

P: n/a
No problem Doug, thanks for clearing that up.

I use MS Outlook Express to read and write to many newsgroups. You probably
already have it and all you have to do is create a newsgroup account with
the following settings:

news server: msnews.microsoft.com
Create a user name of your choosing
Create a reply to email address (don't post your actual email address as the
spambots will find you) do something like Do************@something.com

Please let me know how my proposed solution works for you.

-Scott
"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
Scott,

I apologize!!
Please don't take it as a critique


I missed your complete message - I'm using Google, Google chops off
after about fifty lines - It tells you that the that the messgae
continues - I overlooked it. Usually what is chopped off is the
history of the total thread back to the beginning - or as much as the
last writer decided to leave there. I like Google groups, but it's a
little too easy to miss the full content. [Perhaps I need to look into
other providers of groups. You may make recommendations.]

I DO appreciate critique and input, and as you can see I can benefit
from it, but what I thought was happening was that my thread had
gotton sidetracked from my request for a suggestion and alternative,
to other things NOT at the core of the thread. I was frustrated
because you seemed to be telling me that I was going about it the
wrong way while that was the whole point of the my thread.
I'm curious why you didn't mention any of that in your reply.


I will eagerly go back and read you full post. - I am very sorry for
the confusion I caused.

--Doug

"Scott M." <s-***@nospam.nospam> wrote in message
news:<uy**************@TK2MSFTNGP10.phx.gbl>...
Please don't take it as a critique, but I had difficulty in understanding
where your problem was because of all the unnecessary code changes. I
mentioned Option Strict because you kept saying how you had build errors,
but the code worked. With Option Strict On, you can't even compile your
code when it has errors, so I still am confused as to how you can have
errors but your code works.

I tried to give you the benefit of my experience with .NET because I
could
tell from the code that you wrote that you might gain from it. It wasn't
meant as an insult. I don't think I presented anything in a rude or
condescending way. I was simply trying to help you reach your goal of
slimming down your code.

I asked you to send me the code that you said was working. Now, you are
saying that the code you sent was for illustration purposes and you are
berating me for not knowing this? I've noticed that below you didn't say
anything about the other "critiques" I gave you to reduce your code and
make
it cleaner like getting rid of the explicit command object or declaring
your
string and chopping its first 5 chars off at the same time.

The point is that no one knows your application better than you do and
for
someone else to help, you sometimes have to look at the whole thing, not
just the one line that is failing. I did that and, as I did, I found
that I
would have written this application differently than you did. This is
what
you asked for..."another alternative".

I also spent a good 2 hours of my time trying to help you solve your
problem. I ultimately provided you with code that works and meets your
criteria of being as lean as possible. I'm curious why you didn't
mention
any of that in your reply. Did you try it? Does it do what you were
looking for? Did you understand my approach? Wasn't it that "other
alternative" you had asked for?

I basically wrote this piece of the application for you and tested it.
And
you have no comments on that? Wow...you're welcome.
"Douglas Buchanan" <db*********@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
> Scott,
>
> I'm not looking for a critique on the last sample code I sent. I threw
> it together for illustration purposes.
>
> Scott > I would STRONGLY recommend that you turn Option Strict ON
>
> [Doug] I always use Option Strict in my code.
>
> Scott > Why are your putting da in quotes here?
>
> [Doug] For illustration purposes. Did you forget what this thread is
> about? My use of...
> CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")
> ... was completely out of context of my original post.
> I provided it in isolation for you to illustrate how it worked in the
> presence of a actual SqlDataAdapter.
>
> Scott > you seem to have moved a lot of VS.NET generated code around
> and put it in different places.
>
> [Doug] I admit I'm not familiar with making code not based on a form.
> I was trying to make code that you could run without having a
> drag&drop DataAdapter and an xsd file.
>
> Scott > I understand what you are trying to do, but I think that you
> are going about this entirely the wrong way.
>
> [Doug] Exactly the point of my post. Remember my question in my
> original post: "Does anyone suggest any other alternatives?"
>
> I thought I found a way to replace 70 lines of code with 5 and wanted
> to know what to expect from my approach and asked for suggested
> alternatives.
>
> Are there any alternatives - (please see my second post in this thread
> if you need a refresher on the context.)
>
> Thank you,
> --Doug
>
> "Scott M." <s-***@nospam.nospam> wrote in message
> news:<Of**************@TK2MSFTNGP09.phx.gbl>...
>> A few things here:
>>
>> "Imports System.Windows.Forms" is not needed if you are using VS.NET
>> since a
>> project wide imports statement to this namespace is made automatically
>> for
>> you.
>>
>> I would STRONGLY recommend that you trun Option Strict ON (in the
>> Build
>> section of your project's properties). With OS = ON, if you have
>> build
>> errors, then your code can't run and that is a good thing. This is
>> why I
>> asked you to explain how you could be getting build errors and still
>> say
>> that your code runs.
>>
>> Now, as for this:
>>
>> CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")
>>
>> Why are your putting da in quotes here? This code doesn't pass the
>> Option
>> Strict check. da is the programmatic name of the object, so you
>> wouldn't
>> put its name in as a string. This is the exact problem that you are
>> having
>> when trying to dynamically set the DataAdapter name. You CANT'T take
>> a
>> string and cast it as a DataAdapter. And, maybe most importantly, why
>> are
>> you trying to cast da as a DataAdapter when it already is a
>> DataAdapter?
>>
>> CType("dg", SqlDataAdapter).SetDataBinding(ds, "Employees")
>>
>> And here, you are again placing the object name (dg) in quotes and
>> then
>> casting it to a DataAdapter? First dg is a DataGrid, not a
>> DataAdapter.
>> Second, even if it was a DataAdapter, you wouldn't put its name in
>> quotes,
>> because then you are trying to cast a string as a DataAdapter.
>>
>> Douglas, I understand what you are trying to do, but I think that you
>> are
>> going about this entirely the wrong way. As I look at the code you
>> posted,
>> I am first struck that you seem to have moved a lot of VS.NET
>> generated
>> code
>> around and put it in different places. While not illegal to do this,
>> I
>> can't understand why...it just makes everything much more difficult to
>> follow. I'm even trying to understand why you are launching your
>> application the way you are:
>>
>> > Public Shared Sub Main()
>> > System.Windows.Forms.Application.Run(New NortwindEmployee)
>> > End Sub
>>
>> Again, while not incorrect, wouldn't it easier to just set the form as
>> your
>> project's start up object?
>>
>> How many DataGrids and DataSets do you have? In your OP, you talk
>> about
>> DgMasterLists and DsMasterLists1and in your last post you talk about
>> dg
>> and
>> ds? If there really are 2, you need to post ALL of your code.
>>
>> So much of your code can be condensed or eliminated (which would make
>> things
>> so much easier). If you use the Forms Designer to draw out your
>> button
>> and
>> datagrid and set their respective properties in the properties window,
>> we
>> don't even have to look at their code (or move it around). It will
>> all
>> be
>> nicely contained in the Windows Forms Designer Generated Code and this
>> is
>> all you have to add to get the button and the grid up and running:
>>
>> --------------------------------------------------------------------------------
>> Private conStr As String = "workstation id=SEDNA;packet size=4096;" &
>> _
>> "integrated security=SSPI;data
>> source=(local);" & _
>> "persist security
>> info=False;initial
>> catalog=Northwind"
>>
>> Private selSQL As String = "SELECT EmployeeID, LastName, FirstName,
>> Title, "
>> & _
>> "TitleOfCourtesy,
>> BirthDate,
>> HireDate, Address, City, Region, " & _
>> "PostalCode, Country,
>> HomePhone,
>> Extension, Photo, Notes, " & _
>> "ReportsTo, PhotoPath FROM
>> Employees"
>>
>> Private con As New SqlConnection(conStr)
>> Private da As New SqlDataAdapter(selSQL, con)
>> Private ds As New DataSet
>> --------------------------------------------------------------------------------
>> Private Sub btnPush_Click(ByVal sender As System.Object, ByVal e As
>> System.EventArgs) Handles btnPush.Click
>> 'Fill DataAdapter
>> da.Fill(ds, "Employees")
>>
>> 'Bind DataGrid
>> dg.SetDataBinding(ds, "Employees")
>> End Sub
>> --------------------------------------------------------------------------------
>>
>> You don't even need the command object that you had made.
>>
>> *********Now that we've cleaned up the initial code, let's concentrate
>> on
>> the point of your original post....
>> I would not use the Click event of a listbox because this code would
>> run
>> everytime someone clicks the list (even if they click the same entry
>> as
>> last
>> time). Instead use the SelectedIndexChanged event handler of a
>> ComboBox
>> so
>> that you only run this when it's needed (more efficient).
>>
>> Private Sub lstMasterLists_SelectedIndexChanged _
>> (ByVal sender As System.Object, ByVal e As System.EventArgs) _
>> Handles lstMasterLists.SelectedIndexChanged
>>
>> 'Get the TableName from the selected item in the list box
>> 'and modify it to match an actual table name in the db
>> Dim strTbl As String = lstMasterLists.Text.Remove(0, 5)
>>
>> 'Each time a user selects a different table, re-populate the grid
>> with
>> that data
>> Dim newDA As New SqlClient.SqlDataAdapter(selSQL, con)
>> Dim newDS As New DataSet
>> newDA.Fill(newDS, strTbl)
>> dg.SetDataBinding(newDS, strTbl)
>> End Sub
>>
>> BOTTOM LINE: I don't see a need for you to worry about dynamically
>> naming
>> the DataAdapter at all, no one ever see's this. You only need to have
>> "a"
>> DataAdapter to use each time someone wants to see different table
>> data.
>> Instead, the name of the table being added to the dataset is what's
>> more
>> important and we can use your string to get that name.
>>
>> The problem (or last remaining issue) I see is that for different
>> tables,
>> you need a different SQL string. You could prepare these strings at
>> the
>> module level and using a simple case statement, figure out which is
>> needed:
>>
>> Dim OrdersSQL As String = "SELECT * FROM Orders"
>> Dim ProductsSQL As String = "SELECT * FROM Products"
>> Dim RegionSQL As String = "SELECT * FROM Region"
>>
>> Private Sub lstMasterLists_SelectedIndexChanged _
>> (ByVal sender As System.Object, ByVal e As System.EventArgs) _
>> Handles lstMasterLists.SelectedIndexChanged
>>
>> 'Get the TableName from the selected item in the list box
>> 'and modify it to match an actual table name in the db
>> Dim strTbl As String = lstMasterLists.Text.Remove(0, 5)
>>
>> Select Case strTbl
>> Case "LastName"
>> selSQL = OrdersSQL
>> Case "FirstName"
>> selSQL = ProductsSQL
>> Case "Title"
>> selSQL = RegionSQL
>> End Select
>>
>> 'Each time a user selects a different table, re-populate the grid
>> with
>> that data
>> Dim newDA As New SqlClient.SqlDataAdapter(selSQL, con)
>> Dim newDS As New DataSet
>> newDA.Fill(newDS, strTbl)
>> dg.SetDataBinding(newDS, strTbl)
>> End Sub
>>
>>
>> I have tested all the code I have given here and it works.
>> ================================================== ===========
>>
>>
>> "Douglas Buchanan" <db*********@hotmail.com> wrote in message
>> news:72**************************@posting.google.c om...
>> > Scott,
>> >
>> > Below is code that you you can run that illustrates the use of
>> > CType()
>> > to pass a string (with the same text as an existing DataAdapter) to
>> > type SqlDataAdapter so that it will refer to an actual exitsing
>> > DataAdapter of the same name.
>> >
>> > (You need SQL Server installed locally with the Northwind database ~
>> > Edit the connection string as needed.)
>> >
>> > === Start of code ===
>> > Imports System.Data.SqlClient
>> > Imports System.Windows.Forms
>> >
>> > Public Class NortwindEmployee
>> > Inherits Form
>> >
>> > 'Controls
>> > Friend WithEvents btn As New Button
>> > Friend WithEvents dg As New DataGrid
>> >
>> > 'Connection
>> > Private cn As New SqlConnection
>> >
>> > 'Command
>> > Private cmSel As New SqlCommand
>> >
>> > 'DataAdapter
>> > Private da As New SqlDataAdapter
>> >
>> > 'DataSet
>> > Private ds As New DataSet
>> >
>> > Public Shared Sub Main()
>> > System.Windows.Forms.Application.Run(New NortwindEmployee)
>> >
>> > End Sub
>> >
>> > Public Sub New()
>> >
>> > 'Control - Button
>> > btn.Location = New System.Drawing.Point(8, 8)
>> > btn.Name = "btn"
>> > btn.TabIndex = 0
>> > btn.Text = "Button1"
>> >
>> > 'Control - DataGrid
>> > dg.DataMember = ""
>> > dg.HeaderForeColor = System.Drawing.SystemColors.ControlText
>> > dg.Location = New System.Drawing.Point(8, 40)
>> > dg.Name = "dg"
>> > dg.Size = New System.Drawing.Size(280, 160)
>> > dg.TabIndex = 1
>> >
>> > 'ConnectionString
>> > cn.ConnectionString = "workstation id=SEDNA;packet
>> > size=4096;integrated security=SSPI;data source=(local);persist
>> > security info=False;initial catalog=Northwind"
>> >
>> > 'DataAdapter Commands
>> > da.SelectCommand = cmSel
>> >
>> > 'Select Command
>> > cmSel.CommandText = "SELECT EmployeeID, LastName, FirstName,
>> > Title, TitleOfCourtesy, BirthDate, HireDa" & _
>> > "te, Address, City, Region, PostalCode, Country, HomePhone,
>> > Extension, Photo, Not" & _
>> > "es, ReportsTo, PhotoPath FROM Employees"
>> > cmSel.Connection = cn
>> >
>> > End Sub
>> >
>> > Private Sub InitializeComponent()
>> >
>> > 'Form1
>> > AutoScaleBaseSize = New System.Drawing.Size(5, 13)
>> > ClientSize = New System.Drawing.Size(292, 206)
>> > Name = "NortwindEmployee"
>> > Text = "NortwindEmployee"
>> >
>> > End Sub
>> >
>> > Public Sub btn_Click(ByVal sender As System.Object, ByVal e As
>> > System.EventArgs)
>> >
>> > 'Fill DataAdapter
>> > 'da.Fill(Me.ds, "Employees")
>> > CType("da", SqlDataAdapter).Fill(Me.ds, "Employees")
>> >
>> > 'Bind DataGrid
>> > 'dg.SetDataBinding(ds, "Employees")
>> > CType("dg", SqlDataAdapter).SetDataBinding(ds, "Employees")
>> >
>> > End Sub
>> >
>> > End Class
>> >
>> > =====================
>> > In effect the code 'CType("da", SqlDataAdapter)' is asking the build
>> > engine to consider the string "da" as a SqlDataAdapter.
>> >
>> > As I see it the build engine does either one of these two things;
>> > A.) Either the build engine is telling me that "da" is text and you
>> > need to be aware of what you are doing (puts the error on the task
>> > list) - then goes ahead and assocaites the "da" with SqlDataAdapter.
>> >
>> > or
>> >
>> > B.) The build engine encounters the "da" and stops but goes on to
>> > tell
>> > me why it stops by putting the error on the task list.
>> >
>> > c.) If there is some other thing the build engine is doing I can't
>> > think of it.
>> >
>> >
>> > There are two reasons I think that the build engine does option 'A'.
>> > 1.) It works.
>> > 2.) There are other situations such as untyped datasets where code
>> > is
>> > not recognized until it is "compiled" - (You know how intellisense
>> > does not recognize objects that are not strongly typed?)
>> >
>> > So.... I am open for comments on thaking this direction.
>> >
>> >
>> > "Scott M." <s-***@nospam.nospam> wrote in message
>> > news:<eO*************@TK2MSFTNGP11.phx.gbl>...
>> >> Doug,
>> >>
>> >> I understand what you are trying to do and I will ask again that
>> >> you
>> >> post
>> >> ALL of your code (the short code). In what you have provided, we
>> >> do
>> >> not
>> >> see
>> >> the code that creates the DataAdapters. What I'm getting at here
>> >> is
>> >> that
>> >> this:
>> >>
>> >> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> >>
>> >> even if it didn't throw an exception would not CREATE a DataAdapter
>> >> for
>> >> you.
>> >> It would only create a type. You must have an already instanced
>> >> DataAdapter
>> >> prior to this line that we can pass somewhere and refer to it as
>> >> the
>> >> value
>> >> of your string.
>> >>
>> >>
>> >>
>> >> "Douglas Buchanan" <db*********@hotmail.com> wrote in message
>> >> news:72**************************@posting.google.c om...
>> >> > Scott,
>> >> >
>> >> > I'm sorry. I didn't make it clear enough. I'll explain.
>> >> >
>> >> > The code works because the DataAdapters already exists! I am just
>> >> > refering to its name by the text.
>> >> >
>> >> > The build engine informs me of build errors at the CType()
>> >> > statement
>> >> > because all it sees is that I am trying to turn text into a
>> >> > DataAdapter. The build engine doesn't compare that text to the
>> >> > name
>> >> > of
>> >> > the existing DataAdapter.
>> >> >
>> >> > This is what I did. (later... why I did it)
>> >> >
>> >> > I have these tables...
>> >> >
>> >> > lst01PrimaryOptions
>> >> > lst02SecondaryOptions
>> >> > lst03BusinsessSettings
>> >> > lst04FixedOptions
>> >> > ...
>> >> >
>> >> > When I made my DataAdapters I gave them these names...
>> >> >
>> >> > DaPrimaryOptions
>> >> > DaSecondaryOptions
>> >> > DaBusinsessSettings
>> >> > DaFixedOptions
>> >> > ...
>> >> >
>> >> > So if I take the table name and replace the first 5 letters with
>> >> > the
>> >> > letters "Da" I get the DataAdapter name.
>> >> >
>> >> > Why would I go thorugh this crazy string manipulation to arrive
>> >> > at
>> >> > an
>> >> > existing DataAdapter name?
>> >> >
>> >> > Because it saves me a hell of a lot of code!
>> >> > To replace 70 lines of code with only 5.
>> >> >
>> >> > Example:
>> >> > === Start of lengthy code (70 lines) ===
>> >> >
>> >> > Select Case sTableName
>> >> > Case "lkp01RefSource"
>> >> > da01RefSource.Fill(DsSelectionList1, "lkp01RefSource")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp01RefSource")
>> >> > Case "lkp02GrpCategory"
>> >> > da02GrpCategory.Fill(DsSelectionList1, "lkp02GrpCategory")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp02GrpCategory")
>> >> > Case "lkp03PrgmObjective"
>> >> > da03PrgmObjective.Fill(DsSelectionList1, "lkp03PrgmObjective")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp03PrgmObjective")
>> >> > Case "lkp06JobTitle"
>> >> > da06JobTitle.Fill(DsSelectionList1, "lkp06JobTitle")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp06JobTitle")
>> >> > Case "lkp07Qualification"
>> >> > da07Qualification.Fill(DsSelectionList1, "lkp07Qualification")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp07Qualification")
>> >> > Case "lkp08DayOfWeek"
>> >> > da08DayOfWeek.Fill(DsSelectionList1, "lkp08DayOfWeek")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp08DayOfWeek")
>> >> > Case "lkp09MealType"
>> >> > da09MealType.Fill(DsSelectionList1, "lkp09MealType")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp09MealType")
>> >> > Case "lkp10MerchandType"
>> >> > da10MerchandType.Fill(DsSelectionList1, "lkp10MerchandType")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp10MerchandType")
>> >> > Case "lkp11CommResource"
>> >> > da11CommResource.Fill(DsSelectionList1, "lkp11CommResource")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp11CommResource")
>> >> > Case "lkp12TelephonyDevice"
>> >> > da12TelephonyDevice.Fill(DsSelectionList1,
>> >> > "lkp12TelephonyDevice")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp12TelephonyDevice")
>> >> > Case "lkp13WwwType"
>> >> > da13WwwType.Fill(DsSelectionList1, "lkp13WwwType")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp13WwwType")
>> >> > Case "lkp14ModeOfContact"
>> >> > da14ModeOfContact.Fill(DsSelectionList1, "lkp14ModeOfContact")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp14ModeOfContact")
>> >> > Case "lkp15MsgTopic"
>> >> > da15MsgTopic.Fill(DsSelectionList1, "lkp15MsgTopic")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp15MsgTopic")
>> >> > Case "lkp16ScheduleType"
>> >> > da16ScheduleType.Fill(DsSelectionList1, "lkp16ScheduleType")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp16ScheduleType")
>> >> > Case "lkp17WeightGroup"
>> >> > da17WeightGroup.Fill(DsSelectionList1, "lkp17WeightGroup")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp17WeightGroup")
>> >> > Case "lkp18ProgramCategory"
>> >> > da18ProgramCategory.Fill(DsSelectionList1,
>> >> > "lkp18ProgramCategory")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "lkp18ProgramCategory")
>> >> > Case "lkp19Element"
>> >> > da19Element.Fill(DsSelectionList1, "lkp19Element")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "lkp19Element")
>> >> > Case "enm1FoodAllergy"
>> >> > daFoodAllergy.Fill(DsSelectionList1, "enm1FoodAllergy")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm1FoodAllergy")
>> >> > Case "enm2EnvironAllergy"
>> >> > daEnvironAllergy.Fill(DsSelectionList1, "enm2EnvironAllergy")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "enm2EnvironAllergy")
>> >> > Case "enm3MedicalAllergy"
>> >> > daMedicalAllergy.Fill(DsSelectionList1, "enm3MedicalAllergy")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "enm3MedicalAllergy")
>> >> > Case "enm4MedConcern"
>> >> > daMedConcern.Fill(DsSelectionList1, "enm4MedConcern")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1, "enm4MedConcern")
>> >> > Case "enm5ActivityRequest"
>> >> > daActivityRequest.Fill(DsSelectionList1, "enm5ActivityRequest")
>> >> > DgMasterLists.SetDataBinding(DsSelectionList1,
>> >> > "enm5ActivityRequest")
>> >> > Case Else
>> >> > TableErrorMessage()
>> >> > End Select
>> >> >
>> >> > === End of lengthy code ===
>> >> >
>> >> > === Start of Short code (5 lines) =====
>> >> >
>> >> > Dim strDa As String
>> >> > strDa = strTbl.Remove(0, 5)
>> >> > strDa = strDa.Insert(0, "Da")
>> >> >
>> >> > CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> >> > DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>> >> >
>> >> > === End of short code ===
>> >> >
>> >> > (There are other advantages too)
>> >> >
>> >> > Back to my original question:
>> >> > The code works in debug mode.
>> >> > The exe works.
>> >> >
>> >> > Does anyone anticipate me having any problems with this after
>> >> > deployment?
>> >> > Does anyone suggest any other alturnatives?
>> >> > Does anyone suggest a way to stop the annoying build errors?
>> >> >
>> >> > Thank you,
>> >> > --Doug
>> >> >
>> >> >
>> >> > "Scott M." <s-***@nospam.nospam> wrote in message
>> >> > news:<u5**************@TK2MSFTNGP12.phx.gbl>...
>> >> >> You say that you get an error and you say that the code works?
>> >> >> It
>> >> >> can't
>> >> >> be
>> >> >> both. Your problem is this line:
>> >> >>
>> >> >> CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> >> >>
>> >> >> Because "strDa" is an actual String object and string object
>> >> >> can't
>> >> >> be
>> >> >> converted to DataAdapter objects. I know that you want to wind
>> >> >> up
>> >> >> with a
>> >> >> DataAdapter named whatever "strDa" is named, but this command
>> >> >> won't
>> >> >> do
>> >> >> it.
>> >> >>
>> >> >> Somewhere else in your code you must be creating an instance of
>> >> >> a
>> >> >> DataAdapter. What are you calling that one? Let's see that
>> >> >> code.
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Douglas Buchanan" <db*********@hotmail.com> wrote in message
>> >> >> news:72************************@posting.google.com ...
>> >> >> >I am using the following code instead of a very lengthly select
>> >> >> >case
>> >> >> > statement.
>> >> >> >
>> >> >> > (I have a lot of lookup tables in a settings form that are
>> >> >> > selected
>> >> >> > from a ListBox. The data adapters are given a similar name to
>> >> >> > the
>> >> >> > table. Rather than making a long Select Case that could become
>> >> >> > obsolete if lookup tables are added and the source table of
>> >> >> > the
>> >> >> > ListBox is edited I came up with this code.)
>> >> >> >
>> >> >> > This code works but of course it gives me build errors.
>> >> >> >
>> >> >> > Error:[Value of type 'String' cannot be converted to
>> >> >> > 'System.Data.SqlClient.SqlDataAdapter'.]
>> >> >> >
>> >> >> > === code snippit ===
>> >> >> > Private Sub lstMasterLists_Click(ByVal sender As Object, _
>> >> >> > ByVal e As System.EventArgs) Handles lstMasterLists.Click
>> >> >> >
>> >> >> > 'To populate the dgMasterLists with the proper table
>> >> >> > '1. use the returned TableName to make the DataAdapter name
>> >> >> > '2. Convert the string to the DataAdampter type
>> >> >> >
>> >> >> > 'Get the TableName from the selected item in the list box
>> >> >> > Dim strTbl As String
>> >> >> > strTbl = Me.lstMasterLists.SelectedItem.ToString
>> >> >> >
>> >> >> > 'Make the DataAdapter name from the table name
>> >> >> > Dim strDa As String
>> >> >> > strDa = strTbl.Remove(0, 5)
>> >> >> > strDa = strDa.Insert(0, "Da")
>> >> >> >
>> >> >> > CType(strDa, SqlDataAdapter).fill(DsMasterLists1, strTbl)
>> >> >> > DgMasterLists.SetDataBinding(DsMasterLists1, strTbl)
>> >> >> >
>> >> >> > End Sub
>> >> >> > ======
>> >> >> >
>> >> >> > The code works in debug mode.
>> >> >> > The exe works.
>> >> >> >
>> >> >> > Does anyone anticipate me having any problems with this after
>> >> >> > deployment?
>> >> >> > Does anyone suggest any other alturnatives?
>> >> >> > Does anyone suggest a way to stop the annoying build errors?

Jul 21 '05 #9

P: n/a
Scott,

I have looked again at your post, and this time reviewed your
suggested code.
Thank you. I apreciate the time and detail you provided. Could you
answer a few related questions that your approach brings to mind?

The approach you presented:
The simplicity of creating a DA and DS on-the-fly really impressed me.
I'm sure I will find many uses for this.

In the present scenario I need to both only and edit (Inserts,
Deletes, Updates) the tables. I belive that adds a few layers of
complexity. I think I'm up to it but will ask for a little input.

DataSets:
From seeing your on-the-fly DataSets I would suppose that generally I
don't need fully fledged datasets with xsd files, even if I am
updating, unless of-course I have multiple tables with relationships
involved. Is that right? (IOW keep it simple when you can.)

DataAdapters:
In the code you created an on-the-fly DataAdapter. Since my case needs
to be able to perform updates I understand on-the-fly DA's wouldn't
work.

This brings up another question; Iv'e seen fully functional
DataAdapters in projects constructed as code-your-own and as
drag-drop. What is the best or preferred practice? Drag-drop
DataAdapters seem pretty compact as compared with those written in
code. Would you say that code-your-own is the preferred method? If so
what are some of the advantages?

XSD files:
I believe they are required for multi-table datasets, however I have
seen some code where multiple-tables are built in code and added to
datasets. I have to research this more. If it is possible to construct
datasets without XSD isn't there disadvantages?

Below is code-behind as it now stands. (another is more of the same
but involves more tables).

This code incorporates some of your earlier suggestions
(SelectedIndexChanged...). I have abandoned my string manipulation,
but there are some other shortcuts. I am still using drag-drop CN & DA
and Wizard generateed DS, but I expect to code some of these as I move
forward. I invite your comment.

(Option Strict is set ON in the project)
=== Here is how my code now stands =====
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Form1
Inherits Form

''''' " Windows Form Designer generated code "

'Skip on load
Private SkipTabControl1IndexChangedFunctionality As Boolean = True
Private SkipLstMasterListsndexChangedFunctionality As Boolean =
True

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'LoadLstMasterLists() 'Don't load yet - user might add another
item.
FilldgMasterControlList()
End Sub

'Select a Tab
Private Sub TabControl1_SelectedIndexChanged(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) Handles
TabControl1.SelectedIndexChanged
'[ ] Check if current datagrid is dirty (Unsaved) before
moving to the next page
' Inform user with dialog

If SkipTabControl1IndexChangedFunctionality = True Then
'Skipped on load
'Now allow TabControl1_SelectedIndexChanged to run
SkipTabControl1IndexChangedFunctionality = False
Else
'Load appropriate ListControl
Select Case TabControl1.SelectedTab.Text
Case "Master Control List"
''Fill the DataGrid
FilldgMasterControlList()
Case "Master Lists"
''Fill the ListBox
LoadLstMasterLists()
Case Else
MessageBox.Show("Master Tab Control page does not
exist")
End Select
End If

End Sub

#Region " Fill and Update DataAdapter Coding Shortcuts "

Private Enum FillOrUpdate As Byte
'Used by DataGrid Loading or Updating
Fil '0
Upd '1
End Enum

'This sub runs both the Fill or the Update
Private Sub DaFillOrUpdateDsTbl(ByVal FilUpd As FillOrUpdate, _
ByVal Da As SqlDataAdapter, ByVal Ds As DataSet, ByVal Tbl As
String)

'How to call
'DaFillOrUpdateDsTbl(FilUpd, Da, Ds, Tbl)

'DataSet action Fill or Update
If FilUpd = FillOrUpdate.Fil Then
'Fill
Da.Fill(Ds, Tbl) ' Error here - says the connection is not
closed
ElseIf FilUpd = FillOrUpdate.Upd Then
'Update
Da.Update(Ds, Tbl)
End If
End Sub

#End Region

#Region " Master Control List "

Private Sub FilldgMasterControlList()
Me.Dalst00MasterOptionsList.Fill(Me.DsMasterContro lList1,
"lst00MasterOptionsList")
Me.dgMasterControlList.SetDataBinding(Me.DsMasterC ontrolList1,
"lst00MasterOptionsList")
End Sub

Private Sub btnMasterControlListUpdate_Click(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) Handles
btnMasterControlListUpdate.Click
Me.Dalst00MasterOptionsList.Update(Me.DsMasterCont rolList1,
"lst00MasterOptionsList")
End Sub

Private Sub btnMasterControlListAddRow_Click(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) Handles
btnMasterControlListAddRow.Click
'DataSet.Table.AddRow(FirstColumn, SecondColumn, ThirdColumn,
....)
Me.DsMasterControlList1.lst00MasterOptionsList.Add lst00MasterOptionsListRow(System.Guid.NewGuid().To String,
"", "")
End Sub

#End Region

#Region " Master Lists "

'Provide the GUID to the first column
Private Sub btnMasterListsNewRow_Click(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) Handles btnMasterListsNewRow.Click

'Get the name of the table which is the DataMember of the
DataGrid
Dim TableName As String
TableName = Me.DgMasterLists.DataMember.ToString

Select Case TableName
Case "lst01PrimaryOptions"
DsMasterLists1.lst01PrimaryOptions.Addlst01Primary OptionsRow(System.Guid.NewGuid().ToString,
"", "", "", 0, False)
Case "lst02SecondaryOptions"
DsMasterLists1.lst02SecondaryOptions.Addlst02Secon daryOptionsRow(System.Guid.NewGuid().ToString,
"", "", "", 0, False)
Case "lst03BusinessSettings"
DsMasterLists1.lst03BusinessSettings.Addlst03Busin essSettingsRow(System.Guid.NewGuid().ToString,
"", "", "", 0, False)
Case "lst04FixedOptions"
DsMasterLists1.lst04FixedOptions.Addlst04FixedOpti onsRow(System.Guid.NewGuid().ToString,
"", "", "", 0, False)
Case Else
'TableErrorMessage()
End Select

End Sub

'Fill the DataGrid when an item in the ListControl is selected
Private Sub lstMasterLists_SelectedIndexChanged(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) Handles
lstMasterLists.SelectedIndexChanged

If SkipLstMasterListsndexChangedFunctionality = True Then
'Skipped on load
'Now allow lstMasterLists_SelectedIndexChanged to run
SkipLstMasterListsndexChangedFunctionality = False
Else
'DataGrid - Fill
FillOrUpdateDgMasterLists(FillOrUpdate.Fil)
MessageBox.Show("Fill the list control")
End If

End Sub

'Update the DataGrid
Private Sub btnMasterListsUpdate_Click(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) Handles btnMasterListsUpdate.Click

'DataGrid - Update
FillOrUpdateDgMasterLists(FillOrUpdate.Upd)
End Sub

Protected Sub LoadLstMasterLists()

'Declare and instantiate Command
Dim cm As SqlCommand = New SqlCommand

'Text type command
cm.CommandType = CommandType.Text
cm.CommandText = "Select TableDescription, TableName FROM
lst00MasterOptionsList"
cm.CommandTimeout = 60
cm.Connection = cn

'Open the connection
cn.Open()

' Populates the list box using DataSource.
Dim arTablesList As New ArrayList

'Execute the command and retreive into DataReader
Dim dr As SqlDataReader
Dim i As Integer
Dim TableDescription As String
Dim TableName As String

'Do While
dr = cm.ExecuteReader(CommandBehavior.Default)

'Fill the list control
Do While dr.Read
TableDescription = dr("TableDescription").ToString
TableName = dr("TableName").ToString
'Why is this shown as undefined see TablesList.vb
arTablesList.Add(New TablesList(TableDescription,
TableName))
Loop

'Set the ListBox properties
lstMasterLists.DataSource = arTablesList
lstMasterLists.DisplayMember = "TableDesctiption"
lstMasterLists.ValueMember = "TableName"

'Close the connection
cn.Close()

End Sub

'This code is called for both Fill and Update
Private Sub FillOrUpdateDgMasterLists(ByVal FilUpd As
FillOrUpdate)

'Get the Value Member from the ListControl
Dim TableName As String
TableName = Me.lstMasterLists.SelectedValue.ToString

Select Case TableName
Case "lst01PrimaryOptions"
DaFillOrUpdateDsTbl(FilUpd, Dalst01PrimaryOptions,
DsMasterLists1, TableName)
Case "lst02SecondaryOptions"
DaFillOrUpdateDsTbl(FilUpd, Dalst02SecondaryOptions,
DsMasterLists1, TableName)
Case "lst03BusinessSettings"
DaFillOrUpdateDsTbl(FilUpd, Dalst03BusinessSettings,
DsMasterLists1, TableName)
Case "lst04FixedOptions"
DaFillOrUpdateDsTbl(FilUpd, Dalst04FixedOptions,
DsMasterLists1, TableName)
Case Else
'TableErrorMessage()
End Select

Me.DgMasterLists.SetDataBinding(DsMasterLists1, TableName)
End Sub

#End Region

End Class

#Region " Table List Class "
Public Class TablesList

Private myTableDesctiption As String
Private myTableName As String
'Private myInstruction As String

Public Sub New(ByVal strTableDescription As String, ByVal
strTableName As String)
MyBase.New()
Me.myTableDesctiption = strTableDescription
Me.myTableName = strTableName
End Sub

Public ReadOnly Property TableDesctiption() As String
Get
Return myTableDesctiption
End Get
End Property

Public ReadOnly Property TableName() As String
Get
Return myTableName
End Get
End Property

Public Overrides Function ToString() As String
Return Me.TableName
End Function
End Class
#End Region
=============================
=============================
=============================
=============================
=============================

FWIW below is my code-your-own DA I was practicing with.
(I have and XSD file for the DataSet and am using SQL Stored
procedures.)

(Option Strict is set ON in the project)
==== Code you own DA ====
Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.DataRowVersion
Imports System.Data.ParameterDirection
Imports System.Data.Common
Imports System.Data.Common.DataTableMapping

Public Class Form1
Inherits System.Windows.Forms.Form

''''' " Windows Form Designer generated code "

'Define and Istantiate Connection
Private Cn As New SqlConnection

'Define and Instantiate Commands
Private WithEvents cmSel As New SqlCommand
Private WithEvents cmIns As New SqlCommand
Private WithEvents cmUpd As New SqlCommand
Private WithEvents cmDel As New SqlCommand

'Define and Instantiate DataAdapter
Private WithEvents Da As New SqlDataAdapter

'Define and Instantiate DataSet
'Dim DsRefSource1 As lkp01RefSource.DsRefSource
Private WithEvents DsRefSource As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

'Connection String
Cn.ConnectionString = "workstation id=sedna;packet
size=4096;integrated security=SSPI;data source=SEDNA;persist security
info=False;initial catalog=BccAppDb"

'Set DataAdapter Commands
With Da
.SelectCommand = cmSel
.InsertCommand = cmIns
.UpdateCommand = cmUpd
.DeleteCommand = cmDel
End With

'Set SqlDataAdapter Table Mapptings (I don't think I need this
- Edits don't seem to affect anything.)
Da.TableMappings.AddRange(New DataTableMapping() { _
New DataTableMapping("Table", "lkp01RefSource", _
New DataColumnMapping() { _
New DataColumnMapping("RefSourceID", "RefSourceID"), _
New DataColumnMapping("RefSource", "RefSource"), _
New DataColumnMapping("ord", "ord"), _
New DataColumnMapping("hide", "hide")})})

'Select Command
With cmSel
.CommandText = "NewSelectCommandRefS"
.CommandType = CommandType.StoredProcedure
.Connection = Cn
.Parameters.Add(New SqlParameter("@RETURN_VALUE",
SqlDbType.Int, 4, ReturnValue, False, CType(0, Byte), CType(0, Byte),
"", Current, Nothing))
End With

'Insert Command
With cmIns
.CommandText = "NewInsertCommandRefS"
.CommandType = CommandType.StoredProcedure
.Connection = Cn
With cmIns.Parameters
.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int,
4, ReturnValue, False, CType(0, Byte), CType(0, Byte), "", Current,
Nothing))
.Add(New SqlParameter("@RefSourceID",
SqlDbType.TinyInt, 1, "RefSourceID"))
.Add(New SqlParameter("@RefSource", SqlDbType.VarChar,
25, "RefSource"))
.Add(New SqlParameter("@ord", SqlDbType.TinyInt, 1,
"ord"))
.Add(New SqlParameter("@hide", SqlDbType.Bit, 1,
"hide"))
End With
End With

'Update Command
With cmUpd
.CommandText = "NewUpdateCommandRefS"
.CommandType = CommandType.StoredProcedure
.Connection = Cn
With .Parameters
.Add(New SqlParameter("@RETURN_VaLUE", SqlDbType.Int,
4, ReturnValue, False, CType(0, Byte), CType(0, Byte), "", Current,
Nothing))
.Add(New SqlParameter("@RefSource", SqlDbType.VarChar,
25, "RefSource"))
.Add(New SqlParameter("@ord", SqlDbType.TinyInt, 1,
"ord"))
.Add(New SqlParameter("@hide", SqlDbType.Bit, 1,
"hide"))
.Add(New SqlParameter("@Original_RefSourceID",
SqlDbType.TinyInt, 1, Input, False, CType(0, Byte), CType(0, Byte),
"RefSourceID", Original, Nothing))
.Add(New SqlParameter("@Original_RefSource",
SqlDbType.VarChar, 25, Input, False, CType(0, Byte), CType(0, Byte),
"RefSource", Original, Nothing))
.Add(New SqlParameter("@Original_hide", SqlDbType.Bit,
1, Input, False, CType(0, Byte), CType(0, Byte), "hide", Original,
Nothing))
.Add(New SqlParameter("@Original_ord",
SqlDbType.TinyInt, 1, Input, False, CType(0, Byte), CType(0, Byte),
"ord", Original, Nothing))
.Add(New SqlParameter("@RefSourceID",
SqlDbType.TinyInt, 1, "RefSourceID"))
End With
End With

'Delete Command
With cmDel
.CommandText = "[NewDeleteCommandRefS]"
.CommandType = CommandType.StoredProcedure
.Connection = Cn
With .Parameters
.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int,
4, ReturnValue, False, CType(0, Byte), CType(0, Byte), "", Current,
Nothing))
.Add(New SqlParameter("@Original_RefSourceID",
SqlDbType.TinyInt, 1, Input, False, CType(0, Byte), CType(0, Byte),
"RefSourceID", Original, Nothing))
.Add(New SqlParameter("@Original_RefSource",
SqlDbType.VarChar, 25, Input, False, CType(0, Byte), CType(0, Byte),
"RefSource", Original, Nothing))
.Add(New SqlParameter("@Original_hide", SqlDbType.Bit,
1, Input, False, CType(0, Byte), CType(0, Byte), "hide", Original,
Nothing))
.Add(New SqlParameter("@Original_ord",
SqlDbType.TinyInt, 1, Input, False, CType(0, Byte), CType(0, Byte),
"ord", Original, Nothing))
'Add(
End With
End With

End Sub

Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnFill.Click

'DataAdapter
Da.Fill(DsRefSource, "lkp01RefSource")

'DataGrid Binding
DataGrid1.SetDataBinding(DsRefSource, "lkp01RefSource")

End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdate.Click
Da.Update(DsRefSource, "lkp01RegSource")

End Sub
End Class

=========================

I welcome any comment.

--Doug
Jul 21 '05 #10

P: n/a
Scott,

Thanks for MS Outlook Express tip and the instructions. I'll give it a try.

"Scott M." <s-***@nospam.nospam> wrote in message news:<eh**************@tk2msftngp13.phx.gbl>...
No problem Doug, thanks for clearing that up.

I use MS Outlook Express to read and write to many newsgroups. You probably
already have it and all you have to do is create a newsgroup account with
the following settings:

news server: msnews.microsoft.com
Create a user name of your choosing
Create a reply to email address (don't post your actual email address as the
spambots will find you) do something like Do************@something.com

Please let me know how my proposed solution works for you.

-Scott

Jul 21 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.