Solved

Multiple-step OLE DB operation generated errors.

Posted on 2001-06-19
2
241 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
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
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/…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now