www.webdeveloper.com
Results 1 to 4 of 4

Thread: [RESOLVED] I need help.

  1. #1
    Join Date
    Nov 2005
    Posts
    109

    resolved [RESOLVED] I need help.

    I don't know what to do anymore. I got an error from the first minute of what is going to seem as a long day. I tried something to fix that error and got a new error and that's pretty much my first hour and a half of the day. Anyway, here is the error I'm getting now:

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "IPRINT.PCK_IPRINT_READ", line 211 ORA-06512: at line 1

    And I'll give all necessary information now:

    ASP.NET
    Code:
     Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'Put user code to initialize the page here
    
        End Sub
    
        Private Sub FillDatagrid(Optional ByVal build As String = "", Optional ByVal fl As String = "", Optional ByVal dept As String = "", Optional ByVal rm As String = "")
    
            Dim ar As ArrayList = New ArrayList
            Dim ds As DataSet
            Dim objPDS As PagedDataSource = New PagedDataSource
            Dim lang
    
            If Request.ServerVariables("HTTP_ACCEPT_LANGUAGE") = "en-us" Or Request.ServerVariables("HTTP_ACCEPT_LANGUAGE") = "en-CA" Then
                lang = "English"
            Else
                lang = "French"
            End If
    
            ar.Add(-1)
            ar.Add(build)
            ar.Add(fl)
            ar.Add(dept)
            ar.Add(rm)
            ar.Add(lang)
            ar.Add(DBNull.Value)
    
            ds = mObjdata.ExecSPReturnDS("PCK_IPRINT_READ.SP_SEARCH_PRINTER_READ", ar)
    
            objPDS.DataSource = ds.Tables(0).DefaultView
            objPDS.AllowPaging = True
            objPDS.PageSize = 10
    
            Dim curPage As Integer
    
            If Request.QueryString("page") <> "" Then
                curPage = CInt(Request.QueryString("page"))
            Else
                curPage = 1
            End If
    
            objPDS.CurrentPageIndex = curPage - 1
    
            lblCurrentPage.Visible = True
            lblCurrentPage.Text = "Page: " + curPage.ToString()
    
            If (Not objPDS.IsFirstPage) Then
                lnkPrev.Visible = True
                lnkPrev.NavigateUrl = Request.CurrentExecutionFilePath + "?Page=" + Convert.ToString(curPage - 1)
            End If
    
            If (Not objPDS.IsLastPage) Then
                lnkNext.Visible = True
                lnkNext.NavigateUrl = Request.CurrentExecutionFilePath + "?Page=" + Convert.ToString(curPage + 1)
            End If
    
            rptIPrint.DataSource = objPDS
            rptIPrint.DataBind()
    
        End Sub
    
        Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
            If rbBuilding.Checked = False And rbFloor.Checked = False And rbDept.Checked = False And rbRoom.Checked = False Then
                lblError.Text = "Please select what you are searching for using the radio buttons."
            ElseIf rbBuilding.Checked = True Then
                If txtSearch.Text = "" Then
                    lblError.Text = "Please enter the building you are lookig for in the text box."
                Else
                    FillDatagrid(Trim(txtSearch.Text), "", "", "")
                    rbBuilding.Checked = True
                    txtSearch.Text = txtSearch.Text
                End If
            ElseIf rbFloor.Checked = True Then
                If txtSearch.Text = "" Then
                    lblError.Text = "Please enter the floor you are looking for in the text box."
                Else
                    FillDatagrid("", txtSearch.Text, "", "")
                    rbFloor.Checked = True
                    txtSearch.Text = txtSearch.Text
                End If
            ElseIf rbDept.Checked = True Then
                If txtSearch.Text = "" Then
                    lblError.Text = "Please enter the department you are looking for in the text box."
                Else
                    FillDatagrid("", "", txtSearch.Text, "")
                    rbDept.Checked = True
                    txtSearch.Text = txtSearch.Text
                End If
            ElseIf rbRoom.Checked = True Then
                If txtSearch.Text = "" Then
                    lblError.Text = "Please enter the room you are looking for in the text box."
                Else
                    FillDatagrid("", "", "", txtSearch.Text)
                    rbRoom.Checked = True
                    txtSearch.Text = txtSearch.Text
                End If
            End If
        End Sub
    Stored Procedure:
    Code:
    <StoredProcedure name="PCK_IPRINT_READ.SP_SEARCH_PRINTER_READ">
    		<Parameters>
    			<Parameter name="p_PRINTER_ID" size="3" datatype="NUMBER" direction="spParamInput" isNullable="false" />
    			<Parameter name="p_BUILDING_NAME" size="20" datatype="VARCHAR2" direction="spParamInput" isNullable="false" />
    			<Parameter name="p_FLOOR_ID" size="3" datatype="VARCHAR2" direction="spParamInput" isNullable="false" />
    			<Parameter name="p_DEPT_ID" size="3" datatype="VARCHAR2" direction="spParamInput" isNullable="false" />
    			<Parameter name="p_ROOM_ID" size="3" datatype="VARCHAR2" direction="spParamInput" isNullable="false" />
    			<Parameter name="p_LANG" size="10" datatype="VARCHAR2" direction="spParamInput" isNullable="false" />
    			<Parameter name="p_PRINTER_CUR" size="1000" datatype="T_CURSOR" direction="spParamOutput" isNullable="false" />
    		</Parameters>
    	</StoredProcedure>
    Oracle Procedure:
    Code:
    PROCEDURE SP_SEARCH_PRINTER_READ(p_LANG in VARCHAR2, p_PRINTER_ID IN NUMBER, p_BUILDING_NAME IN VARCHAR2, p_FLOOR_ID IN VARCHAR2, 
    p_DEPT_ID IN VARCHAR2, p_ROOM_ID IN VARCHAR2, p_PRINTER_CUR OUT T_CURSOR) IS
    
    /*
      FUNCTION NAME : SP_SEARCH_PRINTER_READ.
      THIS FUNCTION WILL READ THE PRINTER ITEMS ACCORDING TO WHAT WAS SEARCHED FOR
      AND RETURN A CURSOR.
      CREATED BY  : G. SHEAVES
      DATE       : 2006-03-29.
      NOTES     : 
    */
    
    BEGIN
    
    	 IF p_BUILDING_NAME > 0 THEN
    	 	OPEN p_PRINTER_CUR FOR
    			 SELECT P.PRINTERNAME, B.BUILDINGNAME, F.FLOORNAME, D.DEPARTMENTNAME, R.ROOMNAME
    			 FROM PRINTER P, BUILDING B, FLOOR F, DEPARTMENT D, ROOM R, PRINTERLOCATION PL
    			 WHERE PL.LANGUAGE = p_LANG
    			 AND UPPER(B.BUILDINGNAME) LIKE UPPER('%'||p_BUILDING_NAME||'%')
    			 AND P.PRINTERID = PL.PRINTERID
    			 AND B.BUILDINGID = PL.BUILDINGID
    			 AND F.FLOORID = PL.FLOORID
    			 AND D.DEPARTMENTID = PL.DEPARTMENTID
    			 AND R.ROOMID = PL.ROOMID
    			 ORDER BY PL.PRINTERID;
    	  ELSIF p_FLOOR_ID <> '' THEN		 
    	  	   OPEN p_PRINTER_CUR FOR
    			 SELECT P.PRINTERNAME, B.BUILDINGNAME, F.FLOORNAME, D.DEPARTMENTNAME, R.ROOMNAME
    			 FROM PRINTER P, BUILDING B, FLOOR F, DEPARTMENT D, ROOM R, PRINTERLOCATION PL
    			  WHERE PL.LANGUAGE = p_LANG
    			 AND UPPER(F.FLOORNAME) LIKE UPPER('%'+p_FLOOR_ID+'%') 
    			 AND P.PRINTERID = PL.PRINTERID
    			 AND B.BUILDINGID = PL.BUILDINGID
    			 AND F.FLOORID = PL.FLOORID
    			 AND D.DEPARTMENTID = PL.DEPARTMENTID
    			 AND R.ROOMID = PL.ROOMID
    			 ORDER BY PL.PRINTERID;
    		ELSIF p_DEPT_ID <> '' THEN		 
    	  	   OPEN p_PRINTER_CUR FOR
    			 SELECT P.PRINTERNAME, B.BUILDINGNAME, R.ROOMNAME, F.FLOORNAME, D.DEPARTMENTNAME
    			 FROM PRINTER P, BUILDING B, FLOOR F, ROOM R, DEPARTMENT D, PRINTERLOCATION PL
    			  WHERE PL.LANGUAGE = p_LANG
    			 AND UPPER(D.DEPARTMENTNAME) LIKE UPPER('%'+p_DEPT_ID+'%') 
    			 AND P.PRINTERID = PL.PRINTERID
    			 AND B.BUILDINGID = PL.BUILDINGID
    			 AND F.FLOORID = PL.FLOORID
    			 AND D.DEPARTMENTID = PL.DEPARTMENTID
    			 AND R.ROOMID = PL.ROOMID
    			 ORDER BY PL.PRINTERID;
    		ELSIF p_ROOM_ID <> '' THEN		 
    	  	   OPEN p_PRINTER_CUR FOR
    			 SELECT P.PRINTERNAME, B.BUILDINGNAME, F.FLOORNAME, R.ROOMNAME, D.DEPARTMENTNAME
    			 FROM PRINTER P, BUILDING B, FLOOR F, ROOM R, DEPARTMENT D, PRINTERLOCATION PL
    			  WHERE PL.LANGUAGE = p_LANG
    			 AND UPPER(R.ROOMID) LIKE UPPER('%'+p_ROOM_ID+'%') 
    			 AND P.PRINTERID = PL.PRINTERID
    			 AND B.BUILDINGID = PL.BUILDINGID
    			 AND F.FLOORID = PL.FLOORID
    			 AND D.DEPARTMENTID = PL.DEPARTMENTID
    			 AND R.ROOMID = PL.ROOMID
    			 ORDER BY PL.PRINTERID;
    		ELSIF p_PRINTER_ID > -1 THEN		 
    	  	   OPEN p_PRINTER_CUR FOR
    			 SELECT P.PRINTERNAME, B.BUILDINGNAME, F.FLOORNAME, R.ROOMNAME, D.DEPARTMENTNAME
    			 FROM PRINTER P, BUILDING B, FLOOR F, ROOM R, DEPARTMENT D, PRINTERLOCATION PL
    			  WHERE PL.LANGUAGE = p_LANG
    			 AND UPPER(P.PRINTERNAME) LIKE UPPER('%'+p_PRINTER_ID+'%') 
    			 AND P.PRINTERID = PL.PRINTERID
    			 AND B.BUILDINGID = PL.BUILDINGID
    			 AND F.FLOORID = PL.FLOORID
    			 AND D.DEPARTMENTID = PL.DEPARTMENTID
    			 AND R.ROOMID = PL.ROOMID
    			 ORDER BY PL.PRINTERID;
    					
    	END IF;
    		
    END SP_SEARCH_PRINTER_READ;
    Sorry if t's a lot to read, but I really need help with this. The guys I look to for help here are all stuck in meetings all day.

    I thank you for all help.

  2. #2
    Join Date
    Nov 2005
    Posts
    109

    New Error

    I've made a couple of minor changes and am now getting this error:

    ORA-24338: statement handle not executed

    which is the error I was originally getting when I started today.

    I really need to get this done. To finish the project I'm working on I just need to finish this page and fix a couple of other bugs that I have posted.

    http://www.webdeveloper.com/forum/sh...d.php?t=103798

    http://www.webdeveloper.com/forum/sh...d.php?t=104401

  3. #3
    Join Date
    Nov 2005
    Posts
    109
    Not sure if this info is needed but I'm working off of Win2000PRO and Oracle8i client and Oracle9i server. I can't seem to find the exact version of the oracles on my machine but I will try to find out.

  4. #4
    Join Date
    Nov 2005
    Posts
    109

    Problem solved

    I finally found someone here to help me and we upgraded my oracle to 9i. We still got the error but after he looked at my oracle procedure he discovered a syntax error. This line:

    Code:
    AND UPPER(B.BUILDINGNAME) LIKE UPPER('%'+p_BUILDING_ID+'%')
    should look like this:

    Code:
    AND UPPER(B.BUILDINGNAME) LIKE '%'||UPPER(p_BUILDING_ID)||'%'
    I still have two other bugs to fix before this project is finished. PLease help me with them.

    Thank you.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles