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: 

Answer 1

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.

Answer 2

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());

Answer 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()));