Retrieving records after upsizing database

misskaos · October 4th, 2006, 09:08 AM

I recently upsized a microsoft access database to a SQL server database. The Access database was connected to a website (in which the information for the database was being inserted, updated, and retrieved) so I had to change a few things to get the data connected to the SQL database.

Okay, here is my problem. I have the records retrieving the records and updating them (if necessary) on the "viewall" page. But on the FORMS page, the information will reset but it will not post the new form information to the database.

I am not receiving an error message.

I will post the two pages relevant to the situation (PostNew1.asp and clsUpload1.asp)

I believe the problem is in the clsUpload, but I can't figure it out. I've never called classes from another page like that, but I'm sure with me upsizing the database that that is what the problem is.

Here is the code:

PostNew1.asp

<%
if Session("UserID") = "" then
  Response.Redirect("index1.asp")
end if
%>

<%
if Session("UserType") <> "originator" then
  Response.Redirect("noPostNew1.asp")
end if
%>

<html>
<head>

<title>Post New ECP/IMI Impact Form</title>

<script language = "VBScript">

</script>

</head>
<body bgcolor="#ABCDEF">
<table width="750" border="0">
<tr>
<td width="750" colspan="15"><img src="banner.png"></td>
</tr>
</table>
<table width="750" border="0">
<tr>
<td width="100" colspan="2" bgcolor="#ABCEDF" align="center">

<a href="postNew1.asp">
Post New
</a>

</td>
<td width="100" colspan="2" bgcolor="#ABCEDF" align="center">

<a href="viewAll1.asp">
View All
</a>

</td>
<td width="100" colspan="2" bgcolor="#ABCEDF" align="center">

<a href="search1.asp">
Search
</a>

</td>
<td width="100" colspan="2" bgcolor="#ABCEDF" align="center">

<a href="changePassword1.asp">
Password
</a>

</td>
<td width="100" colspan="2" bgcolor="#ABCEDF" align="center">

<a href="contact1.asp">
Contact
</a>

</td>
<td width="100" colspan="2" bgcolor="#ABCEDF" align="center">

<a href="login1.asp">
Logout
</a>

</td>
<td width="100" colspan="2" align="center">

</td>
<td width="50" colspan="1" align="center">

</td>
</tr>
<tr>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
<td width="50"> </td>
</tr>

</table>

<table width="750">
<tr>
<td width="750" colspan="15" align="center"><h2>New V22 ECP/IMI Impact Form</h2>
</td>
</tr>
</table>
<table width="750" border="0">
<form name="newEcpForm" method="post" action="postNew1.asp" enctype="multipart/form-data">
<tr>
<td width="200" colspan = "4" align="right">

ECP Number

</td>
<td width="550" colspan = "11">
<input type="text" name="ecpNumber" size="30" value= <%=textField1%> >
</td>
</tr>
<tr>
<td width="200" colspan = "4" align="right">

ECP Title

</td>
<td width="550" colspan = "11">
<input type="text" name="ecpTitle" size="70" value="">
</td>
</tr>
<tr>
<td width="200" colspan = "4" align="right">

ECP Filename

</td>
<td width="550" colspan = "11">
<input type="file" name="ecpFilename" size="60">
<br>

(Please ensure the adobe filename

does not have spaces.

You may use underscores.)

</td>
</tr>
<tr>
<td width="200" colspan = "4" align="right">

Submitted by:

</td>
<td width="550" colspan = "11"><%=Session("UserFullName")%>
<input type="hidden" name="ecpOriginator" size="30" value=<%=Session("UserFullName")%> readonly = "readonly" style="background-color: #abcedf;">
</td>
</tr>
<tr>
<td width="200" colspan = "4" align="right">

Date Distributed

</td>
<td width="550" colspan = "11"><%=CStr(date())%>
</td>
</tr>
<tr>
<td width="200" colspan = "4" align="right">

Date Due

</td>
<td width="550" colspan = "11">
<input type="text" name="ecpDateDue" value = "" size = "30">
</td>
</tr>
<tr>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
</tr>
<tr>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50">
<input type="submit" name="submitButton" value="Submit">
</td>
<td width = "50">
<input type="reset" name="resetButton" value="Reset">
</td>
<td width = "50">
<button>Cancel</button>
</td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
<td width = "50"> </td>
</tr>
</table>
</form>

<br><br>
<br><br>
</td>
</tr>
</table>

<%

