Indeed. The da downlaods all matching records and puts them in the nominated table. If you fail to clear the table first, then you will end up with repeated rows.
if i stop my program after each operation i am testing then
it seems to work fine.
You dont clear a dataadapter because a dataadapter is a device that transports data to and from a database. It has no data storage capabilities of its own
b) perform a second sql query to get the sorted data, and fill the dataadapter properly.
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:
VB.NET:
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.
VB.NET:
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.
its a job numbering system, the number will be associated with a drawing file. i only have a range of numbers to work with, everytime the user runs my program they get a job number.
but just in case some one makes a mistake and assigns a job number to something that does not need a number, or if for some other reason the job number needs to gets freed up for use.
i dont want to keep just generating new numbers if previous numbers can be used.
if you have only a limited number of jobs available I suggest you prepoulate your table with all available job numbers, then every time you wish to use one, you should:
SELECT MIN(job_num) FROM table WHERE some_other_field IS NULL
ic, I was thinking on doing something like that but i was thinking on speed issue, which i dont really think is that much of a problem but the number range is from 95000009 - 9999999. do you think this would impact your sugestion att all??
I was just thinking of all the things that could go wrong, and just assumed that if there is a gap, or unused numbers, it would be a problem.
But i am not gunna pre populate the table, first i am gunna use your sql statement to see if there is a job number that has no description(this will happen if some one assigns one, then for some reason they decide they didnt need one). if there is none then i will just add a record.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.
