Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is this possible? (VB.net/Excel) 1

Status
Not open for further replies.
lisalis99

lisalis99

Programmer
Jan 9, 2005
121
US
Is it possible to create a vb.net app that allows a user to choose fields from a database and then export them to excel? I know there are tons of ways to export to excel, but how can the user choose the fields from the DB they want to export?

Thanks,
Lisa
 
Sort by date Sort by votes
Just use a query that pulls the top 1 row from the db into a dataset. Then loop through the tables Columns to get a list of the Field Names. Then let the user select them in any way you see fit (like a list box) and build your query from there.

Senior Software Developer
 
Upvote 0 Downvote
so let's say I have the field names in a dropdown list, they select one, then can I add that to a datagrid and build it to a datagrid and then export the datagrid to excel?

Thanks,
Lisa
 
Upvote 0 Downvote
Actually what I really want to do is load the datagrid from the DB, but then allow the user to select columns and add them to another datagrid and then export the newly created datagrid, but I'm reading that you can select rows but not columns in a datagrid, is that true?

Thanks,
Lisa
 
Upvote 0 Downvote
I'm not positive, but I believe that is basically true.

I'm not so sure that is the best way to go anyway. Try using two list view controls. One list view initially loads with all column names (available columns) and one loads with none (selected columns). Then use Add and Remove buttons to move selected columns into and out of the selected columns list box. Using this method you could also use Up and Down buttons to rearrange the selected columns the user wants to report on. You could also provide Order By functionality in a number of different ways(dropdowns, or check boxes in the list view, etc). I would then use that data in the selected list view control to create a SQL query, and bind the return to the main report Datagrid.


Senior Software Developer
 
Upvote 0 Downvote
Okay, I have the listboxes and I can add from one to the other, now what I want to do is, anything in that second listbox, I want to query the db and grab those values and populate a datagrid, but I'm having an issue.

Here is some code for the "Get Items" button to put into the datagrid.

Code: 
Private Sub btnGet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGet.Click
   
        Dim item As ListItem
        For Each item In ListBox2.Items
            Dim DS As DataSet
            Dim MyConnection As SqlConnection
            Dim MyCommand As SqlDataAdapter
            Dim strState As String
            item = ListBox2.SelectedItem
            Dim MySQL As String

** I want to query the db for those items that are in Listbox2 and add them to the datagrid **

            MySQL = "Select * from vwContactsAll where Item = " & item.Text & "  and tag = 'Memst' and type='m' and active = '1'"


            MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            MyCommand = New SqlDataAdapter(MySQL, MyConnection)
            DS = New DataSet
            MyCommand.Fill(DS, "stuff")
            DataGrid1.DataSource = DS.Tables("stuff").DefaultView
            DataGrid1.DataBind()
        Next
    End Sub
I don't even know if what I'm doing is possible...

Thanks,
Lisa
 
Upvote 0 Downvote
Ok... so you have all of your column names in the second listbox that you want to bring back! That's great.

What you need to do now is build a single query from that data.

Code: 
        Dim item As ListItem
        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter

        Dim sFields as String = ""
        For Each item In ListBox2.Items
            sField += ListBox2.SelectedItem & ", "
        Next

        'strip the last ", "
        sField = sField.TrimEnd(", ")

        Dim MySQL As String = "Select " & sFields  & " from vwContactsAll where Item = " & item.Text & "  and tag = 'Memst' and type='m' and active = '1'"

        MyConnection = New SqlConnection (ConfigurationSettings.AppSettings("connectionString"))
        MyCommand = New SqlDataAdapter(MySQL, MyConnection)
        DS = New DataSet
        MyCommand.Fill(DS, "stuff")
        DataGrid1.DataSource = DS.Tables("stuff").DefaultView
        DataGrid1.DataBind()

I just wrote the free hand and haven't tested it but it should give you a good idea on how it should work.

Also, make sure you validate that at least one field is selected into listbox2.

Senior Software Developer
 
Upvote 0 Downvote
Oops! LOL

Again... this may not be exact, but you want to loop through the SelectedItems Collection and get the field names. So it would be more like this...

Code: 
        Dim sFields as String = ""
        For Each item In ListBox2.SelectedItems
            sField += item & ", "
        Next

If "item" doesn't work to get the field name, then look for properties like Value, Text, ect.

Senior Software Developer
 
Upvote 0 Downvote
Okay I have this:

Code: 
 Private Sub btnGet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGet.Click
        Dim item As ListItem
        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter
        Dim sFields As String = ""
        For Each item In ListBox2.Items
            sFields = sFields & ","
        Next
        'strip the last ", "
        sFields = sFields.TrimEnd(", ")
        Dim MySQL As String = "Select " & sFields & " from vwContactsAll where tag = 'Memst' and type='m' and active = '1'"
        MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
        MyCommand = New SqlDataAdapter(MySQL, MyConnection)
        DS = New DataSet
        MyCommand.Fill(DS, "stuff")
        DataGrid1.DataSource = DS.Tables("stuff").DefaultView
        DataGrid1.DataBind()
    End Sub

