visual basic syntax error

about update statement

Page 1 of 1

3 Replies - 2564 Views - Last Post: 28 July 2010 - 10:16 PM Rate Topic: -----

#1 jaycee27  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-July 10

visual basic syntax error

Posted 21 July 2010 - 11:32 PM

my module come with this code

Option Explicit
Global sql As String

Function ADOConnect(AdoRs As ADODB.Recordset, sql As String) As Boolean
Dim DSN As String
    DSN = "PROVIDER=Microsoft.Jet.OLEDB.3.51; Data Source=" & App.Path & "\inventory.mdb"
    AdoRs.CursorLocation = adUseClient
    AdoRs.Open sql, DSN, adOpenDynamic, adLockOptimistic
    ADOConnect = True
End Function

Function ADOExecute(sql As String) As Boolean
Dim AdoConn As New ADODB.Connection
Dim DSN As String
    DSN = "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" & App.Path & "\inventory.mdb"
    AdoConn.Open (DSN)
    AdoConn.Execute sql
    AdoConn.Close
    ADOExecute = True
End Function

and i dont know where the problem is...
syntax error in UPDATE statement 

in my form.command button save. the code is

Private Sub cmdsave_Click()

If Me.fraitem.Caption = "Add" Then
            sql = " INSERT INTO items_module (item_code, item_name, critic_level, quantity, item_cp, item_srp, del_yn, inv_date) " _
                & " VALUES('" & Me.txtitem_code & "', " _
                & " '" & Trim(Me.txtitem_name) & "', " _
                & " '" & Val(txtcritical) & "', " _
                & " '" & Val(txtquantity) & "', " _
                & " '" & Val(txtcp) & "', " _
                & " '" & Val(txtsrp) & "', " _
                & " '" & "N" & "', " _
                & " '" & "Date" & "') "
            ADOExecute (sql)
        Else
      
            sql = " UPDATE items_module SET " _
                & " item_code = '" & Me.txtitem_code & "', " _
                & " item_name = '" & Trim(txtitem_name) & "', " _
                & " critic_level = '" & Val(txtcritical) & "', " _
                & " quantity = '" & Val(txtquantity) & "', " _
                & " item_cp = '" & Val(txtcp) & "', " _
                & " item_srp = '" & Val(txtsrp) & "', " _
                & " del_yn = '" & "N" & "', " _
                & " inv_date = '" & "Date" & "', " _
                & " WHERE item_code = '" & Me.txtitem_code & "', "
            ADOExecute (sql)
        End If
        Grid_Refresh
        Me.dgriditem.Height = Me.dgriditem.Height + Me.fraitem.Height
        Me.fraitem.Visible = False
        toolbar True
        cmdcancel.Enabled = False: cmdsave.Enabled = False
        Initialize
        
End Sub


can somebody help me to figure it out...

This post has been edited by JackOfAllTrades: 22 July 2010 - 07:08 AM
Reason for edit:: Added code tags.


Is This A Good Question/Topic? 0
  • +

Replies To: visual basic syntax error

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: visual basic syntax error

Posted 22 July 2010 - 01:18 AM

& " del_yn = '" & "N" & "', " _
& " inv_date = '" & "Date" & "', " _
& " WHERE item_code = '" & Me.txtitem_code & "', "

the problem is you have comas at the end of your update statement
check out the update statement
http://www.w3schools.../sql_update.asp
Was This Post Helpful? 1
  • +
  • -

#3 jaycee27  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-July 10

Re: visual basic syntax error

Posted 22 July 2010 - 05:53 AM

thanks
Was This Post Helpful? 0
  • +
  • -

#4 jaycee27  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 21-July 10

Re: visual basic syntax error

Posted 28 July 2010 - 10:16 PM

View PostNoBrain, on 22 July 2010 - 12:18 AM, said:

& " del_yn = '" & "N" & "', " _
& " inv_date = '" & "Date" & "', " _
& " WHERE item_code = '" & Me.txtitem_code & "', "

the problem is you have comas at the end of your update statement
check out the update statement
http://www.w3schools.../sql_update.asp


i think there is no wrong in my comas at the end of my update statement,,, the only problem is the caption of my frame... in my form the caption there is "ADD"
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1