hinteractive02 hinteractive02 - 8 days ago 6
C# Question

c# - MySQL to Array to MSSQL Query Statement

I'm working on a program to link two databases (MySQL and MSSQL) and show them in a datagrid table.

I'm getting a count to get the number of arrays value, then assigning the array, then using the array value to return into a datagrid table.

The problem I have is

skuArray[RowCount++] = Convert.ToInt32(myReader[0]);
is returning error: cannot convert int to string. I changed it to
skuArray[RowCount++] = Convert.String(myReader[0]);
and it complied correctly but gives the message
Object reference not set to an instance of an object.


All SQL Queries have been tested and successfully execute.

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.IO;
using System.Xml.Serialization;
using System.Data.SqlClient;

namespace SQL_Database_Connector
{
public partial class Sync_Databases : Form
{
string serverInfo; // MySQL Database Information
string portInfo;
string databaseInfo;
string usernameInfo;
string passwordInfo;

string MSserverInfo; // MSSQL Database Information
string MSdatabaseInfo;
string MSusernameInfo;
string MSpasswordInfo;

public string[] skuArray;
public string queryString;
public int RowCount;

public Sync_Databases()
{
InitializeComponent();
}

private void Sync_Databases_Load(object sender, EventArgs e)
{
if (File.Exists("data.xml")) // MySQL Database
{
XmlSerializer xs = new XmlSerializer(typeof(Information));
FileStream read = new FileStream("data.xml", FileMode.Open, FileAccess.Read, FileShare.Read);
Information info = (Information)xs.Deserialize(read);
serverInfo = info.server;
portInfo = info.port;
databaseInfo = info.database;
usernameInfo = info.username;
passwordInfo = info.password;
read.Close();
}
try
{
string MyConnection = String.Format("Server={0}; Port={1}; Database={2}; Uid={3}; Pwd={4};", serverInfo, portInfo, databaseInfo, usernameInfo, passwordInfo);
string Query = "SELECT COUNT(*) " +
"FROM catalog_product_entity " +
"INNER JOIN catalog_product_entity_int " +
"ON catalog_product_entity_int.entity_id = catalog_product_entity.entity_id " +
"WHERE catalog_product_entity.sku IS NOT NULL " +
"AND catalog_product_entity.sku <> 0 " +
"AND(catalog_product_entity_int.attribute_id = '84' AND catalog_product_entity_int.value = '1');";
MySqlConnection MyConn = new MySqlConnection(MyConnection);
MySqlCommand MyCommand = new MySqlCommand(Query, MyConn);
MyConn.Open();
MySqlDataReader myReader;
myReader = MyCommand.ExecuteReader();
try
{
while (myReader.Read())
{
RowCount = myReader.GetInt32(0); // Get Row Count
//MessageBox.Show(RowCount.ToString()); // Test Row Count
}
}
finally
{
myReader.Close();
MyConn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Problem with Row Count: "+ ex.Message);
}
try
{
string MyConnection = String.Format("Server={0}; Port={1}; Database={2}; Uid={3}; Pwd={4};", serverInfo, portInfo, databaseInfo, usernameInfo, passwordInfo);
string Query = "SELECT catalog_product_entity.sku AS 'SKU' " +
"FROM catalog_product_entity " +
"INNER JOIN catalog_product_entity_int " +
"ON catalog_product_entity_int.entity_id = catalog_product_entity.entity_id " +
"WHERE catalog_product_entity.sku IS NOT NULL " +
"AND catalog_product_entity.sku <> 0 " +
"AND(catalog_product_entity_int.attribute_id = '84' AND catalog_product_entity_int.value = '1');";
MySqlConnection MyConn = new MySqlConnection(MyConnection);
MySqlCommand MyCommand = new MySqlCommand(Query, MyConn);
MyConn.Open();
MySqlDataReader myReader;
myReader = MyCommand.ExecuteReader();
try
{
while (myReader.Read())
{
skuArray[RowCount++] = Convert.ToString(myReader[0]); // Assigning Array Values
//MessageBox.Show(skuArray.ToString()); //T est
}
}
finally
{
myReader.Close();
MyConn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Problem with MySQL query to capture Array: "+ ex.Message);
}
if (File.Exists("data2.xml")) // MSSQL Database
{
XmlSerializer xs = new XmlSerializer(typeof(Information));
FileStream read = new FileStream("data2.xml", FileMode.Open, FileAccess.Read, FileShare.Read);
Information info = (Information)xs.Deserialize(read);
MSserverInfo = info.server;
MSdatabaseInfo = info.database;
MSusernameInfo = info.username;
MSpasswordInfo = info.password;
read.Close();
}
try
{
string connectionString = string.Format("Data Source={0}; Initial Catalog={1}; User ID={2}; Password={3};", MSserverInfo, MSdatabaseInfo, MSusernameInfo, MSpasswordInfo);
string sql = string.Format("SELECT ItemLookupCode,Description, Quantity, Price, LastReceived " +
"FROM Item " +
"WHERE ItemLookupCode IS IN {0} " +
"ORDER BY LastReceived ASC;", skuArray);
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
connection.Open();
dataadapter.Fill(ds, "sql_table");
connection.Close();
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "sql_table";
SqlConnection conn = new SqlConnection();
}
catch (Exception ex)
{
MessageBox.Show("Problem with SQL query or connection: "+ ex.Message);
}
}
}
}

Answer

Where have you initialised the array? I dont see any line which says skuArray = new string[RowCount]; RowCount is just a placeholder i am using.