CODEWITHSUNDEEP
vb.netms-access
Pedro Andrade
Pedro Andrade

Reputation: 7

VB to Insert in Access

I'm trying to insert data into a MSaccess db using VB but having some trouble finishing the code

Imports System.Data.OleDb

Public Class Form1

Dim provider As String
Dim dataFile As String
Dim connString As String
Public myConnection As OleDbConnection = New OleDbConnection
Public dr As OleDbDataReader

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'BD Connection
    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    dataFile = "C:\Users\Peter\Documents\Database1.accdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
End Sub

Private Sub FindButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FindButton.Click
    'Query BD
    myConnection.Open()
    DescriptionText.Clear()
    CostText.Clear()
    PriceText.Clear()
    Dim str As String
    str = "SELECT * FROM Table1 WHERE (Code = '" & CodeText.Text & "')"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    dr = cmd.ExecuteReader
    While dr.Read()
        DescriptionText.Text = dr("Description").ToString
        CostText.Text = dr("Cost").ToString
        PriceText.Text = dr("Price").ToString
    End While
    myConnection.Close()
End Sub
Private Sub AddButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddButton.Click
    'Insert BD
    Dim str As String

    myConnection.Open()
    CodeText.Clear()
    DescriptionText.Clear()
    CostText.Clear()
    PriceText.Clear()

    str = "INSERT INTO Table1 (Code, Description, Cost, Price) Values (CodeText.Text, DescriptionText.Text, CostText.Text, PriceText.Text) VALUES ('CodeText.Text', 'DescriptionText.Text', 'CostText.Text', 'PriceText.Text')"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    cmd.ExecuteNonQuery()

    MsgBox("saved")

    myConnection.Close()
End Sub
End Class

My query sub work fine but when I try to insert data it gives me an error on the "cmd.ExecuteNonQuery()".

Can you help me find whats wrong?

Upvotes: 0

Views: 220

Answers (2)

Alex B.
Alex B.

Reputation: 2167

Your INSERT query syntax is not correct. You cannot simply add the Textbox.Text as strings into the values. You have to escape them.

But I would highly recommend using parametrized queries for both SELECT and INSERT statements:

Dim insert As String = "INSERT INTO Table1 (Code, Description, Cost, Price) Values (?, ?, ?, ?)"
Dim insertCmd As New OleDbCommand(insert, myConnection)
insertCmd.Parameters.Add(New OleDbParameter("Code", CodeText.Text))
insertCmd.Parameters.Add(New OleDbParameter("Description", DescriptionText.Text))
insertCmd.Parameters.Add(New OleDbParameter("Cost", Double.Parse(CostText.Text)))
insertCmd.Parameters.Add(New OleDbParameter("Price", Double.Parse(PriceText.Text)))
insertCmd.ExecuteNonQuery()

I assume that your Cost and Price database fields are numeric so I used Double.Parse to convert them. Codeseems to be a text field so no convert needed. If my suggested types are not correct you have to adjust this, there is Integer.Parse, Decimal.Parse etc.

Finally here the SELECT part optimized:

 Dim qry As String = "SELECT * FROM Table1 WHERE (Code = ?)"
 Dim selectCmd As OleDbCommand = New OleDbCommand(qry, myConnection)
 selectCmd.Parameters.Add(New OleDbParameter("Code", CodeText.Text))
 selectCmd.ExecuteNonQuery()

More about parametrized queries

Upvotes: 3

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Try this

str = "INSERT INTO Table1 (Code, Description, Cost, Price) Values ('" + CodeText.Text + "', '" + DescriptionText.Text + "', '" + CostText.Text + "', '" + PriceText.Text + "')"

Upvotes: 0

Related Questions