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)

  Print Answers RSS
0 OriginalGriff 431
1 Afzaal Ahmad Zeeshan 184
2 /\jmot 176
3 Sergey Alexandrovich Kryukov 175
4 Marcin Kozub 175
0 OriginalGriff 8,344
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,634
3 Maciej Los 5,024
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 25 Feb 2013
Copyright © CodeProject, 1999-2014
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