Database Discussion Boards

Re: The using block

ekhorutomwen11-Jul-06 22:49

ekhorutomwen

11-Jul-06 22:49

Hi
I don't think you need the

ma se wrote:
dbCommand.Dispose();

dbCommand.Dispose(); in the finally block because the using block would have released the dbcommand

Re: The using block

Brendan Vogt11-Jul-06 23:08

Brendan Vogt

11-Jul-06 23:08

Thanks I thought so, but what about db and rdr, must I still close rdr? Doesn't the using block take care of this?

Re: The using block [modified]

ekhorutomwen11-Jul-06 23:30

ekhorutomwen

11-Jul-06 23:30

Hi i am not completely sure what you are trying to do but i would have implemented your code this way:

try
{
using (dbCommand = db.GetStoredProcCommand("sp_GetAllCars"))
{
rdr = dbCommand.ExecuteReader(); //db.ExecuteReader(dbCommand);

while (rdr.Read())
{
Car objCar = new Car();
objCar.ID = rdr.GetInt32(0); // Required
objCar.Name = rdr.GetString(1); // Required

// Add Car instance to array
carList.Add(objCar);
}
// Close the reader object
rdr.Close();
}
}
catch (SqlException ex)
{
HandleSQLError(ex, "sp_GetAllCars");
}
finally
{

}

you need to close rdr since it has exclusive access to the connection and without closing it, you can not use the underlying connection. at the end of the using, the dbCommand will be release. what i think you might need in the finally is to close the underlying connection. but that also depends on if you are not using the connection else where within the function after the finally block. i generally don't dispose my connections. but i use the Close() option since this will return the connection to the pool rather than releasing all its resources which happens with dispose().

-- modified at 5:31 Wednesday 12th July, 2006

Re: The using block

Brendan Vogt11-Jul-06 23:47

Brendan Vogt

11-Jul-06 23:47

What do you mean by the underlying connection? Is it the db? Do I have to close/ dispose of this db as well? If so where?

I defined db like this:
// Load the default database
Database db = DatabaseGlobals.GetDefaultDatabase;

Re: The using block

Kevin McFarlane12-Jul-06 0:22

Kevin McFarlane

12-Jul-06 0:22

If you use a using for the datbase connection and for the data reader then you don't need the calls to close or dispose, e.g.,

using (SqlConnection cn = new SqlConnection(connectionString))
{
    cn.Open();

    string text = "spReadCustomers";

    SqlCommand command = new SqlCommand();
    command.Connection = cn;
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = text;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            int firstNameIdx = reader.GetOrdinal("FirstName");
            int lastNameIdx = reader.GetOrdinal("LastName");
            string firstName = reader.GetString(firstNameIdx);
            string lastName = reader.GetString(lastNameIdx);
            // ... Do stuff
        }
    }
}

Kevin

Checking for an Existing record when Updating

Brendan Vogt11-Jul-06 21:08

Brendan Vogt

11-Jul-06 21:08

Hi,

When updating a record in the database, for example, is it wise (or good practice) to always do a check to see if the record exists in the stored procedure?

Please can someone comment.

Regards,
ma se

Re: Checking for an Existing record when Updating

kumarprabhakar7412-Jul-06 0:21

kumarprabhakar74

12-Jul-06 0:21

Checking the record in the procedure for update is always secure. It doesn't take any extra overhead in your program but it is additional as well as perfect checking fot data concurrency

Kumar

Update statement

angelagke11-Jul-06 18:23

angelagke

11-Jul-06 18:23

how to write update SQL statement in order to remove all underline in my data for each field, example:

"HIGH COST___" change to "HIGH COST" which is remove the underline for all data..

Thanks

angela

Re: Update statement

Dinuj Nath11-Jul-06 20:51

Dinuj Nath

11-Jul-06 20:51

Update table1 set col1 = replace(col1,'_','')

--
Don't take life seriously because you can't come out of it alive.
-Warren Miller
(From Monty²[^] bio)

Re: Update statement

angelagke11-Jul-06 21:37

angelagke

11-Jul-06 21:37

ok, i get it Smile | :)

Thanks a lot for help, Dinuj Nath Wink | ;)

angela

Re: Update statement

VK-Cadec12-Jul-06 10:01

VK-Cadec

12-Jul-06 10:01

How about::

Update table1 set col1 = 'HIGH COST'

Re: Update statement

angelagke13-Jul-06 2:07

angelagke

13-Jul-06 2:07

