INSERT multiple rows using xbasic

[mikeallenbrown]

Below is an example of the code I'd use to insert one row of new data into a SQL table.

How do I insert multiple rows? ....some rows could be dirty and some will not. Basically, everything that is being displayed in my grid I want to be inserted into another SQL table.

function SomeFunctionName as c (e as p)

dim cn as sql::connection
cn.Open("::Name::SQL") 'saved connection string
dim args as sql::arguments

args.set("arg1",e._currentRowDataNew.StartTime)
args.set("arg2",e._currentRowDataNew.EndTime)
args.set("arg3",e._currentRowDataNew.StaffName)

cn.Execute("INSERT INTO SomeTableName (STARTTIME, ENDTIME, Staffname) VALUES (:arg1, :arg2, :arg3)",args)

cn.close()

[Davidk]

Re: INSERT multiple rows using xbasic

If your "SomeFunctionName" name ajax callback is set up to return 'All' rows (Row number in the Ajax Callback Action), then you can use the 'e' object V property to get all the data currently displayed.

e.V.R1.STARTTIME is the StartTime value in Row 1
e.v.R8.Staffname is the Staffname value in Row 8

Looping through that data you'd build up your SQL Insert statement to look like this...

INSERT INTO SomeTableName
  (STARTTIME, ENDTIME, Staffname)
VALUES
  (ST1, ET1, SN1),
  (ST2, ET2, SN2),
  (ST3, ET3, SN3),
  (ST4, ET4, SN4);

[mikeallenbrown]

Re: INSERT multiple rows using xbasic

If your "SomeFunctionName" name ajax callback is set up to return 'All' rows (Row number in the Ajax Callback Action), then you can use the 'e' object V property to get all the data currently displayed.

e.V.R1.STARTTIME is the StartTime value in Row 1
e.v.R8.Staffname is the Staffname value in Row 8

Looping through that data you'd build up your SQL Insert statement to look like this...

INSERT INTO SomeTableName
  (STARTTIME, ENDTIME, Staffname)
VALUES
  (ST1, ET1, SN1),
  (ST2, ET2, SN2),
  (ST3, ET3, SN3),
  (ST4, ET4, SN4);

I won't know exactly how many rows there will be ...depends on what the user does. There could be two rows or thirty. Maybe I am misinterpreting what you wrote? ...from your example above if there was a 5th row it wouldn't go over to the other table.

[Davidk]

Re: INSERT multiple rows using xbasic

You'll get the current RowCount from

e.__si.RowCount

You said you wanted to insert "everything that is being displayed in my grid" into another table.

Using the row count and e.V you get everything... including dirty field values.

If the user changes the number of displayed row (if you allow that) then e.__si.RowCount will reflect that change.

It's all very cool.

[TheSmitchell]

Re: INSERT multiple rows using xbasic

I won't know exactly how many rows there will be ...depends on what the user does. There could be two rows or thirty. Maybe I am misinterpreting what you wrote? ...from your example above if there was a 5th row it wouldn't go over to the other table.

Use a loop (FOR i = 1 TO x ... NEXT i) to generate your insert statement. Or use a loop to set your arguments and execute each insert individually. You can wrap your inserts in cn.beginTransaction() and cn.commitTransaction() (or cn.rollBackTransaction()) if you want prevent updating the db if there are any issues.
---
Sarah

[mikeallenbrown]

Re: INSERT multiple rows using xbasic

Thanks David for your help ...the user may add, delete, or alter the displayed data. When the user is finished they press a button with calls my XB function. I'd also need help with the syntax. I'm not entirely sure how to put all of this together.

function SomeFunctionName as c (e as p)

dim cn as sql::connection
cn.Open("::Name::SQL") 'saved connection string

args.set("arg1",e.__si.RowCount.StartTime)
args.set("arg2",e.__si.RowCount.EndTime)
args.set("arg3",e.__si.RowCount.StaffName)

