Link to home
Start Free TrialLog in
Avatar of kasperEH
kasperEHFlag for Denmark

asked on

SQL statement works, but not in C# program

I have a SQL statement that works when I enter it manually in SQL Management Studio, but fails when I run it in a C# program. The statement is:
select CaseHistory.CaseNo, CaseInvoice.InvoiceNo, CaseHistory.Code, Paid = cast(Paid as decimal(10,2)), VoucherDate, 
PaymentNo, CaseDebtCollection.RegisteredDate, Debtor.CountryCode, Client.Country from CaseHistory
inner join CaseDebtCollection on CaseDebtCollection.CaseNo = CaseHistory.CaseNo
inner join dbo.Debtor on dbo.CaseDebtCollection.DebtorNo = dbo.Debtor.DebtorNo
inner join dbo.Client on dbo.CaseDebtCollection.ClientNo = Client.ClientNo
inner join CaseInvoice on CaseInvoice.CaseNO=CaseHistory.CaseNo
    LEFT JOIN TempCHUnik ON (TempCHUnik.Unik = CaseHistory.Unik)
WHERE (CaseHistory.Code='301' or CaseHistory.Code='400') and
ClosingDate = '' and CaseDebtCollection.Workflow not in ('0012','0015','0016') and CaseInvoice.InvoiceLineNo=1 and TempCHUnik.Unik IS NULL

Open in new window

When running it in a C# program I get this error in the log:
Invalid object name 'TempCHUnik'.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
The table TempCHUnik is there both before and after, so this is very strange.
I use this code segment to get data from the SQL Server, and it works perfectly with a different sql statement:
      cmd = new SqlCommand(sql, con);

                DataTable table = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(table);
                con.Open();

                dr = cmd.ExecuteReader();
                dr.Read();

Open in new window

Avatar of Chinmay Patel
Chinmay Patel
Flag of India image

Hi kasperEH,

Are you defining login security per table? Is it possible that when you log-in via SQL Management studio you have higher access than the account via which you are executing C# Code?

Regards,
Chinmay.
Invalid object name 'TempCHUnik'.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

2 things for initial debugging:

1. as already mentioned by Chinmay, make sure the db objects you referring to are existed
2. make sure you're connecting to the correct database.
ASKER CERTIFIED SOLUTION
Avatar of kasperEH
kasperEH
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kasperEH

ASKER

Thank you both for your assistance. I just solved it myself, when you posted your comment, Ryan.