Problem in inserting a new row in MS Access using VB.NET

[yzlin04]

Hello,
I'm new in vb.net. I have a problem in inserting a new row to a MS Access database table.
There is no error message comes out when i run my program, but when i check my MS Access table, there is no record inserted. So, i don't know which line is incorrect.

Here is my code:

Dim desc, itemID As String

itemID = txtItemID.Text
desc = txtItemDesc.Tex t

Dim myOleDbConnecti on As OleDb.OleDbConn ection
Dim insertCommand As String
Dim myConnectionStr ing As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=E:\Lectu re\CS220\Progra m backup 3\Ai Ling Ka Salon System\database \login.mdb;User ID=Admin;Passwo rd=;"

insertCommand = "INSERT INTO tblLogin (username, password) VALUES(desc, itemID);"

myOleDbConnecti on = New OleDb.OleDbConn ection(myConnec tionString)

Dim myOleDbCommand As New OleDb.OleDbComm and(insertComma nd, myOleDbConnecti on)

myOleDbCommand = myOleDbConnecti on.CreateComman d

myOleDbCommand. CommandType = CommandType.Tex t

myOleDbCommand. CommandText = insertCommand

myOleDbConnecti on.Open()

Dim temp_num As Integer
Try
temp_num = myOleDbCommand. ExecuteNonQuery
Catch ex As Exception
Trace.WriteLine (ex.ToString)

End Try

myOleDbConnecti on.Close()

Thanks...

[hariharanmca]

insertCommand = "INSERT INTO tblLogin (username, password) VALUES(desc, itemID);"

Is this desc and item is an variable of front-end.
because

desc is a key word of back-end for Descending order

you cannot pass that as string

[yzlin04]

now i change the 'desc' to 'description', the same case happen again...i can't see my record inside the MS Access table...

Dim description, itemID As String


itemID = txtItemID.Text
description = txtItemDesc.Tex t

Dim myOleDbConnecti on As OleDb.OleDbConn ection
Dim insertCommand As String
Dim myConnectionStr ing As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=E:\Lectu re\CS220\Progra m backup 3\Ai Ling Ka Salon System\database \login.mdb;User ID=Admin;Passwo rd=;"

insertCommand = "INSERT INTO tblLogin (username, password) VALUES(descript ion, itemID);"

myOleDbConnecti on = New OleDb.OleDbConn ection(myConnec tionString)

Dim myOleDbCommand As New OleDb.OleDbComm and(insertComma nd, myOleDbConnecti on)

myOleDbCommand = myOleDbConnecti on.CreateComman d

myOleDbCommand. CommandType = CommandType.Tex t

myOleDbCommand. CommandText = insertCommand

myOleDbConnecti on.Open()

Dim temp_num As Integer
Try
temp_num = myOleDbCommand. ExecuteNonQuery
Catch ex As Exception
Trace.WriteLine (ex.ToString)

End Try

myOleDbConnecti on.Close()

Is this desc and item is an variable of front-end.
because

desc is a key word of back-end for Descending order

you cannot pass that as string

[hariharanmca]

now i change the 'desc' to 'description', the same case happen again...i can't see my record inside the MS Access table...

Dim description, itemID As String


itemID = txtItemID.Text
description = txtItemDesc.Tex t

Dim myOleDbConnecti on As OleDb.OleDbConn ection
Dim insertCommand As String
Dim myConnectionStr ing As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=E:\Lectu re\CS220\Progra m backup 3\Ai Ling Ka Salon System\database \login.mdb;User ID=Admin;Passwo rd=;"

insertCommand = "INSERT INTO tblLogin (username, password) VALUES(descript ion, itemID);"

myOleDbConnecti on = New OleDb.OleDbConn ection(myConnec tionString)

Dim myOleDbCommand As New OleDb.OleDbComm and(insertComma nd, myOleDbConnecti on)

myOleDbCommand = myOleDbConnecti on.CreateComman d

myOleDbCommand. CommandType = CommandType.Tex t

myOleDbCommand. CommandText = insertCommand

myOleDbConnecti on.Open()

