Click here to Skip to main content
15,900,461 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
Hi Guys

so currently i'm busy with a personal project, however i am getting errors in my update statement, i got 2 forms
form1 is where the customer enters his personal details(insert statement)
form2 is where the customer enters his credential details however the credential details are on the same table as the personal details
here is my form2 code
VB
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\MAC\Documents\132.mdb")

    Dim cmd As New OleDbCommand
    con.Open()
    cmd.Connection = con

    cmd.CommandText = "UPDATE netcash " & _
        " SET Account Holder Name =" & Me.txtaccHName.Text & _
        ", Bank Name='" & Me.txtBName.Text & "'" & _
        ",Account Type='" & Me.txtAccType.Text & "'" & _
        ",Account Number='" & Me.txtAccNo.Text & "'" & _
        ",Branch Code='" & Me.txtBranchCode.Text & "'" & _
        ",Gross Salary='" & Me.txtGrossS.Text & "'" & _
        ",Nett Salary='" & Me.txtNettS.Text & "'" & _
        ",Spouse Gross='" & Me.txtSpouseG.Text & "'" & _
        ",Spouse Nett='" & Me.txtSpouseN.Text & "'" & _
        ",Total Income='" & Me.txtTIncome.Text & "'" & _
        ",Funeral Cover='" & Me.cmbfuneral.Text & "'" & _
        ",Medical Aid='" & Me.cmbmedical.Text & "'" & _
        ",Life Insurance='" & Me.cmbinsurance.Text & "'" & _
        ",Groceries='" & Me.txtGroceries.Text & "'" & _
        ",Rent/Bond='" & Me.txtRent.Text & "'" & _
        ",School fees='" & Me.txtSfees.Text & "'" & _
        ",Transport='" & Me.txtTransport.Text & "'" & _
        ",Clothing='" & Me.txtClothing.Text & "'" & _
        ",Domestic Worker='" & Me.txtDomesticW.Text & "'" & _
        ",Water and Electricity='" & Me.txtWandE.Text & "'" & _
        ",Rates='" & Me.txtRates.Text & "'" & _
        ",Landline Phone='" & Me.txtLandline.Text & "'" & _
        ",Cell phone='" & Me.txtCellphone.Text & "'" & _
        ",Levies='" & Me.txtLevies.Text & "'" & _
        ",Bank Charges='" & Me.txtBCharges.Text & "'" & _
        ",Medical Expenses='" & Me.txtMedical.Text & "'" & _
        ",Maintenance='" & Me.txtMaintenance.Text & "'" & _
        ",Total Expenses='" & Me.txtTexpenses.Text & "'" & _
        " WHERE ID Number='" & Me.txtvalueid.Tag
   
    cmd.ExecuteNonQuery()
   
    MsgBox("Record Updated Successfully")
End Sub
Posted
Updated 21-May-15 0:27am
v2
Comments
Tomas Takac 21-May-15 6:31am    
What error? I see two problems:
1) Your column names contain spaces, you need to enclose them in []
2) There is no closing apostrophe after txtvalueid.Tag
BTW your code is vulnerable to SQL injection. You should use parameters instead of concatenating the query text.
ebie147 21-May-15 6:36am    
here is the error i get:-

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in UPDATE statement.
Michael_Davies 21-May-15 6:48am    
Before you execute the nonquery dump the command string,that way you might spot a problem easier.

Your first field does not have enclosing quotes, presume it is a number field not a string?
ebie147 21-May-15 6:57am    
what do you mean by dump the command string? and no they all strings
Michael_Davies 21-May-15 15:30pm    
output the string, either using debugger or copy to clipboard the choice is endless.

It still stands then that THE FIRST field is NOT enclosed in single quotes, did you miss that part?

