SqlException

[stanley J mroczek]

System.Data.Sql Client.SqlExcep tion: General network error.
Check your network documentation. I know that this is not
much to go on. But I don't know where to start. the
program updates 186 records and on the 187 record I get
this error. I know this don't make any sense but the code
field has sa in it?

When it bombs I have this in the fields.
ProdIDkey = 488
Name = 10ct diamond & 1.25 Sapphire ring
Code = r1364sa
Caption = 10ct diamond & 1.25 Sapphire ring
Price = 597
SalePrice = 327
CategoryKey = 68
path = Rings:Diamond:G old:14 Kt:White:



CREATE PROCEDURE Update_CSV
@ProdIDkey int,
@Name nvarchar(255),
@code nvarchar(255),
@Caption nvarchar(4000),
@Price int,
@SalePrice int,
@CategoryKey int,
@path nvarchar(255)

as


DECLARE @CountItems int

SELECT
@CountItems = Count(*)
FROM CSV

WHERE ProdIDkey = @ProdIDkey


IF @CountItems > 0 /* There are items - update the
current quantity */

UPDATE CSV
SET Name = @Name, code = @code, Caption =
@Caption, Price = @Price, [Sale-Price] = @SalePrice,
CategoryKey = @CategoryKey, path = @path
WHERE ProdIDkey = @ProdIDkey

ELSE /* New entry for this Cart. Add a new record */

INSERT INTO CSV
(Name, code, Caption, Price, [Sale-
Price], CategoryKey, path,ProdIDkey)
VALUES (@Name, @code, @Caption, @Price, @SalePrice,
@CategoryKey, @path,@ProdIDke y)
GO

[Mary Chipman]

Re: SqlException

First of all, every stored procedure should have SET NOCOUNT ON as
the first statement. Second, you need to implement error handling in
the stored procedure, not rely on the client (SqlException). Here are
two free papers contributed by SQL Server MVP Erland Sommarskog, which
have excellent coverage of the topic:

"Error Handling in SQL Server - a Background",
http://www.algonet.se/~sommar/error-handling-I.html and
"Implementi ng Error Handling with Stored Procedures",

http://www.algonet.se/~sommar/error-handling-II.html

-- Mary
MCW Technologies

http://www.mcwtech.com

On Wed, 16 Jul 2003 15:47:13 -0700, "stanley J mroczek"
<smroczek@si.rr .com> wrote:
[color=blue]
>System.Data.Sq lClient.SqlExce ption: General network error.
>Check your network documentation. I know that this is not
>much to go on. But I don't know where to start. the
>program updates 186 records and on the 187 record I get
>this error. I know this don't make any sense but the code
>field has sa in it?
>
>When it bombs I have this in the fields.
> ProdIDkey = 488
>Name = 10ct diamond & 1.25 Sapphire ring
>Code = r1364sa
>Caption = 10ct diamond & 1.25 Sapphire ring
>Price = 597
>SalePrice = 327
>CategoryKey = 68
>path = Rings:Diamond:G old:14 Kt:White:
>
>
>
>CREATE PROCEDURE Update_CSV
> @ProdIDkey int,
> @Name nvarchar(255),
> @code nvarchar(255),
> @Caption nvarchar(4000),
> @Price int,
> @SalePrice int,
> @CategoryKey int,
> @path nvarchar(255)
>
>as
>
>
>DECLARE @CountItems int
>
>SELECT
> @CountItems = Count(*)
>FROM CSV
>
>WHERE ProdIDkey = @ProdIDkey
>
>
>IF @CountItems > 0 /* There are items - update the
>current quantity */
>
>UPDATE CSV
>SET Name = @Name, code = @code, Caption =
>@Caption, Price = @Price, [Sale-Price] = @SalePrice,
> CategoryKey = @CategoryKey, path = @path
>WHERE ProdIDkey = @ProdIDkey
>
>ELSE /* New entry for this Cart. Add a new record */
>
>INSERT INTO CSV
> (Name, code, Caption, Price, [Sale-
>Price], CategoryKey, path,ProdIDkey)
>VALUES (@Name, @code, @Caption, @Price, @SalePrice,
>@CategoryKey , @path,@ProdIDke y)
>GO[/color]

