Solved

Creating a pdf with a table and writing data to it

Posted on 2009-04-07
28
498 Views
Last Modified: 2012-05-06
Am trying to get the attached coding to work. When I run it in Visual Studio 2005 using Debug/Start Debugging, I get the following error on the line

using (System.Data.SqlClient.SqlDataReader rdr = cmd.ExecuteReader())

"ExecuteReader: Connection property has not been initialized"

Also,
What I want to do is to create a multipage pdf suitable for printing labels. When I get it to work, is this the sort of coding to use?
using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using iTextSharp.text;

using iTextSharp.text.pdf;
 
 

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        Document doc = new Document();

        PdfWriter.GetInstance(doc, new System.IO.FileStream("Test.pdf", System.IO.FileMode.Create));

        doc.Open();

        

        PdfPTable table = new PdfPTable(2);

        //actual width of table in points

        table.TotalWidth = 216f;

        //fix the absolute width of the table

        table.LockedWidth = true;
 

        //relative col widths in proportions - 1/3 and 2/3

        float[] widths = new float[] { 1f, 2f };

        table.SetWidths(widths);

        table.HorizontalAlignment = 0;

        //leave a gap before and after the table

        table.SpacingBefore = 20f;

        table.SpacingAfter = 30f;
 

        PdfPCell cell = new PdfPCell(new Phrase("Products"));

        cell.Colspan = 2;

        cell.Border = 0;

        cell.HorizontalAlignment = 1;

        table.AddCell(cell);
 
 

        String MyConString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=MySQLB15.xxxx.com;DATABASE=xxxxx;UID=xxxx;PASSWORD=xxxx;OPTION=3;";

        

        string query = "SELECT tblschools.fldSchool_ID, fldContact FROM tblschools RIGHT JOIN tblselectedschools ON tblschools.fldSCHOOL_ID = tblselectedschools.fldSCHOOL_ID WHERE tblselectedschools.fldUserID = 2 AND tblselectedschools.fldSelected = 1 ORDER BY fldName";
 

        System.Data.Odbc.OdbcConnection dbConnection = new System.Data.Odbc.OdbcConnection(MyConString);

        

   

        System.Data.Odbc.OdbcDataAdapter adapter = new System.Data.Odbc.OdbcDataAdapter();

        adapter.SelectCommand =

            new System.Data.Odbc.OdbcCommand(query, dbConnection);

        System.Data.Odbc.OdbcCommandBuilder builder =

            new System.Data.Odbc.OdbcCommandBuilder(adapter);

        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query);

       

     

            try

            {

                dbConnection.Open() ;

              

               

                using (System.Data.SqlClient.SqlDataReader rdr = cmd.ExecuteReader())

                {

                    while (rdr.Read())

                    {

                        table.AddCell(rdr[0].ToString());

                        table.AddCell(rdr[1].ToString());

                    }

                }

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message);

            }

            doc.Add(table);

        

 

    }

    }

Open in new window

0
Comment
Question by:bogorman
  • 15
  • 13
28 Comments
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
While you've initialised the OdbcDataAdapter with a connection (and opened it), you haven't initialised your SqlCommand with a connection.

I'd recomment using the .Net driver for MySql:
http://dev.mysql.com/downloads/connector/net/5.2.html
0
 

Author Comment

by:bogorman
Comment Utility
Thanks for your help so far.
Have tried to initialise SqlCommand with a connection by modifying the coding as shown below, but when I try and build it I get two errors:

The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid arguments      
 and
Argument '2': cannot convert from 'string' to 'System.Data.SqlClient.SqlConnection'
System.Data.Odbc.OdbcConnection dbConnection = new System.Data.Odbc.OdbcConnection(MyConString);      

 System.Data.Odbc.OdbcDataAdapter adapter = new System.Data.Odbc.OdbcDataAdapter();

        adapter.SelectCommand = new System.Data.Odbc.OdbcCommand(query, dbConnection);

        

        System.Data.Odbc.OdbcCommandBuilder builder = new System.Data.Odbc.OdbcCommandBuilder(adapter);

         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query,MyConString);

Open in new window

0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
Change the last line to this, and also open the connection
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, new System.Data.SqlClient.SqlConnection(MyConString));

comd.Connection.Open();

Open in new window

0
 

Author Comment

by:bogorman
Comment Utility
It now builds and publishes ok but when I run it (by Debug) I get an error on the first of the lines you changed:

Keyword not supported 'driver'

