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

[lisalis99]

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

 

[lisalis99]

Nope, I still get this:

Incorrect syntax near the keyword 'from'.

Ugh!!! It doesn't like the & ", " or something over there...

 

[zarkon4]

Your sFields variable is ending with a ", "
thereby making your select stmt look like this.

Select A,B,C, from ... when it needs to look like

Select A,B,C from ...
You need to strip the last ", " from sFields variable.

 

[lisalis99]

I have this in there:

sFields = sFields.TrimEnd(", ")

 

[dglienna]

is there a " " at the end before FROM?

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[lisalis99]

there is one " here is the line:

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

 

[SiriusBlackOp]

Lisa, for us to help you, you have to put a break point into the code to pause the execution on a line after MySQL is set. Then use a quick watch or the command line to tell use what the value of MySQL is. I see how you are setting it, but that does not tell me the value.

I have asked you for this before, but I'm not sure you understand what I'm asking. Do you understand how to use break points, quick watch, the command line all in Debug Mode? If you need some help with this let me know, otherwise you can just post the value of MySQL.


Senior Software Developer

http://www.scoutsft.com

 

[lisalis99]

Yes I know what a break is and I did that, it failed because I have to install the MSDN help- so the debug didn't work, I'm in the process of installing that but I did this:

Response.Write(MySQL)
Response.Write(sFields)

I can't get the value of MySQL because it fails, but if I hardcode something, like city, then it will work and I get:

Select city from vwContactsAll where tag = 'Memst' and type='m' and active = '1'

the value of sFields is:
Name,

but when I put the " & sFields & " back in, I get the SQL error...

Sorry to drive you nuts...
Lisa

 

[SiriusBlackOp]

Ok

You have to make sure that "Name, " is getting converted to "Name". Otherwise SQL will be expecting that the next seto of charcters (ie "From") should be another field name.

If [ MySQL = MySql.TrimEnd(", ") ] is failing to make that change then maybe we should try somthing else like this:

If MySQL.Length > 2 Then
'Trim off the last 2 chars (", ")
MySQL = MySQL.Substring(0,(MySQL.Length - 2))
End If

Senior Software Developer

http://www.scoutsft.com

 

[lisalis99]

I tried that-- to no avail... I finally was able to do the breakpoints and it's not stripping off that comma... I tried both methods you suggested... it sees Select Address 1, from etc...

forget it! I don't know what else do to...

 

[SiriusBlackOp]

I'm sorry... I have been posting code without thinking it through and making things harder in the process.

This is wrong:
If MySQL.Length > 2 Then
'Trim off the last 2 chars (", ")
MySQL = MySQL.Substring(0,(MySQL.Length - 2))
End If

This is Right:
'Set sFields in your loop like your are, then...
If sFields.Length > 2 Then
'Trim off the last 2 chars (", ")
sFields = sFields.Substring(0,(sFields.Length - 2))
End If
Dim MySQL As String = "Select " & sFields & " from vwContactsAll where tag = 'Memst' and type='m' and active = '1'"
'Then execute the statement.


I'm very sorry for that.

Senior Software Developer

http://www.scoutsft.com

 

[lisalis99]

Hi- Don't apologize, I appreciate you helping me out! Now that seemed to work except that when I add it to the datagrid, it doesn't add a column for each one I select, it just changes it, so I'm only left with one column- how do I build the datagrid to add those selections instead of replacing it each time? Almost there...Do I have to do some type of "add row" code?

this is what I have:

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 = ListBox2.SelectedItem.Value & ", "
        Next
        If sFields.Length > 2 Then
            sFields = sFields.Substring(0, (sFields.Length - 2))
        End If
        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

I think I have to have some type of add row or something...

 

[SiriusBlackOp]

I'm not sure, but try this.


Change this:
DataGrid1.DataSource = DS.Tables("stuff").DefaultView

To This:
DataGrid1.DataSource = DS.Tables("stuff")

Or Even To This:
DataGrid1.DataSource = DS.Tables(0)

Also, if you have and TableStyles defined for the DataGrid, try deleting them if the above doesn't work.

Then I guess we will see where we are at after that. ;o)

Senior Software Developer

http://www.scoutsft.com

 

[SiriusBlackOp]

Hey... Wait a second.

Are you working in ASP.NET? In a VB.NET console application the datagrid does not have a DataBind Method as it is done automatically.

If it is ASP.NET, also make sure that the AutoGenerateColumns property of the DataGrid is set to True. Otherwise you will have some more work to do. Also, just for reference there is a separate forum on Tek-Tips for ASP.NET here:

http://www.tek-tips.com/threadminder.cfm?pid=855

Senior Software Developer

http://www.scoutsft.com

 

[lisalis99]

It's a web application... that property is set to true....I checked... I would use bound columns, but I don't know what there will be; it may not be a set number as the user is building the grid with their selections from listbox 2-- Ugh!!!by the way, that stuff didn't work...

 

[SiriusBlackOp]

Oh... Problem Solved:

Change this:
For Each item In ListBox2.Items
sFields = ListBox2.SelectedItem.Value & ", "
Next

To This:
For Each item In ListBox2.Items
If item.Selected = True Then
'--Note: if item.Value doesn't work use item.Text
sFields += item.Value & ", "
End If
Next

In the first example you are only setting one selected item over and over. In the second example you will be looping through the Items collection of ListBox2 and when they are Selected then you will be appending (ie "+=" instead of just "=") sFields with each additional field name.


Senior Software Developer

http://www.scoutsft.com

 

[dglienna]

sheesh! This is how to concatenate in VB.

with List1
  For i = 0 To .ListCount - 1
    msg = msg & .List(i)
  Next i
end with

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[dglienna]

oops. wrong forum. sorry.

you can use the listcount property though

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[SiriusBlackOp]

Not to be rude, but just ignore the last 2 posts by dglienna. I think that is VB6 he is talking about, and the ListCount property is not a member of the ASP.NET listbox control. Personally I would rather see people use the .NET (x += y) or (x &= y) opperators than the old VB6 method of (x = x & y).

What he says about looping through the Items collection is true. You can do it more that just one way, but personally I prefer to loop through the collection rather than loop through the collection indexes only to refer back to the object held in the collection by index. Examples below…

Option 1 in code: (by object)
For each o as Object in ObjectCollection
MsgBox ( o.ToString )
Next

Option 2 in code: (by index)
For each i as Integer = 0 in ObjectCollection.Count
MsgBox ( ObjectCollection(i).ToString )
Next


Food for thought I guess.

Lisa, have you had the chance to try the change made in my last post?


Senior Software Developer

http://www.scoutsft.com

 

[lisalis99]

Hello! YESSSS!!!! It works!!!!! Awesome! Phew, that was a toughie--

One thing it doesn't like is the Address 1 and Address 2-- SQL error that says: Line 1: Incorrect syntax near '2'. and Incorrect Syntax near 1-- I'm guessing it's the space. It's like that in the DB, is there anyway to get around that one? I like to use underscores- I know to never use spaces, but I guess when it's being called in the query, it doesn't know what to do with the space that's there, do I have to strip it again?

You're awesome- thanks for the dedication to my challenge!I appreciate it.

 

[SiriusBlackOp]

Awesome!!

Well... It is the space and I think you can solve the space issue by surrounding each field name with brackets like [Address 1] and it will not hurt the [Name] etc fields.

Senior Software Developer

http://www.scoutsft.com