cn.Execute (INSERT INTO SomeTableName STARTTIME, ENDTIME, Staffname)
    VALUES
                (ST1, ET1, SN1),
                (ST2, ET2, SN2),
                (ST3, ET3, SN3),
                (ST4, ET4, SN4);

cn.close()

[-Jinx-]

Re: INSERT multiple rows using xbasic

Mike,
Maybe something like the below is what you need? It will give you an idea on how to loop through the stuff in your grid. I used the primary keys which are available in the e object. I figured you could grab the primary keys and then you would be able to make one single query using "IN" (if you are using SQL) instead of multiple queries.

function TestGridData as c (e as p)

dim jscmd as c
dim keys as p = e.keys
dim ids as c
dim rw as n = convert_type(e.__si.RowCount,"N")
dim cnt as n = 1
dim nxtkey as c

while rw >= cnt
	nxtkey = "keys.R"+cnt+".[1]"	
	ids = ids + eval(nxtkey) + ","
	cnt = cnt + 1
end while

ids = rtrim(ids,",")

jscmd = "alert('"+ids+"');"

TestGridData = jscmd

end function

Of course you can format the ids any way you'd like.

For instance, changing a few lines will give you the right format for an IN

function TestGridData as c (e as p)

dim jscmd as c
dim keys as p = e.keys
dim ids as c = "("
dim rw as n = convert_type(e.__si.RowCount,"N")
dim cnt as n = 1
dim nxtkey as c

while rw >= cnt
	nxtkey = "keys.R"+cnt+".[1]"	
	ids = ids + eval(nxtkey) + ","
	cnt = cnt + 1
end while

ids = rtrim(ids,",") + ")"

jscmd = "alert('"+ids+"');"

TestGridData = jscmd

end function

So, you could do an INSERT INTO SOMETABLE FROM (SELECT Fieldnames WHERE unique id IN ids)

[Davidk]

Re: INSERT multiple rows using xbasic

Here's one way... multiple INSERT statements.

dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments

flag = cn.open("::Name::Test")

if flag then

	dim currRowCount as n = val(e.__si.RowCount)
	for i = 1 to currRowCount
		args.set("FN",eval("e.V.R" + i + ".FirstName"))
		args.set("LN",eval("e.V.R" + i + ".LastName"))
		args.set("CO",eval("e.V.R" + i + ".Company"))
		sqlStmt = "INSERT INTO tblCustomers_copy (FIRSTNAME, LASTNAME, COMPANY) VALUES (:FN, :LN, :CO);"

		flag = cn.Execute(sqlStmt,args)
		if flag then
			'ALL OK
		else
			'there was an error - close the connection and exit
			cn.Close()
			dim msg as c 
			msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
			msg = js_escape(msg)
			dim jscmd as c 
			jscmd = "alert('" + msg + "');"
			gridAllRows = jscmd
			exit function
		end if  
	next i
	
else
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	gridAllRows = jscmd
	exit function
end if

'cn.FreeResult()
cn.close()

or, a single INSERT statement with multiple values... and as a bonus you get to use the mergeDataIntoTemplate function... which is always a hoot.
What you need to watch out for here is missing numeric values in your data... not sure about dates as well.
You won't be putting quotes around numeric values... so you'd have to test the e.V row value and if there's nothing there put in a NULL or something appropriate to your field in the table.

template = <<%html%
({ds.data("firstname")}, {ds.data("lastname")}, {ds.data("company")})
%html%


dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments

flag = cn.open("::Name::Test")

