add a row to an access database

[hugedean]

Hi,

I have been trying to add a row to an access database using Vb.net for about 3 weeks now and I just can't get it to work.

I am desperate, I have tried multitudes of tutorials on the internet and I have gone through as many forums trying to find out how to do this but to no avail.

Its simple, I have a program where the user types a message into a text field and presses a submit button. The message is then split up into individual words and each word is added as a new row to a table in the database. This program should have taken 15mins to write, but it has been three weeks and I still haven't managed to do it. I am soo frustrated about it and I am desperate for help. Can someone please either describe how to do this (in a simple step by step manner explaining what and why for each step) or even better; write a sample program that shows me how?

Thankyou for your time everyone,

Dean

 

[vis781]

Post the code you have and we'll try to point you in right direction.

 

[hugedean]

The code

Ok well this is the code that is supposed to do the database stuff but I want you to know that I'm not really sure what it is all supposed to do because I used some free code from the net and modified it slightly.

Also, I have currently scrapped all the code to do with databases and started from scratch again cause it screwed everything up so bad, so this code below is pretty much in the bin as far as I'm concerned.

PublicSub AddWordRow(ByVal Word AsString, ByVal Used AsInteger, ByVal UsedBefore AsString, ByVal UsedAfter AsString, ByVal Group AsString)
Dim InsertCommand AsString
Try
OleDbCon = New OleDbConnection(Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\ICS.mdb")
OleDbCon.Open()
InsertCommand = "insert into Words values(" & Word & ",'" & Used & "','" & UsedBefore & "','" & UsedAfter & "', '2','" & Group & "')"
'string stores the command
OleDbCmd = New OleDbCommand(InsertCommand, OleDbCon)
Catch
MsgBox("catch")
EndTry
OleDbCon.Close()
EndSub

So that code above is pretty much useless. But here is some new code that is very simple and still has the same idea and I will be able to build on it if I can ever get it to work.

PublicClass Form1
PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
Dim word As String
word = "hello"
'add word to the database here
'the database is simple: a table with two columns (ID(autonumber), word(text))
EndSub
EndClass

 

[vis781]

Ok, i don't want to sound harsh here, but that is a bit all over the place. You need to start from scratch as you have said. First.. Do a member search for TechGnome on these forums and check his signature for the ADO.Net tutorials. Read them carefully and follow along with your own Pc. Don't get all frustrated, take your time. Then once you have it working as per the tutorial. If there are things you don't understand, or are not sure why something has to be done in a certain way post back and ask.

 

[hugedean]

sigh, ok will do.

 

[hugedean]

Ok I have gone through the tutorial and this is the code I have come up with:

PublicClass Form1
PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim word AsString
word = "hello"
Dim objConnection As ADODB.Connection
Dim rstResults As ADODB.Recordset
objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:whatever.mdb"
objConnection.Open()
rstResults = New ADODB.Recordset
rstResults.Open("SELECT * FROM Wordslist", objConnection, adOpenStatic, adLockOptimistic)
rstResults.AddNew()
rstResults.Fields("word").Value = word
rstResults.Update()
objConnection.Close()
objConnection = Nothing
EndSub
EndClass

I have a couple questions...

Firstly I get a lot of blue squiggles under stuff like "ADODB.Recordset", "ADODB.Connection", "adOpenStatic" and "adLockOptimistic", all the error tags to go with these things say that its not defined or not declared, how do I fix this problem?

Secondly what do "adOpenStatic, adLockOptimistic" do?

Thanks for all your help

 

[vis781]

Oh bum. I had to put my head in my hands when i saw your post. I said to have a look at the ADO.Net tutorials. You have been reading the old ADO tutorials. They are quite different. I can't help but hope that you have a little smile to yourself and think.. "Oh bum" or atleast something like that!! Dont give up on this just yet, the tutorial will still have been helpful in some regards. Right here is how you fill a datatable with info from the Database in ADO.Net...

Private MyDatatable as new system.Data.Datatable
Private MyOledbDataAdapter As New System.Data.OleDb.OleDbDataadapter
 
Button1_Click(.....) Handles....
 
Dim MyCon as new System.Data.OleDb.OleDbConnection(your connection string)
 
Dim MyOleDbCommand As New System.Data.OleDb.OleDbCommand("SELECT * FROM Wordslist", Mycon)
 
Me.MyOleDbDataAdapter.SelectCommand = MyOleDbCommand
Me.MyOleDbDataAdapter.Fill(Me.MyDatatable)

Thats it, easy eh? I have included the fully qualified names of the components (so you can get an idea of where things are in the framework) but you don't have to if at right at the top of the class you put the following..

Imports System.Data.Oledb

So when you click the button it will fill the MyDatatable object with all the info from wordslist if you plonk a datagrid on the form and set it's datasource to the datatable you will see the info in there.
Get that right in your head (i mean to say, read through and try to understand what is happening there) then we'll move onto INSERT, UPDATE, DELETE etc.

 

[Rogue 1]

Hang in there Dean. I did the same exact thing this morning until I realized that recordsets did not exist in ADO.net. It will get better.

 

[hugedean]

That'd be right lol. Yeah I was kicking myself laughing. And thanks for the encouragement, I plan to stick this out.

Anyway, I have read through that stuff and I understand it all (I must admit that ADO tutorial did help after all).

Thanks for leaving the full.. I don't know what you call it but the "System.Data.OleDb" - that also helps a bit.

I feel like I'm getting somewhere!! (finally )

So now what?

 

[vis781]

what do you want to do next? INSERT, UPDATE, DELETE I'm glad you understand things better now. Actually, the rest is fairly simple you have the basics down. I would urge you to do some reading on parameterised queries it will be helpful for you to know

 

[hugedean]

Ok I'll start reading up on parameterised queries. I'd like to do INSERT and DELETE next, and could you show me how to do these without doing it through the datagrid? Thanks so much for your help vis781, I really appreciate you giving up time to help me out like this.

 

[vis781]

Ok, here's an INSERT Commadn for ya that should work with your code. Plonk a button on your form and call it 'Insert New Record'

In the click event put this...

Dim MyCommand as New OleDbCommand("INSERT INTO Wordslist VALUES (?, ? , ? , ? , ?)", YourOleDbConnection)
 
MyCommand.Parameters.Add("@Word", OleDbType.VarWChar,255,"Word").Value = "WhatEver You Like Here"
MyCommand.Parameters.Add("@Used",OleDbType.Integer,0,"Used").Value = What Ever You Like Here
MyCommand.Parameters.Add("@UsedBefore",OleDbType.VarWChar,255,"UsedBefore").Value = WhatEver You Like Here
Mycommand.Parameters.Add("@UsedAfter", OleDbType.VarWChar,255,"UsedAfter").Value = WhatEver You Like Here
MyCommand.Parameters.Add("@Group",OleDbType.VarWChar,255,"Group").Vaue = WhatEver You Like Here.

Ok, so we have created a parameterised query. Notice the question marks, they are the placeholders for the .Value bit of the parameters we have added below it. In Ms Access we have to add the parameters to the collection in the order that they appear in the query, Ms Access doesn't support named parameters. So now we have that set up we can use the 'Execute NonQuery' Method to commit our new row to the database.

Try
YourOleDbConnection.Open
MyCommand.ExecuteNonQuery
Catch Ex as Exception
MessageBox.Show("Error Message Was " + Ex.Message + " StackTrace Was " + Ex.StackTrace.ToString" 
 
Finally
YourOleDbConnection.Close
 
End Try

Now i'm going to assume that you have got an error after that line, if you didn't then i'l give myself a pat on the back. But i've no doubt that you will. So when you do get that error, go through the parameters and check all the OleDbTypes against the ones in the database to make sure they are correct. Also check all the casings of the last argument in each parameter.

 

[hugedean]

Ok, cool. I did get some errors but its all good now and I got it working. There is just one problem; I have an ID column that is of type autonumber. I don't know how to get around this, how do I insert a new row with one of the columns being an autonumber? I've had a look on the net but it looks pretty complex and I'm not sure what advice to take as there are many opinions about it.

I got it all working by changing that column type to an Integer and just putting mock values in. I would like to use the autonumber if possible or otherwise somehow get the max value of that column, add 1 and then make this new number the ID for the new row.

Also, do you have any good sites I could check out on parameterised queries?

Cheers

 

[vis781]

The autonumber field will generate it's own entry you don't have to put any data in there. As for a good site for parameterised queries i'll have a look and check back.

 

[hugedean]

how do I just not add a value to the autonumber field? I've tried a few things but I keep getting errors about there not being a value added or if I simply leave out the field it says that the number of values and fields being added does not match the destination. Here is the code I have at the moment...


Private MyDatatable AsNew System.Data.DataTable
Private MyOledbDataAdapter AsNew System.Data.OleDb.OleDbDataAdapter
PublicSub AddWordRow(ByVal Word AsString, ByVal Used AsInteger, ByVal UsedBefore AsString, ByVal UsedAfter AsString, ByVal Group AsString)
Dim MyCon AsNew System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ICS.mdb")
MyCon.Open()
Dim MyCommand AsNew System.Data.OleDb.OleDbCommand("INSERT INTO Words VALUES (? , ? , ? , ? , ? , ?)", MyCon)
MyCommand.Parameters.Add("@Word", System.Data.OleDb.OleDbType.VarWChar, 255, "Word").Value = Word
MyCommand.Parameters.Add("@Used", System.Data.OleDb.OleDbType.Integer, 0, "Used").Value = Used
MyCommand.Parameters.Add("@UsedBefore", System.Data.OleDb.OleDbType.VarWChar, 255, "UsedBefore").Value = UsedBefore
MyCommand.Parameters.Add("@UsedAfter", System.Data.OleDb.OleDbType.VarWChar, 255, "UsedAfter").Value = UsedAfter
MyCommand.Parameters.Add("@ID", System.Data.OleDb.OleDbType.BigInt, 0, "ID").Value = ""
MyCommand.Parameters.Add("@Group", System.Data.OleDb.OleDbType.VarWChar, 255, "Group").Value = Group
Try
MyCommand.ExecuteNonQuery()
Catch Ex As Exception
MessageBox.Show("Error Message Was " + Ex.Message + " StackTrace Was " + Ex.StackTrace.ToString)
EndTry
MyCon.Close()
EndSub