|
Hi
Don't know what's wrong with my code(below), it runs but doesn't return any childrows( however exists -childrows.length=0- & identified by parent rows ID). Dataset was created by wizard, table & nested table in the dataset browser exist. I don't have any idea left... Thanks for your posts in advance, g
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
if (Validate() && uzemanyagBindingSource != null)
{
bool deleteRow = true;
DataRowView rowView = uzemanyagBindingSource.Current as DataRowView;
if (rowView == null)
{ return; }
Debmut9DataSet.UzemanyagRow row = rowView.Row as Debmut9DataSet.UzemanyagRow;
Debmut9DataSet.GepRow[] ChildRows = row.GetGepRows();
if (ChildRows.Length > 0)
{
DialogResult userChoice = MessageBox.Show("All records in this & realted table will be deleted.Continue?", "Delete...", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (userChoice == DialogResult.Yes)
{
foreach (Debmut9DataSet.GepRow childuzemanyag in ChildRows)
{
childuzemanyag.Delete();
}
}
else
{ deleteRow = false; }
}
if (deleteRow)
uzemanyagBindingSource.RemoveCurrent();
uzemanyagBindingSource.EndEdit();
Array.Resize(ref uzemanyagregiar, uzemanyagBindingSource.List.Count);
}
|
|
|
|
|
Think I got it
I forgot to fill the child table, upon the load event of the form whose control is bound to parent table.
so the classic: childtableadapter.fill (dataset.childtable)
|
|
|
|
|
Hi Guys and Gals.
I have the following (sample) info in a table in SQL
1 a 10
1 b 20
1 c 30
1 d 40
1 e 50
1 f 60
2 a 15
2 b 25
2 c 35
2 d 45
2 e 55
2 f 65
3 a 60
3 b 50
3 c 40
3 d 30
3 e 20
3 f 10
I need it in the following format
a b c d e f
1 10 20 30 40 50 60
2 15 25 35 45 55 65
3 60 50 40 30 20 10
Does anyone know of a quick and easy way that's not too painfull? It will be greatly appreciated.
Regards,
Elizma
|
|
|
|
|
If you are using SQL Server 2005, you may want to take a look at the PIVOT command. Here's[^] an example.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanx. It worked beautifully. Now the problem is, this is hard coded, which means in reality I have 30 "fields" that must be hard coded. What happens when we add another "field" in our table then we must go edit the script again.
Is there no way that I can read the field values in a loop maybe? Just wondering. I know how to do this type of goodies in C#, but it's been a while since I last played intensly with SQL.
Advise will be greatly appreciated.
Regards,
Elizma
|
|
|
|
|
Create table tblMatrix(Column1 int,Column2 char,Column3 int)
Create Procedure sp_Transpose As
DECLARE @SQLQuery AS NVARCHAR(2000)
DECLARE @ColumnName Nvarchar(50)
DECLARE @AddedColumns Nvarchar(500)
DECLARE @AddedValues Nvarchar(500)
DECLARE @Col1Val Nvarchar(50)
DECLARE @Col2Val Nvarchar(50)
DECLARE @Val2Add Nvarchar(50)
declare NavigatorRecords cursor for
SELECT DISTINCT(COLUMN2) FROM TBLMATRIX
open NavigatorRecords
fetch next from NavigatorRecords into @ColumnName
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTemp]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table dbo.tblTemp
SET @SQLQuery = 'CREATE TABLE tblTemp(Col Int,'
SET @AddedColumns = 'Col,'
SET @AddedValues = ''
SET @Val2Add = ''
while(@@FETCH_STATUS = 0)
begin
SET @AddedColumns = @AddedColumns + @ColumnName
SET @SQLQuery = @SQLQuery + @ColumnName + ' ' + 'NVARCHAR(50)'
fetch next from NavigatorRecords into @ColumnName
IF (@@FETCH_STATUS = 0)
begin
SET @SQLQuery = @SQLQuery + ','
SET @AddedColumns = @AddedColumns + ','
end
end
SET @SQLQuery = @SQLQuery + ')'
EXEC sp_executesql @SQLQuery
close NavigatorRecords
deallocate NavigatorRecords
declare CursorI cursor for
SELECT DISTINCT(COLUMN1) FROM TBLMATRIX
open CursorI
fetch next from CursorI into @Col1Val
while(@@FETCH_STATUS = 0)
begin
SET @AddedValues = @Col1Val + ','
declare CursorJ cursor for
SELECT DISTINCT(COLUMN2) FROM TBLMATRIX
open CursorJ
fetch next from CursorJ into @Col2Val
while(@@FETCH_STATUS = 0)
begin
set @Val2Add = '0'
select @Val2Add = Isnull(column3,'0') from tblMatrix where column1 = @Col1Val and column2 = @Col2Val
SET @AddedValues = @AddedValues + @Val2Add
fetch next from CursorJ into @Col2Val
IF (@@FETCH_STATUS = 0)
SET @AddedValues = @AddedValues + ','
end
SET @SQLQuery = 'INSERT INTO tblTemp(' + @AddedColumns +') values(' + @AddedValues +')'
EXEC sp_executesql @SQLQuery
close CursorJ
deallocate CursorJ
fetch next from CursorI into @Col1Val
end
close CursorI
deallocate CursorI
Select * from tblTemp
Go
Exec sp_transpose
Regards J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Plz room, is it posible to mount .mdf files ( sql 2005 ) in sql 2000 maybe personal edition. Thanks
phatkin
|
|
|
|
|
prubyholl wrote: is it posible to mount .mdf files ( sql 2005 ) in sql 2000 maybe personal edition
No, it isn't.
|
|
|
|
|
|
Hi
I have month and year values, I want to get last day of month. How it can be acheived in SQL server 2005?
Shahzad Aslam
Software Engineer
Softech Systems Limited
Cell: +92-321-4606036
Email: shehzadaslam@hotmail.com
|
|
|
|
|
Try one of them......
1.
SELECT Datepart(dd,DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, Getdate())+1, 0)))
2.
Select datepart(dd,dateadd(mm,1,Getdate() - day(Getdate())))
have a nice time........
|
|
|
|
|
Thanx!!!
Shahzad Aslam
Software Engineer
Softech Systems Limited
Cell: +92-321-4606036
Email: shehzadaslam@hotmail.com
|
|
|
|
|
I have created 2 radio buttons on my form but using databinding how i can hendle them?????
Base table name = "Employeer"
Column to be binded = "Gender"
HELP????
|
|
|
|
|
try to set it manually...
radiobutton1.databinding.add("Value", bindinsource("Fields")
|
|
|
|
|
i want to know
when we use sqlserver as backend....
while using reporting
i heard people use msoffice
why is it?
|
|
|
|
|
Hi, I wrote a function in C# to be used as a scalar function in SQL2005:
[Microsoft.SqlServer.Server.SqlFunction(IsPrecise = true, IsDeterministic = true, DataAccess = DataAccessKind.Read)]
public static SqlDecimal GetTaxAmountFromTax(SqlString tax, SqlDecimal amount)
{
SqlConnection conn = new SqlConnection("context connection=true");
SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM TransactionTaxes", conn);
DataTable taxTable = new DataTable();
adap.Fill(taxTable);
DataRow[] rows = taxTable.Select("[ID] = '" + tax.ToString() + "'");
if (rows.Length == 0) return SqlDecimal.Null;
if (rows[0]["CalculatedOnTax"] != DBNull.Value)
{
if ((bool)rows[0]["Cumulative"])
{
DataRow[] rows1 = taxTable.Select("[ID] = '" + rows[0]["CalculatedOnTax"].ToString() + "'");
if (rows1.Length == 0) return SqlDecimal.Null;
return ((amount + (amount * (decimal)rows1[0]["Rate"]) / new SqlDecimal(100))) * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
else return amount * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
else return amount * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
I want to publish this function making sure the return type has a precision of 2 and a scale of 18. I found that I have to use the SqlFacetAttributes but I am unsure how to do it for a function. I found examples only for a Strored Procedure parameter.
Any help appreciated. Txs
|
|
|
|
|
Hi
Using MS Query for Excel connecting to a SQL Server 2000 database through ODBC.
I am trying to get the last purchase Order price for all products
There are two tables containing the info I need:
Table: poheadm (Purchase Order Header)
order_no
date_entered
Table: podetm (Purchase Order Detail)
order_no
product
local_expect_cost (i.e. the price)
The following statement gives an error "Could not add the table "(SELECT."
SELECT podetm.product, podetm.cost
FROM podetm podetm,
(SELECT max(poheadm.date_entered) as maxdate, poheadm.order_no
FROM poheadm poheadm
GROUP BY order_no) maxresults
WHERE podetm.order_no = maxresults.order_no
The following statement runs, but I get multiple results for each product, not just the latest price
SELECT podetm.product, podetm.local_expect_cost,
(SELECT max(poheadm.date_entered)
FROM poheadm poheadm
WHERE poheadm.order_no = podetm.order_no)
FROM podetm podetm
|
|
|
|
|
Your tables are a little confusing. I'm assuming that this is a one to many relationship. That you have one header record and multiple details. Therefore an order could have multiple products on it. So what are you trying to pull back. The latest total order cost, or the latest cost of one product? Perhaps you could give us an example of what exactly you want outputted from the data.
|
|
|
|
|
HI
Yes, one order can have many products. E.g. One entry in the PoHeadm table can have many line Items in Podetm table
Header Table: Poheadm
Fields: |date_eneterd |order_no|
Data: |2007-05-28 |00001 |
Data: |2007-05-29 |00002 |
Detail Table: podetm
Fields: |order_no |local_expec_cost |product|
Data: |00001 | USD5.06 |AA01 |
Data: |00001 | USD1.00 |AA02 |
Data: |00001 | USD9.00 |AA03 |
Data: |00002 | USD7.00 |AA01 |
Data: |00002 | USD2.00 |AA04 |
So above we have TWO Purchase orders. Order 00001, has 3 line items. Order two has two line items.
As you can see, product AA01 is on two purchase orders, I want to create a list of ALL products, and I want the last price paid. So for the above, the data I want returned would look as follows (each product is ONLY ONCE, and has the latest price and date):
|product |local_expect_cost |date_enetered|
|AA01 |USD7.00 |2007-05-29 | (Data from Latest Date Order2)
|AA02 |USD1.00 |2007-05-28 | (Only Ordered once)
|AA03 |USD9.00 |2007-05-29 |
|AA04 |USD2.00 |2007-05-29 |
|
|
|
|
|
I think I got it to work with this:
SELECT product, local_expect_cost, max(date_entered) <br />
<br />
FROM podetm as mainDetail, poheadm as mainHeader<br />
<br />
WHERE mainDetail.order_no=mainHeader.order_no and date_entered=(SELECT max(date_entered) FROM poheadm as subHeader, podetm as subDetail WHERE subHeader.order_no=subDetail.order_no and subDetail.product=mainDetail.product)<br />
<br />
GROUP BY product, local_expect_cost
And just to note, I think you meant to put 2007-05-28 for the date_entered in the AA03 product line of your output.
I hope this works for you.
|
|
|
|
|
Hi Kschuler
I tried it, but it maxed out the processor, and seemed to hang.
I will only be able to look at it again in the morning, but thanks for your reply in the mean time
BTW, the podetm table has about 14000 records, and the poheadm table about 5000 records. Maybe thats why its slow?
-- modified at 11:03 Thursday 30th August, 2007
|
|
|
|
|
Try this.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.order_no
,pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.order_no, pod.product ) t
,podetm pod
where pod.order_no = t.order_no
and pod.product = t.product The temporary query could also be established as a view.
Chris Meech
I am Canadian. [heard in a local bar]
|
|
|
|
|
Tks 4 reply
Tried the statement you suggest, but get the following Error: Could not add the table '('.
|
|
|
|
|
Hi
I also tried pulling the SQL tables into Access and running the same query, but had to change the line:
max(poh.date_entered) last_date
to
max(poh.date_entered) AS last_date ????
But then it returns many records for each product, wheras I only want to return ONE record for EVERY product, and that record must be the once which has the latest date
Maybe I explained what I'm trying to do badly, so if you get a chance, perhaps look at my reply to Kschuler's post as I try to explain what I want in more detail...
|
|
|
|
|
My previous SQL would return too many rows. Try this instead.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.product ) t
,poheadm poh
,podetm pod
where poh.date_entered = t.last_date
and poh.order_no = pod.order_no
and pod.product = t.product
As long as your order header records have unique date time stamps, you should only get one record for each product. But this assumes that on the podetm table that the order_no, product fields are a unique key. If they are not a unique key, it possible that a product would appear twice.
Good Luck.
Chris Meech
I am Canadian. [heard in a local bar]
|
|
|
|
|