|
| |
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
 |
Form fields from one table to another? - 9/1/2004 14:12:37
Hi All,
I am using FP2003 with SQL Server 2000 to retrieve a record, populate some (but not all) of a form's fields with stored information, and then post that form's data to a different table in the same database via an INSERT statement. Can I do this with the DRW, without using a custom SQL statement?
Thanks.
|
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 14:59:18
Yes you can. I just did it. Use an insert statement in your drw. it should direct it to the new table or database.
I actually had two queries on my update page. One transfered the data to a new database, and the other updated the original saying it was transfered.
Mark
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 15:04:39
Right, but you had to hand-code it within the DRW section, right?
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 15:18:04
Yes, i had to create the insert into statement.
INSERT INTO yourtablename (fieldname1, fieldname2, fieldname 3 (put in all of your field names) VALUES ('::field1::' , '::field2::' , '::field3::')
make sure you use all the field names you used in the field name section in your values section.
Mark
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 15:26:20
That's where I'm running into trouble. The Custom SQL verifies correctly, but when I run the .asp pages, I get a DRW error on the insert page:
Database Results Wizard Error
Unable to find operator in query string. Query string currently is
[snip]
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
What's driving me nuts is that I have a lot of fields, and I can't tell which of the form fields is empty. I am completing the form so that all fields are filled in, and each of the fields is in the form. I've already checked for misspelled fields, etc.
| |
|
|
|
mfalk
Posts: 330
From: Centereach,NY
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 15:31:10
Is this created in Access? Check your parameters for each field, such as text fields should be '::text::', numeric ::numeric::. If you have a date field put brackets around the field name in the first portion of your statement such as [Date]. Ive received the same error messages from not using a bracket with the date fieldname for some reason, but it works when you use it.
Mark
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 15:40:40
It's SQL Server 2000, with text and numeric fields, and I've formatted the parameters correctly, as you mention. I have one more thing to check.
Question: why did you use a Custom SQL statement--it is because you are modifying two tables at once?
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 16:57:01
All right, I have succeeded in combining the DRW results from the first table and the submission form so that the form submits directly to the second table (in theory). However, now I get the following error message on execution:
Database Results Wizard Error
Description: Invalid character value for cast specification.
Number: -2147217887 (0x80040E21)
Source: Microsoft OLE DB Provider for SQL Server
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
Help, anyone? I am either specifying a default form field value or am entering a value on the form, depending on the field, so none of the fields should be empty. What might I be overlooking? There is a one-to-one mapping taking place between the form and the table, with one additional field in the table called ttimestamp, a timestamp field.
TIA
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 16:58:06
And I've searched for this error message in the KB and elsewhere already. The limited results do not appear to apply to my situation.
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 18:29:51
Hi Spooky,
I have the form submitting directly to the database table, and each of the form fields is mapped to its corresponding database table field. Just for the heck of it I deleted the ttimestamp field, but I get the same result.
Can I do it this way? Or must I use the Custom SQL option with the DRW on a separate .asp page?
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 18:53:21
Yep to the first, and to the second: there are two smallint fields.
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 19:54:31
and the form fields that match the smallint values are spec'd as integers. If I change the datatypes in the table itself to integer, I get the same result.
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 20:48:36
They both have default values of 1, so the datatypes should match.
Let me clarify that my overall technique for doing this is correct:
I use an .asp page to retrieve a record, and that data correctly populates some of the fields on a second .asp page which contains a form with a DRW region, containing the pre-populated fields inside it. That form submits its results, via the Form Properties settings, to a second table in the same database. Is this an acceptable method of writing to the table, or must the form point to another .asp page with a DRW region containing a custom SQL INSERT statement? It seems that all examples I've seen are of the latter type.
Thanks, Spooky.
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/1/2004 21:04:21
quote:
ORIGINAL: Spooky
I would say yes, its ok to do. However its been a while since Ive used it, and my preference is the clarity of an insert statement. It tends to be a lot easier to troubleshoot ;-)
Okay, that takes me back to where I started, which was with a Custom SQL Insert statement. The Custom SQL verified correctly, but when I ran the .asp pages, I got a DRW error on the insert page:
Database Results Wizard Error
Unable to find operator in query string. Query string currently is
[snip]
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
I was just as stumped, being unable to tell which of 25+ fields were empty, since I was providing default values for the fields, and the table's fields are set to accept nulls.
How do I troubleshoot this?
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/2/2004 9:27:51
quote:
ORIGINAL: Spooky
Write the SQL statement - can you post your pages code?
Here is the insert page:
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<table width="100%" border="1">
<thead>
</thead>
</table>
<table width="100%" border="1">
<thead>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes s-dataconnection="WPCustomers" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns s-criteria s-order s-sql="INSERT CustomerUploads (cemail, cContact, cCompany, cPhone, cFax, cAddress1, cAddress2, cCity, cState, cZip, cDeliveryNote, cFileName, cProjectName, cDateTimeNeeded, cPaperSize, nNumberOriginals, nNumberCopies, cPaperStock, cInkColor, cImposition, cFinishing, cSpecialInstructions, cPickupDelivery, cProofType, cPaymentMethod, cCCNumber, cCCExpirationDate, cCardName) VALUES ('::cemail::', '::cContact::', '::cCompany::', '::cPhone::', '::cFax::', '::cAddress1::', '::cAddress2::', '::cCity::', '::cState::', '::cZip::', '::cDeliveryNote::', '::cFileName::', '::cProjectName::', '::cDateTimeNeeded::','::cPaperSize::', ::nNumberOriginals::, ::nNumberCopies::, '::cPaperStock::', '::cInkColor::', '::cImposition::', '::cFinishing::', '::cSpecialInstructions::', '::cPickupDelivery::', '::cProofType::', '::cPaymentMethod::', '::cCCNumber::', '::cCCExpirationDate::', '::cCardName::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="cemail=&cContact=&cCompany=&cPhone=&cFax=&cAddress1=&cAddress2=&cCity=&cState=&cZip=&cDeliveryNote=&cFileName=&cProjectName=&cDateTimeNeeded=&cPaperSize=&nNumberOriginals=&nNumberCopies=&cPaperStock=&cInkColor=&cImposition=&cFinishing=&cSpecialInstructions=&cPickupDelivery=&cProofType=&cPaymentMethod=&cCCNumber=&cCCExpirationDate=&cCardName=" s-norecordsfound="No records returned." i-maxrecords="0" i-groupsize="5" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="TRUE" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="INSERT CustomerUploads (cemail, cContact, cCompany, cPhone, cFax, cAddress1, cAddress2, cCity, cState, cZip, cDeliveryNote, cFileName, cProjectName, cDateTimeNeeded, cPaperSize, nNumberOriginals, nNumberCopies, cPaperStock, cInkColor, cImposition, cFinishing, cSpecialInstructions, cPickupDelivery, cProofType, cPaymentMethod, cCCNumber, cCCExpirationDate, cCardName) VALUES ('::cemail::', '::cContact::', '::cCompany::', '::cPhone::', '::cFax::', '::cAddress1::', '::cAddress2::', '::cCity::', '::cState::', '::cZip::', '::cDeliveryNote::', '::cFileName::', '::cProjectName::', '::cDateTimeNeeded::','::cPaperSize::', ::nNumberOriginals::, ::nNumberCopies::, '::cPaperStock::', '::cInkColor::', '::cImposition::', '::cFinishing::', '::cSpecialInstructions::', '::cPickupDelivery::', '::cProofType::', '::cPaymentMethod::', '::cCCNumber::', '::cCCExpirationDate::', '::cCardName::')"
fp_sDefault="cemail=&cContact=&cCompany=&cPhone=&cFax=&cAddress1=&cAddress2=&cCity=&cState=&cZip=&cDeliveryNote=&cFileName=&cProjectName=&cDateTimeNeeded=&cPaperSize=&nNumberOriginals=&nNumberCopies=&cPaperStock=&cInkColor=&cImposition=&cFinishing=&cSpecialInstructions=&cPickupDelivery=&cProofType=&cPaymentMethod=&cCCNumber=&cCCExpirationDate=&cCardName="
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="WPCustomers"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="34972" --><!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="5" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr><TR><TD VALIGN=MIDDLE COLSPAN=64><NOBR><INPUT TYPE=Button VALUE=" |< "><INPUT TYPE=Button VALUE=" < "><INPUT TYPE=Button VALUE=" > "><INPUT TYPE=Button VALUE=" >| "> [1/5]</NOBR><BR></td></tr>" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
</table>
<table width="100%" border="1">
<tbody>
</tbody>
</table>
</body>
</html>
| |
|
|
|
kewebdesign
Posts: 41
Joined: 7/21/2003
Status: offline
|
RE: Form fields from one table to another? - 9/2/2004 10:17:43
Follow up question:
With regard to the page containing the DRW-populated form field results and the remaining fields to be filled out, should the DRW results region be nested inside the submission form that points to the InsertData.asp code (which I've included in the previous post)? Because that's how it's set up now.
Thanks.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 