I'm with some problems saving null int values in database. My column categoriaId can be null. But, If I put null, I receive a error from compiler telling that int can't be null. If I put 0, I receive an error from my database because my colunm has a FK.
How can I insert int null values in my database? Any help will be appreciated.
var categoriaId = 0;
if (detailId!=null){
categoriaId = detalhe.CategoriaId ?? 0;
}
if (IsPost) {
categoriaId = Request.Form["categoria"].AsInt();
}
// Here I use the categoriaId to insert the Event.
if (ModelState.IsValid) {
try {
if (detalhe==null){
Evento.IncludeEvent(nome, descricao, statusId, dataInicial, dataFinal,
localId, empresaDivulgadoraId, categoriaId, responsavel, executivoResponsavel,
valorPatrocinio, comentarios, cidadeId, participacaoId, empresasAreasEnvolvidas);
statusMessages.Add("O cadastro foi realizado com sucesso.");
}else{
Evento.UpdateEvent(nome, descricao, statusId, dataInicial, dataFinal,
localId, empresaDivulgadoraId, categoriaId, responsavel, executivoResponsavel,
valorPatrocinio, comentarios, cidadeId, participacaoId, empresasAreasEnvolvidas,
selectedId);
statusMessages.Add("A alteração foi realizada com sucesso.");
}
} catch (Exception e) {
ModelState.AddFormError(e.ToString());
}
}
//Then, I have the insert command receiving the categoriaId
public static dynamic IncluirEvento(string nome, string descricao, int statusId, DateTime dataInicial,
DateTime dataFinal, int localId, int empresaDivulgadoraId, int categoriaId, string responsavel,
string executivoResponsavel, Decimal valorPatrocinio, string comentarios, int cidadeId,
int participacaoId, String empresasAreasEnvolvidas){
var db = UserHelper.DatabaseInstance;
return db.Execute("INSERT INTO Eventos (Nome, Descricao, StatusId, DataInicio, DataTermino, " +
"LocalId, EmpresaDivulgadoraId, CategoriaId, Responsavel, ExecutivoResponsavel, " +
"ValorPatrocinio, Comentarios, CidadeId, ParticipacaoId, EmpresasAreasEnvolvidas) " +
"VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14)",
nome, descricao, statusId, dataInicial, dataFinal, localId, empresaDivulgadoraId,
categoriaId, responsavel, executivoResponsavel, valorPatrocinio, comentarios, cidadeId,
participacaoId, empresasAreasEnvolvidas);
}
You do not have to explicitly add "null" into a column if it is nullable. If you omit the column from your INSERT statement, it will automatically leave it null. If you do explicitly add it, it will need to be DBNull.Value, as that is the SqlDataType equivalent.
However, if you are getting the errors that you mention, then your categoriaId column is not marked as nullable. This can be changed in SQL Server Management Studio by opening the database, right-clicking on the table in question, and clicking "Design"
(or "Modify", depending on your version of SQL). Then click the checkbox for "allow nulls" and save.
Alternatively, you can use the following SQL command to alter the table:
ALTER TABLE MyTable
ALTER COLUMN categoriaId INT NULL
"Dream as if you'll live forever, live as if you'll die today." --James Dean
Thank you for answer. But I 'm not having problems with the database configuration. The problem is with the int parameter. Can I assign a null value to a int parameter? Take a look at my code. My insert code recept a parameter of type int.
You are getting that error because you are attempting to assign null to a non-nullable column. You need to make the column nullable using one of the methods I outlined in order to be able to place a null value in it.
"Dream as if you'll live forever, live as if you'll die today." --James Dean
Marked as answer by ronaldorezende on Jun 16, 2012 01:04 PM
So you are saying that you are sure that the categoriaId column is nullable?
What is the exact wording of the error when you pass null to the method? This might be related to what I mentioned in my first post of using DBNull.Value instead of null.
Or is it that the error suggests that null cannot be passed into the foo method as a parameter? If that is the case, the method signature might need to be adjusted:
protected void foo(int? categoriaId)
{
command = new SqlCommand("INSERT INTO MyTable Values(@categoriaId)", myConnection);
command.Parameters.Add("@categoriaId" categoriaId ?? DBNull.Value);
//etc.
command.ExecuteNonQuery();
}
"Dream as if you'll live forever, live as if you'll die today." --James Dean
Marked as answer by ronaldorezende on Jun 16, 2012 01:04 PM
As AceCorban said, so long as your database column accepts null values, you need to pass DBNull.Value in. Create a file called StringExtensions.cs and put this in it:
using System;
public static class StringExtensions
{
public static object OrDBNull(this string s) {
return string.IsNullOrEmpty(s) ? (object)DBNull.Value : (object)s;
}
}
Put that into an App_Code folder.
The following shows two values - foo is required and must be an Int. bar is not required, so you want to pass DBNull.Value rather than the default empty string that comes from Request.Form
var foo = Request["foo"];
var bar = Request["bar"];
if (IsPost){
if(!foo.IsInt()){
// The target column for foo is NOT nullable so
// add an entry to ModelState
} else {
db.execute("insert into xxx values (@0, @1)",foo, bar.OrDBNull());
}
}
ronaldorezen...
Member
28 Points
61 Posts
problems dealing with null integer to insert in database
Jun 15, 2012 06:01 PM|LINK
Hi
I'm with some problems saving null int values in database. My column categoriaId can be null. But, If I put null, I receive a error from compiler telling that int can't be null. If I put 0, I receive an error from my database because my colunm has a FK.
How can I insert int null values in my database? Any help will be appreciated.
var categoriaId = 0; if (detailId!=null){ categoriaId = detalhe.CategoriaId ?? 0; } if (IsPost) { categoriaId = Request.Form["categoria"].AsInt(); } // Here I use the categoriaId to insert the Event. if (ModelState.IsValid) { try { if (detalhe==null){ Evento.IncludeEvent(nome, descricao, statusId, dataInicial, dataFinal, localId, empresaDivulgadoraId, categoriaId, responsavel, executivoResponsavel, valorPatrocinio, comentarios, cidadeId, participacaoId, empresasAreasEnvolvidas); statusMessages.Add("O cadastro foi realizado com sucesso."); }else{ Evento.UpdateEvent(nome, descricao, statusId, dataInicial, dataFinal, localId, empresaDivulgadoraId, categoriaId, responsavel, executivoResponsavel, valorPatrocinio, comentarios, cidadeId, participacaoId, empresasAreasEnvolvidas, selectedId); statusMessages.Add("A alteração foi realizada com sucesso."); } } catch (Exception e) { ModelState.AddFormError(e.ToString()); } } //Then, I have the insert command receiving the categoriaId public static dynamic IncluirEvento(string nome, string descricao, int statusId, DateTime dataInicial, DateTime dataFinal, int localId, int empresaDivulgadoraId, int categoriaId, string responsavel, string executivoResponsavel, Decimal valorPatrocinio, string comentarios, int cidadeId, int participacaoId, String empresasAreasEnvolvidas){ var db = UserHelper.DatabaseInstance; return db.Execute("INSERT INTO Eventos (Nome, Descricao, StatusId, DataInicio, DataTermino, " + "LocalId, EmpresaDivulgadoraId, CategoriaId, Responsavel, ExecutivoResponsavel, " + "ValorPatrocinio, Comentarios, CidadeId, ParticipacaoId, EmpresasAreasEnvolvidas) " + "VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14)", nome, descricao, statusId, dataInicial, dataFinal, localId, empresaDivulgadoraId, categoriaId, responsavel, executivoResponsavel, valorPatrocinio, comentarios, cidadeId, participacaoId, empresasAreasEnvolvidas); }AceCorban
Star
12358 Points
2274 Posts
Re: problems dealing with null integer to insert in database
Jun 15, 2012 06:45 PM|LINK
You do not have to explicitly add "null" into a column if it is nullable. If you omit the column from your INSERT statement, it will automatically leave it null. If you do explicitly add it, it will need to be DBNull.Value, as that is the SqlDataType equivalent.
However, if you are getting the errors that you mention, then your categoriaId column is not marked as nullable. This can be changed in SQL Server Management Studio by opening the database, right-clicking on the table in question, and clicking "Design" (or "Modify", depending on your version of SQL). Then click the checkbox for "allow nulls" and save.
Alternatively, you can use the following SQL command to alter the table:
ronaldorezen...
Member
28 Points
61 Posts
Re: problems dealing with null integer to insert in database
Jun 15, 2012 06:57 PM|LINK
Thank you for answer. But I 'm not having problems with the database configuration. The problem is with the int parameter. Can I assign a null value to a int parameter? Take a look at my code. My insert code recept a parameter of type int.
AceCorban
Star
12358 Points
2274 Posts
Re: problems dealing with null integer to insert in database
Jun 15, 2012 08:37 PM|LINK
You have a problem with database configuration.
You are getting that error because you are attempting to assign null to a non-nullable column. You need to make the column nullable using one of the methods I outlined in order to be able to place a null value in it.
ronaldorezen...
Member
28 Points
61 Posts
Re: problems dealing with null integer to insert in database
Jun 15, 2012 10:43 PM|LINK
Sorry, but I doesn't explained my situation in a clearer way. So, I'm going to explain again:
I need to pass a null value to a method like in the example bellow:
foo(null); foo(int categoriaId){ db.execute('insert into xxx values (@0),categoriaId); }AceCorban
Star
12358 Points
2274 Posts
Re: problems dealing with null integer to insert in database
Jun 15, 2012 10:51 PM|LINK
So you are saying that you are sure that the categoriaId column is nullable?
What is the exact wording of the error when you pass null to the method? This might be related to what I mentioned in my first post of using DBNull.Value instead of null.
Or is it that the error suggests that null cannot be passed into the foo method as a parameter? If that is the case, the method signature might need to be adjusted:
protected void foo(int? categoriaId) { command = new SqlCommand("INSERT INTO MyTable Values(@categoriaId)", myConnection); command.Parameters.Add("@categoriaId" categoriaId ?? DBNull.Value); //etc. command.ExecuteNonQuery(); }Mikesdotnett...
All-Star
154927 Points
19867 Posts
Moderator
MVP
Re: problems dealing with null integer to insert in database
Jun 16, 2012 06:58 AM|LINK
As AceCorban said, so long as your database column accepts null values, you need to pass DBNull.Value in. Create a file called StringExtensions.cs and put this in it:
using System; public static class StringExtensions { public static object OrDBNull(this string s) { return string.IsNullOrEmpty(s) ? (object)DBNull.Value : (object)s; } }Put that into an App_Code folder.
The following shows two values - foo is required and must be an Int. bar is not required, so you want to pass DBNull.Value rather than the default empty string that comes from Request.Form
var foo = Request["foo"]; var bar = Request["bar"]; if (IsPost){ if(!foo.IsInt()){ // The target column for foo is NOT nullable so // add an entry to ModelState } else { db.execute("insert into xxx values (@0, @1)",foo, bar.OrDBNull()); } }Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
ronaldorezen...
Member
28 Points
61 Posts
Re: problems dealing with null integer to insert in database
Jun 16, 2012 01:04 PM|LINK
Hi everyone.
Thank you so much!
ronaldorezen...
Member
28 Points
61 Posts
Re: problems dealing with null integer to insert in database
Jun 16, 2012 01:10 PM|LINK
Just another doubt about the Mike's example.
How can I call the method "bar.OrDBNull()" if I hasn't put this method on the bar's class ?
Mikesdotnett...
All-Star
154927 Points
19867 Posts
Moderator
MVP
Re: problems dealing with null integer to insert in database
Jun 16, 2012 01:40 PM|LINK
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter