Click here to Skip to main content
15,849,494 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Here is my code;

Option Explicit
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
-----------------------------------------------------------------------------
Private Sub cmdadd_Click()
Dim res As String
Dim SQLSTR As String
Dim ConString As String
Set rs = New ADODB.Recordset
ConString= "provider=Microsoft.jet.oledb.4.0;Data Source="D:\My Project VB6\Tubewell.accdb"
Set con = New ADODB.Connection
con.ConnectionString = ConString
res = MsgBox("Do you want to add this record?", vbQuestion + vbYesNo, "Add Employee")
If res = vbYes Then
con.Open
SQLSTR = "Insert into Employee_Details(EmpNo,Employee_Name,Status)values('"&txtempno&"','"&empname&"','"&Combo1&"')"
con.Execute SQLSTR
con.Close
Set con = Nothing
End If
End Sub

When I am running the project I am getting;
"Expected: End of Statement" Error message.

Please help me.

What I have tried:

Here is my code;

Option Explicit
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
-----------------------------------------------------------------------------
Private Sub cmdadd_Click()
Dim res As String
Dim SQLSTR As String
Dim ConString As String
Set rs = New ADODB.Recordset
ConString= "provider=Microsoft.jet.oledb.4.0;Data Source="D:\My Project VB6\Tubewell.accdb"
Set con = New ADODB.Connection
con.ConnectionString = ConString
res = MsgBox("Do you want to add this record?", vbQuestion + vbYesNo, "Add Employee")
If res = vbYes Then
con.Open
SQLSTR = "Insert into Employee_Details(EmpNo,Employee_Name,Status)values('"&txtempno&"','"&empname&"','"&Combo1&"')"
con.Execute SQLSTR
con.Close
Set con = Nothing
End If
End Sub

When I am running the project I am getting;
"Expected: End of Statement" Error message.

Please help me.
Posted
Updated 17-Aug-17 14:36pm
Comments
CHill60 16-Aug-17 8:22am    
Shouldn't you be using the selected value from Combo1 rather than the control itself?

Try terminating the statement with a semicolon and enclose the VALUE keyword by spaces:
VB
SQLSTR = "INSERT INTO Employee_Details (EmpNo,Employee_Name,Status) VALUES ('" & txtempno & "','" & empname & "','" & Combo1 & "');"
I have also inserted additional spaces and used all uppercase for keywords. This is not necessary but improves readability.
 
Share this answer
 
Comments
CHill60 16-Aug-17 7:59am    
5'd.
If the OP says they are still getting the error then try removing the single quotes around txtempno (although from memory the error message should have been different)
Jochen Arndt 16-Aug-17 8:14am    
Thank you.

To be honest: I did not know what exactly causes the error but my example should work. In most cases the semicolon is added by the database driver if not present.

When txtempno is numeric there should be conversion / type mismatch errors but not missing end of statement.
Member 13363527 16-Aug-17 14:10pm    
Hi,
SQL statement is working and thanks for it. But now I get following run time error.
Run time error - '446';
Object doesn't support named arguments.
con.ConString = "provider=Microsoft.Jet.oledb.4.0;Data Source=D:\My Project VB6\Tubewell.accdb" code is marked as yellow.

My full coding as follows;
Option Explicit
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Private Sub cmdadd_Click()
Dim res As String
Dim SQLSTR As String
Dim ConString As String
Set rs = New ADODB.Recordset
con.ConString = "provider=Microsoft.Jet.oledb.4.0;Data Source=D:\My Project VB6\Tubewell.accdb"
Set con = New ADODB.Connection
'con.ConnectionString = ConString
res = MsgBox("Do you want to add this record?", vbQuestion + vbYesNo, "Add Employee")
If res = vbYes Then
con.Open
SQLSTR = "INSERT INTO Employee_Details(EmpNo,Employee_Name,Status) VALUES ('" & txtempno & "','" & txtempname & "','" & Combo1 & "');"
con.Execute SQLSTR
con.Close
Set con = Nothing
End If
End Sub
Private Sub cmdcancel_Click()
Unload Me
End Sub

Please help me.
Jochen Arndt 16-Aug-17 16:03pm    
The ADODB.Connection object does not have a ConString member.
It must be con.ConnectionString as used in your initial question (and commented in the above code snippet).
Member 13363527 17-Aug-17 7:40am    
No. It doesn't work. same error is appearing.
Start by fixing the SQL Injection[^] vulnerability in your code.

Parameterizing queries isn't quite as simple in VBA as it is in .NET, but it's not too bad:
Append and CreateParameter Methods Example (VB) | Microsoft Docs[^]

Something like this should work:
VB
Private Sub cmdadd_Click()
    If MsgBox("Do you want to add this record?", vbQuestion + vbYesNo, "Add Employee") <> vbYes Then
        Exit Sub
    End If
    
    Dim ConString As String
    ConString= "provider=Microsoft.jet.oledb.4.0;Data Source=""D:\My Project VB6\Tubewell.accdb"""
    
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.ConnectionString = ConString
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandText = "INSERT INTO Employee_Details (EmpNo, Employee_Name, Status) VALUES (?, ?, ?)"
    
    Dim p As ADODB.Parameter
    
    Set p = cmd.CreateParameter(, adInteger, adParamInput, , txtempno.Text)
    cmd.Parameters.Append p
    
    Set p = cmd.CreateParameter(, adVarChar, adParamInput, 50, empname.Text)
    cmd.Parameters.Append p
    
    Set p = cmd.CreateParameter(, adVarChar, adParamInput, 10, Combo1.Value)
    cmd.Parameters.Append p

    con.Open
    cmd.Execute
    con.Close
End Sub

NB: You'll need to change the parameter sizes for the name and status parameters to match the database.


Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
 
Share this answer
 
Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
-----
Get the habit to indent your code, it helps reading:
VB
Option Explicit
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
-----------------------------------------------------------------------------
Private Sub cmdadd_Click()
    Dim res As String
    Dim SQLSTR As String
    Dim ConString As String
    Set rs = New ADODB.Recordset
    ConString= "provider=Microsoft.jet.oledb.4.0;Data Source="D:\My Project VB6\Tubewell.accdb"
    Set con = New ADODB.Connection
    con.ConnectionString = ConString
    res = MsgBox("Do you want to add this record?", vbQuestion + vbYesNo, "Add Employee")
    If res = vbYes Then
        con.Open
        SQLSTR = "Insert into Employee_Details(EmpNo,Employee_Name,Status)values('"&txtempno&"','"&empname&"','"&Combo1&"')"
        con.Execute SQLSTR
        con.Close
        Set con = Nothing
    End If
End Sub

Office Automation Ltd. - Smart Indenter[^]
Installing Smart Indenter in Office 2013-2016[^]
 
Share this answer
 
v2

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