Syntax Error in INSERT INTO statement and missing operator in query for UPDATE

[Richard Alvarez]

Dim Request As String, rsData As Recordset
 
    If Me.cmdUpdate.Caption = "&Save" Then
 
        Request = "INSERT INTO tblMasterData VALUES '" & Replace(Me.txtID.Text, " '", "''") & "'," & _
            "'" & Replace(Me.txtFName.Text, "'", "''") & "'," & _
            "'" & Replace(Me.cboPosition.Text, "'", "''") & "'," & _
            "'" & Replace(Me.txtStatus.Text, "'", "''") & "'," & _
            "'" & Replace(Me.txtRate.Text, "'", "''") & "'," & _
            "'" & Replace(Me.dtpStart.value, "'", "''") & "'," & _
            "'" & Replace(Me.dtpEnd.value, "'", "''") & "'," & _
            "'" & Replace(Me.txtAge.Text, "'", "''") & "'," & _
            "'" & Replace(Me.txtAddress.Text, "'", "''") & "'"
 
    ElseIf Me.cmdUpdate.Caption = "&Update" Then
 
        Request = "UPDATE tblMasterData SET EmpName ='" & Replace(Me.txtFName.Text, "'", "''") & "'," & _
            "EmpPosition = '" & Replace(Me.cboPosition.Text, "'", "''") & "'," & _
            "EmpStatus = '" & Replace(Me.txtStatus.Text, "'", "''") & "'," & _
            "EmpRate = '" & Replace(Me.txtRate.Text, "'", "''") & "'," & _
            "DateHired = '" & Replace(Me.dtpStart.value, "'", "''") & "'," & _
            "Endo = '" & Replace(Me.dtpEnd.value, "'", "''") & "'," & _
            "EmpAge = '" & Replace(Me.txtAge.Text, "'", "''") & "'," & _
            "EmpAddress = '" & Replace(Me.txtAddress.Text, "'", "''") & "'"
 
        Request = Request & " WHERE EmpID = " & Me.txtID.Text
 
        cmdAdd.Caption = "&Add"
    End If
 
    DBConn.Execute Request

[VijaySofist]

Hi,

I don't think there is any Syntax Error in the Query, But you may try to Add Open & Close Parenthesis in the Insert Query and Use the Format function for Date in Both the Queries

So the resulting query will look like

Dim Request As String, rsData As Recordset
 
    If Me.cmdUpdate.Caption = "&Save" Then
 
        Request = "INSERT INTO tblMasterData VALUES ('" & Replace(Me.txtID.Text, " '", "''") & "'," & _
            "'" & Replace(Me.txtFName.Text, "'", "''") & "'," & _
            "'" & Replace(Me.cboPosition.Text, "'", "''") & "'," & _
            "'" & Replace(Me.txtStatus.Text, "'", "''") & "'," & _
            "'" & Replace(Me.txtRate.Text, "'", "''") & "'," & _
            "'" & Format(Me.dtpStart.value, "MM/dd/yyyy") & "'," & _
            "'" & Format(Me.dtpEnd.value, "MM/dd/yyyy") & "'," & _
            "'" & Replace(Me.txtAge.Text, "'", "''") & "'," & _
            "'" & Replace(Me.txtAddress.Text, "'", "''") & "')"
 
    ElseIf Me.cmdUpdate.Caption = "&Update" Then
        Request=""
        Request = "UPDATE tblMasterData SET EmpName ='" & Replace(Me.txtFName.Text, "'", "''") & "'," & _
            "EmpPosition = '" & Replace(Me.cboPosition.Text, "'", "''") & "'," & _
            "EmpStatus = '" & Replace(Me.txtStatus.Text, "'", "''") & "'," & _
            "EmpRate = '" & Replace(Me.txtRate.Text, "'", "''") & "'," & _
            "DateHired = '" & Format(Me.dtpStart.value, "MM/dd/yyyy") & "'," & _
            "Endo = '" & Format(Me.dtpEnd.value, "MM/dd/yyyy") & "'," & _
            "EmpAge = '" & Replace(Me.txtAge.Text, "'", "''") & "'," & _
            "EmpAddress = '" & Replace(Me.txtAddress.Text, "'", "''") & "'"
 
        Request = Request & " WHERE EmpID = " & Me.txtID.Text
 
        cmdAdd.Caption = "&Add"
    End If
 
    DBConn.Execute Request
 
 

And Also use Val Function for Numeric values without using single quotes whereever necessary in the Query

Regards
Vijay.R

[Richard Alvarez]

IT DID THE JOB! Sir thank you so much for that. one more thing, could you please explain to me what's the difference between the first one that i use with the date and the one you give using that format function? idid some experimenting and you're right, it does work with the mm/dd/yyy format. Thankkk you so much again for your reply.