Problem with SQL-Server Constraints and Frontpage

[Markus Gonaus]

I am using a Frontpage Applikation to Save a form to a SQL-database.

That works so far. My database has a foreign key constraint on the table
where th form inserts its Data.

An if the inserted data conflicts with that constraint it does not get
inserted. So far just as it should bee.

The only problem is, that even in the case the insert fails, because of
the constraint, my application goes to the success URL and not to the fail
URL, as I would expect.

Server is a Microsoft SBS 2003, with FP-extentions enabled on the Web
belonging to this Project.

Any hints for me?
tia

Markus

 

[Stefan B Rusynko]

Show your code to check for the insertion




| I am using a Frontpage Applikation to Save a form to a SQL-database.
|
| That works so far. My database has a foreign key constraint on the table
| where th form inserts its Data.
|
| An if the inserted data conflicts with that constraint it does not get
| inserted. So far just as it should bee.
|
| The only problem is, that even in the case the insert fails, because of
| the constraint, my application goes to the success URL and not to the fail
| URL, as I would expect.
|
| Server is a Microsoft SBS 2003, with FP-extentions enabled on the Web
| belonging to this Project.
|
| Any hints for me?
| tia
|
| Markus

 

[Markus Gonaus]

Show your code to check for the insertion

Its the Standard Frontpage webbot thing:

<%^M' FP_ASP ASP Automatically generated by a Frontpage Component. Do not
Edit.

On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage Session("FP_OldLCID") =
Session.LCID
Session.CodePage = 1252
Session.LCID = 3079
Err.Clear

strErrorUrl = "fail.htm"

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then If
Request.Form("VTI-GROUP") = "0" Then
Err.Clear

Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"

Set fp_rs = Server.CreateObject("ADODB.Recordset") FP_DumpError
strErrorUrl, "Cannot create record set"

fp_conn.Open Application("database_ConnectionString") FP_DumpError
strErrorUrl, "Cannot open database"

fp_rs.Open "Clients", fp_conn, 1, 3, 2 ' adOpenKeySet,
adLockOptimistic, adCmdTable FP_DumpError strErrorUrl, "Cannot
open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the
database" Dim arFormFields0(36)
Dim arFormDBFields0(36)
Dim arFormValues0(36)

arFormFields0(0) = "Pass"
arFormDBFields0(0) = "Pass"
arFormValues0(0) = Request("Pass")
arFormFields0(1) = "Titel"
arFormDBFields0(1) = "TITEL"
arFormValues0(1) = Request("Titel")
[some more Fields] arFormFields0(35) = "GMDE"
arFormDBFields0(35) = "GMDE"
arFormValues0(35) = Request("GMDE")

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0

If Request.ServerVariables("REMOTE_HOST") <> "" Then
FP_SaveFieldToDB fp_rs,
Request.ServerVariables("REMOTE_HOST"), "PC"
End If
FP_SaveFieldToDB fp_rs, Now, "Zeitmarkierung"

fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"

fp_rs.Close
fp_conn.Close

Session("FP_SavedFields")=arFormFields0
Session("FP_SavedValues")=arFormValues0 Session.CodePage =
Session("FP_OldCodePage") Session.LCID = Session("FP_OldLCID")
Response.Redirect "/danke.htm"

End If
End If

Session.CodePage = Session("FP_OldCodePage") Session.LCID =
Session("FP_OldLCID")

%>

The foreign Key Constraint is on GMDE. It works, that nothing gets
inserted if I use Data not valid for GDE, but i get to /danke.htm and not
like expectet to fail.htm

Markus

 

[Markus Gonaus]

Show your code to check for the insertion

Thank you for your reply.
Meanwhile I solved the problem myself:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314440
was one thing needed.
But there is an adittional problem. SQL Server gives back on successfull
operations "0000000-2147217885" instead of NULL as the FP_DumpError
Function of Frontpage expects. So I had to change this in the FP_DumpError
Funktion too.

Only thing I would like to know is, if this behavior is only in the SBS
2003 Server Premium Version, or a general behavior of MS-SQL Server.