473,696 Members | 1,567 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

CType and annoying build errors

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.Sq lClient.SqlData Adapter'.]

=== code snippit ===
Private Sub lstMasterLists_ Click(ByVal sender As Object, _
ByVal e As System.EventArg s) 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.lstMasterLis ts.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(DsMasterL ists1, strTbl)
DgMasterLists.S etDataBinding(D sMasterLists1, 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
10 2333
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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
news:72******** *************** *@posting.googl e.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.Sq lClient.SqlData Adapter'.]

=== code snippit ===
Private Sub lstMasterLists_ Click(ByVal sender As Object, _
ByVal e As System.EventArg s) 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.lstMasterLis ts.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(DsMasterL ists1, strTbl)
DgMasterLists.S etDataBinding(D sMasterLists1, 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
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...

lst01PrimaryOpt ions
lst02SecondaryO ptions
lst03BusinsessS ettings
lst04FixedOptio ns
....

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

DaPrimaryOption s
DaSecondaryOpti ons
DaBusinsessSett ings
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.F ill(DsSelection List1, "lkp01RefSource ")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp01RefSource ")
Case "lkp02GrpCatego ry"
da02GrpCategory .Fill(DsSelecti onList1, "lkp02GrpCatego ry")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp02GrpCatego ry")
Case "lkp03PrgmObjec tive"
da03PrgmObjecti ve.Fill(DsSelec tionList1, "lkp03PrgmObjec tive")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp03PrgmObjec tive")
Case "lkp06JobTi tle"
da06JobTitle.Fi ll(DsSelectionL ist1, "lkp06JobTitle" )
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp06JobTitle" )
Case "lkp07Qualifica tion"
da07Qualificati on.Fill(DsSelec tionList1, "lkp07Qualifica tion")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp07Qualifica tion")
Case "lkp08DayOfWeek "
da08DayOfWeek.F ill(DsSelection List1, "lkp08DayOfWeek ")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp08DayOfWeek ")
Case "lkp09MealT ype"
da09MealType.Fi ll(DsSelectionL ist1, "lkp09MealType" )
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp09MealType" )
Case "lkp10MerchandT ype"
da10MerchandTyp e.Fill(DsSelect ionList1, "lkp10MerchandT ype")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp10MerchandT ype")
Case "lkp11CommResou rce"
da11CommResourc e.Fill(DsSelect ionList1, "lkp11CommResou rce")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp11CommResou rce")
Case "lkp12Telephony Device"
da12TelephonyDe vice.Fill(DsSel ectionList1, "lkp12Telephony Device")
DgMasterLists.S etDataBinding(D sSelectionList1 ,
"lkp12Telephony Device")
Case "lkp13WwwTy pe"
da13WwwType.Fil l(DsSelectionLi st1, "lkp13WwwTy pe")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp13WwwTy pe")
Case "lkp14ModeOfCon tact"
da14ModeOfConta ct.Fill(DsSelec tionList1, "lkp14ModeOfCon tact")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp14ModeOfCon tact")
Case "lkp15MsgTo pic"
da15MsgTopic.Fi ll(DsSelectionL ist1, "lkp15MsgTopic" )
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp15MsgTopic" )
Case "lkp16ScheduleT ype"
da16ScheduleTyp e.Fill(DsSelect ionList1, "lkp16ScheduleT ype")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp16ScheduleT ype")
Case "lkp17WeightGro up"
da17WeightGroup .Fill(DsSelecti onList1, "lkp17WeightGro up")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp17WeightGro up")
Case "lkp18ProgramCa tegory"
da18ProgramCate gory.Fill(DsSel ectionList1, "lkp18ProgramCa tegory")
DgMasterLists.S etDataBinding(D sSelectionList1 ,
"lkp18ProgramCa tegory")
Case "lkp19Eleme nt"
da19Element.Fil l(DsSelectionLi st1, "lkp19Eleme nt")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp19Eleme nt")
Case "enm1FoodAllerg y"
daFoodAllergy.F ill(DsSelection List1, "enm1FoodAllerg y")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm1FoodAllerg y")
Case "enm2EnvironAll ergy"
daEnvironAllerg y.Fill(DsSelect ionList1, "enm2EnvironAll ergy")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm2EnvironAll ergy")
Case "enm3MedicalAll ergy"
daMedicalAllerg y.Fill(DsSelect ionList1, "enm3MedicalAll ergy")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm3MedicalAll ergy")
Case "enm4MedConcern "
daMedConcern.Fi ll(DsSelectionL ist1, "enm4MedConcern ")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm4MedConcern ")
Case "enm5ActivityRe quest"
daActivityReque st.Fill(DsSelec tionList1, "enm5ActivityRe quest")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm5ActivityRe quest")
Case Else
TableErrorMessa ge()
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(DsMasterL ists1, strTbl)
DgMasterLists.S etDataBinding(D sMasterLists1, 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.nosp am> wrote in message news:<u5******* *******@TK2MSFT NGP12.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
news:72******** *************** *@posting.googl e.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.Sq lClient.SqlData Adapter'.]

=== code snippit ===
Private Sub lstMasterLists_ Click(ByVal sender As Object, _
ByVal e As System.EventArg s) 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.lstMasterLis ts.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(DsMasterL ists1, strTbl)
DgMasterLists.S etDataBinding(D sMasterLists1, 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
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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
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...

lst01PrimaryOpt ions
lst02SecondaryO ptions
lst03BusinsessS ettings
lst04FixedOptio ns
...

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

DaPrimaryOption s
DaSecondaryOpti ons
DaBusinsessSett ings
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.F ill(DsSelection List1, "lkp01RefSource ")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp01RefSource ")
Case "lkp02GrpCatego ry"
da02GrpCategory .Fill(DsSelecti onList1, "lkp02GrpCatego ry")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp02GrpCatego ry")
Case "lkp03PrgmObjec tive"
da03PrgmObjecti ve.Fill(DsSelec tionList1, "lkp03PrgmObjec tive")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp03PrgmObjec tive")
Case "lkp06JobTi tle"
da06JobTitle.Fi ll(DsSelectionL ist1, "lkp06JobTitle" )
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp06JobTitle" )
Case "lkp07Qualifica tion"
da07Qualificati on.Fill(DsSelec tionList1, "lkp07Qualifica tion")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp07Qualifica tion")
Case "lkp08DayOfWeek "
da08DayOfWeek.F ill(DsSelection List1, "lkp08DayOfWeek ")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp08DayOfWeek ")
Case "lkp09MealT ype"
da09MealType.Fi ll(DsSelectionL ist1, "lkp09MealType" )
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp09MealType" )
Case "lkp10MerchandT ype"
da10MerchandTyp e.Fill(DsSelect ionList1, "lkp10MerchandT ype")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp10MerchandT ype")
Case "lkp11CommResou rce"
da11CommResourc e.Fill(DsSelect ionList1, "lkp11CommResou rce")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp11CommResou rce")
Case "lkp12Telephony Device"
da12TelephonyDe vice.Fill(DsSel ectionList1, "lkp12Telephony Device")
DgMasterLists.S etDataBinding(D sSelectionList1 ,
"lkp12Telephony Device")
Case "lkp13WwwTy pe"
da13WwwType.Fil l(DsSelectionLi st1, "lkp13WwwTy pe")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp13WwwTy pe")
Case "lkp14ModeOfCon tact"
da14ModeOfConta ct.Fill(DsSelec tionList1, "lkp14ModeOfCon tact")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp14ModeOfCon tact")
Case "lkp15MsgTo pic"
da15MsgTopic.Fi ll(DsSelectionL ist1, "lkp15MsgTopic" )
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp15MsgTopic" )
Case "lkp16ScheduleT ype"
da16ScheduleTyp e.Fill(DsSelect ionList1, "lkp16ScheduleT ype")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp16ScheduleT ype")
Case "lkp17WeightGro up"
da17WeightGroup .Fill(DsSelecti onList1, "lkp17WeightGro up")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp17WeightGro up")
Case "lkp18ProgramCa tegory"
da18ProgramCate gory.Fill(DsSel ectionList1, "lkp18ProgramCa tegory")
DgMasterLists.S etDataBinding(D sSelectionList1 ,
"lkp18ProgramCa tegory")
Case "lkp19Eleme nt"
da19Element.Fil l(DsSelectionLi st1, "lkp19Eleme nt")
DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp19Eleme nt")
Case "enm1FoodAllerg y"
daFoodAllergy.F ill(DsSelection List1, "enm1FoodAllerg y")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm1FoodAllerg y")
Case "enm2EnvironAll ergy"
daEnvironAllerg y.Fill(DsSelect ionList1, "enm2EnvironAll ergy")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm2EnvironAll ergy")
Case "enm3MedicalAll ergy"
daMedicalAllerg y.Fill(DsSelect ionList1, "enm3MedicalAll ergy")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm3MedicalAll ergy")
Case "enm4MedConcern "
daMedConcern.Fi ll(DsSelectionL ist1, "enm4MedConcern ")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm4MedConcern ")
Case "enm5ActivityRe quest"
daActivityReque st.Fill(DsSelec tionList1, "enm5ActivityRe quest")
DgMasterLists.S etDataBinding(D sSelectionList1 , "enm5ActivityRe quest")
Case Else
TableErrorMessa ge()
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(DsMasterL ists1, strTbl)
DgMasterLists.S etDataBinding(D sMasterLists1, 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.nosp am> wrote in message
news:<u5******* *******@TK2MSFT NGP12.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
news:72******** *************** *@posting.googl e.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.Sq lClient.SqlData Adapter'.]
>
> === code snippit ===
> Private Sub lstMasterLists_ Click(ByVal sender As Object, _
> ByVal e As System.EventArg s) 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.lstMasterLis ts.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(DsMasterL ists1, strTbl)
> DgMasterLists.S etDataBinding(D sMasterLists1, 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
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.nosp am> wrote in message news:<Of******* *******@TK2MSFT NGP09.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.Applicati on.Run(New NortwindEmploye e)
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 DsMasterLists1a nd 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 = "workstatio n id=SEDNA;packet size=4096;" & _
"integrated security=SSPI;d ata
source=(local); " & _
"persist security info=False;init ial
catalog=Northwi nd"

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

Private con As New SqlConnection(c onStr)
Private da As New SqlDataAdapter( selSQL, con)
Private ds As New DataSet
--------------------------------------------------------------------------------
Private Sub btnPush_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnPush.Click
'Fill DataAdapter
da.Fill(ds, "Employees" )

'Bind DataGrid
dg.SetDataBindi ng(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 SelectedIndexCh anged event handler of a ComboBox so
that you only run this when it's needed (more efficient).

Private Sub lstMasterLists_ SelectedIndexCh anged _
(ByVal sender As System.Object, ByVal e As System.EventArg s) _
Handles lstMasterLists. SelectedIndexCh anged

'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.SqlDa taAdapter(selSQ L, con)
Dim newDS As New DataSet
newDA.Fill(newD S, strTbl)
dg.SetDataBindi ng(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_ SelectedIndexCh anged _
(ByVal sender As System.Object, ByVal e As System.EventArg s) _
Handles lstMasterLists. SelectedIndexCh anged

'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.SqlDa taAdapter(selSQ L, con)
Dim newDS As New DataSet
newDA.Fill(newD S, strTbl)
dg.SetDataBindi ng(newDS, strTbl)
End Sub
I have tested all the code I have given here and it works.
=============== =============== =============== =============== =
"Douglas Buchanan" <db*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
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.Sql Client
Imports System.Windows. Forms

Public Class NortwindEmploye e
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.Applicati on.Run(New NortwindEmploye e)

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.HeaderForeCo lor = System.Drawing. SystemColors.Co ntrolText
dg.Location = New System.Drawing. Point(8, 40)
dg.Name = "dg"
dg.Size = New System.Drawing. Size(280, 160)
dg.TabIndex = 1

'ConnectionStri ng
cn.ConnectionSt ring = "workstatio n id=SEDNA;packet
size=4096;integ rated security=SSPI;d ata source=(local); persist
security info=False;init ial catalog=Northwi nd"

'DataAdapter Commands
da.SelectComman d = cmSel

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

End Sub

Private Sub InitializeCompo nent()

'Form1
AutoScaleBaseSi ze = New System.Drawing. Size(5, 13)
ClientSize = New System.Drawing. Size(292, 206)
Name = "NortwindEmploy ee"
Text = "NortwindEmploy ee"

End Sub

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

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

'Bind DataGrid
'dg.SetDataBind ing(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.nosp am> wrote in message
news:<eO******* ******@TK2MSFTN GP11.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
> 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...
>
> lst01PrimaryOpt ions
> lst02SecondaryO ptions
> lst03BusinsessS ettings
> lst04FixedOptio ns
> ...
>
> When I made my DataAdapters I gave them these names...
>
> DaPrimaryOption s
> DaSecondaryOpti ons
> DaBusinsessSett ings
> 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.F ill(DsSelection List1, "lkp01RefSource ")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp01RefSource ")
> Case "lkp02GrpCatego ry"
> da02GrpCategory .Fill(DsSelecti onList1, "lkp02GrpCatego ry")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp02GrpCatego ry")
> Case "lkp03PrgmObjec tive"
> da03PrgmObjecti ve.Fill(DsSelec tionList1, "lkp03PrgmObjec tive")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp03PrgmObjec tive")
> Case "lkp06JobTi tle"
> da06JobTitle.Fi ll(DsSelectionL ist1, "lkp06JobTitle" )
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp06JobTitle" )
> Case "lkp07Qualifica tion"
> da07Qualificati on.Fill(DsSelec tionList1, "lkp07Qualifica tion")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp07Qualifica tion")
> Case "lkp08DayOfWeek "
> da08DayOfWeek.F ill(DsSelection List1, "lkp08DayOfWeek ")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp08DayOfWeek ")
> Case "lkp09MealT ype"
> da09MealType.Fi ll(DsSelectionL ist1, "lkp09MealType" )
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp09MealType" )
> Case "lkp10MerchandT ype"
> da10MerchandTyp e.Fill(DsSelect ionList1, "lkp10MerchandT ype")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp10MerchandT ype")
> Case "lkp11CommResou rce"
> da11CommResourc e.Fill(DsSelect ionList1, "lkp11CommResou rce")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp11CommResou rce")
> Case "lkp12Telephony Device"
> da12TelephonyDe vice.Fill(DsSel ectionList1, "lkp12Telephony Device")
> DgMasterLists.S etDataBinding(D sSelectionList1 ,
> "lkp12Telephony Device")
> Case "lkp13WwwTy pe"
> da13WwwType.Fil l(DsSelectionLi st1, "lkp13WwwTy pe")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp13WwwTy pe")
> Case "lkp14ModeOfCon tact"
> da14ModeOfConta ct.Fill(DsSelec tionList1, "lkp14ModeOfCon tact")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp14ModeOfCon tact")
> Case "lkp15MsgTo pic"
> da15MsgTopic.Fi ll(DsSelectionL ist1, "lkp15MsgTopic" )
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp15MsgTopic" )
> Case "lkp16ScheduleT ype"
> da16ScheduleTyp e.Fill(DsSelect ionList1, "lkp16ScheduleT ype")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp16ScheduleT ype")
> Case "lkp17WeightGro up"
> da17WeightGroup .Fill(DsSelecti onList1, "lkp17WeightGro up")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp17WeightGro up")
> Case "lkp18ProgramCa tegory"
> da18ProgramCate gory.Fill(DsSel ectionList1, "lkp18ProgramCa tegory")
> DgMasterLists.S etDataBinding(D sSelectionList1 ,
> "lkp18ProgramCa tegory")
> Case "lkp19Eleme nt"
> da19Element.Fil l(DsSelectionLi st1, "lkp19Eleme nt")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp19Eleme nt")
> Case "enm1FoodAllerg y"
> daFoodAllergy.F ill(DsSelection List1, "enm1FoodAllerg y")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "enm1FoodAllerg y")
> Case "enm2EnvironAll ergy"
> daEnvironAllerg y.Fill(DsSelect ionList1, "enm2EnvironAll ergy")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "enm2EnvironAll ergy")
> Case "enm3MedicalAll ergy"
> daMedicalAllerg y.Fill(DsSelect ionList1, "enm3MedicalAll ergy")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "enm3MedicalAll ergy")
> Case "enm4MedConcern "
> daMedConcern.Fi ll(DsSelectionL ist1, "enm4MedConcern ")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "enm4MedConcern ")
> Case "enm5ActivityRe quest"
> daActivityReque st.Fill(DsSelec tionList1, "enm5ActivityRe quest")
> DgMasterLists.S etDataBinding(D sSelectionList1 , "enm5ActivityRe quest")
> Case Else
> TableErrorMessa ge()
> 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(DsMasterL ists1, strTbl)
> DgMasterLists.S etDataBinding(D sMasterLists1, 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.nosp am> wrote in message
> news:<u5******* *******@TK2MSFT NGP12.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
>> news:72******** *************** *@posting.googl e.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.Sq lClient.SqlData Adapter'.]
>> >
>> > === code snippit ===
>> > Private Sub lstMasterLists_ Click(ByVal sender As Object, _
>> > ByVal e As System.EventArg s) 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.lstMasterLis ts.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(DsMasterL ists1, strTbl)
>> > DgMasterLists.S etDataBinding(D sMasterLists1, 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
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*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
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.nosp am> wrote in message
news:<Of******* *******@TK2MSFT NGP09.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.Applicati on.Run(New NortwindEmploye e)
> 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 DsMasterLists1a nd 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 = "workstatio n id=SEDNA;packet size=4096;" & _
"integrated security=SSPI;d ata
source=(local); " & _
"persist security
info=False;init ial
catalog=Northwi nd"

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

Private con As New SqlConnection(c onStr)
Private da As New SqlDataAdapter( selSQL, con)
Private ds As New DataSet
--------------------------------------------------------------------------------
Private Sub btnPush_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnPush.Click
'Fill DataAdapter
da.Fill(ds, "Employees" )

'Bind DataGrid
dg.SetDataBindi ng(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 SelectedIndexCh anged event handler of a ComboBox
so
that you only run this when it's needed (more efficient).

Private Sub lstMasterLists_ SelectedIndexCh anged _
(ByVal sender As System.Object, ByVal e As System.EventArg s) _
Handles lstMasterLists. SelectedIndexCh anged

'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.SqlDa taAdapter(selSQ L, con)
Dim newDS As New DataSet
newDA.Fill(newD S, strTbl)
dg.SetDataBindi ng(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_ SelectedIndexCh anged _
(ByVal sender As System.Object, ByVal e As System.EventArg s) _
Handles lstMasterLists. SelectedIndexCh anged

'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.SqlDa taAdapter(selSQ L, con)
Dim newDS As New DataSet
newDA.Fill(newD S, strTbl)
dg.SetDataBindi ng(newDS, strTbl)
End Sub
I have tested all the code I have given here and it works.
=============== =============== =============== =============== =
"Douglas Buchanan" <db*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
> 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.Sql Client
> Imports System.Windows. Forms
>
> Public Class NortwindEmploye e
> 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.Applicati on.Run(New NortwindEmploye e)
>
> 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.HeaderForeCo lor = System.Drawing. SystemColors.Co ntrolText
> dg.Location = New System.Drawing. Point(8, 40)
> dg.Name = "dg"
> dg.Size = New System.Drawing. Size(280, 160)
> dg.TabIndex = 1
>
> 'ConnectionStri ng
> cn.ConnectionSt ring = "workstatio n id=SEDNA;packet
> size=4096;integ rated security=SSPI;d ata source=(local); persist
> security info=False;init ial catalog=Northwi nd"
>
> 'DataAdapter Commands
> da.SelectComman d = cmSel
>
> 'Select Command
> cmSel.CommandTe xt = "SELECT EmployeeID, LastName, FirstName,
> Title, TitleOfCourtesy , BirthDate, HireDa" & _
> "te, Address, City, Region, PostalCode, Country, HomePhone,
> Extension, Photo, Not" & _
> "es, ReportsTo, PhotoPath FROM Employees"
> cmSel.Connectio n = cn
>
> End Sub
>
> Private Sub InitializeCompo nent()
>
> 'Form1
> AutoScaleBaseSi ze = New System.Drawing. Size(5, 13)
> ClientSize = New System.Drawing. Size(292, 206)
> Name = "NortwindEmploy ee"
> Text = "NortwindEmploy ee"
>
> End Sub
>
> Public Sub btn_Click(ByVal sender As System.Object, ByVal e As
> System.EventArg s)
>
> 'Fill DataAdapter
> 'da.Fill(Me.ds, "Employees" )
> CType("da", SqlDataAdapter) .Fill(Me.ds, "Employees" )
>
> 'Bind DataGrid
> 'dg.SetDataBind ing(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.nosp am> wrote in message
> news:<eO******* ******@TK2MSFTN GP11.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
>> news:72******** *************** ***@posting.goo gle.com...
>> > 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...
>> >
>> > lst01PrimaryOpt ions
>> > lst02SecondaryO ptions
>> > lst03BusinsessS ettings
>> > lst04FixedOptio ns
>> > ...
>> >
>> > When I made my DataAdapters I gave them these names...
>> >
>> > DaPrimaryOption s
>> > DaSecondaryOpti ons
>> > DaBusinsessSett ings
>> > 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.F ill(DsSelection List1, "lkp01RefSource ")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp01RefSource ")
>> > Case "lkp02GrpCatego ry"
>> > da02GrpCategory .Fill(DsSelecti onList1, "lkp02GrpCatego ry")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp02GrpCatego ry")
>> > Case "lkp03PrgmObjec tive"
>> > da03PrgmObjecti ve.Fill(DsSelec tionList1, "lkp03PrgmObjec tive")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp03PrgmObjec tive")
>> > Case "lkp06JobTi tle"
>> > da06JobTitle.Fi ll(DsSelectionL ist1, "lkp06JobTitle" )
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp06JobTitle" )
>> > Case "lkp07Qualifica tion"
>> > da07Qualificati on.Fill(DsSelec tionList1, "lkp07Qualifica tion")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp07Qualifica tion")
>> > Case "lkp08DayOfWeek "
>> > da08DayOfWeek.F ill(DsSelection List1, "lkp08DayOfWeek ")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp08DayOfWeek ")
>> > Case "lkp09MealT ype"
>> > da09MealType.Fi ll(DsSelectionL ist1, "lkp09MealType" )
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp09MealType" )
>> > Case "lkp10MerchandT ype"
>> > da10MerchandTyp e.Fill(DsSelect ionList1, "lkp10MerchandT ype")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp10MerchandT ype")
>> > Case "lkp11CommResou rce"
>> > da11CommResourc e.Fill(DsSelect ionList1, "lkp11CommResou rce")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp11CommResou rce")
>> > Case "lkp12Telephony Device"
>> > da12TelephonyDe vice.Fill(DsSel ectionList1, "lkp12Telephony Device")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> > "lkp12Telephony Device")
>> > Case "lkp13WwwTy pe"
>> > da13WwwType.Fil l(DsSelectionLi st1, "lkp13WwwTy pe")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp13WwwTy pe")
>> > Case "lkp14ModeOfCon tact"
>> > da14ModeOfConta ct.Fill(DsSelec tionList1, "lkp14ModeOfCon tact")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp14ModeOfCon tact")
>> > Case "lkp15MsgTo pic"
>> > da15MsgTopic.Fi ll(DsSelectionL ist1, "lkp15MsgTopic" )
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp15MsgTopic" )
>> > Case "lkp16ScheduleT ype"
>> > da16ScheduleTyp e.Fill(DsSelect ionList1, "lkp16ScheduleT ype")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp16ScheduleT ype")
>> > Case "lkp17WeightGro up"
>> > da17WeightGroup .Fill(DsSelecti onList1, "lkp17WeightGro up")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp17WeightGro up")
>> > Case "lkp18ProgramCa tegory"
>> > da18ProgramCate gory.Fill(DsSel ectionList1, "lkp18ProgramCa tegory")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> > "lkp18ProgramCa tegory")
>> > Case "lkp19Eleme nt"
>> > da19Element.Fil l(DsSelectionLi st1, "lkp19Eleme nt")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp19Eleme nt")
>> > Case "enm1FoodAllerg y"
>> > daFoodAllergy.F ill(DsSelection List1, "enm1FoodAllerg y")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm1FoodAllerg y")
>> > Case "enm2EnvironAll ergy"
>> > daEnvironAllerg y.Fill(DsSelect ionList1, "enm2EnvironAll ergy")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm2EnvironAll ergy")
>> > Case "enm3MedicalAll ergy"
>> > daMedicalAllerg y.Fill(DsSelect ionList1, "enm3MedicalAll ergy")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm3MedicalAll ergy")
>> > Case "enm4MedConcern "
>> > daMedConcern.Fi ll(DsSelectionL ist1, "enm4MedConcern ")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm4MedConcern ")
>> > Case "enm5ActivityRe quest"
>> > daActivityReque st.Fill(DsSelec tionList1, "enm5ActivityRe quest")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> > "enm5ActivityRe quest")
>> > Case Else
>> > TableErrorMessa ge()
>> > 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(DsMasterL ists1, strTbl)
>> > DgMasterLists.S etDataBinding(D sMasterLists1, 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.nosp am> wrote in message
>> > news:<u5******* *******@TK2MSFT NGP12.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
>> >> news:72******** *************** *@posting.googl e.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.Sq lClient.SqlData Adapter'.]
>> >> >
>> >> > === code snippit ===
>> >> > Private Sub lstMasterLists_ Click(ByVal sender As Object, _
>> >> > ByVal e As System.EventArg s) 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.lstMasterLis ts.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(DsMasterL ists1, strTbl)
>> >> > DgMasterLists.S etDataBinding(D sMasterLists1, 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
[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
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.nosp am> wrote in message news:<uy******* *******@TK2MSFT NGP10.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*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
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.nosp am> wrote in message
news:<Of******* *******@TK2MSFT NGP09.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.Applicati on.Run(New NortwindEmploye e)
> 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 DsMasterLists1a nd 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 = "workstatio n id=SEDNA;packet size=4096;" & _
"integrated security=SSPI;d ata
source=(local); " & _
"persist security
info=False;init ial
catalog=Northwi nd"

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

Private con As New SqlConnection(c onStr)
Private da As New SqlDataAdapter( selSQL, con)
Private ds As New DataSet
--------------------------------------------------------------------------------
Private Sub btnPush_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnPush.Click
'Fill DataAdapter
da.Fill(ds, "Employees" )

'Bind DataGrid
dg.SetDataBindi ng(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 SelectedIndexCh anged event handler of a ComboBox
so
that you only run this when it's needed (more efficient).

Private Sub lstMasterLists_ SelectedIndexCh anged _
(ByVal sender As System.Object, ByVal e As System.EventArg s) _
Handles lstMasterLists. SelectedIndexCh anged

'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.SqlDa taAdapter(selSQ L, con)
Dim newDS As New DataSet
newDA.Fill(newD S, strTbl)
dg.SetDataBindi ng(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_ SelectedIndexCh anged _
(ByVal sender As System.Object, ByVal e As System.EventArg s) _
Handles lstMasterLists. SelectedIndexCh anged

'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.SqlDa taAdapter(selSQ L, con)
Dim newDS As New DataSet
newDA.Fill(newD S, strTbl)
dg.SetDataBindi ng(newDS, strTbl)
End Sub
I have tested all the code I have given here and it works.
=============== =============== =============== =============== =
"Douglas Buchanan" <db*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
> 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.Sql Client
> Imports System.Windows. Forms
>
> Public Class NortwindEmploye e
> 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.Applicati on.Run(New NortwindEmploye e)
>
> 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.HeaderForeCo lor = System.Drawing. SystemColors.Co ntrolText
> dg.Location = New System.Drawing. Point(8, 40)
> dg.Name = "dg"
> dg.Size = New System.Drawing. Size(280, 160)
> dg.TabIndex = 1
>
> 'ConnectionStri ng
> cn.ConnectionSt ring = "workstatio n id=SEDNA;packet
> size=4096;integ rated security=SSPI;d ata source=(local); persist
> security info=False;init ial catalog=Northwi nd"
>
> 'DataAdapter Commands
> da.SelectComman d = cmSel
>
> 'Select Command
> cmSel.CommandTe xt = "SELECT EmployeeID, LastName, FirstName,
> Title, TitleOfCourtesy , BirthDate, HireDa" & _
> "te, Address, City, Region, PostalCode, Country, HomePhone,
> Extension, Photo, Not" & _
> "es, ReportsTo, PhotoPath FROM Employees"
> cmSel.Connectio n = cn
>
> End Sub
>
> Private Sub InitializeCompo nent()
>
> 'Form1
> AutoScaleBaseSi ze = New System.Drawing. Size(5, 13)
> ClientSize = New System.Drawing. Size(292, 206)
> Name = "NortwindEmploy ee"
> Text = "NortwindEmploy ee"
>
> End Sub
>
> Public Sub btn_Click(ByVal sender As System.Object, ByVal e As
> System.EventArg s)
>
> 'Fill DataAdapter
> 'da.Fill(Me.ds, "Employees" )
> CType("da", SqlDataAdapter) .Fill(Me.ds, "Employees" )
>
> 'Bind DataGrid
> 'dg.SetDataBind ing(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.nosp am> wrote in message
> news:<eO******* ******@TK2MSFTN GP11.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
>> news:72******** *************** ***@posting.goo gle.com...
>> > 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...
>> >
>> > lst01PrimaryOpt ions
>> > lst02SecondaryO ptions
>> > lst03BusinsessS ettings
>> > lst04FixedOptio ns
>> > ...
>> >
>> > When I made my DataAdapters I gave them these names...
>> >
>> > DaPrimaryOption s
>> > DaSecondaryOpti ons
>> > DaBusinsessSett ings
>> > 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.F ill(DsSelection List1, "lkp01RefSource ")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp01RefSource ")
>> > Case "lkp02GrpCatego ry"
>> > da02GrpCategory .Fill(DsSelecti onList1, "lkp02GrpCatego ry")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp02GrpCatego ry")
>> > Case "lkp03PrgmObjec tive"
>> > da03PrgmObjecti ve.Fill(DsSelec tionList1, "lkp03PrgmObjec tive")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp03PrgmObjec tive")
>> > Case "lkp06JobTi tle"
>> > da06JobTitle.Fi ll(DsSelectionL ist1, "lkp06JobTitle" )
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp06JobTitle" )
>> > Case "lkp07Qualifica tion"
>> > da07Qualificati on.Fill(DsSelec tionList1, "lkp07Qualifica tion")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp07Qualifica tion")
>> > Case "lkp08DayOfWeek "
>> > da08DayOfWeek.F ill(DsSelection List1, "lkp08DayOfWeek ")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp08DayOfWeek ")
>> > Case "lkp09MealT ype"
>> > da09MealType.Fi ll(DsSelectionL ist1, "lkp09MealType" )
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp09MealType" )
>> > Case "lkp10MerchandT ype"
>> > da10MerchandTyp e.Fill(DsSelect ionList1, "lkp10MerchandT ype")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp10MerchandT ype")
>> > Case "lkp11CommResou rce"
>> > da11CommResourc e.Fill(DsSelect ionList1, "lkp11CommResou rce")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp11CommResou rce")
>> > Case "lkp12Telephony Device"
>> > da12TelephonyDe vice.Fill(DsSel ectionList1, "lkp12Telephony Device")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> > "lkp12Telephony Device")
>> > Case "lkp13WwwTy pe"
>> > da13WwwType.Fil l(DsSelectionLi st1, "lkp13WwwTy pe")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp13WwwTy pe")
>> > Case "lkp14ModeOfCon tact"
>> > da14ModeOfConta ct.Fill(DsSelec tionList1, "lkp14ModeOfCon tact")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp14ModeOfCon tact")
>> > Case "lkp15MsgTo pic"
>> > da15MsgTopic.Fi ll(DsSelectionL ist1, "lkp15MsgTopic" )
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp15MsgTopic" )
>> > Case "lkp16ScheduleT ype"
>> > da16ScheduleTyp e.Fill(DsSelect ionList1, "lkp16ScheduleT ype")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp16ScheduleT ype")
>> > Case "lkp17WeightGro up"
>> > da17WeightGroup .Fill(DsSelecti onList1, "lkp17WeightGro up")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp17WeightGro up")
>> > Case "lkp18ProgramCa tegory"
>> > da18ProgramCate gory.Fill(DsSel ectionList1, "lkp18ProgramCa tegory")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> > "lkp18ProgramCa tegory")
>> > Case "lkp19Eleme nt"
>> > da19Element.Fil l(DsSelectionLi st1, "lkp19Eleme nt")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp19Eleme nt")
>> > Case "enm1FoodAllerg y"
>> > daFoodAllergy.F ill(DsSelection List1, "enm1FoodAllerg y")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm1FoodAllerg y")
>> > Case "enm2EnvironAll ergy"
>> > daEnvironAllerg y.Fill(DsSelect ionList1, "enm2EnvironAll ergy")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm2EnvironAll ergy")
>> > Case "enm3MedicalAll ergy"
>> > daMedicalAllerg y.Fill(DsSelect ionList1, "enm3MedicalAll ergy")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm3MedicalAll ergy")
>> > Case "enm4MedConcern "
>> > daMedConcern.Fi ll(DsSelectionL ist1, "enm4MedConcern ")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm4MedConcern ")
>> > Case "enm5ActivityRe quest"
>> > daActivityReque st.Fill(DsSelec tionList1, "enm5ActivityRe quest")
>> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> > "enm5ActivityRe quest")
>> > Case Else
>> > TableErrorMessa ge()
>> > 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(DsMasterL ists1, strTbl)
>> > DgMasterLists.S etDataBinding(D sMasterLists1, 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.nosp am> wrote in message
>> > news:<u5******* *******@TK2MSFT NGP12.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
>> >> news:72******** *************** *@posting.googl e.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.Sq lClient.SqlData Adapter'.]
>> >> >
>> >> > === code snippit ===
>> >> > Private Sub lstMasterLists_ Click(ByVal sender As Object, _
>> >> > ByVal e As System.EventArg s) 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.lstMasterLis ts.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(DsMasterL ists1, strTbl)
>> >> > DgMasterLists.S etDataBinding(D sMasterLists1, 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
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.microsof t.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*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
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.nosp am> wrote in message
news:<uy******* *******@TK2MSFT NGP10.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*********@ho tmail.com> wrote in message
news:72******** *************** ***@posting.goo gle.com...
> 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.nosp am> wrote in message
> news:<Of******* *******@TK2MSFT NGP09.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.Applicati on.Run(New NortwindEmploye e)
>> > 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 DsMasterLists1a nd 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 = "workstatio n id=SEDNA;packet size=4096;" &
>> _
>> "integrated security=SSPI;d ata
>> source=(local); " & _
>> "persist security
>> info=False;init ial
>> catalog=Northwi nd"
>>
>> Private selSQL As String = "SELECT EmployeeID, LastName, FirstName,
>> Title, "
>> & _
>> "TitleOfCourtes y,
>> BirthDate,
>> HireDate, Address, City, Region, " & _
>> "PostalCode , Country,
>> HomePhone,
>> Extension, Photo, Notes, " & _
>> "ReportsTo, PhotoPath FROM
>> Employees"
>>
>> Private con As New SqlConnection(c onStr)
>> Private da As New SqlDataAdapter( selSQL, con)
>> Private ds As New DataSet
>> --------------------------------------------------------------------------------
>> Private Sub btnPush_Click(B yVal sender As System.Object, ByVal e As
>> System.EventArg s) Handles btnPush.Click
>> 'Fill DataAdapter
>> da.Fill(ds, "Employees" )
>>
>> 'Bind DataGrid
>> dg.SetDataBindi ng(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 SelectedIndexCh anged event handler of a
>> ComboBox
>> so
>> that you only run this when it's needed (more efficient).
>>
>> Private Sub lstMasterLists_ SelectedIndexCh anged _
>> (ByVal sender As System.Object, ByVal e As System.EventArg s) _
>> Handles lstMasterLists. SelectedIndexCh anged
>>
>> '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.SqlDa taAdapter(selSQ L, con)
>> Dim newDS As New DataSet
>> newDA.Fill(newD S, strTbl)
>> dg.SetDataBindi ng(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_ SelectedIndexCh anged _
>> (ByVal sender As System.Object, ByVal e As System.EventArg s) _
>> Handles lstMasterLists. SelectedIndexCh anged
>>
>> '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.SqlDa taAdapter(selSQ L, con)
>> Dim newDS As New DataSet
>> newDA.Fill(newD S, strTbl)
>> dg.SetDataBindi ng(newDS, strTbl)
>> End Sub
>>
>>
>> I have tested all the code I have given here and it works.
>> =============== =============== =============== =============== =
>>
>>
>> "Douglas Buchanan" <db*********@ho tmail.com> wrote in message
>> news:72******** *************** ***@posting.goo gle.com...
>> > 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.Sql Client
>> > Imports System.Windows. Forms
>> >
>> > Public Class NortwindEmploye e
>> > 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.Applicati on.Run(New NortwindEmploye e)
>> >
>> > 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.HeaderForeCo lor = System.Drawing. SystemColors.Co ntrolText
>> > dg.Location = New System.Drawing. Point(8, 40)
>> > dg.Name = "dg"
>> > dg.Size = New System.Drawing. Size(280, 160)
>> > dg.TabIndex = 1
>> >
>> > 'ConnectionStri ng
>> > cn.ConnectionSt ring = "workstatio n id=SEDNA;packet
>> > size=4096;integ rated security=SSPI;d ata source=(local); persist
>> > security info=False;init ial catalog=Northwi nd"
>> >
>> > 'DataAdapter Commands
>> > da.SelectComman d = cmSel
>> >
>> > 'Select Command
>> > cmSel.CommandTe xt = "SELECT EmployeeID, LastName, FirstName,
>> > Title, TitleOfCourtesy , BirthDate, HireDa" & _
>> > "te, Address, City, Region, PostalCode, Country, HomePhone,
>> > Extension, Photo, Not" & _
>> > "es, ReportsTo, PhotoPath FROM Employees"
>> > cmSel.Connectio n = cn
>> >
>> > End Sub
>> >
>> > Private Sub InitializeCompo nent()
>> >
>> > 'Form1
>> > AutoScaleBaseSi ze = New System.Drawing. Size(5, 13)
>> > ClientSize = New System.Drawing. Size(292, 206)
>> > Name = "NortwindEmploy ee"
>> > Text = "NortwindEmploy ee"
>> >
>> > End Sub
>> >
>> > Public Sub btn_Click(ByVal sender As System.Object, ByVal e As
>> > System.EventArg s)
>> >
>> > 'Fill DataAdapter
>> > 'da.Fill(Me.ds, "Employees" )
>> > CType("da", SqlDataAdapter) .Fill(Me.ds, "Employees" )
>> >
>> > 'Bind DataGrid
>> > 'dg.SetDataBind ing(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.nosp am> wrote in message
>> > news:<eO******* ******@TK2MSFTN GP11.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
>> >> news:72******** *************** ***@posting.goo gle.com...
>> >> > 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...
>> >> >
>> >> > lst01PrimaryOpt ions
>> >> > lst02SecondaryO ptions
>> >> > lst03BusinsessS ettings
>> >> > lst04FixedOptio ns
>> >> > ...
>> >> >
>> >> > When I made my DataAdapters I gave them these names...
>> >> >
>> >> > DaPrimaryOption s
>> >> > DaSecondaryOpti ons
>> >> > DaBusinsessSett ings
>> >> > 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.F ill(DsSelection List1, "lkp01RefSource ")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp01RefSource ")
>> >> > Case "lkp02GrpCatego ry"
>> >> > da02GrpCategory .Fill(DsSelecti onList1, "lkp02GrpCatego ry")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp02GrpCatego ry")
>> >> > Case "lkp03PrgmObjec tive"
>> >> > da03PrgmObjecti ve.Fill(DsSelec tionList1, "lkp03PrgmObjec tive")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp03PrgmObjec tive")
>> >> > Case "lkp06JobTi tle"
>> >> > da06JobTitle.Fi ll(DsSelectionL ist1, "lkp06JobTitle" )
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp06JobTitle" )
>> >> > Case "lkp07Qualifica tion"
>> >> > da07Qualificati on.Fill(DsSelec tionList1, "lkp07Qualifica tion")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp07Qualifica tion")
>> >> > Case "lkp08DayOfWeek "
>> >> > da08DayOfWeek.F ill(DsSelection List1, "lkp08DayOfWeek ")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp08DayOfWeek ")
>> >> > Case "lkp09MealT ype"
>> >> > da09MealType.Fi ll(DsSelectionL ist1, "lkp09MealType" )
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp09MealType" )
>> >> > Case "lkp10MerchandT ype"
>> >> > da10MerchandTyp e.Fill(DsSelect ionList1, "lkp10MerchandT ype")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp10MerchandT ype")
>> >> > Case "lkp11CommResou rce"
>> >> > da11CommResourc e.Fill(DsSelect ionList1, "lkp11CommResou rce")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp11CommResou rce")
>> >> > Case "lkp12Telephony Device"
>> >> > da12TelephonyDe vice.Fill(DsSel ectionList1,
>> >> > "lkp12Telephony Device")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp12Telephony Device")
>> >> > Case "lkp13WwwTy pe"
>> >> > da13WwwType.Fil l(DsSelectionLi st1, "lkp13WwwTy pe")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp13WwwTy pe")
>> >> > Case "lkp14ModeOfCon tact"
>> >> > da14ModeOfConta ct.Fill(DsSelec tionList1, "lkp14ModeOfCon tact")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp14ModeOfCon tact")
>> >> > Case "lkp15MsgTo pic"
>> >> > da15MsgTopic.Fi ll(DsSelectionL ist1, "lkp15MsgTopic" )
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp15MsgTopic" )
>> >> > Case "lkp16ScheduleT ype"
>> >> > da16ScheduleTyp e.Fill(DsSelect ionList1, "lkp16ScheduleT ype")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp16ScheduleT ype")
>> >> > Case "lkp17WeightGro up"
>> >> > da17WeightGroup .Fill(DsSelecti onList1, "lkp17WeightGro up")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp17WeightGro up")
>> >> > Case "lkp18ProgramCa tegory"
>> >> > da18ProgramCate gory.Fill(DsSel ectionList1,
>> >> > "lkp18ProgramCa tegory")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "lkp18ProgramCa tegory")
>> >> > Case "lkp19Eleme nt"
>> >> > da19Element.Fil l(DsSelectionLi st1, "lkp19Eleme nt")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "lkp19Eleme nt")
>> >> > Case "enm1FoodAllerg y"
>> >> > daFoodAllergy.F ill(DsSelection List1, "enm1FoodAllerg y")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm1FoodAllerg y")
>> >> > Case "enm2EnvironAll ergy"
>> >> > daEnvironAllerg y.Fill(DsSelect ionList1, "enm2EnvironAll ergy")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "enm2EnvironAll ergy")
>> >> > Case "enm3MedicalAll ergy"
>> >> > daMedicalAllerg y.Fill(DsSelect ionList1, "enm3MedicalAll ergy")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "enm3MedicalAll ergy")
>> >> > Case "enm4MedConcern "
>> >> > daMedConcern.Fi ll(DsSelectionL ist1, "enm4MedConcern ")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 , "enm4MedConcern ")
>> >> > Case "enm5ActivityRe quest"
>> >> > daActivityReque st.Fill(DsSelec tionList1, "enm5ActivityRe quest")
>> >> > DgMasterLists.S etDataBinding(D sSelectionList1 ,
>> >> > "enm5ActivityRe quest")
>> >> > Case Else
>> >> > TableErrorMessa ge()
>> >> > 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(DsMasterL ists1, strTbl)
>> >> > DgMasterLists.S etDataBinding(D sMasterLists1, 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.nosp am> wrote in message
>> >> > news:<u5******* *******@TK2MSFT NGP12.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(DsMasterL ists1, 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*********@ho tmail.com> wrote in message
>> >> >> news:72******** *************** *@posting.googl e.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.Sq lClient.SqlData Adapter'.]
>> >> >> >
>> >> >> > === code snippit ===
>> >> >> > Private Sub lstMasterLists_ Click(ByVal sender As Object, _
>> >> >> > ByVal e As System.EventArg s) 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.lstMasterLis ts.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(DsMasterL ists1, strTbl)
>> >> >> > DgMasterLists.S etDataBinding(D sMasterLists1, 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
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
(SelectedIndexC hanged...). 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.Sql Client
Imports System.Windows. Forms

Public Class Form1
Inherits Form

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

'Skip on load
Private SkipTabControl1 IndexChangedFun ctionality As Boolean = True
Private SkipLstMasterLi stsndexChangedF unctionality As Boolean =
True

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
'LoadLstMasterL ists() 'Don't load yet - user might add another
item.
FilldgMasterCon trolList()
End Sub

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

If SkipTabControl1 IndexChangedFun ctionality = True Then
'Skipped on load
'Now allow TabControl1_Sel ectedIndexChang ed to run
SkipTabControl1 IndexChangedFun ctionality = False
Else
'Load appropriate ListControl
Select Case TabControl1.Sel ectedTab.Text
Case "Master Control List"
''Fill the DataGrid
FilldgMasterCon trolList()
Case "Master Lists"
''Fill the ListBox
LoadLstMasterLi sts()
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 DaFillOrUpdateD sTbl(ByVal FilUpd As FillOrUpdate, _
ByVal Da As SqlDataAdapter, ByVal Ds As DataSet, ByVal Tbl As
String)

'How to call
'DaFillOrUpdate DsTbl(FilUpd, Da, Ds, Tbl)

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

#End Region

#Region " Master Control List "

Private Sub FilldgMasterCon trolList()
Me.Dalst00Maste rOptionsList.Fi ll(Me.DsMasterC ontrolList1,
"lst00MasterOpt ionsList")
Me.dgMasterCont rolList.SetData Binding(Me.DsMa sterControlList 1,
"lst00MasterOpt ionsList")
End Sub

Private Sub btnMasterContro lListUpdate_Cli ck(ByVal sender As
System.Object, _
ByVal e As System.EventArg s) Handles
btnMasterContro lListUpdate.Cli ck
Me.Dalst00Maste rOptionsList.Up date(Me.DsMaste rControlList1,
"lst00MasterOpt ionsList")
End Sub

Private Sub btnMasterContro lListAddRow_Cli ck(ByVal sender As
System.Object, _
ByVal e As System.EventArg s) Handles
btnMasterContro lListAddRow.Cli ck
'DataSet.Table. AddRow(FirstCol umn, SecondColumn, ThirdColumn,
....)
Me.DsMasterCont rolList1.lst00M asterOptionsLis t.Addlst00Maste rOptionsListRow (System.Guid.Ne wGuid().ToStrin g,
"", "")
End Sub

#End Region

#Region " Master Lists "

'Provide the GUID to the first column
Private Sub btnMasterListsN ewRow_Click(ByV al sender As
System.Object, _
ByVal e As System.EventArg s) Handles btnMasterListsN ewRow.Click

'Get the name of the table which is the DataMember of the
DataGrid
Dim TableName As String
TableName = Me.DgMasterList s.DataMember.To String

Select Case TableName
Case "lst01PrimaryOp tions"
DsMasterLists1. lst01PrimaryOpt ions.Addlst01Pr imaryOptionsRow (System.Guid.Ne wGuid().ToStrin g,
"", "", "", 0, False)
Case "lst02Secondary Options"
DsMasterLists1. lst02SecondaryO ptions.Addlst02 SecondaryOption sRow(System.Gui d.NewGuid().ToS tring,
"", "", "", 0, False)
Case "lst03BusinessS ettings"
DsMasterLists1. lst03BusinessSe ttings.Addlst03 BusinessSetting sRow(System.Gui d.NewGuid().ToS tring,
"", "", "", 0, False)
Case "lst04FixedOpti ons"
DsMasterLists1. lst04FixedOptio ns.Addlst04Fixe dOptionsRow(Sys tem.Guid.NewGui d().ToString,
"", "", "", 0, False)
Case Else
'TableErrorMess age()
End Select

End Sub

'Fill the DataGrid when an item in the ListControl is selected
Private Sub lstMasterLists_ SelectedIndexCh anged(ByVal sender As
System.Object, _
ByVal e As System.EventArg s) Handles
lstMasterLists. SelectedIndexCh anged

If SkipLstMasterLi stsndexChangedF unctionality = True Then
'Skipped on load
'Now allow lstMasterLists_ SelectedIndexCh anged to run
SkipLstMasterLi stsndexChangedF unctionality = False
Else
'DataGrid - Fill
FillOrUpdateDgM asterLists(Fill OrUpdate.Fil)
MessageBox.Show ("Fill the list control")
End If

End Sub

'Update the DataGrid
Private Sub btnMasterListsU pdate_Click(ByV al sender As
System.Object, _
ByVal e As System.EventArg s) Handles btnMasterListsU pdate.Click

'DataGrid - Update
FillOrUpdateDgM asterLists(Fill OrUpdate.Upd)
End Sub

Protected Sub LoadLstMasterLi sts()

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

'Text type command
cm.CommandType = CommandType.Tex t
cm.CommandText = "Select TableDescriptio n, TableName FROM
lst00MasterOpti onsList"
cm.CommandTimeo ut = 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 TableDescriptio n As String
Dim TableName As String

'Do While
dr = cm.ExecuteReade r(CommandBehavi or.Default)

'Fill the list control
Do While dr.Read
TableDescriptio n = dr("TableDescri ption").ToStrin g
TableName = dr("TableName") .ToString
'Why is this shown as undefined see TablesList.vb
arTablesList.Ad d(New TablesList(Tabl eDescription,
TableName))
Loop

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

'Close the connection
cn.Close()

End Sub

'This code is called for both Fill and Update
Private Sub FillOrUpdateDgM asterLists(ByVa l FilUpd As
FillOrUpdate)

'Get the Value Member from the ListControl
Dim TableName As String
TableName = Me.lstMasterLis ts.SelectedValu e.ToString

Select Case TableName
Case "lst01PrimaryOp tions"
DaFillOrUpdateD sTbl(FilUpd, Dalst01PrimaryO ptions,
DsMasterLists1, TableName)
Case "lst02Secondary Options"
DaFillOrUpdateD sTbl(FilUpd, Dalst02Secondar yOptions,
DsMasterLists1, TableName)
Case "lst03BusinessS ettings"
DaFillOrUpdateD sTbl(FilUpd, Dalst03Business Settings,
DsMasterLists1, TableName)
Case "lst04FixedOpti ons"
DaFillOrUpdateD sTbl(FilUpd, Dalst04FixedOpt ions,
DsMasterLists1, TableName)
Case Else
'TableErrorMess age()
End Select

Me.DgMasterList s.SetDataBindin g(DsMasterLists 1, TableName)
End Sub

#End Region

End Class

#Region " Table List Class "
Public Class TablesList

Private myTableDesctipt ion As String
Private myTableName As String
'Private myInstruction As String

Public Sub New(ByVal strTableDescrip tion As String, ByVal
strTableName As String)
MyBase.New()
Me.myTableDesct iption = strTableDescrip tion
Me.myTableName = strTableName
End Sub

Public ReadOnly Property TableDesctiptio n() As String
Get
Return myTableDesctipt ion
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.Sql Client
Imports System.Data
Imports System.Data.Dat aRowVersion
Imports System.Data.Par ameterDirection
Imports System.Data.Com mon
Imports System.Data.Com mon.DataTableMa pping

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(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load

'Connection String
Cn.ConnectionSt ring = "workstatio n id=sedna;packet
size=4096;integ rated security=SSPI;d ata source=SEDNA;pe rsist security
info=False;init ial catalog=BccAppD b"

'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.TableMapping s.AddRange(New DataTableMappin g() { _
New DataTableMappin g("Table", "lkp01RefSource ", _
New DataColumnMappi ng() { _
New DataColumnMappi ng("RefSourceID ", "RefSourceI D"), _
New DataColumnMappi ng("RefSource" , "RefSource" ), _
New DataColumnMappi ng("ord", "ord"), _
New DataColumnMappi ng("hide", "hide")})})

'Select Command
With cmSel
.CommandText = "NewSelectComma ndRefS"
.CommandType = CommandType.Sto redProcedure
.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 = "NewInsertComma ndRefS"
.CommandType = CommandType.Sto redProcedure
.Connection = Cn
With cmIns.Parameter s
.Add(New SqlParameter("@ RETURN_VALUE", SqlDbType.Int,
4, ReturnValue, False, CType(0, Byte), CType(0, Byte), "", Current,
Nothing))
.Add(New SqlParameter("@ RefSourceID",
SqlDbType.TinyI nt, 1, "RefSourceI D"))
.Add(New SqlParameter("@ RefSource", SqlDbType.VarCh ar,
25, "RefSource" ))
.Add(New SqlParameter("@ ord", SqlDbType.TinyI nt, 1,
"ord"))
.Add(New SqlParameter("@ hide", SqlDbType.Bit, 1,
"hide"))
End With
End With

'Update Command
With cmUpd
.CommandText = "NewUpdateComma ndRefS"
.CommandType = CommandType.Sto redProcedure
.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.VarCh ar,
25, "RefSource" ))
.Add(New SqlParameter("@ ord", SqlDbType.TinyI nt, 1,
"ord"))
.Add(New SqlParameter("@ hide", SqlDbType.Bit, 1,
"hide"))
.Add(New SqlParameter("@ Original_RefSou rceID",
SqlDbType.TinyI nt, 1, Input, False, CType(0, Byte), CType(0, Byte),
"RefSourceI D", Original, Nothing))
.Add(New SqlParameter("@ Original_RefSou rce",
SqlDbType.VarCh ar, 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.TinyI nt, 1, Input, False, CType(0, Byte), CType(0, Byte),
"ord", Original, Nothing))
.Add(New SqlParameter("@ RefSourceID",
SqlDbType.TinyI nt, 1, "RefSourceI D"))
End With
End With

'Delete Command
With cmDel
.CommandText = "[NewDeleteComman dRefS]"
.CommandType = CommandType.Sto redProcedure
.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_RefSou rceID",
SqlDbType.TinyI nt, 1, Input, False, CType(0, Byte), CType(0, Byte),
"RefSourceI D", Original, Nothing))
.Add(New SqlParameter("@ Original_RefSou rce",
SqlDbType.VarCh ar, 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.TinyI nt, 1, Input, False, CType(0, Byte), CType(0, Byte),
"ord", Original, Nothing))
'Add(
End With
End With

End Sub

Private Sub btnFill_Click(B yVal sender As System.Object, ByVal e
As System.EventArg s) Handles btnFill.Click

'DataAdapter
Da.Fill(DsRefSo urce, "lkp01RefSource ")

'DataGrid Binding
DataGrid1.SetDa taBinding(DsRef Source, "lkp01RefSource ")

End Sub

Private Sub btnUpdate_Click (ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles btnUpdate.Click
Da.Update(DsRef Source, "lkp01RegSource ")

End Sub
End Class

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

I welcome any comment.

--Doug
Jul 21 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1210
by: Daniel Lidström | last post by:
Hi, why is the cursor placed in the output view after a completed build? Also, why when I type F4 to find out errors, is the cursor still in the output view? Visual Studio 6.0 moved the cursor to the code view, now I have to either use the mouse or ctrl-tab two times to place the cursor at the error location. In my opinion this is very annoying. Does anyone agree? IDE version: 7.1.3088 --
15
2350
by: cody | last post by:
We have a huge project, the solutuion spans 50 projects growing. Everytime I want to start the project I have to wait nearly over 1 minute for the compiler to complete building. This is unaccaptable. I thought about loading only the project I need into visual studio and not the whole solution. The problem is that the compiler tells me it cannot find the referenced dlls (project references) although they are all lying in their bin and obj...
0
1372
by: Igor | last post by:
Hi all! As many of you, I have a big VB.NET solution and quite often get bogus build errors out of the blue. This isn't declared, that is not a property of some shit and so on... It's very frustrating and the only solution for me was to reload a solution which takes time... I've browsed through usenet to find some solution, but nothing... One of the posts gave me an idea, so I've put together a macro that fixes the problem for me....
0
2630
by: pcgxk | last post by:
Due to some bugs in .NET SP1 which we are currently review with MS under separate cover, I am having to regress the patching levels of several of my desktops to rebuild and retest several versions of our code. To do this I had been previously advised to ensure that both versions of the Framework (the software supports both 1.0 and 1.1) were completely removed from the PC before starting the installs again from scratch. i.e. to do this as...
3
4565
by: Charles Law | last post by:
I have the following lines Dim t As Type = GetType(MyType) Dim serialiser As New XmlSerializer(t) I want to be able to do the following with a FileStream fs Dim instance As MyType instance = CType(serialiser.Deserialize(fs), MyType)
5
1562
by: Jon | last post by:
I am constantly getting reports in my task list like these: * 'someObjectName" is not a member of 'someOtherObjectName' * Handles clause requires a WithEvents variable. * Name 'someName' is not declared These errors are totally erroneous. If I go into the class and cut the offending code and repaste it in the same spot the errors temporarily go away. Also, more effective is to delete the files in project/bin and rebuild. Both fixes...
13
2293
by: Douglas Buchanan | last post by:
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...
5
1515
by: Pieter | last post by:
Hi, I'm getting a really terrible and anoying bug/problem in VS.NET 2005: 1. Create a new Windows Application. 2. Add a new class Class1. 3. Add a usercontrol UserControl1. 4. Add a public instance of Class1 to UserControl1 (with WithEvents): "Public WithEvents ThisClass As New Class1". 5. Build the Solution, and put an instance of UserControl1 on Form1.
0
8661
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9008
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7699
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6512
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5855
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4608
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3030
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2313
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.