ObjectDataSource - problem inserting empty field into stored proce

Discussion in 'ASP .Net' started by =?Utf-8?B?RGFiYmxlcg==?=, Apr 7, 2006.

  1. I'm using an ObjectDataSource with a stored procedure and am getting the
    following error when trying to update (ExecuteNonQuery):

    System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
    expects parameter '@EMail', which was not supplied.

    The field value was null in the database and not changed in the FormView so
    is null going back into the stored procedure. I'm stumped and would greatly
    appreciate any suggestions.

    TypeName code from RegistrantDB:
    public void UpdateRegistrant(
    RegistrantDetails reg
    ) {
    SqlConnection con = new SqlConnection( connectionString );
    SqlCommand cmd = new SqlCommand( "UpdateRegistrant", con );
    cmd.CommandType = CommandType.StoredProcedure;
    ...
    cmd.Parameters.Add( new SqlParameter( "@EMail", SqlDbType.NVarChar, 25 ) );
    cmd.Parameters["@EMail"].Value = reg.EMail;
    ...
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();

    code from my DataObjectTypeName RegistrantDetails:
    protected string eMail = String.Empty;
    public string EMail
    {
    get {return eMail;}
    set {eMail = value;}
    }

    My stored procedure UpdateRegistrant:
    CREATE PROCEDURE [dbo].UpdateRegistrant
    @RegistrantId int,
    @FirstName nvarchar(25),
    @MI nvarchar(3),
    @LastName nvarchar(25),
    @EMail nvarchar(25),
    ...
    AS
    UPDATE [dbo].[Registrants] SET
    [FirstName] = @FirstName,
    [MI] = @MI,
    [LastName] = @LastName,
    = @EMail,
    ...
    WHERE
    [RegistrantId] = @RegistrantId
     
    =?Utf-8?B?RGFiYmxlcg==?=, Apr 7, 2006
    #1
    1. Advertising

  2. Dabbler,

    When you add a parameter to your sqlcommand with the value null or even an
    empty string, the stored procedure will think the parameter is not supplied.

    There are two ways to solve your problem:
    1: Alter your stored procedure to allow the Email parameter (and maybe
    others) to be null:

    CREATE PROCEDURE [dbo].UpdateRegistrant
    @RegistrantId int,
    @FirstName nvarchar(25),
    @MI nvarchar(3),
    @LastName nvarchar(25),
    @EMail nvarchar(25) = null,
    ...
    AS

    2: When passing the parameter to the sqlcommand ensure that the value is not
    null or an empty string:
    if (reg.EMail == null || reg.EMail.Length ==0)
    reg.EMail = "dummy value";
    cmd.Parameters["@EMail"].Value = reg.EMail;

    Good luck.

    Regards, Dustin.

    "Dabbler" wrote:

    > I'm using an ObjectDataSource with a stored procedure and am getting the
    > following error when trying to update (ExecuteNonQuery):
    >
    > System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
    > expects parameter '@EMail', which was not supplied.
    >
    > The field value was null in the database and not changed in the FormView so
    > is null going back into the stored procedure. I'm stumped and would greatly
    > appreciate any suggestions.
    >
    > TypeName code from RegistrantDB:
    > public void UpdateRegistrant(
    > RegistrantDetails reg
    > ) {
    > SqlConnection con = new SqlConnection( connectionString );
    > SqlCommand cmd = new SqlCommand( "UpdateRegistrant", con );
    > cmd.CommandType = CommandType.StoredProcedure;
    > ...
    > cmd.Parameters.Add( new SqlParameter( "@EMail", SqlDbType.NVarChar, 25 ) );
    > cmd.Parameters["@EMail"].Value = reg.EMail;
    > ...
    > con.Open();
    > cmd.ExecuteNonQuery();
    > con.Close();
    >
    > code from my DataObjectTypeName RegistrantDetails:
    > protected string eMail = String.Empty;
    > public string EMail
    > {
    > get {return eMail;}
    > set {eMail = value;}
    > }
    >
    > My stored procedure UpdateRegistrant:
    > CREATE PROCEDURE [dbo].UpdateRegistrant
    > @RegistrantId int,
    > @FirstName nvarchar(25),
    > @MI nvarchar(3),
    > @LastName nvarchar(25),
    > @EMail nvarchar(25),
    > ...
    > AS
    > UPDATE [dbo].[Registrants] SET
    > [FirstName] = @FirstName,
    > [MI] = @MI,
    > [LastName] = @LastName,
    > = @EMail,
    > ...
    > WHERE
    > [RegistrantId] = @RegistrantId
    >[/color]
     
    =?Utf-8?B?RHVzdGluIHZhbiBkZSBTYW5kZQ==?=, Apr 7, 2006
    #2
    1. Advertising

  3. RE: ObjectDataSource - problem inserting empty field into stored p

    Hi Dustin

    Thanks for that clarification. I'm wondering what the best practices is for
    this, as I've been pouring over other developers ObjectDataSource examples
    using business classes and stored procedures and nowhere do I see a hint of
    coding to deal with null values.

    Do people typically initialize db table columns to "" instead of leaving
    them null?

    Thanks again for this info, I have 50+ columns in this table and form so I
    think the easiest thing to do is renegerate the stored procedure (using
    Codesmith) with null as default value. I'm really loosing my interest in
    ObjectDataSource, given the gray hair it's given me ;)

    "Dustin van de Sande" wrote:

    > Dabbler,
    >
    > When you add a parameter to your sqlcommand with the value null or even an
    > empty string, the stored procedure will think the parameter is not supplied.
    >
    > There are two ways to solve your problem:
    > 1: Alter your stored procedure to allow the Email parameter (and maybe
    > others) to be null:
    >
    > CREATE PROCEDURE [dbo].UpdateRegistrant
    > @RegistrantId int,
    > @FirstName nvarchar(25),
    > @MI nvarchar(3),
    > @LastName nvarchar(25),
    > @EMail nvarchar(25) = null,
    > ...
    > AS
    >
    > 2: When passing the parameter to the sqlcommand ensure that the value is not
    > null or an empty string:
    > if (reg.EMail == null || reg.EMail.Length ==0)
    > reg.EMail = "dummy value";
    > cmd.Parameters["@EMail"].Value = reg.EMail;
    >
    > Good luck.
    >
    > Regards, Dustin.
    >
    > "Dabbler" wrote:
    >
    > > I'm using an ObjectDataSource with a stored procedure and am getting the
    > > following error when trying to update (ExecuteNonQuery):
    > >
    > > System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
    > > expects parameter '@EMail', which was not supplied.
    > >
    > > The field value was null in the database and not changed in the FormView so
    > > is null going back into the stored procedure. I'm stumped and would greatly
    > > appreciate any suggestions.
    > >
    > > TypeName code from RegistrantDB:
    > > public void UpdateRegistrant(
    > > RegistrantDetails reg
    > > ) {
    > > SqlConnection con = new SqlConnection( connectionString );
    > > SqlCommand cmd = new SqlCommand( "UpdateRegistrant", con );
    > > cmd.CommandType = CommandType.StoredProcedure;
    > > ...
    > > cmd.Parameters.Add( new SqlParameter( "@EMail", SqlDbType.NVarChar, 25 ) );
    > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > ...
    > > con.Open();
    > > cmd.ExecuteNonQuery();
    > > con.Close();
    > >
    > > code from my DataObjectTypeName RegistrantDetails:
    > > protected string eMail = String.Empty;
    > > public string EMail
    > > {
    > > get {return eMail;}
    > > set {eMail = value;}
    > > }
    > >
    > > My stored procedure UpdateRegistrant:
    > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > @RegistrantId int,
    > > @FirstName nvarchar(25),
    > > @MI nvarchar(3),
    > > @LastName nvarchar(25),
    > > @EMail nvarchar(25),
    > > ...
    > > AS
    > > UPDATE [dbo].[Registrants] SET
    > > [FirstName] = @FirstName,
    > > [MI] = @MI,
    > > [LastName] = @LastName,
    > > = @EMail,
    > > ...
    > > WHERE
    > > [RegistrantId] = @RegistrantId
    > >[/color][/color]
     
    =?Utf-8?B?RGFiYmxlcg==?=, Apr 7, 2006
    #3
  4. RE: ObjectDataSource - problem inserting empty field into stored p

    Hi Dabbler,

    I normally use the objectdatasource so I can use a typed dataset without
    stored procedures.
    The stored procedures I normally use on heavy calculation jobs or multiple
    db updates, not single records.
    However I don't believe it's a problem to have your nullable fields as a
    nullable parameter in a stored procedure.

    Good luck.

    "Dabbler" wrote:

    > Hi Dustin
    >
    > Thanks for that clarification. I'm wondering what the best practices is for
    > this, as I've been pouring over other developers ObjectDataSource examples
    > using business classes and stored procedures and nowhere do I see a hint of
    > coding to deal with null values.
    >
    > Do people typically initialize db table columns to "" instead of leaving
    > them null?
    >
    > Thanks again for this info, I have 50+ columns in this table and form so I
    > think the easiest thing to do is renegerate the stored procedure (using
    > Codesmith) with null as default value. I'm really loosing my interest in
    > ObjectDataSource, given the gray hair it's given me ;)
    >
    > "Dustin van de Sande" wrote:
    >
    > > Dabbler,
    > >
    > > When you add a parameter to your sqlcommand with the value null or even an
    > > empty string, the stored procedure will think the parameter is not supplied.
    > >
    > > There are two ways to solve your problem:
    > > 1: Alter your stored procedure to allow the Email parameter (and maybe
    > > others) to be null:
    > >
    > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > @RegistrantId int,
    > > @FirstName nvarchar(25),
    > > @MI nvarchar(3),
    > > @LastName nvarchar(25),
    > > @EMail nvarchar(25) = null,
    > > ...
    > > AS
    > >
    > > 2: When passing the parameter to the sqlcommand ensure that the value is not
    > > null or an empty string:
    > > if (reg.EMail == null || reg.EMail.Length ==0)
    > > reg.EMail = "dummy value";
    > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > >
    > > Good luck.
    > >
    > > Regards, Dustin.
    > >
    > > "Dabbler" wrote:
    > >
    > > > I'm using an ObjectDataSource with a stored procedure and am getting the
    > > > following error when trying to update (ExecuteNonQuery):
    > > >
    > > > System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
    > > > expects parameter '@EMail', which was not supplied.
    > > >
    > > > The field value was null in the database and not changed in the FormView so
    > > > is null going back into the stored procedure. I'm stumped and would greatly
    > > > appreciate any suggestions.
    > > >
    > > > TypeName code from RegistrantDB:
    > > > public void UpdateRegistrant(
    > > > RegistrantDetails reg
    > > > ) {
    > > > SqlConnection con = new SqlConnection( connectionString );
    > > > SqlCommand cmd = new SqlCommand( "UpdateRegistrant", con );
    > > > cmd.CommandType = CommandType.StoredProcedure;
    > > > ...
    > > > cmd.Parameters.Add( new SqlParameter( "@EMail", SqlDbType.NVarChar, 25 ) );
    > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > > ...
    > > > con.Open();
    > > > cmd.ExecuteNonQuery();
    > > > con.Close();
    > > >
    > > > code from my DataObjectTypeName RegistrantDetails:
    > > > protected string eMail = String.Empty;
    > > > public string EMail
    > > > {
    > > > get {return eMail;}
    > > > set {eMail = value;}
    > > > }
    > > >
    > > > My stored procedure UpdateRegistrant:
    > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > @RegistrantId int,
    > > > @FirstName nvarchar(25),
    > > > @MI nvarchar(3),
    > > > @LastName nvarchar(25),
    > > > @EMail nvarchar(25),
    > > > ...
    > > > AS
    > > > UPDATE [dbo].[Registrants] SET
    > > > [FirstName] = @FirstName,
    > > > [MI] = @MI,
    > > > [LastName] = @LastName,
    > > > = @EMail,
    > > > ...
    > > > WHERE
    > > > [RegistrantId] = @RegistrantId
    > > >[/color][/color][/color]
     
    =?Utf-8?B?RHVzdGluIHZhbiBkZSBTYW5kZQ==?=, Apr 7, 2006
    #4
  5. RE: ObjectDataSource - problem inserting empty field into stored p

    Dustin

    What happens if I have a datetime field which is not on my form, but is in
    my object class and is pushed into the stored procedure. If I set the stored
    procedure to JoinDate = null will it simply ignore this column in the update
    or will it replace the value with null?

    Thanks again, I think the fog is lifting... ;)

    "Dustin van de Sande" wrote:

    > Hi Dabbler,
    >
    > I normally use the objectdatasource so I can use a typed dataset without
    > stored procedures.
    > The stored procedures I normally use on heavy calculation jobs or multiple
    > db updates, not single records.
    > However I don't believe it's a problem to have your nullable fields as a
    > nullable parameter in a stored procedure.
    >
    > Good luck.
    >
    > "Dabbler" wrote:
    >
    > > Hi Dustin
    > >
    > > Thanks for that clarification. I'm wondering what the best practices is for
    > > this, as I've been pouring over other developers ObjectDataSource examples
    > > using business classes and stored procedures and nowhere do I see a hint of
    > > coding to deal with null values.
    > >
    > > Do people typically initialize db table columns to "" instead of leaving
    > > them null?
    > >
    > > Thanks again for this info, I have 50+ columns in this table and form so I
    > > think the easiest thing to do is renegerate the stored procedure (using
    > > Codesmith) with null as default value. I'm really loosing my interest in
    > > ObjectDataSource, given the gray hair it's given me ;)
    > >
    > > "Dustin van de Sande" wrote:
    > >
    > > > Dabbler,
    > > >
    > > > When you add a parameter to your sqlcommand with the value null or even an
    > > > empty string, the stored procedure will think the parameter is not supplied.
    > > >
    > > > There are two ways to solve your problem:
    > > > 1: Alter your stored procedure to allow the Email parameter (and maybe
    > > > others) to be null:
    > > >
    > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > @RegistrantId int,
    > > > @FirstName nvarchar(25),
    > > > @MI nvarchar(3),
    > > > @LastName nvarchar(25),
    > > > @EMail nvarchar(25) = null,
    > > > ...
    > > > AS
    > > >
    > > > 2: When passing the parameter to the sqlcommand ensure that the value is not
    > > > null or an empty string:
    > > > if (reg.EMail == null || reg.EMail.Length ==0)
    > > > reg.EMail = "dummy value";
    > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > >
    > > > Good luck.
    > > >
    > > > Regards, Dustin.
    > > >
    > > > "Dabbler" wrote:
    > > >
    > > > > I'm using an ObjectDataSource with a stored procedure and am getting the
    > > > > following error when trying to update (ExecuteNonQuery):
    > > > >
    > > > > System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
    > > > > expects parameter '@EMail', which was not supplied.
    > > > >
    > > > > The field value was null in the database and not changed in the FormView so
    > > > > is null going back into the stored procedure. I'm stumped and would greatly
    > > > > appreciate any suggestions.
    > > > >
    > > > > TypeName code from RegistrantDB:
    > > > > public void UpdateRegistrant(
    > > > > RegistrantDetails reg
    > > > > ) {
    > > > > SqlConnection con = new SqlConnection( connectionString );
    > > > > SqlCommand cmd = new SqlCommand( "UpdateRegistrant", con );
    > > > > cmd.CommandType = CommandType.StoredProcedure;
    > > > > ...
    > > > > cmd.Parameters.Add( new SqlParameter( "@EMail", SqlDbType.NVarChar, 25 ) );
    > > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > > > ...
    > > > > con.Open();
    > > > > cmd.ExecuteNonQuery();
    > > > > con.Close();
    > > > >
    > > > > code from my DataObjectTypeName RegistrantDetails:
    > > > > protected string eMail = String.Empty;
    > > > > public string EMail
    > > > > {
    > > > > get {return eMail;}
    > > > > set {eMail = value;}
    > > > > }
    > > > >
    > > > > My stored procedure UpdateRegistrant:
    > > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > > @RegistrantId int,
    > > > > @FirstName nvarchar(25),
    > > > > @MI nvarchar(3),
    > > > > @LastName nvarchar(25),
    > > > > @EMail nvarchar(25),
    > > > > ...
    > > > > AS
    > > > > UPDATE [dbo].[Registrants] SET
    > > > > [FirstName] = @FirstName,
    > > > > [MI] = @MI,
    > > > > [LastName] = @LastName,
    > > > > = @EMail,
    > > > > ...
    > > > > WHERE
    > > > > [RegistrantId] = @RegistrantId
    > > > >[/color][/color][/color]
     
    =?Utf-8?B?RGFiYmxlcg==?=, Apr 7, 2006
    #5
  6. RE: ObjectDataSource - problem inserting empty field into stored p

    Dabbler,

    If you have the date in your update statement it will update the value with
    null. You can create a script to bypass this, but I don't think you should.
    If @Joindate is null
    begin
    update ....
    end
    else
    begin
    update
    end

    Another way could be to generate a sql statement in a varchar to update the
    fields supplied:
    declare @sqlstring varchar(2000)
    set @sqlstring = 'Update tbl_table set '
    if @JoinDate is null
    begin
    set @sqlstring = @sqlstring + 'joindate='+ @JoinDate --Probably you will
    have to cast this
    end
    --Other params
    exec @sqlstring

    Beware of the permissions however. When you use an sql string in your sp,
    the user will need rights to the table aswell.

    Another method (which I prefer) is of course to use a typed dataset in your
    business layer.

    "Dabbler" wrote:

    > Dustin
    >
    > What happens if I have a datetime field which is not on my form, but is in
    > my object class and is pushed into the stored procedure. If I set the stored
    > procedure to JoinDate = null will it simply ignore this column in the update
    > or will it replace the value with null?
    >
    > Thanks again, I think the fog is lifting... ;)
    >
    > "Dustin van de Sande" wrote:
    >
    > > Hi Dabbler,
    > >
    > > I normally use the objectdatasource so I can use a typed dataset without
    > > stored procedures.
    > > The stored procedures I normally use on heavy calculation jobs or multiple
    > > db updates, not single records.
    > > However I don't believe it's a problem to have your nullable fields as a
    > > nullable parameter in a stored procedure.
    > >
    > > Good luck.
    > >
    > > "Dabbler" wrote:
    > >
    > > > Hi Dustin
    > > >
    > > > Thanks for that clarification. I'm wondering what the best practices is for
    > > > this, as I've been pouring over other developers ObjectDataSource examples
    > > > using business classes and stored procedures and nowhere do I see a hint of
    > > > coding to deal with null values.
    > > >
    > > > Do people typically initialize db table columns to "" instead of leaving
    > > > them null?
    > > >
    > > > Thanks again for this info, I have 50+ columns in this table and form so I
    > > > think the easiest thing to do is renegerate the stored procedure (using
    > > > Codesmith) with null as default value. I'm really loosing my interest in
    > > > ObjectDataSource, given the gray hair it's given me ;)
    > > >
    > > > "Dustin van de Sande" wrote:
    > > >
    > > > > Dabbler,
    > > > >
    > > > > When you add a parameter to your sqlcommand with the value null or even an
    > > > > empty string, the stored procedure will think the parameter is not supplied.
    > > > >
    > > > > There are two ways to solve your problem:
    > > > > 1: Alter your stored procedure to allow the Email parameter (and maybe
    > > > > others) to be null:
    > > > >
    > > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > > @RegistrantId int,
    > > > > @FirstName nvarchar(25),
    > > > > @MI nvarchar(3),
    > > > > @LastName nvarchar(25),
    > > > > @EMail nvarchar(25) = null,
    > > > > ...
    > > > > AS
    > > > >
    > > > > 2: When passing the parameter to the sqlcommand ensure that the value is not
    > > > > null or an empty string:
    > > > > if (reg.EMail == null || reg.EMail.Length ==0)
    > > > > reg.EMail = "dummy value";
    > > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > > >
    > > > > Good luck.
    > > > >
    > > > > Regards, Dustin.
    > > > >
    > > > > "Dabbler" wrote:
    > > > >
    > > > > > I'm using an ObjectDataSource with a stored procedure and am getting the
    > > > > > following error when trying to update (ExecuteNonQuery):
    > > > > >
    > > > > > System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
    > > > > > expects parameter '@EMail', which was not supplied.
    > > > > >
    > > > > > The field value was null in the database and not changed in the FormView so
    > > > > > is null going back into the stored procedure. I'm stumped and would greatly
    > > > > > appreciate any suggestions.
    > > > > >
    > > > > > TypeName code from RegistrantDB:
    > > > > > public void UpdateRegistrant(
    > > > > > RegistrantDetails reg
    > > > > > ) {
    > > > > > SqlConnection con = new SqlConnection( connectionString );
    > > > > > SqlCommand cmd = new SqlCommand( "UpdateRegistrant", con );
    > > > > > cmd.CommandType = CommandType.StoredProcedure;
    > > > > > ...
    > > > > > cmd.Parameters.Add( new SqlParameter( "@EMail", SqlDbType.NVarChar, 25 ) );
    > > > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > > > > ...
    > > > > > con.Open();
    > > > > > cmd.ExecuteNonQuery();
    > > > > > con.Close();
    > > > > >
    > > > > > code from my DataObjectTypeName RegistrantDetails:
    > > > > > protected string eMail = String.Empty;
    > > > > > public string EMail
    > > > > > {
    > > > > > get {return eMail;}
    > > > > > set {eMail = value;}
    > > > > > }
    > > > > >
    > > > > > My stored procedure UpdateRegistrant:
    > > > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > > > @RegistrantId int,
    > > > > > @FirstName nvarchar(25),
    > > > > > @MI nvarchar(3),
    > > > > > @LastName nvarchar(25),
    > > > > > @EMail nvarchar(25),
    > > > > > ...
    > > > > > AS
    > > > > > UPDATE [dbo].[Registrants] SET
    > > > > > [FirstName] = @FirstName,
    > > > > > [MI] = @MI,
    > > > > > [LastName] = @LastName,
    > > > > > = @EMail,
    > > > > > ...
    > > > > > WHERE
    > > > > > [RegistrantId] = @RegistrantId
    > > > > >[/color][/color][/color]
     
    =?Utf-8?B?RHVzdGluIHZhbiBkZSBTYW5kZQ==?=, Apr 7, 2006
    #6
  7. RE: ObjectDataSource - problem inserting empty field into stored p

    Wow, I wasn't aware of this drawback using stored procedures. I thought I
    could use an object class (commuter) and a db class to retrieve/store the
    form fields. Given the number of fields involved these options would get
    pretty ugly.

    In your preferred DataSet scenario how do you get the data from the dataset
    columns in and out of the form fields? Do you set them one by one in the code
    behind file or do you have some other way of binding the field values?

    Thanks for your time in getting me past these hurdles.

    "Dustin van de Sande" wrote:

    > Dabbler,
    >
    > If you have the date in your update statement it will update the value with
    > null. You can create a script to bypass this, but I don't think you should.
    > If @Joindate is null
    > begin
    > update ....
    > end
    > else
    > begin
    > update
    > end
    >
    > Another way could be to generate a sql statement in a varchar to update the
    > fields supplied:
    > declare @sqlstring varchar(2000)
    > set @sqlstring = 'Update tbl_table set '
    > if @JoinDate is null
    > begin
    > set @sqlstring = @sqlstring + 'joindate='+ @JoinDate --Probably you will
    > have to cast this
    > end
    > --Other params
    > exec @sqlstring
    >
    > Beware of the permissions however. When you use an sql string in your sp,
    > the user will need rights to the table aswell.
    >
    > Another method (which I prefer) is of course to use a typed dataset in your
    > business layer.
    >
    > "Dabbler" wrote:
    >
    > > Dustin
    > >
    > > What happens if I have a datetime field which is not on my form, but is in
    > > my object class and is pushed into the stored procedure. If I set the stored
    > > procedure to JoinDate = null will it simply ignore this column in the update
    > > or will it replace the value with null?
    > >
    > > Thanks again, I think the fog is lifting... ;)
    > >
    > > "Dustin van de Sande" wrote:
    > >
    > > > Hi Dabbler,
    > > >
    > > > I normally use the objectdatasource so I can use a typed dataset without
    > > > stored procedures.
    > > > The stored procedures I normally use on heavy calculation jobs or multiple
    > > > db updates, not single records.
    > > > However I don't believe it's a problem to have your nullable fields as a
    > > > nullable parameter in a stored procedure.
    > > >
    > > > Good luck.
    > > >
    > > > "Dabbler" wrote:
    > > >
    > > > > Hi Dustin
    > > > >
    > > > > Thanks for that clarification. I'm wondering what the best practices is for
    > > > > this, as I've been pouring over other developers ObjectDataSource examples
    > > > > using business classes and stored procedures and nowhere do I see a hint of
    > > > > coding to deal with null values.
    > > > >
    > > > > Do people typically initialize db table columns to "" instead of leaving
    > > > > them null?
    > > > >
    > > > > Thanks again for this info, I have 50+ columns in this table and form so I
    > > > > think the easiest thing to do is renegerate the stored procedure (using
    > > > > Codesmith) with null as default value. I'm really loosing my interest in
    > > > > ObjectDataSource, given the gray hair it's given me ;)
    > > > >
    > > > > "Dustin van de Sande" wrote:
    > > > >
    > > > > > Dabbler,
    > > > > >
    > > > > > When you add a parameter to your sqlcommand with the value null or even an
    > > > > > empty string, the stored procedure will think the parameter is not supplied.
    > > > > >
    > > > > > There are two ways to solve your problem:
    > > > > > 1: Alter your stored procedure to allow the Email parameter (and maybe
    > > > > > others) to be null:
    > > > > >
    > > > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > > > @RegistrantId int,
    > > > > > @FirstName nvarchar(25),
    > > > > > @MI nvarchar(3),
    > > > > > @LastName nvarchar(25),
    > > > > > @EMail nvarchar(25) = null,
    > > > > > ...
    > > > > > AS
    > > > > >
    > > > > > 2: When passing the parameter to the sqlcommand ensure that the value is not
    > > > > > null or an empty string:
    > > > > > if (reg.EMail == null || reg.EMail.Length ==0)
    > > > > > reg.EMail = "dummy value";
    > > > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > > > >
    > > > > > Good luck.
    > > > > >
    > > > > > Regards, Dustin.
    > > > > >
    > > > > > "Dabbler" wrote:
    > > > > >
    > > > > > > I'm using an ObjectDataSource with a stored procedure and am getting the
    > > > > > > following error when trying to update (ExecuteNonQuery):
    > > > > > >
    > > > > > > System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
    > > > > > > expects parameter '@EMail', which was not supplied.
    > > > > > >
    > > > > > > The field value was null in the database and not changed in the FormView so
    > > > > > > is null going back into the stored procedure. I'm stumped and would greatly
    > > > > > > appreciate any suggestions.
    > > > > > >
    > > > > > > TypeName code from RegistrantDB:
    > > > > > > public void UpdateRegistrant(
    > > > > > > RegistrantDetails reg
    > > > > > > ) {
    > > > > > > SqlConnection con = new SqlConnection( connectionString );
    > > > > > > SqlCommand cmd = new SqlCommand( "UpdateRegistrant", con );
    > > > > > > cmd.CommandType = CommandType.StoredProcedure;
    > > > > > > ...
    > > > > > > cmd.Parameters.Add( new SqlParameter( "@EMail", SqlDbType.NVarChar, 25 ) );
    > > > > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > > > > > ...
    > > > > > > con.Open();
    > > > > > > cmd.ExecuteNonQuery();
    > > > > > > con.Close();
    > > > > > >
    > > > > > > code from my DataObjectTypeName RegistrantDetails:
    > > > > > > protected string eMail = String.Empty;
    > > > > > > public string EMail
    > > > > > > {
    > > > > > > get {return eMail;}
    > > > > > > set {eMail = value;}
    > > > > > > }
    > > > > > >
    > > > > > > My stored procedure UpdateRegistrant:
    > > > > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > > > > @RegistrantId int,
    > > > > > > @FirstName nvarchar(25),
    > > > > > > @MI nvarchar(3),
    > > > > > > @LastName nvarchar(25),
    > > > > > > @EMail nvarchar(25),
    > > > > > > ...
    > > > > > > AS
    > > > > > > UPDATE [dbo].[Registrants] SET
    > > > > > > [FirstName] = @FirstName,
    > > > > > > [MI] = @MI,
    > > > > > > [LastName] = @LastName,
    > > > > > > = @EMail,
    > > > > > > ...
    > > > > > > WHERE
    > > > > > > [RegistrantId] = @RegistrantId
    > > > > > >[/color][/color][/color]
     
    =?Utf-8?B?RGFiYmxlcg==?=, Apr 7, 2006
    #7
  8. RE: ObjectDataSource - problem inserting empty field into stored p

    When you have to update a lot of fields in a table, a stored procedure isn't
    always the best solution indeed.

    In my solutions I add a dataset to my businesslayer.
    In my webapplication I create an objectdatasource to the dataset in the
    businesslayer and bind a formview control to it. You can specify which
    get/set/delete command the formview control should use.
    All the fields are automatically bound to the formview.
    You can create your own template for the edit and readonly mode (and if you
    prefer even one for the insert mode. This one is automatically added, but
    when deleted it uses the edit mode!)

    Your welcome.

    "Dabbler" wrote:

    > Wow, I wasn't aware of this drawback using stored procedures. I thought I
    > could use an object class (commuter) and a db class to retrieve/store the
    > form fields. Given the number of fields involved these options would get
    > pretty ugly.
    >
    > In your preferred DataSet scenario how do you get the data from the dataset
    > columns in and out of the form fields? Do you set them one by one in the code
    > behind file or do you have some other way of binding the field values?
    >
    > Thanks for your time in getting me past these hurdles.
    >
    > "Dustin van de Sande" wrote:
    >
    > > Dabbler,
    > >
    > > If you have the date in your update statement it will update the value with
    > > null. You can create a script to bypass this, but I don't think you should.
    > > If @Joindate is null
    > > begin
    > > update ....
    > > end
    > > else
    > > begin
    > > update
    > > end
    > >
    > > Another way could be to generate a sql statement in a varchar to update the
    > > fields supplied:
    > > declare @sqlstring varchar(2000)
    > > set @sqlstring = 'Update tbl_table set '
    > > if @JoinDate is null
    > > begin
    > > set @sqlstring = @sqlstring + 'joindate='+ @JoinDate --Probably you will
    > > have to cast this
    > > end
    > > --Other params
    > > exec @sqlstring
    > >
    > > Beware of the permissions however. When you use an sql string in your sp,
    > > the user will need rights to the table aswell.
    > >
    > > Another method (which I prefer) is of course to use a typed dataset in your
    > > business layer.
    > >
    > > "Dabbler" wrote:
    > >
    > > > Dustin
    > > >
    > > > What happens if I have a datetime field which is not on my form, but is in
    > > > my object class and is pushed into the stored procedure. If I set the stored
    > > > procedure to JoinDate = null will it simply ignore this column in the update
    > > > or will it replace the value with null?
    > > >
    > > > Thanks again, I think the fog is lifting... ;)
    > > >
    > > > "Dustin van de Sande" wrote:
    > > >
    > > > > Hi Dabbler,
    > > > >
    > > > > I normally use the objectdatasource so I can use a typed dataset without
    > > > > stored procedures.
    > > > > The stored procedures I normally use on heavy calculation jobs or multiple
    > > > > db updates, not single records.
    > > > > However I don't believe it's a problem to have your nullable fields as a
    > > > > nullable parameter in a stored procedure.
    > > > >
    > > > > Good luck.
    > > > >
    > > > > "Dabbler" wrote:
    > > > >
    > > > > > Hi Dustin
    > > > > >
    > > > > > Thanks for that clarification. I'm wondering what the best practices is for
    > > > > > this, as I've been pouring over other developers ObjectDataSource examples
    > > > > > using business classes and stored procedures and nowhere do I see a hint of
    > > > > > coding to deal with null values.
    > > > > >
    > > > > > Do people typically initialize db table columns to "" instead of leaving
    > > > > > them null?
    > > > > >
    > > > > > Thanks again for this info, I have 50+ columns in this table and form so I
    > > > > > think the easiest thing to do is renegerate the stored procedure (using
    > > > > > Codesmith) with null as default value. I'm really loosing my interest in
    > > > > > ObjectDataSource, given the gray hair it's given me ;)
    > > > > >
    > > > > > "Dustin van de Sande" wrote:
    > > > > >
    > > > > > > Dabbler,
    > > > > > >
    > > > > > > When you add a parameter to your sqlcommand with the value null or even an
    > > > > > > empty string, the stored procedure will think the parameter is not supplied.
    > > > > > >
    > > > > > > There are two ways to solve your problem:
    > > > > > > 1: Alter your stored procedure to allow the Email parameter (and maybe
    > > > > > > others) to be null:
    > > > > > >
    > > > > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > > > > @RegistrantId int,
    > > > > > > @FirstName nvarchar(25),
    > > > > > > @MI nvarchar(3),
    > > > > > > @LastName nvarchar(25),
    > > > > > > @EMail nvarchar(25) = null,
    > > > > > > ...
    > > > > > > AS
    > > > > > >
    > > > > > > 2: When passing the parameter to the sqlcommand ensure that the value is not
    > > > > > > null or an empty string:
    > > > > > > if (reg.EMail == null || reg.EMail.Length ==0)
    > > > > > > reg.EMail = "dummy value";
    > > > > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > > > > >
    > > > > > > Good luck.
    > > > > > >
    > > > > > > Regards, Dustin.
    > > > > > >
    > > > > > > "Dabbler" wrote:
    > > > > > >
    > > > > > > > I'm using an ObjectDataSource with a stored procedure and am getting the
    > > > > > > > following error when trying to update (ExecuteNonQuery):
    > > > > > > >
    > > > > > > > System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant'
    > > > > > > > expects parameter '@EMail', which was not supplied.
    > > > > > > >
    > > > > > > > The field value was null in the database and not changed in the FormView so
    > > > > > > > is null going back into the stored procedure. I'm stumped and would greatly
    > > > > > > > appreciate any suggestions.
    > > > > > > >
    > > > > > > > TypeName code from RegistrantDB:
    > > > > > > > public void UpdateRegistrant(
    > > > > > > > RegistrantDetails reg
    > > > > > > > ) {
    > > > > > > > SqlConnection con = new SqlConnection( connectionString );
    > > > > > > > SqlCommand cmd = new SqlCommand( "UpdateRegistrant", con );
    > > > > > > > cmd.CommandType = CommandType.StoredProcedure;
    > > > > > > > ...
    > > > > > > > cmd.Parameters.Add( new SqlParameter( "@EMail", SqlDbType.NVarChar, 25 ) );
    > > > > > > > cmd.Parameters["@EMail"].Value = reg.EMail;
    > > > > > > > ...
    > > > > > > > con.Open();
    > > > > > > > cmd.ExecuteNonQuery();
    > > > > > > > con.Close();
    > > > > > > >
    > > > > > > > code from my DataObjectTypeName RegistrantDetails:
    > > > > > > > protected string eMail = String.Empty;
    > > > > > > > public string EMail
    > > > > > > > {
    > > > > > > > get {return eMail;}
    > > > > > > > set {eMail = value;}
    > > > > > > > }
    > > > > > > >
    > > > > > > > My stored procedure UpdateRegistrant:
    > > > > > > > CREATE PROCEDURE [dbo].UpdateRegistrant
    > > > > > > > @RegistrantId int,
    > > > > > > > @FirstName nvarchar(25),
    > > > > > > > @MI nvarchar(3),
    > > > > > > > @LastName nvarchar(25),
    > > > > > > > @EMail nvarchar(25),
    > > > > > > > ...
    > > > > > > > AS
    > > > > > > > UPDATE [dbo].[Registrants] SET
    > > > > > > > [FirstName] = @FirstName,
    > > > > > > > [MI] = @MI,
    > > > > > > > [LastName] = @LastName,
    > > > > > > > = @EMail,
    > > > > > > > ...
    > > > > > > > WHERE
    > > > > > > > [RegistrantId] = @RegistrantId
    > > > > > > >[/color][/color][/color]
     
    =?Utf-8?B?RHVzdGluIHZhbiBkZSBTYW5kZQ==?=, Apr 10, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ButlerDJIAM
    Replies:
    0
    Views:
    563
    ButlerDJIAM
    Nov 9, 2006
  2. =?Utf-8?B?UGF1bA==?=
    Replies:
    0
    Views:
    276
    =?Utf-8?B?UGF1bA==?=
    Dec 6, 2006
  3. krishnakant Mane
    Replies:
    11
    Views:
    434
    Stefan Sonnenberg-Carstens
    May 7, 2007
  4. Carmine [www.thetotalsite.it]
    Replies:
    4
    Views:
    6,641
    Carmine [www.thetotalsite.it]
    Mar 5, 2008
  5. c676228
    Replies:
    4
    Views:
    119
    c676228
    Oct 19, 2006
Loading...

Share This Page