Solved

Multiple-step OLE DB operation generated errors.

Posted on 2001-06-19
2
246 Views
Last Modified: 2008-02-01
' This is the error message
'Function add user prefs
'Error Number -2147217887
'Error Source Object Microsoft OLE DB Provider for SQL Server
'Error Description Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

'Any suggestion is appreciated








<!--#include file="_common_inc.asp"-->

<%

response.buffer=True
If not application("debug") Then
      on error resume next
End If

'*** constants
const lblLastFirstErr = 208
const lblUserNameErr = 209
const lblPasswordItemErr = 210
const lblPasswordSameErr = 211
const lblUserNameDupe = 303


'*** get edit/add request variables
crypt = request("crypt")
if crypt ="" then
crypt = Request.QueryString("crypt")
End If
if sLastPage = "" then
sLastPage = Request.querystring("LastPage")
end if
If sMode = "" Then
sMode = Request.querystring("mode")
End If
'if instr(sLastPage,"asp?") =0  Then
'sLastPage = sLastPage &"?crypt="&crypt
'else
'sLastPage = sLastPage &"&crypt="&crypt
'end if
'Response.Write LastPageURL
bSave=trim(request("bSave"))
bCancel=trim(request("bCancel"))
iUserID = Request.Form("UserID")
sUserName = trim(Request.Form("UserName"))
sPassword = trim(Request.Form("Password"))
sRePassword = trim(Request.Form("RePassword"))
sLastName = trim(Request.Form("LastName"))
sFirstName = trim(Request.Form("FirstName"))
sEmail = trim(Request.Form("Email"))
sAdmin = Request.Form("Admin")
iGroupId = Request.Form("Group")
sActive = Request.Form("Active")


If bCancel ="" Then

