Keeping a Form with drop/add to dbf

[steve745]

I'm trying to pull sql data to a temp table that will be overwritten everytime the sql query is ran. I want to make a form to work the table which i'm thinking that i have to design it in the script , when i design the form within the script it opens unsaved and in design mode. Here is the sql code i'm starting with

DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("::Name::*****.net")
if flagResult = .f. then 
	ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
	end 
end if

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .t. 
dim sqlStatement as c 
sqlStatement = <<%sql%
SELECT FIRST 1 custid, custfullname, fname, lname, address, city, state, zip, Dispo, findone, repid, called, cid, erollapdone, phone, notes_custid, callnote, payinfo, recentmtgamt, loanorigdate, recentlender, recentloantype, loantoval, age, creditrating  FROM custinfo  WHERE (phone IS NOT NULL ) AND (called IS NULL )
%sql%
'Execute the Query
flagResult = cn.Execute(sqlStatement)
if flagResult = .f. then 
	ui_msg_box("Error",cn.CallResult.text)
	cn.close()
	end 
end if 
dim rs as sql::resultset
rs = cn.ResultSet 

resultP = SQL_QueryImport(cn,sqlStatement,"C:\ahtdialer\tst.DBF")
'Execute a query on a SQL database
'DIM a connection variable

'Now, dump the contents of the ResultSet to a variable
dim qpoint as c 
qpoint = rs.ToString()

'Now, close the connection 
cn.close()

i've tried to design the form and then run the script but it deletes the form when the table is overwritten

[Stan Mathews]

Re: Keeping a Form with drop/add to dbf

You'll probably have to have a destination table upon which the form is built and empty the table each time you run the script. You would then append from the temp table to the "real" table.

[steve745]

Re: Keeping a Form with drop/add to dbf

You'll probably have to have a destination table upon which the form is built and empty the table each time you run the script. You would then append from the temp table to the "real" table.

I was thinking that would have to be the way. trying to cut query time.

[steve745]

Re: Keeping a Form with drop/add to dbf

Ok so i have managed to send the results to dbf and then append them to another static table and now i can't seem to figure out how to run an sql update record based on a var.
when i read the xbasic not sure how to set the txt string. Here is what i have.

'Get 'Value' property of 'Custid' in Form 'TST2' .
DIM SHARED CUST AS c
CUST = parentform:Custid.value
'Update a record(s) in a remote SQL database.
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l 
flagResult = cn.open("::Name::stevengreer.net")
if flagResult = .f. then 
	ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
	end 
end if

'Specify that we are using Portable SQL syntax
cn.PortableSQLEnabled = .t.

'Dim a SQL arguments object, create arguments and set their values
DIM args as sql::arguments

if a5_eval_valid_expression("=Var->user",local_variables()) then 
	args.add("New__called",convert_type(a5_eval_expression("=Var->user",local_variables()),"C"))
end If


dim sqlUpdateStatement as c 
sqlUpdateStatement = [COLOR="Red"]<<%txt%
UPDATE custinfo SET called = :New__called  WHERE custid = 'cust'
%txt%[/COLOR]

dim flag as l 
flag = cn.Execute(sqlUpdateStatement,args)
if flag = .f. then 
	ui_msg_box("Error","Record was not updated. Error reported was: " + crlf(2) + cn.CallResult.text,UI_STOP_SYMBOL)
else
	if cn.AffectedRows() > 0 then 
		ui_msg_box("Notice", "" + cn.AffectedRows() + " record(s) updated.",UI_INFORMATION_SYMBOL)
	else
		ui_msg_box("Error","Record was not updated." ,UI_STOP_SYMBOL)
	end if 
end if 

'Now, close the connection 
cn.close()

[Tim Kiebert]

Re: Keeping a Form with drop/add to dbf

Steve, you may have better exposure for your new question if you start a new thread. It also helps others later to find threads relevant to the issue.

Re your original question.

You can also try creating a set and only include the one table. Sounds a bit useless to only have one table in a set because it won't be set of anything.

But the forms and reports etc will be stored in the .se* files of the set instead of the .dd*. When the table gets completely replaced then the set will still be pointing to that table and its data.

[steve745]

Re: Keeping a Form with drop/add to dbf

Thanks Tim that sounds
like a simple clean way to get past form pointers
I'll check it out and see if it works
for
me

[CALocklin]

Re: Keeping a Form with drop/add to dbf

...You can also try creating a set and only include the one table. Sounds a bit useless to only have one table in a set because it won't be set of anything.

But the forms and reports etc will be stored in the .se* files of the set instead of the .dd*. When the table gets completely replaced then the set will still be pointing to that table and its data.

Bravo! Great idea!

So simple. So obvious once you think about it. Yet not something most people (including me) would think of right away.

[MikeC]

Re: Keeping a Form with drop/add to dbf

Doing that with QReportBuilder (QRB) and QuickBooks is something that is almost a necessity as Passive linked tables are being used. In fact this is what Alpha did when creating QRB---any report data brought over from QuickBooks is placed into a new table that is actually as Tim states...a set of one table.

Another way of course is to duplicate the table, not having it Passive or Active linked, and then append to it from the tables that are recreated as such when new data is brought across.

[Tim Kiebert]

Re: Keeping a Form with drop/add to dbf

Bravo! Great idea!

So simple. So obvious once you think about it. Yet not something most people (including me) would think of right away.

Yeh, would love to take credit for that great idea. But alas, it did not come from I. Not sure where I first saw it. I think it was one of the Steves from which I gleaned this little gem.

Cal,
I love you smilies.