Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL WinForm
I have two tables 1.custTransaction and 2.custDetails
I want to display the customer whose limit(from custDetails) are getter than Due Amount ( from custTransaction) tables.I provide the customer ID from a textBox.
custTransaction table structure is-:
 
fields Name -> Data type
=========================
custID -> text
custTranDate -> datetime
custTranType -> text
custPaidAmt -> number
custDueAmt -> number
 
custDetails table structure is -:
fields Name -> Data type
=========================
custID -> text
custName -> text
custAdd -> text
custLimit -> number
 

My sql Query is -:
SELECT custName, custAdd, custPho
FROM custDetails, custTransaction
WHERE custTransaction.custID='"+textBox1.text+"' and custTransaction.custDueAmt < custDetails.custLimit;
I used => MSAccess.
how to write the query in C# Application???
Posted 25-Feb-13 4:06am
Edited 25-Feb-13 4:08am
v2
Comments
Shubh Agrahari at 25-Feb-13 9:09am
   
whats happening with your SELECT custName, custAdd, custPho
FROM custDetails, custTransaction
WHERE custTransaction.custID='"+textBox1.text+"' and custTransaction.custDueAmt < custDetails.custLimit; this code what error occurring...?
ThePhantomUpvoter at 25-Feb-13 9:12am
   
What is it the you are trying to do? Queries are written in SQL just like you have, not in C#.
Jayanta Chatterjee at 25-Feb-13 9:18am
   
It doesn't show the specific records???
CHill60 at 25-Feb-13 9:22am
   
If you pick a sample custID and run the query directly in Access what results do you get
ThePhantomUpvoter at 25-Feb-13 9:25am
   
Is custID an int or text?
Jayanta Chatterjee at 25-Feb-13 9:27am
   
custID text..
onurag19 at 25-Feb-13 9:28am
   
Chatterjee Da what have you tried?
Jayanta Chatterjee at 25-Feb-13 9:32am
   
I posted my query(which i tried)... @onurag19 :-)
Jayanta Chatterjee at 25-Feb-13 9:29am
   
when i run this query in access it shows wrong records, which does not belongs to that custID... :-(
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hope This Helps.Just copy and paste.
SELECT     dbo.custDetails.custID AS Expr1, dbo.custDetails.custName, dbo.custDetails.custLimit, dbo.custTransaction.custDueAmt
FROM         dbo.custTransaction INNER JOIN
                      dbo.custDetails ON dbo.custTransaction.CustId = dbo.custDetails.custID
WHERE     (custDetails.custLimit > dbo.custTransaction.custDueAmt)
  Permalink  
Comments
Jayanta Chatterjee at 25-Feb-13 10:11am
   
Sir I want to give the custID through textBox.
where I put the textBox value in this query???
Maksud Saifullah Pulak at 25-Feb-13 10:20am
   
SELECT dbo.custDetails.custID AS Expr1, dbo.custDetails.custName, dbo.custDetails.custLimit, dbo.custTransaction.custDueAmt
FROM dbo.custTransaction INNER JOIN
dbo.custDetails ON dbo.custTransaction.CustId = dbo.custDetails.custID AND dbo.custTransaction.custDueAmt < dbo.custDetails.custLimit
WHERE (dbo.custDetails.custID ='"+textBox1.text+"')
Maksud Saifullah Pulak at 25-Feb-13 10:22am
   
Just exactly paste this code and check the syntex.
Jayanta Chatterjee at 25-Feb-13 10:30am
   
its works, but it return the multiple row, which depend on custTransaction tables records....
I want Only one row..
Jayanta Chatterjee at 25-Feb-13 10:36am
   
My final query is -:
SELECT top 1 custDetails.custID AS Expr1, custDetails.custName,custDetails.custPho,custDetails.custAdd FROM custTransaction INNER JOIN custDetails ON custTransaction.CustId = custDetails.custID AND custTransaction.custDueAmt < custDetails.custLimit WHERE (custDetails.custID ='"+textBox1.text+"')
Thanks a lottttttttttttttt Sir for Helping meee.... :-)
Maksud Saifullah Pulak at 25-Feb-13 12:51pm
   
You are most welcome.
ridoy at 25-Feb-13 13:42pm
   
+5 for good solution and also OP does a good job.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

SqlConnection sqlConnection = new SqlConnection( "Data Source=localhost;Initial Catalog=databasename;Integrated Security=true;" );
					SqlCommand command = sqlConnection.CreateCommand();
					sqlConnection.Open();
					command.CommandTimeout = 0;
					command.CommandText = "SELECT custName, custAdd, custPho FROM custDetails, custTransaction WHERE custTransaction.custID='" + textBox1.text + "' and custTransaction.custDueAmt < custDetails.custLimit;";
					SqlDataReader reader =  command.ExecuteReader();
					while( reader.Read() )
					{
						reader [ 0 ].ToString() + "," + reader [ 1 ].ToString() + "," + reader [ 2 ].ToString() + Environment.NewLine;
					}
					reader.Close();
  Permalink  
v2
Comments
Jayanta Chatterjee at 25-Feb-13 10:13am
   
You write the same query,which I written. This is not the solution,It doesn't show the correct records...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150301.1 | Last Updated 25 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100