Re: Intercepting invalid parameters to a stored procedure

On 7 Oct, 16:46, "K Viltersten" <t...@xxxxxxxxxxxxxx> wrote:
That's the solution i'm using today. A switch statement that
cuts up the query string into pieces and barks if the input
wasn't valid. I hoped for an even better way. I guess i'm
already at the optimal point, hehe.

Thanks!

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
"Ciaran O''Donnell" <CiaranODonn...@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i
meddelandetnews:4372D33F-9BCD-4694-99A6-AE61F284C0C6@xxxxxxxxxxxxxxxx



As far as I am aware, there is NO way to do this. SqlServer has no way to
provide a handler for this situation.
Why not provide people that use you database with an assembly that will
call
the stored procedures for them. That way they dont have the chance to mess
up
the actual call, they will be bound by the compiler to call your functions
correctly.

--
Ciaran O''Donnell
http://wannabedeveloper.spaces.live.com

"K Viltersten" wrote:

SqlCommand cares about argument names; and
frankly, your code should be getting the
names right.

Thanks. Now, _MY_ code is getting the
arguments right. It's _OTHERS_ code i was
worrying about. I was aiming at writing an
idiot-proof stored procedure. (Or, at least,
a crash-proof one...)

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.- Hide quoted text -

- Show quoted text -

On 7 Oct, 16:46, "K Viltersten" <t...@xxxxxxxxxxxxxx> wrote:
That's the solution i'm using today. A switch statement that
cuts up the query string into pieces and barks if the input
wasn't valid. I hoped for an even better way. I guess i'm
already at the optimal point, hehe.

Thanks!

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
"Ciaran O''Donnell" <CiaranODonn...@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i
meddelandetnews:4372D33F-9BCD-4694-99A6-AE61F284C0C6@xxxxxxxxxxxxxxxx



As far as I am aware, there is NO way to do this. SqlServer has no way to
provide a handler for this situation.
Why not provide people that use you database with an assembly that will
call
the stored procedures for them. That way they dont have the chance to mess
up
the actual call, they will be bound by the compiler to call your functions
correctly.

--
Ciaran O''Donnell
http://wannabedeveloper.spaces.live.com

"K Viltersten" wrote:

SqlCommand cares about argument names; and
frankly, your code should be getting the
names right.

Thanks. Now, _MY_ code is getting the
arguments right. It's _OTHERS_ code i was
worrying about. I was aiming at writing an
idiot-proof stored procedure. (Or, at least,
a crash-proof one...)

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.- Hide quoted text -

- Show quoted text -

On 7 Oct, 16:46, "K Viltersten" <t...@xxxxxxxxxxxxxx> wrote:
That's the solution i'm using today. A switch statement that
cuts up the query string into pieces and barks if the input
wasn't valid. I hoped for an even better way. I guess i'm
already at the optimal point, hehe.

Thanks!

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.
"Ciaran O''Donnell" <CiaranODonn...@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i
meddelandetnews:4372D33F-9BCD-4694-99A6-AE61F284C0C6@xxxxxxxxxxxxxxxx



As far as I am aware, there is NO way to do this. SqlServer has no way to
provide a handler for this situation.
Why not provide people that use you database with an assembly that will
call
the stored procedures for them. That way they dont have the chance to mess
up
the actual call, they will be bound by the compiler to call your functions
correctly.

--
Ciaran O''Donnell
http://wannabedeveloper.spaces.live.com

"K Viltersten" wrote:

SqlCommand cares about argument names; and
frankly, your code should be getting the
names right.

Thanks. Now, _MY_ code is getting the
arguments right. It's _OTHERS_ code i was
worrying about. I was aiming at writing an
idiot-proof stored procedure. (Or, at least,
a crash-proof one...)

--
Regards
Konrad Viltersten
----------------------------------------
May all spammers die an agonizing death;
have no burial places; their souls be
chased by demons in Gehenna from one room
to another for all eternity and beyond.- Hide quoted text -

- Show quoted text -

As Ciaran has suggested write an assembly that calls the stored
procedures for them. The following example shows an InsertOrder and
UpdateOrder Stored Procedure with a Data Access Layer class that calls
them and enforces the required values through the methods.

CREATE PROCEDURE InsertOrder
@OrderNumber int output
@CustomerName varchar(20)
@ProductID varchar(20)
@Qty int = 1
AS
BEGIN
INSERT INTO Order (CustomerName, ProductId, Qty)
VALUES (@CustomerName, @ProductID, @Qty)

SET @OrderNumber = @@IDENTITY
END

CREATE PROCEDURE UpdateOrder
@OrderNumber int output
@ProductID int
@Qty int
AS
BEGIN
UPDATE Order
SET Qty = @Qty
WHERE OrderNumber = @OrderNumber AND ProductID = @ProductID
END

Example Data Access Layer Component (Exception handling etc omitted
for clarity)

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace OrderEntry.DataAccessLayer
{
public class OrderDALC
{
public static SqlConnection Connection
{
get
{
string connStr =
ConfigurationManager.ConnectionStrings["Order Entry Connection
String"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
return conn;
}
}

public static void InsertOrder(out int orderNumber, string
customerName, int productId, int quantity)
{
SqlCommand cmd = new SqlCommand("InsertOrder",
Connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@OrderNumber", SqlDbType.Int, 4);
cmd.Parameters["@OrderNumber"].Direction =
ParameterDirection.Output;

cmd.Parameters.AddWithValue("@CustomerName",
customerName);
cmd.Parameters.AddWithValue("@ProductId", productId);
cmd.Parameters.AddWithValue("@Qty", quantity);

Connection.Open();

cmd.ExecuteNonQuery();

Connection.Close();

orderNumber = cmd.Parameters["@OrderNumber"].Value;
}

public static void UpdateOrder(int orderNumber, int productId,
int quantity)
{
SqlCommand cmd = new SqlCommand("UpdateOrder",
Connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@OrderNumber", orderNumber);
cmd.Parameters.AddWithValue("@ProductId", productId);
cmd.Parameters.AddWithValue("@Qty", quantity);

Connection.Open();

cmd.ExecuteNonQuery();

Connection.Close();
}
}
}
.

Relevant Pages

  • Re: Ignore SQL Server 2000 store proc errors and still get results?
    ... is occurring in ADO.NET when I try to execute that stored procedure. ... > The problem is in the way you are handling errors in your sproc code. ... > DECLARE @Rows int ... >> BEGIN SETUP TEST - run this script in Query Analyzer ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Ignore SQL Server 2000 store proc errors and still get results?
    ... DECLARE @Rows int ... > BEGIN SETUP TEST - run this script in Query Analyzer ... Create the console app ... >> temp table in your stored procedure to handle the collection of valid ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to retrieve stored procedure error code in C#?
    ... @Fred int OUTPUT ... >>> I am able to view these error codes when I run the stored proc in Query ... >>> have seen some mention of the stored procedure having to stuff it into ...
    (microsoft.public.dotnet.framework.adonet)
  • TableAdapter, INNER JOINs, stored procs, and problems with Update
    ... I have a stored procedure that uses JOINs to return columns from multiple ... I also have another stored proc that that takes a series of params ... @ac2 int, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Please Help before I pull out the last of my hair
    ... @ConsultantID Int, ... INNER JOIN PartyGuestLineItem AS PGLI WITH (NOLOCK) ... -- RETURN a recordset with a summary of the products purchased by the ... However when I run the stored procedure as shown here in an ASP page I get ...
    (microsoft.public.inetserver.asp.db)
Loading