none
SqlCommand and SQL stored procedures RRS feed

  • Question

  • I am trying to enter some data into a database using a stored procedure through c#. Below is the code:


    addSiteCmd = new SqlCommand("sp_add_site", addSiteConn);
    addSiteCmd.CommandType = CommandType.StoredProcedure;
    addSiteCmd.Parameters.Add("@ID", SqlDbType.Char, 5).Value = selectedSite;
    addSiteCmd.Parameters.Add("@name", SqlDbType.VarChar, 250).Value = textBoxSiteName.Text;
    addSiteCmd.Parameters.Add("@area", SqlDbType.VarChar, 50).Value = comboBoxRegions.Text;
    addSiteCmd.Parameters.Add("@notes", SqlDbType.Text).Value = textBoxNotes.Text;
    addSiteCmd.Parameters.Add("@state", SqlDbType.Char, 2).Value = comboBoxState.Text;
    addSiteCmd.ExecuteNonQuery();


    the problem I am having is that the the @ID is a 5 digit number (ex: 02345). sometimes it starts with a zero, so SQL keeps truncating it (ex: 2345). Using query analyzer, I can set the column to char(5) and I can insert the ID by surrounding the 5 digits with '' but I can't seem to figure out how to do this with a stored procedure through c#.
    Saturday, August 4, 2007 8:32 PM

Answers

  • What datatype do you have the "selectedSite" variable defined as in your application?  Did you put a breakpoint on the line of code that sets that parameter value and see what the value of "selectedSite" is at that point?  What value is it at that point (put a breakpoint on that line, run a test, let us know what value you entered and what "selectedSite" actually is)?

     

    Saturday, August 4, 2007 9:19 PM

All replies

  • What datatype do you have the "selectedSite" variable defined as in your application?  Did you put a breakpoint on the line of code that sets that parameter value and see what the value of "selectedSite" is at that point?  What value is it at that point (put a breakpoint on that line, run a test, let us know what value you entered and what "selectedSite" actually is)?

     

    Saturday, August 4, 2007 9:19 PM
  • OK I did that and it shows the full 5 digits. I checked the stored procedure and it was type int, so I changed it to char and it works now. Thanks
    Saturday, August 4, 2007 9:38 PM