add a row to an access database

hugedean

Member
Joined
Oct 25, 2006
Messages
13
Programming Experience
Beginner
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
 
you need to take out references to all to the Autonumber field (assuming ID). so take out the parameter for the ID and take out the corresponding question mark in the connection string.

should work :)

regards
adam

EDIT: if that doesnt work, you should probably check the table you are inserting to to make sure the ID field is an autonumber, and not just a number. :)
 
Hey Anti-Rich, I tried that but I still get the error "The number of query strings does not match the number of destination fields" - it seems I must insert something into the ID field (which is an autonumber cause I double checked). Here is the code again after doing what you suggested that was to remove the parameter for the ID (autonumber) field and the question mark from the connection string. Any more ideas? Have I done something wrong?

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("@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
 
hm not sure if this will have an effect but i changed your code as per the following...
VB.NET:
[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] AddWordRow([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] Word [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] Used [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] UsedBefore [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] UsedAfter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] Group [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyCon [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbConnection([/SIZE][SIZE=2][COLOR=#800000]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ICS.mdb"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbCommand([/SIZE][SIZE=2][COLOR=#800000]"INSERT INTO Words VALUES (? , ? , ? , ?, ?)"[/COLOR][/SIZE][SIZE=2], MyCon)
MyCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@Word"[/COLOR][/SIZE][SIZE=2], System.Data.OleDb.OleDbType.VarWChar, 255, [/SIZE][SIZE=2][COLOR=#800000]"Word"[/COLOR][/SIZE][SIZE=2]).Value = Word
MyCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@Used"[/COLOR][/SIZE][SIZE=2], System.Data.OleDb.OleDbType.Integer, 0, [/SIZE][SIZE=2][COLOR=#800000]"Used"[/COLOR][/SIZE][SIZE=2]).Value = Used
MyCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@UsedBefore"[/COLOR][/SIZE][SIZE=2], System.Data.OleDb.OleDbType.VarWChar, 255, [/SIZE][SIZE=2][COLOR=#800000]"UsedBefore"[/COLOR][/SIZE][SIZE=2]).Value = UsedBefore
MyCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@UsedAfter"[/COLOR][/SIZE][SIZE=2], System.Data.OleDb.OleDbType.VarWChar, 255, [/SIZE][SIZE=2][COLOR=#800000]"UsedAfter"[/COLOR][/SIZE][SIZE=2]).Value = UsedAfter
MyCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@Group"[/COLOR][/SIZE][SIZE=2], System.Data.OleDb.OleDbType.VarWChar, 255, [/SIZE][SIZE=2][COLOR=#800000]"Group"[/COLOR][/SIZE][SIZE=2]).Value = Group
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]MyCon.Open()
MyCommand.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] Ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MessageBox.Show([/SIZE][SIZE=2][COLOR=#800000]"Error Message Was "[/COLOR][/SIZE][SIZE=2] + Ex.Message + [/SIZE][SIZE=2][COLOR=#800000]" StackTrace Was "[/COLOR][/SIZE][SIZE=2] + Ex.StackTrace.ToString)
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2]MyCon.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

just good practice to only open the connection after you have added the parameters etc. just my opinion anyway.

try that. if that doesnt work, i would reccomend you simply change the ID field back to integer and when you are inserting the row, for ID field, simply use a
select max(ID) from yourtable

and increment it by one.
let me know what happens
regards
adam
 
ok, say you have a function that gets the max id...
VB.NET:
private function GetMaxID() as integer
dim sql as string
dim cmd as oledbcommand
dim cnn as new oledbconnection("your connection string")
dim i as integer
dim reader as oledbdatareader
 
sql = "SELECT MAX(ID) FROM YourTable"
cmd = new oledbcommand
with cmd
 
    .connection = cnn
    .commandtext=sql
 
    try
         .connection.open
         reader = .executequery
         reader.read
         i = reader.getint32(0)
         reader.close
    catch ex as exception
          ' using ex.tostring gets the message and stack trace at once
          messagebox.show(ex.tostring)
    finally
          .connection.close
    end try
 
return i
 
end function
if you want some good links about sql, and all the functions and specialized syntax that there are, try this link...

http://www.1keydata.com/sql/sql.html

i think its an excellent site which explains the functions and syntax quite well.

let me know how you go!

regards
adam
 
Last edited:
Hey,

It seems to be all good except for one thing. The line of code where you have "reader = .executequery" I get an error that says "ExecuteQuery is not a member of System.Data.OleDb.OleDbCommand".

I'm not really sure what to do with it. I looked it up on the net and I think I need to use a data adapter instead but I have no clue how to do it properly without breaking anything else in the code you gave me. Besides, I could be completely wrong and you don't need the data adapter.

Thanks for your time and help I really appreciate it :)
 
lol, ok cool it works. Thanks heaps... It's almost time to celebrate!

I say almost because there is just one more thing I'd like to fix. The code works fine so long as there is something in the table already. But when the table is empty it has an error saying that it can't cast the type Int32 or whatever. In any case it happens at this line:

i = reader.GetInt32(0)

Any fixes?
 
yeah no problem, glad to help.

yeah thats easy to fix.. man that error used to annoy me.

delete that line and put this...
VB.NET:
if not reader.isdbnull(0) then 
    i = reader.getint32(0)
end if

im pretty sure that will fix it (i havent dealt with that one in ages)

test it and let me know if it works
:)
regards
adam
 
Oh yeh! haha Sweet!!! It works... Booyah!

Thanks heaps Anti-Rich and vis781.

I'm outta here... now where's that bottle of... ;)
 
Cor.. looks like a load of hard work and typing to me! Take a read of the DataWalkthroughs 2.0 link in my signature - it should help you see that you were correct in the first instance.. the program should take about 30 minutes to write and is probably achievable in less than 20 lines of code when using the new data access methods
 
Agreed, but that wasn't the point of this thread. It is important for someone new to know how to wite SQL add parameters etc. Wizards and automation are all well and good but sometimes you need to resort to the old fashioned method of writing it yourself. hugedean has gained much more knowledge by doing it 'the hard way' than just having the process automated for him.
 
good point... i think its better to do it the hard way because you understand better whats going on behind the scenes rather than just trusting it to the wizards and pre-defined methods. also, some people (including myself) just prefer to have the control and flexibility of doing it the "hard way", its easier to fix if problems pop up.
 
While I dont disagree, I would point out that:

Even when using TableAdapters, it is still necessary to write at least the SELECT query, any specialised selects, and possibly custom I/U/D statements

Manually writing the parameter addition code is tedious and boring. Manually writing the InitializeComponents() method to lay out, size, colour and font all the controls of a form is also tedious and boring. I gotta say.. respect to you guys if you manually write your InitComponents() the hard way too, to get an appreciation of what it's doing! :D

Sometimes, beavering away at the lower levels for hours occludes the higher purpose one is trying to achieve and can make the program logic quirky and hard to follow. While I appreciate that the OP has gained understanding from writing database code in e.g. a button handler, IO dont think it's helped his sense of encapsulation. Additionally, he may leave with the impression that this is the way it must always be done, which would indeed be a shame because it means Microsoft's efforts in providing a very good GUI to a tedious task, are under utilised.

Doing database access in this way, more often than not, I see it coded such that it all goes out of scope, and everything is destroyed, only to be recreated the next time the access is required; not exactly the idea of parameterisation


I'm not looking to spark a conflict of opinion; I totally agree with you guys from the "get your hands messy and learn" aspect because I used to write my Java GUIs by hand, specifying every grid location, resize policy, padding, margin, font, colour etc of every control on a form. I was so glad when NetBeans came along with a visual designer that allowed me to tap those values into a property grid.. I just think it's easier to learn about the overall data flow and access aspects through use of the visual tools than hand writing code like this.
 
Back
Top