but... it doesn't like something, my error is: Incorrect syntax near the keyword 'from'.

Which I think it doesn't like the " & sFields & " if I take that out and put in * it pulls data but of course all of it and not the selected items in the listbox- any more ideas? I'm not positive though... Thanks

Lisa
 
Upvote 0 Downvote
okay so I changed this line:

Code: 
        Dim MySQL As String = "Select '" & sFields & " ' from vwContactsAll where tag = 'Memst' and type='m' and active = '1'"

and now my datagrid shows column1 but blank???!@!!
 
Upvote 0 Downvote
go back to here and change the Red line to include the space at the end.

Code: 
 Private Sub btnGet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGet.Click
        Dim item As ListItem
        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter
        Dim sFields As String = ""
        For Each item In ListBox2.Items
            [red][b]sFields = sFields & ", "[/b][/red]
        Next
        'strip the last ", "
        sFields = sFields.TrimEnd(", ")
        Dim MySQL As String = "Select " & sFields & " from vwContactsAll where tag = 'Memst' and type='m' and active = '1'"
        MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
        MyCommand = New SqlDataAdapter(MySQL, MyConnection)
        DS = New DataSet
        MyCommand.Fill(DS, "stuff")
        DataGrid1.DataSource = DS.Tables("stuff").DefaultView
        DataGrid1.DataBind()
    End Sub

Senior Software Developer
 
Upvote 0 Downvote
LOL- Now I'm getting Column1 in my datagrid but only commas where there should be values, meaning that when I add something to the listbox and click "get items" I get this in column1 of my datagrid:

,,,

It's not pulling anything! UGH!!! what a nightmare!
 
Upvote 0 Downvote
It's ok... it's not a nightmare... you are really close.

Put a break point on this line:
Dim MySQL As String = "Select " & sFields & " from vwContactsAll where tag = 'Memst' and type='m' and active = '1'"

What is the value of sFields?

If you advance it one line, what then is the value of MySQL?

Senior Software Developer
 
Upvote 0 Downvote
LOL! Ok... so I woke up this morning took a look and the problem jumped right out.

This:
For Each item In ListBox2.Items
sFields = [red]sFields[/red] & ", "
Next

Should be This:
For Each item In ListBox2.Items
sField += [red]ListBox2.SelectedItem[/red] & ", "
Next


Senior Software Developer
 
Upvote 0 Downvote
Oh Crap... Still groogy though.

This:
For Each item In ListBox2.Items
sFields = sFields & ", "
Next

Should be This:
For Each item In ListBox2.Items
sField += item & ", "
Next

Note: If "item" doesn't work then try one of it's properties like text or value or item.tostring.

Senior Software Developer
 
Upvote 0 Downvote
Thanks for all of your help- now it's taking the name of the field, not the value, even if I change it to:

Code: 
For Each item In ListBox2.Items
            sFields += item.Value & ", "
        Next

I get Address 1, City, Name not the actual values in there.

Ugh!! I'm trying to add this to a datagrid too, will the comma's be an issue? I want to add the column and the values so they can pick and choose, then after the datagrid has what they want, I'm going to put a button to "export to excel".

Can I email you a screen shot? I don't have a URL for you to see...it's internal.

Thanks again!
 
Upvote 0 Downvote
Did you ever find out what the value of MySQL is?

Should be something like this:

Select Address1, City, Name from vwContactsAll where tag = 'Memst' and type='m' and active = '1'


Senior Software Developer
 
Upvote 0 Downvote
Okay, it's pulling the value now but when I go to add another it doesn't add it to the datagrid it replaces what's there! Ugh- Almost there... I think this is wrong:

Dim sFields As String = ""
For Each item In ListBox2.Items

sFields = ListBox2.SelectedItem.Text & ", "

Next

If I change it to sFields = ListBox2.SelectedItem.Text and leave it there, it will grab the value and put the column in the datagrid, if I put it back with & ", " it gives me a sql error, the generic error: Incorrect syntax near the keyword 'from'.

So... I'm not sure why it doesn't like the appending of the items.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
NEVER used - get all possible info by ip
Replies
4
Views
204
strongm
strongm
Michael OConnor
  • Locked
  • Question
Is this Meridian Mail
Replies
2
Views
117
Michael OConnor
Michael OConnor
sal21
  • Locked
  • Question
how to read the coordinates in from this file
Replies
3
Views
228
Ken01
Ken01
sal21
  • Locked
  • Question
ARRANGE this Access coe to vb6
Replies
4
Views
197
strongm
strongm
Swi
  • Locked
  • Question
Read Excel Sharepoint File
Replies
0
Views
134
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top