if flag then

	dim currRowCount as n = val(e.__si.RowCount)
	dim insertValues as c = ""
	dim dataSource[0] as p

	for i = 1 to currRowCount
	
		dataSource[].firstname = "'" + eval("e.V.R" + i + ".FirstName") + "'"
		dataSource[..].lastname = "'" + eval("e.V.R" + i + ".LastName") + "'"
		dataSource[..].company = "'" + eval("e.V.R" + i + ".Company") + "'"
	
	Next i

	insertValues = a5_mergeDataIntoTemplate(template,dataSource)
	
	insertValues = crlf_to_comma(insertValues)
	
	sqlStmt = "INSERT INTO tblCustomers_copy (FIRSTNAME, LASTNAME, COMPANY) VALUES " + insertValues
	flag = cn.Execute(sqlStmt,args)
	if flag then
		'ALL OK
	else
		'there was an error - close the connection and exit
		cn.Close()
		dim msg as c 
		msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
		msg = js_escape(msg)
		dim jscmd as c 
		jscmd = "alert('" + msg + "');"
		gridAllRows = jscmd
		exit function
	end if  
	
else
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	gridAllRows = jscmd
	exit function
end if

'cn.FreeResult()
cn.close()

[GGandhi]

Re: INSERT multiple rows using xbasic

may be I shouldn't ask any question. but..
how is the grid populated, how the rows copy function is called?
why would there be different number of rows in different scenario?
is the grid better suited for this or dialog?
just curious.

[mikeallenbrown]

Re: INSERT multiple rows using xbasic

David,

I'm just now getting back to this. I'm using your code from above for multiple inserts, however, I'm getting an error. Not sure what the problem is. "ClientID" is in the grid...

Function WriteIDT_MPC as c (e as p)

dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments

flag = cn.open("::Name::SQL")

if flag then

	dim currRowCount as n = val(e.__si.RowCount)
	for i = 1 to currRowCount
		args.set("A",eval("e.V.R" + i + ".ClientID"))
		args.set("B",eval("e.V.R" + i + ".MRNum"))
		args.set("C",eval("e.V.R" + i + ".PatientName"))
		args.set("D",eval("e.V.R" + i + ".DateTimeFinalized"))
		args.set("E",eval("e.V.R" + i + ".FinalizedBy"))
		args.set("F",eval("e.V.R" + i + ".IDT_Meeting_Date"))
		args.set("G",eval("e.V.R" + i + ".Category"))
		args.set("H",eval("e.V.R" + i + ".StartDate"))
		args.set("I",eval("e.V.R" + i + ".Medication"))
		args.set("J",eval("e.V.R" + i + ".InjectPumps"))
		args.set("K",eval("e.V.R" + i + ".Dose"))
		args.set("L",eval("e.V.R" + i + ".Route"))
		args.set("M",eval("e.V.R" + i + ".Frequency"))
		args.set("N",eval("e.V.R" + i + ".Indication"))
		args.set("O",eval("e.V.R" + i + ".DC_Date"))
		args.set("P",eval("e.V.R" + i + ".C_NC"))
		args.set("Q",eval("e.V.R" + i + ".High_Alert"))
		
		sqlStmt = "INSERT INTO IDT_POC_MPC (ClientID, MRNum, PatientName, DateTimeFinalized, FinalizedBy, IDT_Meeting_Date, Category, StartDate, Medication, InjectPumps, Dose, Route, Frequency, Indication, DC_Date, C_NC, High_Alert) VALUES (:A, :B, :C, :D, :E, :F, :G, :H, :I, :J, :K, :L, :M, :N, :O, :P, :Q);"
		

		flag = cn.Execute(sqlStmt,args)
		if flag then
			'ALL OK
		else
			'there was an error - close the connection and exit
			cn.Close()
			dim msg as c 
			msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
			msg = js_escape(msg)
			dim jscmd as c 
			jscmd = "alert('" + msg + "');"
			gridAllRows = jscmd
			exit function
		end if  
	next i
	
else
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	gridAllRows = jscmd
	exit function
end if

'cn.FreeResult()
cn.close()	

end function

[-Jinx-]

Re: INSERT multiple rows using xbasic

if you put in a debug, can you find it in the "e" object? If so, it's probably the format of the eval statement, which can be fruity sometimes I've found.
You can also switch the first and second args.set lines and see if the error switches to "MRNum".