Click here to Skip to main content
15,922,407 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
AM trying to update but get this error
syntax near '='


What I have tried:

con = New SqlConnection(cs)
           con.Open()
           Dim cb As String = "Update CourseFeePayment set CFP_ID=@d1, PaymentID=@d2, AdmissionNo=@d3, Session=@d4,TotalFee=@d6, DiscountPer=@d7, DiscountAmt=@d8, PreviousDue=@d9, Fine=@d10, GrandTotal=@d11, TotalPaid=@d12, ModeOfPayment=@d13, PaymentModeDetails=@d14, PaymentDue=@d16 where CFP_ID= '" & txtCFPId.Text & "'"
           cmd = New SqlCommand(cb)
           cmd.Connection = con
           cmd.Parameters.AddWithValue("@d1", Val(txtCFPId.Text))
           cmd.Parameters.AddWithValue("@d2", txtFeePaymentID.Text)
           cmd.Parameters.AddWithValue("@d3", txtAdmissionNo.Text)
           cmd.Parameters.AddWithValue("@d4", txtSession.Text)
           cmd.Parameters.AddWithValue("@d6", txtCourseFee.Text)
           cmd.Parameters.AddWithValue("@d7", txtDiscountPer.Text)
           cmd.Parameters.AddWithValue("@d8", txtDiscount.Text)
           cmd.Parameters.AddWithValue("@d9", txtPreviousDue.Text)
           cmd.Parameters.AddWithValue("@d10", txtFine.Text)
           cmd.Parameters.AddWithValue("@d11", txtGrandTotal.Text)
           cmd.Parameters.AddWithValue("@d12", txtTotalPaid.Text)
           cmd.Parameters.AddWithValue("@d13", cmbPaymentMode.Text)
           cmd.Parameters.AddWithValue("@d14", txtPaymentModeDetails.Text)
           cmd.Parameters.AddWithValue("@d16", txtBalance.Text)
           cmd.ExecuteNonQuery()
           con.Close()
           con = New SqlConnection(cs)
           con.Open()
           Dim cq As String = "delete from CourseFeePayment_Join where C_PaymentID= " & txtCFPId.Text & ""
           cmd = New SqlCommand(cq)
           cmd.Connection = con
           cmd.ExecuteNonQuery()
           con.Close()
           con.Open()
           Dim cb1 As String = "insert into CourseFeePayment_Join(C_PaymentID,Month, FeeName, Fee) VALUES (" & txtCFPId.Text & ",@d1,@d2,@d3)"
           cmd = New SqlCommand(cb1)
           cmd.Connection = con
           ' Prepare command for repeated execution
           cmd.Prepare()
           ' Data to be inserted
           For Each row As DataGridViewRow In dgw.Rows
               If Not row.IsNewRow Then
                   cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value)
                   cmd.Parameters.AddWithValue("@d2", row.Cells(1).Value)
                   cmd.Parameters.AddWithValue("@d3", Val(row.Cells(2).Value))
                   cmd.ExecuteNonQuery()
                   cmd.Parameters.Clear()
               End If
Posted
Updated 16-Feb-17 21:24pm
Comments
Maciej Los 17-Feb-17 3:27am    
Does CFP_ID field is numeric data type? Seems, it's numeric. So, why you're using '' aroud its value? This causes the database engine to treat it as string value.
I'd try something like this:
Hide   Copy Code
Dim cb As String = "Update CourseFeePayment set PaymentID=@d2, AdmissionNo=@d3, Session=@d4,TotalFee=@d6, DiscountPer=@d7, DiscountAmt=@d8, PreviousDue=@d9, Fine=@d10, GrandTotal=@d11, TotalPaid=@d12, ModeOfPayment=@d13, PaymentModeDetails=@d14, PaymentDue=@d16 where CFP_ID=@d1"

Never use concatenated text as command text, because of SQL Injection.
Karthik_Mahalingam 17-Feb-17 4:01am    
possible solution.
kntmickeal 18-Feb-17 4:53am    
Worked thanks alot

1 solution

First of all why are you taking only
txtCFPId.Text
by concatenating in SQl query.
Its vulnerable to SQL injection.

Please use parameter for this value also.
then you no need to take care of quotes for varchar columns also. which looks like causing the issue.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900