hashtable to pass to proc

Discussion in 'ASP .Net' started by =?Utf-8?B?Q3NoYXJwR3V5?=, Feb 9, 2006.

  1. 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?
     
    =?Utf-8?B?Q3NoYXJwR3V5?=, Feb 9, 2006
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    >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?
    >
    >
    >
     
    Peter Rilling, Feb 9, 2006
    #2
    1. Advertising

  3. 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" <> wrote in message
    > news:...
    > >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?
    > >
    > >
    > >

    >
    >
    >
     
    =?Utf-8?B?Q3NoYXJwR3V5?=, Feb 9, 2006
    #3
  4. 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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> >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?
    >> >
    >> >
    >> >

    >>
    >>
    >>
     
    Peter Rilling, Feb 9, 2006
    #4
  5. 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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > >> news:...
    > >> >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?
    > >> >
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
    =?Utf-8?B?Q3NoYXJwR3V5?=, Feb 9, 2006
    #5
    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. David Lozzi
    Replies:
    3
    Views:
    1,993
    David Lozzi
    Jun 1, 2005
  2. NevilleDNZ
    Replies:
    9
    Views:
    462
    NevilleDNZ
    Aug 16, 2006
  3. JG
    Replies:
    0
    Views:
    312
  4. Jean-Hugues ROBERT

    Why no Proc##[]=() ? Why no Proc##replace() ?

    Jean-Hugues ROBERT, May 1, 2004, in forum: Ruby
    Replies:
    14
    Views:
    315
    Jean-Hugues ROBERT
    May 5, 2004
  5. Minkoo Seo

    Proc vs lambda vs proc

    Minkoo Seo, Feb 4, 2007, in forum: Ruby
    Replies:
    19
    Views:
    262
    Brian Candler
    Feb 6, 2007
Loading...

Share This Page