Export SQL Server Data to Excel Using C#? - C# Discussion Boards

Don't post this here - if you got the code from an article, then there is a "Add a Comment or Question" button at the bottom of that article, which causes an email to be sent to the author. They are then alerted that you wish to speak to them.
Posting this here relies on them "dropping by" and realising it is for them.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

I want to write multiple attributes and their values in single node without closing tag in c#.

And at which point did you run into a problem?

Recursion: see Recursion.

If you had a question, we could help. As is, I can only wish you good luck with your intention Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

try json

C#

XElement request = new XElement( "IntlRateV2Request",
   new XAttribute( "USERID", _username ), 
   new XAttribute( "PASSWORD", _password )
);

request.Add( new XElement( "Revision", "2" ) );

etc.

please can you tell us what have you tried and where you are stuck?

I'm working on an assignment that requires a simple data transfer data of a SQL Server to MS Excel; using C#. I was able to develop the "foundation" of what I believe works/ However I'm unable to run the program. Assistance is appreciated!

namespace ProjectLab1
{
    class Program
    {
        static void Main(string[] args)
        {
            //Connection string needed to talk to Omnymbus
            string conString = "Driver={MySQL ODBC 5.3 ANSI Driver};"
                + "Server=devry.edupe.net;Port=4300;"
                + "Database=Inventory_XXXX;"
                + "uid=XXXX;pwd=DeVry_Student";
            OdbcConnection connection = new OdbcConnection(conString);
            connection.Open();
            //Get all data
            //string theQuery = "SELECT * FROM item";
            string theQuery = "SELECT * FROM item i, inventory v where i.invent_id=v.invent_id";
            OdbcDataAdapter DataAdapter = new OdbcDataAdapter(theQuery, connection);
            DataSet theData = new DataSet();
            DataAdapter.Fill(theData, "item");
            DataTable theTable = theData.Tables[0];

            Console.Write("ITEM".PadRight(30));
            Console.Write("QUANT".PadLeft(10));
            Console.Write("SIZE".PadLeft(10));
            Console.Write("COLOR".PadLeft(10));
            Console.Write("PRICE\n".PadLeft(10));//the "\n" moves cursor to next line
            Console.WriteLine("-".PadLeft(70, '-'));// "WriteLine" places cursor next line

            // Loop through all data results
            foreach (DataRow dataRow in theTable.Rows)
            {
                Console.Write(dataRow["invent_desc"].ToString().PadRight(30));
                Console.Write(dataRow["qoh"].ToString().PadLeft(10));
                Console.Write(dataRow["itemsize"].ToString().PadLeft(10));
                Console.Write(dataRow["color"].ToString().PadLeft(10));
                Console.WriteLine(((decimal)dataRow["curr_price"]).ToString("C").PadLeft(10));

            }

            {



                StreamWriter file = new StreamWriter(@"C:\Users\redfaction\Desktop\Data\" + strFileName);
                file.WriteLine(sb.ToString());
                File.Close();
                connection.Close(); // Close connection

                //Have program pause to keep from closing console window



            }
        }
    }
}

modified 17-Mar-15 4:58am.

Member 11527519 wrote:
I'm unable to run the program.

Care to be more specific? How is anyone supposed to know what you mean by that?

The difficult we do right away...
...the impossible takes slightly longer.

I'm sorry about that. The main issue here is that the code simply doesn't work and I've tried different ways to make a cmd-based program take a .sql file and export the data as a MS Excel file - as end result. Out of all the attempts I've made; this is the closest I've gotten but I'm unable to run the program due to a small number of errors I'm not familiar with. If possible I would appreciate it if you took a look at the code though a program like Visual Studio.

Have you tried the writing to excel using the interop and you are connecting using MySQL odbc. is your db really sql server?

Yes, it is a server that is holding SQL Datatables on a cloudserver that can be connected using ODBC. I'm able to connect to this server (that I have done before using cmd-based program to simply see particular tables, which HAS worked) I'm just simply trying to figure out how to export the data to MS Excel, by making a CSV file.

What are these small number of errors? Are they compilation errors? Are they runtime exceptions?

There are about 6 errors in total; But 5 are name errors giving the message of "ddlExportFormat, strFormat, ds and connection does not exist in current content". I felt like within the code I posted above they where accurately addressed towards their methods. and the Last error address a System.IO file saying

VB

'System.IO.File; does not contain a definition for Close"

Start over by first studying and running this example:

Using MySQL With C#[^]

Oh ... And why are you talking "SQL Server" even though you are using a MySQL ODBC driver?

modified 16-Mar-15 20:09pm.

hi i am a intermediate in c#
i want to open .po file and translate it from my repository
that i translate in the past
its not a single text file to open it very easily
i don't know whats the structure of this kind of file
i hope that some one have some experience and help me to
achieve my goal.
i will appreciate your answer
thx alot Smile | :)

