Dim sTempTable As String = sUserName & "ItemsDetail"
Try
If TableExists(sTempTable) Then
Dim comm As New MySqlCommand("DROP TABLE " & sTempTable, con)
If con.State = ConnectionState.Closed Then
con.Open()
End If
comm.ExecuteNonQuery()
con.Close()
End If
If con.State = ConnectionState.Closed Then
con.Open()
End If
Dim cmd As New MySqlCommand("CREATE TABLE invoicesdata." & sTempTable & " (PONo VARCHAR(50) NOT NULL, InvNo VARCHAR(50) NOT NULL, LineNo SMALLINT(5) UNSIGNED NOT NULL, EncumbrChange DOUBLE(15,5) NOT NULL, InvDate DATETIME NOT NULL, ProcessDate DATETIME NOT NULL, Reviewed TINYINT(1) UNSIGNED NOT NULL, Posted TINYINT(1) UNSIGNED NOT NULL) ENGINE = InnoDB", con)
cmd.ExecuteNonQuery()
cmd = New MySqlCommand("INSERT INTO invoicesdata." & sTempTable & " (PONo, InvNo, LineNo, EncumbrChange, InvDate, ProcessDate, Reviewed, Posted) SELECT Invoices.PONo, Invoices.InvNo, Invoices.ItemsLine AS LineNo, -InvoiceAmt AS EncumbrChange, Invoices.InvDate, Invoices.ProcessDate, Invoices.Reviewed, Invoices.Posted FROM(Invoices) WHERE (((Invoices.PONo)='" & sPONoPassed & "') AND ((Invoices.Exhibit)=0))", con)
cmd.ExecuteNonQuery() '<-----this line throws the error
Catch dbex As MySqlException
MessageBox.Show("Cannot connect to the database. Check the network status and try again.", "Data access error")
Finally
con.Close()
End Try
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.