Sql Parameters passing with @parameter = value type

Question

I am using an accounting program. I want to implement one feature of this program to my C# application. Accounting Program uses MS SQL database. So I have looked up SQL trace logs via SQL Server Profiler.

Accounting Program sends this command to database that i want to implement in my app:

exec sp_executesql N'UPDATE "MyDB".."MyTable"
 SET "CARDREF"=@P1,"DATE_"=@P2,"MODULENR"=@P3,"SIGN"=@P4,"FICHEREF"=@P5,
"FICHELINEREF"=@P6,"TRCODE"=@P7,"TOTAL"=@P8,(....)
WHERE "LOGICALREF"=@P78 AND "CARDREF"=@P79 AND "DATE_"=@P80 AND "MODULENR"=@P81
 AND "SIGN"=@P82 AND (...),

N'@P1 int,@P2 datetime,@P3 smallint,@P4 smallint,@P5 int,@P6 int,@P7 smallint,
@P8 float,@P9 float,@P10 float,@P11 float,@P12 int,@P13 smallint,@P14 smallint (....),

12309,'2016-01-01 00:00:00',5,0,1417,0,14,1126,9000000000001,(.....)

I implemented this query to my app but i have a problem. My app sends parameter values like this (From SQL Server Profiler Trace Logs):

exec sp_executesql N'UPDATE "MyDB".."MyTable"
 SET "CARDREF"=@P1,"DATE_"=@P2,"MODULENR"=@P3,
"SIGN"=@P4,"FICHEREF"=@P5,"FICHELINEREF"=@P6,"TRCODE"=@P7,"TOTAL"=@P8,(....)
 WHERE "LOGICALREF"=@P78 AND "CARDREF"=@P79 AND "DATE_"=@P80 AND "MODULENR"=@P81
 AND "SIGN"=@P82 AND (...),

 N'@P1 int,@P2 datetime,@P3 smallint,@P4 smallint,@P5 int,@P6 int,@P7 smallint, @P8 float,
@P9 float,@P10 float,@P11 float,@P12 int,@P13 smallint,@P14 smallint (....),    


@P1=12309,@P2='2016-04-17 00:00:00',@P3=6,@P4=0,@P5=181,@P6=1068,@P7=3,@P8=5138,8999999999996,
 @P9=5138,8999999999996,@P10=0,@P11=0,@P12=11636,@P13=0,@P14=0,(...)

You see my app's parameter values passing like this; @Parameter = value. Not like the accounting program. (Only parameter value, without @Parameter tag.)

Here's my code below;

using (SqlCommand cmd = new SqlCommand("UPDATE"))
            {
                SqlTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = trans;
                cmd.Connection = connection;


            cmd.CommandText = "UPDATE MyTable SET CARDREF=@P1,DATE_=@P2,MODULENR=@P3,
SIGN=@P4,FICHEREF=@P5,FICHELINEREF=@P6, TRCODE=@P7,TOTAL=@P8,PAID=@P9,
EARLYINTRATE=@P10,LATELYINTRATE=@P11,CROSSREF=@P12,PAIDINCASH=@P13,CANCELLED=@P1
4,PROCDATE=@P15,TRCURR=@P16,TRRATE=@P17,REPORTRATE=@P18(....)
 WHERE LOGICALREF=@P78 AND CARDREF=@P79 AND DATE_=@P80 AND MODULENR=@P81 AND      SIGN=@P82
 AND FICHEREF=@P83 AND FICHELINEREF=@P84 AND TRCODE=@P85 AND TOTAL=@P86 AND     PAID=@P87
 AND EARLYINTRATE=@P88 AND LATELYINTRATE=@P89 AND CROSSREF=@P90";

                cmd.Parameters.Add("@P1", SqlDbType.Int).Value = ptrns1.CARDREF;
                cmd.Parameters.Add("@P2", SqlDbType.DateTime).Value = ptrns1.DATE_;
                cmd.Parameters.Add("@P3", SqlDbType.SmallInt).Value = ptrns1.MODULENR;
                cmd.Parameters.Add("@P4", SqlDbType.SmallInt).Value = ptrns1.SIGN;
                cmd.Parameters.Add("@P5", SqlDbType.Int).Value = ptrns1.FICHEREF;               
                cmd.Parameters.Add("@P6", SqlDbType.Int).Value = ptrns1.FICHELINEREF;
                (......)
cmd.ExecuteNonQuery();
        }

What is your exact problem? It is not quite clear from your question - what are you asking.... — Andrey Korneyev, Feb 11 '16 at 08:49
I'm guessing, based on your trace, that your issue is you are supplying floats in your locale-specific format (i.e. comma rather than period for the decimal point), whereas the locale of the database might be different. Do you have the actual error message your sql returns? — dash, Feb 11 '16 at 08:53
@dash There is no error messages thrown. But update fails beacuse no records supplies where condition cause of passing parameters @parameter=value type. — erhanzeyrek, Feb 11 '16 at 09:09
@erhanzeyrek so if you run the SQL against the database directly does it work? If the record doesn't exist, you can't update it and would instead need to insert it. — dash, Feb 11 '16 at 10:02

Sql Parameters passing with @parameter = value type

0 Answers0