passing "Where" value with Prepared Statement?

[barianto]

Hi, i have this

    Set cmdSQLPembayaran = New ADODB.Command
    With cmdSQLPembayaran
        .ActiveConnection = objConnMarketing
        .Prepared = True
        .Parameters.Append .CreateParameter("Periode", adChar, adParamInput, 6)
        .Parameters.Append .CreateParameter("Site", adChar, adParamInput, 1)
        .Parameters.Append .CreateParameter("Slip", adChar, adParamInput, 11)
        .Parameters.Append .CreateParameter("Faktur", adChar, adParamInput, 9)
        .Parameters.Append .CreateParameter("NotaBank", adChar, adParamInput, 9)
        .Parameters.Append .CreateParameter("TanggalSlip", adDate, adParamInput)
        .Parameters.Append .CreateParameter("SudahBayar", adDouble, adParamInput)
        .Parameters.Append .CreateParameter("BayarPokok", adDouble, adParamInput)
        .Parameters.Append .CreateParameter("BayarDenda", adDouble, adParamInput)
        .Parameters.Append .CreateParameter("BayarLebih", adDouble, adParamInput)
        .Parameters.Append .CreateParameter("Slip", adChar, adParamInput, 11)
    End With



    varStrSql = " UPDATE pembayaran SET Periode = ?, Site = ?, Slip = ?, Faktur = ?, NotaBank = ?,  " _
                & " TanggalSlip = ?, SudahBayar = ?, BayarPokok = ?, BayarDenda = ?, BayarLebih = ? " _
                & " WHERE Slip = ?"
    With cmdSQLPembayaran
        .CommandType = adCmdText
        .CommandText = varStrSql
    End With
    For x = 1 To (fg.Rows - 1)
                'cmdSQLPembayaran("Periode") = TAHUN & BULAN
                'cmdSQLPembayaran("Site") = SITE
                'cmdSQLPembayaran("NotaBank") = Trim(RemoveChar(myCmbNotaBank.Text, "."))
                'cmdSQLPembayaran("TanggalSlip") = mytTanggalSetor.Text
                
                cmdSQLPembayaran("Slip") = Trim(RemoveChar(fg.TextMatrix(x, 0), ".")) & Trim(fg.TextMatrix(x, 1))
                cmdSQLPembayaran("Faktur") = Trim(RemoveChar(fg.TextMatrix(x, 0), "."))
                cmdSQLPembayaran("SudahBayar") = CCur(Trim(fg.TextMatrix(x, 4)))
                cmdSQLPembayaran("BayarPokok") = CCur(Trim(fg.TextMatrix(x, 6)))
                cmdSQLPembayaran("BayarDenda") = CCur(Trim(fg.TextMatrix(x, 7)))
                cmdSQLPembayaran("BayarLebih") = CCur(Trim(fg.TextMatrix(x, 8)))
                cmdSQLPembayaran.Execute , , adCmdText + adExecuteNoRecords       'Ctatan: the last two arguments used for Execute here makes the execution of the command faster
                Debug.Print "masuk " & Trim(RemoveChar(fg.TextMatrix(x, 0), ".")) & Trim(fg.TextMatrix(x, 1))
    Next

I already debug "Slip" Parameter it should meet the condition. But somehow it never meet any condition that it should. What's wrong with my code above?
Should i create new parameter object for "Slip" since it "calling" twice, once in UPDATE and once in WHERE CLAUSE?
Should i create different parameter object (adParamOutput, adParamReturnValue, etc) for "Slip" that will use in WHERE Clause?

Please help....

[erickwidya]

u use adParam output/input/return value if u need to ouput/input/return for Query u specified and is sure need to be SPROC in MSSQL i think..if u need to run it against Access then it must do different way

u need to pass this Param using @PARAM_NAME and for the query u need to use something like this

SELECT * FROM Table_1 WHERE UserName = @PARAM_NAME

Should i create new parameter object for "Slip" since it "calling" twice, once in UPDATE and once in WHERE CLAUSE?

if u the 'Slip' is the same then u don't need to create new param, if not i need to create new param using different name hence make the same in the query

HTH

[barianto]

Thanks Eric,

But how i passed this @PARAM_NAME using prepared statement?
Can you give me example (on my code above)?

[si_the_geek]

Is the value the same for each (in the Update and Where clauses)?

If so, simply remove it from the Update clause (and the associated parameter), as it is pointless to update a field to the value it already is.

If not, change the name of your second Slip parameter (eg: .CreateParameter("SlipOld", ).

[barianto]

The Value "Slip" Parameter were different (in the UPDATE and WHERE clause).

If i create different parameter for "Slip" in WHERE clause, how SQL statement will match it with correct field in my table (which is "Slip")?

Is that parameter regconized by order it declared not by it's name?

Thanks guys

[si_the_geek]

The parameters are done by order (the first one you .Append is the first ? in the command text), not by name.

The name is basically for your own benefit.

[barianto]

Thanks si_the_geek. Yes it;s by order it append, now i know what solution is best for me.

Thanks.