how to store/retreive to/from database

[nahya^^]

consider me a retard when reading/replying.

i have no idea how to even begin this. but what i'm trying to do is maybe use an access database to store/retrieve data.

i've looked at a tutorial from vbcity.com, which didn't help me out a lot.

can anyone get me started? like a quick and easy way to read/write databases?

[Pirate]

This is for adding data to the database . Note , it's one of 10s ways to do that .
I'll send you retrieving data in a second .(I'll look for that in another thread)
I'll update this one and post it in a minute .

[Pirate]

Retrieving data . It populate a combobox with data from database .

[nahya^^]

thanks!

i'll look into those. after half a day of struggling i was about to make some progress

EDIT:

hmm... i was using OleDbDataAdapter control.
ponder...
ponder...

[nahya^^]

have you tried the adding data program? it's not working for me. i got 9 errors when trying to run it.

oh (insert beep here).
i forgot i had math final the day after tomorrow.

[Pirate]

Originally posted by nahya^^
have you tried the adding data program? it's not working for me. i got 9 errors when trying to run it.

I'm sorry , I didn't test it . I just found it in one of my old dir . I'll check it now .

[Pirate]

Retrieving data [Updated version]

Ok , It's working now . I changed the whole code .

[nahya^^]

thanks. it works now.
but i tried modifying it and it doesn't work. i can't figure out why.

Public MyPath As String = Application.StartupPath & "\dbDatabase.mdb"
    Public MyConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyPath)

that's what i have at form level. and...
[Highlight=VB]
Public Sub SaveDB(ByVal TableStr As String, ByVal _Name As String, ByVal _From As String, ByVal _Until As String)

Dim SQLStr = "SELECT * FROM " & TableStr
Dim MyAdapter As New OleDb.OleDbDataAdapter(SQLStr, MyConnection)
Dim builder As New OleDbCommandBuilder(MyAdapter)
Dim mydataset As New DataSet

MyAdapter.Fill(mydataset, TableStr)
Dim MyDataRow As DataRow = mydataset.Tables(TableStr).NewRow
MyDataRow(0) = _Name
MyDataRow(1) = _From
MyDataRow(2) = _Until

mydataset.Tables(TableStr).Rows.Add(MyDataRow)
MyAdapter.Update(mydataset, TableStr)
MyConnection.Close()

MyAdapter = Nothing
MessageBox.Show("Data Saved..")
PopulateLBox(lstName, "tblDatabase")
End Sub
[Highlight=VB]
i call that with

SaveDB("tblDatabase", txtName.Text, dtpAssign.Value, dtpDue.Value)

but i keep getting this error @ MyAdapter.Update(mydataset, TableStr):

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

what could be the problem?
thanks.

[Pirate]

Try to put the whole code for adding data in try catch block and get more descriptive error message . Post here .

[nahya^^]

oh yeah. i gotta use error handling more often...

Syntax error in INSERT INTO statement.

[Pirate]

What data types your columns set to ? if text then it should save string , number = int and so on .

[nahya^^]

how can i find that out? i have access 2003. i've seen something with the column types but didn't pay much attention and forgot where i found it.

[Pirate]

Open your database and rightclick the table and choose "design view" (this applies to ms access xp but I believe they're quite similar ) .

[nahya^^]

ok i changed From and Until columns to dates, cause they're dates in hh:mm:ss dd/mm/yy format.

i still get the same error though.
i tried changing ByVal _From as String... etc to as Date but same error.

[Pirate]

Change the datatypes for From and Until to text . I know they will contain dates and time format but you can always cast that back to its origin type . and Change this line to :

SaveDB("tblDatabase", txtName.Text, dtpAssign.Value, dtpDue.Value)


with this :

SaveDB("tblDatabase", txtName.Text, dtpAssign.Value.ToString(), dtpDue.Value.ToString())

[nahya^^]

hmmm...
i tried all sorts of combinations of strings and dates with that code but still the same error.

[Pirate]

Send me sample of the proj with the db if you like !

[nahya^^]

hmm. i don't know what i did but it's giving me a different error message.

but here it is.

EDIT:

oh i moved some stuff around and it caused a different error. fixed and back with that same error as before. i'll have to be away for about an hour..

[nahya^^]

hmm..

[Pirate]

Your problem is : The column names are actually conflicting with the VB or SQL reserved keywords . Change them and it should work fine .

e.g :
CName
CFrom
****il

[nahya^^]

heh. that makes sense now.
thanks. i'll try it

EDIT:

yay it works. you're awesome.

note to self: hit yourself in the face before going to bed.

[nahya^^]

ok... this is a bit hard.

since i've populated a listbox with all the entries in the first column and haven't sorted anything, the order of items in the listbox and the entries in the database should match.

i need to use that information to retreive other information from the same row as the selected entry. i'm not sure if i'm making sense here... but i guess an example should suffice.

my database looks like this:

nName        fFrom        uUntil
   A           1            2
   B           2            3
   C           4            5

and my listbox looks like this:

A
B
C

so what i'm trying to accomplish is to retrieve 1 and 2 when the user chooses A from the listbox, 2 and 3 when s/he chooses B, etc.

i'd appreciate any help to get me started.
thanks

[nahya^^]

whoa.. i just tried something and i think it works
it's the first time i feel like i'm really object-oriented programming

i instantiated 2 new listboxes without creating them using the controls. then i filled up 2nd and 3rd columns into those listboxes. then i made the new listboxes' selected index change to that of the listbox which is on the form. i tested it by MsgBox(lstFrom.SelectedItem) and it gives me the correct entry corresponding to the entry from the "real" listbox.

i think i've just exposed my stupidity here but it's pretty cool. heh.
thanks me