Error trying to INSERT record using FP2003 DRW. (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

tcbarker4 -> 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



Spooky -> RE: Error trying to INSERT record using FP2003 DRW. (11/23/2007 14:57:00)

At the very top of the insert page, what does this do ?

<%
response.write "Form = " &request.form("AccountID")
response.end
%>



tcbarker4 -> 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 -> 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 -> 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.



Spooky -> RE: Error trying to INSERT record using FP2003 DRW. (11/23/2007 18:15:01)

Whats the field type and size that you are inserting into ? Does it error when using any and all values?



William Lee -> 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.




tcbarker4 -> 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 -> 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 -> 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?




tcbarker4 -> 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 -> 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?




tcbarker4 -> 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 -> 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.



tcbarker4 -> 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)



Spooky -> RE: Error trying to INSERT record using FP2003 DRW. (11/25/2007 23:23:13)

Can you provide the whole code from this page?



William Lee -> 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") + "')"



tcbarker4 -> 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('&quot;&amp;request.form(&quot;AccountID&quot;)&amp;&quot;')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="&lt;b&gt;&lt;font size=&quot;&quot;5&quot;&quot;&gt;&lt;font color=&quot;&quot;#CC3300&quot;&quot;&gt;&lt;p align=&quot;&quot;center&quot;&quot;&gt;Account Updated Successfully !!&lt;/p&gt;&lt;/font&gt;&lt;/font&gt;&lt;/b&gt;" 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 -> 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 -> 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 -> 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 -> 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



tcbarker4 -> 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 -> 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?



tcbarker4 -> 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 -> 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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125