title: C# data.fill (conversion failure)
body: <p>Using C#, .NET 4, SQL, visual studio 2010 and sql server manager 2008.</p>
  
  <p>Hi guys, Seems like that same old easy to solve 101 question, but something has been bugging me.</p>
  
  <p>My program runs a query and grabs the data and fills a dataset.</p>
  
  <p>One of my fields is called pg (product group) and is set to a nvarchar(255).
  most of the pgs are numbers like 30,50,12 etc but there are exceptions to this such as T1B and 50a.</p>
  
  <p>For some reason I'm getting a conversion error.</p>
  
  <p>"Conversion failed when converting the varchar value 'T1B' to an int".</p>
  
  <p>In my code all I'm using is data.fill();</p>
  
  <p>Is it me or does it seem as though the data.fill is trying to assign the field PG as an integer because the first records it pulls looks like an int?
  To be honest I always thought the fill would mimic whatever the sql data types were to its equivalent.</p>
  
  <p>Hopefully its something easy that im overlooking.</p>
  
  <p>If you wish to see more code, let me know and i will edit this post.</p>
  
  <p>Many thanks</p>
  
  <p>SQL Query</p>
  
  <pre><code>USE [ShaftData]
  GO
  /****** Object:  StoredProcedure [dbo].[GetSalesParetotemp]    Script Date: 03/14/2012       09:30:13 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[GetSalesParetotemp] 
  @acct varchar(255), 
  @From date, 
  @Too date
  AS
  SELECT i.Acct,
     i.Name, 
     i.Document, 
     i.Part, 
     i.Qty, 
     i.Unit, 
     dbo.NEWPareto.Pareto, 
     i.pg,
     dbo.MyPgTable.PgName,
     i.[DateTime]
  
  FROM   
  OPENQUERY(SACBAUTO, 'SELECT dbo.iHeads.acct,
                              dbo.iHeads.name,
                              dbo.iLines.Document,
                              dbo.iLines.Part,
                              dbo.iLines.Pg,
                              dbo.iLines.Qty,
                              dbo.iLines.unit,
                              dbo.iHeads.[DateTime]  
                       FROM Autopart.dbo.iheads INNER JOIN     Autopart.dbo.iLines ON 
                       Autopart.dbo.Iheads.document =   autopart.dbo.iLines.document
                       GROUP By dbo.iHeads.acct,
                              dbo.iHeads.name,
                              dbo.iLines.Document,
                              dbo.iLines.Part,
                              dbo.iLines.Pg,
                              dbo.iLines.Qty,
                              dbo.iLines.unit,
                              dbo.iHeads.[DateTime]
                        ') i
  left JOIN
    dbo.NEWPareto
  ON 
  i.Part collate SQL_Latin1_General_CP1_CI_AS = dbo.NEWPareto.Part 
  left JOIN
  dbo.MyPgTable 
  ON  
    i.pg collate SQL_Latin1_General_CP1_CI_AS = dbo.MyPgTable.[pGroup]
  
  WHERE
   (i.[DateTime] BETWEEN @From AND @Too) AND
   i.Acct = @acct 
   AND i.pg != 60
   AND i.pg != 61
   AND i.pg != 62
  
   GROUP BY i.Acct,
     i.Name, 
     i.Document, 
     i.Part, 
     i.Qty, 
     i.Unit, 
     dbo.NEWPareto.Pareto, 
     i.pg,
     dbo.MyPgTable.PgName, 
     i.[DateTime]
  </code></pre>
  
  <p>And here's the method that calls it</p>
  
  <pre><code>try
          {
              string myConn = "Server=derp;" +
                              "Database=derp;" +
                              "uid=derp;" +
                              "pwd=derp;" +
                              "Connect Timeout=120;";
  
              string acct;// test using 1560
  
              SqlConnection conn = new SqlConnection(myConn);
              SqlCommand Pareto = new SqlCommand();
              BindingSource bindme = new BindingSource();
              SqlDataAdapter adapt1 = new SqlDataAdapter(Pareto);
              DataSet dataSet1 = new DataSet();
              DataTable table1 = new DataTable();
  
              acct = Acct;
  
              string fromDate = this.dateTimePicker1.Value.ToString("MM/dd/yyyy");
              string tooDate = this.dateTimePicker2.Value.ToString("MM/dd/yyyy");
  
              Pareto.Connection = conn;
              Pareto.CommandType = CommandType.StoredProcedure;
              Pareto.CommandText = "dbo.GetSalesParetotemp";
              Pareto.CommandTimeout = 120;
  
              Pareto.Parameters.AddWithValue("@acct", acct);
              Pareto.Parameters.AddWithValue("@from", fromDate);
              Pareto.Parameters.AddWithValue("@too", tooDate);
  
              SetCheckBoxValue(true);
              SetPictureBoxVisibility(true);
              //label1.Visible = true;
              adapt1.Fill(dataSet1, "Pareto");
              //label1.Visible = false;
              SetCheckBoxValue(false);
              SetPictureBoxVisibility(false);
  
              SetDataGrid(true, dataSet1, "Pareto", DataGridViewAutoSizeColumnsMode.AllCells);
  
              dataGridView1.AutoResizeColumns(
                  DataGridViewAutoSizeColumnsMode.AllCells);
          }
          catch (Exception execc)
          {
              MessageBox.Show("Whoops! Seems we couldnt connect to the server!"
                              + " information:\n\n" + execc.Message + execc.StackTrace,
                              "Fatal Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
          }
  </code></pre>