This is probably a simple question but I want to make sure I am doing
it right.
I have a spoc with two select statements which results in two tables.
Very Basic
---------------------------------------------------------------------------------
@strProductCode nvarchar (50)
select *
from tblProducts
where strproductCode = @strProductCode
select * from tblJctProductCo lors
-----------------------------------------------------------------------------------
I will eventually set up a relationship between the two but first I
need to get them into one dataset as two tables. I am not sure of the
code. I have looked through past posts but I am still confused. Can
someone help. This is what I have so far.
-------------------------------------------------------------------------------------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
'retreive the prodcut code
Dim strProductCode = Request.QuerySt ring("ProductCo de")
'create the connection string
Dim connection As New SqlConnection(c onnectionString )
'Create and initialize the command Object
Dim command As New SqlCommand("New _getDetailsTabl es",
connection)
command.Command Type = CommandType.Sto redProcedure
' Add an input parameter and supply a value for it
command.Paramet ers.Add("@strPr oductCode", SqlDbType.VarCh ar,
50)
command.Paramet ers("@strProduc tCode").Value = strProductCode
Dim mySqlDataAdapte r As SqlDataAdapter = New
SqlDataAdapter( command)
Dim dsGetProducts As New DataSet
mySqlDataAdapte r.Fill(dsGetPro ducts, "Products")
DataList1.DataS ource = dsGetProducts.T ables("products ")
DataList1.DataB ind()
End Sub
I have got the dataset returning values from the first table in the
sproc but I need values from the second table.
I would like to have two tables from in the dataset. Products and
colors. The first select statement is products and the second is
colors. Can someone advise?
Thanks for any help!
Laura K 6 2468
your code is currently loading two tables. the first table "Products"
contrain the data from tblProduct. the second table "Products_1 " (auto
named) contains the data from the table tblJctProductCo lors. you can rename
the second table after the fill.
-- bruce (sqlwork.com)
"Laura K" <kl*****@charte r.net> wrote in message
news:11******** *************@j 33g2000cwa.goog legroups.com... This is probably a simple question but I want to make sure I am doing it right.
I have a spoc with two select statements which results in two tables.
Very Basic
--------------------------------------------------------------------------------- @strProductCode nvarchar (50)
select * from tblProducts where strproductCode = @strProductCode
select * from tblJctProductCo lors
-----------------------------------------------------------------------------------
I will eventually set up a relationship between the two but first I need to get them into one dataset as two tables. I am not sure of the code. I have looked through past posts but I am still confused. Can someone help. This is what I have so far.
------------------------------------------------------------------------------------------------- Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Load 'retreive the prodcut code Dim strProductCode = Request.QuerySt ring("ProductCo de")
'create the connection string Dim connection As New SqlConnection(c onnectionString ) 'Create and initialize the command Object Dim command As New SqlCommand("New _getDetailsTabl es", connection) command.Command Type = CommandType.Sto redProcedure
' Add an input parameter and supply a value for it command.Paramet ers.Add("@strPr oductCode", SqlDbType.VarCh ar, 50) command.Paramet ers("@strProduc tCode").Value = strProductCode
Dim mySqlDataAdapte r As SqlDataAdapter = New SqlDataAdapter( command) Dim dsGetProducts As New DataSet mySqlDataAdapte r.Fill(dsGetPro ducts, "Products")
DataList1.DataS ource = dsGetProducts.T ables("products ") DataList1.DataB ind()
End Sub
I have got the dataset returning values from the first table in the sproc but I need values from the second table.
I would like to have two tables from in the dataset. Products and colors. The first select statement is products and the second is colors. Can someone advise?
Thanks for any help!
Laura K
Try a union join.
e.g.
SELECT * FROM Table1
UNION
SELECT * FROM Table2
"Laura K" <kl*****@charte r.net> wrote in message
news:11******** *************@j 33g2000cwa.goog legroups.com... This is probably a simple question but I want to make sure I am doing it right.
I have a spoc with two select statements which results in two tables.
Very Basic
--------------------------------------------------------------------------------- @strProductCode nvarchar (50)
select * from tblProducts where strproductCode = @strProductCode
select * from tblJctProductCo lors
-----------------------------------------------------------------------------------
I will eventually set up a relationship between the two but first I need to get them into one dataset as two tables. I am not sure of the code. I have looked through past posts but I am still confused. Can someone help. This is what I have so far.
------------------------------------------------------------------------------------------------- Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Load 'retreive the prodcut code Dim strProductCode = Request.QuerySt ring("ProductCo de")
'create the connection string Dim connection As New SqlConnection(c onnectionString ) 'Create and initialize the command Object Dim command As New SqlCommand("New _getDetailsTabl es", connection) command.Command Type = CommandType.Sto redProcedure
' Add an input parameter and supply a value for it command.Paramet ers.Add("@strPr oductCode", SqlDbType.VarCh ar, 50) command.Paramet ers("@strProduc tCode").Value = strProductCode
Dim mySqlDataAdapte r As SqlDataAdapter = New SqlDataAdapter( command) Dim dsGetProducts As New DataSet mySqlDataAdapte r.Fill(dsGetPro ducts, "Products")
DataList1.DataS ource = dsGetProducts.T ables("products ") DataList1.DataB ind()
End Sub
I have got the dataset returning values from the first table in the sproc but I need values from the second table.
I would like to have two tables from in the dataset. Products and colors. The first select statement is products and the second is colors. Can someone advise?
Thanks for any help!
Laura K
Hello Laura,
If you have a stored procedure with two select statements in it:
=-=-=-=-
....
select *
from tblProducts
where strproductCode = @strProductCode
select * from tblJctProductCo lors
....
=-=-=-=-
and you call SqlDataAdapter. Fill(dataSet), you will find there are two
tables (rowsets) returned upon return from the Fill() method (provided both
select statements return data. If you want to examine the DataSet, you can
set a breakpoint and open a watch window to verify the contents of the
dataSet object.)
You can then name the tables and specify the relationship between the tables
using syntax similar to:
=-=-=-=-=-=-
dataSet.Table[0].TableName = "Products";
dataSet.Table[1].TableName = "Colors";
dataSet.Relatio ns.Add("Product Color",
dsTables["Products"].Columns["ProductID"],
dsTables["ProductCol or"].Columns["ColorID"]);
=-=-=-=-=-=-
Hope this helps.
--
brians http://www.limbertech.com
"Laura K" wrote: This is probably a simple question but I want to make sure I am doing it right.
I have a spoc with two select statements which results in two tables.
Very Basic
--------------------------------------------------------------------------------- @strProductCode nvarchar (50)
select * from tblProducts where strproductCode = @strProductCode
select * from tblJctProductCo lors
-----------------------------------------------------------------------------------
I will eventually set up a relationship between the two but first I need to get them into one dataset as two tables. I am not sure of the code. I have looked through past posts but I am still confused. Can someone help. This is what I have so far.
------------------------------------------------------------------------------------------------- Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles MyBase.Load 'retreive the prodcut code Dim strProductCode = Request.QuerySt ring("ProductCo de")
'create the connection string Dim connection As New SqlConnection(c onnectionString ) 'Create and initialize the command Object Dim command As New SqlCommand("New _getDetailsTabl es", connection) command.Command Type = CommandType.Sto redProcedure
' Add an input parameter and supply a value for it command.Paramet ers.Add("@strPr oductCode", SqlDbType.VarCh ar, 50) command.Paramet ers("@strProduc tCode").Value = strProductCode
Dim mySqlDataAdapte r As SqlDataAdapter = New SqlDataAdapter( command) Dim dsGetProducts As New DataSet mySqlDataAdapte r.Fill(dsGetPro ducts, "Products")
DataList1.DataS ource = dsGetProducts.T ables("products ") DataList1.DataB ind()
End Sub
I have got the dataset returning values from the first table in the sproc but I need values from the second table.
I would like to have two tables from in the dataset. Products and colors. The first select statement is products and the second is colors. Can someone advise?
Thanks for any help!
Laura K
Great it is coming together. I have the tables under control. Now I am
working on the relationship. I have received the following error
T"hese columns don't currently have unique values."
My updated code is as follows:
Dim dsGetProducts As New DataSet
mySqlDataAdapte r.Fill(dsGetPro ducts, "Products")
dsGetProducts.T ables(0).TableN ame = "Products"
dsGetProducts.T ables(1).TableN ame = "Colors"
dsGetProducts.R elations.Add("P roductsToColors ",
dsGetProducts.T ables("products ").Columns("int ProductID"),
dsGetProducts.T ables("colors") .Columns("intPr oductID"))
Now this is a parent child relationship. The product table returns a
list of items which match the productcode. ProductID is the PK. The
color table has productID as a FK. and should return a list of colors
for each productID.
I am obviously missing something.
Thanks for the help so far. I see a light at the end of the tunnel
---------
Laura K
Figured the error out. Added a ,false to the relationship and all is
well...so far. Thanks for all the help.
Good you have seen a light at the end of the tunnel :)
Patrick
"Laura K" <kl*****@charte r.net> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. . Figured the error out. Added a ,false to the relationship and all is well...so far. Thanks for all the help. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Stanav |
last post by:
Hello all,
I'm developing a web application using VB.Net 2003 and Framework 1.1. This
application queries an AS/400 database. I'm using the IBM OleDb provider
that came with IBM Client Access for Windows (V5R3). Everything works fine
on my development PC, but when I move the application to a Windows Server
2003, it crashes when trying to fill a dataset. I've double-checked that the
Win 2k3 server does have Client Access installed, that it...
|
by: Dave Edwards |
last post by:
I understand that I can fill a datagrid with multiple queries, but I cannot
figure out how to fill a dataset with the same query but run against
multiple SQL servers, the query , table structure and username, password etc
will be exactly the same for each server, the only thing that will change is
the server name. Idealy I would like to get the server names from a seperate
dataset so there could be any number of servers, allthough in...
|
by: Nikolay Petrov |
last post by:
How to fill dataset with multiple tables and set their relaition?
Can I get the relations from the SQL server?
Also I would like to do it using stored procedures.
TIA
|
by: scottin |
last post by:
I setup the dataset, which is the datasource for a datagrid.
When I originally fill the dataset, the data is loaded into
dataset.tables(0). (dataset.tables(0).rows.count = 8)
Then, if a row gets added to the table, I want to refresh the dataset.
When I do so, the data is loaded into dataset.tables(1).
(dataset.tables(0).rows.count = 0, dataset.tables(1).rows.count = 8)
(We can go into why the record isn't added later)
|
by: Stanav |
last post by:
Hello all,
I'm developing a web application using VB.Net 2003 and Framework 1.1. This
application queries an AS/400 database. I'm using the IBM OleDb provider
that came with IBM Client Access for Windows (V5R3). Everything works fine
on my development PC, but when I move the application to a Windows Server
2003, it crashes when trying to fill a dataset. I've double-checked that the
Win 2k3 server does have Client Access installed, that it...
| |
by: dauphian |
last post by:
Hello,
I am new to .net and am trying to build a report application that
queries 4 different tables based on a id, and I need to return them in
the same table for easy viewing.
Basically, I have one querie that grabs all of the id's I need for the
other 4 queries, but I am not sure how to get them into a DataTable or
DataSet, or if that is the best way to do this. Seperately the queries
all work with no problems.
|
by: MDB |
last post by:
Hello All,
I have a data grid that I fill using a dataset. The results of the query
has around 15 columns and 500 rows (and growing). The reason I am using the
datagrid is so the end users can auto sort and page so I would like to keep
using the datagrid. My problem is that when I call DataAdapter.Fill, it
takes a very long time and is even causing the web page to time out. Does
anyone know why the fill is taking so long (15-20 sec)...
|
by: Monty M. |
last post by:
Hello;
I was wondering if anyone can assist me with this problem. Here are
the tools I am using:
Language: C#
Database: MS SQL Server 2000
Application: Visual Studio 2005
1. I have a table whose primary key is a varchar data type.
|
by: slinky |
last post by:
I'm getting a error when I open my . aspx in my browser... line 34:
da.Fill(ds, "Assets")
Here's the error and my entire code for this .aspx.vb is below
that ...
I need some clues as to what is causing the error... Thanks!!!
Server Error in '/' Application.
--------------------------------------------------------------------------------
Multiple-step OLE DB operation generated errors. Check each OLE DB
|
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...
|
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...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |