• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

uploading a text file and write a store procedure to store the data as a record in data table

Hi,

I  have a suitation where user will upload a text file. the text file has two columns. the data  in the columns should be read and stored as a record in the table. can any one give me code?

i have written little code.
<html>
<body bgcolor="#ffffff">
<form method="post" enctype="multipart/form-data" action="\HTMLPage1.asp">
<INPUT type="file" ID="File1" NAME="File1">
<br>
<br>
<INPUT type="SUBMIT" value="UpLoad" ID="Button1" NAME="Button1">
</form>
</body>
</html>

The above code just  produces a browse button and upload button. It does not do anything else.

I could not figured it out how to proceed next. Please this really urgent and please help me out.

Thanks in Advance,
nk
0
NK9
Asked:
NK9
  • 4
  • 4
1 Solution
 
ThinkPaperCommented:
You will need to open the file and parse thru it . As you read each line, run a query to store in the DB

Take a look at file system object and text stream.
http://www.w3schools.com/asp/asp_ref_textstream.asp
http://www.w3schools.com/asp/asp_ref_filesystem.asp
0
 
ThinkPaperCommented:
You also need to look at whether you're uploading a file to the server or via DB.
In this case, may be easier to upload to a folder, then open the file, parse thru it and run a query to store each line into the DB.. Then delete the file.

How to upload file via ASP ( to folder or DB)
http://www.asp101.com/articles/jacob/scriptupload.asp
0
 
NK9Author Commented:
Hi,

Actually I don't upload the file. the file will be uploaded from the client machine. He will just browse the text file and press upload button then the data in the text file should be read and stored as a record in the datatable.
so still i need to save the file to folder or DB? Please Provide me the code. It is really very urgent.

Thanks,
nk
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NK9Author Commented:
Hi Experts,

I have done little home work and written some code but some how i could not run in my machine. it is giving the following error:
Exception from HRESULT: 0x800A0035 (CTL_E_FILENOTFOUND)

I am pasting the code below. Please check it.

Thanks in advance,
nk

<%@ page aspcompat=true  %>

<html>

