how to pass string parameter to sql SP with ado.net command in c#?

Farshad Valizade 421 Reputation points
2023-05-22T04:56:41.2366667+00:00

hi every body.

I have a big problem with SP in sql server.

I have a SP that join 3 table and I pass two parameters to it . one for column name another for it's value.

ALTER procedure [dbo].[GetAllLine](@column nvarchar(10) , @value int)
as
begin
select * from 
Joint as j
inner join Line as l on l.LineID =j.LineID
inner join Sheet as s on s.LineID=s.LineID
where @column = @value
end

and my c# code:


        public static DataTable GetAll(string column , int value)
        {
            string query = "GetAllLine";
            SqlCommand cmd = new SqlCommand(query, Connection.Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@column", column);
            cmd.Parameters.AddWithValue("@value", value);

            DataTable dt = new DataTable();
            Connection.Open();
            try
            {
                SqlDataReader rd = cmd.ExecuteReader();
                dt.Load(rd);
                return dt;
            }
            catch
            {
                throw;
            }
            finally
            {
                Connection.Close();
            }
        } 

and my command btn :

DataTable dt = Test.GetAll("j.LineID", 2);
            dataGridView1.DataSource = dt;

I want use one query with different column name to pass to this SP.

exmalpe :

Test.GetAll("l.LineID", 21);
Test.GetAll("s.SheetId", 12);

after run get this error:

Conversion failed when converting the nvarchar value 'j.LineID' to data type int.

How Should I Pass String Column with Int Value to this SP?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,271 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,603 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Olaf Helper 42,926 Reputation points
    2023-05-22T07:20:22.83+00:00

    Conversion failed when converting the nvarchar value 'j.LineID' to data type int.

    As the error says, you have to use the right data types.