Page 1 of 1
Problems with Access database and DataAdapter
Posted: Sat Mar 12, 2011 3:00 pm
by GoodGuy17
Hello,
I am having horrible trouble with the DataAdapter, which I just learned to use yesterday. I am learning database working in VB.NET, and this is my problem.
I get a line highlighted:
Code: Select allDataAdapter.Update(DataSet, "Invoices")
and the error says "Syntax error in INSERT INTO statement."
Download the source files for this project (and Access database too) here:
Invoice Manager.zip
Please look around in the source code and tell me what is wrong. I don't see anything wrong! Please help! +rep to first correct answerer!
~GoodGuy17
Re: Problems with Access database and DataAdapter
Posted: Sat Mar 12, 2011 4:11 pm
by Dummy1912
hello,
well I'm not sure but you use UPDATE
i have seen the database file
and i don't see any autonumber (this create a recordno by its self like 1 2 3 4 5 ect...)
because you will need this to update the current record.
so the update will not work. i guess
so if you load a data it will read the autonumber and load the record when you press update this will overwrite the current info from that autonumber.
Re: Problems with Access database and DataAdapter
Posted: Sun Mar 13, 2011 2:45 am
by GoodGuy17
Can you show me how to add autonumbers? I don't know anything with Access, but I am trying to learn using databases from them with VB :P
Re: Problems with Access database and DataAdapter
Posted: Tue Mar 15, 2011 9:42 pm
by mandai
I couldn't make much sense out of using DataAdapter in that way. You can list, add, and remove database records with this:
Code: Select all 'table name is Invoices
Dim odc As OleDbConnection = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=Invoices.mdb")
odc.Open()
'read the records + some attributes
Dim cmd As OleDbCommand = odc.CreateCommand()
cmd.CommandText = "SELECT * FROM Invoices"
Dim res As OleDbDataReader = cmd.ExecuteReader()
Dim current As Integer = 0
While res.Read()
For i As Integer = 0 To res.FieldCount - 1
MsgBox(current & ": " & res.GetName(i) & ": " & res.Item(i) & " [" & res.GetDataTypeName(i) & "]")
Next
current += 1
End While
res.Close()
'add a record
cmd.CommandText = "INSERT INTO Invoices VALUES ('user', 5, 15/03/2011, 'address', 'service')"
cmd.ExecuteNonQuery()
'remove user's record
cmd.CommandText = "DELETE * FROM Invoices WHERE name='user'"
Dim affected As Integer = cmd.ExecuteNonQuery()
MsgBox(affected & " rows deleted")
odc.Close()
Re: Problems with Access database and DataAdapter
Posted: Tue Mar 15, 2011 11:39 pm
by GoodGuy17
Mandai, the way I am learning it is from here:
http://homeandlearn.co.uk/NET/nets12p1.html
Note that if you just view that, you will get misinformed; I am learning NON Data Source, I am trying to make it from scratch myself. Just scan through the parts in the database teacher, and you will get a general idea.
I looked over your code, and I got scrambled up (like you did with my code, I think). Not to be mean or anything, but I would like to learn one way, so can you please try to help using my code? If you didn't understand the code I had, just take a quick look through the tutorials I am learning from.
Re: Problems with Access database and DataAdapter
Posted: Thu Mar 17, 2011 11:26 pm
by GoodGuy17
BUMP!
I really need help! Please take another look!
Re: Problems with Access database and DataAdapter
Posted: Fri Mar 18, 2011 12:11 am
by mandai
Is there any particular advantage in following the other method?
Re: Problems with Access database and DataAdapter
Posted: Fri Mar 18, 2011 12:56 am
by GoodGuy17
I don't really wanna learn another way :P
Seriously though, I already learnt the way I said, and if I learn another way in my beginner steps in database programming, I'll become confused.
Re: Problems with Access database and DataAdapter
Posted: Sat Apr 09, 2011 7:21 pm
by GoodGuy17
Does anyone know how to help?