By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,247 Members | 871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,247 IT Pros & Developers. It's quick & easy.

hashtable to pass to proc

P: n/a
I took over an web app (C#) were the developer put everything in a has table
then called a method to execute a stored procedure, now I'm running into some
issues were if I do an update and a NULL is passed that the field in the db
is left empty and NULL is not entered in. So how can I pass a NULL value in a
hashtable, execute the stored procedure and have NULL entered in the table
instead of a blank field?

here is an example of the code:
the call to the stored procedure and passing parameters:

private voide UpdateCars(string Make, string Model, string Notes)
{
string sql = "spUpdateCars";
System.Collections.HashTable params = new System.Collections.HashTables();
params.add("@make", Make);
params.add("@model", Model);
params.add("@notes", Notes);

this.executeStoredProcedureNonQuery(strSQL, params)

}

now the executeStoredProcedureNonQuery method:
protected int executeStoredPorcedureNonQuery(string sqlCommand ,
System.Collections.Hashtable parameters)
{
int result = 0 ;

SqlConnection conn = getConnection() ;
try
{
SqlCommand cmd = new SqlCommand(sqlCommand, conn) ;
cmd.CommandType = CommandType.StoredProcedure ;
foreach (DictionaryEntry parameterEntry in parameters)
{
cmd.Parameters.Add((string)parameterEntry.Key, parameterEntry.Value) ;

}

cmd.Connection.Open() ;
result = cmd.ExecuteNonQuery() ;
cmd.Connection.Close() ;
}
catch(SqlException e)
{
Console.Write(e.StackTrace) ;
}
finally
{
if (conn != null)
conn.Close() ;
}

return result ;
}

so how can i get it to enter NULL in the field and not leave it blank?

Feb 9 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Maybe you could set the HT value to DbNull, or just have a conditional
statement when you build the SqlCommand where if the value is null, then
insert DbNull. Either way you are using DbNull.

"CsharpGuy" <Cs*******@discussions.microsoft.com> wrote in message
news:F0**********************************@microsof t.com...
I took over an web app (C#) were the developer put everything in a has
table
then called a method to execute a stored procedure, now I'm running into
some
issues were if I do an update and a NULL is passed that the field in the
db
is left empty and NULL is not entered in. So how can I pass a NULL value
in a
hashtable, execute the stored procedure and have NULL entered in the table
instead of a blank field?

here is an example of the code:
the call to the stored procedure and passing parameters:

private voide UpdateCars(string Make, string Model, string Notes)
{
string sql = "spUpdateCars";
System.Collections.HashTable params = new
System.Collections.HashTables();
params.add("@make", Make);
params.add("@model", Model);
params.add("@notes", Notes);

this.executeStoredProcedureNonQuery(strSQL, params)

}

now the executeStoredProcedureNonQuery method:
protected int executeStoredPorcedureNonQuery(string sqlCommand ,
System.Collections.Hashtable parameters)
{
int result = 0 ;

SqlConnection conn = getConnection() ;
try
{
SqlCommand cmd = new SqlCommand(sqlCommand, conn) ;
cmd.CommandType = CommandType.StoredProcedure ;
foreach (DictionaryEntry parameterEntry in parameters)
{
cmd.Parameters.Add((string)parameterEntry.Key, parameterEntry.Value) ;

}

cmd.Connection.Open() ;
result = cmd.ExecuteNonQuery() ;
cmd.Connection.Close() ;
}
catch(SqlException e)
{
Console.Write(e.StackTrace) ;
}
finally
{
if (conn != null)
conn.Close() ;
}

return result ;
}

so how can i get it to enter NULL in the field and not leave it blank?

Feb 9 '06 #2

P: n/a
I've tried that and still passing in blank and not NULL to the table.
I did something like;
if(parameterEntry.value == "")
{
parameterEntry.value = System.DBNull.value;
}

and it would not pass NULL to the table, i even checked on the web form
itself,
if (make.text == "")
{
make.text = System.DBNull.value;
}

and still nothing,

"Peter Rilling" wrote:
Maybe you could set the HT value to DbNull, or just have a conditional
statement when you build the SqlCommand where if the value is null, then
insert DbNull. Either way you are using DbNull.

"CsharpGuy" <Cs*******@discussions.microsoft.com> wrote in message
news:F0**********************************@microsof t.com...
I took over an web app (C#) were the developer put everything in a has
table
then called a method to execute a stored procedure, now I'm running into
some
issues were if I do an update and a NULL is passed that the field in the
db
is left empty and NULL is not entered in. So how can I pass a NULL value
in a
hashtable, execute the stored procedure and have NULL entered in the table
instead of a blank field?

here is an example of the code:
the call to the stored procedure and passing parameters:

private voide UpdateCars(string Make, string Model, string Notes)
{
string sql = "spUpdateCars";
System.Collections.HashTable params = new
System.Collections.HashTables();
params.add("@make", Make);
params.add("@model", Model);
params.add("@notes", Notes);

this.executeStoredProcedureNonQuery(strSQL, params)

}

now the executeStoredProcedureNonQuery method:
protected int executeStoredPorcedureNonQuery(string sqlCommand ,
System.Collections.Hashtable parameters)
{
int result = 0 ;

SqlConnection conn = getConnection() ;
try
{
SqlCommand cmd = new SqlCommand(sqlCommand, conn) ;
cmd.CommandType = CommandType.StoredProcedure ;
foreach (DictionaryEntry parameterEntry in parameters)
{
cmd.Parameters.Add((string)parameterEntry.Key, parameterEntry.Value) ;

}

cmd.Connection.Open() ;
result = cmd.ExecuteNonQuery() ;
cmd.Connection.Close() ;
}
catch(SqlException e)
{
Console.Write(e.StackTrace) ;
}
finally
{
if (conn != null)
conn.Close() ;
}

return result ;
}

so how can i get it to enter NULL in the field and not leave it blank?


Feb 9 '06 #3

P: n/a
Have you checked to see if it is a problem with the stored procedure? Maybe
the SP has the code to convert nulls into empty strings. Maybe that field
cannot handle nulls.

"CsharpGuy" <Cs*******@discussions.microsoft.com> wrote in message
news:CB**********************************@microsof t.com...
I've tried that and still passing in blank and not NULL to the table.
I did something like;
if(parameterEntry.value == "")
{
parameterEntry.value = System.DBNull.value;
}

and it would not pass NULL to the table, i even checked on the web form
itself,
if (make.text == "")
{
make.text = System.DBNull.value;
}

and still nothing,

"Peter Rilling" wrote:
Maybe you could set the HT value to DbNull, or just have a conditional
statement when you build the SqlCommand where if the value is null, then
insert DbNull. Either way you are using DbNull.

"CsharpGuy" <Cs*******@discussions.microsoft.com> wrote in message
news:F0**********************************@microsof t.com...
>I took over an web app (C#) were the developer put everything in a has
>table
> then called a method to execute a stored procedure, now I'm running
> into
> some
> issues were if I do an update and a NULL is passed that the field in
> the
> db
> is left empty and NULL is not entered in. So how can I pass a NULL
> value
> in a
> hashtable, execute the stored procedure and have NULL entered in the
> table
> instead of a blank field?
>
> here is an example of the code:
> the call to the stored procedure and passing parameters:
>
> private voide UpdateCars(string Make, string Model, string Notes)
> {
> string sql = "spUpdateCars";
> System.Collections.HashTable params = new
> System.Collections.HashTables();
> params.add("@make", Make);
> params.add("@model", Model);
> params.add("@notes", Notes);
>
> this.executeStoredProcedureNonQuery(strSQL, params)
>
> }
>
> now the executeStoredProcedureNonQuery method:
> protected int executeStoredPorcedureNonQuery(string sqlCommand ,
> System.Collections.Hashtable parameters)
> {
> int result = 0 ;
>
> SqlConnection conn = getConnection() ;
> try
> {
> SqlCommand cmd = new SqlCommand(sqlCommand, conn) ;
> cmd.CommandType = CommandType.StoredProcedure ;
> foreach (DictionaryEntry parameterEntry in parameters)
> {
> cmd.Parameters.Add((string)parameterEntry.Key, parameterEntry.Value) ;
>
> }
>
> cmd.Connection.Open() ;
> result = cmd.ExecuteNonQuery() ;
> cmd.Connection.Close() ;
> }
> catch(SqlException e)
> {
> Console.Write(e.StackTrace) ;
> }
> finally
> {
> if (conn != null)
> conn.Close() ;
> }
>
> return result ;
> }
>
> so how can i get it to enter NULL in the field and not leave it blank?
>
>
>


Feb 9 '06 #4

P: n/a
The field in the table allows NULLS.

The procs looks like this
create procedure spUpdateCars
{
@salesID int,
@make char(25) = NULL,
@model char(20) = NULL,
@notes varchar(250) = NULL
},
update Cars
set
CarMake = @make,
CarModel = @model,
SaleNotes = @notes
where sales ID = @salesID
is this how it should look to allow NULLS and insert NULL in the table or no?

"Peter Rilling" wrote:
Have you checked to see if it is a problem with the stored procedure? Maybe
the SP has the code to convert nulls into empty strings. Maybe that field
cannot handle nulls.

"CsharpGuy" <Cs*******@discussions.microsoft.com> wrote in message
news:CB**********************************@microsof t.com...
I've tried that and still passing in blank and not NULL to the table.
I did something like;
if(parameterEntry.value == "")
{
parameterEntry.value = System.DBNull.value;
}

and it would not pass NULL to the table, i even checked on the web form
itself,
if (make.text == "")
{
make.text = System.DBNull.value;
}

and still nothing,

"Peter Rilling" wrote:
Maybe you could set the HT value to DbNull, or just have a conditional
statement when you build the SqlCommand where if the value is null, then
insert DbNull. Either way you are using DbNull.

"CsharpGuy" <Cs*******@discussions.microsoft.com> wrote in message
news:F0**********************************@microsof t.com...
>I took over an web app (C#) were the developer put everything in a has
>table
> then called a method to execute a stored procedure, now I'm running
> into
> some
> issues were if I do an update and a NULL is passed that the field in
> the
> db
> is left empty and NULL is not entered in. So how can I pass a NULL
> value
> in a
> hashtable, execute the stored procedure and have NULL entered in the
> table
> instead of a blank field?
>
> here is an example of the code:
> the call to the stored procedure and passing parameters:
>
> private voide UpdateCars(string Make, string Model, string Notes)
> {
> string sql = "spUpdateCars";
> System.Collections.HashTable params = new
> System.Collections.HashTables();
> params.add("@make", Make);
> params.add("@model", Model);
> params.add("@notes", Notes);
>
> this.executeStoredProcedureNonQuery(strSQL, params)
>
> }
>
> now the executeStoredProcedureNonQuery method:
> protected int executeStoredPorcedureNonQuery(string sqlCommand ,
> System.Collections.Hashtable parameters)
> {
> int result = 0 ;
>
> SqlConnection conn = getConnection() ;
> try
> {
> SqlCommand cmd = new SqlCommand(sqlCommand, conn) ;
> cmd.CommandType = CommandType.StoredProcedure ;
> foreach (DictionaryEntry parameterEntry in parameters)
> {
> cmd.Parameters.Add((string)parameterEntry.Key, parameterEntry.Value) ;
>
> }
>
> cmd.Connection.Open() ;
> result = cmd.ExecuteNonQuery() ;
> cmd.Connection.Close() ;
> }
> catch(SqlException e)
> {
> Console.Write(e.StackTrace) ;
> }
> finally
> {
> if (conn != null)
> conn.Close() ;
> }
>
> return result ;
> }
>
> so how can i get it to enter NULL in the field and not leave it blank?
>
>
>


Feb 9 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.