Insert value in SQL table from webserver

[Badgers]

I have a problem with inserting data into an SQL server 7.0 database.

I am using Visual Interdev, and when I run the project locally my insert Sql code works fine, but when I publish the project to a webserver my insert Sql stops working.

This seems strange bearing in mind my localcopy and webserver uses the same database?

Could it be something to do with server permission ?

Thanks

TIMSTAR

 

[jonscott8]

Define "insert Sql stops working". Does it hang? or does it throw an error and if so, what's the message?

Have you isolated the code to determine it's the Insert that's failing? or does the script in general fail and you presume it's the Insert that's failing?

Post your script. Jon Hawkins

 

[Badgers]

If sUpdateOrInsert = "Insert" Then
Set rsHotTipsInsert = Server.CreateObject("ADODB.Recordset&quot

sParamsInsert = iCompNo & "," & iAccId & ",'" & sIntOpt
& "'" & ",'" & sWhichHotTip & "'" & ",'" & sEditedHotTip
& "'"

rsHotTipsInsert.Open "SP_HOTTIPS_INSERT_CSS_PRG " & sParamsInsert, Application("Savings_ConnectionString&quot,1

Set xmlDoc = Nothing
Set rsHotTipsInsert = Nothing

End If

The "sParamsInsert" as a collection of parametres, the connection string is made through the global.asa. (Application("Savings_ConnectionString&quot.

When I run the project locally but accessing server database the code works, but when I publish the project which uses the same database the code stops.

I get no errors when this happens.

Thanks.

 

[jonscott8]

First, where is the Insert command you speak of? All I see is a call to a stored procedure. If it's in that, you may need to post the SP code.

Second, what authentication are you using, NT or SQLS? If you're unsure, post your connection string.

Lastly, if you're gonna be executing non-row returning SPs, you really should use Command/Connection objects ISO Recordsets. Otherwise, there's no way to determine success/failure. IOW,

Set oConn = Server.CreateObject("ADODB.Connection&quot
oConn.Open Application("Savings_ConnectionString&quot
If oConn.State = 1 Then
oConn.Execute "Your_SP " & sParams, nCount
If nCount > 0 Then
'everything okay
Else
'this only works if your SP surfaces the # of affected recs
End If
oConn.Close
Else
'got a problem
End If
Set oConn = Nothing Jon Hawkins

 

[Badgers]

Here is a the SP.

CREATE PROCEDURE SP_HOTTIPS_INSERT_CSS_PRG
(
@iCompNo as int,
@iAccId as int,
@sIntPaid as varchar(10),
@sWhichHotTip as varchar(15),
@sEditedHotTip as varchar(500)
)
AS
-- If no record exists, insert a new one
IF @sWhichHotTip = "frameLeft"
BEGIN
IF (Select Count(*) from SAVINGS_HOTTIPS
Where COMPNO = @iCompNo And ACCID = @iAccid And INTPAID = @sIntPaid) = 0
BEGIN
Insert Into SAVINGS_HOTTIPS
(COMPNO, ACCID, INTPAID, OURNOTE)
Values (@iCompNo, @iAccId, @sIntPaid, @sEditedHotTip)
END
END
-- If matching record exists, delete old and re-insert new
IF @sWhichHotTip = "frameLeft"
BEGIN
IF (Select Count(*) from SAVINGS_HOTTIPS
Where COMPNO = @iCompNo And ACCID = @iAccid And INTPAID = @sIntPaid) > 0
BEGIN
Delete from SAVINGS_HOTTIPS
Where COMPNO = @iCompNo And ACCID = @iAccid And INTPAID = @sIntPaid
Insert Into SAVINGS_HOTTIPS
(COMPNO, ACCID, INTPAID, OURNOTE)
Values (@iCompNo, @iAccId, @sIntPaid, @sEditedHotTip)
END
END

Here is the connection string.


Application("Savings_ConnectionString&quot = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Savings;Data Source=WEBTEST;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TIMOLD;Use Encryption for Data=False;Tag with column collation when possible=False;"
Application("Savings_ConnectionTimeout&quot = 15
Application("Savings_CommandTimeout&quot = 30
Application("Savings_CursorLocation&quot = 3
Application("Savings_RuntimeUserName&quot = "sa"
Application("Savings_RuntimePassword&quot = ""

Thanks