locked
Need some advice on how to allow users to run query based on a list of part numbers RRS feed

  • Question

  • User-1004674190 posted

    I have a ASPX page I have been building in Visual Studio.

    I have the page working to look up single part numbers
    here it is
    http://bakerabilene.com/interchange.aspx

    Well, now i want users to be able to compare many numbers instead of one at a time.
    I had this setup in my access database but now i need it to be web based so it works for all my companies.



    Turns out that since my host is GoDaddy they do not allow excel files to be uploaded to sql server. I already have purchased a year and would like to get this done by staying with them.

    What should I do? Is there another way of doing this?
    Could i do this via csv file? I found that GoDaddy allows csv imports but i cant seem to figure it out.

    I had originally planned on users clicking a browse button to select the file, then upload to transfer data to sql server. Once there it would run a query to fill the appropriate data, export back to excel and users could view file.

    Is there another way of doing this that im missing?

    Or is my best option to switch web host?

    Protected Sub cmdImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdImport.Click
            Dim savedFiles As String
            If Me.FileUpload1.PostedFile Is Nothing Then
                MessageBox("this file not correct")
            Else
                Dim strExt As String = Path.GetExtension(Me.FileUpload1.PostedFile.FileName)
                If strExt.ToLower() = ".csv" Then
                    savedFiles = Path.GetFileName(Date.Now.Day & Date.Now.Month & Date.Now.Year & Date.Now.Hour & Date.Now.Minute & Date.Now.Second & ".csv")
                    Me.FileUpload1.PostedFile.SaveAs(Server.MapPath("temp\") & savedFiles)
                    MessageBox("upload successful ")
    
                    Dim Excel As String = Server.MapPath("temp\" & savedFiles)
                    Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Excel & ";" & "Extended Properties=Text;FMT=Delimited;")
                    ExcelConnection.Open()
    
                    Dim expr As String = "SELECT * FROM [Sheet1$]"
                    Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
                    Dim objDR As OleDbDataReader
    
                    Dim SQLconn As New SqlConnection()
                    Dim ConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("baminterchangerConnectionString").ConnectionString
    
                    SQLconn.ConnectionString = ConnString
                    SQLconn.Open()
    
                    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
    
                        bulkCopy.DestinationTableName = "imports"
    
                        Try
    
                            objDR = objCmdSelect.ExecuteReader
                            bulkCopy.WriteToServer(objDR)
                            ExcelConnection.Close()
                            MessageBox("Sent to SQL")
    
                            'objDR.Close()
    
                            SQLconn.Close()
    
                        Catch ex As Exception
    
                            MessageBox(ex.ToString)
                        End Try
                    End Using
                End If
            End If
        End Sub

    I tried the above code to get the csv file to web server (which it does) but it will not get it to my sql table

    Gives me this error

    Could not find installable ISAM.


    Thursday, May 16, 2013 7:20 PM

Answers

  • User-1004674190 posted

    Well after much searching i have it working.

    I am not able to send my uploaded csv file to my sql table

    Protected Sub cmdImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdImport.Click
            Dim savedFiles As String
            If Me.FileUpload1.PostedFile Is Nothing Then
                MessageBox("this file not correct")
            Else
                Dim strExt As String = Path.GetExtension(Me.FileUpload1.PostedFile.FileName)
                If strExt.ToLower() = ".csv" Then
                    savedFiles = Path.GetFileName(Date.Now.Month & Date.Now.Day & Date.Now.Year & Date.Now.Hour & Date.Now.Minute & Date.Now.Second & ".csv")
                    Me.FileUpload1.PostedFile.SaveAs(Server.MapPath("temp\") & savedFiles)
                    MessageBox("Successfully Uploaded CSV File")
    
                    Dim Excel As String = Server.MapPath("temp\" & savedFiles)
    
                    Dim table As New DataTable()
                    table.Columns.Add("OEMPartNumber")
                    Dim parser As New FileIO.TextFieldParser(Excel)
                    parser.Delimiters = New String() {","}
                    parser.TrimWhiteSpace = True
                    Do Until parser.EndOfData = True
                        table.Rows.Add(parser.ReadFields())
                    Loop
    
                    Dim contentType As String = FileUpload1.PostedFile.ContentType
                    Using fs As Stream = FileUpload1.PostedFile.InputStream
                        Using br As New BinaryReader(fs)
                            Dim bytes As Byte() = br.ReadBytes(DirectCast(fs.Length, Long))
                            Dim constr As String = ConfigurationManager.ConnectionStrings("baminterchangerConnectionString").ConnectionString
                            Using con As New SqlConnection(constr)
                                Dim query As String = "insert into [dbo].[imports](OEMPartNumber) values (@OEMPartNumber)"
                                Using cmd As New SqlCommand(query)
                                    cmd.Connection = con
                                    cmd.Parameters.Add("@OEMPartNumber", SqlDbType.VarChar, 50, "OEMPartNumber")
                                    con.Open()
                                    Dim adapter As New SqlClient.SqlDataAdapter()
                                    adapter.InsertCommand = cmd
                                    Dim iRowsInserted As Int32 = adapter.Update(table)
                                    con.Close()
                                End Using
                            End Using
                        End Using
                    End Using
                End If
            End If
        End Sub



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 17, 2013 10:00 AM

All replies

  • User1992938117 posted

    Could not find installable ISAM.

    It can be due to many reasons, could you confirm as described below

    http://stackoverflow.com/questions/4214527/could-not-find-installable-isam

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/62ee4978-ca29-48d3-9367-6dd4ab639d2d

    Friday, May 17, 2013 12:39 AM
  • User-1004674190 posted

    Well after much searching i have it working.

    I am not able to send my uploaded csv file to my sql table

    Protected Sub cmdImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdImport.Click
            Dim savedFiles As String
            If Me.FileUpload1.PostedFile Is Nothing Then
                MessageBox("this file not correct")
            Else
                Dim strExt As String = Path.GetExtension(Me.FileUpload1.PostedFile.FileName)
                If strExt.ToLower() = ".csv" Then
                    savedFiles = Path.GetFileName(Date.Now.Month & Date.Now.Day & Date.Now.Year & Date.Now.Hour & Date.Now.Minute & Date.Now.Second & ".csv")
                    Me.FileUpload1.PostedFile.SaveAs(Server.MapPath("temp\") & savedFiles)
                    MessageBox("Successfully Uploaded CSV File")
    
                    Dim Excel As String = Server.MapPath("temp\" & savedFiles)
    
                    Dim table As New DataTable()
                    table.Columns.Add("OEMPartNumber")
                    Dim parser As New FileIO.TextFieldParser(Excel)
                    parser.Delimiters = New String() {","}
                    parser.TrimWhiteSpace = True
                    Do Until parser.EndOfData = True
                        table.Rows.Add(parser.ReadFields())
                    Loop
    
                    Dim contentType As String = FileUpload1.PostedFile.ContentType
                    Using fs As Stream = FileUpload1.PostedFile.InputStream
                        Using br As New BinaryReader(fs)
                            Dim bytes As Byte() = br.ReadBytes(DirectCast(fs.Length, Long))
                            Dim constr As String = ConfigurationManager.ConnectionStrings("baminterchangerConnectionString").ConnectionString
                            Using con As New SqlConnection(constr)
                                Dim query As String = "insert into [dbo].[imports](OEMPartNumber) values (@OEMPartNumber)"
                                Using cmd As New SqlCommand(query)
                                    cmd.Connection = con
                                    cmd.Parameters.Add("@OEMPartNumber", SqlDbType.VarChar, 50, "OEMPartNumber")
                                    con.Open()
                                    Dim adapter As New SqlClient.SqlDataAdapter()
                                    adapter.InsertCommand = cmd
                                    Dim iRowsInserted As Int32 = adapter.Update(table)
                                    con.Close()
                                End Using
                            End Using
                        End Using
                    End Using
                End If
            End If
        End Sub



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 17, 2013 10:00 AM
  • User1992938117 posted

    Well after much searching i have it working.

    Good

    am not able to send my uploaded csv file to my sql table

    What error you are getting now?

    Friday, May 17, 2013 10:23 AM
  • User-1004674190 posted

    lol. sorry.

    I meant i am NOW :)

    but now having a problem with my command with i posted here

    http://forums.asp.net/p/1906775/5397095.aspx/1?Re+Running+a+sqlcommand+on+table+and+adding+column

    Friday, May 17, 2013 10:36 AM