Database

Re: Simple Database access

8-Jun-06 20:08

Andrew Stampor wrote:
I am looking for suggestions on a good way to set up this application and databases.

I typed "ADO.NET Tutorial" into a search engine and got this: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx[^] There are more tutorials out there if you care to look. There are also excellent books on the subject.

"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

Andrew Stampor11-Jun-06 8:47

Andrew Stampor

11-Jun-06 8:47

Thanks for your help!

I've been looking through the example at the site you suggested and I am having a little trouble getting it to run. I've installed "Microsoft SQL Sever 2005" and am trying to run the code from lesson 2 (http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson02.aspx). When it gets to the "conn.Open()" call, I get an exception, which says:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I have attempted to resolve this but am not sure what needs to be done. I cannot find a setting that specifically allows or disallows remote connections.

Re: Simple Database access [modified]

Colin Angus Mackay11-Jun-06 11:16

Help with Navigation in Reporting Services

11-Jun-06 11:16

It means you are trying to access the SQL Server over a network connection. By default, for security, the SQL Server 2005 only permits local connections (Previously TCP/IP was also turned on).

If your SQL Server is installed on the same machine as your application is running then change the Data Source value in the connection string to (local), with the brackets, instead of the machine name, an IP address or localhost.

If your SQL Server is running on a different machine to your application then allow it to accept incoming network connections (Open the SQL Server Configuration Manager). Find "Client Protocols", and in the right side Enable "TCP/IP". If you take this option you may wish to check the properties of the TCP/IP connection and ensure the port is locked down on your internet connection's firewall.

Scottish Developers upcoming sessions include:
.NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
My: Website | Blog

-- modified at 17:16 Sunday 11th June, 2006

spirit_kleptus8-Jun-06 12:50

spirit_kleptus

8-Jun-06 12:50

Hi all,

Here's where I am stuck:

I am planning to have a sitemapath or a treeview of available reports for users in SQL reporting services 2005. I am pretty sure it is not an easy job. I am thinking of using frames here. Let's say the left frame shows the treeview and the right frame contains the reports page. Assume, there are three folders (Folder A, B, & C) containing few reports each. I want these folders and the corresponding reports(preferable) to be present in the left frame as treeview. And when I click on the folder from the left frame, it should bring up the corresponding Report Manager page showing the folder contents in the right frame.

any help or ideas would be greatly appreciated! Confused | :confused:

Dynamic SQL - Not Returning Unicode

Tristan Rhodes8-Jun-06 6:56

Tristan Rhodes

8-Jun-06 6:56

I've got a dynamic SQL query that is generated inside a SP, and is run using the

EXEC (@SQL)

or

EXEC sp_executesql @strSQL

This has worked fine until now, where we are now using a database for unicode charachters.

All fixed code stored procedures return data correctly in the unicode format, however, all queries run using EXEC / sp_executesql, return '?' instead of unicode charachters.

This is a cause of some serious issues, and i'm really hoping someone knows if there is a fix for it!

Cheers

Tris

Syntax question: Multiple Inner Joins

leckey8-Jun-06 6:04

leckey

8-Jun-06 6:04

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!

Kschuler8-Jun-06 6:29

Kschuler

8-Jun-06 6:29

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

leckey8-Jun-06 6:38

leckey

8-Jun-06 6:38

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.

Kschuler8-Jun-06 6:42

Kschuler

8-Jun-06 6:42

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.)

Re: SQL2000 SP4 Disaster!

Scott Serl8-Jun-06 8:48

Scott Serl

8-Jun-06 8:48

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

SQL2000 SP4 Disaster!

MatthysDT8-Jun-06 3:29

MatthysDT

8-Jun-06 3:29

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...

Colin Angus Mackay8-Jun-06 4:37

Re: SQL2000 SP4 Disaster!

8-Jun-06 4:37

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?

MatthysDT8-Jun-06 5:09

MatthysDT

8-Jun-06 5:09

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...

SQL Connection

nitin_ion8-Jun-06 1:42

8-Jun-06 1:42

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.

Paul Brower8-Jun-06 1:48

Paul Brower

8-Jun-06 1:48

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.

nitin_ion8-Jun-06 1:52

8-Jun-06 1:52

That is what i am doing when before opening the connection in the function.

SeMartens8-Jun-06 1:50

SeMartens

8-Jun-06 1:50

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

Colin Angus Mackay8-Jun-06 4:33

Re: SQL Connection [modified]

8-Jun-06 4:33

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.

Rob Graham8-Jun-06 5:03

Rob Graham

8-Jun-06 5:03

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 --

Re: SQL Connection [modified]

nitin_ion8-Jun-06 17:36

8-Jun-06 17:36

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.

Rob Graham9-Jun-06 5:23

Rob Graham

9-Jun-06 5:23

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.

nitin_ion9-Jun-06 17:53