Insert Probelms

I am producing a page that tracks jobs, for reference the method of payment is inserted into my database. Using javascript the user (using radio buttons) can show/hide table rows that contain Form fields for Amex(3), Cheque(1) or Cash(1). My Probelm is that the page will only insert if all 3 Amex fields have entered values, but because of the jave script you cant see the 3 Amex fields if you choose Cheque or Cash - I am using access and have set the field to Required No and Allow Zero Length Yes.
The insert code is:
<%

if(Request("JobID") <> "") then Command1__varJobID = Request("JobID")

if(Request("Quantity") <> "") then Command1__varQuantity = Request("Quantity")

if(Request("Size") <> "") then Command1__varSize = Request("Size")

if(Request("SizeCustom") <> "") then Command1__varSizeCustom = Request("SizeCustom")

if(Request("Stock") <> "") then Command1__varStock = Request("Stock")

if(Request("Range") <> "") then Command1__varRange = Request("Range")

if(Request("Finishing") <> "") then Command1__varFinishing = Request("Finishing")

if(Request("Security") <> "") then Command1__varSecurity = Request("Security")

if(Request("Cost") <> "") then Command1__varCost = Request("Cost")

if(Request("DeliveryName") <> "") then Command1__varDeliveryName = Request("DeliveryName")

if(Request("DeliveryTel") <> "") then Command1__varDeliveryTel = Request("DeliveryTel")

if(Request("DeliveryAddress") <> "") then Command1__varDeliveryAddress = Request("DeliveryAddress")

if(Request("PaymentType") <> "") then Command1__varPaymentType = Request("PaymentType")

if(Request("Payer1") <> "") and (Request("Payer2") = "") and (Request("Payer3") = "") then Command1__varPayer = Request("Payer1")

if(Request("Payer1") = "") and (Request("Payer2") <> "") and (Request("Payer3") = "") then Command1__varPayer = Request("Payer2")

if(Request("Payer1") = "") and (Request("Payer2") = "") and (Request("Payer3") <> "") then Command1__varPayer = Request("Payer3")

if(Request("AmexNumber") <> "") then Command1__varAmexNumber = Request("AmexNumber")

if(Request("AmexMonth") <> "") then Command1__varAmexMonth = Request("AmexMonth")

if(Request("AmexYear") <> "") then Command1__varAmexYear = Request("AmexYear")

if(Request("ContractNumber") <> "") then Command1__varContractNumber = Request("ContractNumber")

if(Request("SapBann") <> "") then Command1__varSapBann = Request("SapBann")

if(Request("AmountReceived") <> "") then Command1__varAmountReceived = Request("AmountReceived")

%>
<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_Publishing_STRING
Command1.CommandText = "INSERT INTO PrtCopy (JobID, Quantity, Size, SizeCustom, Stock, Range, Finishing, Security, Cost, DeliveryName, DeliveryTel, DeliveryAddress, PaymentType, Payer, AmexNumber, ExpiryMonth, ExpiryYear, ContractNumber, SapBann, AmountReceived) VALUES (" + Replace(Command1__varJobID, "'", "''") + ", '" + Replace(Command1__varQuantity, "'", "''") + "', '" + Replace(Command1__varSize, "'", "''") + "', '" + Replace(Command1__varSizeCustom, "'", "''") + "','" + Replace(Command1__varStock, "'", "''") + "','" + Replace(Command1__varRange, "'", "''") + "','" + Replace(Command1__varFinishing, "'", "''") + "','" + Replace(Command1__varSecurity, "'", "''") + "'," + Replace(Command1__varCost, "'", "''") + ",'" + Replace(Command1__varDeliveryName, "'", "''") + "','" + Replace(Command1__varDeliveryTel, "'", "''") + "','" + Replace(Command1__varDeliveryAddress, "'", "''") + "','" + Replace(Command1__varPaymentType, "'", "''") + "','" + Replace(Command1__varPayer, "'", "''") + "'," + Replace(Command1__varAmexNumber, "'", "''") + "," + Replace(Command1__varAmexMonth, "'", "''") + "," + Replace(Command1__varAmexYear, "'", "''") + ",'" + Replace(Command1__varContractNumber, "'", "''") + "','" + Replace(Command1__varSapBann, "'", "''") + "'," + Replace(Command1__varAmountReceived, "'", "''") + ") "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()