[stanley mroczek]

Re: SqlException

I read
"Error Handling in SQL Server - a Background",
http://www.algonet.se/~sommar/error-handling-I.html and
"Implementi ng Error Handling with Stored Procedures",

http://www.algonet.se/~sommar/error-handling-II.html

and made the following changes:

if I take out the try I still get the error and I know how to find
whats wrong.

Please hekp??
Stan
-----------------------------------------------------------
Public Function CSVUpdate(ByVal Name As String, _
ByVal code As String, _
ByVal Caption As String, _
ByVal Price As Integer, _
ByVal SalePrice As Integer, _
ByVal id As String, _
ByVal path As String)

' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New
SqlConnection(C onfigurationSet tings.AppSettin gs("ConnectionS tring"))
Dim myCommand As SqlCommand = New SqlCommand("Upd ate_CSV",
myConnection)

' Mark the Command as a SPROC
myCommand.Comma ndType = CommandType.Sto redProcedure


' Add Parameters to SPROC
Dim parameterName As SqlParameter = New
SqlParameter("@ Name", SqlDbType.NVarC har, 255)
parameterName.V alue = Name
myCommand.Param eters.Add(param eterName)

Dim parametercode As SqlParameter = New
SqlParameter("@ code", SqlDbType.NVarC har, 255)
parametercode.V alue = code
myCommand.Param eters.Add(param etercode)

Dim parameterCaptio n As SqlParameter = New
SqlParameter("@ Caption", SqlDbType.NVarC har, 4000)
parameterCaptio n.Value = Caption
myCommand.Param eters.Add(param eterCaption)


Dim parameterPrice As SqlParameter = New
SqlParameter("@ Price", SqlDbType.Int, 4)
parameterPrice. Value = Price
myCommand.Param eters.Add(param eterPrice)

Dim parameterSalePr ice As SqlParameter = New
SqlParameter("@ SalePrice", SqlDbType.Int, 4)
parameterSalePr ice.Value = SalePrice
myCommand.Param eters.Add(param eterSalePrice)

Dim parameterid As SqlParameter = New SqlParameter("@ Id",
SqlDbType.NVarC har, 255)
parameterid.Val ue = id
myCommand.Param eters.Add(param eterid)

Dim parameterpath As SqlParameter = New
SqlParameter("@ path", SqlDbType.NVarC har, 255)
parameterpath.V alue = path
myCommand.Param eters.Add(param eterpath)

Dim parametererr As SqlParameter = New SqlParameter("@ err",
SqlDbType.Int, 4)
parametererr.Di rection = ParameterDirect ion.Output
myCommand.Param eters.Add(param etererr)

Try
' Open the connection and execute the Command
myConnection.Op en()
myCommand.Execu teNonQuery()
Catch myException As SqlException
Dim errornumber As Integer = CInt(parametere rr.Value)
End Try
myConnection.Cl ose()

------------------------------------------------------------------------
----
CREATE PROCEDURE Update_CSV

@Name nvarchar(255),
@code nvarchar(255),
@Caption nvarchar(4000),
@Price int,
@SalePrice int,
@id nvarchar(255),
@path nvarchar(255),
@err int output
as


SET NOCOUNT ON

DECLARE @CountItems int

SELECT
@CountItems = Count(*)
FROM CSV

WHERE code = @code


IF @CountItems > 0 /* There are items - update the current quantity */

UPDATE CSV
SET Name = @Name, code = @code,Id = @id, Caption =
@Caption, Price = @Price, [Sale-Price] = @SalePrice,
path = @path
WHERE code = @code

ELSE /* New entry for this Cart. Add a new record */

INSERT INTO CSV
(Name, code, Caption, Price, [Sale-Price],
path,id)
VALUES (@Name, @code, @Caption, @Price, @SalePrice, @path,@id)

SELECT @err = @@error if @err <> 0 return @err
GO
---------------------------


Stanley J. Mroczek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!