HELP!! Can''t post records to database!

• Anthony Phillips-301303

  Mr or Mrs. 500

  Points: 574

  More actions

  April 21, 2006 at 8:38 am

  #167855

  Hello, I hope someone can help me with this problem I'm having. I can't seem to post multiple records to my SQL DB. Below is the error and the important parts of the code I'm using in my two pages. This is in ASP VBScript by the way. This has worked in the past so I'm at a loss as to why it's doesn't work now.

  Error Message:

• Error Type:

  Microsoft OLE DB Provider for SQL Server (0x80040E57)

  String or binary data would be truncated.

  /MySubmitPageExceptionsSQLServerB.asp, line 135

  -----------------------------------------------------------------------------------------

Page 1

Code of Posting Page

<%

'queue up to the top record of the recordset

     If Not rs_users.EOF Then rs_users.MoveFirst

    

'make a temporary variable for user id because the less typing the better

dim t_id, t_idDate

'here I loop through all the records outputting a row for each one

     Do Until rs_users.EOF

     t_id = rs_users("ClientCode")

     t_idDate = Session("ChangeDate")

%>

Start of HTML. Here I'm getting the auto numeber of each record and attaching it using <%=t_id%>. I'll explain why in page 2

 <!-- Here is the id for this loop -->

             <input type="hidden" name="ua_id" value="<%=t_id%>">

<input type="Hidden" name="txtBoxNumber_<%=t_id%>" value="<%=rs_users("ClientBoxNumber")%>" size="9" maxlength="30">

---------------------------------------------------------------------------------

Page 2

Page the handles the post

'Here I have a function to reduce the number of times we have to type Request.Form and Replace functions

Function CleanInput(strReqName)

CleanInput = Replace(Request.Form(strReqName),"'","''")

End Function

'---------------------------Make a connection to the db--------------------------------------------------------

    Dim sql_users,rs_users,cmdDC,t_id,TheMessage, arr_ids

               

                set Conn = Server.CreateObject("ADODB.Connection")

    Conn.Open "Provider=sqloledb; Data Source=REMITCOCLTVOL; Initial Catalog=Billing;Integrated Security=SSPI;"

                sql_users = "SELECT * FROM tblImportedVolume" 

                Set rs_users = conn.Execute(sql_users)

'--------------------------------------------------------------------------------------------------------------

'Here you can see why I attached the autonumber of each record in page 1. This allows me to us a For Loop to post all the records to the db from page 1. 

arr_ids = Split(Request.Form("ua_id"),", ")

'now I loop through each id, build the sql, and execute the sql

Dim id

For Each id in arr_ids

sql_update = "INSERT INTO tblImportedVolume (ClientCode, Correspondence, Unbankables, PostalCards,VolDate, Attempts, Lookups, Returns, ExtractOnly, Shift) VALUES ('" & CleanInput("txtCustomerName_" & id) & _

"','" & CleanInput("txtCorrespondence_" & id) & _

"','" & CleanInput("txtUnbankables_" & id) & _

"','" & CleanInput("txtPostal_" & id) & _

"','" & CleanInput("txtDated_" & id) & _

"','" & CleanInput("txtAttempts_" & id) & _

"','" & CleanInput("txtLookups_" & id) & _

"','" & CleanInput("txtReturns_" & id) & _

"','" & CleanInput("txtExtract_" & id) & _

"','" & CleanInput("txtShifts_" & id) & "');"

'Make it so!

conn.Execute sql_update