%>


Does anybody know how I can insert if some of my fields are empty?.

Thanks in advance

D

hi,

first little advices. try to use select-cases or try to interleave the if-statements, you'll need not so much code and you have a better overview.

in addition the escape-character between variables and strings is not "+", but "&" (if you use vbscript)

my suggestion is that the sql-string is wrong. for getting the error try this:


or print the variable strSQL before executing the SQL-String.
-->Copy-Paste into Access and see what happens....

tom

Hi Tom,
Thanks for responding. I have changed the code to:
<% If Request("Submit") <> "" Then %>
<%
Dim strJobCode, strQuantity, strSize, strSizeCustom, strStock, strRange, strFinishing, strSecurity, strCost, strDeliveryName, strDeliveryTel, strDeliveryAddress, strPaymentType
Dim objConn, strInsert

'gather form field values
strJobCode = Replace(request("JobCode"), "'", "''")
strQuantity = Replace(request("Quantity"), "'", "''")
strSize = Replace(request("Size"), "'", "''")
strSizeCustom = Replace(request("SizeCustom"), "'", "''")
strStock = Replace(request("Stock"), "'", "''")
strRange = Replace(request("Range"), "'", "''")
strFinishing = Replace(request("Finishing"), "'", "''")
strSecurity = Replace(request("Security"), "'", "''")
strCost = Replace(request("Cost"), "'", "''")
strDeliveryName = Replace(request("DeliveryName"), "'", "''")
strDeliveryTel = Replace(request("DeliveryTel"), "'", "''")
strDeliveryAddress = Replace(request("DeliveryAddress"), "'", "''")
strPaymentType = Replace(request("PaymentType"), "'", "''")


'create and open a connection
Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open MM_Publishing_STRING

'define the append query
strInsert = "INSERT INTO PrtCopy (JobID, Quantity, Size, SizeCustom, Stock, Range, Finishing, Security, Cost, DeliveryName, DeliveryTel, DeliveryAddress, PaymentType) VALUES ('"&strJobCode&"', '"&strQuantity&"', '"&strSize&"', '"&strSizeCustom&"', '"&strStock&"', '"&strRange&"', '"&strFinishing&"', '"&strSecurity&"', '"&strCost&"', '"&strDeliveryName&"', '"&strDeliveryTel&"', '"&strDeliveryAddress&"', '"&strPaymentType&"')"

objConn.Execute()
%>
<% End IF %>

But now I get the following error:
Error Type:
Microsoft VBScript runtime (0x800A01C2)
Wrong number of arguments or invalid property assignment: 'Execute'

Any Ideas Why?

You need to tell it what to execute: objConn.execute(strInsert)

Cheers Karsh, Just figured that out myself although I was worried it would write to the Database as soon as the page is loaded. However obvously the If statement stops it.

Thanks again

OK the first thing I have to ask is:
Why are you doing this?

if(Request("JobID") <> "") then Command1__varJobID = Request("JobID")

Why don't you simply do:
Command1__varJobID = Request("JobID")

Why not? I mean after all, your variable Command1__varJobID is *STILL* inside your INSERT query so it doesn't make any sense?

and another thing...what if the user decides to enter a *SPACE* using the space bar then when you do:
if(Request("JobID") <> "") then Command1__varJobID = Request("JobID")

You'll insert a *space* into your variable Command1__varJobID You don't even Trim() it ?

I *STRONGLY* suggest to change the way you're doing things...look at this and it turns out to be the EXACT same thing but cleaner code and more efficient.

Here...I've attached the .asp page...have a look!

Hope this helps!
Sincerely

Vlince