I'm using an ObjectDataSourc e with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuer y):
System.Data.Sql Client.SqlExcep tion: Procedure or Function 'UpdateRegistra nt'
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 UpdateRegistran t(
RegistrantDetai ls reg
) {
SqlConnection con = new SqlConnection( connectionStrin g );
SqlCommand cmd = new SqlCommand( "UpdateRegistra nt", con );
cmd.CommandType = CommandType.Sto redProcedure;
...
cmd.Parameters. Add( new SqlParameter( "@EMail", SqlDbType.NVarC har, 25 ) );
cmd.Parameters["@EMail"].Value = reg.EMail;
...
con.Open();
cmd.ExecuteNonQ uery();
con.Close();
code from my DataObjectTypeN ame RegistrantDetai ls:
protected string eMail = String.Empty;
public string EMail
{
get {return eMail;}
set {eMail = value;}
}
My stored procedure UpdateRegistran t:
CREATE PROCEDURE [dbo].UpdateRegistra nt
@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] = @EMail,
...
WHERE
[RegistrantId] = @RegistrantId 7 3466
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].UpdateRegistra nt
@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.Lengt h ==0)
reg.EMail = "dummy value";
cmd.Parameters["@EMail"].Value = reg.EMail;
Good luck.
Regards, Dustin.
"Dabbler" wrote: I'm using an ObjectDataSourc e with a stored procedure and am getting the following error when trying to update (ExecuteNonQuer y):
System.Data.Sql Client.SqlExcep tion: Procedure or Function 'UpdateRegistra nt' 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 UpdateRegistran t( RegistrantDetai ls reg ) { SqlConnection con = new SqlConnection( connectionStrin g ); SqlCommand cmd = new SqlCommand( "UpdateRegistra nt", con ); cmd.CommandType = CommandType.Sto redProcedure; ... cmd.Parameters. Add( new SqlParameter( "@EMail", SqlDbType.NVarC har, 25 ) ); cmd.Parameters["@EMail"].Value = reg.EMail; ... con.Open(); cmd.ExecuteNonQ uery(); con.Close();
code from my DataObjectTypeN ame RegistrantDetai ls: protected string eMail = String.Empty; public string EMail { get {return eMail;} set {eMail = value;} }
My stored procedure UpdateRegistran t: CREATE PROCEDURE [dbo].UpdateRegistra nt @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] = @EMail, ... WHERE [RegistrantId] = @RegistrantId
Hi Dustin
Thanks for that clarification. I'm wondering what the best practices is for
this, as I've been pouring over other developers ObjectDataSourc e 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
ObjectDataSourc e, 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].UpdateRegistra nt @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.Lengt h ==0) reg.EMail = "dummy value"; cmd.Parameters["@EMail"].Value = reg.EMail;
Good luck.
Regards, Dustin.
"Dabbler" wrote:
I'm using an ObjectDataSourc e with a stored procedure and am getting the following error when trying to update (ExecuteNonQuer y):
System.Data.Sql Client.SqlExcep tion: Procedure or Function 'UpdateRegistra nt' 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 UpdateRegistran t( RegistrantDetai ls reg ) { SqlConnection con = new SqlConnection( connectionStrin g ); SqlCommand cmd = new SqlCommand( "UpdateRegistra nt", con ); cmd.CommandType = CommandType.Sto redProcedure; ... cmd.Parameters. Add( new SqlParameter( "@EMail", SqlDbType.NVarC har, 25 ) ); cmd.Parameters["@EMail"].Value = reg.EMail; ... con.Open(); cmd.ExecuteNonQ uery(); con.Close();
code from my DataObjectTypeN ame RegistrantDetai ls: protected string eMail = String.Empty; public string EMail { get {return eMail;} set {eMail = value;} }
My stored procedure UpdateRegistran t: CREATE PROCEDURE [dbo].UpdateRegistra nt @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] = @EMail, ... WHERE [RegistrantId] = @RegistrantId
Hi Dabbler,
I normally use the objectdatasourc e 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 ObjectDataSourc e 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 ObjectDataSourc e, 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].UpdateRegistra nt @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.Lengt h ==0) reg.EMail = "dummy value"; cmd.Parameters["@EMail"].Value = reg.EMail;
Good luck.
Regards, Dustin.
"Dabbler" wrote:
I'm using an ObjectDataSourc e with a stored procedure and am getting the following error when trying to update (ExecuteNonQuer y):
System.Data.Sql Client.SqlExcep tion: Procedure or Function 'UpdateRegistra nt' 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 UpdateRegistran t( RegistrantDetai ls reg ) { SqlConnection con = new SqlConnection( connectionStrin g ); SqlCommand cmd = new SqlCommand( "UpdateRegistra nt", con ); cmd.CommandType = CommandType.Sto redProcedure; ... cmd.Parameters. Add( new SqlParameter( "@EMail", SqlDbType.NVarC har, 25 ) ); cmd.Parameters["@EMail"].Value = reg.EMail; ... con.Open(); cmd.ExecuteNonQ uery(); con.Close();
code from my DataObjectTypeN ame RegistrantDetai ls: protected string eMail = String.Empty; public string EMail { get {return eMail;} set {eMail = value;} }
My stored procedure UpdateRegistran t: CREATE PROCEDURE [dbo].UpdateRegistra nt @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] = @EMail, ... WHERE [RegistrantId] = @RegistrantId
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 objectdatasourc e 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 ObjectDataSourc e 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 ObjectDataSourc e, 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].UpdateRegistra nt @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.Lengt h ==0) reg.EMail = "dummy value"; cmd.Parameters["@EMail"].Value = reg.EMail;
Good luck.
Regards, Dustin.
"Dabbler" wrote:
> I'm using an ObjectDataSourc e with a stored procedure and am getting the > following error when trying to update (ExecuteNonQuer y): > > System.Data.Sql Client.SqlExcep tion: Procedure or Function 'UpdateRegistra nt' > 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 UpdateRegistran t( > RegistrantDetai ls reg > ) { > SqlConnection con = new SqlConnection( connectionStrin g ); > SqlCommand cmd = new SqlCommand( "UpdateRegistra nt", con ); > cmd.CommandType = CommandType.Sto redProcedure; > ... > cmd.Parameters. Add( new SqlParameter( "@EMail", SqlDbType.NVarC har, 25 ) ); > cmd.Parameters["@EMail"].Value = reg.EMail; > ... > con.Open(); > cmd.ExecuteNonQ uery(); > con.Close(); > > code from my DataObjectTypeN ame RegistrantDetai ls: > protected string eMail = String.Empty; > public string EMail > { > get {return eMail;} > set {eMail = value;} > } > > My stored procedure UpdateRegistran t: > CREATE PROCEDURE [dbo].UpdateRegistra nt > @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] = @EMail, > ... > WHERE > [RegistrantId] = @RegistrantId >
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 objectdatasourc e 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 ObjectDataSourc e 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 ObjectDataSourc e, 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].UpdateRegistra nt > @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.Lengt h ==0) > reg.EMail = "dummy value"; > cmd.Parameters["@EMail"].Value = reg.EMail; > > Good luck. > > Regards, Dustin. > > "Dabbler" wrote: > > > I'm using an ObjectDataSourc e with a stored procedure and am getting the > > following error when trying to update (ExecuteNonQuer y): > > > > System.Data.Sql Client.SqlExcep tion: Procedure or Function 'UpdateRegistra nt' > > 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 UpdateRegistran t( > > RegistrantDetai ls reg > > ) { > > SqlConnection con = new SqlConnection( connectionStrin g ); > > SqlCommand cmd = new SqlCommand( "UpdateRegistra nt", con ); > > cmd.CommandType = CommandType.Sto redProcedure; > > ... > > cmd.Parameters. Add( new SqlParameter( "@EMail", SqlDbType.NVarC har, 25 ) ); > > cmd.Parameters["@EMail"].Value = reg.EMail; > > ... > > con.Open(); > > cmd.ExecuteNonQ uery(); > > con.Close(); > > > > code from my DataObjectTypeN ame RegistrantDetai ls: > > protected string eMail = String.Empty; > > public string EMail > > { > > get {return eMail;} > > set {eMail = value;} > > } > > > > My stored procedure UpdateRegistran t: > > CREATE PROCEDURE [dbo].UpdateRegistra nt > > @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] = @EMail, > > ... > > WHERE > > [RegistrantId] = @RegistrantId > >
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 objectdatasourc e 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 ObjectDataSourc e 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 > ObjectDataSourc e, 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].UpdateRegistra nt > > @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.Lengt h ==0) > > reg.EMail = "dummy value"; > > cmd.Parameters["@EMail"].Value = reg.EMail; > > > > Good luck. > > > > Regards, Dustin. > > > > "Dabbler" wrote: > > > > > I'm using an ObjectDataSourc e with a stored procedure and am getting the > > > following error when trying to update (ExecuteNonQuer y): > > > > > > System.Data.Sql Client.SqlExcep tion: Procedure or Function 'UpdateRegistra nt' > > > 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 UpdateRegistran t( > > > RegistrantDetai ls reg > > > ) { > > > SqlConnection con = new SqlConnection( connectionStrin g ); > > > SqlCommand cmd = new SqlCommand( "UpdateRegistra nt", con ); > > > cmd.CommandType = CommandType.Sto redProcedure; > > > ... > > > cmd.Parameters. Add( new SqlParameter( "@EMail", SqlDbType.NVarC har, 25 ) ); > > > cmd.Parameters["@EMail"].Value = reg.EMail; > > > ... > > > con.Open(); > > > cmd.ExecuteNonQ uery(); > > > con.Close(); > > > > > > code from my DataObjectTypeN ame RegistrantDetai ls: > > > protected string eMail = String.Empty; > > > public string EMail > > > { > > > get {return eMail;} > > > set {eMail = value;} > > > } > > > > > > My stored procedure UpdateRegistran t: > > > CREATE PROCEDURE [dbo].UpdateRegistra nt > > > @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] = @EMail, > > > ... > > > WHERE > > > [RegistrantId] = @RegistrantId > > >
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 objectdatasourc e 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 objectdatasourc e 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 ObjectDataSourc e 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 > > ObjectDataSourc e, 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].UpdateRegistra nt > > > @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.Lengt h ==0) > > > reg.EMail = "dummy value"; > > > cmd.Parameters["@EMail"].Value = reg.EMail; > > > > > > Good luck. > > > > > > Regards, Dustin. > > > > > > "Dabbler" wrote: > > > > > > > I'm using an ObjectDataSourc e with a stored procedure and am getting the > > > > following error when trying to update (ExecuteNonQuer y): > > > > > > > > System.Data.Sql Client.SqlExcep tion: Procedure or Function 'UpdateRegistra nt' > > > > 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 UpdateRegistran t( > > > > RegistrantDetai ls reg > > > > ) { > > > > SqlConnection con = new SqlConnection( connectionStrin g ); > > > > SqlCommand cmd = new SqlCommand( "UpdateRegistra nt", con ); > > > > cmd.CommandType = CommandType.Sto redProcedure; > > > > ... > > > > cmd.Parameters. Add( new SqlParameter( "@EMail", SqlDbType.NVarC har, 25 ) ); > > > > cmd.Parameters["@EMail"].Value = reg.EMail; > > > > ... > > > > con.Open(); > > > > cmd.ExecuteNonQ uery(); > > > > con.Close(); > > > > > > > > code from my DataObjectTypeN ame RegistrantDetai ls: > > > > protected string eMail = String.Empty; > > > > public string EMail > > > > { > > > > get {return eMail;} > > > > set {eMail = value;} > > > > } > > > > > > > > My stored procedure UpdateRegistran t: > > > > CREATE PROCEDURE [dbo].UpdateRegistra nt > > > > @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] = @EMail, > > > > ... > > > > WHERE > > > > [RegistrantId] = @RegistrantId > > > > This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: FizzBin |
last post by:
We are writing a C application that is using ODBC to insert records
into a database. We have a NOT NULL column that can legitimately have
an empty value, i.e. we know the value and it is empty (i.e. a zero
length string).
We are using SQLBindParameter() to bind a variable to the
parameterized insert statement <<in the form: INSERT INTO table VALUES
(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.
We are running into the...
|
by: Marcel Brekelmans |
last post by:
Hello,
I seem to get an extra empty field in every 'mysql_fetch_array' command I issue. For example:
I have a simple table 'tblName':
ID Name
1 Jane
2 Joe
2 Doe
|
by: Srinadh |
last post by:
Hi all,
We have files with about 20 to 30 fields per row.
We are trying to update such files with about 60 rows as contiguous
data in a CLOB field.
It passes through.
But when we try updating files with about 60 to 200 rows, we get the
|
by: Oleg Ogurok |
last post by:
Hi all,
I want to use RegularExpressionValidator to enforce non-empty integer format
in a TextBox. However, the validator doesn't give the error when the textbox
is empty. For example, if ValidationExpression is \d+ or even \d{1,}, the
validator still allows empty field. Must I use an additional
RequiredFieldValidator?
-Oleg.
|
by: Joachim |
last post by:
Hi
I am a beginner in VB.NET, and have a problem with empty field in Access
I have transfered a worksheet in Excel to Access table. Some of the cels are empty
I use VB.NET program to acces this Access table (product.mdb)
One of the statements is to validate whether the field empty or not
My statement is
If Trim(dr_Product("bt_m3event")) = "" Or Trim(dr_Product("bt_m3event")) = " " The
| |
by: Cylix |
last post by:
As the title,
I have a simple stored procedure just input a index and return a row,
how can I get the return record by in my VB.Net application using ADODB
Object 2.5?
|
by: Paul |
last post by:
Hi I have a simple stored procedure shown below and am trying to use it in a
..net window application to fill a dataset. IT works out of query analyzer,
returns data from an excel file. Anyhow in the windows form the designer can
not generate the schema so I can not configure a data adapter. I could use
the Execute Nonquery but I need to return data so not quite sure if that
would be correct.
ALTER PROCEDURE emp_excel$inputfile...
|
by: Chris Zopers |
last post by:
Hello,
I have some textboxes in a FormView control. The FormView is bound to an
ObjectDataSource. The ObjectDataSource has a SelectMethod that gets an
object and the textboxes in the FormView are bound to the Object's
properties. This all works fine.
The ObjectDataSource also has an UpdateMethod that points to a function
that accepts the whole object as a parameter. But when I call the
ObjectDataSource.Update() method, the specified...
|
by: Shan Yang |
last post by:
Hi,
I am handling tab delimited txt files that have multiple fields. But some of the fields can be empty. So it will appear as <TAB><TAB> in the file rather than <TAB>something<TAB>. If I read in the line and then use something like
sscanf(buf0, "%s %ld %ld %s %s %s %s %s", chr, &bg, &ed, zg, var, ref, s1, s2);
The program will skip these empty field and assign the value of the next non-empty field to the variable. What I really...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |