Asp Sql

[bloke]

Guys

Can anyone tell me what is wrong with the following:

strPurchaseAdd = "INSERT INTO EXT_purchase_Req (" & strInsertFields & ") VALUES(" & strReqString & ") SELECT @@identity as 'requisitionID';"

Set rsPurchaseAdd = Server.CreateObject("ADODB.Recordset")

rsPurchaseAdd.Open strPurchaseAdd, gstrConn

response.write rsPurchaseAdd("requisitionID")

It works just fine and dandy in sqlserver query analyser and when I run it in the asp page it inserts the record correctly it just doesn't return requisitionID in the recordset.

I'm sure it should work and guessing it's something very silly which is stopping it.

Cheers

[rdoekes]

Im not sure about this one, Bloke, but what if you try an enter in between the statements?
....VALUES(" & strReqString & ")" & vbcrlf & _
"SELECT ....

Does that solve it?

-Rogier Doekes

[bloke]

Doesn't change anything mate. I've tried all sorts of nonsense and I think I'm really close, I just think that I'm missing something really really obvious.

I've tried passing the entire string to a stored procedure but I can't get that to work. I've tried passing the INSERT parameters and the VALUES parameters to a sp too and can't get that to work.

Thanks for your suggestion though!

;-)

[rdoekes]

what about this stored procedure:

CREATE PROCEDURE spDynamic
@fields nvarchar(1000),
@values nvarchar(1000)

AS
DECLARE @sql nvarchar(4000)

SET @sql = N'INSERT INTO Ext_Purch_req (' +
@fields + N') VALUES (' + @values + ')'

Execute sp_executesql @sql

SELECT @@IDENTITY as requisitionID

GO


Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "connectionstring"
Set rs = cnn.Execute ("EXECUTE spDynamic @fields = '" & _
strInsertFields & "', @values = '" & strReqString & "'")
Response.write rs.Fields("requisitionID").Value

[bloke]

Looking good but falling with this error...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'value'.

/bespoke/purchase1.asp, line 162


Line 162 being:

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open gstrConn
'the line below is 162
Set rs = cnn.Execute ("EXECUTE sp_Purchase_Req @fields = '" & _
strInsertFields & "', @values = '" & strReqString & "'")
Response.write rs.Fields("requisitionID").Value

Have I just interpreted your example incorrectly?

Cheers

[rdoekes]

this stored procedure works fine in Query Analyser?

[bloke]

Sorry, I'm confusing myself now (not difficult!)

Here is a response.write of the string followed by the error. I've removed a single quote from @ values = and at the end as they were already contained in the string strReqString.

EXECUTE sp_Purchase_Req @fields = 'Contract_no, Activity_code, Sector_no, purchase, enquiry, vendor_docs, ISO, Inspection, Required_dte, Budget, Item1, Quantity1, Unit1, Description1, Unit_rate1, Total1, Item2, Quantity2, Unit2, Description2, Unit_rate2, Total2, total_value, raised_by, raised_dte, qa_review, qa_dte, approved_manager, app_man_dte, approved_director, app_dir_dte, attachment_string', @values = 'something','a','a',1,1,0,'a',1,'29-Aug-2003',1000,'a',10,'a','a',10,100,'b',10,'c','s',10,100,200,'Peter Wilson','28/08/2003','nameA','28/08/2003','nameB','28/08/2003','nameC','28/08/2003','na'
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

/bespoke/purchase1.asp, line 168

[rdoekes]

I think doubling up the quotes in the values will do the trick, since the quote in T-SQL signals the end of a field. Doubling up the quotes espaces it.

Create this ASP function:

Function ReplaceQuotes(strText)
ReplaceQuotes = "'" & Replace(strText, "'", "''") & "'"
End Function
And make your execute statement as:

Set rs = Cnn.Execute("Execute sp_Purch_req @fields = " & _
ReplaceQuotes(strInsertFields) & ", @values = " & _
ReplaceQuotes(strReqString) )

-Rogier Doekes

[bloke]

Rogier, you are a star!

Works a treat. Give yourself a pat on the back! ;-)

Thanks for all your help.


Cheers

[rdoekes]

You're welcome, Bloke.