set o = new clsUpload
if o.Exists("submitButton") then

  'get client file name without path
  sFileSplit = split(o.FileNameOf("ecpFilename"), "\")
  sFile = sFileSplit(Ubound(sFileSplit))

  'Upload filename
  o.FileInputName = "ecpFilename"
  o.FileFullPath = Server.MapPath(".") & "\ecpFiles\" & sFile
  o.save

  'If not error redirect to complete message
  if o.Error = "" then

    Dim pSQLStatement, pConnection
    Dim pRS
    Dim pEcpNumber, pEcpTitle, pEcpFilename, pEcpOriginator, pEcpDateDistributed, pEcpDateDue

    pEcpNumber = o.ValueOf("ecpNumber")
    pEcpTitle = o.ValueOf("ecpTitle")
    pEcpFilename = sFile
    pEcpOriginator = o.ValueOf("ecpOriginator")
    pEcpOriginator = Session("UserFullName")
    pEcpDateDistributed = CStr(date())
    pEcpDateDue = CStr(o.ValueOf("ecpDateDue"))

        pConnection = "Provider=SQLOLEDB.1;UID=sa;PWD=;Initial Catalog=ecpDatabase2SQL;Data Source=localhost"
Set pRS = server.CreateObject("ADODB.RecordSet")

    pRS.open "INSERT INTO ecpForms (ecpNumber, ecpTitle, ecpFilename, ecpOriginator, ecpDateDistributed, ecpDateDue) VALUES ('" & pEcpNumber & "', '" & pEcpTitle & "', '" & pEcpFilename & "', '" & pEcpOriginator & "', '" & pEcpDateDistributed & "', '" & pEcpDateDue & "') ", pConnection

    response.write "Success. File saved to " & o.FileFullPath

    emailMessage = "Do not reply. This is an automatic email." & Chr(13)
    emailMessage = emailMessage & "ECP Number: " & pEcpNumber & Chr(13)
    emailMessage = emailMessage & "ECP Title: " & pEcpTitle & Chr(13) & Chr(13)
    emailMessage = emailMessage & "New ECP form just posted on forum." & Chr(13) & Chr(13)
    emailMessage = emailMessage & "Check details on someplace.net"

    Set objMail = Server.CreateObject("CDO.Message")
    objMail.From = Session("UserEmail")
    objMail.Subject = "ECP Number: " & pEcpNumber & " -- " & pEcpTitle

    objMail.To = "[email protected]"
    'objMail.To = "[email protected]"
    objMail.Cc = "[email protected]"
    objMail.Bcc = "[email protected]"

    objMail.TextBody = emailMessage
    objMail.Send

    Response.Redirect("viewAll1.asp")

  else
    response.write "Failed due to the following error: " & o.Error
  end if

end if
set o = nothing

%>

</body>
</html>

clsUpload1.asp

<%
' ------------------------------------------------------------------------------
' Container of Field Properties
Class clsField
    Public FileName
    Public ContentType
    Public Value
    Public FieldName
    Public Length
    Public BinaryData
End Class
' ------------------------------------------------------------------------------
Class clsUpload
' ------------------------------------------------------------------------------
    Private nFieldCount
    Private oFields()
    Private psFileFullPath
    Private psError
    Private psFileInputName
' ------------------------------------------------------------------------------
    Public Property Get Count()
        Count = nFieldCount
    End Property
' ------------------------------------------------------------------------------
    Public Default Property Get Field(ByRef asFieldName)
        Dim lnLength
        Dim lnIndex

        lnLength = UBound(oFields)

        If IsNumeric(asFieldName) Then
            If lnLength >= asFieldName And asFieldName > -1 Then
                Set Field = oFields(asFieldName)
            Else
                Set Field = New clsField
            End If
        Else
            For lnIndex = 0 To lnLength
                If LCase(oFields(lnIndex).FieldName) = LCase(asFieldName) Then
                    Set Field = oFields(lnIndex)
                    Exit Property
                End If
            Next
            Set Field = New clsField
        End If
    End Property
' ------------------------------------------------------------------------------
    Public Function Exists(ByRef avKeyIndex)
        Exists = Not IndexOf(avKeyIndex) = -1
    End Function
' ------------------------------------------------------------------------------
    Public Property Get ValueOf(ByRef avKeyIndex)
        Dim lnIndex
        lnIndex = IndexOf(avKeyIndex)
        if lnIndex = -1 Then Exit Property
        ValueOf = oFields(lnIndex).Value
    End Property
' ------------------------------------------------------------------------------
    Public Property Get FileNameOf(ByRef avKeyIndex)
        Dim lnIndex
        lnIndex = IndexOf(avKeyIndex)
        if lnIndex = -1 Then Exit Property
        FileNameOf = oFields(lnIndex).FileName
    End Property
' ------------------------------------------------------------------------------
    Public Property Get LengthOf(ByRef avKeyIndex)
        Dim lnIndex
        lnIndex = IndexOf(avKeyIndex)
        if lnIndex = -1 Then Exit Property
        LengthOf = oFields(lnIndex).Length
    End Property
' ------------------------------------------------------------------------------
    Public Property Get BinaryDataOf(ByRef avKeyIndex)
        Dim lnIndex
        lnIndex = IndexOf(avKeyIndex)
        if lnIndex = -1 Then Exit Property
        BinaryDataOf = oFields(lnIndex).BinaryData
    End Property
' ------------------------------------------------------------------------------
    Private Function IndexOf(ByVal avKeyIndex)
        Dim lnIndex

        If avKeyIndex = "" Then
            IndexOf = -1
        ElseIf IsNumeric(avKeyIndex) Then
            avKeyIndex = CLng(avKeyIndex)
            If nFieldCount > avKeyIndex And avKeyIndex > -1 Then
                IndexOf = avKeyIndex
            Else
                IndexOf = -1
            End If
        Else
            For lnIndex = 0 To nFieldCount - 1
                If LCase(oFields(lnIndex).FieldName) = LCase(avKeyIndex) Then
                    IndexOf = lnIndex
                    Exit Function
                End If
            Next
            IndexOf = -1
        End If
    End Function
' ------------------------------------------------------------------------------
Public Property Let FileFullPath(sValue)
    psFileFullPath = sValue
End Property
'_________________________________________________ __________________________________
Public Property Get FileFullPath()
    FileFullPath = psFileFullPath
End Property
' ------------------------------------------------------------------------------
Public Property Let FileInputName(sValue)
    psFileInputName = sValue
End Property
' -------------------- ----------------------------------------------------------
Public Function Save()
    if psFileFullPath <> "" and psFileInputName <> "" then
        'Save to connectionless client side recordset, write to stream,
        'and persist stream.
        'would think you should be able to write directly to
        'stream without recordset, but I could not get that to work

        On error resume next
        binData = o.BinaryDataOf(psFileInputName)
        set rs = server.createobject("ADODB.RECORDSET")
        rs.fields.append "FileName", 205, LenB(binData)
        rs.open
        rs.addnew
         rs.fields(0).AppendChunk binData

        if err.number = 0 then
            set objStream = Server.CreateObject("ADODB.Stream")
              objStream.Type = 1
               objStream.Open
             objStream.Write rs.fields("FileName").value
            objStream.SaveToFile psFileFullPath, 2
            objStream.close
            set objStream = Nothing

        ENd if
        rs.close
        set rs = nothing
        psError = Err.Description
else
        psError = "One or more required properties (FileFullPath and/or FileInputName) not set"

  End If

End Function

Public Property Get Error()
    Error = psError
End Property

' ------------------------------------------------------------------------------
    Public Property Get ContentTypeOf(ByRef avKeyIndex)
        Dim lnIndex
        lnIndex = IndexOf(avKeyIndex)
        if lnIndex = -1 Then Exit Property
        ContentTypeOf = oFields(lnIndex).ContentType
    End Property

' ------------------------------------------------------------------------------
    Private Sub Class_Terminate()
        Dim lnIndex
        For lnIndex = 0 To nFieldCount - 1
            Set oFields(0) = Nothing
        Next
    End Sub
' ------------------------------------------------------------------------------
    Private Sub Class_Initialize()

        Dim lnBytes ' Bytes received from the client
        Dim lnByteCount ' Number of bytes received
        Dim lnStartPosition ' Position at which content begins
        Dim lnEndPosition ' Position at which content ends

        Dim loDic ' Contains properties of each
                                ' specific field
                                ' Local dictionary object(s)
                                ' to be appended to class-scope
                                ' dictioary object.

        Dim lnBoundaryBytes ' Bytes contained within the current boundary
        Dim lnBoundaryStart ' Position at wich the current boundary begins
                                ' within the lnBytes binary data.
        Dim lnBoundaryEnd ' Position at wich the current boundary ends
                                ' within the lnBytes binary data.
        Dim lnDispositionPosition

        Dim lsFieldName ' Name of the current field being parsed from
                                ' Binary Data
        Dim lsFileName ' Name of the file within the current boundary
        Dim lnFileNamePosition ' Location of file name within current boundary
        Dim loField ' clsField Object
        Dim lsValue ' Value of the current field
        Dim lsContentType ' ContentType of the binary file (MIME Type)

        ' Initialize Fields
        nFieldCount = 0
        ReDim oFields(-1)

        ' Read the bytes (binary data) into memory
        lnByteCount = Request.TotalBytes
        lnBytes = Request.BinaryRead(lnByteCount)

        'Get the lnBoundaryBytes
        lnStartPosition = 1
        lnEndPosition = InstrB(lnStartPosition, lnBytes, CStrB(vbCr))

        If lnEndPosition >= lnStartPosition Then
            lnBoundaryBytes = MidB(lnBytes, lnStartPosition, lnEndPosition - lnStartPosition)
        End If

        lnBoundaryStart = InstrB(1, lnBytes, lnBoundaryBytes)

        ' Loop until the BoundaryBytes begin with "--"
        Do Until (lnBoundaryStart = InstrB(lnBytes, lnBoundaryBytes & CStrB("--")))

            ' All data within this boundary is stored within a local dictionary
            ' to be appended to the class-scope dictionary.

            ReDim Preserve oFields(nFieldCount)
            nFieldCount = nFieldCount + 1

            Set loField = New clsField

            lnDispositionPosition = InstrB(lnBoundaryStart, lnBytes, CStrB("Content-Disposition"))

            ' Get an object name
            lnStartPosition = InstrB(lnDispositionPosition, lnBytes, CStrB("name=")) + 6
            lnEndPosition = InstrB(lnStartPosition, lnBytes, CStrB(""""))
            lsFieldName = CStrU(MidB(lnBytes, lnStartPosition, lnEndPosition - lnStartPosition))
            loField.FieldName = lsFieldName

            ' Get the location fo the file name.
            lnFileNamePosition = InstrB(lnBoundaryStart, lnBytes, CStrB("filename="))
            lnBoundaryEnd = InstrB(lnEndPosition, lnBytes, lnBoundaryBytes)

            'Test if object is a file
            If Not lnFileNamePosition = 0 And lnFileNamePosition < lnBoundaryEnd Then

                ' Parse Filename
                lnStartPosition = lnFileNamePosition + 10
                lnEndPosition = InstrB(lnStartPosition, lnBytes, CStrB(""""))
                lsFileName = CStrU(MidB(lnBytes,lnStartPosition,lnEndPosition-lnStartPosition))
                loField.FileName = lsFileName

                ' Parse Content-Type
                lnStartPosition = InstrB(lnEndPosition,lnBytes,CStrB("Content-Type:")) + 14
                lnEndPosition = InstrB(lnStartPosition,lnBytes,CStrB(vbCr))
                lsContentType = CStrU(MidB(lnBytes,lnStartPosition,lnEndPosition-lnStartPosition))
                loField.ContentType = lsContentType

                ' Parse Content
                lnStartPosition = lnEndPosition + 4
                lnEndPosition = InstrB(lnStartPosition,lnBytes,lnBoundaryBytes)-2
                lsValue = MidB(lnBytes,lnStartPosition,lnEndPosition-lnStartPosition)
                loField.BinaryData = lsValue & CStrB(vbNull)
                loField.Length = LenB(lsValue)
            Else

                ' Parse Content
                lnStartPosition = InstrB(lnDispositionPosition, lnBytes, CStrB(vbCr)) + 4
                lnEndPosition = InstrB(lnStartPosition, lnBytes, lnBoundaryBytes) - 2
                lsValue = CStrU(MidB(lnBytes,lnStartPosition,lnEndPosition-lnStartPosition))
                loField.Value = lsValue
                loField.Length = Len(lsValue)
            End If

            Set oFields(UBound(oFields)) = loField

            'Loop to next object
            lnBoundaryStart = InstrB(lnBoundaryStart + LenB(lnBoundaryBytes), lnBytes, lnBoundaryBytes)

            Set loField = Nothing

        Loop

    End Sub
' ------------------------------------------------------------------------------
    Private Function CStrU(ByRef psByteString)
        Dim lnLength
        Dim lnPosition
        lnLength = LenB(psByteString)
        For lnPosition = 1 To lnLength
            CStrU = CStrU & Chr(AscB(MidB(psByteString, lnPosition, 1)))
        Next
    End Function
' ------------------------------------------------------------------------------
    Private Function CStrB(ByRef psUnicodeString)
        Dim lnLength
        Dim lnPosition
        lnLength = Len(psUnicodeString)
        For lnPosition = 1 To lnLength
            CStrB = CStrB & ChrB(AscB(Mid(psUnicodeString, lnPosition, 1)))
        Next
    End Function
' ------------------------------------------------------------------------------
End Class
' ------------------------------------------------------------------------------
%>

As always any help is very much appreciated!

Thanks,
Toni
[email protected]

Toni Burgess
[email protected]

dparsons · October 4th, 2006, 09:20 AM

O.o thats alot of code. Ok. Lets start here, since you are not getting an error I am led to believe that 1 of 2 things are happening.

1. In your first page you have 2 if statements, are you sure that your program flow is going as it should be, e.g. that the call to the database is even being made? (Place respone.writes in the places the program SHOULD execute and see if those check out)

2. An error is being generated from your class, you just dont know it because of the On Error Resume Next inside your Save() method, comment this out and see if you get an error then.

hth

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

misskaos · October 4th, 2006, 09:27 AM

Okay, i commented the On error resume next, out of the code and yup, I got an error:
Error Type:
ADODB.Stream (0x800A0BBC)
Write to file failed.
/wwwroot/testecp/clsUpload1.asp, line 135

Toni Burgess
[email protected]

dparsons · October 4th, 2006, 09:41 AM

The directory where you are writing the file, you need to grant Write permissions to the IUSR_<machinename> user and that should allow you to save the files correctly.

hth

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

misskaos · October 4th, 2006, 10:13 AM

still not working. still receiving same msg:
Error Type:
ADODB.Stream (0x800A0BBC)
Write to file failed.
/testecp1/clsUpload1.asp, line 135

Quote:

quote:Originally posted by dparsons
The directory where you are writing the file, you need to grant Write permissions to the IUSR_<machinename> user and that should allow you to save the files correctly.

hth

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

Toni Burgess
[email protected]

dparsons · October 4th, 2006, 10:16 AM

Try giving it Read, Write, and Modify control. If it still doesnt work, give that account full control of the folder and see if it works. (I say give it full control for testing purposes, this is a bad and potentially dangerous practice for live applications)

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

misskaos · October 4th, 2006, 10:26 AM

You know, now that I've looked through this code a thousand times I'm starting to see a view other things myself....

It looks like it "is" trying to save the information to a file (as well as inserting it into the SQL database). However, i don't see anywhere in the code where the file name is listed. (Someone else wrote the program, I am just trying to switch the databases). So, it may not be saving to my database "because" it can't find the file to post form information. IF this is the case, I do not see the purpose of it trying to save the information to a file, it's unnecessary. (Though, I know they may have had to do it that way in order to get Access to work right).

Isn't there someway I can just delete obj.stream and just use a simple insert statement without having to completely change the program itself? I just need it to insert the records, I have yet to see the importance of all the extra code that has been put in there. I just would hate to delete all of it if I can use it, but I most definitely don't want to keep it if I can't. I know most of it is based on the programmer using Microsoft Access database--so I may have to take out the classupload all together, but I do not want to destroy the look of the page. But I see the clsUpload page as making a simple insert a lot more complicated than it actually is.

Any suggestions?

Toni Burgess
[email protected]

dparsons · October 4th, 2006, 10:42 AM

First, to answer your question, no you cant simply eliminate the Class. The reason is because Classic ASP has no native support for uploading files, it can only be achieved by using COM Components or messy script code. (You are using the latter ;])

What you can try to do, is in your function SAVE(), instead of writing to a stream, return the value binData and write that variable to your binary column in the database. (Nearest I can tell that is the binary stream from the HTTP Pipe)

That should work..i think. ;]

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

misskaos · October 4th, 2006, 11:25 AM

Ok, still having problems. Let me ask you this....

Ok I can use Server.MapPath in access or SQL, from my understanding.... However, when I'm using a Access database you would need to put Server.MapPath in your connection string like:

Connection = "Driver={Microsoft Access Driver (* .mdb); DBQ=" Server.MapPath(".\aspupload.mdb")

But where would I need to put it when I am using a SQL connection string??

Toni Burgess
[email protected]

misskaos · October 4th, 2006, 11:54 AM

Okay I got it working. How, I have no idea-- I started changing so much stuff I can't even remember now was worked, I'll figure that out later. But it is working; i needed to changed the permissions thing also inside of SQL Enterprise Manager too so that helped it. As always, thank youuuuuuuuuuuuuuuu

Quote:

quote:Originally posted by misskaos
Ok, still having problems. Let me ask you this....

Ok I can use Server.MapPath in access or SQL, from my understanding.... However, when I'm using a Access database you would need to put Server.MapPath in your connection string like:

Connection = "Driver={Microsoft Access Driver (* .mdb); DBQ=" Server.MapPath(".\aspupload.mdb")

But where would I need to put it when I am using a SQL connection string??

Toni Burgess
[email protected]

Toni Burgess
[email protected]