I assume this is due to an error in the line:

        String MyConString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=MySQLB15.webcontrolcenter.com;DATABASE=lifenet;UID=brianog;PASSWORD=keizer321;OPTION=3;";
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
Ok, should have picked up this earlier, I can't get why you're using both the System.Data.Odbc & System.Data.SqlClient.
The error you're getting is because the SqlConnection is purely for MS SQL Server, so doesn't support ODBC drivers.

For the moment , as you've set up the OdbcCommand I suggest you use that.

If you're using MySql you really should download the MySql connector from the link I provided before, and add it as a reference to your project. Then add the MySql.Data.MySqlClient namespace. Then change SqlConnection to MySqlConnection, SqlCommand to MySqlCommand etc.
Your connectionstring would be
String MyConString = "SERVER=<host>;DATABASE=<db>;UID=<uid>;PWD=<pwd>;";
You are also going to need to go straight to your Hosting provider and change your uid & password immediately. You've just published your login credentials.
using (System.Data.Odbc.OdbcDataReader rdr = adapter.SelectCommand.ExecuteReader()) {

  while (rdr.Read()) {

    table.AddCell(rdr[0].ToString());

     table.AddCell(rdr[1].ToString());

  }

}

Open in new window

0
 

Author Comment

by:bogorman
Comment Utility
Thanks. Stupid of me to publish uname and password. I usually edit them out when including them in a question. Have changed them now.
Think I have altered the coding as you suggested but when I dubug I get the error:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
 
When the webpage opens.

Have not specified a dataSOURCE name, just the server and dataBASE name, plus the login details.


using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using iTextSharp.text;

using iTextSharp.text.pdf;

using MySql.Data.MySqlClient;
 
 

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        Document doc = new Document();

        PdfWriter.GetInstance(doc, new System.IO.FileStream("Test.pdf", System.IO.FileMode.Create));

        doc.Open();

        

        PdfPTable table = new PdfPTable(2);

        //actual width of table in points

        table.TotalWidth = 216f;

        //fix the absolute width of the table

        table.LockedWidth = true;

 

        //relative col widths in proportions - 1/3 and 2/3

        float[] widths = new float[] { 1f, 2f };

        table.SetWidths(widths);

        table.HorizontalAlignment = 0;

        //leave a gap before and after the table

        table.SpacingBefore = 20f;

        table.SpacingAfter = 30f;

 

        PdfPCell cell = new PdfPCell(new Phrase("Products"));

        cell.Colspan = 2;

        cell.Border = 0;

        cell.HorizontalAlignment = 1;

        table.AddCell(cell);

 

 

        String MyConString = "SERVER=xxxx;DATABASE=xxxx;UID=xxxx;PASSWORD=xxxx;";

        

        string query = "SELECT tblschools.fldSchool_ID, fldContact FROM tblschools RIGHT JOIN tblselectedschools ON tblschools.fldSCHOOL_ID = tblselectedschools.fldSCHOOL_ID WHERE tblselectedschools.fldUserID = 2 AND tblselectedschools.fldSelected = 1 ORDER BY fldName";

 

        System.Data.Odbc.OdbcConnection dbConnection = new System.Data.Odbc.OdbcConnection(MyConString);

        

   

        System.Data.Odbc.OdbcDataAdapter adapter = new System.Data.Odbc.OdbcDataAdapter();

        adapter.SelectCommand =

            new System.Data.Odbc.OdbcCommand(query, dbConnection);

        System.Data.Odbc.OdbcCommandBuilder builder =

            new System.Data.Odbc.OdbcCommandBuilder(adapter);

        MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query);

     

     

            try

            {

                dbConnection.Open() ;

              

               

                using (System.Data.Odbc.OdbcDataReader rdr = adapter.SelectCommand.ExecuteReader()) {

  while (rdr.Read()) {

    table.AddCell(rdr[0].ToString());

     table.AddCell(rdr[1].ToString());

  }

}

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message);

            }

            doc.Add(table);

        

 

    }

    }

 

Open in new window

0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
I think you misunderstood me.

When using the ODBC connector, you need to specify the driver, as you did originally.

When using the SqlClient or MySql Connector, you do not have to specify a driver as the are designed to work only with MS SQL Server & MySql respectively

MySql.Data.MySqlClient.MySqlConnection connMySql = new MySql.Data.MySqlClient.MySqlConnection("SERVER=xxxx;DATABASE=xxxx;UID=xxxx;PASSWORD=xxxx;");
 

