|
This will only work if there are no more than 1 record in qtnmaster per itemcode.
|
|
|
|
|
(Posted on c# forum - better suited to DB)
I have an Access db with a number of related tables.
Some tables have a boolean column, eg HTReadOnly, to determine whether it is a system row (true) or not (false).
If HTReadOnly is true the user cannot edit the table row.
In a databound DataGridView, if the user chooses to Edit a row I need to check the underlying table column value, HTReadOnly for the current row and allow editing if false.
My current solution, using static methods, although reusable for different DataGridViews, requires some manual checking of design time values to protect against run time errors. eg. DataRow,column name and column type are valid etc
If I could get the row index of the table row matching the DataGridViewRow I could use...
<pre>if(!MyDataSet.MyTable1[pos].HTReadOnly ) {
else {
The benefit of this method (in my mind) is the IDE and intellisense will provide the table names and check the type etc. So I know they are correct.
I tried to get pos from....
int pos = MyTable1bindingSource.Position;
however this seems to return the position of the row in the DataGridView not the table, as it doesn't match the table row when DGV sorted.
I can't seem to find a method or property for either, DataGridView, bindingSource, or TableAdapter that will return the record row index matching the currently selected DataGridViewRow.
Any Ideas?
Should I just be content getting the value of the DataGridView current row cell for my boolean value?
I read somewhere that it is better to get the actual data rather than the data loaded into the DGV, not sure why?
My current method utilizes various statics methods with
DataGridViewRow dgvr = dgv.CurrentRow;
DataRowView drv = (DataRowView)dgvr.DataBoundItem;
to be able to get the column value
eg retVal = (bool)drv.Row[colName] after various tests.
Any help would be appreciated.
Thanks...
modified on Wednesday, January 13, 2010 1:54 AM
|
|
|
|
|
ok, I'm game to take a shot at this, IMHO your design of allowing the user to edit into the DGV is wrong, in all but the most trivial cases I force the user to double click the list control item (DGV, listview etc) and pop a dialog that allows the crud work to be done on the single row. Trap the results from the dialog and reload the list control if the data has been modified/deleted.
The double click event will give you the id from the datarow.
Having said that have you tried the cellformat event to deal with each row as it is displayed. DGV has a myriad of event and you need to get the one that will allow you to identify the row and make it read only before the user gets at it. Search for alternate row colouring and you will see the events that can be adopted for this.
I assume your Boolean flag is in the DGV, probably a hidden column!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply!
To expand a bit further...
I have set up the DGV so that no edits , updates etc can be done in the DGV.
So the whole DGV is ReadOnly.
I am providing the edit/add fields ( as databound textboxes etc )separately elsewhere on the form.
These normally display a bit more info than the DGV itself, and are all ReadOnly unless an Edit button (or row double click - yet to be done) is clicked.
If the Boolean flag (as you said,in a hidden column) allows edits, I change all the appropriate TextBox.ReadOnly values to allow changes there.
(There are some rows that I don't want the user ever to edit or delete)
(1)So, if it is considered to be best practice to get the column value from the table itself rather than the DGV , I need to get the row index of the table that matches the current row in the DGV.
(2) Also being able to use MyDataSet.MyTable1[pos].HTReadOnly ) syntax allow for error checking in the IDE in case of wrong column name or wrong type etc
Otherwise I can get the DGV row/column value from ( off the top of my head) with
DataGridViewRow dgvr = table1DataGridView.CurrentRow;
DataRowView drv = (DataRowView )dgvr.DataBoundItem;
bool isReadOnly = drv.Row["htreadonly"];
By using drv.Row["htreadonly"]; am I in fact looking directly at the data?
If so it leaves only point (2) which I think is significant instead of coding (as at present) to catch runtime errors.
Hope this serves to clarify, not confuse....
LJL
|
|
|
|
|
I am not sure I agree with (1) that you should be getting the data from the underlying datatable, I don't distinguish between the 2 as a datasource for editing. Then again I never edit into the underlying datasource.
AussieLew wrote: I am providing the edit/add fields ( as databound textboxes etc )separately elsewhere on the form
When you get he data for this do you use the DGV row or go back to the database get a row to bind to the textboxes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I am not sure I agree with (1) that you should be getting the data from the underlying datatable,
You are probably right, unless there is a reason they may not always match?
Something I read somewhere that made sense at the time!
I guess also that this would become more unwieldy in the case of a DGV based on multi table queries?
(That one is coming up soon..)
Mycroft Holmes wrote: When you get he data for this do you use the DGV row or go back to the database get a row to bind to the textboxes.
The textboxes etc are bound to the same bindingSource as the DGV's DataSource and change as the DGV row is selected etc.
I worked out how I can do what I (feel I) want to do. I needed to go one more level and get the DataRow.
DataGridViewRow dgvr = MyTable1DataGridView.CurrentRow;
DataRowView drv = (DataRowView)dgvr.DataBoundItem;
DataRow dr = (DataRow)drv.Row;
int pos = dr.Table.Rows.IndexOf(dr);
bool boolColumnValue = MyDataSet.MyTable1[pos].HTReadOnly;
if (boolColumnValue)
{
}
else
{
}
Ah...It's been an interesting exercise, learnt a lot..There's nothing like a real life project for giving incentive to learn.
Now all I need to determine is the "best" way to do the updates and deletes etc. There seems to be many classes where this can done.
Thanks for your help, it's been fun!
LJL
|
|
|
|
|
Your challenge now is to get the bool check to one line/statement. Breaking the steps up is the correct way to go when understanding the process of achieving a goal, well done. Then finding and understanding the correct syntax that achieves the 5 lines of code in 1 statement is the real bonus !
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Say if i have 100 rows, if i need top 15 rows i'll use,
select top 15 * from table1
if i need from row 25 to 50, how will i select!!!!
|
|
|
|
|
You can use row_number() over the sort field of your choice as a secondary query. In the following I have 1 select gets me the currencyID and the row number and joins that query to the select of the currency table. I can then filter of the RN field in the row_number query
SELECT *
FROM Currency C
INNER JOIN
(SELECT CurrencyID, ROW_NUMBER() OVER (ORDER BY CurrencyID) RN FROM currency) R
ON R.currencyid = C.CurrencyID -- Join back to the currency table
WHERE R.RN BETWEEN 6 AND 10
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanx man...i'll try that...
|
|
|
|
|
Hi All
How can i get list of database in oracle(Like mysql "Show databases" command)?Plz help me
|
|
|
|
|
Did you think to ask Google[^]before posting, it should be one of your first resources!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oracle is quite different to MSSql in that aspect. Databases in Oracle are really quite separated from each other.
So I suggest that you read up on schemas and tablespaces, and eventually database links in Oracle.
|
|
|
|
|
Hello,
I want to write a stored procedure in SQL wich returns last 12 weeks list (including current week) with start date.
e.g.
01: 04 Jan 10
53: 28 Dec 09
52: 21 Dec 09
51: 14 Dec 09
:
:
P.S. My first day of week is Monday so first week of year 2010 will start from 4 Jan 2010 and week starting from 28 Dec will be wk 53.
I am doing something like this
ALTER PROCEDURE [dbo].[GetWeekNumberList]
@IncludeCurrentWeek BIT = NULL
AS
BEGIN
SET DATEFIRST 1;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #tempWeekList(weekDate DATETIME, weekLabel VARCHAR(25))
DECLARE @Counter INT,
@StartDate DATETIME,
@EndDate DATETIME
IF @IncludeCurrentWeek IS NOT NULL AND @IncludeCurrentWeek = 1
BEGIN
SET @StartDate = DATEADD(dd, (DATEPART(dw, GETDATE()) * -1) + 1, GETDATE())
END
ELSE
BEGIN
SET @StartDate = DATEADD(dd, (DATEPART(dw, DATEADD(dd, -7, GETDATE())) * -1) + 1, DATEADD(dd, -7, GETDATE()))
END
SET @EndDate = DATEADD(dd, 4, @StartDate)
SET @Counter = 1
WHILE @Counter <= 12
BEGIN
INSERT INTO #tempWeekList
VALUES (@EndDate, CAST(DATEPART(wk, @EndDate) AS VARCHAR) + ': ' + CONVERT(VARCHAR, @StartDate, 6))
SET @StartDate = DATEADD(dd, -7, @StartDate)
SET @EndDate = DATEADD(dd, 4, @StartDate)
SET @Counter = @Counter + 1
END
SELECT * FROM #tempWeekList
DROP TABLE #tempWeekList
END
I am not able to get the 53 as last week. How to do it?
Any idea or help will be very useful to me.
Thanks.
|
|
|
|
|
Not all years have a week 53, only in the 1-1 in that weeks is thursday or later
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Hi, I have a table with 1000 stock tickers and their historical prices. Today I was trying to write a simple query to retrieve all the stocks with 11/20/2009 prices greater than their 11/17/2009 prices. However my query returned an error saying, "Subquery returned more than 1 value, this is not permitted when the subquery follows .....".
I think I know what the problem is, however I am not sure how to fix this. Is there an alternative query I can write to get around this?
Thanks!!
Below's my current query:
modified on Monday, January 11, 2010 10:52 AM
|
|
|
|
|
You might be having something like this:
select columns from tablename where somecolumn = (select someColumn1 from sometableone)
Now, that subquery is returning multiple values and hence "=" won't work. Replace "=" by "in" or check the sub query if it should return only one value.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thanks Danish, I've posted in a reply thread below. The operator I am using is a ">" instead of a "=", so using "IN" probably won't work for me. Could you please let me know how I can get around this problem?
I'm not really sure why subquery would still return more than 1 result, since I've specified that Table.Ticker = t2.Ticker and Date = a specific date in the subquery, thus it should only return the price on that specific date for that specific stock....
Thanks in advance!
|
|
|
|
|
Show us what you have and someone will help.
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
Last night I had some trouble pasting the codes properly in the forum, fortunately the problem is fixed now. Yes I currently have:
SELECT Ticker FROM Table WHERE
Date = '11/20/2009' AND
Price > (Select Price from Table WHERE Date = '11/17/2009')
|
|
|
|
|
You can use a join similar to this one:
select a.ticker from table as a inner join table as b
on a.ticker=b.ticker and a.price>b.price
where a.date='11/20/2009' and b.date='11/17/2009'
group by a.ticker;
|
|
|
|
|
Thank you Dxlee, not only that it works, I learned a new way of using inner join table! I used to think inner join is only for joining two different tables, but now I see that you can use it to link to the same table filtered for some criterias. This is going to change the way I write queries from now on
Could you please recommend a good online source where I can learn how to use SQL more effectively? Using INNER JOIN in this manner (seems creative and ingenious to me) was not covered in W3Schools nor in most SQL tutorial websites. Many thanks again!
|
|
|
|
|
I think d@nish's solution is better for your case, even though you found my inner join to be new and helpful to you.
I am sorry I don't have a book or website to recommend to you on SQL. I learned something about SQL by reading an Oracle manual on SQL*Plus (which has lots of very interesting examples) about 20 years ago (in 1990) when I was working on a project for a friend. I moved a few times (to different countries) ever since and lost that manual. It was for an old version of Oracle anyway (which ran on VMS with no graphical user interface.)
The new Oracle manuals seem to have nothing similar to the one I read. Your best bet is to find a good book on SQL.
|
|
|
|
|
You must be having multiple prices for that date. If you need the top record only, use order by and top 1 in you statement.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thank you D@nish for the tip, yes indeed there are multiple prices on same dates belonging to a ticker, though that shouldn't happen. Bad data, may need to redownload.
Top 1 solves this for now. Many thanks again.
|
|
|
|