SqlException

[stanley J mroczek]

System.Data.SqlClient.SqlException: 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 = Ringsiamond:Gold: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,@ProdIDkey)
GO

 

[Mary Chipman]

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
"Implementing Error Handling with Stored Procedures",
http://www.algonet.se/~sommar/error-handling-II.html

-- Mary
MCW Technologies
http://www.mcwtech.com

 

[stanley mroczek]

I read
"Error Handling in SQL Server - a Background",
http://www.algonet.se/~sommar/error-handling-I.html and
"Implementing 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(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("Update_CSV",
myConnection)

' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure


' Add Parameters to SPROC
Dim parameterName As SqlParameter = New
SqlParameter("@name", SqlDbType.NVarChar, 255)
parameterName.Value = Name
myCommand.Parameters.Add(parameterName)

Dim parametercode As SqlParameter = New
SqlParameter("@code", SqlDbType.NVarChar, 255)
parametercode.Value = code
myCommand.Parameters.Add(parametercode)

Dim parameterCaption As SqlParameter = New
SqlParameter("@Caption", SqlDbType.NVarChar, 4000)
parameterCaption.Value = Caption
myCommand.Parameters.Add(parameterCaption)


Dim parameterPrice As SqlParameter = New
SqlParameter("@Price", SqlDbType.Int, 4)
parameterPrice.Value = Price
myCommand.Parameters.Add(parameterPrice)

Dim parameterSalePrice As SqlParameter = New
SqlParameter("@SalePrice", SqlDbType.Int, 4)
parameterSalePrice.Value = SalePrice
myCommand.Parameters.Add(parameterSalePrice)

Dim parameterid As SqlParameter = New SqlParameter("@Id",
SqlDbType.NVarChar, 255)
parameterid.Value = id
myCommand.Parameters.Add(parameterid)

Dim parameterpath As SqlParameter = New
SqlParameter("@path", SqlDbType.NVarChar, 255)
parameterpath.Value = path
myCommand.Parameters.Add(parameterpath)

Dim parametererr As SqlParameter = New SqlParameter("@err",
SqlDbType.Int, 4)
parametererr.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parametererr)

Try
' Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
Catch myException As SqlException
Dim errornumber As Integer = CInt(parametererr.Value)
End Try
myConnection.Close()

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