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
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'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.
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.
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.
[RESOLVED] I need help.
Reply With Quote
Bookmarks