datagridview to accdb

[Sammys.Man]

Hi guys,

wondered if anyone could help, or point me in the right direction please,

i want to insert data from the datagridview to the access database, that datagridview is not bound to anything, i insert data from textboxes to the DGV, and want them to insert into the DB, the form i am working with is a order form, and the DGV is to be the sales

 Private Sub btn_Add_Click(sender As System.Object, e As System.EventArgs) Handles btn_Add.Click
        DataGridView1.Rows.Add(cbox_Product.Text, tbox_Sale_Price.Text, tbox_Qty.Text, tbox_Discount.Text, tbox_SubTotal.Text, tbox_SalesID.Text)
        Dim con As New OleDb.OleDbConnection                     '"con" variable holds the Connection Object
        Dim dbProvider As String                                 'creates provider variable
        Dim dbSource As String

        dbProvider = "Provider = Microsoft.ACE.OLEDB.12.0;"      'specifies the provider technology
        dbSource = "Data Source = E:\VB.NET\Computer First Ade VB.net\Computer First Ade\Computer First Ade\CFA_DB.mdb" 'specifies the path to the Database

        con.ConnectionString = dbProvider & dbSource             'creates the connection string'creates source variable

        'opens connection
        con.Open()
        'creates new variable and specifies which technology to use and shows the file path to the database 
        Dim Connection As New OleDb.OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0" & _
        "Data Source = E:\VB.NET\Computer First Ade VB.net\Computer First Ade\Computer First Ade\CFA_DB.mdb")
        'If con.State = ConnectionState.Open Then
        '    con.Open()
        'End If
        Dim InsertCommand As New OleDb.OleDbCommand("INSERT INTO tbl_Sales( sal_Product_Code, sal_Price, sal_Quantity, sal_Discount, sal_Subtotal)" & _
                        " VALUES( @sal_Product, @sal_Price, @sal_Quantity, @sal_Discount, @sal_Subtotal)", con)

        'Insert command, adds what ever is in the textbox
        InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@sal_Product", cbox_Product.Text))        'to the specified column(item) from the SQL query
        InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@sal_Price", tbox_Sale_Price.Text))
        InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@sal_Quantity", tbox_Qty.Text))
        InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@sal_Discount", tbox_Discount.Text))
        InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@sal_Subtotal", tbox_SubTotal.Text))
        InsertCommand.ExecuteNonQuery()                                                                      'executes this insert command
        con.Close()                                                                                          'closes connection
        MessageBox.Show("Inserted")                                                                'displays the messagebox

    End Sub

any ideas?

[G_Waddell]

Hi,

I see you adding to the datagridview and I see you inserting via your insert command - are you getting an issue or something?

[Sammys.Man]

hiya, yea the error is
Data type mismatch in criteria expression.

what i want to achieve is (like in access) a form and a subform, where they are bound, so when i create a new order (main form) i can add things to my sales(subfor or DGV) then save it

[G_Waddell]

So the issue is in your insert command, do you know what types of parameters the database is expecting? For example, I'll assume the sal_Quantity field in your Database is numeric maybe a decimal figure? so I would ensure I'm passing in a valid decimal value and not a text values:

dim Sal_Quantity as Decimal

'...
'Set a default valid value
Sal_Quantity = 0
IF isnumeric(tbox_Quantity.text.trim) then
    'we know now we can convert to numeric value
    Sal_Quantity = Cdec(tbox_Quantity.text.trim)
end if
'....
'Insert our valid value either 0 or a decimal
InsertCommand.Parameters.Add(New OleDb.OleDbParameter("@sal_Quantity",Sal_Quantity))