[RESOLVED] Resordset

[Dbee]

Just want to let you guys know that all of you that said it could not be done.

That I did what I wanted to do and it works like a charm.

I will now be able to do my edit/insert/delete as I designed it.

[techgnome]

1) What the heck are you talking about? If this is related to another thread, a) either it should have been in that thread, or b) at least post a link to it.

2) How/What did you solve? Don't hold back, show us how insignificant we really are.

-tg

[Dbee]

I posted Theads on questions about extracting data from a database and putting it into varibles and then doing edit/insert/delete using a mshflexgrid.

Was told this would not work I got it to work fine and was able to save all changes I made on the grid.

Just wanted to let every one know I worked it out

What I applyed was an old DOS method that I have been using for years now.

[Dbee]

This is how I Accomplished the storing of the Varibles
This is a modified Microsoft Routine I found on there site

Acct is the Account number

Tables is the Table Name I am Storing

Sub Store_Other(Tables As String, DDD As Integer)
     Dim BookArray() As Variant
     Dim I As Integer
     Record_Sets.Open "SELECT * FROM " & Tables & " WHERE Account = '" & Acct & "' ORDER BY Record", Data_Connection(1), adOpenStatic, adLockOptimistic, adCmdText
     I = 0
     Record_Sets.MoveFirst
     Do While Not Record_Sets.EOF
      If InStr(1, Record_Sets("Account"), Acct) Then
       ReDim Preserve BookArray(I)
       BookArray(I) = Record_Sets.Bookmark
       I = I + 1
      End If
      Record_Sets.MoveNext
     Loop
     Cq = 1
     Record_Sets.Filter = BookArray
     Do While Not Record_Sets.EOF
      Select Case DDD
       Case 1' Created 50 records By Customer
        Record_Sets("Quanity") = Data(2, Cq, 1)
        Record_Sets("Item_Number") = Data(2, Cq, 2)
        Record_Sets("Description") = Trim(Data(2, Cq, 3))
        Record_Sets("Cost_ea") = Val(Data(2, Cq, 4))
        Record_Sets("TotalCost") = Val(Data(2, Cq, 5))
       Case 2 'Created 20 records By Customer
        Record_Sets("Zone_Number") = Data(3, Cq, 1)
        Record_Sets("Description") = Data(3, Cq, 2)
       Case 3'Created 20 records By Customer
        Record_Sets("Phone") = Data(4, Cq, 1)
        Record_Sets("Aname") = Data(4, Cq, 2)
        Record_Sets("Note") = Data(4, Cq, 3)
       Case 4'Created 20 records By Customer
        Record_Sets("Phone") = Data(5, Cq, 1)
        Record_Sets("Code") = Data(5, Cq, 2)
        Record_Sets("Aname") = Data(5, Cq, 3)
        Record_Sets("Note") = Data(5, Cq, 4)
       Case 5'Created 20 records By Customer
        Record_Sets("CodeName") = Data(6, Cq, 2)
        Record_Sets("Codes") = Data(6, Cq, 1)
       Case 6'Created 50 records By Customer
        Record_Sets("AName") = Data(6, Cq, 2)
        Record_Sets("Codes") = Data(6, Cq, 1)
      End Select
      Cq = Cq + 1
      Record_Sets.MoveNext
     Loop
     Record_Sets.Close
  End Sub

My Database is more along the line of Random than a True Database

Just passing along How I did this.

[szlamany]

I'm curious - since I've never seen something like this...

What is the point of:

If InStr(1, Record_Sets("Account"), Acct) Then

Since you already have:

Record_Sets.Open "SELECT * FROM " & Tables & " WHERE Account = '" & Acct

Isn't that INSTR going to be true for every row in the RECORDSET?

What is the DATA() array? Is that your GRID?

Are you actually updating the RECORDSET from the DATA array?

[Dbee]

Like I said Its a modifyed Routine from Microsoft

You were correct took I it of out of the routine. I will remember that from now on.

The Data() is for 8 different tables.

And I do use a grid but I also use an Insert/delete/Edit routine. I found this to be the fastest method of data entry for me. I am sure there are faster methods just not found them yet.

I use this routine to edit information and also to get information from other Data files Like Equipment,Zone Names,Central Station Codes,Police Departments,that pertain to my type of business.

