Saving item textbox to access

[ImZick]

i need some help how can you save the item in my textbox to the access database?
For example i have

Firstname.Text
Middlename.text
Lastname.text

if i click the add button it will save it to my access database

and also if i want to edit it and update it, how will i do that?

thanks in advance

[Reverend Jim]

Here's how to insert a new record.

Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=Yes;")
Dim cmd As New OleDbCommand("", con)

cmd.CommandText = "INSERT INTO myTable (Firstname,Middlename,Lastname) VALUES(?,?,?)"
cmd.Parameters.AddWithValue("@FirstName ", Firstname.Text)
cmd.Parameters.AddWithValue("@MiddleName", Middlenameame.Text)
cmd.Parameters.AddWithValue("@LastName  ", Lastname.Text)

con.Open()
cmd.ExecuteNonQuery()
con.Close() 

An update query requires that you be able to identify a particular record first. For example, if you wanted to change the first and last names for a record with a specific last name you could code

Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=Yes;")
Dim cmd As New OleDbCommand("", con)

cmd.CommandText = "UPDATE myTable         " _
                & "   SET Firstname  = ?, " _
                & "       Middlename = ?  " _
                & " WHERE Lastname   = ?"
cmd.Parameters.AddWithValue("@FirstName ", Firstname.Text)
cmd.Parameters.AddWithValue("@MiddleName", Middlename.Text)
cmd.Parameters.AddWithValue("@LastName  ", Lastname.Text)

con.Open()
cmd.ExecuteNonQuery()
con.Close()

Please note that when adding the parameter values, you must add the values in the same order in which they appear in the query. The symbolic names used (eg @Firstname) are not important. They are there for convenience. If you were to code

cmd.Parameters.AddWithValue("@LastName  ", Lastname.Text)
cmd.Parameters.AddWithValue("@MiddleName", Middlename.Text)
cmd.Parameters.AddWithValue("@FirstName ", Firstname.Text)

you would end up with the last and first names reversed. You could just as well code

cmd.Parameters.AddWithValue("@splunge", Firstname.Text)
cmd.Parameters.AddWithValue("@berferd", Middlename.Text)
cmd.Parameters.AddWithValue("@kumquat", Lastname.Text)

which is confusing but still gives you what you want. Not allowing named and positional parameters was, in my opinion, a serious brainfart on some designer's part. At least they got it right for Sql specific commands.

[ImZick]

is it okay if my provider is a access?

[ImZick]

can i know what is the meaning of this?
(Firstname,Middlename,Lastname) VALUES(?,?,?)

[Reverend Jim]

is it okay if my provider is a access?

You'd need to modify the connection string but that should be it. As for the statement

"INSERT INTO myTable (Firstname,Middlename,Lastname) VALUES(?,?,?)"
                     |------------- 1 -------------|       |- 2 -|

Section 1 lists all of the fields in the record you are inserting. Section 2 lists the values you are inserting, or at least it would if you weren't using parameterized entry (which you should). A simple insert would be

"INSERT INTO myTable (Firstname,Middlename,Lastname) VALUES('Homer','J.','Simpson')"

but you don't want to hard code in actual values so we replace each value with ?, then add the actual values later with cmd.Parameters.AddWithValue

[ImZick]

ohh i see i get it.. and its perfectly working but i can't understand the update code

        cmd.CommandText = "UPDATE [" & EBU2DB_LOB & "] " _
        & " SET Months = [" & .Combo_LOB_Month.Text & "], " _
        & " Brand = [" & .Combo_LOB_Brand.Text & "]" _
        & " LOB = [" & .Combo_LOB_LOB.Text & "] " _
        & " Revenue = [" & .Text_LOB_Revenue.Text & "]" _
        & " WHERE GP = [" & .Text_LOB_GP.Text & "]"

        cmd.Parameters.AddWithValue("@Months ", .Combo_LOB_Month_Update.Text)
        cmd.Parameters.AddWithValue("@Brand", .Combo_LOB_Brand_Update.Text)
        cmd.Parameters.AddWithValue("@LOB ", .Combo_LOB_LOB_Update.Text)
        cmd.Parameters.AddWithValue("@Revenue ", .Text_LOB_Revenue_Update.Text)
        cmd.Parameters.AddWithValue("@GP ", .Text_LOB_GP_Update.Text)

here is my code

but i get this error
Syntax error (missing operator) in query expression '[Dell] LOB = [Access Server] Revenue = [900000]'.

[Reverend Jim]

You need to change it to

cmd.CommandText = "UPDATE [" & EBU2DB_LOB & "] " _
                & "   SET Months  = ?," _
                & "       Brand   = ?," _
                & "       LOB     = ?," _
                & "       Revenue = ? " _
                & " WHERE GP      = ? "

cmd.Parameters.AddWithValue("@Months  ", .Combo_LOB_Month_Update.Text)
cmd.Parameters.AddWithValue("@Brand   ", .Combo_LOB_Brand_Update.Text)
cmd.Parameters.AddWithValue("@LOB     ", .Combo_LOB_LOB_Update.Text)
cmd.Parameters.AddWithValue("@Revenue ", .Text_LOB_Revenue_Update.Text)
cmd.Parameters.AddWithValue("@GP      ", .Text_LOB_GP_Update.Text)

I'm assuming that because your controls have a leading "." the code is isside a With block. Note how much easier it is to read the code when a few extra spaces makes the fields line up and by spreading out the query text over multiple lines.

[ImZick]

Wow thanks it works :)
i get confused in this line

& " SET Months = ?," _
& " Brand = ?," _
& " LOB = ?," _
& " Revenue = ? " _
& " WHERE GP = ? "

[Reverend Jim]

What are you confused about?

[ImZick]

i don't knnow what is the meaning of this " ? "

[Reverend Jim]

I don't know how it could be any more obvious. "?" is a place holder. Each call to Parameters.AddWithValue replaces each successive "?" with a value. Unfortunately, Oledb uses this somewhat confusing format. If you were using a SQL database you could use a native SQL interface and do

cmd.CommandText = "UPDATE [" & EBU2DB_LOB & "] " _
                & "   SET Months  = @Months, " _
                & "       Brand   = @Brand,  " _
                & "       LOB     = @LOB,    " _
                & "       Revenue = @Revenue " _
                & " WHERE GP      = @GP      "

cmd.Parameters.AddWithValue("@Months ", .Combo_LOB_Month_Update.Text)
cmd.Parameters.AddWithValue("@Brand  ", .Combo_LOB_Brand_Update.Text)
cmd.Parameters.AddWithValue("@LOB    ", .Combo_LOB_LOB_Update.Text)
cmd.Parameters.AddWithValue("@Revenue", .Text_LOB_Revenue_Update.Text)
cmd.Parameters.AddWithValue("@GP     ", .Text_LOB_GP_Update.Text)

which would be much clearer.