Error Import Database mdb into Sql Server

Question

Error Import Database mdb into Sql Server

c# sqlbulkcopy sql-server

Question

Hello I have an error on this c# code,I have an exception on code: MessageBox.Show("Salto sulla query 3 "+ex), but I can not understand why, I loaded the exception image that is generated below,can you help me thanks.

  MessageBox.Show("Aggiorno Articoli ");
                //APRO LA CONNESSIONE AL FILE 
                dbConn = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" + dialog.FileName + "; Persist Security Info = False; Jet OLEDB:Database Password = " + textBoxPwdComet.Text + "; Mode = Share Deny None");
                //APRO LA CONNESSIONE
                SqlConnection conn = db.apriconnessione();
                //CREO LA TABELLA TEMPORANEA
                String QueryTemp = "CREATE TABLE TabellaTemp(CODMARCA varchar(MAX),CODART varchar(MAX),DESCR varchar(MAX),UM varchar(MAX),PRZNETTO money,PRZCASA money,DATAAGG datetime,);";
                SqlCommand cmdTemp = new SqlCommand(QueryTemp, conn);
                cmdTemp.ExecuteNonQuery();
                //COPIA DEI DATI NELLA TABELLA TEMPORANEA
                string query = "SELECT CODMARCA,CODART,DESCR,UM,PRZNETTO,PRZCASA,DATAAGG FROM ARTICOLI";
                OleDbDataAdapter da = new OleDbDataAdapter(query, dbConn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                SqlBulkCopy bulk = new SqlBulkCopy(conn);
                bulk.DestinationTableName = "TabellaTemp";
                bulk.WriteToServer(dt);
            //Setto tutti gli articoli come non disponbili(QUELLI COMET)
            try
            {
                String Query2 = "Update Articolo set Stato = 'Nondisponibile' where Importato = 'COMET' ";
                cmdTemp = new SqlCommand(Query2, conn);
                cmdTemp.ExecuteNonQuery();
            }

            catch(Exception ex)
            {
                MessageBox.Show("Salto su query 2");
            }
            //Aggiorno gli articoli, quelli che non vengono aggiornati non sono piÃ¹ disponbili

            try {
                String Query3 = "Update Articolo set Articolo.Stato = 'Disponibile',Articolo.Prezzo = TabellaTemp.PRZNETTO,Articolo.PrezzoListino = TabellaTemp.PRZCASA,Articolo.DataAggiornamento = TabellaTemp.DATAAGG,Articolo.Descrizione = TabellaTemp.DESCR,Articolo.UM = TabellaTemp.UM from Articolo inner join TabellaTemp on TabellaTemp.CodMarca = Articolo.CodMarca and TabellaTemp.CODART = Articolo.CodArt where Articolo.Importato = 'COMET' ";
                cmdTemp = new SqlCommand(Query3, conn);
                cmdTemp.ExecuteNonQuery();
            }

            catch(Exception ex)
            {
                MessageBox.Show("Salto sulla query 3 "+ex);
            }

                //FINE COPIA DATI TABELLA TEMPORANEA

                //ELIMINO LA TABELLA TEMPORANEA
                QueryTemp = "DROP TABLE TabellaTemp";
                cmdTemp = new SqlCommand(QueryTemp, conn);
                cmdTemp.ExecuteNonQuery();

                //FINE 
                conn.Close();

                MessageBox.Show("Aggiornamento Articoli terminato!! ");


                CaricamentoDataGridNonDisponbili();

Exception:

user7538827

Popular Answer

First try setting your command object to nothing.

cmdTemp = Nothing;

If that doesn't fix it, then try investigating why your update query for query 3 is timing out. I would first convert your UPDATE statement into a SELECT statement. Verify that you are getting results, and that you aren't taxing the server with your query.

SELECT a.Stato, a.Prezzo, a.PrezzoListino, a.DataAggiornamento, a.Descrizione, a.UM, 
   t.PRZNETTO, t.PRZCASA, t.DATAAGG, t.DESCR, t.UM
FROM Articolo a
INNER JOIN TabellaTemp t on t.CodMarca = a.CodMarca 
   and t.CODART = a.CodArt 
WHERE a.Importato = 'COMET'

If your SELECT statement is working properly, then I'm betting your UPDATE needs more time to run. I believe the timeout property for your command is 30 seconds. You could default this to 0 to run for as long as you wanted, but I would be careful on doing that. Try toying around with the timeout statement. This should fix things up for you.

String Query3 = "Update Articolo set Articolo.Stato = 'Disponibile',Articolo.Prezzo = TabellaTemp.PRZNETTO,Articolo.PrezzoListino = TabellaTemp.PRZCASA,Articolo.DataAggiornamento = TabellaTemp.DATAAGG,Articolo.Descrizione = TabellaTemp.DESCR,Articolo.UM = TabellaTemp.UM from Articolo inner join TabellaTemp on TabellaTemp.CodMarca = Articolo.CodMarca and TabellaTemp.CODART = Articolo.CodArt where Articolo.Importato = 'COMET' ";
cmdTemp = Nothing;
cmdTemp = new SqlCommand(Query3, conn);
// Setting command timeout to 2 minutes
cmdTemp.CommandTimeout = 120;
cmdTemp.ExecuteNonQuery();

I would honestly work on getting your UPDATE statement running at a more optimal speed if this fixes things.

HKImpact

View more on Stack Overflow

Nadeem · Accepted Answer · 3/3/2017 8:20:34 PM

Accepted Answer

Your query is timing out ie: not completing in default 30 second. Setting the CommandTimeout to higher value is not a good practice. To actually fix this, you need to provide the table schema (indexing if any) for all the table involved. Based on that you need to optimize your query to run faster using proper indexing.

Nadeem

Error Import Database mdb into Sql Server

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator