|
Colin Angus Mackay wrote:
What tools are we talking about here? Is this in the SQL Server Query Analyser?
Yes, SQL query analyzer.
Colin Angus Mackay wrote:
Also, what sort of commands? INSERT/UPDATE/DELETE or something else?
set @Query = master.dbo.xp_cmdshell bcp stuff
exec (@query)
drop stuff
Colin Angus Mackay wrote:
I'm not entirely sure what you are looking for here. Do you want to see a before and after view of the data?
Anything like that, even just knowing if anything has changed.
Cheers,
Jim
|
|
|
|
|
Finish with a select to show the end result. Otherwise, you'll just get the '1 row affected' type messages.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
First some background:
I am working on a very big project in .NET. I have a client and server applications. I use ADO.NET to manage the data. The server is the only application which access the data source, and it passes the data to the clients using disconnected DataSets. The clients do not have access to the data source. So far I worked with DataGrids very easily, using several custom column styles.
My Problem:
My system engineer wants me to show in one grid columns from several connected tables (exactly like MS-Access queries, or SQL JOIN). What I mean is that I have two tables with a DataRelation defined between them (the child table contains a foriegn key of the parent table), and I want to view columns from both tables in the grid.
The requirement from the grid is that I will be able to edit fields of existing rows (adding new rows by the user is not done in the grid itself, so this feature I do not need). What I also need is that the columns will support custom ColumnStyle.
For Example: I have two tables ORDER and CUSTOMER, and the ORDER table contains a foriegn key CUSTOMER_ID, which is related to the ID field from CUSTOMER table. I want to show both ORDER_DATE (using a DateColumnStyle) and CUSTOMER_EMAIL (using EMAILColumnStyle). Both ColumnStyles are implemented and works perfectly with DataTables and DataViews.
I searched the internet for solutions, and realized that I cannot show in one grid columns from several DataTables. I can't apply SQL JOIN statements on DataSets (only on data sources, and using DataAdapters which I don't have in my clients). I almost gave up (I was quite surprised how little material there is on the subject, because it looks to me as this is a very simple feature and a must in data-oriented applications).
The only solution I found was the JoinView class from http://support.microsoft.com/kb/325682/EN-US/. I read about the limitations published on the site and tried the object. It works! As long as you want a ReadOnly view of the data. If you want to edit columns, only the main table (the child) can be edited - columns from other tables are read only (Which I guess I can live with, though I don't want to). But trying to apply custom ColumnStyles fails. Giving MappingNames to the ColumnStyles fails, and the grid still looks the same as before (without the ColumnStyles). As I said my custom ColumnStyles work for DataView.
Does anyone have a solution to the very common problem (using JoinView or not)of viewing to joined tables in a grid? Does anyone have actual exprerience with JoinView and can tell if it is any good?
|
|
|
|
|
a dataGrid.SelectedRowIndex gives information about the selected row from the grid which corresponds to the same row in the table that the grid is bound to.
this is only true if data on the dataGrid is sorted as this of the sourceTable, ...how can i find the correct RowIndex if the user sorts the grid in a different way then select any row (in this case the SelectedRowIndex will not match the one in the sourceTable form the dataSet).
if there is a way to figure this out, or work around ... please tell me.
thanx
|
|
|
|
|
Hello,
I have a table Order. There is a field called "Symbol", another field
called "Price",and several other fields. Now I want to select the
minimum price records with distinct "Symbol".
Id Symbol Price Volume Col1 Col2 Col3...
---------------------------------------------------------------------
1 AAA 19 1000 xxxx xxxx xxxx
2 AAA 40 4000 xxxx xxxx xxxx
3 CBC 150 50000 xxxx xxxx xxxx
4 CBC 149 4000 xxxx xxxx xxxx
I want records 1 and 4 to be selected, since CBC has shortest price 149
and AAA has shortest price 19. Can anyone help me out in this regard?
Thanks in advance
|
|
|
|
|
How about
select distinct(symbol), min(price) from Order group by symbol
Database FAQ
|
|
|
|
|
Thanks for the reply but the problem is not that simple.. I want to
select the whole record not just 2 fields.. and when I select all
records the distinct stops working.. I tried sub query.. didn't
work..
|
|
|
|
|
Hey, another place where my correlation trick is useful!
SELECT Order.*
FROM Order
INNER JOIN
(
SELECT Symbol, MIN(Price) AS MinPrice
FROM Order
GROUP BY Symbol
) O2 ON Order.Symbol = O2.Symbol AND Order.Price = O2.MinPriceCaveat: this trick won't work directly if there's more than one row for a given Symbol with the minimum Price value. If there is you'll get every row with that price.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hey.. this solves my problem.. Thanks a lot..
Best Regards,
Zishan
|
|
|
|
|
|
I have a script that I am pulling apart and I don't quite understand this one thing that they did:
They have these two references that I cannot find where they have defined them :
...
insert into InvoiceTemp select i.Status, i.DisplayInvoiceId, i.InvoiceDate, e.ContractNumber, Replace(c.name, ',', ''), c.UserDefinedCustomerId, ...
There are absolutely no references to i or e at any point in the program before that,
does anyone have any suggestions?
Cheers,
Jim
|
|
|
|
|
Esmo2000 wrote:
There are absolutely no references to i or e at any point in the program before that,
What about after that?
For example:
SELECT i.col1, e.col2
FROM Invoice AS i
INNER JOIN Employee AS e ON i.EmployeeId = e.EmployeeId
i and e in the above example are defined in the FROM and INNER JOIN clauses and this is perfectly valid because it is all self contained in one statement.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Ah, I see, that was exactly the matter. I did not realise them for definitions.
Cheers,
Jim
|
|
|
|
|
Okay, perhaps i lied a bit, there are actually no occurrences of the word "as" in the document, except for one unrelated one. Is that the only way to declare an alias?
|
|
|
|
|
You can safely remove the word AS and it will still work. My personal style is to use the word AS whenever I define an alias to make my intent clear. The SQL parser will accept an alias with or without it.
For example (from the Northwind database):
SELECT p.ProductName, c.CategoryName
FROM Products p
INNER JOIN Categories c ON p.CategoryID = c.CategoryID versus:
SELECT p.ProductName, c.CategoryName
FROM Products AS p
INNER JOIN Categories AS c ON p.CategoryID = c.CategoryID Which evaluate to the same thing.
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Colin Angus Mackay wrote:
Does this help?
Tremendously sir,
as I said, I truly am a newb!
Cheers,
Jim
|
|
|
|
|
Hello everyone,
I am trying to build a stored procedure for a search page that will send many input parameters of different demographic data. The problem is, not all the search fields have to be used so some of the input parameters will be null when they are sent. I'm trying to construct a where clause for this but don't know how to do it without including a whole bunch of "if" statements to build a where clause based on which parameters have data. Does anyone have any suggestions? Thanks for your time!
mav0707
|
|
|
|
|
SELECT *
FROM MyTable
WHERE
(@param1 IS NULL OR (@param1=Column1)) AND
(@param2 IS NULL OR (@param2=Column2))....
If the parameter is null then the left side of the OR clause is true and the right side does not need to be evaluated, if the parameter is not null then the left side of the OR clause is false and so the right side must be evaluated.
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
That works great!! Thank you so much for your help!
mav0707
|
|
|
|
|
In ADO.NET
I am able to create an array of DataRows that match my criteria, but only when cboSponsor.Text contains one word. If there is more than one word I get an error message when I am debugging.
-------------------------------------------------------------
Code:
Dim drFound() As System.Data.DataRow
Dim dr As System.Data.DataRow
drFound = Me.DsEvent1.EventTbl.Select("OrgName = " & cboSponsor.Text)
-------------------------------------------------------------
Error message:
An unhandled exception of type 'System.Data.SyntaxErrorException' occurred in system.data.dll
Additional information: Syntax error: Missing operand after 'Helpers' operator.
-------------------------------------------------------------
In this case, 'Helpers' is the second of two words.
How can I code it so it recognizes a phrase?
Thanks
|
|
|
|
|
try
drFound = Me.DsEvent1.EventTbl.Select("OrgName = '" & cboSponsor.Text&"'")
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Thank you.
Now on to solve the next problem that comes up. 
|
|
|
|
|
I'm not sure where this posts belongs because it involves coding in Visual C++. I was asked to create an application that uses a MSDE database for storage. When all the data has been parsed and dumped into the database, I would like the user to view the tables within Access project. My application does not require a custom form to display the data but random queries and reports need to be generated. This was why I chose to Access to display the data. Now my question is:
1. If my application does not need custom forms to display the data and a front-end database client like Access is sufficient in providing all the tools and functionality needed to manipulate the data should I even bother using MSDE? Should I just dump the data into an Access database (.mdb)? I want to make sure I'm designing this effectively.
2. If I were to continue using MSDE, how do I make the call in my code to open the newly created database within Access project? When you create a new access project that connects to an existing database there are certain settings that the wizard asks of you before connection to the database can be made. How do I pass those parameters from my application to access project? Once the project has been created after the connections settings have been configured, the user will have full access to the tables within the database. The user can then create queries and reports as needed. How can I manage this within Visual C++. Am I headed in the wrong direction?
Any help would be much appreciated.
|
|
|
|
|
Hi, imagine the following example:
select [o].[ID] , [r].[quantity],[r].[price]<br />
from <br />
[Order] o,<br />
[OrderRow] r<br />
where <br />
(r.OrderID = o.ID)
if i would like to apply a filter on the sum of quantity*price for all rows ontop of that and still return each orderrow just like before. how can this be done?
currently i have :
select [o].[ID] , [r].[quantity],[r].[price]<br />
from <br />
[Order] o,<br />
[OrderRow] r<br />
where <br />
(r.OrderID = o.ID) and<br />
o.id in (<br />
select [o].[ID] <br />
from <br />
[Order] o,<br />
[OrderRow] r<br />
where <br />
(r.OrderID = o.ID)<br />
group by [o].[id]<br />
having <br />
sum (r.quantity * r.price) >= 100<br />
)
so this query will still give me each orderrow , but filter out orders where the order sum is less than 100
is it really not possible to accomplish this w/o a subquery?
//Roger
|
|
|
|
|
hello forum,
Not sure if this is the most suitable place in codeproject to put a thread related to Active Directory... sorry, if does not.
I have got a problem while searching directory through ADSI.
I want to search those organizational Units which contains a specific substring in its distinguishedName attribute. The filter expression is as follow:
(&(objectClass=organizationalUnit)(distinguishedName=*mysubstring*))
where mysubstring is the specific substring.
The problem is I get always nothing.
Could u give me any idea what is the problem and how to solve it?
thanks in advance!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