System.Data.Odbc.OdbcConnection connOdbc = new System.Data.Odbc.OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver};SERVER=MySQLB15.xxxx.com;DATABASE=xxxxx;UID=xxxx;PASSWORD=xxxx;OPTION=3;");

Open in new window

0
 

Author Comment

by:bogorman
Comment Utility
I think I confused you!
I did try to follow your advice re the MySQL driver.
Using VS2005, I selected Website/Add Reference and found in 'Recent' MySQL.Data.dll which I selected and thought I had added this. However, when I right-click the project name in Solution Explorer and select Property Pages it is not listed under References.
Have I selected the correct dll?
The MySQL Connector NET 1.07 and MySQL/ODBC 3.51 are both listed in Add or Remove Programs in Control Panel.
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
MySqlData.dll is the MySql Connector driver. You should just have to double click on it in the Add Reference dialog, then check for it in the bin directory in your project.

Just import MySql.Data.MySqlClient to the your code file, and try using this:



    /* Create the Connectionstring

     * PersistSecurityInfo = false means that the password is not displayed in the

     * connectionstring of the MySqlCommand.

     */

    MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();

    connBuilder.Database ="";

    connBuilder.Password ="";

    connBuilder.PersistSecurityInfo = false;

    connBuilder.Server = "";

    connBuilder.UseCompression = true;

    connBuilder.UserID = "";
 

    // Create the connection

    MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
 

    // Create the select command

    MySqlCommand comm = new MySqlCommand(

      "SELECT tblschools.fldSchool_ID, fldContact " +

      "FROM tblschools " +

      "RIGHT JOIN tblselectedschools ON tblschools.fldSCHOOL_ID = tblselectedschools.fldSCHOOL_ID " +

      "WHERE tblselectedschools.fldUserID = @userID " +

      "AND tblselectedschools.fldSelected = @selected " +

      "ORDER BY fldName", conn);

    comm.Parameters.AddWithValue("userID", 2);

    comm.Parameters.AddWithValue("selected", 1);
 

    try {

      // Open and execute the command

      conn.Open();

      using (MySqlDataReader reader = comm.ExecuteReader()) {

        while (reader.Read()) {

          table.AddCell(reader[0].ToString());

          table.AddCell(reader[1].ToString());

        }

      }
 

    } finally {

      conn.Dispose();

      comm.Dispose();

    }

Open in new window

0
 

Author Comment

by:bogorman
Comment Utility
Have tried the coding but I get errors related to MySQLConnectionStringBuilder:

The type or namespace name MySQLConnectionStringBuilder could not be found

and

MySQL.Data.MySQLClient.MySQLParameterCollection does not contain a definition for AddWithValue

Have searched the net but cannot find the answer.
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
It sounds like you haven't imported the namespace, Have you placed
using MySql.Data.MySqlClient;
at the top of the file?

I've also just looked through the source for v.5.2.5 of the MySqlConnector, and the MySQLParameterCollection definately contains a definition for AddWithValue(string, object)
0
 

Author Comment

by:bogorman
Comment Utility
It's strange because I have
 
using MySql.Data.MySqlClient;

with all the other namespaces at the top. It is the last one, but I don't think the order is significant.

Sometimes, when I hover over an error like this (it is underlined with a squiggly blue line) it displays suggestions to correct the error but it doesn't in this case.  Is there anything else I can do to correct it?
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
That's utterly bizarre as I've copied that code direct from VS2008 (which compiles). When you right click on MySqlConnectionStringBuilder or MySqlConnection, is there an option for "Go to definition"? And does it open a new window called .... [from metadata] when you click on it?
0
 

Author Comment

by:bogorman
Comment Utility
When I do that I get a dialog

Cannot navigate to MySQLConnectionStringBuilder

And a similar message with MySQLConnection

However, I have found two things:

The comments you have in your coding a preceded by /* or * or */.    Should they be //. Anyway, I have replaced them, but it makes no difference.

What I have found is that if I start typng in 'MySQL....' I get an Intellisense (?) dropdown, but it doesn't contain MySQLConnectionStringBuilder (it DOES contain MySQLConnection). Could this be something to do with using VS2005 and not VS2008?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 15

Expert Comment

by:oobayly
Comment Utility
Can't see why the MySql connector wouldn't work in VS2005, I take it that your project is .Net v.2.0 or above?
Can I suggest that you download the drivers using the "Windows Binaries, no installer (ZIP)" link.
Then unzip just the MySql.Data.dll to a directory somewhere on your machine.
Remove the current reference to MySql.Data from your project.
Add a new reference, but instead of using MySql.Data.dll from the .Net or Recent tab, select the Browse tab, and use the file that you've just unzipped.