'*** Add Contact to Database
            ' Check for required Fields
            If sLastName="" or sFirstName=""  Then
                  formerror = LoadString(lblLastFirstErr)
                  errorflag = true
            End If
            If sUserName="" Then
                  formerror = LoadString(lblUserNameErr)
                  errorflag = true
            End If
            If sPassword ="" or sRePassword ="" Then
                  formerror = LoadString(lblPasswordItemErr)
                  errorflag = true
            End If
            If sPassword <> sRePassword Then
                  formerror = LoadString(lblPasswordSameErr)
                  errorflag = true
            End If
            
            If iUserID = "" Then
                  'Check for duplicate UserName
            
                  set oRs = CreateObject("ADODB.Recordset")
                  '***Begin Check Error
                  if Not Err.Number=0 then Call HandleErrors(ADD_BASIC,"add_user.asp - Duplicate UserName Check")
                  '***End Check Error      if List.EOF then

                   sSQL = "Select * from Users where UserName='"&sUserName&"'"
                   oRs.Open sSQL, application("CONNECTIONSTRING"),adOpenStatic,adLockReadOnly

                   If oRs.Recordcount > 0 Then
                        formerror = LoadString(lblUserNameDupe)
                        errorflag = true
                        oRs.Close
                   End if
                   Set oRs = Nothing            
             End IF
            
            If errorflag then
            %>      
                  <!--#include file="createuser.asp"-->
            <%      
            
            Else ' Check for ADD or UPDATE record      
            set oRs = CreateObject("ADODB.Recordset")
            '***Begin Check Error
            if Not Err.Number=0 then Call HandleErrors(ADD_BASIC,"add basic user")
            '***End Check Error      if List.EOF then

            If iUserID <>""  Then ' Update
               
             sSQL = "Select * from Users where UserID="&Cstr(iUserID)
             oRs.Open sSQL, application("CONNECTIONSTRING"),adOpenKeyset,adLockOptimistic
                         
            '***Begin Check Error
            if Not Err.Number=0 then Call HandleErrors(ADD_USR,"add to Users")
            '***End Check Error      if List.EOF then

            Else ' Add
            bAddUser = True ' Set Flag to add children
            oRs.Open "Select * From Users", application("CONNECTIONSTRING"), adOpenKeyset,adLockOptimistic
            '***Begin Check Error
            if Not Err.Number=0 then Call HandleErrors(ADD_BASIC,"add to Users")
            '***End Check Error      if List.EOF then
            
            oRs.AddNew
            
            End If ' Add or Update
            
            oRs("UserName")            = replace(sUserName,"'","")
            oRs("Password")            = sPassword
            oRs("LastName")            = replace(sLastName,"'","`")
            oRs("FirstName")      = replace(sFirstName,"'","`")
            oRs("Email")            = replace(sEmail,"'","`")
        oRs("Admin")        = convertbool(sAdmin)
        oRs("Active")        = convertbool(sActive)
        oRs("LastUpdate")   = now()            
        oRs.Update
        storeUserID = oRs("UserID")
        oRs.Close
        'Response.write "UserID:"&iUserID&" GroupID:"&iGroupID
   
        If bAddUser  Then ' Not an Update
       

        'Add User Prefs Defaults
            oRs.Open "Select * From UserPrefs", application("CONNECTIONSTRING"),adOpenKeyset,adLockOptimistic

            '***Begin Check Error
            if Not Err.Number=0 then Call HandleErrors(ADD_BASIC,"add basic user prefs")
            '***End Check Error      if List.EOF then
            oRs.AddNew
            oRs("UserId")=storeUserID
            oRs("ViewType")= "day"
            oRs("GridInterval")=application("interval")
            oRs("StartTime")=application("starttime")
            oRs("EndTime")=application("endtime")            
            oRs("TimeZone")=application("TimeZone")
            if Ucase(application("db"))="ACCESS" Then
                  oRs("Display") = True
            else      'SQL
                  oRs("Display") = 1
            end if
            oRs("updated") = now()
            oRs.Update
            if Not Err.Number=0 then Call HandleErrors(Add_User,"add user prefs")
            oRs.Close             


       
        End If 'bAddUser



       
               
                   
        If bAddUser Then ' Add Group
                  If iGroupID <> "" Then
                  
                  oRs.Open "Select * From UserGroupXref", application("CONNECTIONSTRING"),adOpenKeyset,adLockOptimistic                  
                  '***Begin Check Error
                  if Not Err.Number=0 then Call HandleErrors(ADD_BASIC,"add UserGroupXref")
                  '***End Check Error      if List.EOF then
                  oRs.AddNew
                  oRs("UserId")=storeUserID
                  oRs("GroupId")= cint(iGroupId)
                  oRs.Update
                  if Not Err.Number=0 then Call HandleErrors(Add_User,"add user groupxref-add")
                  oRs.Close             
                  
                  End If  ' GroupID not null
        Else ' Update Group
                    sSQL = "Select UserID, GroupID from UserGroupXref where UserID="&iUserID
                    oRs.Open sSQL, application("CONNECTIONSTRING") ,adOpenKeyset,adLockOptimistic
                    If not oRs.EOF Then
                          If iGroupID <> "" Then
                                oRs("GroupId") = cint(iGroupID)
                                oRs.Update
                                if Not Err.Number=0 then Call HandleErrors(Add_User,"update user groupxref-update")
                                oRs.Close
                          Else 'Changed from existing group to No Group
                                oRs.Close
                                sSQL = "Delete from UserGroupXref where UserID="&iUserID
                                oRs.Open sSQL, application("CONNECTIONSTRING") ,adOpenKeyset,adLockOptimistic
                                if Not Err.Number=0 then Call HandleErrors(Add_User,"update user groupxref-Chg to NULL")
                               
                         End If
                    
                    else  'no record already, Add to UserGroupXref
                          if iGroupID<> "" Then
                                oRs.AddNew
                              oRs("UserId")=iUserID
                              oRs("GroupId")= cint(iGroupId)
                              oRs.Update
                              if Not Err.Number=0 then Call HandleErrors(Add_User,"add user groupxref-update/add")
                        End If 'iGroupID
                        oRs.Close             
                  
                    
                    End If
     
        End If  ' Add or Update
                 
       
       
        Set oRs = Nothing
                  'Response.write sLastPage
                  Response.Redirect(sLastPage)
        End If ' Required Field Check
       
        Else  ' Cancel Button Hit
                Response.Redirect(sLastPage)
        End If ' Cancel Button hit
   
 
            
 
   
       
       
%>

0
Comment
Question by:iyiola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 1

Accepted Solution

by:
marounk earned 20 total points
ID: 6209160
hi

This error is generated in 2 situtations :

1 - you are trying to insert a string,or char in a field that is of type for example int or float

2 - you are trying to insert a value that is bigger than its target size field

for example :
You are trying to insert the string "experts-exchange" into a field of type varchar(5) which is obviously bigger than the field.

0
 

Author Comment

by:iyiola
ID: 6211478
Very helpful
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question