help me in correcting that sql query

Question

0.00/5 (No votes)

See more:

i am getting the fallowing error in that query.

i somewhere use that statement

C#

"INSERT INTO ROLES (userid,groupid) SELECT userid,groupid FROM users, groups WHERE username= '" + TextBox1.Text + "' AND name='" + a + "'";

works fine for me..

in the fallowing statement..
i want category id to be taken from another table categories id and selection is done on the bases of category name.
and the rest of the values are inserted from the textboxes.
no how to pass categoryid in the values field of the insert command.

C#

string query = "insert into ESK_Products(CateogoryID,ProductName,ProductImage,UnitCost,Description) values('" + txtproname.Text + "','" + FileUpload1.FileName + "'," + txtproprice.Text + ",'" + txtprodesc.Text + "') select CategoryID from ESK_Categories where CategoryName='" + DropDownList1.Text + "'";

code form the dropdownlist:

C#

SqlCommand cmd = new SqlCommand("SELECT * FROM ESK_Categories)", con"]));
cmd.Connection.Open();

SqlDataReader dr;
dr = cmd.ExecuteReader();

DropDownList1.DataSource = dr;
DropDownList1.DataValueField = "CateogoryName";

cmd.Connection.Close();

the error is as under:-

C#

not insertedSystem.Data.SqlClient.SqlException: There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Admin_Products.btnAdd_Click(Object sender, EventArgs e)    Label

Posted 1-Sep-11 21:21pm

codegeekalpha

Updated 2-Sep-11 1:30am

v5

Add a Solution

2 solutions

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)

Toniyo Jackson · Accepted Answer · 2011-09-01T21:25:00

Solution 1

You have mentioned 5 columns here ESK_Products(CateogoryID,ProductName,ProductImage,UnitCost,Description). But you have used only 4 columns here values('" + txtproname.Text + "','" + FileUpload1.FileName + "'," + txtproprice.Text + ",'" + txtprodesc.Text + "'). Number of columns should match here. If CateogoryID is auto increment in table then remove CateogoryID or else pass the value for CateogoryID also.

Try this.

string query = "insert into ESK_Products(ProductName,ProductImage,UnitCost,Description) values('" + txtproname.Text + "','" + FileUpload1.FileName + "'," + txtproprice.Text + ",'" + txtprodesc.Text + "') select CategoryID from ESK_Categories where CategoryName='" + DropDownList1.Text + "'";

Posted 1-Sep-11 21:25pm

Toniyo Jackson

Comments

codegeekalpha 2-Sep-11 3:40am

categoryID is not autoincrement

i want category id to be taken from another table categories id and selection is done on the bases of category name.
and the rest of the values are inserted from the textboxes.
no how to pass categoryid in the values field of the insert command.

Toniyo Jackson 2-Sep-11 3:48am

I think you are selecting the category name from the dropdownlist. If so, first bind the dropdownlist with categoryid as ValueMember and then get the categoryid by using dropdownlist.SelectedValue.

codegeekalpha 2-Sep-11 4:20am

can u pls help me how to do this.??

Toniyo Jackson 2-Sep-11 4:49am

How you are selecting Category name?

codegeekalpha 2-Sep-11 5:51am

category name and cateogryid is on the table ESK_Categories. i am just taking the categoryname form the categoriestable (ESK_Categories) in database into dropdownlist. now i have the another table having productid, categoryid , parductimage ,unitcost, description.. i am selecting the value of categoryname from the dropdownlist. and in order to insert categoryid from the form the ESK_categories table. i use select in insert query as shown above

Toniyo Jackson 2-Sep-11 5:55am

How are you binding dropdownlist. Are you using DataValueField? Please share dropdownlist binding code.

codegeekalpha 2-Sep-11 7:30am

o added the code in question see it

codegeekalpha 2-Sep-11 7:30am

i added the code for dropdownlist in quetion just check it

codegeekalpha 2-Sep-11 7:35am

i put that drowdownlist code in pageload and its working fine.

Toniyo Jackson 2-Sep-11 7:36am

Use DropDownList1.DataValueField = "CateogoryId"; and DropDownList1.DataTextField = "CateogoryName";. Then get the CateogoryId by using DropDownList1.SelectedValue.

FastEvo8 · Accepted Answer · 2011-09-02T03:30:00

Solution 2

I would try this:

string query = "insert into ESK_Products(CategoryID,ProductName,ProductImage,UnitCost,Description)
select CategoryID,' + " + txtproname.Text + "','" + FileUpload1.FileName + "'," + txtproprice.Text + ",'" + txtprodesc.Text + "' from ESK_Categories where CategoryName='" + DropDownList1.Text + "'";

Posted 2-Sep-11 3:30am

FastEvo8

Comments

codegeekalpha 2-Sep-11 13:57pm

thanku fastevo it works for me..