As a follow-on from the comments and solution 1 try something like this (NB untested)
VB
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\MAC\Documents\132.mdb")

    Dim cmd As New OleDbCommand
    con.Open()
    cmd.Connection = con

    Dim sql As StringBuilder = New StringBuilder("UPDATE netcash SET ")
    sql.Append("[Account Holder Name] = ?, Bank Name= ?, ")
    sql.Append("[Account Type] = ?, [Account Number] = ?,")
    sql.Append("[Branch Code]=?,[Gross Salary]=?,")
    sql.Append("[Nett Salary]=?,[Spouse Gross]=?,")
    sql.Append("[Spouse Nett]=?,[Total Income]=?,")
    sql.Append("[Funeral Cover]=?,[Medical Aid]=?,")
    sql.Append("[Life Insurance]=?,[Groceries]=?,")
    sql.Append("[Rent/Bond]=?,[School fees]=?,")
    sql.Append("[Transport]=?,[Clothing]=?,")
    sql.Append("[Domestic Worker]=?,[Water and Electricity]=?,")
    sql.Append("[Rates]=?,[Landline Phone]=?,")
    sql.Append("[Cell phone]=?,[Levies]=?,")
    sql.Append("[Bank Charges]=?,[Medical Expenses]=?,")
    sql.Append("[Maintenance]=?,[Total Expenses]=?")
    sql.Append(" WHERE [ID Number]=?")

    cmd.CommandText = sql.ToString()

    'NB with unnamed parameters the order below is fundamental
    cmd.Parameters.AddWithValue("AHName", txtaccHName.Text)
    cmd.Parameters.AddWithValue("BankName", txtBName.Text)
    cmd.Parameters.AddWithValue("AccType", txtAccType.Text)
    cmd.Parameters.AddWithValue("AccNumber", txtAccNo.Text)
    cmd.Parameters.AddWithValue("Branch", txtBranchCode.Text)
    cmd.Parameters.AddWithValue("GrossSalary", txtGrossS.Text)
    cmd.Parameters.AddWithValue("NettSalary", txtNettS.Text)
    cmd.Parameters.AddWithValue("SpouseGross", txtSpouseG.Text)
    cmd.Parameters.AddWithValue("SpouseNett", txtSpouseN.Text)
    cmd.Parameters.AddWithValue("TotalIncome", txtTIncome.Text)
    cmd.Parameters.AddWithValue("FuneralCover", cmbfuneral.Text)
    cmd.Parameters.AddWithValue("MedicalAid", cmbmedical.Text)
    cmd.Parameters.AddWithValue("LifeInsurance", cmbinsurance.Text)
    cmd.Parameters.AddWithValue("Groceries", txtGroceries.Text)
    cmd.Parameters.AddWithValue("RentBond", txtRent.Text)
    cmd.Parameters.AddWithValue("Schoolfees", txtSfees.Text)
    cmd.Parameters.AddWithValue("Transport", txtTransport.Text)
    cmd.Parameters.AddWithValue("Clothing", txtClothing.Text)
    cmd.Parameters.AddWithValue("DomesticWorker", txtDomesticW.Text)
    cmd.Parameters.AddWithValue("WaterElect", txtWandE.Text)
    cmd.Parameters.AddWithValue("Rates", txtRates.Text)
    cmd.Parameters.AddWithValue("Landline", txtLandline.Text)
    cmd.Parameters.AddWithValue("Cellphone", txtCellphone.Text)
    cmd.Parameters.AddWithValue("Levies", txtLevies.Text)
    cmd.Parameters.AddWithValue("BankCharges", txtBCharges.Text)
    cmd.Parameters.AddWithValue("Medical", txtMedical.Text)
    cmd.Parameters.AddWithValue("Maintenance", txtMaintenance.Text)
    cmd.Parameters.AddWithValue("TotalExpenses", txtTexpenses.Text)
    cmd.Parameters.AddWithValue("IDNumber", txtvalueid.Tag)

    If cmd.ExecuteNonQuery() > 0 Then
        MsgBox("Record Updated Successfully")
    End If

End Sub
 
Share this answer
 
v2
Comments
ebie147 21-May-15 7:46am    
would sql work with oledb?
CHill60 21-May-15 8:02am    
I don't understand your question - if you mean "sql" in my solution it's just a variable name to temporarily build the CommandText
ebie147 21-May-15 7:58am    
still receiving syntax error
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in UPDATE statement.
CHill60 21-May-15 8:01am    
I put too many commas in when I was constructing the string - I've fixed the code in my solution now
ebie147 21-May-15 8:03am    
sql.Append("[Account Holder Name] = ?, Bank Name= ?, ")
sql.Append("[Account Type] = ?, [Account Number] = ?,")
sql.Append("[Branch Code]=?,[Gross Salary]=?,")
sql.Append("[Nett Salary]=?,[Spouse Gross]=?,")
sql.Append("[Spouse Nett]=?,[Total Income]=?,")
sql.Append("[Funeral Cover]=?,[Medical Aid]=?,")
sql.Append("[Life Insurance]=?,[Groceries]=?,")

i changed the "?" with their parameter name, is this correct or should i leave them as "?"
Oh dear...
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

And also change your column names, or surround them with '[' and ']' pairs:
Any column name with spaces in will cause a syntax error unless escaped with brackets.
SQL
cmd.CommandText = "UPDATE netcash " & _
    " SET Account Holder Name =" & Me.txtaccHName.Text & _

Needs to be:
SQL
cmd.CommandText = "UPDATE netcash " & _
    " SET [Account Holder Name] =" & Me.txtaccHName.Text & _


But seriously, concatenating strings is going to cause you trouble, and risk your DB being deleted.
 
Share this answer
 
Comments
ebie147 21-May-15 7:12am    
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error (missing operator) in query expression '',Bank Name='',Account Type='',Account Number='',Branch Code='',Gross Salary='',Nett Salary='',Spouse Gross='',Spouse Nett='',Total Income='',Funeral Cover='',Medical Aid='',Life Insurance='',Groceries='',Rent/Bond='',School fees='',Transport='',Clothing='.

this is what comes up when i tried adding the [ ].
Afzaal Ahmad Zeeshan 21-May-15 7:14am    
OriginalGriff has almost given the best possible idea of how to do it. Do not concatenate! If you remove the concatenation and use String.Format(string str, object[] params) it would automatically remove the chances of such syntax error.
ebie147 21-May-15 7:16am    
and this is what happens when i add the the [ ] around all the column names
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error (missing operator) in query expression '',[Bank Name]='',[Account Type]='',[Account Number]='',[Branch Code]='',[Gross Salary]='',[Nett Salary]='',[Spouse Gross]='',[Spouse Nett]='',[Total Income]='',[Funeral Cover]='',[Medical Aid]='',[Life Insurance]='',[Groceries]='',[Rent/Bond]='',[School f'.
CHill60 21-May-15 7:27am    
This looks like your text boxes are empty
ebie147 21-May-15 7:37am    
yes some of them are empty but that shouldn't cause a syntax error? or am i wrong?

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