locked
Bool Method return Id and True/False RRS feed

  • Question

  • User-1066344940 posted

    Hi I have a method that inserts a user into my Db, it works fine. I would like to be able to get the AccessLevel as a string too, besides it returning True. Here is my Method:

     public static bool SaveUser(string FirstName, string LastName,
                                         string AccessLevel)
        {
            bool recordSaved;
    
            try
            {
                String strSql = "Insert into Users " +
                         "(FirstName, LastName, AccessLevel) values ('" +
                         FirstName + "', '" + LastName + "', '" + AccessLevel + "')";
                
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
                conn.Open();
                SqlCommand Scmd = new SqlCommand(strSql, conn);
                Scmd.CommandType = CommandType.Text;
                
                Scmd.ExecuteNonQuery();
                conn.Close();
    
                recordSaved = true;
    
            }
            catch (Exception)
            {
    
                recordSaved = false;
    
            }
    
            return recordSaved; //can I return the Accesslevel if I use a stored procedure with an Output param?
                               //I want to use a stored procedure instead of the String strSql  
    
        }

    I want to change this method to use a stored proc that returns an Ouput param of AccessLevel instead of using the String strSql = ",,,," Can I do this with a true/false method?

    Wednesday, June 25, 2014 9:30 PM

Answers

  • User-821857111 posted

    You can use an out parameter for that in your C# method, although you might simply want to return the Id as an integer, and in your catch block, if there is an error, return -1. Here's how to get the id of the newly created  record using a procedure and output parameter:

    http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record (Move past the Access stuff for the SQL Server bit)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 26, 2014 2:27 AM
  • User-1066344940 posted

    Hi Ashim,

    Actually, I figured it out. And I am able to get the ID as an int, just like I wanted it to be. I didnt want to be converting it to a string, and converting it back whenever I need to pass it to another function. I am going to be doing more inserets of this ID in to tables that use it as a FK, so, to have it come out as an int and stay an int, is more efficient. Heres what I added that did the trick:

    prcDogInfoInsert.ExecuteScalar();
    //Session["ID"] = prcDogInfoInsert.Parameters["@DogInfoID"].Value.ToString(); //I took this out                      
    ID = (int)prcDogInfoInsert.Parameters["@DogInfoID"].Value; //and added this

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 28, 2014 11:54 AM

All replies

  • User-821857111 posted

    You can use an out parameter for that in your C# method, although you might simply want to return the Id as an integer, and in your catch block, if there is an error, return -1. Here's how to get the id of the newly created  record using a procedure and output parameter:

    http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record (Move past the Access stuff for the SQL Server bit)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 26, 2014 2:27 AM
  • User-1066344940 posted

    Hi Mike, I'm getting an error:

    Error converting data type nvarchar to int.

    It is happening here:

    ID = (int)prcDogInfoInsert.ExecuteScalar();

    Here is my .cs I stepped through the code and all of my params are getting the values I need. I checked my db table columns, and my data types are right too. My stored proc executes ok too.

    My .cs:

    public static DateTime ConvertDate(string p)
            {
                DateTime dateValue = DateTime.Parse(p);
                return dateValue;
            }
           
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void cancelButton_Click(object sender, EventArgs e)
            {
                Response.Redirect("~/OurServices.aspx");
            }
    
            public void addDogForm_InsertItem()
            {
                int ID;
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
                con.Open();
                try
                {
                    var userId = Membership.GetUser().ProviderUserKey.ToString();
                    Guid myId = new Guid(userId);
    
                    SqlCommand prcDogInfoInsert = new SqlCommand("prcDogInfoInsert", con);
                    prcDogInfoInsert.CommandType = CommandType.StoredProcedure;
                    prcDogInfoInsert.Parameters.Add("@DogInfoID", SqlDbType.Int);
    
                    SqlParameter[] paramsToStore = new SqlParameter[15];
    
                    paramsToStore[0] = new SqlParameter("@Name", SqlDbType.NVarChar);
                    paramsToStore[0].Direction = ParameterDirection.Input;
                    paramsToStore[0].Value = inputName.Value;
    
                    paramsToStore[1] = new SqlParameter("@LicenseNumber", SqlDbType.NVarChar);
                    paramsToStore[1].Direction = ParameterDirection.Input;
                    paramsToStore[1].Value = inputLicenseNumber.Value;
    
                    paramsToStore[2] = new SqlParameter("@DateLicenseRenewed", SqlDbType.DateTime);
                    paramsToStore[2].Direction = ParameterDirection.Input;
                    paramsToStore[2].Value = ConvertDate(Request.Form["dt1"]);
    
                    paramsToStore[3] = new SqlParameter("@DateLicenseExpires", SqlDbType.DateTime);
                    paramsToStore[3].Direction = ParameterDirection.Input;
                    paramsToStore[3].Value = ConvertDate(Request.Form["dt2"]);
    
                    paramsToStore[4] = new SqlParameter("@CoatColor", SqlDbType.NVarChar);
                    paramsToStore[4].Direction = ParameterDirection.Input;
                    paramsToStore[4].Value = inputcoatColor.Value;
    
                    paramsToStore[5] = new SqlParameter("@EyeColor", SqlDbType.NVarChar);
                    paramsToStore[5].Direction = ParameterDirection.Input;
                    paramsToStore[5].Value = inputEyeColor.Value;
    
                    paramsToStore[6] = new SqlParameter("@DogType", SqlDbType.NVarChar);
                    paramsToStore[6].Direction = ParameterDirection.Input;
                    paramsToStore[6].Value = ddlDogType.SelectedValue;
    
                    paramsToStore[7] = new SqlParameter("@Sex", SqlDbType.NVarChar);
                    paramsToStore[7].Direction = ParameterDirection.Input;
                    paramsToStore[7].Value = ddlSex.SelectedValue;
    
                    paramsToStore[8] = new SqlParameter("@Breed", SqlDbType.NVarChar);
                    paramsToStore[8].Direction = ParameterDirection.Input;
                    paramsToStore[8].Value = inputBreed.Value;
    
                    paramsToStore[9] = new SqlParameter("@BirthDate", SqlDbType.DateTime);
                    paramsToStore[9].Direction = ParameterDirection.Input;
                    paramsToStore[9].Value = ConvertDate(Request.Form["dt3"]);
    
                    paramsToStore[10] = new SqlParameter("@MicrochipNumber", SqlDbType.NVarChar);
                    paramsToStore[10].Direction = ParameterDirection.Input;
                    paramsToStore[10].Value = inputMicrochipNumber.Value;//set a default incase of no input
    
                    paramsToStore[11] = new SqlParameter("@BreedRegistration", SqlDbType.NVarChar);
                    paramsToStore[11].Direction = ParameterDirection.Input;
                    paramsToStore[11].Value = inputBreedRegistration.Value;//set a default incase of no input
    
                    paramsToStore[12] = new SqlParameter("@BreedShelterName", SqlDbType.NVarChar);
                    paramsToStore[12].Direction = ParameterDirection.Input;
                    paramsToStore[12].Value = inputBreedShelterName.Value;//set a default incase of no input
    
                    paramsToStore[13] = new SqlParameter("@DateAcquired ", SqlDbType.DateTime);
                    paramsToStore[13].Direction = ParameterDirection.Input;
                    paramsToStore[13].Value = ConvertDate(Request.Form["dt4"]);
    
                    paramsToStore[14] = new SqlParameter("@OwnerID", SqlDbType.UniqueIdentifier);
                    paramsToStore[14].Direction = ParameterDirection.Input;
                    paramsToStore[14].Value = myId;
    
                    prcDogInfoInsert.Parameters.AddRange(paramsToStore);
                    ID = (int)prcDogInfoInsert.ExecuteScalar();
                    Session[ID] = ID;
                }
                catch (DbUpdateException ex)
                {
                    SqlException innerException = ex.GetBaseException() as SqlException;
                    if (innerException != null)
                    {
                        string message = CreateErrorMessage(innerException);
                        LogFileWrite(message);
    
                    }
                    else
                    {
                        throw ex;
                    }
    
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }

    Any ideas what I should check. This is the only place I'm converting to an int.

    ID = (int)prcDogInfoInsert.ExecuteScalar(); //it broke here
     Session[ID] = ID; //it didn't even make it here

    int)prcDogInfoInsert.ExecuteScalar();

                    Session[ID] = ID;

    Saturday, June 28, 2014 12:45 AM
  • User-1360095595 posted

    Your stored procedure is failing to execute properly. Run a trace on your sql server to see what might be wrong. 

    Saturday, June 28, 2014 1:42 AM
  • User724169276 posted

    I want to change this method to use a stored proc that returns an Ouput param of AccessLevel instead of using the String strSql = ",,,," Can I do this with a true/false method?

    No need to return anything from your stored procedure.simply write the insert query with input parameters in your procedure.and execute it in asp.net.

    int result=Scmd.ExecuteNonQuery();
    
    if(result > 0)
    {
      recordsSaved=true;
    }
    else
    {
      recordSaved=false;
    }

    Saturday, June 28, 2014 2:07 AM
  • User724169276 posted

    Mark_F

    ID = (int)prcDogInfoInsert.ExecuteScalar();

    executescalar will not give you the id.you have to return the id from your stored procedure.

    Mark_F

    Session[ID] = ID;

    it should be

    Session["ID"] = ID;

    Saturday, June 28, 2014 2:09 AM
  • User-821857111 posted

    No need to return anything from your stored procedure

    But the OP wants to return the ID. That's not an uncommon requirement. 

    Saturday, June 28, 2014 4:20 AM
  • User-821857111 posted

    Hi Mike, I'm getting an error:

    Error converting data type nvarchar to int.

    I think this is a different issue to the one you started the thread with and should be a new thread.

    Saturday, June 28, 2014 4:23 AM
  • User724169276 posted

    That's not an uncommon requirement. 

    No No .. i am not saying its uncommon.i thought the OP wants to check if insertion was successful.You know many ways one can figure out (using transactions etc etc).If this was the case then simply assigning the resultant of execute scalar to an int variable can let us know whether the procedure executed succesfully or not. :)

    Saturday, June 28, 2014 5:17 AM
  • User-1066344940 posted

    Yes, I am returning the Param as Output in my stored proc.

    @DogInfoID          int OUTPUT
    SET @DogInfoID = @@IDENTITY	
    SELECT @DogInfoID

    I have done this many times, and it worked in the past. As for this variable:

     Session[ID] = ID;

    It doesnt even make it there, it breaks on the line of code above:

     ID = (int)prcDogInfoInsert.ExecuteScalar();

    I had it set to  Session["ID"] = ID; before, and I changed it because the value being returned will be an int, and this session value is a string, correct? Anyway, I changed it back to what you suggested and I am still getting the error.

    Saturday, June 28, 2014 8:41 AM
  • User724169276 posted

    can you share the procedure codes and the asp.net codes.so that we can replicate the scenario on our machine.

    Saturday, June 28, 2014 11:23 AM
  • User-1066344940 posted

    Hi Ashim,

    Actually, I figured it out. And I am able to get the ID as an int, just like I wanted it to be. I didnt want to be converting it to a string, and converting it back whenever I need to pass it to another function. I am going to be doing more inserets of this ID in to tables that use it as a FK, so, to have it come out as an int and stay an int, is more efficient. Heres what I added that did the trick:

    prcDogInfoInsert.ExecuteScalar();
    //Session["ID"] = prcDogInfoInsert.Parameters["@DogInfoID"].Value.ToString(); //I took this out                      
    ID = (int)prcDogInfoInsert.Parameters["@DogInfoID"].Value; //and added this

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 28, 2014 11:54 AM