Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi everyone!
 
I am running into an issue with data conversion failing that I cannot pinpoint as to why this is happening.
 
(My apologies for the length of this code).
Here is the code that I have so far:
 
if (File.Exists("C:\\Program Files (x86)\\Pico Technology\\TMS\\PicoScope.exe"))
{
     SqlCeCommand tableNameGeneratorCommand = new SqlCeCommand("SELECT table_name FROM information_schema.tables WHERE (table_name <> 'docfiles') AND (table_name <> 'waveform_files') AND " + "(table_name <> 'vehicles') AND (table_name <> 'accountmanagement') AND (table_name <> 'engineandtransmissioncodes') AND (table_name != 'enginecodemaster') AND (table_name != 'transmissioncodemaster')", conn);
     tableNameGeneratorCommand.CommandType = CommandType.Text;
     SqlCeDataReader tableNameGeneratorCommand_dr = tableNameGeneratorCommand.ExecuteReader();
 
     while (tableNameGeneratorCommand_dr.Read())
     {
          SqlCeCommand columnNameGeneratorCommand = new SqlCeCommand("SELECT DISTINCT column_name FROM information_schema.columns " + "WHERE (table_name <> 'docfiles') AND (table_name <> 'waveform_files') AND (table_name <> 'vehicles') AND (table_name <> 'engineandtransmissioncodes') " + "AND (column_name <> 'vehicle_key') AND table_name = @table_name", conn);
 
          columnNameGeneratorCommand.CommandType = CommandType.Text;
          columnNameGeneratorCommand.Parameters.AddWithValue("@table_name", tableNameGeneratorCommand_dr.GetString(0));
          SqlCeDataReader columnNameGeneratorCommand_dr = columnNameGeneratorCommand.ExecuteReader();
 
          while (columnNameGeneratorCommand_dr.Read())
          {
               SqlCeCommand keyCommand = new SqlCeCommand("SELECT 'SELECT DISTINCT ' + [2] + ' FROM ' + [1] FROM __sysobjects WHERE [1] = @TableName AND [2] = @ColumnName", conn);
               keyCommand.CommandType = CommandType.Text;
               keyCommand.Parameters.AddWithValue("@TableName", tableNameGeneratorCommand_dr.GetString(0));
               keyCommand.Parameters.AddWithValue("@ColumnName", columnNameGeneratorCommand_dr.GetString(0));
               keyCommand.ExecuteNonQuery();
 
               SqlCeDataReader keyCommand_dr = keyCommand.ExecuteReader();
 
               while (keyCommand_dr.Read())
               {
                    SqlCeCommand keyCommandPrime = new SqlCeCommand(keyCommand_dr.GetString(0) + " LEFT OUTER JOIN vehicles ON " + tableNameGeneratorCommand_dr.GetString(0) + "." + columnNameGeneratorCommand_dr.GetString(0) +
                    " = Vehicles.[KEY] WHERE make = @make AND model = @model AND year = @year AND engine_code = @engine_code AND transmission_code = @transmission_code", conn);
                    keyCommandPrime.CommandType = CommandType.Text;
                    keyCommandPrime.Parameters.AddWithValue("@make", Convert.ToString(makeComboBox.SelectedItem));
                    keyCommandPrime.Parameters.AddWithValue("@model", Convert.ToString(modelComboBox.SelectedItem));
                    keyCommandPrime.Parameters.AddWithValue("@year", Convert.ToString(yearComboBox.SelectedItem));
                    keyCommandPrime.Parameters.AddWithValue("@engine_code", Convert.ToString(engineCodeComboBox.SelectedItem));
                    keyCommandPrime.Parameters.AddWithValue("@transmission_code", Convert.ToString(transmissionCodeComboBox.SelectedItem));
                    keyCommandPrime.ExecuteNonQuery();
 
                    SqlCeDataReader keyCommandPrime_dr = keyCommandPrime.ExecuteReader();
 
                    try
                    {
                         while (keyCommandPrime_dr.Read())
                         {
                              MessageBox.Show(keyCommandPrime_dr.GetString(0));
                         }
                    }
                    catch (Exception ex)
                    {
                         MessageBox.Show(ex.Message);
                    }
               }
          }
     }
}
 
The issue I am having is I get this message "Data conversion failed. [ OLE DB status value (if known) = 2 ]
 
It is failing on this line.
while (keyCommandPrime_dr.Read())
All 5 parameters are coming from 5 comboboxes and what the selections are before this function is fired.
 
Does anyone know what I'm missing? I can't see the problem.
Posted 30-May-13 10:52am
Edited 30-May-13 10:54am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I solved it myself.
 
I was trying to join columns that didn't match up with type of data between the 2.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 230
2 BillWoodruff 205
3 DamithSL 165
4 Sergey Alexandrovich Kryukov 155
0 OriginalGriff 5,130
1 DamithSL 4,157
2 Maciej Los 3,650
3 Kornfeld Eliyahu Peter 3,460
4 Sergey Alexandrovich Kryukov 2,811


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 30 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100