Need Help to insert data to MS Access table

[VIPER5646]

I been searching for days for a sulotion but can't seem to find one.
The coed that I have does not give me an error but it does not save anything on the table either '
Can someone help me find what I'm missing?
this is my coed

Dim mycon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DEsign.accdb"
        Dim customer As String = "select * from Customer_tbl"
        Dim Notes As String = "select * from Notes_tbl"
        Dim con As New OleDb.OleDbConnection(mycon)
        Dim comCustomer As New OleDb.OleDbCommand(customer, con)
        Dim comdNotes As New OleDb.OleDbCommand(Notes, con)
        Dim Custsql As String = "first name,Last Name"
        Dim Sql As String = "insert into Customer_tbl (Custsql )values '" & txtFirstName.Text & "','" & txtLastName.Text & "')"
        comCustomer = New OleDb.OleDbCommand(Sql, con)
        
        con.Open()
        comCustomer.ExecuteNonQuery()
       comCustomer.Dispose()
        con.Close()

Thank you
Viper

[debasisdas]

i don't think that is VB 6.0 code.

if vb 6.0 then try this

Dim Sql As String = "insert into Customer_tbl (Custsql )values '" & txtFirstName.Text & "','" & txtLastName.Text & "')"
con.begintrans          'con-----ADODB connection object.
con.execute sql
con.committrans

[VIPER5646]

i don't think that is VB 6.0 code.

if vb 6.0 then try this

Dim Sql As String = "insert into Customer_tbl (Custsql )values '" & txtFirstName.Text & "','" & txtLastName.Text & "')"
con.begintrans          'con-----ADODB connection object.
con.execute sql
con.committrans

thanks for your reply.
when I try the coed I get an error that execute and committrans are not members of the OLEDB.
I'm using Acess 2007 as a Database. Hope this helps.
Thanks

[debasisdas]

Are you using ADO to connect to the database ?

[VIPER5646]

Are you using ADO to connect to the database ?

No i'm using OLEDB

[vb5prgrmr]

I been searching for days for a sulotion but can't seem to find one.
The coed that I have does not give me an error but it does not save anything on the table either '
Can someone help me find what I'm missing?
this is my coed

Dim mycon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DEsign.accdb"
        Dim customer As String = "select * from Customer_tbl"
        Dim Notes As String = "select * from Notes_tbl"
        Dim con As New OleDb.OleDbConnection(mycon)
        Dim comCustomer As New OleDb.OleDbCommand(customer, con)
        Dim comdNotes As New OleDb.OleDbCommand(Notes, con)
        Dim Custsql As String = "first name,Last Name"
        Dim Sql As String = "insert into Customer_tbl [b](Custsql )[/b]values '" & txtFirstName.Text & "','" & txtLastName.Text & "')"
        comCustomer = New OleDb.OleDbCommand(Sql, con)
        
        con.Open()
        comCustomer.ExecuteNonQuery()
       comCustomer.Dispose()
        con.Close()

Thank you
Viper

Should be...

Insert Into Table Values (Field_1_Name, Field_2_Name) Values (Field_1_Value, Field_2_Value)

Good Luck

[debasisdas]

what is that CON in your code ?

What type of object is that ?

[VIPER5646]

HI v5prgrmr

I tried your QRY and now I get an error.
The error is ( Missing Semicolon at the end oof the SQL statement.
I have tried to place a Semicolon at the end and in diferent places in the SQL but I continue to get the same error.
This is my updated SQL statement.

Dim mysql As String = "insert into Customer_tbl values('First Name','Last Name') values ('John','Unknown');"

[VIPER5646]

Hi Debasisdas
TEX]If I understood your question my Con is posted.
If you see my coed that is my CON.[/TEX]

[vb5prgrmr]

HI v5prgrmr Dim mysql As String = "insert into Customer_tbl values('First Name','Last Name') values ('John','Unknown');"

Ahh... My bust. Dim mysql As String = "insert into Customer_tbl ('First Name','Last Name') values ('John','Unknown');" Remove that first values

[VIPER5646]

HI v5prgrmr Dim mysql As String = "insert into Customer_tbl values('First Name','Last Name') values ('John','Unknown');"

[TEX]Now I seam to get a diferent error.
Which tells me that it canot find the "First Name ".
I went to the table and copy and paste it in the sql.
I still get the error.
Can you help me ?[/TEX]

[VIPER5646]

Now I seam to get a diferent error.
Which tells me that it canot find the "First Name ".
I went to the table and copy and paste it in the sql.
I still get the error.
Can you help me?

[vb5prgrmr]

Insert into tablename (field1, field2) values (value1, value2)

Okay, the above is the basic psudo code design of your insert statement. Now where it says field1 that represents your First Name field and you are getting the error that says it cannot find the first name field. So, one of the easiest ways to figure this out is to open the table in design view, find the first name field, highlight it, copy the field name, and then paste it into your code.

The reason for these steps is I am betting that the first name field might be named differently like first_name or as I sometimes like to do FName. So make sure that you have the correct spelling for your table name and field names. Then check to make sure they are of the correct data types.

Good Luck

[VIPER5646]

I had tried that .
This might help you help me solve this problem.
My Customer_tbl is built of ID,Date,PO Number,First Name,Last Name, Address,Civic,City,Home Phone,Mobile Phone,Fax Number.
THE ID is auto Number and is linked to another table,Date --Date/Time, PO Number--Number, the rest is all text.
I'm just wondering if I have to enter every field in my app.
Thanks again

[vb5prgrmr]

inserting into a table should not be a problem as long as you add values for each field that has the required arguement set to yes. However, you may be able to get around the need for this by specifying a default value.

Good Luck

[VIPER5646]

I do not understand what you mean by " required arguments" being set to yes.
Or can u clarify what I should set to YES.
Thanks

[vb5prgrmr]

I am guessing you are using access as your database, Goto the design of the table and check each field to see if down at the bottom the required is set to yes.

[jonc]

I personally use a .mdb database (despite using Access 2007, I use an Access 2000 format database) and I use ADO to connect to the database without any problems.

Unless there is any particular reason for using OLEDB (as in it gives you extra functionality that ADO won't), I'd recommend changing to ADO as it seems to be the one most people know (as in, its probably the easiest too :))

Dim oConnection as ADODB.Connection
Dim oRecordset as ADODB.Recordset
Dim sSQL as String

On Error GoTo ErrorHandler

sSQL = "INSERT INTO TableName (Field1,Field2) VALUES(Value1,Value2);"
oConnection.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=Database.mdb"

If oConnection.State <> adStateOpen Then
    Set oConnection = Nothing
    Set oRecordset = Nothing
End If

' If there were problems connecting to the database (hence the state was not adStateOpen as it got to the IF statement above, the next line will kick the method to the Error Handler.
oRecordset.Open sSQL, oConnection, adOpenDynamic, adLockOptimistic, adCmdText

Set oRecordset = Nothing
Set oConnection = Nothing
Exit Sub

ErrorHandler:
    Err.Raise Err.Number    'Returns the error to the calling method - this can be changed to a MsgBox if preferred.

That usually works no problems for me - I tend to use a class module though and push all my database requests through that to avoid repeating code unnecessarily.

[jonc]

Just noticed Comatose's "Read Me" thread at the top of the VB4/5/6 forum - http://www.daniweb.com/forums/thread41057.html
That'll come in really useful for you :)