Saturday, April 16, 2011

hashtable to pass to proc

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?

No comments: