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?
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:
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
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.
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. :)
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.
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
Mark_F
Member
359 Points
327 Posts
Bool Method return Id and True/False
Jun 25, 2014 09:30 PM|Mark_F|LINK
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?
bool storedprocedureoutput
Mikesdotnett...
All-Star
184233 Points
26625 Posts
Moderator
MVP
Re: Bool Method return Id and True/False
Jun 26, 2014 02:27 AM|Mikesdotnetting|LINK
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)
bool storedprocedureoutput
ASP.NET Tutorials | Learn Entity Framework Core
Mark_F
Member
359 Points
327 Posts
Re: Bool Method return Id and True/False
Jun 28, 2014 12:45 AM|Mark_F|LINK
Hi Mike, I'm getting an error:
It is happening here:
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.
int)prcDogInfoInsert.ExecuteScalar();
Session[ID] = ID;
bool storedprocedureoutput
MetalAsp.Net
All-Star
101929 Points
20709 Posts
Moderator
Re: Bool Method return Id and True/False
Jun 28, 2014 01:42 AM|MetalAsp.Net|LINK
Your stored procedure is failing to execute properly. Run a trace on your sql server to see what might be wrong.
bool storedprocedureoutput
Ashim Chatte...
Star
13650 Points
5473 Posts
Re: Bool Method return Id and True/False
Jun 28, 2014 02:07 AM|Ashim Chatterjee|LINK
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; }bool storedprocedureoutput
Ashim Chatte...
Star
13650 Points
5473 Posts
Re: Bool Method return Id and True/False
Jun 28, 2014 02:09 AM|Ashim Chatterjee|LINK
executescalar will not give you the id.you have to return the id from your stored procedure.
it should be
Session["ID"] = ID;bool storedprocedureoutput
Mikesdotnett...
All-Star
184233 Points
26625 Posts
Moderator
MVP
Re: Bool Method return Id and True/False
Jun 28, 2014 04:20 AM|Mikesdotnetting|LINK
But the OP wants to return the ID. That's not an uncommon requirement.
bool storedprocedureoutput
ASP.NET Tutorials | Learn Entity Framework Core
Mikesdotnett...
All-Star
184233 Points
26625 Posts
Moderator
MVP
Re: Bool Method return Id and True/False
Jun 28, 2014 04:23 AM|Mikesdotnetting|LINK
I think this is a different issue to the one you started the thread with and should be a new thread.
bool storedprocedureoutput
ASP.NET Tutorials | Learn Entity Framework Core
Ashim Chatte...
Star
13650 Points
5473 Posts
Re: Bool Method return Id and True/False
Jun 28, 2014 05:17 AM|Ashim Chatterjee|LINK
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. :)
bool storedprocedureoutput
Mark_F
Member
359 Points
327 Posts
Re: Bool Method return Id and True/False
Jun 28, 2014 08:41 AM|Mark_F|LINK
Yes, I am returning the Param as Output in my stored proc.
I have done this many times, and it worked in the past. As for this variable:
It doesnt even make it there, it breaks on the line of code above:
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.
bool storedprocedureoutput
Ashim Chatte...
Star
13650 Points
5473 Posts
Re: Bool Method return Id and True/False
Jun 28, 2014 11:23 AM|Ashim Chatterjee|LINK
can you share the procedure codes and the asp.net codes.so that we can replicate the scenario on our machine.
bool storedprocedureoutput
Mark_F
Member
359 Points
327 Posts
Re: Bool Method return Id and True/False
Jun 28, 2014 11:54 AM|Mark_F|LINK
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:
bool storedprocedureoutput