Newbie non-programmer needs help with SqlInsert for all checked records

[ibliving]

Hi everyone,

I have searched for a solution to what seems to be a simple task I would like to perform and have found similar items but am having trouble understanding. I even tried to copy the code used in one of the V10 videos for doing this for a single record but got errors I didn't understand. Although I am fairly technical and have used Access extensively many years ago and written a little VB code, I don't know Javascript or SQL and just cannot get this to work.

I have 2 tables displayed as linked via a field called Product_ID
PRODUCTS which contains the entire inventory
SPECIALS which is initially empty

I want to allow the user to select several records in the PRODUCTS grid using the checkbox column (got that working) and click a button in the Parent Grid Toolbar called "AddSpecials" that will add a new record to SPECIALS for each checked item.

Data fields to be copied to the Specials table for each checked record are: Product_ID, Brand, Product (and then the user will fill in the sale price and other details in the Specials grid) I would also like to display a message after each "add" indicating the number of items that were added (or an error message if none were added).

Can anyone give me some sample code I can use on the Action button to make this happen? I would be eternally grateful. So far I am loving Alpha5 and I think this is the most complicated thing I am going to need to do.

thanks in advance

[lvasic]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

Let me see if I understand. So the button is in the products grid and when you click it, it will insert those items into the special table? That is very doable.

[omagarc]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

http://msgboard.alphasoftware.com/al...8-Multi-select
You will do what is called an Ajax call back. You will place this code in the button to run the code to process all checked boxes.

[GGandhi]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

hello

i am curious and would like to know.
why you can't you keep all the data in the product table itself and show the specials only when needed, this will help to minimize work of writing code and use the sale price instead of the regular price on certain conditions. for example
the product table can have
product_id
name
description
price
...other fields you want
start_date
end_date
sale_price
with this you can have a check box to add sale price and show the same table when needed with records that contain sale_price. is that acceptable to you?
or if you want to have separate sale_items table then you will have to write code to add records to that table. in that case, video #91 on version 10 will show you how to add data to another table. if you need specifics i can show you some code i wrote.

[ibliving]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

hello

i am curious and would like to know.
why you can't you keep all the data in the product table itself and show the specials only when needed, this will help to minimize work of writing code and use the sale price instead of the regular price on certain conditions. for example
the product table can have
product_id
name
description
price
...other fields you want
start_date
end_date
sale_price
with this you can have a check box to add sale price and show the same table when needed with records that contain sale_price. is that acceptable to you?
or if you want to have separate sale_items table then you will have to write code to add records to that table. in that case, video #91 on version 10 will show you how to add data to another table. if you need specifics i can show you some code i wrote.

Thanks SO MUCH for responding! The Sale table will be purged and repopulated weekly and a single item in the PRODUCTS table may appear several times in the SALE table with different prices at different retail outlets. I already tried emulating what was in the Ajax callback in Video 91 but I clearly made some mistakes interpreting what was being referenced in some of the code. So, if someone could give me the code using my own references, then that would be WONDERFUL! I am using tables I created in an Access database. Here is some more detail.

ConnectionName = CurrentSpecials

PRODUCTS TABLE fields in PRODUCTS grid (only need to send data from a couple of these)
Prod_ID
Prod_type
Category1
Category2
Brand
Product

Fields in the SPECIALS table (and grid) that need to be populated by the checked items in the PRODUCTS grid
Prod_ID
Brand
Product
There are other fields in the SPECIALS grid for Beg Date, End Date, Sale Price, etc that the user will then manually complete for the applicable week.

Thanks again for being willing to help!

[GGandhi]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

okay now i understand why you need separate sales table
disclaimer: i use mysql not access but this should work with access too, i can modify if you need some change since i have access to access(pardon the pun)
if you use checkbox you can add once ( i think) but if you add a button to call ajax function to add as many times the items you want and then you can review the sales grid and set various prices.
step 1> create a grid displaying product_id, brand,product and what ever you need to display. then at the bottom there is something like insert use that hyperlink and insert a button to the grid name that button "add to sales". and on the on_click event define an ajax call back function addSales and create the prototype and then click on the xbasic the function will be there. add the following code (not the name - it should be there already)
see if that works.

looks like i have told you too much - i am sure you know most of this except for the code. but i want to sync with you so i know when you say something about the code i know what you mean.

function addSales as c (e as p)
	'debug(1)
	dim prod_id as n
	dim brand as c
	dim product as c
	
	prod_id = e._currentRowDataNew.prod_id
	brand = e._currentRowDataNew.brand
	product = e._currentRowDataNew.product
		
	dim cn as sql::connection
	dim result as l
	result = cn.open("::Name::CurrentSpecials")
	if result = .f. then
	    addSales = "alert('Could not connect to database!');"
	    exit function
	end if
	
	'else
	dim sqlInsert as c
	sqlInsert = <<%sql%
	INSERT INTO specials(prod_id,brand,product)
	VALUES(:newID,:newBRAND,:newPRODUCT)
	%sql%
	dim args as sql::arguments
	args.add("newID",prod_id)
	args.add("newBRAND",brand)
	args.add("newPRODUCT",product)
	
	result = cn.Execute(sqlInsert,args)
	if result = .f. then
	    addSales = "alert('Record could not be inserted!');"
	    cn.close()
	    exit function
	end if
	addSales = "alert('Record successfully inserted!');"
	cn.Close()
	exit function

