By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

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

P: 18
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...
Jul 16 '07 #1
Share this Question
Share on Google+
15 Replies


hariharanmca
100+
P: 1,977
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
Jul 16 '07 #2

P: 18
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
Jul 16 '07 #3

hariharanmca
100+
P: 1,977
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
Jul 16 '07 #4

P: 18
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
Jul 16 '07 #5

hariharanmca
100+
P: 1,977
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
Jul 16 '07 #6

P: 18
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
Jul 16 '07 #7

hariharanmca
100+
P: 1,977
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’);"
Jul 16 '07 #8

hariharanmca
100+
P: 1,977
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
Jul 16 '07 #9

P: 18
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’);"
Jul 16 '07 #10

P: 18
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
Jul 16 '07 #11

P: 18
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
Jul 16 '07 #12

hariharanmca
100+
P: 1,977
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

Expand|Select|Wrap|Line Numbers
  1. insertCommand = "INSERT INTO tblLogin (username, password) 
  2. VALUES( ‘" & strValue1 & "’ , ‘" & strValue2 & "’);"
Jul 16 '07 #13

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




So, use like this

Expand|Select|Wrap|Line Numbers
  1. insertCommand = "INSERT INTO tblLogin (username, password) 
  2. VALUES( ‘" & strValue1 & "’ , ‘" & strValue2 & "’);"
Jul 16 '07 #14

hariharanmca
100+
P: 1,977
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?
Jul 16 '07 #15

P: 18
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
Jul 19 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.