<script language="vbscript" runat="Server">
Sub Upload_Click(Sender as Object, e as EventArgs)

        Dim strFileName as string
       strFileName = MyFile.PostedFile.FileName
        Dim c as string = System.IO.Path.GetFileName(strFileName)
          Try

             MyFile.PostedFile.SaveAs("D:\ServerFolder\" + c)
                       Span1.InnerHtml = "Your File Uploaded Sucessfully at server!"

                catch Exp as exception
                  span1.InnerHtml = "An Error occured. Please check the attached  file"                        
                       span2.visible=false
                End Try
          Dim objFSO, objFile, strAllFile, objTextFile
          Const ForReading = 1
dim strComputer as string
      objFSO = CreateObject("Scripting.FileSystemObject")
       Dim stringx
 stringx = "D:\ServerFolder\Uploadfile.txt"
objTextFile = objFSO.OpenTextFile(stringx, ForReading)
 dim oDic = CreateObject("Scripting.Dictionary")
 dim Element, strNextLine, objItem
 dim objDictionary = CreateObject("Scripting.Dictionary")
 
 dim inext = 0
Dim arrComputers, strText, arrwords
strText = objTextFile.ReadAll
objTextFile.Close

arrComputers = Split(strText, vbCrLf)
dim splitline as string
dim rdStock = "false"
Dim cn = Server.CreateObject("ADODB.Connection")

Dim strConn = "Driver={SQL Server};Server=RRCGX280-CBC155\SQLEXPRESS;Database=SSI;Uid=sa;"
   dim idx as string
   idx = 5
   dim sql = "exec EmployeeStoredProc @EmpID='dlf'"
   '"select * from tb_12d3"
Dim rs=Server.CreateObject("ADODB.recordset")

cn.Open(strConn)
dim insertsql = "INSERT INTO tb_12d3 (Trust,Symbol) VALUES('"
dim sqlqueryIn as string
dim recaffected    
dim isStockName = "false"
dim sumMarket = 0
dim newsql
dim countStk = 0
For Each strComputer in arrComputers
    'Response.Write(strComputer)
      For Each arrwords in Split(strComputer, " ")
      if arrwords = "Symbol" then
         rdStock = "true"      
      else if rdStock = "true" then
        if not arrwords = "" then          
          if isStockName = "false" then
             isStockName = "true"
             sqlqueryIn = insertsql + Trim(arrwords)+"','"            
          else if isStockName = "true" then
            sqlqueryIn = sqlqueryIn + Trim(arrwords)+"')"
            Response.Write(sqlqueryIn)  
            'cn.Execute(sqlqueryIn)          
            Response.Write("<br/>")
            Response.Write(arrwords)        
            Response.Write("<br/>")
            isStockName = "false"
            newsql = "exec EmployeeStoredProc @EmpID='"+Trim(arrwords)+"'"
            'Response.Write(newsql)
            Dim rsNew=Server.CreateObject("ADODB.recordset")
            rsNew.Open (newsql, cn)
            dim myString = rsNew("MarketValue").value
            sumMarket = sumMarket + myString            
            objDictionary.Add(Trim(arrwords), myString)
            oDic.Add(countStk, Trim(arrwords))            
            countStk = countStk + 1
          end if
        end if
      end if
      Next
    'Response.Write("<br/>")
Next

'Response.ContentType = "application/vnd.ms-excel"
Dim KeysArray = objDictionary.Keys
dim iteminArray


'For Each objItem in objDictionary
' Response.Write(objDictionary.Key(objItem))
' Response.Write(KeysArray[2])
'Next

response.write("<table border=2>")
response.write("<tr>")
response.write("<td><b>ID</b></td>")
response.write("<td><b>CompanyName</b></td>")
response.write("<td><b>MarketValue</b></td>")
response.write("<td><b>Percentage</b></td>")
response.write("</tr>")

For Each iteminArray in KeysArray
  'Response.Write(iteminArray)
  'Response.Write(objDictionary.Item(iteminArray))
  dim perMarValue = objDictionary.Item(iteminArray)
  dim perMarValuex = (perMarValue/sumMarket)*100
  response.write("<tr> ")
response.write("<td>")
response.write(iteminArray)
response.write("</td>")
response.write("<td>")
response.write(iteminArray)
response.write("</td>")
response.write("<td>")
response.write(perMarValue)
response.write("</td>")
response.write("<td>")
response.write(perMarValuex)
response.write("</td>")
response.write("</tr>")
Next
response.write("</table> ")
'Response.Write(sumMarket)

cn.Close
'rs.Close
'rs = Nothing
cn = Nothing
Response.Write ("<br/>")
End Sub
</script>

<body>
    <font color="DarkGreen" face="Helvetica" size="5">
    <b>Uploading Trade file of NYB
        ! </font>
    <hr size="2" color="Black">
    <p>
        <Form id="Form1" method="Post" enctype="Multipart/Form-Data" runat="Server">
            Choose Your File To Upload :
            <br>
            <input id="MyFile" type="File" runat="Server" size="40">
            <br>
            <br>
            <input id="Submit1" type="Submit" value="Upload" onserverclick="Upload_Click" runat="Server">
            <p>
                <span id="Span1" style="color: Red" runat="Server" /><span id="Span2" style="color: Red"
                    runat="Server" />
        </form>
        <hr size="2" color="Black">
</body>
</html>
0
 
NK9Author Commented:
Hi Experts,

Please help me getting code to upload a text file and reading the file. the code in the above article does not work. I have VB5.o but still it is working. I have tried changing it but could not figured out.

Please any one provide me progamming code. I am new to classic asp and also VBscript.

Thanks,
nk
0
 
ThinkPaperCommented:
The code you have is uploading a file to the server.. which is what was suggested initially. So you are uploading a file to the server. The error given looks like you're not uploading it properly (i.e. file not found). You need to fix this before you do anything else...
Your save location must have security permissions set for users to save to the folder. Also make sure that your server does indeed have a D drive since that's what you're specifying it as.

Basically your code should be doing something like this...
- Upload file to server and rename as a "default.txt" or something (using filesystem object)
- Open file
- open DB
- Start Loop while File is not EOF
- Parse each line of text and run query to insert to DB
- move to next line
- End Loop
- Close File
- close DB
0
 
NK9Author Commented:
Hi Think Paper,

How are you doing?

I thank you for responding for my question and answering it. i guess i can do it now according to your suggestions,

Thanks,
nk
0
 
ThinkPaperCommented:
np. let us know if you run into any other trouble. =)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now