modified 15-Mar-15 16:08pm.

You might want to avoid assuming everyone knows what you're talking about when you say ".po file". What is in this file?

Also, what's stopping you from doing this stuff? What don't you know how to do?

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

SecondLanguage[^] should help. It handles .po AND .mo files.

What do you get when you cross a joke with a rhetorical question?
---
The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.
---
Do questions with multiple question marks annoy you???

I am wondering what is the best way to edit a database that has multiple tables.

(Section 1) I have a Administration, Teacher and Student table that is linked up with the Person table which has a Primary Key.

The Person table consists of the general information of the person.

The Student table consists of information about the student; student ID and qualification code.

The Teacher table consists of information about the teacher; teacher ID, Reg No and password.

The Administration table consists of information about the admin; admin ID, Role and password.

As the Primary Key in the Person table is the ID, I have linked up with each of the other's table with their appropriate ID.

(Section 2) I have a Course and Qualification table that is linked up with the Student and Teacher table as well as each other.

The course table constists of the Course ID, Course Name ... and Teacher ID.

The Qualifications table consists of Qualification Code, Qualification Name and Duration.

There is a section where I have to create a view which shows just the Student ID, Course ID and has the Student's Marks in it.

I have got a combobox which then links up with the dgv(datagridview).

I have got insert and delete methods for both sections. Here is an example of the insert method into the Admin/Person table.

C#

