Databases
StackOverflow2013
Postsdbo
POGetting an int from a DataTable DataRow when strings normally appear
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGetting an int from a DataTable DataRow when strings normally appear
    primarykey
    data
    text
    <p>I have searched extensively for an answer here and elsewhere but have had little success.</p> <p>I am using OLEDB to connect to a spreadsheet and extract data to port to a database, and all is going fairly smoothly except one minor detail.</p> <p>There is a column called Skills where the entry I receive is usually a string, however the value can sometimes be an int like 334423, when I try to add this value to the new database, the entry is simply blank.</p> <p>Usual approach to extracting:</p> <pre><code> foreach(DataRow dr in dt.Rows) { /* dealing with other columns */ sTemp.skill = dr[3].ToString(); /* more stuff */ } </code></pre> <p>But I get absolutely no response when the column in this row is an int, just a blank string. It doesn't even throw any exceptions, which would at least give me some direction.</p> <p>I also tried casting it as an int:</p> <pre><code>sTemp.skill = (int)dr[3].ToString(); </code></pre> <p>(which didn't work)</p> <p>FYI: "sTemp" is an object with strings associated with it, skill being a string. The mysteriously quiet nature of this problem also makes it hard for me to know what I should be asking. Any OLEDB gurus out there, please help! Thanks</p> <p><strong>EDIT:</strong> After some more investigation, it seems that the problem was not with conversion/casting of strings to ints, but actually getting the data in the first place.</p> <p>In the instance of a field having a number, it will totally ignore it (for reasons I cannot think of), so there is nothing to parse. Mysterious behaviour indeed.</p> <p>Unless anyone knows of known problems getting ints from excel using an OleDbDataAdapter, then feel free to answer... Sorry to have wasted your time all, how embarrassing.</p> <p><strong>EDIT #2:</strong> Here is an updated code view:</p> <pre><code>using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace SQEPSkillsImporter { public partial class MainWindow : Form { string skillsFileName = ""; List&lt;SkillTemplate&gt; TemplateList = new List&lt;SkillTemplate&gt;(); public MainWindow() { InitializeComponent(); } private void skillsFilePathBtn_Click(object sender, EventArgs e) { this.skillsFileDialog.Filter = "Excel Worksheets|*.xls;*xlsx"; DialogResult result = skillsFileDialog.ShowDialog(); if (result == DialogResult.OK) { this.skillsFileName = skillsFileDialog.FileName; skillsFilePathTxBx.Text = skillsFileName; } } private void readSkillsBtn_Click(object sender, EventArgs e) { DataTable skillsTable = new DataTable("SkillsResults"); string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;" + " Data Source=" + this.skillsFileName + ";Extended Properties=Excel 8.0;"; using(OleDbConnection Connection = new OleDbConnection(connectionString)) { Connection.Open(); using(OleDbCommand Command = new OleDbCommand()) { Command.Connection = Connection; /* The columns I want are in A through D */ Command.CommandText = "SELECT * FROM [Skills$A:D]"; using (OleDbDataAdapter Adapter = new OleDbDataAdapter()) { Adapter.SelectCommand = Command; Adapter.Fill(skillsTable); } } Connection.Close(); Connection.Dispose(); } SkillTemplate sTemp = new SkillTemplate(); foreach (DataRow dr in skillsTable.Rows) { /* most sGroup, group and heading columns are blank, so remain the same unless changed, meaning the name wont be null */ if(dr[0].ToString() != "") sTemp.SuperGroup = dr[0].ToString(); if (dr[1].ToString() != "") sTemp.Group = dr[1].ToString(); if (dr[2].ToString() != "") sTemp.Heading = dr[2].ToString(); if (dr[3].ToString() != "") sTemp.Skill = dr[3].ToString(); /* VERY rough test textbox to verify that my output is correct before sending to DB */ this.skillsDumpTxBx.Text += sTemp.SuperGroup + " /// " + sTemp.Group + " /// " + sTemp.Heading + " /// " + sTemp.Skill + System.Environment.NewLine; } } } public class SkillTemplate { public string SuperGroup { get; set; } public string Group { get; set; } public string Heading { get; set; } public string Skill { get; set; } } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPhoenix
    1. USPhoenix
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGetting an int from a DataTable DataRow when strings normally appear
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POGetting an int from a DataTable DataRow when strings normally appear
      1. This table or related slice is empty.
      1. VTDownMod
    1. CO`(int)dr[3].ToString();` cannot work but this would: `int.Parse(dr[3].ToString());`
      singulars
      1. POGetting an int from a DataTable DataRow when strings normally appear
      1. USTim Schmelter
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload