Am facing a problem while inserting data to mdb file

Question

0.00/5 (No votes)

See more:

I receive Syntax error in INSERT INTO statement and the reason is hidden for me , i debugged the whole thing since last Thursday and still no valid reason why i 'm receiving this error

Am facing a problem while inserting data to mdb file

Public Class SuppliersManagement
    Dim cnnOLEDB As New OleDbConnection
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim SystemUserName As String = Environment.UserName
    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\SupplierDB.mdb"


Private Sub Fill_Suppliers_Data()
    Try
        Dim sqlQRY As String
        sqlQRY = "Select * From Supp_Users"
        Dim da As OleDbDataAdapter
        Dim ds As DataSet = New DataSet
        da = New OleDbDataAdapter(sqlQRY, cnnOLEDB)
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
        da.Fill(ds, "Supp_Users")
        DataGridSupplierMGT.DataSource = ds
        DataGridSupplierMGT.DataMember = "Supp_Users"
        lbl_RowCount.Text = DataGridSupplierMGT.RowCount
        Dim row As Integer
        If DataGridSupplierMGT.RowCount > 0 Then
            DataGridSupplierMGT.Columns("Supp_UserName").ReadOnly = True
            DataGridSupplierMGT.Columns("EmailTo").Visible = False
            DataGridSupplierMGT.Columns("EmailCC").Visible = False
            DataGridSupplierMGT.Columns("Password").Visible = False
            DataGridSupplierMGT.Columns("Supp_Domain").Visible = False
            DataGridSupplierMGT.Columns("ID").Visible = False
            row = DataGridSupplierMGT.FirstDisplayedCell.RowIndex
            DataGridSupplierMGT.Rows(row).Selected = True
            If RowIndex_TXT.Text = vbNullString Then
                Exit Sub
            Else
                Me.DataGridSupplierMGT.ClearSelection()
                Me.DataGridSupplierMGT.Rows(RowIndex_TXT.Text).Selected = True
                Me.DataGridSupplierMGT.FirstDisplayedScrollingRowIndex = RowIndex_TXT.Text
            End If
        Else
            row = 0
        End If
    Catch ex As OleDbException
        MessageBox.Show(ex.Message)
    End Try
End Sub




Private Sub AddNew_BTN_Click(sender As Object, e As EventArgs) Handles AddNew_BTN.Click
        Try
            If RecordID_TXT.Text = "" Then
                If MessageBox.Show("Do you want to Add a New Record?", "Insert", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
                    Exit Sub
                Else
                    If SuppNameVariable.Text <> "" And Supp_domain_TXT.Text <> "" And Supp_Pass_TXT.Text <> "" And MailAdress_Holder_TXT_TO.Text <> "" And MailAdress_Holder_TXT_CC.Text <> "" Then
                        If cnnOLEDB.State = ConnectionState.Open Then
                            cnnOLEDB.Close()
                        End If
                        cnnOLEDB.Open()
                        cmdInsert.CommandText = "INSERT INTO Supp_Users (Supp_UserName, Supp_Domain, Password, EmailTo, EmailCC) VALUES ('" & SuppNameVariable.Text & "', '" & Supp_domain_TXT.Text & "', '" & Supp_Pass_TXT.Text & "', '" & MailAdress_Holder_TXT_TO.Text & "', '" & MailAdress_Holder_TXT_CC.Text & "');"
                        cmdInsert.CommandType = CommandType.Text
                        cmdInsert.Connection = cnnOLEDB
                        cmdInsert.ExecuteNonQuery()
                        MsgBox("Record added successfully.")
                        RecordID_TXT.Text = ""
                        SuppNameVariable.Text = ""
                        Supp_domain_TXT.Text = ""
                        Supp_Pass_TXT.Text = ""
                        MailAdress_Holder_TXT_TO.Text = ""
                        MailAdress_Holder_TXT_CC.Text = ""
                        lbl_RowCount.Text = DataGridSupplierMGT.RowCount
                    Else
                        MsgBox("Please make sure to add below data:" & vbNewLine & "1. Supplier Name" & vbNewLine & "2. Supplier Domain" & vbNewLine & "3. Supplier Password" & vbNewLine & "4. Email To" & vbNewLine & "5. Email CC")
                        lbl_RowCount.Text = DataGridSupplierMGT.RowCount
                    End If
                End If
            End If
            lbl_RowCount.Text = DataGridSupplierMGT.RowCount
            cmdInsert.Dispose()
            Fill_Suppliers_Data()
            If DataGridSupplierMGT.RowCount > 0 Then
                DataGridSupplierMGT.ClearSelection()
                Me.DataGridSupplierMGT.FirstDisplayedScrollingRowIndex = Me.DataGridSupplierMGT.RowCount - 1
                Me.DataGridSupplierMGT.Rows(Me.DataGridSupplierMGT.RowCount - 1).Selected = True
            End If
        Catch ex As OleDbException
            MessageBox.Show(ex.Message)
        End Try
End Sub

What I have tried:

Am facing a problem while inserting data to mdb file

Public Class SuppliersManagement
    Dim cnnOLEDB As New OleDbConnection
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim SystemUserName As String = Environment.UserName
    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\SupplierDB.mdb"


Private Sub Fill_Suppliers_Data()
    Try
        Dim sqlQRY As String
        sqlQRY = "Select * From Supp_Users"
        Dim da As OleDbDataAdapter
        Dim ds As DataSet = New DataSet
        da = New OleDbDataAdapter(sqlQRY, cnnOLEDB)
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
        da.Fill(ds, "Supp_Users")
        DataGridSupplierMGT.DataSource = ds
        DataGridSupplierMGT.DataMember = "Supp_Users"
        lbl_RowCount.Text = DataGridSupplierMGT.RowCount
        Dim row As Integer
        If DataGridSupplierMGT.RowCount > 0 Then
            DataGridSupplierMGT.Columns("Supp_UserName").ReadOnly = True
            DataGridSupplierMGT.Columns("EmailTo").Visible = False
            DataGridSupplierMGT.Columns("EmailCC").Visible = False
            DataGridSupplierMGT.Columns("Password").Visible = False
            DataGridSupplierMGT.Columns("Supp_Domain").Visible = False
            DataGridSupplierMGT.Columns("ID").Visible = False
            row = DataGridSupplierMGT.FirstDisplayedCell.RowIndex
            DataGridSupplierMGT.Rows(row).Selected = True
            If RowIndex_TXT.Text = vbNullString Then
                Exit Sub
            Else
                Me.DataGridSupplierMGT.ClearSelection()
                Me.DataGridSupplierMGT.Rows(RowIndex_TXT.Text).Selected = True
                Me.DataGridSupplierMGT.FirstDisplayedScrollingRowIndex = RowIndex_TXT.Text
            End If
        Else
            row = 0
        End If
    Catch ex As OleDbException
        MessageBox.Show(ex.Message)
    End Try
End Sub




Private Sub AddNew_BTN_Click(sender As Object, e As EventArgs) Handles AddNew_BTN.Click
        Try
            If RecordID_TXT.Text = "" Then
                If MessageBox.Show("Do you want to Add a New Record?", "Insert", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
                    Exit Sub
                Else
                    If SuppNameVariable.Text <> "" And Supp_domain_TXT.Text <> "" And Supp_Pass_TXT.Text <> "" And MailAdress_Holder_TXT_TO.Text <> "" And MailAdress_Holder_TXT_CC.Text <> "" Then
                        If cnnOLEDB.State = ConnectionState.Open Then
                            cnnOLEDB.Close()
                        End If
                        cnnOLEDB.Open()
                        cmdInsert.CommandText = "INSERT INTO Supp_Users (Supp_UserName, Supp_Domain, Password, EmailTo, EmailCC) VALUES ('" & SuppNameVariable.Text & "', '" & Supp_domain_TXT.Text & "', '" & Supp_Pass_TXT.Text & "', '" & MailAdress_Holder_TXT_TO.Text & "', '" & MailAdress_Holder_TXT_CC.Text & "');"
                        cmdInsert.CommandType = CommandType.Text
                        cmdInsert.Connection = cnnOLEDB
                        cmdInsert.ExecuteNonQuery()
                        MsgBox("Record added successfully.")
                        RecordID_TXT.Text = ""
                        SuppNameVariable.Text = ""
                        Supp_domain_TXT.Text = ""
                        Supp_Pass_TXT.Text = ""
                        MailAdress_Holder_TXT_TO.Text = ""
                        MailAdress_Holder_TXT_CC.Text = ""
                        lbl_RowCount.Text = DataGridSupplierMGT.RowCount
                    Else
                        MsgBox("Please make sure to add below data:" & vbNewLine & "1. Supplier Name" & vbNewLine & "2. Supplier Domain" & vbNewLine & "3. Supplier Password" & vbNewLine & "4. Email To" & vbNewLine & "5. Email CC")
                        lbl_RowCount.Text = DataGridSupplierMGT.RowCount
                    End If
                End If
            End If
            lbl_RowCount.Text = DataGridSupplierMGT.RowCount
            cmdInsert.Dispose()
            Fill_Suppliers_Data()
            If DataGridSupplierMGT.RowCount > 0 Then
                DataGridSupplierMGT.ClearSelection()
                Me.DataGridSupplierMGT.FirstDisplayedScrollingRowIndex = Me.DataGridSupplierMGT.RowCount - 1
                Me.DataGridSupplierMGT.Rows(Me.DataGridSupplierMGT.RowCount - 1).Selected = True
            End If
        Catch ex As OleDbException
            MessageBox.Show(ex.Message)
        End Try
End Sub

Posted 25-Dec-17 2:34am

BassamKassem

Updated 25-Dec-17 6:35am

Add a Solution

2 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)

OriginalGriff · Answer 1 · 2017-12-25T03:26:00

Solution 1

Don't do it like that! Never 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.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Chances are, when you fix that your other problem will disappear at the same time.

Posted 25-Dec-17 3:26am

OriginalGriff

Comments

BassamKassem 25-Dec-17 10:48am

Thanks for your continuous support , I tried this but still not working donno why

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
Using connection As OleDbConnection = CreateConnection()
Using cmdInsert As OleDbCommand = connection.CreateCommand()
If RecordID_TXT.Text = "" Then
If MessageBox.Show("Do you want to Add a New Record?", "Insert", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
Exit Sub
Else
'cmdInsert.CommandText = "INSERT INTO Supp_Users (Supp_UserName, Supp_Domain, Password, EmailTo, EmailCC) VALUES (@Supp_UserName = ?, @Supp_Domain = ?, @Password = ?, @EmailTo = ?, @EmailCC = ?)"
cmdInsert.CommandText = "Insert Into Supp_Users (Supp_UserName, Supp_Domain, Password, EmailTo, EmailCC) Values (@Supp_UserName, @Supp_Domain, @Password, @EmailTo, @EmailCC)"
cmdInsert.Parameters.AddWithValue("Supp_UserName", SuppNameVariable.Text)
cmdInsert.Parameters.AddWithValue("Supp_Domain", Supp_domain_TXT.Text)
cmdInsert.Parameters.AddWithValue("Password", Supp_Pass_TXT.Text)
cmdInsert.Parameters.AddWithValue("EmailTo", MailAdress_Holder_TXT_TO.Text)
cmdInsert.Parameters.AddWithValue("EmailCC", MailAdress_Holder_TXT_CC.Text)
cmdInsert.ExecuteNonQuery()
MsgBox("Record added successfully.")
RecordID_TXT.Text = ""
SuppNameVariable.Text = ""
Supp_domain_TXT.Text = ""
Supp_Pass_TXT.Text = ""
MailAdress_Holder_TXT_TO.Text = ""
MailAdress_Holder_TXT_CC.Text = ""
lbl_RowCount.Text = DataGridSupplierMGT.RowCount
End If
Else
MsgBox("Please make sure to add below data:" & vbNewLine & "1. Supplier Name" & vbNewLine & "2. Supplier Domain" & vbNewLine & "3. Supplier Password" & vbNewLine & "4. Email To" & vbNewLine & "5. Email CC")
lbl_RowCount.Text = DataGridSupplierMGT.RowCount
End If
End Using
End Using
Catch ex As OleDbException
' TODO: Log the full exception details somewhere:
System.Diagnostics.Trace.TraceError(ex.ToString())
MessageBox.Show(ex.Message)
End Try
End Sub

BassamKassem · Answer 2 · 2017-12-25T06:35:00

I made it like this and it works just fine

1- I amended the "Password" filed name in MS Access to be "Supp_Password" then I removed its masked item "Password" and made the TextBox named "

Supp_Pass_TXT

to not display the actual characters and to display it as *** from its properties

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Using connection As OleDbConnection = CreateConnection()
            Using cmdInsert As OleDbCommand = connection.CreateCommand()
                If RecordID_TXT.Text = "" Then
                    If MessageBox.Show("Do you want to Add a New Record?", "Insert", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
                        Exit Sub
                    Else
                        cmdInsert.CommandText = "INSERT INTO Supp_Users (Supp_UserName, Supp_Domain, Supp_Password, EmailTo, EmailCC) VALUES (@Supp_UserName, @Supp_Domain, @Supp_Password, @EmailTo, @EmailCC)"
                        cmdInsert.Parameters.AddWithValue("@Supp_UserName", SuppNameVariable.Text)
                        cmdInsert.Parameters.AddWithValue("@Supp_Domain", Supp_domain_TXT.Text)
                        cmdInsert.Parameters.AddWithValue("@Supp_Password", Supp_Pass_TXT.Text)
                        cmdInsert.Parameters.AddWithValue("@EmailTo", MailAdress_Holder_TXT_TO.Text)
                        cmdInsert.Parameters.AddWithValue("@EmailCC", MailAdress_Holder_TXT_CC.Text)
                        cmdInsert.ExecuteNonQuery()
                        MsgBox("Record added successfully.")
                        RecordID_TXT.Text = ""
                        SuppNameVariable.Text = ""
                        Supp_domain_TXT.Text = ""
                        Supp_Pass_TXT.Text = ""
                        MailAdress_Holder_TXT_TO.Text = ""
                        MailAdress_Holder_TXT_CC.Text = ""
                        lbl_RowCount.Text = DataGridSupplierMGT.RowCount
                        Fill_Suppliers_Data()
                    End If
                Else
                    MsgBox("Please make sure to add below data:" & vbNewLine & "1. Supplier Name" & vbNewLine & "2. Supplier Domain" & vbNewLine & "3. Supplier Password" & vbNewLine & "4. Email To" & vbNewLine & "5. Email CC")
                    lbl_RowCount.Text = DataGridSupplierMGT.RowCount
                    Fill_Suppliers_Data()
                End If
            End Using
        End Using
    Catch ex As OleDbException
        ' TODO: Log the full exception details somewhere:
        System.Diagnostics.Trace.TraceError(ex.ToString())
        MessageBox.Show(ex.Message)
    End Try