-----Original Message-----
Here's an example:
If Len(Request.Form("OperativeID") < 0 then
OperativeID = ""
else
OperativeID = Request.Form("OperativeID")
end if
If you do this check for all fields then you can ensure that something is
getting inserted into the database, even if it's a blank. Otherwise you
need to break down your SQL statement to only do the parts where there's
data in the field. Here's an example of what I mean from Beginning ASP
Databases by Wrox. what it does is only include the fields in the SQL
statement where there's a value.
'Declare variables needed
Dim strInsert
Dim strValues
Dim strSQL
Dim adCmdText
Dim blnCriticalError
Dim blnFirstParameter
'Set required variables
adCmdText = 1
'Start building the SQL string
strSQL = "Update Clubs Set"
'Set the first parameter flag to true
blnFirstParameter = True
'Update meeting date if present
If Len(Request.Form("txtMeetingDate")) > 0 Then
'Add the value to the SQL string
strSQL = strSQL & " ClubAnnualMeeting = '" & _
Cstr(Request.Form("txtMeetingDate")) & "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update club dues if present
If Len(Request.Form("txtClubDues")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " ClubDues = "
Else
strSQL = strSQL & ", ClubDues = "
End If
strSQL = strSQL & CCur(Request.Form("txtClubDues"))
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update web site URL if present
If Len(Request.Form("txtWebSite")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " ClubWWWSite = '"
Else
strSQL = strSQL & ", ClubWWWSite = '"
End If
strSQL = strSQL & Cstr(Request.Form("txtWebSite")) & "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update cranes if checked
If Request.Form("chkCrane") = 1 Then
'Add True to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " ClubHasCranes = True"
Else
strSQL = strSQL & ", ClubHasCranes = True"
End If
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update club membership number if present
If Len(Request.Form("txtMembers")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " ClubMembership = "
Else
strSQL = strSQL & ", ClubMembership = "
End If
strSQL = strSQL & CLng(Request.Form("txtMembers"))
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Update notes if present
If Len(Request.Form("txtNotes")) > 0 Then
'Add the value to the SQL string
If blnFirstParameter Then
strSQL = strSQL & " ClubNote = '"
Else
strSQL = strSQL & ", ClubNote = '"
End If
strSQL = strSQL & Cstr(Request.Form("txtNotes")) & "'"
'Set the first parameter flag to false
blnFirstParameter = False
End If
'Set the Where clause
strSQL = strSQL & " Where ClubCode = '" & _
Request.Form("txtClubCode") & "'"
'Create and open the database object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=Sailors"
'Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")
'Set the command object properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strSQL
objCmd.CommandType = adCmdText
'Execute the command
objCmd.Execute
--
David Berry - MCP
Microsoft MVP - FrontPage
FrontPage Support:
http://www.net-sites.com/sitebuilder/
-----------------------------------
To assist you in getting the best answers for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
-----------------------------------
My database does not reuqire a value as the fields can be
blank.
I do not understand what you mean - can you repeat please.
-----Original Message-----
If your database requires a value in the field then you
need to check the
data being sent from the form and if it's empty you need
to substitute a
bank "" or a NULL.
--
David Berry - MCP
Microsoft MVP - FrontPage
FrontPage Support:
http://www.net- sites.com/sitebuilder/
-----------------------------------
To assist you in getting the best answers for FrontPage
support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
-----------------------------------
message
In my "code view" I have a custom SQL (DRW) that
updates a
database based on values from the previous page. I have
two asp pages...Page 1 is a form, Page 2 is a DRW with
the
code shown below:
s-sql="UPDATE Job_test SET
OperativeID='::OperativeID::',
Account_Details_Job_No='::Account_Details_Job_No::'
,
Account_Details_Date_Issued='::Account_Details_Date
_Issued::',
Account_Details_Date_of_Job='::Account_Details_Date
_of_Job::',
Account_Details_Acc_No='::Account_Details_Acc_No::
WHERE JobID=::JobID::"
I keep getting the error message:
----------------------
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
One or more form fields were empty. You should provide
default values for all form fields that are used in the
query.
-------------------------
Not all the fields on the previuous page as filled in -
as
they are non-mandatory.
What can I do to make this work?
skc
.
.
