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.Text

Dim myOleDbConnection As OleDb.OleDbConnection
Dim insertCommand As String
Dim myConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Lecture\CS220\Program backup 3\Ai Ling Ka Salon System\database\login.mdb;User ID=Admin;Password=;"

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

myOleDbConnection = New OleDb.OleDbConnection(myConnectionString)

Dim myOleDbCommand As New OleDb.OleDbCommand(insertCommand, myOleDbConnection)

myOleDbCommand = myOleDbConnection.CreateCommand

myOleDbCommand.CommandType = CommandType.Text

myOleDbCommand.CommandText = insertCommand

myOleDbConnection.Open()

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

End Try

myOleDbConnection.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.Text

Dim myOleDbConnection As OleDb.OleDbConnection
Dim insertCommand As String
Dim myConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Lecture\CS220\Program backup 3\Ai Ling Ka Salon System\database\login.mdb;User ID=Admin;Password=;"

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

myOleDbConnection = New OleDb.OleDbConnection(myConnectionString)

Dim myOleDbCommand As New OleDb.OleDbCommand(insertCommand, myOleDbConnection)

myOleDbCommand = myOleDbConnection.CreateCommand

myOleDbCommand.CommandType = CommandType.Text

myOleDbCommand.CommandText = insertCommand

myOleDbConnection.Open()

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

End Try

myOleDbConnection.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.Text

Dim myOleDbConnection As OleDb.OleDbConnection
Dim insertCommand As String
Dim myConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Lecture\CS220\Program backup 3\Ai Ling Ka Salon System\database\login.mdb;User ID=Admin;Password=;"

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

myOleDbConnection = New OleDb.OleDbConnection(myConnectionString)

Dim myOleDbCommand As New OleDb.OleDbCommand(insertCommand, myOleDbConnection)

myOleDbCommand = myOleDbConnection.CreateCommand

myOleDbCommand.CommandType = CommandType.Text

myOleDbCommand.CommandText = insertCommand

myOleDbConnection.Open()

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

End Try

myOleDbConnection.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 "description" 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 "description" 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 tableTempTransaction (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 tableTempTransaction (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 tableTempTransaction (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?

[yzlin04]

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

no, actually i just want to test whether my code works or not. if already can work, i can change it to the value i want.
Thanks