Please solve this problem

Question

0.00/5 (No votes)

See more:

Vendor_Gatepass  Customer_Name         Location Product         Vendor_name     PID      Count

qMDQ4bxF	Annik Technologies	okhla	RAM		Religare	12160	1c	NULL
qMDQ4bxF	Annik Technologies	okhla	RAM		Religare	12161	1c	NULL
M1Z-uUso	Religare	Noida	Motherboard	fs	NULL	12043	11	NULL
M1Z-uUso	Annik Technologies	okhla	RAM		NULL	12160	1c	NULL
h2wR4O-h	Accreative Health	hj	SMPS		Religare	12236	6	NULL
h2wR4O-h	Religare	Noida	Motherboard	fs	Religare	12043	11	NULL
h2wR4O-h	Annik Technologies	okhla	RAM		Religare	12160	1c	NULL
VrxaZiJD	Annik Technologies	okhla	RAM		NULL	12160	1c	NULL
VrxaZiJD	Annik Technologies	okhla	RAM		NULL	12161	1c	NULL
qut3f3SQ	Accreative Health	hj	SMPS		Religare	12236	6	NULL
qut3f3SQ	Annik Technologies	okhla	RAM		Religare	12160	1c	NULL
qut3f3SQ	Annik Technologies	okhla	RAM		Religare	12161	1c	NULL
NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Hi everyone,
This is my database. The number of product name with same Vendor gatepass and Serail number I want to put in count column. how will i put it please help. I have written code but that is not working.
My code is as below

C#

con.Open();
string qry = "Select COUNT( Product_Name) from VendorGatePass where Vendor_GatePass=' " + TextBox7.Text + "'and Serial_Number='" + SerialNo.Text + "'";
SqlCommand comd = new SqlCommand(qry, con);
Int32 count = Convert.ToInt32(comd.ExecuteScalar());
con.Close();
con.Open();
string que = "update VendorGatePass set Count='" + count + "' where Vendor_GatePass='" + TextBox7.Text + "' and Product_Name='" + Product_Name.Text + "' and Serial_Number='" + SerialNo.Text + "'";
SqlCommand comm = new SqlCommand(que, con);
com.ExecuteNonQuery();
con.Close();

[edit]Code block added - OriginalGriff[/edit]

Posted 9-Jul-14 21:05pm

Member 10578683

Updated 9-Jul-14 21:17pm

OriginalGriff

v2

Add a Solution

Comments

George Jonsson 10-Jul-14 3:24am

Do you really want to put a count column into your table?
This information can easily be calculated upon request on the client side, so no need to add a column. It is even a bad idea.
In that case you should create a View.

Member 10578683 10-Jul-14 3:34am

How to get this information. Please help

George Jonsson 10-Jul-14 4:02am

See my answer. There are several options you can chose from.

Thanks7872 10-Jul-14 8:56am

Stop abusing forum by posting similar questions over and over again. This may lead you to account cancellation.

2 solutions

Solution 1

Oh dear...

Let's start with the dangerous stuff, shall we? Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Why doesn't it work? Simple: you don;t have a column called "Serial_Number" - so the query has no idea what you are talking about! And I don't see anything in there that could be a serial number.
Either you need to look further afield in your database, and possibly use an SQL JOIN, or you need to look at your table and add a column, or your query and work out what you do want to look at.

But fix the SQL Injection problem first - and look at the rest of your code because otherwise, you best mate will come along and try it, just to see the look on your face...

Posted 9-Jul-14 21:23pm

OriginalGriff

Comments

Member 10578683 10-Jul-14 3:29am

I forgot to put that column name there after Product Serial Number column is there in database

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)

George Jonsson · Accepted Answer · 2014-07-09T21:53:00

This can be used to calculate the value of a column on the client side.
Then the return for your select command will not have any Count column, but your DataTable has.
Use the link below to get info for how to fill this column with values.

DataColumn.Expression Property[^]

DataTable.Compute Method[^]

You can also do this in SQL

SQL

select Vendor_Gatepass, Customer_Name, count(Product) as Count from A_Table;

Add more columns to the select statement as you wish.

For creating a view, you can have a look here.
SQL CREATE VIEW Statement[^]