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

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

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
bignadad
Asked:
bignadad
  • 8
  • 3
  • 3
  • +1
1 Solution
 
käµfm³d 👽Commented:
Are you wanting to store the file that contains the multiple numbers to search for?
0
 
bignadadAuthor Commented:
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
 
bignadadAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
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
 
bignadadAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
But is seems like you from a previous question you posted, you have already Figured it out on my own
0
 
bignadadAuthor Commented:
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
 
CodeCruiserCommented:
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
 
bignadadAuthor Commented:
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
 
CodeCruiserCommented:
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
 
bignadadAuthor Commented:
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
 
CodeCruiserCommented:
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
 
bignadadAuthor Commented:
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
 
bignadadAuthor Commented:
Will be my option if i cannot get sql to work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 8
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now