|
 |
aspx_beginners thread: Can use 1 DataAdapter to store multiple tables ?
Message #1 by "CMY" <my62202@y...> on Thu, 26 Sep 2002 04:31:44
|
|
Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
Message #2 by ramprasad upadhyaya <rpu_forum@y...> on Wed, 25 Sep 2002 22:01:23 -0700 (PDT)
|
|
Hi,
Yes , u can use a dataadapter to store more than one tables as below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders; select OrderID ,Products.ProductName,[order
Details].Unitprice,[order Details].Quantity,[order Details].discount from [order Details],Products where [Order
Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
Message #3 by "CMY" <my62202@y...> on Thu, 26 Sep 2002 11:26:14
|
|
Hi...thanks for the help....
but my problem is like below:
I have 2 drop down list..the 1st one (ddlTable) is to list out all the
tables in my database and the 2nd one (ddlColumn) is suppose to list out
all the related columns name for tat particular table selected so that my
user will know what are the columns available for that table.
in my ddlTable_SelectedIndexChanged sub, i do the following code :
Dim ds As New DataSet()
Dim myCommand As String
myCommand = "select * from " & ddlTable.SelectedItem.Text
Dim da As SqlDataAdapter
da = New SqlDataAdapter(myCommand, conn)
da.Fill(ds)
Dim col As DataColumn
For Each col In ds.Tables(0).Columns
ddlColumn.Items.Add(col.ColumnName)
Next
rite now the first time I select a table from ddlTable, it will give me
the correct corresponding column name in ddlColumn.
but when i select another table, it will append the column names of the
2nd table and displayed together with the 1st table's column names in
ddlColumn.
so, how do I actually get rid of the previous dataTable in the dataSet ?
or is there a better way to solve this problem ?
Pls help, thanks a lot.
>
Hi,
Yes , u can use a dataadapter to store more than one tables as
below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders;
select OrderID ,Products.ProductName,[order Details].Unitprice,[order
Details].Quantity,[order Details].discount from [order Details],Products
where [Order Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
Message #4 by ramprasad upadhyaya <rpu_forum@y...> on Thu, 26 Sep 2002 04:34:02 -0700 (PDT)
|
|
Hi,
I think Below way is best thing to solve your problem. Please check the steps...
1. Connect to database
2. Get the Table Informations as below ......
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND name<>'dtproperties' ORDER BY name "
3. Fill data from the Query to -Tables dropdownlist and value of dropdownlist is 'id' which is fetched from above query
4. On selection of Perticular table Get the 'id' value in ur ddlTable_SelectedIndexChanged event and use below query to get the
columns for the table selected.
strsql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & intid
5. fill the data in columns dropdownlist.(Reset the earlier contents before filling the columns data to dropdownlist.)
Hope this will solve u r problem..
Let me know if u have any query...
Ramprasad. S.U
######################################################################
CMY wrote: Hi...thanks for the help....
but my problem is like below:
I have 2 drop down list..the 1st one (ddlTable) is to list out all the
tables in my database and the 2nd one (ddlColumn) is suppose to list out
all the related columns name for tat particular table selected so that my
user will know what are the columns available for that table.
in my ddlTable_SelectedIndexChanged sub, i do the following code :
Dim ds As New DataSet()
Dim myCommand As String
myCommand = "select * from " & ddlTable.SelectedItem.Text
Dim da As SqlDataAdapter
da = New SqlDataAdapter(myCommand, conn)
da.Fill(ds)
Dim col As DataColumn
For Each col In ds.Tables(0).Columns
ddlColumn.Items.Add(col.ColumnName)
Next
rite now the first time I select a table from ddlTable, it will give me
the correct corresponding column name in ddlColumn.
but when i select another table, it will append the column names of the
2nd table and displayed together with the 1st table's column names in
ddlColumn.
so, how do I actually get rid of the previous dataTable in the dataSet ?
or is there a better way to solve this problem ?
Pls help, thanks a lot.
>
Hi,
Yes , u can use a dataadapter to store more than one tables as
below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders;
select OrderID ,Products.ProductName,[order Details].Unitprice,[order
Details].Quantity,[order Details].discount from [order Details],Products
where [Order Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
Message #5 by "CMY" <my62202@y...> on Fri, 27 Sep 2002 05:56:19
|
|
Hi Ramprasad,
Sorry to trouble you again.
I don't quite understand point no. 4 where you say get the table ID.
How do I actually get the table ID ?
And for point no. 5, how do i reset the earlier contents ?
Thanks for your patience and help.
################################################################
>
Hi,
I think Below way is best thing to solve your problem. Please
check the steps...
1. Connect to database
2. Get the Table Informations as below ......
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
3. Fill data from the Query to -Tables dropdownlist and value of
dropdownlist is 'id' which is fetched from above query
4. On selection of Perticular table Get the 'id' value in ur
ddlTable_SelectedIndexChanged event and use below query to get the
columns for the table selected.
strsql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & intid
5. fill the data in columns dropdownlist.(Reset the earlier contents
before filling the columns data to dropdownlist.)
Hope this will solve u r problem..
Let me know if u have any query...
Ramprasad. S.U
######################################################################
CMY wrote: Hi...thanks for the help....
but my problem is like below:
I have 2 drop down list..the 1st one (ddlTable) is to list out all the
tables in my database and the 2nd one (ddlColumn) is suppose to list out
all the related columns name for tat particular table selected so that my
user will know what are the columns available for that table.
in my ddlTable_SelectedIndexChanged sub, i do the following code :
Dim ds As New DataSet()
Dim myCommand As String
myCommand = "select * from " & ddlTable.SelectedItem.Text
Dim da As SqlDataAdapter
da = New SqlDataAdapter(myCommand, conn)
da.Fill(ds)
Dim col As DataColumn
For Each col In ds.Tables(0).Columns
ddlColumn.Items.Add(col.ColumnName)
Next
rite now the first time I select a table from ddlTable, it will give me
the correct corresponding column name in ddlColumn.
but when i select another table, it will append the column names of the
2nd table and displayed together with the 1st table's column names in
ddlColumn.
so, how do I actually get rid of the previous dataTable in the dataSet ?
or is there a better way to solve this problem ?
Pls help, thanks a lot.
>
Hi,
Yes , u can use a dataadapter to store more than one tables as
below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders;
select OrderID ,Products.ProductName,[order Details].Unitprice,[order
Details].Quantity,[order Details].discount from [order Details],Products
where [Order Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
Message #6 by ramprasad upadhyaya <rpu_forum@y...> on Fri, 27 Sep 2002 02:34:02 -0700 (PDT)
|
|
Hi,
Findin attachment(aspx and codebehind simply copy all the 3 files and change the connection string) for the complete solution
of u r problem...Send me the feedback
Ok..... First i will clarify the point no 4.
4. U need not have to get the id again. U have allready got that in below query in Point no-2
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
Mail me back , I want to know whether the solution is clear to u or not...
Ramprasad
#########################################################################
CMY wrote: Hi Ramprasad,
Sorry to trouble you again.
I don't quite understand point no. 4 where you say get the table ID.
How do I actually get the table ID ?
And for point no. 5, how do i reset the earlier contents ?
Thanks for your patience and help.
################################################################
>
Hi,
I think Below way is best thing to solve your problem. Please
check the steps...
1. Connect to database
2. Get the Table Informations as below ......
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
3. Fill data from the Query to -Tables dropdownlist and value of
dropdownlist is 'id' which is fetched from above query
4. On selection of Perticular table Get the 'id' value in ur
ddlTable_SelectedIndexChanged event and use below query to get the
columns for the table selected.
strsql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & intid
5. fill the data in columns dropdownlist.(Reset the earlier contents
before filling the columns data to dropdownlist.)
Hope this will solve u r problem..
Let me know if u have any query...
Ramprasad. S.U
######################################################################
CMY wrote: Hi...thanks for the help....
but my problem is like below:
I have 2 drop down list..the 1st one (ddlTable) is to list out all the
tables in my database and the 2nd one (ddlColumn) is suppose to list out
all the related columns name for tat particular table selected so that my
user will know what are the columns available for that table.
in my ddlTable_SelectedIndexChanged sub, i do the following code :
Dim ds As New DataSet()
Dim myCommand As String
myCommand = "select * from " & ddlTable.SelectedItem.Text
Dim da As SqlDataAdapter
da = New SqlDataAdapter(myCommand, conn)
da.Fill(ds)
Dim col As DataColumn
For Each col In ds.Tables(0).Columns
ddlColumn.Items.Add(col.ColumnName)
Next
rite now the first time I select a table from ddlTable, it will give me
the correct corresponding column name in ddlColumn.
but when i select another table, it will append the column names of the
2nd table and displayed together with the 1st table's column names in
ddlColumn.
so, how do I actually get rid of the previous dataTable in the dataSet ?
or is there a better way to solve this problem ?
Pls help, thanks a lot.
>
Hi,
Yes , u can use a dataadapter to store more than one tables as
below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders;
select OrderID ,Products.ProductName,[order Details].Unitprice,[order
Details].Quantity,[order Details].discount from [order Details],Products
where [Order Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="cboProb.aspx.vb" Inherits="cboTest.WebForm1"%>
WebForm1
Tables
Columns
<?xml version="1.0" encoding="utf-8" ?>
<root>
<xsd:schema id="root" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="root" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="data">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1" />
<xsd:element name="comment" type="xsd:string" minOccurs="0" msdata:Ordinal="2" />
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string" />
<xsd:attribute name="type" type="xsd:string" />
<xsd:attribute name="mimetype" type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="resheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="value" type="xsd:string" minOccurs="0" msdata:Ordinal="1" />
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string" use="required" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<resheader name="ResMimeType">
<value>text/microsoft-resx</value>
</resheader>
<resheader name="Version">
<value>1.0.0.0</value>
</resheader>
<resheader name="Reader">
<value>System.Resources.ResXResourceReader, System.Windows.Forms, Version=1.0.3300.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089</value>
</resheader>
<resheader name="Writer">
<value>System.Resources.ResXResourceWriter, System.Windows.Forms, Version=1.0.3300.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089</value>
</resheader>
</root>
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As System.Web.UI.WebControls.DropDownList
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents DropDownList2 As System.Web.UI.WebControls.DropDownList
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim daNex As New SqlDataAdapter()
Dim con As New SqlConnection()
Dim strSql As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
DropDownList1.Attributes("OnChange") = "fnSmt();"
con = New SqlConnection("Data Source=sparepc;Initial Catalog=derivetest;User ID=sa;Password=;")
con.Open()
If Request("ctx") = "Fill" Then
Dim dsCols As New DataSet()
strSql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & DropDownList1.SelectedItem.Value
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsCols, "sysobjects")
DropDownList2.DataSource = dsCols
DropDownList2.DataTextField = dsCols.Tables(0).Columns("name").ToString
DropDownList2.DataBind()
Else
Dim daNex As New SqlDataAdapter()
Dim dsTables As New DataSet()
strSql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND name<>'dtproperties' ORDER BY name"
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsTables, "sysobjects")
'FILL THE DATA TO DROPDOWNLIST
DropDownList1.DataSource = dsTables
DropDownList1.DataTextField = dsTables.Tables(0).Columns("name").ToString
DropDownList1.DataValueField = dsTables.Tables(0).Columns("id").ToString
DropDownList1.DataBind()
End If
End Sub
End Class
Message #7 by ramprasad upadhyaya <rpu_forum@y...> on Fri, 27 Sep 2002 03:41:56 -0700 (PDT)
|
|
attchment was included in the mail itself... check this file
ramprasad upadhyaya wrote:
Hi,
Findin attachment(aspx and codebehind simply copy all the 3 files and change the connection string) for the complete solution of u r
problem...Send me the feedback
Ok..... First i will clarify the point no 4.
4. U need not have to get the id again. U have allready got that in below query in Point no-2
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
Mail me back , I want to know whether the solution is clear to u or not...
Ramprasad
#########################################################################
CMY wrote: Hi Ramprasad,
Sorry to trouble you again.
I don't quite understand point no. 4 where you say get the table ID.
How do I actually get the table ID ?
And for point no. 5, how do i reset the earlier contents ?
Thanks for your patience and help.
################################################################
>
Hi,
I think Below way is best thing to solve your problem. Please
check the steps...
1. Connect to database
2. Get the Table Informations as below ......
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
3. Fill data from the Query to -Tables dropdownlist and value of
dropdownlist is 'id' which is fetched from above query
4. On selection of Perticular table Get the 'id' value in ur
ddlTable_SelectedIndexChanged event and use below query to get the
columns for the table selected.
strsql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & intid
5. fill the data in columns dropdownlist.(Reset the earlier contents
before filling the columns data to dropdownlist.)
Hope this will solve u r problem..
Let me know if u have any query...
Ramprasad. S.U
######################################################################
CMY wrote: Hi...thanks for the help....
but my problem is like below:
I have 2 drop down list..the 1st one (ddlTable) is to list out all the
tables in my database and the 2nd one (ddlColumn) is suppose to list out
all the related columns name for tat particular table selected so that my
user will know what are the columns available for that table.
in my ddlTable_SelectedIndexChanged sub, i do the following code :
Dim ds As New DataSet()
Dim myCommand As String
myCommand = "select * from " & ddlTable.SelectedItem.Text
Dim da As SqlDataAdapter
da = New SqlDataAdapter(myCommand, conn)
da.Fill(ds)
Dim col As DataColumn
For Each col In ds.Tables(0).Columns
ddlColumn.Items.Add(col.ColumnName)
Next
rite now the first time I select a table from ddlTable, it will give me
the correct corresponding column name in ddlColumn.
but when i select another table, it will append the column names of the
2nd table and displayed together with the 1st table's column names in
ddlColumn.
so, how do I actually get rid of the previous dataTable in the dataSet ?
or is there a better way to solve this problem ?
Pls help, thanks a lot.
>
Hi,
Yes , u can use a dataadapter to store more than one tables as
below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders;
select OrderID ,Products.ProductName,[order Details].Unitprice,[order
Details].Quantity,[order Details].discount from [order Details],Products
where [Order Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
WebForm1
Tables
Columns
text/microsoft-resx
1.0.0.0
System.Resources.ResXResourceReader, System.Windows.Forms, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Resources.ResXResourceWriter, System.Windows.Forms, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As System.Web.UI.WebControls.DropDownList
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents DropDownList2 As System.Web.UI.WebControls.DropDownList
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim daNex As New SqlDataAdapter()
Dim con As New SqlConnection()
Dim strSql As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
DropDownList1.Attributes("OnChange") = "fnSmt();"
con = New SqlConnection("Data Source=sparepc;Initial Catalog=derivetest;User ID=sa;Password=;")
con.Open()
If Request("ctx") = "Fill" Then
Dim dsCols As New DataSet()
strSql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & DropDownList1.SelectedItem.Value
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsCols, "sysobjects")
DropDownList2.DataSource = dsCols
DropDownList2.DataTextField = dsCols.Tables(0).Columns("name").ToString
DropDownList2.DataBind()
Else
Dim daNex As New SqlDataAdapter()
Dim dsTables As New DataSet()
strSql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND name<>'dtproperties' ORDER BY name"
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsTables, "sysobjects")
'FILL THE DATA TO DROPDOWNLIST
DropDownList1.DataSource = dsTables
DropDownList1.DataTextField = dsTables.Tables(0).Columns("name").ToString
DropDownList1.DataValueField = dsTables.Tables(0).Columns("id").ToString
DropDownList1.DataBind()
End If
End Sub
End Class
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
Message #8 by "CMY" <my62202@y...> on Mon, 30 Sep 2002 04:06:21
|
|
Hi Ramprasad,
Thanks a lot....I followed the coding u taught me and IT WORKS !!
Really thank you for the help and appreciate it....
Just to clarify some of my doubts and queries,
1) 1st to get the table names, we do "select name, id from sysobject...."
and then assign the name to dataTextField of dropDownList1 and assign
the id to dataValueField of dropDownList1. What is the difference
between dataTextField and dataValueField ?
2) In your second strSql, u put "select name from syscolumns...." and then
you fill the dataAdapter with : daNex.Fill(dsCols, "sysobjects")
Isn't it suppose to be daNex.Fill(dsCols, "syscolumns") ?
####################################################
>
attchment was included in the mail itself... check this file
ramprasad upadhyaya wrote:
Hi,
Findin attachment(aspx and codebehind simply copy all the 3 files and
change the connection string) for the complete solution of u r
problem...Send me the feedback
Ok..... First i will clarify the point no 4.
4. U need not have to get the id again. U have allready got that in below
query in Point no-2
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
Mail me back , I want to know whether the solution is clear to u or not...
Ramprasad
#########################################################################
CMY wrote: Hi Ramprasad,
Sorry to trouble you again.
I don't quite understand point no. 4 where you say get the table ID.
How do I actually get the table ID ?
And for point no. 5, how do i reset the earlier contents ?
Thanks for your patience and help.
################################################################
>
Hi,
I think Below way is best thing to solve your problem. Please
check the steps...
1. Connect to database
2. Get the Table Informations as below ......
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
3. Fill data from the Query to -Tables dropdownlist and value of
dropdownlist is 'id' which is fetched from above query
4. On selection of Perticular table Get the 'id' value in ur
ddlTable_SelectedIndexChanged event and use below query to get the
columns for the table selected.
strsql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & intid
5. fill the data in columns dropdownlist.(Reset the earlier contents
before filling the columns data to dropdownlist.)
Hope this will solve u r problem..
Let me know if u have any query...
Ramprasad. S.U
######################################################################
CMY wrote: Hi...thanks for the help....
but my problem is like below:
I have 2 drop down list..the 1st one (ddlTable) is to list out all the
tables in my database and the 2nd one (ddlColumn) is suppose to list out
all the related columns name for tat particular table selected so that my
user will know what are the columns available for that table.
in my ddlTable_SelectedIndexChanged sub, i do the following code :
Dim ds As New DataSet()
Dim myCommand As String
myCommand = "select * from " & ddlTable.SelectedItem.Text
Dim da As SqlDataAdapter
da = New SqlDataAdapter(myCommand, conn)
da.Fill(ds)
Dim col As DataColumn
For Each col In ds.Tables(0).Columns
ddlColumn.Items.Add(col.ColumnName)
Next
rite now the first time I select a table from ddlTable, it will give me
the correct corresponding column name in ddlColumn.
but when i select another table, it will append the column names of the
2nd table and displayed together with the 1st table's column names in
ddlColumn.
so, how do I actually get rid of the previous dataTable in the dataSet ?
or is there a better way to solve this problem ?
Pls help, thanks a lot.
>
Hi,
Yes , u can use a dataadapter to store more than one tables as
below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders;
select OrderID ,Products.ProductName,[order Details].Unitprice,[order
Details].Quantity,[order Details].discount from [order Details],Products
where [Order Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
WebForm1
Tables
Columns
text/microsoft-resx
1.0.0.0
System.Resources.ResXResourceReader, System.Windows.Forms,
Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Resources.ResXResourceWriter, System.Windows.Forms,
Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents DropDownList2 As
System.Web.UI.WebControls.DropDownList
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim daNex As New SqlDataAdapter()
Dim con As New SqlConnection()
Dim strSql As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
DropDownList1.Attributes("OnChange") = "fnSmt();"
con = New SqlConnection("Data Source=sparepc;Initial
Catalog=derivetest;User ID=sa;Password=;")
con.Open()
If Request("ctx") = "Fill" Then
Dim dsCols As New DataSet()
strSql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" &
DropDownList1.SelectedItem.Value
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsCols, "sysobjects")
DropDownList2.DataSource = dsCols
DropDownList2.DataTextField = dsCols.Tables(0).Columns("name").ToString
DropDownList2.DataBind()
Else
Dim daNex As New SqlDataAdapter()
Dim dsTables As New DataSet()
strSql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name"
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsTables, "sysobjects")
'FILL THE DATA TO DROPDOWNLIST
DropDownList1.DataSource = dsTables
DropDownList1.DataTextField = dsTables.Tables(0).Columns("name").ToString
DropDownList1.DataValueField = dsTables.Tables(0).Columns("id").ToString
DropDownList1.DataBind()
End If
End Sub
End Class
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
Message #9 by ramprasad upadhyaya <rpu_forum@y...> on Mon, 30 Sep 2002 00:08:06 -0700 (PDT)
|
|
Hi,
1. dataTextField is the text which is going to be displayed in dropdownlist but dataValueField will not be displayed on u r
dropdownlist but used to store the unique value for the data selected( unique value for dataTextField , in our case id is unique one
which identify perticular table name).
2. The parameter 'sysobjects' u specify for dataadapter fill property, like daNex.Fill(dsCols, "sysobjects") will not make any thing
, This is Just for recognizing table .
U can make it as daNex.Fill(dsCols, "syscolumns")
are u clear..
Mail me back
Ramprasad
CMY wrote:Hi Ramprasad,
Thanks a lot....I followed the coding u taught me and IT WORKS !!
Really thank you for the help and appreciate it....
Just to clarify some of my doubts and queries,
1) 1st to get the table names, we do "select name, id from sysobject...."
and then assign the name to dataTextField of dropDownList1 and assign
the id to dataValueField of dropDownList1. What is the difference
between dataTextField and dataValueField ?
2) In your second strSql, u put "select name from syscolumns...." and then
you fill the dataAdapter with : daNex.Fill(dsCols, "sysobjects")
Isn't it suppose to be daNex.Fill(dsCols, "syscolumns") ?
####################################################
>
attchment was included in the mail itself... check this file
ramprasad upadhyaya wrote:
Hi,
Findin attachment(aspx and codebehind simply copy all the 3 files and
change the connection string) for the complete solution of u r
problem...Send me the feedback
Ok..... First i will clarify the point no 4.
4. U need not have to get the id again. U have allready got that in below
query in Point no-2
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
Mail me back , I want to know whether the solution is clear to u or not...
Ramprasad
#########################################################################
CMY wrote: Hi Ramprasad,
Sorry to trouble you again.
I don't quite understand point no. 4 where you say get the table ID.
How do I actually get the table ID ?
And for point no. 5, how do i reset the earlier contents ?
Thanks for your patience and help.
################################################################
>
Hi,
I think Below way is best thing to solve your problem. Please
check the steps...
1. Connect to database
2. Get the Table Informations as below ......
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
3. Fill data from the Query to -Tables dropdownlist and value of
dropdownlist is 'id' which is fetched from above query
4. On selection of Perticular table Get the 'id' value in ur
ddlTable_SelectedIndexChanged event and use below query to get the
columns for the table selected.
strsql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & intid
5. fill the data in columns dropdownlist.(Reset the earlier contents
before filling the columns data to dropdownlist.)
Hope this will solve u r problem..
Let me know if u have any query...
Ramprasad. S.U
######################################################################
CMY wrote: Hi...thanks for the help....
but my problem is like below:
I have 2 drop down list..the 1st one (ddlTable) is to list out all the
tables in my database and the 2nd one (ddlColumn) is suppose to list out
all the related columns name for tat particular table selected so that my
user will know what are the columns available for that table.
in my ddlTable_SelectedIndexChanged sub, i do the following code :
Dim ds As New DataSet()
Dim myCommand As String
myCommand = "select * from " & ddlTable.SelectedItem.Text
Dim da As SqlDataAdapter
da = New SqlDataAdapter(myCommand, conn)
da.Fill(ds)
Dim col As DataColumn
For Each col In ds.Tables(0).Columns
ddlColumn.Items.Add(col.ColumnName)
Next
rite now the first time I select a table from ddlTable, it will give me
the correct corresponding column name in ddlColumn.
but when i select another table, it will append the column names of the
2nd table and displayed together with the 1st table's column names in
ddlColumn.
so, how do I actually get rid of the previous dataTable in the dataSet ?
or is there a better way to solve this problem ?
Pls help, thanks a lot.
>
Hi,
Yes , u can use a dataadapter to store more than one tables as
below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders;
select OrderID ,Products.ProductName,[order Details].Unitprice,[order
Details].Quantity,[order Details].discount from [order Details],Products
where [Order Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
WebForm1
Tables
Columns
text/microsoft-resx
1.0.0.0
System.Resources.ResXResourceReader, System.Windows.Forms,
Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Resources.ResXResourceWriter, System.Windows.Forms,
Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents DropDownList2 As
System.Web.UI.WebControls.DropDownList
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim daNex As New SqlDataAdapter()
Dim con As New SqlConnection()
Dim strSql As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
DropDownList1.Attributes("OnChange") = "fnSmt();"
con = New SqlConnection("Data Source=sparepc;Initial
Catalog=derivetest;User ID=sa;Password=;")
con.Open()
If Request("ctx") = "Fill" Then
Dim dsCols As New DataSet()
strSql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" &
DropDownList1.SelectedItem.Value
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsCols, "sysobjects")
DropDownList2.DataSource = dsCols
DropDownList2.DataTextField = dsCols.Tables(0).Columns("name").ToString
DropDownList2.DataBind()
Else
Dim daNex As New SqlDataAdapter()
Dim dsTables As New DataSet()
strSql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name"
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsTables, "sysobjects")
'FILL THE DATA TO DROPDOWNLIST
DropDownList1.DataSource = dsTables
DropDownList1.DataTextField = dsTables.Tables(0).Columns("name").ToString
DropDownList1.DataValueField = dsTables.Tables(0).Columns("id").ToString
DropDownList1.DataBind()
End If
End Sub
End Class
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
Message #10 by "CMY" <my62202@y...> on Mon, 30 Sep 2002 08:35:14
|
|
Hi,
Yup, your explaination is clear to me.
Again, thanks a lot for the help.
#############################################
>
Hi,
1. dataTextField is the text which is going to be displayed in
dropdownlist but dataValueField will not be displayed on u r
dropdownlist but used to store the unique value for the data selected(
unique value for dataTextField , in our case id is unique one which
identify perticular table name).
2. The parameter 'sysobjects' u specify for dataadapter fill property,
like daNex.Fill(dsCols, "sysobjects") will not make any thing , This is
Just for recognizing table .
U can make it as daNex.Fill(dsCols, "syscolumns")
are u clear..
Mail me back
Ramprasad
CMY wrote:Hi Ramprasad,
Thanks a lot....I followed the coding u taught me and IT WORKS !!
Really thank you for the help and appreciate it....
Just to clarify some of my doubts and queries,
1) 1st to get the table names, we do "select name, id from sysobject...."
and then assign the name to dataTextField of dropDownList1 and assign
the id to dataValueField of dropDownList1. What is the difference
between dataTextField and dataValueField ?
2) In your second strSql, u put "select name from syscolumns...." and then
you fill the dataAdapter with : daNex.Fill(dsCols, "sysobjects")
Isn't it suppose to be daNex.Fill(dsCols, "syscolumns") ?
####################################################
>
attchment was included in the mail itself... check this file
ramprasad upadhyaya wrote:
Hi,
Findin attachment(aspx and codebehind simply copy all the 3 files and
change the connection string) for the complete solution of u r
problem...Send me the feedback
Ok..... First i will clarify the point no 4.
4. U need not have to get the id again. U have allready got that in below
query in Point no-2
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
Mail me back , I want to know whether the solution is clear to u or not...
Ramprasad
#########################################################################
CMY wrote: Hi Ramprasad,
Sorry to trouble you again.
I don't quite understand point no. 4 where you say get the table ID.
How do I actually get the table ID ?
And for point no. 5, how do i reset the earlier contents ?
Thanks for your patience and help.
################################################################
>
Hi,
I think Below way is best thing to solve your problem. Please
check the steps...
1. Connect to database
2. Get the Table Informations as below ......
strsql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name "
3. Fill data from the Query to -Tables dropdownlist and value of
dropdownlist is 'id' which is fetched from above query
4. On selection of Perticular table Get the 'id' value in ur
ddlTable_SelectedIndexChanged event and use below query to get the
columns for the table selected.
strsql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" & intid
5. fill the data in columns dropdownlist.(Reset the earlier contents
before filling the columns data to dropdownlist.)
Hope this will solve u r problem..
Let me know if u have any query...
Ramprasad. S.U
######################################################################
CMY wrote: Hi...thanks for the help....
but my problem is like below:
I have 2 drop down list..the 1st one (ddlTable) is to list out all the
tables in my database and the 2nd one (ddlColumn) is suppose to list out
all the related columns name for tat particular table selected so that my
user will know what are the columns available for that table.
in my ddlTable_SelectedIndexChanged sub, i do the following code :
Dim ds As New DataSet()
Dim myCommand As String
myCommand = "select * from " & ddlTable.SelectedItem.Text
Dim da As SqlDataAdapter
da = New SqlDataAdapter(myCommand, conn)
da.Fill(ds)
Dim col As DataColumn
For Each col In ds.Tables(0).Columns
ddlColumn.Items.Add(col.ColumnName)
Next
rite now the first time I select a table from ddlTable, it will give me
the correct corresponding column name in ddlColumn.
but when i select another table, it will append the column names of the
2nd table and displayed together with the 1st table's column names in
ddlColumn.
so, how do I actually get rid of the previous dataTable in the dataSet ?
or is there a better way to solve this problem ?
Pls help, thanks a lot.
>
Hi,
Yes , u can use a dataadapter to store more than one tables as
below..
Dim OrderAdapter As New SqlDataAdapter("Select top 10 * from Orders;
select OrderID ,Products.ProductName,[order Details].Unitprice,[order
Details].Quantity,[order Details].discount from [order Details],Products
where [Order Details].ProductId=Products.ProductID", OrdersConnection)
Dim OrderDataSet As New DataSet()
OrderAdapter.Fill(OrderDataSet)
OrderDataSet.Tables(0).TableName = "Orders"
OrderDataSet.Tables(1).TableName = "Order Details"
Thats all .. too simple...
CMY wrote:Hi all,
Pls help to enlighten me on this little problem...thanks...
It is known that to store multiple tables, we need to use a new
dataAdapter object for each DataTable object in a DataSet object.
Can we use the same DataAdapter to store to store a few DataTable in the
same DataSet ?
And can the previous DataTable be cleared off the values each time a new
DataTable is stored into the DataSet ?
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
WebForm1
Tables
Columns
text/microsoft-resx
1.0.0.0
System.Resources.ResXResourceReader, System.Windows.Forms,
Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
System.Resources.ResXResourceWriter, System.Windows.Forms,
Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents DropDownList2 As
System.Web.UI.WebControls.DropDownList
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim daNex As New SqlDataAdapter()
Dim con As New SqlConnection()
Dim strSql As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
DropDownList1.Attributes("OnChange") = "fnSmt();"
con = New SqlConnection("Data Source=sparepc;Initial
Catalog=derivetest;User ID=sa;Password=;")
con.Open()
If Request("ctx") = "Fill" Then
Dim dsCols As New DataSet()
strSql = "SELECT name FROM syscolumns(NOLOCK) WHERE id=" &
DropDownList1.SelectedItem.Value
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsCols, "sysobjects")
DropDownList2.DataSource = dsCols
DropDownList2.DataTextField = dsCols.Tables(0).Columns("name").ToString
DropDownList2.DataBind()
Else
Dim daNex As New SqlDataAdapter()
Dim dsTables As New DataSet()
strSql = "SELECT name,id FROM sysobjects(NOLOCK) WHERE type='U' AND
name<>'dtproperties' ORDER BY name"
daNex = New SqlDataAdapter(strSql, con)
daNex.Fill(dsTables, "sysobjects")
'FILL THE DATA TO DROPDOWNLIST
DropDownList1.DataSource = dsTables
DropDownList1.DataTextField = dsTables.Tables(0).Columns("name").ToString
DropDownList1.DataValueField = dsTables.Tables(0).Columns("id").ToString
DropDownList1.DataBind()
End If
End Sub
End Class
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
---
Beginning ASP.NET Databases using VB.NET
http://www.wrox.com/ACON11.asp?ISBN=1861006195
Beginning ASP.NET Databases using C#
http://www.wrox.com/ACON11.asp?ISBN=1861007418
These books look at how we can create data-centric ASP.NET
applications. Requiring some basic knowledge of ASP.NET,
Access and SQL the authors guide you through the process
of connecting and consuming information in a variety of
ways. They are packed full of excellent illustrative code
examples, demonstrating important fundamental principles.
Regards,
Ramprasad Upadhyaya
---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
|
|
|
View Cart