I think the previous 'replace' sql statement is more suitable for me because the data that I wanted to remove those underline have different value, not just for 'HIGH COST' only Smile | :)

Anyway, thanks again for your suggestion.;)

angela

ConnectionString works for VB6/VBNET/VBNET2003 for SQL SERVER but not for VBNET2005 SQL SERVER only local

Serpiente11-Jul-06 15:16

Serpiente

11-Jul-06 15:16

Hello,

I have this sub:

Sub ConDB()
Dim oSqlCliCon As SqlConnection
oSqlCliCon = New SqlConnection("Data Source=120.260.9.8;Integrated Security=SSPI;Initial Catalog=pubs")
Try
oSqlCliCon.Open()
Catch ex As Exception
msgbox(ex.Message)
End Try
oSqlCliCon.Close()
End Sub

The problem:

The code works for VB6/VBNET/VBNET2003 for SQL SERVER
for VBNET2005 works only local but for the SQL SERVER don't.

The exception message:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The exception message if modify the connectionstring:

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)

Please help, thank you.

gangeles

Output parameter in stored procedure

leckey11-Jul-06 9:27

leckey

11-Jul-06 9:27

I've been researching this but am confused on how to set up the syntax.
I have a stored procedure that bascially does the following:
1. Recieves two parameters: a part type, and a part number. The part number is actually a varchar.
2. Inserts the part type ID (taken from a second table called PartTypes) and Part Number into the table Parts.
3. The table Parts has a third field, ID which is an auto-incrmemented number. (Apparently the "Part Number" can change so we use this auto-incremented ID to reference the part number in other tables to keep things simple if the number changes.)

Okay, what I need to do is once that gets inserted into the Parts table, I need to use that Parts.ID that the system assigned. I need to send it back to the page that called it.

So two questions:
1. how do I set up this OUTPUT thing? The code I found online wasn't very helpful. Here is the stored proc:

CREATE PROCEDURE [dbo].[UpdatePart_Step_1]<br />
@PartNumber varchar (15),<br />
@PartTypeDescription varchar (60) <br />
<br />
AS<br />
declare @pt_id integer<br />
<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
SELECT  @pt_id = pt.ID FROM PartTypes pt <br />
<br />
INNER JOIN Parts p on pt.ID = p.PartTypeID<br />
<br />
WHERE pt.Description = @PartTypeDescription<br />
<br />
insert into Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@pt_id<br />
)<br />
END<br />
return 0<br />
GO

2. How do I get this value back into the original page that called the stored proc?

My thinking is that I should create a completely separate stored proc to get this Parts.ID, but I'm not sure what the norm is.

Thanks again all!

Re: Output parameter in stored procedure

nguyenvhn11-Jul-06 22:38

nguyenvhn

11-Jul-06 22:38

<br />
CREATE PROCEDURE [dbo].[UpdatePart_Step_1]<br />
@PartNumber varchar (15),<br />
@PartTypeDescription varchar (60),<br />
@ID int OUTPUT<br />
AS<br />
declare @pt_id integer<br />
<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
SELECT @pt_id = pt.ID FROM PartTypes pt<br />
<br />
INNER JOIN Parts p on pt.ID = p.PartTypeID<br />
<br />
WHERE pt.Description = @PartTypeDescription<br />
<br />
insert into Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@pt_id<br />
)<br />
END<br />
SELECT @ID=@@IDENTITY<br />
return 0<br />
GO<br />

And here is the code from client side

<br />
Dim cmd as new SqlCommand("UpdatePart_Step_1", connection)<br />
cmd.CommandType=StoredProcedure<br />
cmd.Parameters.Add("@PartNumber", varchar, 15).Value="Anything"<br />
cmd.Parameters.Add("@PartTypeDescription", varchar, 60).Value="Anything else"<br />
cmd.Parameters.Add("@ID", int).Direction=ParameterDirection.Output<br />
cmd.ExecuteNonQuery()<br />
Dim res as integer=cmd.Parameters("@ID").Value -> Returning value from SP<br />

Hope that help

Re: Output parameter in stored procedure

leckey12-Jul-06 3:13

leckey

12-Jul-06 3:13

nguyenvhn wrote:
cmd.Parameters.Add("@ID", int).Direction=ParameterDirection.Output

How would I do this in C#? This is my code that calls the stored proc:

