|
| |
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
 |
Error trying to INSERT record using FP2003 DRW. - 11/23/2007 0:26:11
I have two .asp pages and am using FP2003 with a SQL database. One is called insert.asp and it prompts a user to input a value into a text box called Account. The second page is called insert2.asp and it is the Database Results region page that inserts the record into a table. Account is defined in the database table as nchar(10) and allows nulls.
I have an issue when trying to INSERT records into a table. Using the DRW, I have the following SQL statement:
INSERT INTO TblVehicle (AccountNr1) VALUES('::AccountID::')
When I press Verify Query, I get the following error:
The Custom Query Contains Errors
If I use a literal like this:
INSERT INTO TblVehicle (AccountNr1) VALUES('guest01')
I do not get the error and the value is inserted into the table without problem.
I looked around on the Internet and found this syntax workaround:
INSERT INTO TblVehicle (AccountNr1) VALUES('"&request.form("AccountID")&"')
...and when I click Verify Query, it's fine. But when I submit the page. it gives me the following error:
Database Results Wizard Error
Description: String or binary data would be truncated.
Number: -2147217833 (0x80040E57)
Source: Microsoft OLE DB Provider for SQL Server
I have noticed when viewing the code on the second page that extra quotes are being placed in the query string:
fp_sQry="INSERT INTO TblVehicle (AccountNr1) VALUES('""&request.form(""AccountID"")&""')"
I cannot get a simple insert into a database to work when using the custom query. It is driving me nuts. Any help would be TREMENDOUSLY appreciated.
Thanks
Tim
|
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/23/2007 17:11:51
I don't see what you are referencing. I looked at the code view on both pages but don't see that code.
If anything, it might be something just left over from something else I tried. The insert.asp and insert2.asp pages are just test pages to get the record insert to work. Once I've figured out what the right syntax/insert statement is, I'll build the actual pages to insert the records into the production database.
Question: Could the problem have something to do with the fpdbrgn1.inc building the query string incorrectly and inserting too many double quotes (")?
Thanks
Tim
| |
|
|
|
Spooky
Posts: 26603
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/23/2007 17:18:35
Sorry - I meant to add that code to see what the inputed value was
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/23/2007 17:43:54
Sorry, I didn't understand....my bad...
The output was:
Form=tcbarker4
tcbarker4 is the value that I inputted into the text box on the first .asp page.
| |
|
|
|
William Lee
Posts: 1131
Joined: 1/25/2002
From: Singapore
Status: online
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/23/2007 22:54:37
quote:
ORIGINAL: tcbarker4
I have an issue when trying to INSERT records into a table. Using the DRW, I have the following SQL statement:
INSERT INTO TblVehicle (AccountNr1) VALUES('::AccountID::')
When I press Verify Query, I get the following error:
The Custom Query Contains Errors
:
:
I cannot get a simple insert into a database to work when using the custom query. It is driving me nuts. Any help would be TREMENDOUSLY appreciated.
It appears the problem you mentioned here are documented in this linkhttp://support.microsoft.com/kb/907307
Check if your insert statement belongs to the examples of INVALID queries against a database.
_____________________________
William Lee
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 13:18:41
quote:
linkhttp://support.microsoft.com/kb/907307
The size of the database field is 10 characters and the form field is also 10. This error happens whenever I try to insert a variable. If I change the query to use a literal, it works fine.
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 13:21:54
I looked at that article and it does appear that my query falls into the "invalid queries against a database" group. But I've googled this problem and found a ton of examples that say this is the right syntax. Unfortunately, that article you supplied me does not show the correct syntax for an INSERT query. So I tried taking out the parenthesis and all combinations of it and then I get a syntax error from SRW.
Any suggestions on the syntax?
Thanks
Tim
| |
|
|
|
William Lee
Posts: 1131
Joined: 1/25/2002
From: Singapore
Status: online
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 20:49:31
quote:
ORIGINAL: tcbarker4
INSERT INTO TblVehicle (AccountNr1) VALUES('"&request.form("AccountID")&"')
...and when I click Verify Query, it's fine. But when I submit the page. it gives me the following error:
Database Results Wizard Error
Description: String or binary data would be truncated.
Number: -2147217833 (0x80040E57)
Source: Microsoft OLE DB Provider for SQL Server
I have noticed when viewing the code on the second page that extra quotes are being placed in the query string:
fp_sQry="INSERT INTO TblVehicle (AccountNr1) VALUES('""&request.form(""AccountID"")&""')"
I cannot get a simple insert into a database to work when using the custom query.
Try enter this into the custom query first before I suggest a workaround to Inserting record using DRW
"INSERT INTO TblVehicle (AccountNr1) VALUES ('" & request.form("AccountID") & "')"
You should see in the fp_sQry as:
fp_sQry="INSERT INTO TblVehicle (AccountNr1) VALUES ('" & request.form("AccountID") & "')"
Question:How many columns do you have in TblVehicle besides AccountNr1?
_____________________________
William Lee
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 22:15:52
This is the Custom Query string that I typed in:
INSERT INTO TblVehicle (AccountNr1) VALUES('"&request.form("AccountID")&"')
When I click on Verify Query, it say that The query was verified with the selected database connection.
But when I look at the code, this is what is shown:
fp_sQry="INSERT INTO TblVehicle (AccountNr1) VALUES('""&request.form(""AccountID"")&""')"
It appears that there are too many quotes. That is why I was wondering if the fpdbrgn1.inc file might have an issue because, as I understand it, that is the file that opens the connection and builds the query string.
When I actually execute the code, I get the following error:
Database Results Wizard Error
Description: String or binary data would be truncated.
Number: -2147217833 (0x80040E57)
Source: Microsoft OLE DB Provider for SQL Server
Because I was having this issue with my production table that had about 20 columns, I figured I eliminate some of the possible issues with no populating all the columns so I created a test table with only one column called AccountID.
| |
|
|
|
William Lee
Posts: 1131
Joined: 1/25/2002
From: Singapore
Status: online
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 22:21:25
quote:
Try enter this into the custom query first before I suggest a workaround to Inserting record using DRW
"INSERT INTO TblVehicle (AccountNr1) VALUES ('" & request.form("AccountID") & "')"
You should see in the fp_sQry as:
fp_sQry="INSERT INTO TblVehicle (AccountNr1) VALUES ('" & request.form("AccountID") & "')"
Hi Tim
Did you try the above suggestion?
_____________________________
William Lee
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 22:36:30
I tried it. This is what I did:
I typed into the Custom Query :
INSERT INTO TblVehicle (AccountNr1) VALUES('"&request.form("AccountID")&"')
When I click on Verify Query, it say that The query was verified with the selected database connection.
But when I switch to code view this is what is shown:
fp_sQry="INSERT INTO TblVehicle (AccountNr1) VALUES('""&request.form(""AccountID"")&""')"
It appears that there are too many quotes. That is why I was wondering if the fpdbrgn1.inc file might have an issue because, as I understand it, that is the file that opens the connection and builds the query string.
When I actually execute the code, I get the following error:
Database Results Wizard Error
Description: String or binary data would be truncated.
Number: -2147217833 (0x80040E57)
Source: Microsoft OLE DB Provider for SQL Server
Because I was having this issue with my production table that had about 20 columns, I figured I eliminate some of the possible issues with no populating all the columns so I created a test table with only one column called AccountID.
| |
|
|
|
William Lee
Posts: 1131
Joined: 1/25/2002
From: Singapore
Status: online
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 22:38:58
quote:
ORIGINAL: tcbarker4
I tried it. This is what I did:
I typed into the Custom Query :
INSERT INTO TblVehicle (AccountNr1) VALUES('"&request.form("AccountID")&"')
When I click on Verify Query, it say that The query was verified with the selected database connection.
But when I switch to code view this is what is shown:
fp_sQry="INSERT INTO TblVehicle (AccountNr1) VALUES('""&request.form(""AccountID"")&""')"
It appears that there are too many quotes. That is why I was wondering if the fpdbrgn1.inc file might have an issue because, as I understand it, that is the file that opens the connection and builds the query string.
When I actually execute the code, I get the following error:
Database Results Wizard Error
Description: String or binary data would be truncated.
Number: -2147217833 (0x80040E57)
Source: Microsoft OLE DB Provider for SQL Server
Because I was having this issue with my production table that had about 20 columns, I figured I eliminate some of the possible issues with no populating all the columns so I created a test table with only one column called AccountID.
Try typing into the Custom Query EXACTLY what I showed you that is in bold, beginning with double quotes and end with double quotes.
_____________________________
William Lee
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 22:44:44
I copied it in just as you typed it but when I click the Verify Query, this is what I get:
Server error: Unable to retrieve schema information from the query:
"INSERT INTO TblVehicle (AccountNr1) VALUES ('" & request.form("AccountID") & "')"
against a database using the connection string
DRIVER={SQL Server};SERVER=sql1905.mssqlservers.com;DATABASE=repor8;UID=********;PWD=********
The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '&'.
Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
| |
|
|
|
William Lee
Posts: 1131
Joined: 1/25/2002
From: Singapore
Status: online
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 23:27:22
quote:
ORIGINAL: tcbarker4
I copied it in just as you typed it but when I click the Verify Query, this is what I get:
Server error: Unable to retrieve schema information from the query:
"INSERT INTO TblVehicle (AccountNr1) VALUES ('" & request.form("AccountID") & "')"
against a database using the connection string
DRIVER={SQL Server};SERVER=sql1905.mssqlservers.com;DATABASE=repor8;UID=********;PWD=********
The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '&'.
Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)
OK Tim, it might be possible that this will work better with MS SQL database.
Try changing "&" to "+" like this:
"INSERT INTO TblVehicle (AccountNr1) VALUES ('" + request.form("AccountID") + "')"
_____________________________
William Lee
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 23:32:18
Here is the first page. (insert.asp is where the user enters the account number)
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Report2Mom - Activate New Account</title>
</head>
<body bgcolor="#A2D9EE">
<table border="1" width="100%" id="table1">
<tr>
<td width="177">
<img border="0" src="../images/tools1.JPG" width="248" height="182"></td>
<td bgcolor="#A2D9EE">
<p style="margin-top: 0; margin-bottom: -5px"><i><b><font size="7" face="Viner Hand ITC">
How's My Driving?</font></b></i></p>
<p style="margin-top: 0; margin-bottom: 0px"><b><font face="Adobe Garamond Pro Bold" size="6">
1-888-99-2REPORT</font></b></p>
<p style="margin-top: 0; margin-bottom: -5px">
<img border="0" src="../images/report2momlogo.jpg" width="608" height="86"></td>
</tr>
</table>
<p style="margin-top: 0; margin-bottom: -5px"> </p>
<p style="margin-top: 0; margin-bottom: -5px"><u><font size="5"><b>Activate New
Account</b></font><b><font size="5">:</font></b></u></p>
<form method="POST" action="InsertEnrollment.asp" onsubmit="return FrontPage_Form1_Validator(this)" language="JavaScript" name="FrontPage_Form1">
<p style="margin-top: 0; margin-bottom: -5px" align="center">
Account ID :
<!--webbot bot="Validation" b-value-required="TRUE" i-maximum-length="10" -->
<input type="text" name="AccountID" size="10" maxlength="10"></p>
<p style="margin-top: 0; margin-bottom: -5px" align="center">
</p>
<p style="margin-top: 0; margin-bottom: -5px" align="center">
</p>
<p style="margin-top: 0; margin-bottom: -5px" align="center">
<input type="submit" value="Continue Activation" name="B1"></p>
</form>
</body>
</html>
Here is the second page. (insertenrollment.asp is what I'm trying to get to work with the Insert command.
<html>
<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not Edit.
FP_LCID = 1033 %>
<meta http-equiv="Content-Language" content="en-us">
<% ' 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>Report2Mom - Activate Account</title>
</head>
<body bgcolor="#A2D9EE">
<table border="0" width="100%" id="table1">
<tr>
<td width="177">
<img border="0" src="../images/EditAccount.jpg" width="248" height="182"></td>
<td bgcolor="#A2D9EE">
<p style="margin-top: 0; margin-bottom: -5px"><i><b><font size="7" face="Viner Hand ITC">
How's My Driving?</font></b></i></p>
<p style="margin-top: 0; margin-bottom: 0px"><b><font face="Adobe Garamond Pro Bold" size="6">
1-888-99-2REPORT</font></b></p>
<p style="margin-top: 0; margin-bottom: -5px">
<img border="0" src="../images/report2momlogo.jpg" width="653" height="67"></td>
</tr>
</table>
<p style="margin-top: 0; margin-bottom: -5px"> </p>
<p style="margin-top: 0; margin-bottom: -5px"> </p>
<p style="margin-top: 0; margin-bottom: -5px"> </p>
<table width="100%" border="1">
<thead>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes s-dataconnection="repor8" 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 INTO TblVehicle (AccountNr1) VALUES('"&request.form("AccountID")&"')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="<b><font size=""5""><font color=""#CC3300""><p align=""center"">Account Updated Successfully !!</p></font></font></b>" i-maxrecords="256" i-groupsize="0" 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>" startspan b-UseDotNET="FALSE" CurrentExt sa-InputTypes b-DataGridFormat="FALSE" b-DGridAlternate="TRUE" sa-CritTypes b-WasTableFormat="TRUE" --><!--#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 INTO TblVehicle (AccountNr1) VALUES('""&request.form(""AccountID"")&""')"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%""><b><font size=""5""><font color=""#CC3300""><p align=""center"">Account Updated Successfully !!</p></font></font></b></td></tr>"
fp_sDataConn="repor8"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
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="29927" --><!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" 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>" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="56926" --></tbody>
</table>
<p> </p>
<p><font size="4"><b><a target="_self" href="../index.htm">Home</a></b></font></p>
</body>
</html>
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 23:33:31
You can also go to www.report2mom.com/insert.asp to see the page and then the error I keep getting. If you need me to turn debug on, I can.
Thanks
Tim
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 23:41:49
I tried that and this is the error message I'm getting on the second .asp page:
Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 23:45:38
This is the error message with Debug turned on:
Database Results Wizard Error
Description: String or binary data would be truncated.
Number: -2147217833 (0x80040E57)
Source: Microsoft OLE DB Provider for SQL Server
| |
|
|
|
William Lee
Posts: 1131
Joined: 1/25/2002
From: Singapore
Status: online
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 23:48:56
I checked through your webbot code and indeed you have the attributes info missing for s-columnnames and s-columntype.
All I can say is that Spooky has a sticky thread on this in this very ASP and Database forum. Hope you can find useful info here:
http://www.frontpagewebmaster.com/m-175524/tm.htm
_____________________________
William Lee
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/25/2007 23:53:38
Thanks for all your help. I'll check it out and give it a shot.
Thanks again.
Tim
| |
|
|
|
William Lee
Posts: 1131
Joined: 1/25/2002
From: Singapore
Status: online
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/26/2007 0:07:31
Spooky
Where did you get those numeric info for the s-columntype?
_____________________________
William Lee
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/26/2007 0:19:08
OK I did the following:
1. Locate the following section of code:
<!--webbot bot="DatabaseRegionStart"
2. Copy the following text of s-columnname and s-columntype [example given
below].
s-columnnames="AccountID" s-columntypes="3" (I'm assuming 3 means text field)
Now I'm not sure what I should do. I went back into Design and tried to enter my custom query again but am still getting the same error about "Custom Query contains errors".
What should I do next?
| |
|
|
|
tcbarker4
Posts: 15
Joined: 11/23/2007
Status: offline
|
RE: Error trying to INSERT record using FP2003 DRW. - 11/26/2007 0:23:28
How does the fp_sQry variable get built?
Even though my custom query is:
INSERT INTO TblVehicle (AccountNr1) VALUES ('" & request.form("AccountID") & "')
The fp_sQry variable is:
fp_sQry="INSERT INTO TblVehicle (AccountNr1) VALUES ('"" & request.form(""AccountID"") & ""')"
Again, it looks like there are too many quotes in the fp_sQry variable.
| |
|
 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¥ 