How to use setSQLText? 1

[jmccolley]

The following sql query works to insert a row in my table when run from the Query Analyser.

insert SubmittalPackagesItems (pkgcode, projidjobnumber, pkgitem, pkgdesc, pkgaction, pkgnotes, pkgclosed, pkggennotes)
select pkgcode + 1, projidjobnumber, pkgitem, pkgdesc, pkgaction, pkgnotes, pkgclosed, pkggennotes
from SubmittalPackagesItems
where projidjobnumber = 2018 and pkgclosed = "" and pkgcode = 3011100

How can I get this same query to work using setSQLText?

Thanks.

 

[rtshort]

I'm not sure what you are talking about really. If it's what I think you mean, you can put it in a on_click event, or pass it to another asp page.

You'll have to set the connection object like this:

Set cn = Server.CreateObject("ADODB.Connection&quot
set rs = Server.CreateObject("ADODB.Recordset&quot
cn.ConnectionString = "Driver=SQL Server;uid=UserID;Pwd=Password;Server=ServerName;Database=DBName
rs.Open cn, sql, 3, 3
sql = Your Statement above
cn.Close
Set cn = Nothing
rs.Close
Set rs = Nothing

Hope that helps ya. Rob
Just my $.02.

 

[jmccolley]

Sorry. I need to be more specific. When I try to use this string I get an error relating to syntax. Can anyone give me some tips on proper syntax and where to use the setSQLText command?

 

[MerlinB]

setSQLText is used by the RecordsetDTC - which is only really designed to cope with SQL that returns a recordset.

Your best bet is to use the
thisPage.createDE
command to create a Data Environment variable.
(you must add a PageObjectDTC on the page first).
If you can figure out the help text on this, it can save a fair amount of typing.

You can also place your insert as a command of the Data Environment - right click a connection in the Global.asa to add a command. Place parameters as question marks (?).

If you add the command 'insMyInsertCommand', with 3 parameters...
Then in your page, do

thisPage.createDE
iResult = DE.insMyInsertCommand iJobNo, bClose, iPkgCode

There are a couple of ways to obtain the result code or return parameters. You can inspect the parameters collection after calling the command to get any return information. But try to find this info in the online help, as I can't quite remember.

http://www.corporateinternet.com

 

[jmccolley]

Hi Merlin! Thanks for your help. I "almost" have it working!
I have set up a DE command named InsertRevisionItems that uses the following sql:

insert SubmittalPackagesItems (pkgcode, projidjobnumber, pkgitem, pkgdesc, pkgaction, pkgnotes, pkgclosed, pkggennotes)
select pkgcode + 1, projidjobnumber, pkgitem, pkgdesc, pkgaction, pkgnotes, pkgclosed, pkggennotes
from SubmittalPackagesItems
where (projidjobnumber = ? and pkgclosed = ?)

I then use the following in my page:
DE.InsertRevisionItems projidjobnumber, pkgclosed

And the record is inserted into the table as desired!

However, when I add the third parameter to the above sql ie: where (projidjobnumber = ? and pkgclosed = ? and pkgcode = ?)
And I use the following in my page:
DE.InsertRevisionItems projidjobnumber, pkgclosed, pkgcode

The page runs as if the record was inserted without error but, no record is ever actually inserted into the table!

Any idea as to why this won't work?

 

[MerlinB]

As these are parameters of a Where clause, if they return an empty result set (but are syntactically correct) then you will get no insert and no error.

Try response.write-ing out the parameter values, then doing the select in a query tool and see how many rows are returned.

http://www.corporateinternet.com

 

[jmccolley]

When I response.write the values they are correct. If I then go to the command under global.asa and manually enter the values for each parameter the row is inserted correctly.

 

[MerlinB]

Often when this happens, it is because of space or non-text characters being in the parameter variable value. A simple response.write will not show spaces - so put some brackets around the values when you print them.

Failing this, I am not too sure. You could try to see if additional information is returned:

dim oCmd
set oCmd = DE.commands("InsertRevisionItems&quot
nRetCode = oCmd.parameters(0)

I am not sure what other information (like an SQL message) you can obtain from this.

Also, if you place the DataEnvironment.asa (that contains these commands) onto another web server (by copying it manually), then you will need to 'unload' or stop/start the web - this causes the current copy to be cleared from memory, and the new version to be read in.

http://www.corporateinternet.com