I'm trying to update a table with a stored procedure in C# and ASP.NET.
I have a table with a self increment value @ticketnumber.
I also have a stored procedure that inserts data and creates the next value of the ID.
I have another stored procedure that is supposed to update this table.
My initial issue is that I kept getting the error
cannot implicitly convert type string to int
I then messed around a bit and got the error to go away. When I run my code, I'll input the value, but Visual Studio will then throw the "Input String Was Not in the Correct Format" error
To clarify, my stored procedure to insert works just fine, but I cannot use the ID value to update it.
Here is my code for my stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[StoredProcUpdateTicket]
@fname nvarchar(50),
@lname nvarchar(50),
@email nvarchar(50),
@severityID bigint,
@statusID bigint,
@departmentID bigint,
@issuetype bigint,
@issuedesc nvarchar(500),
@ticketnumber bigint OUTPUT
AS
BEGIN
UPDATE supporticket
SET fname = @fname,
lname = @lname,
email = @email,
severityID = @severityID,
statusID = @statusID,
departmentID = @departmentID,
issuetype = @issuetype,
issuedesc = @issuedesc
WHERE
ticketnumber = @ticketnumber
SET @ticketnumber = SCOPE_IDENTITY()
RETURN @ticketnumber
END
Here is my C# code to update:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.Security;
using System.Data.SqlClient;
namespace TrackIT
{
public class UpdateTicket
{
public string fname { get; set; }
public string lname { get; set; }
public string email { get; set; }
public int statusID { get; set; }
public string issuedesc { get; set; }
public int severityID { get; set; }
public int issuetype { get; set; }
public int departmentID { get; set; }
public int ticketnum { get; set; }
public void Update()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("StoredProcUpdateTicket"))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramFName = new SqlParameter("@fname", SqlDbType.NVarChar, 50);
paramFName.Value = fname;
cmd.Parameters.Add(paramFName);
SqlParameter paramLName = new SqlParameter("@lName", SqlDbType.NVarChar, 50);
paramLName.Value = lname;
cmd.Parameters.Add(paramLName);
SqlParameter paramEmail = new SqlParameter("@email", SqlDbType.NVarChar, 50);
paramEmail.Value = email;
cmd.Parameters.Add(paramEmail);
SqlParameter paramSeverity = new SqlParameter("@severityID", SqlDbType.BigInt);
paramSeverity.Value = severityID;
cmd.Parameters.Add(paramSeverity);
SqlParameter paramStatus = new SqlParameter("@statusID", SqlDbType.BigInt);
paramStatus.Value = statusID;
cmd.Parameters.Add(paramStatus);
SqlParameter paramDepartment = new SqlParameter("@departmentID", SqlDbType.BigInt);
paramDepartment.Value = departmentID;
cmd.Parameters.Add(paramDepartment);
SqlParameter paramissuedesc = new SqlParameter("@issuedesc", SqlDbType.NVarChar, 500);
paramissuedesc.Value = issuedesc;
cmd.Parameters.Add(paramissuedesc);
SqlParameter paramissuetype = new SqlParameter("@issuetype", SqlDbType.BigInt);
paramissuetype.Value = issuetype;
cmd.Parameters.Add(paramissuetype);
SqlParameter paramticketnum = new SqlParameter("@ticketnumber", SqlDbType.BigInt);
paramticketnum.Direction = ParameterDirection.Output;
paramticketnum.Value = ticketnum;
cmd.Parameters.Add(paramticketnum);
try
{
con.Open();
cmd.Connection = con;
cmd.ExecuteNonQuery();
int tickID = Int32.Parse(cmd.Parameters["@ticketnumber"].Value.ToString());
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
finally
{
cmd.Dispose();
con.Close();
}
}
}
}
}
}
And lastly, where I implement my update
protected void btnUpdate_Click(object sender, EventArgs e)
{
upd.ticketnum = Int32.Parse(ticketnumberbox.ToString().Trim());
upd.fname = fnamebox.Text;
upd.lname = lnamebox.Text;
upd.email = emailbox.Text;
upd.severityID = Convert.ToInt32(listSeverity.SelectedValue);
upd.statusID = Convert.ToInt32(liststatus.SelectedValue);
upd.departmentID = Convert.ToInt32(ListDept.SelectedValue);
upd.issuetype = Convert.ToInt32(Listissuetype.SelectedValue);
upd.issuedesc = BoxDetails.Text;
upd.Update();
uForm.Visible = false;
UpdateSuccess.Visible = true;
}
My problem lies with creating a textbox for ticketnumber and being able to use that user input to then update the database.
Thanks in advance.
ticketnumberto your update stored procedure - and the update will NOT produce a newID- you're just updating an existing row - so usingSCOPE_IDENTITYand returning an ID is pointless here.....