Update Database problem? 2

[Chopstik]

I'm not sure if this is the right forum for this, but since I'm working within ASP, I'll start here.

I have several variables I am using to add to two tables within a database. When I run my update command (using VBScript, the rs.update.fields("fieldname&quot = request.form("fieldname&quot), I receive an error on the page. However, when I go to review the database, the data IS UPDATED! Additionally, it is being updated to a SQL server db, and the error message is very generic, stating only that "Errors occurred" and giving me a line number.

I thought originally that it might be due to type conversion, and changed them, but the line number that it says has the problem is a text field, and the db field is varchar, so I can't see the problem here.

This is really confusing me. Does anyone know why this is happening or what I can do to correct it? Thanks!

 

[spencertaylor]

Please post you actual code and a dump of the error

 

[Chopstik]

Spencer,

Will post the code tomorrow from office since that's where info is. Also found a possible cause for the problem being that it the update is run through an iteration and there may be an issue with MS SQL Server 7 trying to update through the iteration. Not sure, will check tomorrow and let you know. Thanks!

 

[Chopstik]

Here's the code:

<%@ Language=VBScript %>
<!--#include file=&quot;LD1VBFunction.asp&quot;-->
<%
Response.Buffer = True

dim conLink4, rsPO, rsPOD, strSQL, i, count

count = Request.Form(&quot;count&quot

set conLink4 = server.CreateObject(&quot;adodb.connection&quot
conLink4.ConnectionString = strConnectLink4
conLink4.Open

set rsPO = server.CreateObject(&quot;adodb.recordset&quot
strSQL = &quot;SELECT PurchaseOrderID, VendorNumber, VendorDirections, &quot; & _
&quot;BillToID, BillToDirections, ShipToPropertyID, ShipToDirections, &quot; & _
&quot;Buyer, TermsName, ShipViaName, DeliveryDate, CIPProjectNumber, &quot; & _
&quot;Company, CostCenter, Tax, Freight, GLAcctNo, POOrderDate FROM PurchaseOrder&quot;
rsPO.Open strSQL, conLink4, 3, 3

with rsPO
.AddNew
.Fields(&quot;PurchaseOrderID&quot = Request.Form(&quot;strPONum&quot
.Fields(&quot;VendorNumber&quot = Request.Form(&quot;strVenNum&quot
.Fields(&quot;VendorDirections&quot = Request.Form(&quot;strVenDesc&quot
.Fields(&quot;BillToID&quot = Request.Form(&quot;strBillToID&quot
.Fields(&quot;BillToDirections&quot = Request.Form(&quot;strBTDesc&quot
.Fields(&quot;ShipToPropertyID&quot = Request.Form(&quot;strShipToID&quot
.Fields(&quot;ShipToDirections&quot = Request.Form(&quot;strSTDesc&quot
.Fields(&quot;Buyer&quot = Request.Form(&quot;strBuyer&quot
.Fields(&quot;TermsName&quot = Request.Form(&quot;strTerms&quot
.Fields(&quot;ShipViaName&quot = Request.Form(&quot;strShipVia&quot
.Fields(&quot;DeliveryDate&quot = Request.Form(&quot;strDelivDate&quot
.Fields(&quot;CIPProjectNumber&quot = Request.Form(&quot;strCapProjNo&quot
.Fields(&quot;Company&quot = Request.Form(&quot;strCompanyNo&quot
.Fields(&quot;CostCenter&quot = Request.Form(&quot;strCostCtr&quot
.Fields(&quot;Tax&quot = Request.Form(&quot;strTax&quot
.Fields(&quot;Freight&quot = Request.Form(&quot;strFreight&quot
.Fields(&quot;GLAcctNo&quot = Request.Form(&quot;strGLAcctNo&quot
.Fields(&quot;POOrderDate&quot = Left(Date,8)
.Update
end with
set rsPO = nothing

set rsPOD = server.CreateObject(&quot;adodb.recordset&quot
strSQL = &quot;SELECT PurchaseOrderID, ItemCode, Description, Quantity, &quot; & _
&quot;UnitOfMeasure, PricePerUnit, ExtendedPrice FROM PurchaseOrderDetail&quot;
rsPOD.Open strSQL, conLink4, 3, 3

i = 0
with rsPOD
while i < count
.AddNew
.Fields(&quot;PurchaseOrderID&quot = Request.Form(&quot;strPONum&quot
.Fields(&quot;ItemCode&quot = Request.Form(&quot;strVenItemCd&quot; & i)
.Fields(&quot;Quantity&quot = csng(Request.Form(&quot;strUnits&quot; & i))
.Fields(&quot;UnitOfMeasure&quot = Request.Form(&quot;strUM&quot; & i)
.Fields(&quot;PricePerUnit&quot = ccur(Request.Form(&quot;strCost&quot; & i))
.Fields(&quot;ExtendedPrice&quot = ccur(Request.Form(&quot;Extended&quot; & i))
.Fields(&quot;Description&quot = Request.Form(&quot;strItemDesc&quot; & i)
.Update
i = i + 1
wend
end with

set rsPOD = nothing

%>
<HTML>
<HEAD>
<META NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;>
</HEAD>
<BODY>

<H1>UPDATE COMPLETE</H1>

</BODY>
</HTML>

The error is as follows:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Errors occurred

/TestProject/LD1PurchOrdAdd.asp, line 60


Line 60 refers to the line where the Description field is being updated in the rsPOD recordset. I cannot find any other problems with this code.

As another aside and as I mentioned in last night's post, I came across something in the MS KnowledgeBase that I thought might be related (Knowledge Base ID = Q261297), but when I tried testing this morning, it seemed to create a never-ending loop until I cut it off. It's not included as part of the code pasted above. If anyone has any suggestions, they are greatly appreciated! Thanks!

 

[spencertaylor]

Ok, just a thought but try changing line 48 I think which reads:

rsPOD.Open strSQL, conLink4, 3, 3

to:

rsPOD.Open strSQL, conLink4, 3, 2

Let me know what happens.

 

[Chopstik]

Ok, tried that, but still getting the same result. I also set it to rsPOD.Open strSQL, conLink4, 1, 3 (which should be CursorType = adOpenKeyset and LockType = adLockOptimistic) and come back with same error.

 

[Chopstik]

Through added use of some response.write statements, I know that the code is iterating through correctly for each item that needs to be added, but appears to end at the &quot;end with&quot; statement after the last item. It then seems to indicate that there is a problem with the update to the Description field (even though it is correctly adding/updating the record) and ends there. I don't know if this is an issue with my code or with my connection to SQL Server 7 or something totally extraneous to all of the above. If anyone has any ideas, please feel free to toss them to me. Thanks!

 

[spencertaylor]

http://www.adopenstatic.com/faq/80040e21.asp

Try this while I look for a few more things

 

[spencertaylor]

another question for you is what does the LD1VBFunction.asp file do and get up to?

 

[spencertaylor]

This looks promising for you:

DB_E_DATAFIELD_OVERFLOW 80040E21 OLE DB Specific:
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Have a look at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adoce31/html/ado30ref_103.asp

 

[Chopstik]

Here's the LD1VBFunction.asp file. Essentially, it creates the connection string(s) and has a couple of functions that can be used throughout the application.

Does that answer your question?

<%
Const strConnectLink4=&quot;Driver={SQL Server};Server=strecnt.suntrust.com;Database=linkdev1;uid=crecapitalbudget;password=crecapitalbudget&quot;
Const strConnect=&quot;Driver={SQL Server};Server=strecnt.suntrust.com;Database=crecapitalbudget;uid=crecapitalbudget;password=crecapitalbudget&quot;

'Return the numeric components of an alpha string
Function vbParseInt(strSource)
Dim strTemp, i, strResult
For i = 1 to Len(strSource)
strTemp = Mid(strSource, i, 1)
If IsNumeric(strTemp) Or strTemp = &quot;.&quot; Or strTemp = &quot;-&quot; Then
strResult = strResult & strTemp
End If
Next
vbParseInt = CLng(strResult)
End Function

'Replace % in query strings with !
Function ReplacePercent(strSource)
ReplacePercent = Replace(strSource, &quot;%&quot;, &quot;!&quot
End Function

'Replace ! in query strings with %
Function ReplaceExclamation(strSource)
ReplaceExclamation = Replace(strSource, &quot;!&quot;, &quot;%&quot
End Function

'Double any single quotes in the input string to avoid confusing SQL
Function DoubleQuotes(strSource)
Dim strTemp, i, strResult
For i = 1 to Len(strSource)
strTemp = Mid(strSource, i, 1)
If strTemp = &quot;'&quot; Then
strResult = strResult & &quot;''&quot;
Else
strResult = strResult & strTemp
End If
Next
DoubleQuotes = strResult
End Function
%>

 

[Chopstik]

I had thought about the data being too much but the field is a varchar with a length of 100 (in SQL Server) and the data being entered is no more than 15-20 characters in length. Thought that maybe spaces in the user entry may also be a problem, but even a single word isn't working correctly. Also, if the entry were too long, would the field still update and then present the error message?

Sorry for the trouble, but I really do appreciate all of your help with this!

 

[spencertaylor]

Why are you connecting to two servers at the top? Can't you put these in an if statement using the filesystem object to check which machine you're on?

Not sure what all the Link4 links are but assuming they're a glitch, you are using the same name to connect to the database.

At least try commenting out one of these as a test.

With the length of the field, have you tried response.write to see the results as they would be entered size wise?

 

[Chopstik]

I assume you're referring to the two servers at the top of the LD1VBFunction.asp include file? Same server, two different databases. Still relatively new to ASP, so have to do some research on an if statement using the FSO, but I can try.

Link4 is the name of the application, ergo the reason being used so often in the application. Not my design, just following someone else's example. But two different connections, strConnectLink4 and strConnect. I'll comment out the one not being used here and see if that fixes it.

Not sure what you mean as far as using the response.write to see how results would be entered size wise. Can you explain? Thanks!

 

[Chopstik]

Another peculiar issue that I thought I'd bring up. If I comment out that particular line, it creates a never ending loop when it is run with all of the values being appended as nulls. May not be relevant, but there's no reason for that to occur, either, that I can see.

 

[spencertaylor]

On your page that updates the database table, comment it's redirect so it stays on that page.

Then add some lines to check the data that is being sent across:

response.write &quot;#&quot; & Request.Form(&quot;strItemDesc&quot; & i) & &quot;#&quot;

the page should show # directly on both sides of the text being entered. If there is a gap then it's sending spaces too.

 

[spencertaylor]

the never ending loop sounds weird. have you checked the value of the count variable?

The other thing is that you are using SQL statements to open the recordsets but you are not using any criteria.

So:

set rsPO = server.CreateObject(&quot;adodb.recordset&quot
strSQL = &quot;SELECT PurchaseOrderID, VendorNumber, VendorDirections, &quot; & _
&quot;BillToID, BillToDirections, ShipToPropertyID, ShipToDirections, &quot; & _
&quot;Buyer, TermsName, ShipViaName, DeliveryDate, CIPProjectNumber, &quot; & _
&quot;Company, CostCenter, Tax, Freight, GLAcctNo, POOrderDate FROM PurchaseOrder&quot;
rsPO.Open strSQL, conLink4, 3, 3

Could become:

set rsPO = server.CreateObject(&quot;adodb.recordset&quot

rsPO.Open &quot;PurchaseOrder&quot;, conLink4, 3, 3

And this would be the same for the other recordset rsPOD

 

[Chopstik]

When it started looping around like that, first thing I looked at was the count variable thinking that maybe it wasn't carrying correctly. However, it is correct and I even put in a response.write command to verify the iterations and that also is correct. It appears to stop directly after the last wend command (after last record has been added) b/c I can't get the response.write to work after that command, only the error message. Says to me that the iteration is working, just something after that and it's pointing back to that one line.

As far as the SQL, you're saying I could open the table directly without having to go through the strSQL line? This would have the same effect or something different? I'll play with it and see.

 

[spencertaylor]

One more thing you've just made me remember! Don't know why I suddenly thought this but try it anyway ;o)

After the update do a moveprevious then movenext

 

[Chopstik]

You mean after each iteration?