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