Error converting data type varchar to int (stored procedure C# ASP.NET)

Question

I am new in C# and i am working on a project where you call stored procedures from sql database in asp.net C#. In my table, the 'ID' uses varchar datatype. when ever i type the ID in a textbox and click in the search button, it generate an error

System.Data.SqlClient.SqlException: Error converting data type varchar to int.*

I have gone through the code over and over again but i can't see the error. Kindly assist. Thank you.

MYCODE

string connString2 = "Data Source=EFTSRV4;Initial Catalog=PaySwitch;Integrated Security=True";
        SqlConnection con = new SqlConnection(connString2);
        con.Open();
        SqlCommand cmd = new SqlCommand("GetMemberDetailsByID", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetMemberDetailsByID";
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        DataSet ds = new DataSet();
        cmd.Parameters.Add("@MBID", SqlDbType.VarChar).Value = (txtSearch.Text.Trim());
        cmd.ExecuteNonQuery();
        cmd.Connection = con;

        //da.Fill(ds, "Members");
        //(SqlDbType.Int).Parse(da.RowUpdated[0]["@MBID"]);
        // Mobileno = Convert.ToInt32(txmobileno.Text);
        //cmd.Parameters.Add("@MBID", SqlDbType.Int).Value = (txtSearch.Text.Trim());
        try
        {
            dg_Data.EmptyDataText = "No Records Found";
            dg_Data.DataSource = cmd.ExecuteReader();
            dg_Data.DataBind();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }

MYSTOREDPROCEDURE

USE [PaySwitch]
GO
/****** Object:  StoredProcedure [dbo].[GetMemberDetailsByID]    Script Date: 03/02/2014 15:19:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetMemberDetailsByID] (
@MBID VARCHAR (20)
)
AS
BEGIN
SELECT MBCompanyName, MBContactAddress1, MBContactCity,MBContactCountry, MBContactPostCode, MBContactPhone,MBContactEmailAdmin,MInstitutions FROM [dbo].[Members]
WHERE MBID=@MBID
END

MYERROR

Exception Details: System.Data.SqlClient.SqlException: Error converting data type varchar to int.

Source Error: 


Line 38:         DataSet ds = new DataSet();
Line 39:         cmd.Parameters.Add("@MBID", SqlDbType.VarChar).Value = (txtSearch.Text.Trim());
Line 40:         cmd.ExecuteNonQuery();
Line 41:         cmd.Connection = con;
Line 42:

Habib · Accepted Answer · 2014-02-03 13:49:20Z

3

From the error it appears that your column MBID in your table Members is of type int and you are trying to compare it with VARCHAR. Modify your stored procedure to receive a parameter of type int instead of VARCHAR like:

ALTER PROCEDURE [dbo].[GetMemberDetailsByID] (
@MBID INT (20)  --HERE it should be INT
)

and for sending parameters through C# use AddWithValue method like:

cmd.Parameters.AddWithValue("@MBID", int.Parse(txtSearch.Text.Trim()));

answered Feb 3, 2014 at 13:49

Habib

224k30 gold badges420 silver badges446 bronze badges

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

2 Comments

Hannah Over a year ago

Thank you for the response. From my Members' table, MBID is of datatype varchar. However, i have changed it to 'int' but still getting an error "Value was either too large or too small for an Int32." I have amended the stored procedure too (to int). My MBID is a 13 digits code(6161101272995), could it be the reason for the error? If so, how can i amend? thanks

Fayilt Over a year ago

I've amended my answer (above) to reflect this.

Fayilt · Accepted Answer · 2014-02-03 15:31:18Z

UPDATE (after the author's comment about int being too large).

As you noted, the value is too large for Int32. If MBID is just a "code" and you don't need the numerical meaning of the value, then you can to use varchar in SQL Server and string in C# as a datatype for MBID.

Your stored procedure and SqlParameter creation already use strings, so you just need to change the datatype on your table to be varchar(20) (adjust the size accordingly).

Here is SQL to amend your table:

(I'm sure you won't, but just to ensure - don't run it in production before testing somewhere else):

alter table dbo.Members alter column MBID varchar(20)

you can add other keywords to the query above to make the column not nullable, create constraints, etc.

Alternatively, if you want to stick to a numeric type, you can use decimal (it exists in both SQL and C#).

Old answer:

The error is coming from your stored procedure, not from .Net.

One possible reason: Your MBID parameter is varchar(20), but the MBID column in Members is int. Change the parameter to be int and see if it fixes the problem.

You'll also need to change this line in C#:

cmd.Parameters.Add("@MBID", SqlDbType.VarChar).Value = (txtSearch.Text.Trim());

to

cmd.Parameters.Add("@MBID", SqlDbType.Int).Value = Convert.ToInt32(txtSearch.Text.Trim());

Hannah · Accepted Answer · 2014-02-04 05:19:46Z

0

Thank you guys for your assistance. truly appreciated. I changed the MBID datatype back to varchar (20).

ALTER PROCEDURE [dbo].[GetMemberDetailsByID] (
@MBID VARCHAR (20)
)

Then made the changes on the code in c#

cmd.Parameters.Add("@MBID", SqlDbType.VarChar).Value = txtSearch.Text.Trim();

And got the results. Once again, thank you.

answered Feb 4, 2014 at 5:19

Hannah

131 gold badge1 silver badge6 bronze badges

Collectives™ on Stack Overflow

Error converting data type varchar to int (stored procedure C# ASP.NET)

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related