try
 {
personTableAdapter.Insert(aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text);

administrationTableAdapter.Insert(Convert.ToInt32(aAID1.Text), aRole.Text, aPassword.Text);
MessageBox.Show(aFirstName.Text + " " + aSurname.Text + " has been added. Your ID is " + aAID1.Text);

this.personTableAdapter.Fill(this._30002195DataSet.Person);

this.administrationTableAdapter.Fill(this._30002195DataSet.Administration);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

Here is an example of the delete method in the Admin/Person table.

C#

try
            {
                personTableAdapter.Delete(Convert.ToInt32(aID.Text), aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text);
                administrationTableAdapter.Delete(Convert.ToInt32(aAID.Text), aRole.Text, aPassword.Text);
                MessageBox.Show("Person Deleted");
                this.personTableAdapter.Fill(this._30002195DataSet.Person);
                this.administrationTableAdapter.Fill(this._30002195DataSet.Administration);

            }
            catch (Exception)
            {
                MessageBox.Show("Cannot delete Person");
            }

Those methods above are working fine, what I'm having problems with, is with the editing/updating part.

I have tried a few things and haven't worked.

Here's an example of an edit, while trying to use textboxes.

SQL

personTableAdapter.Update(aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text, Convert.ToInt32(aID.Text), aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text);
                administrationTableAdapter.Update(aRole.Text, aPassword.Text, Convert.ToInt32(aAID.Text), aRole.Text, aPassword.Text);
                

                personBindingSource.EndEdit();
                administrationBindingSource.EndEdit();
                administrationTableAdapter.Update(_30002195DataSet.Administration);
                personTableAdapter.Update(_30002195DataSet.Person);
                MessageBox.Show("Person Updated");

                this.personTableAdapter.Fill(this._30002195DataSet.Person);
                this.administrationTableAdapter.Fill(this._30002195DataSet.Administration);

Here I tried to do the new values/original values, while trying to use textboxes

SQL

personTableAdapter.Update(aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text, Convert.ToInt32(aID.Text), aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text);

administrationTableAdapter.Update(aRole.Text, aPassword.Text, Convert.ToInt32(aAID.Text), aRole.Text, aPassword.Text);

Trying to use the example through the mdsn, this is trying to use the datagridview.

this.Validate();
                personBindingSource.EndEdit();
                teacherBindingSource.EndEdit();

                _30002195DataSet.PersonDataTable deletedPerson = (_30002195DataSet.PersonDataTable)
                    _30002195DataSet.Person.GetChanges(DataRowState.Deleted);

                _30002195DataSet.PersonDataTable newPerson = (_30002195DataSet.PersonDataTable)
                    _30002195DataSet.Person.GetChanges(DataRowState.Added);

                _30002195DataSet.PersonDataTable modifiedPerson = (_30002195DataSet.PersonDataTable)
                _30002195DataSet.Person.GetChanges(DataRowState.Modified);

                try
                {
                    if (deletedPerson != null)
                    {
                        personTableAdapter.Update(deletedPerson);
                    }

                    teacherTableAdapter.Update(_30002195DataSet.Teacher);

                    if (newPerson != null)
                    {
                        personTableAdapter.Update(newPerson);
                    }

                    if (modifiedPerson != null)
                    {
                        personTableAdapter.Update(modifiedPerson);
                    }
                    _30002195DataSet.AcceptChanges();
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    if (deletedPerson != null)
                    {
                        deletedPerson.Dispose();
                    }
                    if (newPerson != null)
                    {
                        newPerson.Dispose();
                    }
                    if (modifiedPerson != null)
                    {
                        modifiedPerson.Dispose();
                    }
                    MessageBox.Show("Updated");
                    this.personTableAdapter.Fill(this._30002195DataSet.Person);
                    this.teacherTableAdapter.Fill(this._30002195DataSet.Teacher);
                }

Now because I am trying to edit a certain user which requires the Person table along with the other table, it just doesn't seem to work at all.

With the datagridview, I made a new view in the sql and it has both tables combined and shows when I bring it out from the datasource, but where I go into the dataset builder and try to create an update method, all I get is the "Update" not what I would get when I created the update method from just the person's table by itself.

Can someone provide me with an example or help me out someway because I am struggling with this, I can't seem to find much information at all.

Thanks.

I'm relatively new to .NET. Using VS 2013.
I am not sure what to call this or how to search it so I apologize if this is a duplicate entry.

I have a listbox (lstConditions) populated with quote conditions. Each being a string with a Int ConditionID associated with it.

There can be any number of conditions selected for each quote (from 1 to 15 items for example) thereby setting the checkbox as checked.

Currently when a quote is saved the selected items from the lstConditions listbox are saved to an SQL Database table with each item being a new row. So each quote saved can have up to 15 rows in the table per quote.

Is there a way to store the list of selected items as one string in one row per quote?

i.e.

QuoteID = 1
SelectedConditions = 1,2,3,5,6,9,12,15

If QuoteID 1 was modified, only 1 row would need changed.

Would it be difficult to repopulate the list of conditions the next time someone was to view QuoteID 1 again?

Hope this make sense.
Thanks in advance.

Don't.
Seriously, it's simple enough to do, but it makes processing the SQL data a total pain in the backside for anything more complex than minor information retrieval. For example, if you remove "condition 7" from the system completely, how are you going to remove all references to it from all the strings? Keeping it as separate rows makes that trivially easy.

Instead, if you want to display multiple conditions as a string then use SQL to return them as a comma separated list:

SQL

SELECT Id, Conditions=
   STUFF((SELECT ',' + CONVERT(VarChar(10), Condition)
      FROM myTable b
      WHERE b.Id = a.Id
      FOR XML PATH('')), 1, 1, '')
FROM myTable a
GROUP BY Id

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

It probably won't be a problem for this query, but in general this method would interpret the concatenated column as XML. This could lead to errors, or to characters being "entitized".

The fix is fairly simple:

SQL

SELECT Id, Conditions=
   STUFF((SELECT ',' + CONVERT(varchar(10), Condition)
      FROM myTable b
      WHERE b.Id = a.Id
      FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
FROM myTable a
GROUP BY Id

Concatenating Row Values in Transact-SQL[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

from what I understand, when using session ended event, the action will be done if the computer shuts down or if the user logs off.
I have this event, but the action only occurs when the computer shuts down, but when the user logs off, nothing happens.

Here is the code I'm using:

//After InitializeComponent()
SystemEvents.UserPreferenceChanging += new UserPreferenceChangingEventHandler(SessionEnded);

static void SessionEnded(object sender, UserPreferenceChangingEventArgs e)
{  
//action
}

Can some one please tell me how to make my app do something, when the user logs off?
Thanks.

Member 10850253 wrote:
I have this event

You have the "UserPreferenceChanging" event.

Try SessionEnding[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

I tried:

InitializeComponent();
            //Log off
            SystemEvents.SessionEnding += (s, e) =>
            {
                switch(e.Reason){
                    case SessionEndReasons.Logoff:
                    ComPort.Close();
                    ComPort.Write(new byte[] { 48 }, 0, 1);
                    break;
                    case SessionEndReasons.SystemShutdown:
                    ComPort.Close();
                    ComPort.Write(new byte[] { 48 }, 0, 1);
                    break;
                }
                
            };

But still nothing works.
Please help me.
Thanks.