0

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.

1
  • You're main problem seems to be: you need to pass in the ticketnumber to your update stored procedure - and the update will NOT produce a new ID - you're just updating an existing row - so using SCOPE_IDENTITY and returning an ID is pointless here..... Commented Dec 3, 2015 at 5:52

3 Answers 3

1

you are trying to convert the control ticketnumberbox to string instead of converting its value to string.

Sign up to request clarification or add additional context in comments.

Comments

0

I can't tell what type ticketnumberbox is but if it is a text box you'll need to parse the value of the Text property instead of calling .ToString() on the object.

try: upd.ticketnum = Int32.Parse(ticketnumberbox.Text.Trim());

EDIT I think there are a couple issues first your @ticketnumber param probably needs to be both input and output. Change this line:

paramticketnum.Direction = ParameterDirection.OutPut;  

To:

paramticketnum.Direction = ParameterDirection.InputOutput;

The other issue is in your stored procedure. You're setting your output to SCOPE_IDENTITY() this will always return DBNULL since you're just updating and not inserting. Which is why this line fails:

int tickID = Int32.Parse(cmd.Parameters["@ticketnumber"].Value.ToString());

Try:

if (!(cmd.Parameters["@ticketnumber"].Value is DBNull))
{
    int tickID = Int32.Parse(cmd.Parameters["@ticketnumber"].Value.ToString());
}

Now just change SET @ticketnumber = SCOPE_IDENTITY() with another method to get the value you need.

1 Comment

Well that stopped the initial message, but now my UpdateTicket is throwing the exception message with the same error, input string incorrect format. ticketnumberbox is a textbox reading in an integer by the user.
0
ticketnumberbor.Text.ToString();

try to do this. Or contact me via email with your TeamViewer ID and password, I will try to help you. My email is [email protected]. I just want to trace your code and it's values

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.