Insert textboxes and combo box values in VB6 to MS access 2010

Question

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 16-Aug-17 0:48am

Member 13363527

Updated 17-Aug-17 13:36pm

Add a Solution

Comments

CHill60 16-Aug-17 8:22am

Shouldn't you be using the selected value from Combo1 rather than the control itself?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jochen Arndt · Answer 1 · 2017-08-16T01:48:00

Solution 1

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.

Posted 16-Aug-17 1:48am

Jochen Arndt

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.

Member 13363527 17-Aug-17 7:41am

It doesn't work.
Error;
Run time error - '446';
Object doesn't support named arguments.

Jochen Arndt 17-Aug-17 8:07am

Did you read the comment from CHill60?

I guess that is the other problem which has not been detected before while the SQL command was inavlid.

Patrice T 17-Aug-17 19:38pm

Use Improve question to update your question.
So that everyone can pay attention to this information.

Member 13363527 17-Aug-17 13:22pm

Now I am getting following error message.
Run time error - 3709
Requested operation requires as OLEDB Session object, which is not supported by the current provider.

Please help me on this.

Richard Deeming · Answer 2 · 2017-08-17T04:57:00

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[^]

Patrice T · Answer 3 · 2017-08-17T13:36:00

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[^]

Insert textboxes and combo box values in VB6 to MS access 2010

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0