저기 질문 올린사람인데요 ^^; 좀 의문가는 점이 있어서요
아래 함수들은 제가 디버깅 할때 쓰는건데요. 다른거와 섰여있습니다.
두가지 상황에 따라서 결과가 틀려서요? 초보라서 잘모릅니다. 위에글 읽기도 힘들어요;; ㅠㅜ
◆참고
asp 에서 사용되는 db 핸들입니다
◆소스
- ▲함수 1
'필드명과 값만확인할대 :: 개발용
Function RunSQLReturnGR_fieldName(strSQL,maxLength)
On Error Resume Next
Dim Rs , Cmd
Set Rs = Server.CreateObject("ADODB.Recordset")
Set Cmd = Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection = Conn
Cmd.CommandText = strSQL
Cmd.CommandType = adCmdText
Cmd.Prepared = True
Rs.CursorType = adOpenForwardOnly
Rs.CursorLocation = adUseClient
Rs.LockType = adLockReadOnly
Rs.Open Cmd
Call ViewParam(Cmd,maxLength)
Set Cmd = Nothing
If Err.Number <> 0 Then
Call errorLog_write("RunSQLReturnGR_fieldName",strSQL)
End if
End function
- ▲함수 2
'레코드셋 필드명과 내용함께 출력하기(아웃풋용) :: 개발용
Sub ViewParam(byref objCmd,maxLength)
'On Error Resume Next
Redim strOutputParamName(objCmd.Parameters.Count) , execStr(objCmd.Parameters.Count)
Dim param , i , j
i = 0 : j = 0
If maxLength = true Or maxLength = false Then
If objCmd.CommandType = adCmdStoredProc Then
Response.Write "<table border=1><tr><td><b>속성</b></td><td><b>값</b></td><td><b>사이즈</b></td><td><b>입력타입</b></td><td><b>변수타입</b></td><td><b>정밀도</b></td></tr>"
For Each Param In objCmd.Parameters
If Param.Name <> "@RETURN_VALUE" Then
If (GetParameterDirectionEnum(Param.Direction) = adParamOutput) OR (GetParameterDirectionEnum(Param.Direction) = adParamInputOutput) Then
strOutputParamName(j) = Param.Name
execStr(i) = "''"
j = j + 1
Else
execStr(i) = "'"& Param.Value &"'"
Response.Write "<tr><td><font color=orange>"& i &"번째 "& Param.Name &"</font></td><td>"& Param.Value &"</td><td>"& Param.Size &"</td><td>"& GetParameterDirectionEnum(Param.Direction) &"</td><td>"& GetDataTypeEnum(Param.Type) &"</td><td>"& Param.Precision &"</td></tr>"
End if
i = i + 1
End if
Next
Response.Write "<tr><td colspan=6><font color=red><b>아웃풋</b></font></td>"
For j = 0 To j - 1
Response.Write "<tr><td><font color=orange>"& j &"번째 "& strOutputParamName(j) &"</font></td><td>"& objCmd.Parameters(strOutputParamName(j)).Value &"</td><td>"& objCmd.Parameters(strOutputParamName(j)).Size &"</td><td>"& GetParameterDirectionEnum(objCmd.Parameters(strOutputParamName(j)).Direction) &"</td><td>"& GetDataTypeEnum(objCmd.Parameters(strOutputParamName(j)).Type) &"</td><td>"& objCmd.Parameters(strOutputParamName(j)).Precision &"</td></tr>"
Next
Response.Write "<tr><td colspan=6><font color=red><b>프로시저 실행문</b></font></td></tr>"
Response.Write "<tr><td colspan=6><font color=orange>Exec "& Mid(objCmd.CommandText, Instr(objCmd.CommandText,"call ")+5 , Instr(objCmd.CommandText,"(") - (Instr(objCmd.CommandText,"call ")+5) ) &"</font> "& arrFormat(execStr,","," ") &"</td></tr>"
Response.Write "</table><br>"
End if
End if
Dim Rs
Set Rs = Server.CreateObject("ADODB.RecordSet")
Rs.CursorType = adOpenForwardOnly
Rs.CursorLocation = adUseClient
Rs.LockType = adLockReadOnly
Rs.Open objCmd
Response.Write Rs_fieldName(Rs,maxLength)
If objState(Rs.State) = "adStateOpen" Then <=================== 이 부분
Rs.Close
Set Rs = Nothing
End if
If Err.Number <> 0 Then
Call errorLog_write("ViewParam",Mid(objCmd.CommandText, Instr(objCmd.CommandText,"call ")+5 , Instr(objCmd.CommandText,"(") - (Instr(objCmd.CommandText,"call ")+5) ))
Set objCmd = Nothing
Else
Set objCmd = Nothing
End if
End Sub
- ▲함수 3
'레코드셋 필드명과 내용함께 출력하기 :: 개발용
Function Rs_fieldName(objRecordSet , maxLength)
Dim Field , tempStr , i , j , k
If maxLength = false Then
Exit function
End if
tempStr = ""
Dim objRecordSet_temp
Set objRecordSet_temp = objRecordSet
Do Until objRecordSet_temp Is nothing
i = 0 : j = 0 : k = 0
tempStr = tempStr &"<table border=1><tr>"
For Each Field in objRecordSet_temp.Fields
tempStr = tempStr &"<td><b>"& Field.name &" <br><font color=red>"& i &"열</font></b></td>"
i = i + 1
Next
tempStr = tempStr &"</tr>"
If objState(objRecordSet_temp.State) = "adStateClosed" Then
tempStr = tempStr &"</table>"
Rs_fieldName = tempStr
Exit function
End if
If objRecordSet_temp.Eof OR objRecordSet_temp.Bof Then
tempStr = tempStr &"<tr><td colspan="& i &"><b>결과없음</b></td></tr>"
Else
Do Until objRecordSet_temp.Eof
tempStr = tempStr &"<tr>"
For Each Field in objRecordSet_temp.Fields
tempStr = tempStr &"<td valign=top><font color=orange>"& j &"행 "& k &"열</font><br><br>"& Field &"</td>"
k = k + 1
next
tempStr = tempStr &"</tr>"
objRecordSet_temp.MoveNext
j = j + 1
k = 0
If isNumeric(maxLength) Then
If Cint(j) = Cint(maxLength) Then Exit do
End if
Loop
objRecordSet_temp.MoveFirst
End if
tempStr = tempStr &"</table><br><br>"
Set objRecordSet_temp = objRecordSet_temp.NextRecordset
Loop
Rs_fieldName = tempStr
End function
- ▲함수 4
'레코드셋 필드명과 내용함께 출력하기 :: 개발용
Function Rs_fieldName(byVal objRecordSet , maxLength)
Dim Field , tempStr , i , j , k
If maxLength = false Then
Exit function
End if
tempStr = "<table cellpadding=0 cellspacing=0 border=1><tr>"
i = 0 : j = 0 : k = 0
For Each Field in objRecordSet.Fields
tempStr = tempStr &"<td><b>"& Field.name &"</b><br><font color=red>"& i &"열</font></td>"
i = i + 1
Next
tempStr = tempStr &"</tr>"
If objRecordSet.State = 0 Then
tempStr = tempStr &"</table>"
Rs_fieldName = tempStr
Exit function
End if
If objRecordSet.Eof Then
tempStr = tempStr &"<tr><td colspan=100>자료가 없습니다</td></tr>"
Else
Do Until objRecordSet.Eof
tempStr = tempStr &"<tr>"
For Each Field in objRecordSet.Fields
tempStr = tempStr &"<td valign=top><font color=orange>"& j &"행 "& k &"열</font><br><br>"& Field &"</td>"
k = k + 1
next
tempStr = tempStr &"</tr>"
objRecordSet.MoveNext
j = j + 1
k = 0
If isNumeric(maxLength) Then
If Cint(j) = Cint(maxLength) Then Exit do
End if
Loop
objRecordSet.MoveFirst
End if
tempStr = tempStr &"</table>"
Rs_fieldName = tempStr
End function
◆요약
rs 를 제가 원하는 식으로 출력후 rs 를 원상 복귀 시키는거조 그래서야 정상적인 값이 나올테니까요
◆질문 1
함수 1을 사용해서 결과를 출력하면 잘됩니다;;
그런데 함수 3을 사용하면
ADODB.Recordset error '800a0e78'
개체가 닫혀있으면 작업이 허용되지 않습니다.
ㅌㅌㅌㅌ.asp, line 591
그래서
함수 3대신 함수 4를 쓰면
잘됩니다;; 왜 그런걸까요?
sql = sql & "ORDER BY IDX DESC "
Function rs_open(sql)
Set rs = Server.CreateObject("Adodb.Recordset")
rs.open sql, db, 1
End Function
Call rs_open(sql)
'print dbs.RunSQLReturnGR_fieldName(sql,true) <--------------- 정상 ( 이건 새로운 rs 를 생성하니 그럴수도 있다 생각은 됩니다.)
'print Rs_fieldName(rs,true) <--------------- 정상(이건 왜 그럴까요?)
response.write dbs.rs_fieldName(rs,true) <--------------- 비정상
rs.pagesize = pSize
IntArecord = rs.recordcount <------ 이부분에서 요류가 나내요
IntApage = rs.pagecount
◆질문 2
더 이상한건 함수 3의 상화에 따라서 함수 2의 개체 처리 부분이 이상합니다. 아직 개념이 안서서 ^^
If objState(Rs.State) = "adStateOpen" Then <=================== 이 부분
Rs.Close
Set Rs = Nothing
End if
' If Not(Rs is nothing) Then
' Rs.Close
' Set Rs = Nothing
' End if
두개의 처리 구문이 서로 다른 결과를 나타 냅니다.