Sql Exception in .net

  • Thread starter Thread starter stanley j mroczek
  • Start date Start date
S

stanley j 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
---------------------------
 
What is the error, which line causes it?
What happens if you run the sproc in query analyzer?


stanley j mroczek said:
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
Click to expand...
 
i run the program and 188 records are processed before i get this error.

Server Error in '/' Application.
------------------------------------------------------------------------
--------

General network error. Check your network documentation.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: General network
error. Check your network documentation.

Source Error:


Line 1865: ' Open the connection and execute the Command
Line 1866: myConnection.Open()
Line 1867: myCommand.ExecuteNonQuery()
Line 1868: myConnection.Close()
Line 1869:


Source File: C:\Market\Components\MaintenanceDB.vb Line: 1867

Stack Trace:


[SqlException: General network error. Check your network
documentation.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
Market.MK.MaintenanceDB.ProductUpdate(Int32 ProdIDkey, Int32
WholesalerId, String prodID, String prodName, String AuctionTitle,
String prodDescription, String prodImageSmallPath, String
prodImageLargePath, String RetailPrice, String CostPrice, String
SalePrice, String revPrice, String IdMetal, String idlength, String
gramsofgold, String Minimumcarattotalweight, String page, String
CategoryKey, String Width, String Numberofdiamonds) in
C:\Market\Components\MaintenanceDB.vb:1867
Market.__MaintProducts.updatecommandheader(Object sender,
DataGridCommandEventArgs H) in C:\Market\_MaintProducts.ascx.vb:531

System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventA
rgs e) +109
System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source,
EventArgs e) +507
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
+26
System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source,
EventArgs e) +100
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
+26
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e)
+120

System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler
.RaisePostBackEvent(String eventArgument) +115
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+138
System.Web.UI.Page.ProcessRequestMain() +1258





Stanley J. Mroczek
 
Back
Top