Thanks for the replies guys.
My code got to be out of hand its pretty all over the place right now I was trying different things.
But here is the purpose of my progrm:
It’s an add-in that will be used from a couple different apps
the goal is to increment the table by one, but first it has to check if any previous values/rows are missing, (I am comparing the current row with the next row to see if there is a gap, that’s why I always need the data sorted and up to date)
((That’s the only way I see of doing it right now, is there a sql function that will do this type of check??))
If there is a gap in the table then, re-use the missing number
When the user presses a button it calls my function to connect to the database then:
-get all the data sorted
--is there a gap
---yes -> use the missing number
---no -> increment the table by one
At this point I am only testing in a very basic vb app that I created:
My test app, I made reference to my dll and created an object and I can see all the functions I made; that’s all working fine.
I solved one of my issues, while copy and pasting my code..
In my test app I made a connection to the database on form load with:
mc = New dbt.mapicsPop
mc.conn("C:\db1.mdb")
in the test app one button displays the data in the listbox(thanks for the tip on databinding)
the other button would call a function that would do the check, then add a row to the table.
I took the connection part from the form load, and put it in the button to display the data, at the top.
now whenever I press that display button, it will display the data up to date and sorted properly. Because I guess it creates a new connection object, runs the sql string, and fills the ds again.
So my problem is, I only know how to run a sql query as part of the connection string.
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & dbLoc
con.Open()
'MsgBox("A Connection to the Database is now open")
sql = "SELECT * FROM mapics order by mapicsNum"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "mapic")
How can I just run a query, without reconnecting to the database, and get the sorted data (or maybe just run a completely different query), without using those 6 lines of code?
Or do I just clear the ds first, then re-fill it?
Because right now with my temporary fix, the program will only work preoperly if I hit the button to display first, example:
Press the button to display…good.( makes the connectioin to the db)
Press the button to add record…good(I can press this button all day works).
If I manually delete a record in the db and press the button to add record then
Works the first time but puts it at the bottom, (I understand this is normal now)
If I hit the button to add record again then
I get an error because this ds is not sorted yet, and my program checks current record and next record to see if there is a gap. In this case it thinks there is a gap but the missing value is at the bottom of the table, so I get an error when trying to add a new record with the same value)
If I hit the button to display, before I hit the button to add a record the second time then
It will work normally, because it goes through that whole connection process, and gets the sorted data.
Thanks again, sorry for all the typing
