how to use xbasic to perform CRUD operations

[eduduru]

Hi everyone!

I have an UX component form which i want to use xbasic to perform insertion and deletion on a different tables. Below is the code i have written so far, and i have a button in the form which i use Javascript function on Ajax call back to carryout the operation.



function ToSellStock as c (e as p)

dim Quantity as n
dim output as c =""
dim conn as sql::Connection
dim args as sql::arguments
dim sqlInsert as c
dim sqlInsert1 as c
dim sqlUpdate as c
dim rs as sql::ResultSet
conn.Open("::name::Colink")


dim clientID as n=convert_type(e._currentRowDataNew.ClientID,"N")
'output=output+"alert('"+clientno+"');"

if e.datasubmitted.sellStockTypeId="" then
output=output+"alert('"+"please select a stock to sell"+"');"

else if e.datasubmitted.sellQuantity=""
output=output+"alert('"+"please select quantity to be sold"+"');"

else if e.datasubmitted.sellQuantity>e.datasubmitted.testQty
output=output+"alert('"+"Stock quantity over range"+"');"

else if e.datasubmitted.sellPrice=""
output=output+"alert('"+"Stock price empty"+"');"

else if e.datasubmitted.sellCommission=""
output=output+"alert('"+"Commission field can not be empty"+"');"

else if e.datasubmitted.sellRemark=""
output=output+"alert('"+"Remark field is empty"+"');"

else if e.datasubmitted.sellCreditId=""
output=output+"alert('"+"Credit field is empty"+"');"

else

dim sql as c="select Quantity from dbo.StockValuationSheets where ClientID="+e.datasubmitted.ClientID+" AND StockValuationSheetID="+e.datasubmitted.sellStockValuation+" AND StockTypeID="+e.datasubmitted.sellStockTypeId+""
if .not. conn.Execute(sql)then
output=output+"alert('"+"error"+"');"
conn.Close()
end
end if

rs=conn.ResultSet
Quantity =convert_type(rs.Data("Quantity"),"N")
dim testQtyy as n=convert_type(e.datasubmitted.testQty,"N")
if Quantity<=testQtyy then

sqlInsert = "INSERT INTO dbo.ClientAccounts(ClientID,UserID,Credit,Reference,Description,StockValuationSheetID,CreditDefinitionID,SystemDate)VALUES("+e.datasubmitted.ClientID+", '"+e.datasubmitted.sellUserid+"',"+(e.datasubmitted.sellTotalCost+e.datasubmitted.sellCommission)+","+e.datasubmitted.sellReference+","+e.datasubmitted.sellDetails+",+"+e.datasubmitted.sellStockValuation+","+e.datasubmitted.sellCreditId+",'"+e.datasubmitted.sellDateBought+"')"
sqlUpdate = "UPDATE dbo.StockValuationSheets SET Quantity=Quantity-"+e.datasubmitted.sellQuantity+",CreditDefinitionID="+e.datasubmitted.sellCreditId+",TotalCost="+e.datasubmitted.sellTotalCost+" where StockValuationSheetID="+e.datasubmitted.sellStockValuation+" AND ClientID ="+datasubmitted.ClientID+" "

if .not. conn.Execute(sqlInsert)then

output=output+"alert('"+"Client account have not been credited"+"');"
'output="alert('"+conn.CallResult.Text+"');"
'conn.Close()
else
if .not. conn.Execute(sqlUpdate)then

output=output+"alert('"+"Error salling stock"+"');"
'output="alert('"+conn.CallResult.Text+"');"
'conn.Close()
else
output=output+"alert('"+"Stock sold to Client"+"');"
output=output+"{dialog.object}.runAction('NewSell');"

end if

end if
end if


end if
conn.Close()
SellStock=output
end function


I need someone to current me where i get it wrong.

Thanks for your help in advance.

[frankbicknell]

Re: how to use xbasic to perform CRUD operations

First of all I would test for the empty values at the control level with validation (validation in the controls properties) so that the user cannot get this far without all of the fields properly filled.

The only issue I see is that you don't need any database designation since you have created a connection to the database in the connection statements. I would get rid of all of the dbo. as in ,dbo.StockValuationSheets, dbo.ClientAccounts.

I am not sure if anything is wrong with your if then else statements where you are inserting and updating.

[Davidk]

Re: how to use xbasic to perform CRUD operations

There are a few things you need to do.

You should always test your open connection.
Instead of using .not. to test your connection and sql calls, dim a flag as L and use that...

dim flag as l
flag = conn.Open("::name::Colink")
if flag = .f. then
   'we have a problem
   'send back a message
   end
end if

Then you can use the same flag throughout the rest of the calls that need testing.

Next, use arguments in your SQL statements. Concatenating is a mess. You already dim it, but you don't use it.

args.set("clientId",e.datasubmitted.ClientID)
args.set("stockVal",e.datasubmitted.sellStockValuation)
etc.

dim sql as c="SELECT Quantity from dbo.StockValuationSheets WHERE ClientID=:clientId AND StockValuationSheetID=:stockVal"

or

dim sql as c
sql = <<%str%
SELECT Quantity from dbo.StockValuationSheets
WHERE ClientID=:clientId AND StockValuationSheetID=:stockVal"
%str%

Either way is much more readable.

Finally, use the best friend you have in XBasic. Put a debug(1) statement at the top of your code... throughout your code... where ever... run your UX in LivePreview within Alpha and just step through your code. You'll soon see your errors.