|
|
Hi Friends I have to join 4 tables in SQL Server and need to Show Specific Coulmns in Data Grid View
From First Table User Tabel and feild User_ID, User_Name, User_Email, User_Password, UserR_ID, UserS_ID, Co_ID, User_Remark
from Second Table User_right Tabel and feild UserR_ID, User_Right
.from third Table User_Status Tabel and feild UserS_ID, User_Status
From Fourth Table Company and feild Co_ID, Co_Name
From First Table i need to show User_ID, User_Name, User_Email, User_Password, User_Remark
2.from Second Table i need to show User_Right
3.from third Table i need to show User_Status
4.From Fourth Table i need to show Co_Name
Try to Reply to this as soon as possible Friends...Thanks..
|
|
|
|
|
What have you tried, this seems to be a very simple exercise in organising your joins. Table 1 has all the foriegn keys to the other tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
can you do for me i am new i have tried but i can not
|
|
|
|
|
|
Lets see what you have tried and we will try and correct it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT UserT.User_ID, UserT.User_Name, UserT.User_Email, UserT.User_Password, UserRightT.User_Right, UserStatusT.UserStatus, CompanyT.Co_Name, UserT.User_Remark
FROM CompanyT INNER JOIN
UserT ON CompanyT.Co_ID = UserT.Co_ID CROSS JOIN
UserStatusT CROSS JOIN
UserRightT
query work but record again agai show in grid
|
|
|
|
|
I'm pretty sure you should replace the cross joins with either inner or left joins. Read this article [^]to help understand the joins.
Displaying the data in a grid is NOT a database problem, choose the correct forum (web/wpf/winforms) to get support on the UI.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I have built a few SSIS packages over the years, most of which contain a design which will fit within the VS window with little scrolling (i.e. not hugely complex). Although I have largely got my head round logging and configuration, I have never really fully settled on a standard for error handling.
I often handle errors via control flow (i.e. the red connectors) but have dabbled once or twice with the event handlers. While I have got the latter to work the implementation of them never seemed quite right (apologies for vagueness).
Does anyone have any thoughts on, or real world experience of, which route is better?
...or does it depend on the nature of the error? i.e. data-related errors via flow but critical failures via handlers.
Hoping this is not another one of those bun-fight topics...
|
|
|
|
|
I wrote this years ago for a report on total items sold, and I'm trying to convert it to a SQL Linq Statement in VB, but I think I'm way off here. Plus I'm not sure if I got the joins right, pretty sure that part should work.
SELECT
coH.PartNumber
, coH.ManPartNumber
, SUM(coH.Qty) as TotalQty
, CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost
, CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPrice
there must be a shorter way of writing the sum statement, and also I can't figure out how to sum the column within the select new. My original thought was to just think sql, but on the SUM, I think I need throw that idea out the window.
From cohc In context.Order_History_Cart
Join pi In context.ProductInfo On pi.PartNumber Equals cohc.PartNumber
Join vi In context.ProductInfo_Vendors On vi.VendorID Equals pi.VendorID
Where cohc.OrderDate >= m_startDate _
And cohc.OrderDate <= m_stopDate _
And cohc.PartNumber = p_PartNumber
Select New productItem With
{
.m_partNumber = cohc.PartNumber,
.m_manPartNumber = cohc.ManPartNumber,
.m_totalQty = (From x In context.Order_History_Cart Where x.OrderDate >= m_startDate And x.OrderDate <= m_stopDate And x.PartNumber = p_PartNumber Select x.Qty).Sum(),
.m_totalCost = (From y In context.Order_History_Cart Where y.OrderDate >= m_startDate And y.OrderDate <= m_stopDate And y.PartNumber = p_PartNumber Select (y.Qty * y.Cost)).Sum(),
.m_totalPrice = (From z In context.Order_History_Cart Where z.OrderDate >= m_startDate And z.OrderDate <= m_stopDate And z.PartNumber = p_PartNumber Select (z.Qty * z.Price)).Sum(),
.m_cost = pi.Cost,
.m_price = pi.Price,
|
|
|
|
|
Why do you have SQL embedded in your VB code? As a DBA that is a no no. You can get SUM in T-SQL, but you can also SUM in SSRS and Crystal Reports. As for your new I have never seen that. I am not a very experienced VB developer so it may be something I have not seen there. I would do it in a stored proc.
|
|
|
|
|
This is probably the result of using EF against one of the desktop versions of a database, don't support stored procs.
It is also the way of the new developer, use linq for everything. TSQL is not considered and not learnt because they can just wire up EF. But then I'm old school 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You need to group the set.
Have a look at this[^] example.
|
|
|
|
|
Thanks!
I got thtis to work, but I can't figure out how to insert my 2 joins, and get those values all in 1 result set
Dim pResults = _
(
From cohc In context.Order_History_Cart
Where cohc.PartNumber = "24-FLP44"
Group By cohc = New With
{
cohc.Qty,
cohc.Cost,
cohc.Price<br>
} Into group
Select New With
{
.totalQty = group.Sum(function(q) q.Qty),
.totalCost = group.Sum(function(c) c.Qty * c.Cost),
.TotalPrice = group.Sum(function(p) p.Qty * p.Price)<br>
}
)
<pre>
|
|
|
|
|
I'm new to EF, so bear that in mind..
I'm using WPF 4.5 and EF against a SQLite DB. EF was installed when I installed dotConnect for SQLite[^].
I have a WPF user control that calls into the DAL. However, in DESIGN mode the view is showing the error
"Connection string "MyConnectionString" could not be found in the configuration file."
However, the app.config DOES have the connection string.
Anyone know what's going on?
If it's not broken, fix it until it is
|
|
|
|
|
Been there done that, but in WCF.
In design time you're apparently not using the app.config you think you are.
There's a description and kind of solution to one of the ways this problem can occur here[^].
|
|
|
|
|
I am facing some problem with database connection .can anyone guide me?so i can explain my problem
|
|
|
|
|
Explain your problem and someone may be able to offer some ideas on how to overcome them.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
1) What database software are you using?
2) What are you trying to connect to what?
3) What have you tried?
4) What is the error?
We need a lot more detail before we can start to answer your problem.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
First, I no nothing at all about C++...
We need to use SQLite for cross platform compatibility. We would like to have one copy of DB code in C++ and somehow make requests/ send command into it from C#.
One possibility is to have the C++ portion send/receive its data from C# as JSON. In C# I can easily convert the JSON to C# objects and vice versa.
Anyone done this? Any tips/advice/pointers?
Thanks
If it's not broken, fix it until it is
|
|
|
|
|
OK.
Or C# can call a C++ DLL with P/Invoke?
|
|
|
|
|
I have few questions about SQL/C# transaction.
1/ How can you add transaction functionality to your stored procedure without locking anything ( I know there is different isolation level, but I'm not sure which one to use )
2/ I need to use transaction just for the sake of rolling back the changes if there was an exception in the middle of the procedure. so I really don't care about locking tables. which isolation level should I use.
|
|
|
|
|
Tunisiano32 wrote: How can you add transaction functionality to your stored procedure without
locking anything ( I know there is different isolation level, but I'm not sure
which one to use )
Locking is done when writing. The isolation levels are for specifying what you want to read.
Tunisiano32 wrote: I need to use transaction just for the sake of rolling back the changes if there
was an exception in the middle of the procedure. so I really don't care about
locking tables. which isolation level should I use.
Sounds like the procedure consists of multiple steps that can fail individually, and those updates might lock multiple tables while processing.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
READ UNCOMMITTED is the isolation level you are looking for I think. This will allow dirty reads, but almost eliminate all locking. Writing will lock the affected tables, but if others READ UNCOMMITTED they should be fine. If you have to lock the table in order the rollback the process then you really have no choice but to lock it.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
