Alpha Video Training
Results 1 to 10 of 10

Thread: How do I query and update SQL records?

  1. #1
    Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Versailles, KY
    Posts
    1,436

    Default How do I query and update SQL records?

    Even after reading this page
    http://www.downloads.alphasoftware.c...SQLTables.html
    and watching this excellent videos associated with the page,
    I am still not sure how to perform a query on an SQL table and update the selected records.
    In this case, since deliveries may be deleted or changed, the purpose of this script is to re-number the records based on the previous order.

    In other words, if I have four deliveries with the customer last name, and the delivery order:

    Workings - 1
    Rochford - 2
    Wayne - 3
    Wood -4

    and have to postpone the Wayne delivery, I want the records to then look like this:
    Workings - 1
    Rochford - 2
    Wood -3


    In Xbasic on the DBF side, it is done like this:

    t = table.open("customer")
    query.description = "all customers for one trip"
    query.order = "delivorder"
    query.options = "T"
    query.filter = "trip_no = '"+vc_trip_no+"'"
    indx = t.query_create()
    t.fetch_first()
    vn_del_number = 0
    for i = 1 to indx.records_get()
    vn_del_number = vn_del_number +1
    t.change_begin()
    t.delivorder = vn_del_number
    t.change_end()
    t.fetch_next()
    next i
    I suspect the SQL select statement would be something like:

    SELECT * FROM CUSTOMER WHERE TRIP_NO = 100
    I also understand connection strings. I just don't know how to fetch through SQL records and make the updates.

    Thank you for your time.
    Jay
    Jay Talbott
    Lexington, KY


  2. #2
    "Certified" Alphaholic
    Real Name
    Lee Vasic
    Join Date
    May 2010
    Location
    Salt Lake City
    Posts
    1,188

    Default Re: How do I query and update SQL records?

    You can run this from a stored procedure...

    /* Update flxid */
    Declare @NextFlxid Int
    Set @NextFlxid = 0
    Update #staff set @NextFlxid = OrderNum = @NextFlxid +1
    Last edited by lvasic; 08-17-2011 at 05:05 PM.

  3. #3
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,577

    Default Re: How do I query and update SQL records?

    Have a look here at video #85

    http://news.alphasoftware.com/v10preview/videos.htm

    Also have a look through this thread

    http://msgboard.alphasoftware.com/al...-payments-made

    It's recent, and swipes most of the code from Selwyn's video, but there are lots of SQL examples in this forum.

  4. #4
    Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Versailles, KY
    Posts
    1,436

    Default Re: How do I query and update SQL records?

    Lee,
    I delete the record from the table using the delete function of the grid.

    David,
    Thanks for the video referrals. I understand that I need an Ajax callback, and I know how to create one, but after watching the 7 videos, I am still not sure how to fetch through a set of records. I do see that a result set is read only, so that won't work, unless I can somehow use an SQL update statement while rolling forward through the result set.

    Jay
    Last edited by Jay Talbott; 08-17-2011 at 05:29 PM.
    Jay Talbott
    Lexington, KY


  5. #5
    "Certified" Alphaholic
    Real Name
    Lee Vasic
    Join Date
    May 2010
    Location
    Salt Lake City
    Posts
    1,188

    Default Re: How do I query and update SQL records?

    Have you tried this...
    You can run this from a stored procedure...

    /* Update flxid */
    Declare @NextFlxid Int
    Set @NextFlxid = 0
    Update #staff set @NextFlxid = OrderNum = @NextFlxid +1

  6. #6
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,577

    Default Re: How do I query and update SQL records?

    All this really depends on what you need to do and how you need to do it... that sounds kinda silly, but it's true.

    Out on a limb, a bit, but I'd say you probably don't need to perform a Select. You need to perform and Update on fields with a Where clause. As you showed in your first post, with Xbasic you need to grab a set and update within that set. With SQL you don't need the set. You already know what you need to update, so run an Update SQL statement on the specific record or set of records that is defined by a where clause.

    If you want to iterate through a recordset you could

    while rs.nextrow()
    'do something
    end while

    And, have a look here for lots of other resultset stuff.

    http://wiki.alphasoftware.com/SQL+ResultSet+Object
    Last edited by Davidk; 08-17-2011 at 07:38 PM.

  7. #7
    Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Versailles, KY
    Posts
    1,436

    Default Re: How do I query and update SQL records?

    Thanks, Lee. I will have to look into this. Learning HTML, Javascript, CSS and SQL is going to be a full plate of alphabet soup.

    David, I understand this, I believe. I will try and fool around with this.
    Thanks.
    Jay
    Jay Talbott
    Lexington, KY


  8. #8
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,595

    Default Re: How do I query and update SQL records?

    Jay, Looks like you are working on something like a food delivery system. Are you using MySQL? If so, I've got a process to handle adding new orders, canceled orders, moving an order up one position or down one position or setting the position to any given number and renumbering everything around it. It uses callbacks and stored procedures.

    If you want to see how I do it (not in the food business, by the way) then I can show you with GoToMeeting. Send me an email.
    Pat Bremkamp
    MindKicks Consulting

  9. #9
    "Certified" Alphaholic
    Real Name
    Lee Vasic
    Join Date
    May 2010
    Location
    Salt Lake City
    Posts
    1,188

    Default Re: How do I query and update SQL records?

    Jay inspired me to create the practical. This is a short video of the stored procedure that handles the SQL update.

    http://www.screencast.com/t/AFXQoI8rz

  10. #10
    Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Versailles, KY
    Posts
    1,436

    Default Re: How do I query and update SQL records?

    Lee,
    Thanks for the video.
    Since I am just dipping my toe into the Store Procedures pond, I did what I needed to do with javascript.
    Here is the code I used:
    var i = 1;
    do
    {
    {grid.Object}.setValue('G','DELIVORDER',i,i);
    i++;
    }
    while ({grid.Object}.getValue('G','LAST_NAME',i) != '');
    {grid.Object}.submitGridPart();
    //{grid.Object}.refresh();
    While it is currently on a button, it could be kicked off from an event.
    The only problem with my method is that it does not affect records that may be on page two or subsequent pages in a grid.
    I'll have to get more familiar with stored procedures.
    Thanks again.
    Jay
    Jay Talbott
    Lexington, KY


Similar Threads

  1. SQL Query records for yesterdays date
    By jwb in forum Application Server Version 10 - Web/Browser Applications
    Replies: 8
    Last Post: 08-17-2018, 04:04 PM
  2. Edit Grid SQL Query without Query Builder
    By christappan in forum Archived Wishlist
    Replies: 0
    Last Post: 03-15-2011, 11:58 AM
  3. no records for SQL query
    By christappan in forum Application Server Version 10 - Web/Browser Applications
    Replies: 4
    Last Post: 08-31-2010, 01:41 PM
  4. Query multible records the query against sql
    By steve745 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 1
    Last Post: 05-10-2010, 06:42 AM
  5. Update Query for Groups of Records
    By CDeCinko in forum Alpha Five Version 8
    Replies: 23
    Last Post: 05-01-2008, 09:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •