Solved

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

Posted on 2013-05-16
15
276 Views
Last Modified: 2013-05-17
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.

I asked this question below to figure out what the process of uploading excel file to web host and putting it into ms sql table was not working
http://www.experts-exchange.com/Programming/Languages/.NET/Q_28130364.html

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?

Im just looking for advice here.
0
Comment
Question by:bignadad
  • 8
  • 3
  • 3
  • +1
15 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
Are you wanting to store the file that contains the multiple numbers to search for?
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
No.

Basically the user has a file with one column of data on it that contains multiple part numbers.

I need to run a query to retrieve the matching part numbers and put into column b.

The user then needs that file to see what parts we have available.
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
Anyone have any input?

Im not asking for how to do this. Code and details

Im just looking for some advice.

Is this the wrong place for this question or is this just not a common task?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I am not entirely following what you are doing.  Can you not write a simple.NET app to read these part numbers?  It then would be as simple as a single INSERT statement into a temporary table or variable of type table and then match the part numbers.
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
If you look at the site I posted in question you will see that users can post a single oem part number and see if an ami number is available. I want to give users the option to do many numbers so they don't have to enter one by one. It might be 10 numbers. It might be 200 numbers
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
That part I understood, hence my question:  
Can you not write a simple.NET app to read these part numbers?
That is part numbers as in plural.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
But is seems like you from a previous question you posted, you have already Figured it out on my own
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Author Comment

by:bignadad
Comment Utility
I am unsure what to write to get this done.  Thats why im asking for advice.

What i figured out was how to have a user upload a xls or csv file to my web host.

Now how can i run my query on that file (match oem part numbers and return ami part numbers) and let the user see results to print or save?

Maybe the way im trying to do it is not the best way. By having user upload xls or csv file,  store in sql table,  query,  then return updated file back to user.  Maybe that is the best way?  Im just not sure.  

It seems being on godaddy they have limitations on how files are sent to sql server. And i cant seem to get it to work.  That is why im questioning my method and asking for advice
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
Comment Utility
Why involve excel files and uploading to SQL server at all? You can ask uses to put a comma separated list of part numbers. You can then use this in your query in Where clause with a In block and put results in gridview which users can then print. You can also write results to browser as a excel file if you need to.
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
Sorry but i am new to the .net programming and this is exactly why i am asking this question. I figured there had to be an easier way.

So with the method you are talking about the user will still upload a CSV file to server with my already established file dialog control, once its there i can run a query on that file since its on the web server then display results on the bottom of my page or a new page in a gridview. is that right?
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
You have multiple options

1) User can upload csv  file which you can then read and create the list of IDs in your page
2) User can provide the list of IDs in a textbox on your page.

The rest is correct.
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
I tried this just to see if i could get the csv file to my gridview. I wanted to make sure i could get this working before i ran my query on it.

Is this looking right?

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 TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(Excel)
                TextFileReader.TextFieldType = FileIO.FieldType.Delimited
                TextFileReader.SetDelimiters(",")
                Dim TextFileTable As DataTable = Nothing
                Dim Column As DataColumn
                Dim Row As DataRow
                Dim UpperBound As Int32
                Dim ColumnCount As Int32
                Dim CurrentRow As String()

                While Not TextFileReader.EndOfData
                    Try
                        CurrentRow = TextFileReader.ReadFields()
                        If Not CurrentRow Is Nothing Then
                            ''# Check if DataTable has been created
                            If TextFileTable Is Nothing Then
                                TextFileTable = New DataTable("TextFileTable")
                                ''# Get number of columns
                                UpperBound = CurrentRow.GetUpperBound(0)
                                ''# Create new DataTable
                                For ColumnCount = 0 To UpperBound
                                    Column = New DataColumn()
                                    Column.DataType = System.Type.GetType("System.String")
                                    Column.ColumnName = "Column" & ColumnCount
                                    Column.Caption = "Column" & ColumnCount
                                    Column.ReadOnly = True
                                    Column.Unique = False
                                    TextFileTable.Columns.Add(Column)
                                Next
                            End If
                            Row = TextFileTable.NewRow
                            For ColumnCount = 0 To UpperBound
                                Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
                            Next
                            TextFileTable.Rows.Add(Row)
                        End If
                    Catch ex As  _
                    Microsoft.VisualBasic.FileIO.MalformedLineException
                        MsgBox("Line " & ex.Message & _
                        "is not valid and will be skipped.")
                    End Try
                End While
                TextFileReader.Dispose()
                GridView1.DataSource = TextFileTable


            End If
        End If
    End Sub

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Looks alright except following line

savedFiles = Path.GetFileName(Date.Now.Month & Date.Now.Day & Date.Now.Year & Date.Now.Hour & Date.Now.Minute & Date.Now.Second & ".csv")

change it

savedFiles = Date.Now.Month & Date.Now.Day & Date.Now.Year & Date.Now.Hour & Date.Now.Minute & Date.Now.Second & ".csv"
0
 
LVL 2

Author Comment

by:bignadad
Comment Utility
Thanks for that.

I played around with that code for a while and could not get it to fill the gridview.

After stumbling across some code on internet i found something that works by importing csv to my sql table.

For now im going to try working it from that angle since i have a start on it.

You were very helpful and the gridview will be my next option if this does not work.

here is the code i got to work for getting csv to slq

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

Open in new window

0
 
LVL 2

Author Closing Comment

by:bignadad
Comment Utility
Will be my option if i cannot get sql to work.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now