c# stored procedure don't work

Question

I use a stored procedure to update a table,but it's not working. Here it is :

ALTER PROCEDURE [dbo].[updateRessourceLigneFab]
-- Add the parameters for the stored procedure here
@CodeArticle nvarchar(300),
@Division nvarchar(300),
@Colonne nvarchar(300),
@Valeur nvarchar(300),
@IDres nvarchar(300)
AS BEGIN SET NOCOUNT ON;
update dbo.[Ressources PDP] set @Colonne = @Valeur where [Code article] = @CodeArticle and Division = @Division and LigneFab = @IDres
END
GO

i'm calling my stored procedure in c# like this :

        public static DataTable updateRessourceLigneFab(string CodeArticle, string Division, string Colonne, string Valeur, string IDres)
    {
        String querystring = "[TipiakTTP].[dbo].[updateRessourceLigneFab]";
        var lsParam = new List<SqlParameter>();
        AccesBase.ajouteParametre(ref lsParam, CodeArticle, "@CodeArticle");
        AccesBase.ajouteParametre(ref lsParam, Division, "@Division");
        AccesBase.ajouteParametre(ref lsParam, Colonne, "@Colonne");
        AccesBase.ajouteParametre(ref lsParam, Valeur, "@Valeur");
        AccesBase.ajouteParametre(ref lsParam, IDres, "@IDres");
        return AccesBase.execute(querystring, lsParam);
    }

here is the code i'm using to call the function that call my stored procedure :

 private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {
        var grid = (DataGridView)sender;
        if (grid[e.ColumnIndex, e.RowIndex].Value.ToString() != "")
        {
            string codearticle =  grid[4, 0].Value.ToString() ;
            string division = grid[3, 0].Value.ToString() ;
            int y = int.Parse(getWeekNumberIncrement(Week)) - int.Parse(getDateNow())+1;
            string val = "ress" + y.ToString();
            if (y < 10)
            {
                 val = "ress0" + y.ToString();
            }
            string colonne =  "'"+ val +"'" ;
            string valeur =  grid[0, 0].Value.ToString() ;
            string ligne =  grid[5, 0].Value.ToString() ;
            DataTable datagrid1 = Controller.Version.ControllerChoixVersion.getRessourceLigneFab(codearticle, division, ligne);
            if ( datagrid1.Rows.Count == 0)
            {
                Controller.Version.ControllerChoixVersion.insertRessourceLigneFab(codearticle, division, ligne);
            }
            Controller.Version.ControllerChoixVersion.updateRessourceLigneFab(codearticle, division, colonne, valeur, ligne);
        }

The problem is that i don't find out why my table isn't update, i tried many things and i'm still trying to fix it..

for information this sql statement works :

    update dbo.[Ressources PDP] set ress01 = '111' where [Code article] = '10000100510' and Division = '1000' and LigneFab = 'RECONDI'

i'm sure that is something simple,but i don't find it.

thanks for the help

UPDATE / SOLUTION :

using the code above in my stored procedure do the work :

ALTER PROCEDURE [dbo].[updateRessourceLigneFab]
-- Add the parameters for the stored procedure here
@CodeArticle nvarchar(300),
@Division nvarchar(300),
@Colonne nvarchar(300),
@Valeur nvarchar(300),
@IDres nvarchar(300)
AS BEGIN DECLARE @SQLString NVARCHAR(500); SET NOCOUNT ON;

        SET @SQLString = 'update dbo.[Ressources PDP] set '+@Colonne+' = '+@Valeur+' where [Code article] = '+@CodeArticle+' and Division = '+@Division+' and LigneFab = '+@IDres+'';
EXECUTE sp_executesql @SQLString
END
GO

Its the code for `AccesBase.execute` we need to answer this. also this bit `@Colonne = @Valeur` is never going to work. — Jamiec, Aug 25 '16 at 08:52
You cannot parameterize column *names* in SQL. Nor should you generally need to. It's usually an indication that the data model is wrong and that data items of the same type have ended up in different columns, when in fact they should all have occupied the same columns (but different rows) — Damien_The_Unbeliever, Aug 25 '16 at 08:52
That single line of SQL might work, but the stored proc won't. — DavidG, Aug 25 '16 at 08:53
i edited my question and added my working updated procedure as i can't copy it in comment. That's my bad, i didn't search with good keyword.Solution here : http://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-input-parameter-in-sql-stored-procedure — Tristan Sébillet, Aug 25 '16 at 09:09

score 1 · Accepted Answer · answered Aug 25 '16 at 08:57

1

The Problem is in this statement

update dbo.[Ressources PDP] set @Colonne = @Valeur where [Code article] = @CodeArticle and Division = @Division and LigneFab = @IDres

you cannot set @Colonne=@Valeur

answered Aug 25 '16 at 08:57

Ashu

462
3
16

c# stored procedure don't work

1 Answers1