C# insert into from select [FIXED]

See more:

C#

Hi there,

I'm looking for an solution for an query in 3 multiple databases.

(1) First I will truncate table A in database A
(2) Then I will delete table B from database B
(3) Next step is insert into database B with an select from database C
(4) Then update database A
(5) And the last step is to insert all the data from database A into database C

SQL CODE

TRUNCATE TABLE BB.dbo.Credentials -- (1)

DELETE FROM CA.dbo.Badge WHERE UserField1 = 'CABB' -- (2)

INSERT INTO BB.dbo.Credentials (3)
 (id,Badge,firstname,middlename,lastname,createddatetime)
SELECT u.ID, w.VALUE, u.FIRSTNAME, u.MIDDLENAME, u.LASTNAME, w.CREATEDDATETIME FROM MM.dbo.User_ u 
INNER JOIN MM.dbo.WiegandUserValue w
ON u.ID = w.OWNERID

UPDATE BB.dbo.Credentials (4)
SET enabled = 1, resident = 1, initload = 1, accgrp = 1, facility = 0, userfield1 = 'CABB', record = 1, val1 = 0, val3 = 0, val4 = 0, val5 = 0


INSERT INTO CA.dbo.Badge (5)(FrstName,Middlename,Lastname,Badge,Enabled,Resident,InitLoad,AGroup1,Facility,UserField1)
SELECT firstname,middlename,lastname,value,enabled,resident,initload,accgrp,facility,userfield1 FROM cabb.dbo.Credentials

c# CODE

public void SQLQuery1()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringBiobridge());
            sc.Open();
 
            string query = string.Format("TRUNCATE TABLE BioBridge.dbo.Credentials");
 
            sc.Close();
        }
 
        public void SQLQuery2()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringCardAccess());
            sc.Open();
 
            string query = string.Format("DELETE FROM ca211LiveDB05242017_1213013.dbo.Badge WHERE UserField1 = 'CABB'");
 
            sc.Close();
        }
 
        public void SQLQuery3()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringBiobridge());
            sc.Open();
 
            string query = string.Format("INSERT INTO BioBridge.dbo.Credentials (id,value,firstname,middlename,lastname,createddatetime) " +
                "SELECT u.ID, w.VALUE, u.FIRSTNAME, u.MIDDLENAME, u.LASTNAME, w.CREATEDDATETIME FROM MorphoManager.dbo.User_ u " +
                "INNER JOIN MorphoManager.dbo.WiegandUserValue w ON u.ID = w.OWNERID");
 
            sc.Close();
        }
 
        public void SQLQuery4()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringBiobridge());
            sc.Open();
 
            string query = string.Format("UPDATE Biobridge.dbo.Credentials SET enabled = 1, resident = 1, initload = 1, accgrp = 1, facility = 0, userfield1 = 'CABB', record = 1, val1 = 0, val3 = 0, val4 = 0, val5 = 0");
 
            sc.Close();
        }
 
        public void SQLQuery5()
        {
            SqlConnection sc = new SqlConnection(GetConnectionStringCardAccess());
            sc.Open();
 
            string query = string.Format("INSERT INTO ca211LiveDB05242017_1213013.dbo.Badge (FrstName,Middlename,Lastname,Badge,Enabled,Resident,InitLoad,AGroup1,Facility,UserField1) " +
                "SELECT firstname, middlename, lastname, value, enabled, resident, initload, accgrp, facility, userfield1 FROM BioBridge.dbo.Credentials");
 
            sc.Close();
        }
 
        private void btnInsert_Click(object sender, EventArgs e)
        {
            SQLQuery1();
            SQLQuery2();
            SQLQuery3();
            SQLQuery4();
            SQLQuery5();
        }

What I have tried:

When I do above by the hand in SQL Management Studio it works, but when I do this in C# by an button_click it doesn't work. When I create an messagebox.show and I press on the button the messagebox will come up.

Does someone knows what I do wrong?

Thanks in advance!