I've just done exactly that, and again no compile errors.

Regarding the my comments, /* */ are multiline comments whereas // is a single line comment
0
 

Author Comment

by:bogorman
Comment Utility
Hi. Sorry to show my ignorance! How can I find out the NET version of my project?

Also, I find I have four NET dll's already on my machine:

Program Files\MySQL\MySQL Connector Net 1.0.7\bin\.NET 1.0\MySql.Data.dll
Program Files\MySQL\MySQL Connector Net 1.0.7\bin\.NET 1.1\MySql.Data.dll
Program Files\MySQL\MySQL Connector Net 1.0.7\bin\.NET 2.0\MySql.Data.dll
Program Files\MySQL\MySQL Connector Net 1.0.7\bin\.Mono 1.0\MySql.Data.dll


0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
Aha, you're seemed to have been using pretty much the oldest version of the MySql Connector.

To determine which framework you're using, open the Project Properties and under the Application tab, you should see a dropdownlist showing "Target Framework"
If you're using VS 2005, you're almost definately using .Net v.2
0
 

Author Comment

by:bogorman
Comment Utility
Cannot find the Application tab in Project Properties. If I right-click the Solution in Project Explorer and select Configuration Properties/Configuration it lists under Platform '.NET'. It does not specify which version.
I have the dll in the bin folder but it is still not listed under References in Property Pages. Could it be that I have not installed it into the GAC? If so, how do I do that? Seem to remember I had this problem before.
Also, it is not listed under .NET in Add Reference.
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
You need to look at the Project Properties, not solution properties, ie right click on the PROJECT (not the solution) and select properties. On the left hand side of the window, you'll get a list of tabs, the top one is Application and when you click on that you'll see the Target Framework.

The Mysql drivers don't need to be in the GAC. Assemblies installed in the GAC won't necessarily be listed in Visual Studio's Add Reference list. This is why I said to use BROWSE option and select the library that I told you to unzip.

If that fails create a new Solution, and try adding the MySql reference using the BROWSE option only. If that doesn't work, then I can only assume that your VS installation is borked.
0
 

Author Comment

by:bogorman
Comment Utility
If I right-click on the project there is only Property Pages, not Properties. If I click on that, the left hand pane shows References, Build, Accessibility, Start Options and MSBuild Options. There is no Application. I have created a Website project - does this make a difference?
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
Apologies, but if you do look under the Build tab you should see a Target Framework option.
0
 

Author Comment

by:bogorman
Comment Utility
No, there is no Target Framework option but there is Configuration Manager and that lists the Active Solution Platform as '.NET' but it doesn't state the version.
0
 
LVL 15

Accepted Solution

by:
oobayly earned 500 total points
Comment Utility
OK, another way to tell is if this line compiles as Generics were only introduced in .Net v.2
List<string> foo = new List<string>()

Finally, a 3rd way to tell is to look at the debug output when building the site, check for any mentions of:
"c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\"

Have you had any luck with getting the MySql driver working in your project?
0
 

Author Comment

by:bogorman
Comment Utility
Sorry for delay.
Have, as you suggested, installed version 5.2.5 of MySQL Connector.NET using the windows installer package and it appears to work until the program tries to open the connection and then I get an access denied error. Am using the new username and password. Have emailed the web providers and asked if it could be anything at their end. Assume there is nothing in the coding that could do this.
0
 

Author Comment

by:bogorman
Comment Utility
We're really getting somewhere now! The problem was due to the web provider not implementing the change of password, etc, properly.
I can now run the program with no errors but it does not create the pdf file. Thought it was due to not closing the document and I tried:

doc.Close;

After the line doc.Add(table);

but I get an error:

Only assignment, call, increment, decrement, and new object expressions car be used as a statement
0
 

Author Comment

by:bogorman
Comment Utility
Forgot the parentheses after the close statement. Should have been doc.Close();
Thanks so much for all your help. will assign the points.
0
 

Author Closing Comment

by:bogorman
Comment Utility
Thanks so much for all the work you have done on this. I really appreciate it.
Brian
0
 
LVL 15

Expert Comment

by:oobayly
Comment Utility
No worries. Glad it all works now.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 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

9 Experts available now in Live!

Get 1:1 Help Now