how to write this sql query in C#?

Question

1.00/5 (1 vote)

See more:

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 -:

SQL

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 3:06am

JayantaChatterjee

Updated 25-Feb-13 3:08am

v2

Add a Solution

Comments

Shubh Agrahari 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...?

[no name] 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#.

JayantaChatterjee 25-Feb-13 9:18am

It doesn't show the specific records???

CHill60 25-Feb-13 9:22am

If you pick a sample custID and run the query directly in Access what results do you get

[no name] 25-Feb-13 9:25am

Is custID an int or text?

JayantaChatterjee 25-Feb-13 9:27am

custID text..

[no name] 25-Feb-13 9:28am

Chatterjee Da what have you tried?

JayantaChatterjee 25-Feb-13 9:32am

I posted my query(which i tried)... @onurag19 :-)

JayantaChatterjee 25-Feb-13 9:29am

when i run this query in access it shows wrong records, which does not belongs to that custID... :-(

2 solutions

Solution 2

C#

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();

Posted 25-Feb-13 4:02am

bopannavb

Updated 25-Feb-13 7:37am

v2

Comments

JayantaChatterjee 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...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maksud Saifullah Pulak · Accepted Answer · 2013-02-25T04:00:00

Solution 1

Hope This Helps.Just copy and paste.

SQL

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)

Posted 25-Feb-13 4:00am

Maksud Saifullah Pulak

Comments

JayantaChatterjee 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 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 25-Feb-13 10:22am

Just exactly paste this code and check the syntex.

JayantaChatterjee 25-Feb-13 10:30am

its works, but it return the multiple row, which depend on custTransaction tables records....
I want Only one row..

JayantaChatterjee 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 25-Feb-13 12:51pm

You are most welcome.

ridoy 25-Feb-13 13:42pm

+5 for good solution and also OP does a good job.