ds2 = new DataSet();<br />
			SqlParameter param1 = new SqlParameter("@PartNumber", strPartNumberInputReference);	<br />
			SqlParameter param2 = new SqlParameter("@DwgNumber", strDwgNumber);<br />
			SqlParameter param3 = new SqlParameter("@DwgRevision", strDwgRevision);<br />
			SqlParameter param4 = new SqlParameter("@DwgLocation", strDwgLocation);<br />
			SqlParameter param5 = new SqlParameter("@PartTypeDescription", strPartTypeID);<br />
<br />
			ds2=SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure,"dbo.UpdatePart_Step_1", param1, param5);<br />
			dt2 = ds2.Tables[0];

Thanks again for your help!

DB Connection Failure using C#

Cadence2.011-Jul-06 9:03

Cadence2.0

11-Jul-06 9:03

Hey there.

I just installed SQL Server express 2005 yesterday and I'm trying to connect to it using C# on Win 2000 SP4. I've never used SQL server so this is probably a novice question.

I used the following connection string to connect
SqlConnection conn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=pubs");

However, I get the following error:

Exception Occured -->> System.Data.SqlClient.SqlException: An error has occurred
while establishing a connection to the server. When connecting to SQL Server 2
005, 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 am running the SQL Server service. I see the sqlservr.exe in the taskbar but I'm not sure if it is actually running.

Anyone know how I can connect to the sql server from C#?

Thank you!

-K

Re: DB Connection Failure using C#

god_bless_the_world11-Jul-06 11:22

god_bless_the_world

11-Jul-06 11:22

On SQL Server Express, the name of the datasource is like "SERVERNAME\SQLEXPRESS" where SERVERNAME is replaced by the name of your server. Rather than localhost, may be you can try the above syntax and check if it works.

In faith there is enough light for those who want to believe and enough shadows to blind those who don't.
- Blaise Pascal

Re: DB Connection Failure using C#

Cadence2.011-Jul-06 11:47

Cadence2.0

11-Jul-06 11:47

Indeed! Thanks for your help god_bless.

Here is my final connection string:

SqlConnection conn = new SqlConnection(@"Server=.\SQLEXPRESS; Connect Timeout=5; Database=myTestDb; User ID=****; Password=****");

Also, since I am not using Windows authentication, I had add a user which I accomplished by
SQLCMD.exe -S (local)\SQLExpress -E
> EXEC sp_adduser '[machineName]\[userName]', '[userNameInDB]'
> GO
> EXEC sp_ addsrvrolemember '[userName]', 'sysadmin'
> GO

These steps are from this (excellent) tutorial: http://www.aspfree.com/c/a/MS-SQL-Server/Configuring-SQL-Server-Express-2005/

Finally, I made sure that the server was listening for the user I specified instead of some other user; that got rid of my Login Failed attempts.

Then, it finally worked! Cool | :cool:

-K

Stored Procedures

HRiazi11-Jul-06 6:50

HRiazi

11-Jul-06 6:50

Hi;

I have this big problem in .net 2005 (C#). In the previouse version (2003) when we made a stored procedure we could simply drag and drop it into the screen and VIOLA you had all the code you needed for the procedure and with a little touch everything was fine. Now in .net 2005 I can't do that anymore or at least I dont know anyway. Could some one help me on this please or at least present another solution or 3rd party tool I could use.
I realy need to use SP.

Thanks very much
Hadi

The best third party tool

Ennis Ray Lynch, Jr.12-Jul-06 7:15

Ennis Ray Lynch, Jr.

12-Jul-06 7:15

Is likely a computer science education from a decent university. The teach concepts such as programming, ethics, and even into (which includes websearch). A good rule of thumb is: "If you can't code it by hand don't use the tool";

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

SQL Server files very large

dptalt11-Jul-06 5:17

dptalt

11-Jul-06 5:17

We have Microsoft SQL Server 2000. The database inside has very minimal information in it yet it uses 4 gigs of space. Why is it so big and how can we reduce its size?

Re: SQL Server files very large

Eric Dahlvang11-Jul-06 7:48

Eric Dahlvang

11-Jul-06 7:48

Shrinking a Database [^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: SQL Server files very large

dptalt13-Jul-06 3:09

dptalt

13-Jul-06 3:09

Turns out the log file has become very large. How can I purge this file? Can it be done from the enterprise manager?

Re: SQL Server files very large

Eric Dahlvang13-Jul-06 3:52

Eric Dahlvang

13-Jul-06 3:52

I think this Mike Dimmick answers your questions:clickety[^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.