I like the database concept but I use it a different, but it works

I can still quary it by the select method.

An Yes I am updateing from the Data Array To me its Faster

Let me give you this informattion I use 106 Lables to display one of tables Information.

I edit all of these Lables by a custom Program that I wrote that uses only 1 Textbox, Like all my forms I use this same routine.

Just makes creating a Form alot faster.

[szlamany]

Thanks for the clarifications...

I guess a benefit to using a RECORDSET for update to a table is that you are most likely holding locks against those rows while the updates are taking place. If needed - that's a good thing.

We call a "stored procedure" to update each row in our FLEXGRID's one-row-at-a-time. If you don't do MS SQL Server and have never used a "stored procedure" it's basically an action query (the UPDATE query in this case) that's wrapped in a function-like object in the DB. Each parameter of the SPROC represents a column in the grid.

We only call the update SPROC if the row has been changed.

That would be similar to actually building an UPDATE string:

"UPDATE SOMETABLE SET SOMECOL=" & FLEXGRID.TEXTMATRIX(r,c)...

and executing that UPDATE string.

Benefit's to this is that we are not holding locks against any rows in the table. We generally see locks as a negative thing - users holding locks that potentially go on until they click some EXIT/DONE button put a strain on a large enterprise database application.

At any rate we have taken our concept to the n-th degree at my shop. Each "screen item" - text box, combo box, flex grid - is drawn on the form at runtime. The position, size and order of the items comes from a table in the DB - so in essence we clone forms at runtime out of nothing. The queries to VIEW, INQUIRE and UPDATE (and validate) each of these screen items on our forms sits in stored procedures in the DB as well. So basically we create a form doing no VB code whatsoever - and rely on "properly named" objects in the DB to move data to and from those screen objects.

When we started our mainframe-to-VB+MSSQL conversions 6 years ago we realized that creating a VB form for every screen our customers used on the mainframe would be a month-long project for each one. When in reality every UI form basically does the same functionality (as you mentioned!).

Many ways to skin a cat...

[Dbee]

Thanks.

I agree it would be nice to update any changes to the Rows as you go the only thing that stops me from that is deleting of a row.

The way I am doing this is just a simple move of data from one Varible to another by Row.

I am totaly new to Databases my thing for a number of years before I switched to Vb as Quickbasic PDS 7.1

I converted alot of my programs over to Vb.

Thought it was about time to try databases

As for SQL Server I do not have it and as I looked one the net I don't think will pay 1,000 for it.

Again thanks for you comments.

I don't have a screen shot or I would show you the From as I have created it

[szlamany]

Keep in mind that there are free versions of MS SQL Server - SQL Server Express (MS SQL 2005) and MSDE (MS SQL 2000)...

Free to download - free to distribute - with some cap's on numbers of users and things like that.

[Dbee]

You got any Locations of this Free ware so to speak.

I have notice that there are lots of users of SQL

By the way what is the atvantage of SQL over Access

[szlamany]

MS Download site: http://www.microsoft.com/sql/edition...s/default.mspx

Here's a discussion on ACCESS vs MS SQL server:

http://forums.microsoft.com/MSDN/Sho...37134&SiteID=1

[Dbee]

I am downloading SQl Express now. And Thanks for the Info on there Location.


But I still have about 15 programs to covert to Access Database that I created.

The reason for the conversion was corrupted Random Files had to many go south on me.

Let me ask you this is SQL more for Xp than the win2000 I run on all my machines

[szlamany]

MS SQL is for everything...

It's a true server database - it doesn't become corrupt (like ACCESS can)...

We were mainframe for 25 years until MS SQL 2000 came out - the first MS SQL product that viable for our customers to use...

Our customers are large (school district's with 1000+ teachers - potentially all connecting at the same time) and health insurance processing - tables with 3 or 4 million rows...

[Dbee]

Like I said I Do want to thank you for your time and for stearing me another way.

I will give it a look see.

Might be back with more Brain pickers so to speak on the subject.

But for Now I got to get the programs going got invoices and Statements to do.


Let me say this and I hush I have been working in Vb for about 6 years. And only scratch the surface of its power.

Still in the learning process for an old man of 63

[szlamany]

Programming is certainly a life long addiction

Good luck...

Hope to see new threads with new questions from you soon!