kasperEH
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:
Invalid object name 'TempCHUnik'. at System.Data.SqlClient.SqlC onnection. OnError(Sq lException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlI nternalCon nection.On Error(SqlE xception 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:
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
When running it in a C# program I get this error in the log:Invalid object name 'TempCHUnik'. at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlI
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();
Invalid object name 'TempCHUnik'. at System.Data.SqlClient.SqlConnection. OnError(Sq lException 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your assistance. I just solved it myself, when you posted your comment, Ryan.
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.