-8

What's wrong with my code... I tried several things but getting the same error again and again.

Any help?? The Code is:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[SubDomainName].ToString());
conn.Open();
string temptable = "CREATE TABLE [dbo].[Tmptablenew]([TicketID] [int] NULL,[TicketDescription][varchar](max) NULL,[TicketAssignedTo] [varchar](100) NULL,[TicketCreatedDate] [datetime] NULL,[TicketStatus][varchar](50),CRMConnectionID [int] NULL,[TicketUpdatedDate] [Datetime] NULL,img [varchar](500) NULL)";
SqlCommand cmd = new SqlCommand(temptable, conn);
cmd.ExecuteNonQuery();
SqlCommand cmmd = new SqlCommand("select * from Tickets", conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmmd);
DataTable dt1 = new DataTable("dt1");
adapter.Fill(dt1);
cmmd.ExecuteNonQuery();

//BulkCopy the data in the DataTable to the temp table
using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
{
    bulk.DestinationTableName = "Tmptablenew";
    bulk.WriteToServer(result);
    conn.Close();
}
conn.Open();
string mergeSql = "merge into Tickets as Target " +
                     "using Tmptablenew as Source " +
                     "on " +
                     "Target.TicketID= Source.TicketID " +
                     "and Target.CRMConnectionID = Source.CRMConnectionID " +
                     "when not matched then " +
                     "insert (TicketID,TicketDescription,TicketAssignedTo,TicketCreatedDate,TicketStatus,CRMConnectionID,TicketUpdatedDate,img) values (Source.TicketID,Source.TicketDescription,Source.TicketAssignedTo,Source.TicketCreatedDate,Source.TicketStatus,Source.CRMConnectionID,Source.TicketUpdatedDate,Source.img);";

string mergesql1 = "Update Tickets SET TicketDescription=S.TicketDescription, TicketAssignedTo = S.TicketAssignedTo, TicketStatus = S.TicketStatus,TicketUpdatedDate = S.TicketUpdatedDate,img = S.img FROM Tickets t JOIN Tmptablenew AS S ON t.TicketID = S.TicketID and T.CRMConnectionID = S.CRMConnectionID";
cmd.CommandText = mergeSql;
cmd.ExecuteNonQuery();
cmmd.CommandText = mergesql1;
cmmd.ExecuteNonQuery();
cmd.CommandText = "drop table Tmptablenew";
cmd.ExecuteNonQuery();
//Clean up the temp table
conn.Close();
Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
user3226735
  • 11
  • 1
  • 4
  • 1
    do something with code and describe the problem, please – lenden Sep 26 '14 at 12:46
  • What is the _error_? On which line? We need more details.. – Soner Gönül Sep 26 '14 at 12:49
  • 1
    Clearly there is "already an object in the database". You can't create an object with the same name as one which already exists. So either get rid of the one that already exists or don't try to create a new one. – David Sep 26 '14 at 12:51
  • Take your inline SQL and execute it with values inside SQL management studio- my guess is there is something wrong with your merge transaction. I would also move that to a stored proc. – Nyra Sep 26 '14 at 12:53
  • 1
    There's only one line that tries to create anything, and that's the third. `Tmptablenew` already exists. – Ed Gibbs Sep 26 '14 at 12:55
  • I didn't even see that- yeah you can't create another table. Also calling something temptable is not the same as #tempTable or ##tempTable or table variable @tempTable – Nyra Sep 26 '14 at 13:02

1 Answers1

1

You are creating table Tmptablenew, it looks like this table alredy exists in database.

I think, you are trying to create a temporary table, in this case create it as CREATE TEMPORARY TABLE for MySQL or use table name #Tmptablenew for MS SQL. Also in this case it will be dropped automatically when session is closed.

EDIT

So, you have to change string temptable = "CREATE TEMPORARY TABLE [dbo].[Tmptablenew]([TicketID]... and so on if you are using MySQL (as per tag of your question). Or if it is MS SQL (as I suspect) then string temptable = "CREATE TABLE [dbo].[#Tmptablenew]([TicketID]... and so on (and in this case you should name it #Tmptablenew everywhere in your code).

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
  • I'm not fluent in My SQL, but google will help you loads here for learning how to create this. http://dev.mysql.com/doc/refman/5.1/en/create-table.html – Nyra Sep 26 '14 at 13:11
  • I tried with ur code now i got the Following Exception, The Error is System.InvalidOperationException: Cannot access destination table 'Tmptablenew'. ---> System.Data.SqlClient.SqlException: Invalid object name 'Tmptablenew'. – user3226735 Sep 26 '14 at 13:20
  • @user3226735 Let's clarify. Are you using mysql as it said on your question tag, or mssql? Then, if it's MS Sql - have you changed all occurences of `Tmptablenew` to `#Tmptablenew` in your code? And `DestinationTableName` of SqlBulkCopy too. – Andrey Korneyev Sep 26 '14 at 13:25
  • Am using MSSql, I tried with #tmptablenew i got that exception which i mentioned in previous comment. – user3226735 Sep 26 '14 at 13:28
  • @user3226735 Have you changed `bulk.DestinationTableName` to "#Tmptablenew"? It looks like exception you've provided has it source in `SqlBulkCopy` – Andrey Korneyev Sep 26 '14 at 13:31
  • 1
    @user3226735 Revised your code and found one more issue. You should not close connection after `bulk.WriteToServer(result);` and later open it again, as temporary table will only be visible while connection in which it was created, is open. – Andrey Korneyev Sep 26 '14 at 13:35
  • I tried in bulk.destinationTable but still same error – user3226735 Sep 26 '14 at 13:38
  • I think you need to check up on some SQL tutorials. I'm not saying that to be mean; this is basic sql transaction material. Use SQL Management studio and test queries. Practice. You need to check out temp tables and table variables and transactions. This seems to be a knowledgeable article https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/ – Nyra Sep 26 '14 at 13:48