Xbasic problem with SQL statements (could be a Alpha five defect)

[MRall]

In the begining of the function I have opened a SQL table with no problems.

dim conn as sql::connection
	dim sqlCommand as c
	dim args as SQL::Arguments
	dim rs as SQL::ResultSet
	dim ct as n
	ct=0
	args.add("whattrans",33)
	flag1 =conn.open("::name::Inventory")
	sqlCommand = "SELECT Transactions.Transaction_no, Transactions.Site, Transactions.SubSite, TransactionParts.Location, TransactionParts.Part_number, TransactionParts.Condition, TransactionParts.AutoInc, TransactionParts.Quantity, Transactions.UserName, Transactions.TransDate, TransactionParts.Processed FROM Transactions Transactions INNER JOIN TransactionParts TransactionParts ON  Transactions.Transaction_no = TransactionParts.Transaction_no WHERE Transactions.Transaction_no = :whattrans"
	flag2 = conn.execute(sqlCommand,args)
	rs= conn.ResultSet
	recexist=rs.NextRow()

I have read one line at a time into arrays no problem.
I later open another sql table to update and or insert the values from the arrays into the table.

sqlCommand = "SELECT Site, SubSite, Location, Part_ID, Condition, Qty, UserName, Updated FROM NInventory WHERE Site = :site AND SubSite = :subsite AND Part_ID = :partno"
			flag4 = conn.execute(sqlCommand,args)
			sql_ResultSet_preview(rs)

If I view the results, the result always show tha fields from the orginal SQL table. I have tried testing this in the interactive window by running just the sql code from the second table even after closing the function and I still get the fields form the first table. Yet, I am able to do an insert into the second table. Is this a defect of Alpha 5?

[Pat Bremkamp]

Re: Xbasic problem with SQL statements (could be a Alpha five defect)

You are obviously not showing some of your code, but one piece I don't see in what you have done is where you reset rs as the result set of the second select statement. So it should be
flag4 = conn.execute(sqlCommand,args)
rs=conn.ResultSet
sql_ResultSet_preview(rs)
Otherwise, you are viewing the result set from the first execute, not the second.

[MRall]

Re: Xbasic problem with SQL statements (could be a Alpha five defect)

That seemed to fix the problem. (can't believe one missing line)

I noticed that there doesn't seem to be any examples of how to process records from one SQL table to another using a function. So I would like to post this example of this.

(microsoft has a Tek tips and tricks forum for most of their products showing example code of how to do lots of different things. I have found this to be very helpful and time saving) The examples have come from Q&A on a forum just like this. (anytime a good answer with an example is found to do anything it gets tested, posted, and catagorised)

This will save on repeat questions.

Thanks

[DaveM]

Re: Xbasic problem with SQL statements (could be a Alpha five defect)

Down at the bottom of the forum, there is a place for such things.