Dim temp_num As Integer
Try
temp_num = myOleDbCommand. ExecuteNonQuery
Catch ex As Exception
Trace.WriteLine (ex.ToString)

End Try

myOleDbConnecti on.Close()

and you never explain what is the error








1. Go to MS access views
2. copy your query and past it in query view
3. then execute it using “!” symbol
4. I think description is also reserved word of database


and copy the error

[yzlin04]

just like the above, there is no error message comes out, so i don't know what is the error.
after i run using "!" symbol, also no error.

and you never explain what is the error








1. Go to MS access views
2. copy your query and past it in query view
3. then execute it using “!” symbol
4. I think description is also reserved word of database


and copy the error

[hariharanmca]

just like the above, there is no error message comes out, so i don't know what is the error.
after i run using "!" symbol, also no error.

change the name descreption

[yzlin04]

it is still the same as before even i have changed the name "descriptio n" to "test1"
note: i only have one table (tblLogin) and one query (qryLogin) in MS Access.
both of them are empty.

change the name descreption

[hariharanmca]

it is still the same as before even i have changed the name "descriptio n" to "test1"
note: i only have one table (tblLogin) and one query (qryLogin) in MS Access.
both of them are empty.

Refer post #2

and you never explain why you are using description, itemID


if this description, itemID is a string value then you should pass query like

insertCommand = "INSERT INTO tblLogin (username, password) VALUES(‘desc’, ‘itemID’);"

[hariharanmca]

Refer post #2

and you never explain why you are using description, itemID


if this description, itemID is a string value then you should pass query like

insertCommand = "INSERT INTO tblLogin (username, password) VALUES(‘desc’, ‘itemID’);"

i think this is wrong,
login name = Descreption ot test1
password = itemID

just check that

[yzlin04]

i already changed the description to test1.

i use:
- test1 as a name for the txtItemDesc text field value
- itemID refers to the txtItemID text field value, and the ID is not integer, but string.

i pass in the query:

insertCommand = "INSERT INTO tblLogin (username, password) VALUES ('test1', 'itemID');"

Refer post #2

and you never explain why you are using description, itemID


if this description, itemID is a string value then you should pass query like

insertCommand = "INSERT INTO tblLogin (username, password) VALUES(‘desc’, ‘itemID’);"

[yzlin04]

i just use the login.mdb file because both the fields are in text.
And the value i want to insert into the row is also string. so i just use the tblLogin for trying to see whether the code works or not.

i think this is wrong,
login name = Descreption ot test1
password = itemID

just check that

[yzlin04]

hello Hariharanmca...
thanks a lot for your help.
now i can insert a record into the row already, but with the value that i already assigned (static).

but so sorry, i still have another question that i want to ask.
how to insert the value from a textbox into the table row?
because when i try this:

itemID = txtItemID.Text
insertCommand = "INSERT INTO tableTempTransa ction (item_Name, quantity, unit_Price, discount, subtotal) VALUES(itemID, '123', '456', '789', '951');"

it doesn't work. no record added in the row.


but when i try this:

insertCommand = "INSERT INTO tableTempTransa ction (item_Name, quantity, unit_Price, discount, subtotal) VALUES('itemID' , '123', '456', '789', '951');"

it works. but the field is only filled by those value, not the value from the text box.

i think this is wrong,
login name = Descreption ot test1
password = itemID

just check that

[hariharanmca]

i just use the login.mdb file because both the fields are in text.
And the value i want to insert into the row is also string. so i just use the tblLogin for trying to see whether the code works or not.

So, use like this

insertCommand = "INSERT INTO tblLogin (username, password) 
VALUES( ‘" & strValue1 & "’ , ‘" & strValue2 & "’);"

[yzlin04]

ok...finally it works...
thanks a lot...:)

So, use like this

insertCommand = "INSERT INTO tblLogin (username, password) 
VALUES( ‘" & strValue1 & "’ , ‘" & strValue2 & "’);"

[hariharanmca]

insertCommand = "INSERT INTO tableTempTransa ction (item_Name, quantity, unit_Price, discount, subtotal) VALUES('itemID', '123', '456', '789', '951');"

it works. but the field is only filled by those value, not the value from the text box.

i don't know, why you are using numbers as string?