then create another grid for the sales table editable and add the price dates etc.,

[ibliving]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

Thank you. This looks like exactly what I need. Yes I have everything set up and ready to go, just needed the code behind the action button. I will try this and get back to you. THANK YOU AGAIN!!

[ibliving]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

Thank you - Govindan. That worked and helped me understand what was going on, EXCEPT for 2 things. I am using the checkbox column on the PRODUCTS grid and need to add all the records that are checked to the SPECIALS table/grid. The code you provided only adds the record that is the "current" record as indicated by the row selector. Also, the PRODUCT grid is at the top of the page while the SPECIALS grid is below and I need to refresh the SPECIALS grid as soon as the new records are added but retain the view of the checked records in the PRODUCTS grid (the ones just added to the SPECIALS grid).

Once again, I have searched the documentation and videos and although I found some similar solutions, and this seems like it should be fairly simple, when I try and implement it in my situation, it doesn't work.

Help? Just a dum 'ol Southern girl here tryin to solve a problem. :)

Thanks!!

[ibliving]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

Hi again - hoping for a little more help here.
Thanks to Govindan (used his sample code) I have everything working except I still can't figure out how to add all checked rows to the SPECIALS table instead of just the current row. I looked at videos 87 and 88 as well as all other videos referencing doing something with multiple checked rows - AND I have searched and read forum posts until my eyes are about to bleed <grin> but all the examples seem to use 2 related grids or are selecting from the 2nd grid to add to the first or something that makes it just enough different from what I'm trying to do that I don't understand what I'm reading.

Remember, I'm a non-programmer with a little VB experience - virtually NO SQL or Javascript knowledge - just what I've learned in the past week since I bought Alpha 5.

After all that research and trying a few things it appears the data I need to use in my SQL Insert to add the checked records into the SPECIALS table is in e.CheckBoxFilterArgumentsXML and e.CheckBoxFilterArgumentsEncoded but I have no idea how to construct the code to use those values in the Insert. Note - I don't want to apply the filter to the PRODUCTS grid and then add those records to the SPECIALS table, I simply want to add all items that are checked in the PRODUCTS table to the SPECIALS table.

Any help would be extremely appreciated. I have been researching for 2 days.

[omagarc]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

function ProcessSelected as C (e as P)

   dim js as C  
   dim i as N
   dim selectedCount as N
	
   dim lastname as C
   dim firstname as C

   'Get number of selected records
   selectedCount = e.checkboxRows.countChecked

   'Process key field values of selected rows
   for i=1 to selectedCount
      'Assuming that lastname is the first part of primary key 
      lastname = eval("e.checkedRows.key" + i + "[1]")
      'Assuming that firstname is the second part of primary key 
      firstname = eval("e.checkedRows.key" + i + "[2]")
     
      'do something with the key values, i.e. add them to a
      'transaction table, retrieve other fields etc. using
      'regular xbasic code/SQL etc.
      '...

   next

   'Generate javascript code or just empty string	
   js="alert('Selected Records Processed');"

   ' Return javascript code to be executed after callback (optional)
   ProcessSelected  = js

end function

Sample by wolfgang. This was included in the link provided in my post.

[lvasic]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

This will copy the records over:

function MoveCheckedRows as c (e as p)

Dim cn as sql::connection
Dim rs as sql::resultset
Dim args as sql::arguments
Dim count as n
dim i as n
dim txt as c
dim txt2 as c
dim Sql as c
dim jscmd as c
dim vGUID as c

args.xml = e.checkboxFilterArgumentXML
count = args.count

flag = a5SQLConnectionOpen(cn,"::Name::DynamicConnection_AgencyConnection",session)

for i= 1 to count
args.add("UserID",vGUID)
args.add("GroupID",args[i].data)
args.add("UpdWeb",Now())

sql = "Insert into webMembers(Group_GUID, User_Guid,UpdWeb) Values(:GroupID,:UserID,:UpdWeb)"
flag = cn.Execute(Sql,args)
If flag = .t. then
e.flagError = .f.
e.errorText = ""
e.rowsAffected = cn.affectedRows()
e.SQLToDisplay = sql
e.ArgsToDisplay = ""
else
e.flagError = .t.
e.errorText = cn.callresult.text
e.rowsAffected = 0
jscmd = jscmd + "alert('There was a problem: " + js_escape(e.rowsAffected) + "');"
MoveCheckedRows = jscmd
end if
Next i
cn.close()
jscmd = jscmd + "alert('Security Groups have been updated.');"
MoveCheckedRows = jscmd
end function

[ibliving]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

Thanks OMAR!!! Now it totally works. And I did look at this when you first replied but since I didn't know how to do the Insert at the point where the sample code says, "do something with the key values" at the time, I was lost. However, after implementing what Govindan gave me and understanding the Insert syntax, now when I looked at this again it made a lot more sense. YAY!!

You guys are great and so nice to be willing to help those of us struggling with the learning curve!!

[omagarc]

Re: Newbie non-programmer needs help with SqlInsert for all checked records

Glad to hear you got it working! You'll find the forum to be very helpful. The code sample is that of a member "wolfgang" so he deserves the credit!