|
I am working with a datagrid in c#/asp.net. Within the datagrid I have to display fields from 10 different tables. I am wondering if my thinking of the syntax is correct.
First, the user enters the part number (Parts.PartNumber)which is actually a varchar. Then it finds the Part.ID. This Part.ID is what most things are going to inner join. Right now I have it just displaying the cost:
Select cost from costs c INNER JOIN Parts p ON c.PartID = p.Id where p.PartNumber = @PartID
But I want it to also display SubParts.Quantity where SubParts.PartID = Parts.ID. So would I set it up like this?
Select cost from costs c, quantity from SubParts sp (INNER JOIN Parts p ON c.PartID = p.ID AND INNER JOIN sp.PartID = p.ID) where p.PartNumber = @PartID
I've got a bunch of these so if someone can point me in the right direction much thanks!
|
|
|
|
|
Personally, I don't use the "JOIN" keywords very often. You can do a typical join like this:
SELECT cost FROM costs as c, parts as p WHERE c.PartID=p.ID AND p.PartNumber=@PartID
Then your second statement would look like this:
SELECT cost, quantity FROM costs as c, SubPars as sp, parts as p WHERE c.PartID=p.ID AND p.PartNumber=@PartID AND sp.PartID=p.ID
Basically you just need to make sure that whatever you would put in the "ON" portion, you include in the "WHERE" portion.
If you want to keep the "JOIN" keywords, I believe it would have to look like this:
SELECT cost, quantity FROM costs as c INNER JOIN parts as p ON c.PartID=p.ID INNER JOIN SubParts as sp ON sp.PartID=p.ID WHERE p.PartNumber=@PartID
|
|
|
|
|
Okay, elaborating on the second statement...I have two tables that I have to pull the field 'description' (I had nothing to do with the table structures !). So would you say
SELECT cost, quantity, description, description from costs as c, SubParts as sp, WeightType as wt, CostType as ct, parts as p WHERE c.PartID = p.ID AND p.PartNumber = @PartID AND sp.PartID=p.ID...(and the other joins for the descriptions).
Since description is in there twice, is that a legal statement since the number of select fields matches the number of tables listed? Just curious.
|
|
|
|
|
I think you will have to use the table aliases and then give aliases to the field names. So if description is from the part table and the sub part table it would look like this:
SELECT cost, quantity, p.description as PartDesc, sp.description as SubPartDesc, ... (etc.)
|
|
|
|
|
SELECT c.cost, sp.quantity
FROM costs c
INNER JOIN Parts p ON c.PartID = p.ID
INNER JOIN SubParts sp ON p.ID = sp.PartID
WHERE p.PartNumber = @PartID
|
|
|
|
|
We use replication between our respective sites to keep certain data synchronized between our SQL servers. In order to use SQL's reporting service as a tool to notify us of replication failures, we had to update our main server (the publisher) to SP4.
After doing this our client app crashes (along with all the other clients) as soon as a certain form in our app is updated.
What can cause this? Memory leaks? Table locks?
Any ideas?
Thanks!
you can't forget something you never knew...
|
|
|
|
|
evilnoodle wrote: After doing this our client app crashes (along with all the other clients) as soon as a certain form in our app is updated.
Too vague - What is the error message? What was the app doing at the time?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
The form has functionality to insert or update data, the control source is a combination of two tables. Upon exit of the form (upon update of the DB) the app hangs, but on all client PC's.
you can't forget something you never knew...
|
|
|
|
|
I have a function which is interacting with SQL.
In the function i am opening the connection and closing it in the finally block.
When i execute that function it opens a connection with SQL that's fine.
Now if i call that functuion say 10 times 10 connections will be opened to SQL which is correct, but that should not be. Soon the SQL pool size will be filled and application(APS.Net) will get timeout exception.
That function will be used a lot of time in the application.
Is there a way that i can use a single connection to SQL for that application
Cause the problem that we are facing is that pool gets filled in a couple of hours and why the connection is not destroyed once i close it in FINALLY block.
Any help will be welcomed.
|
|
|
|
|
Use a SqlConnection object at the class level. Create a property accessor for the SqlConnection object, and in the 'get' portion of the property, only create a new connection if the existing SqlConnection object is null.
|
|
|
|
|
That is what i am doing when before opening the connection in the function.
|
|
|
|
|
Hi,
I think the easiest way to use a single connection for the whole application is to maintain the connection within the Global.asax.
There are several methods which will be called if your application starts or stops. Myself, I used this method several times when facing the same problem like you, and it worked fine for me.
I hope this helps a bit (if not, don't hesitate to ask again)
Regards
Sebastian
|
|
|
|
|
The best practices for ADO.NET suggest you aquire late and release early. You should create a connection only when you need it and release it as soon as you are done with it. You should not hold it for the life of the application.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I believe you need to Dispose() and set null in the finally block to insure it is returned to the pool. Close() is not enough.
As Colin pointed out, best practices recommend createing the connection as late as possible, and destroying it as soon as possible. You only need to keep it around across multiple operations if you are executing a multi-step transaction.Last modified: Thursday, June 08, 2006 10:01:36 AM --
|
|
|
|
|
I m connecting to SQL only when needed and closing and disposing ASAP.
When i connect to database the pool size goes to 2 irrespective of how many times i call the function and whether i open and close the connection.
But as soon as i closes the application the pool goes to 0
I have tried with different options (Closing and keeping the connection open)
Dim scon As New SqlConnection(ConnectionString)
Dim da As New SqlDataAdapter("select * from Table", scon)
Dim ds As New DataSet
Try
da.Fill(ds)
Return ds
Catch ex As Exception
Finally
'da.Dispose()
'scon.Close()
'scon.Dispose()
End Try
what can be the reason.
|
|
|
|
|
nitin_ion wrote: When i connect to database the pool size goes to 2 irrespective of how many times i call the function and whether i open and close the connection
But as soon as i closes the application the pool goes to 0
Looks like its working correctly to me. On the first use, the connection pool is initialized to the minimum (2) and stays there (since you are only using one connection. The pool is not destroyed until your app exits... How are you determining the pool's connection count?
You can cause connections not in use to be removed by setting the "Connection Lifetime" parameter in your connection string to the number of seconds after which the connection should be reclaimed (NET2.0)
It defaults to 0 which meens infinite lifetime.
|
|
|
|
|
Ok, just 1 Q
as i am using asp.net and i am not using Global.asaz for connectionstring and also i am using web services which interacts with the same database.
So if multiple users are using the web pages and at the same time webservice is also quering database then how many connections should be open.
In oor case if 5-7 users are using and web services are working the the connections to Db goes upto 100+ and
at the login page it starts 11+ connections and we are just checking userid and password that's it.
What can be the reason
|
|
|
|
|
select * from premium where Reg_Number_Vehicle like '%PB-1-AD-511%' or Reg_Number_Vehicle like '%PB-1-AU-396%' or Reg_Number_Vehicle like '%PCL-6888 %' or Reg_Number_Vehicle like '%PB-1-AP-535%' or Reg_Number_Vehicle like '%PB-1-AV-223%' or Reg_Number_Vehicle like '%PIM-224 %' or Reg_Number_Vehicle like '%CHE-5851 %' or Reg_Number_Vehicle like '%PCS-5569 %' or Reg_Number_Vehicle like '%PJL-161 %' or Reg_Number_Vehicle like '%CH-3-B-99%' or Reg_Number_Vehicle like '%PB-1-AC-198%' or Reg_Number_Vehicle like '%PB-1-AL-87%'
|
|
|
|
|
what u get as result? be more specific.
|
|
|
|
|
this query takes a lot of time .How do i optimize this
|
|
|
|
|
Using LIKE, especially with wild cards at the start of a string, basically mean that the database can't use indexes (or can't use them efficiently) so it will naturally take a long time.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
i have to keep it at the start as it is required
|
|
|
|
|
I wouldn't optimize it! I'd change the data design. You should create another table called Reg_Number_Vehicle_Class that is used to store all of the the different classes you are using. Then create an insert trigger on your premium table to ensure the appropriate class id is assigned to each row. Create a non-unique index on that column and your query should run faster.
Chris Meech
I am Canadian. [heard in a local bar]
The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]
GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
|
|
|
|
|
Don't bother; it's a waste of time. You're doing an arbitrary query on text in the middle of a bunch of strings. Short of rearranging your data, the only significant optimization you're going to get is by throwing more hardware at it.
What does the schema or the rest of the data in the data set look like? Maybe there's some optimization based on the sequences your're looking for vs. the sequences in the data set. It's unlikely, though.
|
|
|
|
|
Halo,
Which tables in SQL server i need to update to create users for database and assign them to a groups? any articles to share.
planning to use this sql script from vb.net to create users 
thanks
Stephen
---------------------
www.islasolutions.net
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
