help me in correcting sql query

Question

5.00/5 (1 vote)

See more:

i improved the query as said by the two programmers here.. the new query is as

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 + "'";

but now i am getting another error. i think its becuase of categoryid column.. but how can i solve it...some thing we have to specify in for category in sql values..

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 12:42pm

codegeekalpha

Updated 1-Sep-11 21:37pm

Herman<T>.Instance

v5

Add a Solution

Comments

Herman<T>.Instance 1-Sep-11 19:05pm

what happens if you try this?
string query = string.Format("declare @catName varchar(50); select @catName = CategoryID from ESK_Categories where CategoryName = '{0}';", DropDownList1.Text);
string query += string.Format("insert into ESK_Products(CategoryID,ProductName,ProductImage,UnitCost,Description) values( @catName, '{0}', '{1}', '{2}', '{3}' ", txtproname, FileUpload1.FileName, txtproprice, txtprodesc);

codegeekalpha 1-Sep-11 19:09pm

i get the error

not insertedSystem.Data.SqlClient.SqlException: The name "System.Web.UI.WebControls.TextBox" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. 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)

codegeekalpha 1-Sep-11 19:11pm

i am not getting u. digimanus

justinonday 2-Sep-11 1:10am

split query as two query and try?

Herman<T>.Instance 2-Sep-11 3:33am

no it is 2 commands but with += you create 1 string. the ';' separates the queries for sql but is in 1 call

codegeekalpha 2-Sep-11 6:01am

i tried to split it in two quries but still having problems..

codegeekalpha 2-Sep-11 6:11am

i split this query as

query1="insert into ESKProducts (categoryid) select categoryid from ESK_Categories where categoryname=dropdownlist1.Text;

query="insert into ESK_Products(CateogoryID,ProductName,ProductImage,UnitCost,Description) values('" + txtproname.Text + "','" + FileUpload1.FileName + "'," + txtproprice.Text + ",'" + txtprodesc.Text + "')

5 solutions

Solution 1

What happens if you remove the select part - "select CategoryID from ESK_Categories where CategoryName='" + DropDownList1.Text + "'"

Does it insert data then?

I see you have used txtproprice instead of txtproprice.Text.
txtproprice will return "System.Web.UI.WebControls.TextBox" which I can see in your error.
Can you try using txtproprice.Text? Do the same for other text boxes (txtproprice and txtprodesc)

Posted 1-Sep-11 15:57pm

pkkalra1978

Solution 2

Dear Friend,

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

Replace with txtproprice to txtproprice.text then try the Query

Regards,

Anilkumar.

Posted 1-Sep-11 17:43pm

Anil Honey 206

Solution 5

I see two errors:
1. probably typo -

SQL

INSERT INTO ESK_Products(CateogoryID...

2. You don't supply value for CategoryID in VALUES part

After new information supplied by OP I've changed query.

C#

string query = 
"DECLARE @categoryID INT; " +
"SET @categoryID = (SELECT CategoryID FROM ESK_Categories where CategoryName='" + DropDownList1.Text + "'); " +
"INSERT INTO ESK_Products " +
"(CategoryID, ProductName, ProductImage, UnitCost, Description) " +
"VALUES " +
"(@categoryID, '" + txtproname.Text + "', '" + FileUpload1.FileName + "', " + txtproprice.Text + ", '" + txtprodesc.Text + "')";

Posted 2-Sep-11 0:39am

Oshtri Deka

Updated 2-Sep-11 12:49pm

v2

Comments

codegeekalpha 2-Sep-11 6:50am

categoryid is the foreign key.. its not autoincrement..

Solution 3

hi,

you miss values

string query = "insert into ESK_Products values 

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

Posted 1-Sep-11 21:42pm

adadsadsasd

Comments

codegeekalpha 2-Sep-11 5:57am

???

Prerak Patel 2-Sep-11 6:46am

I guess, you missed values in question.

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)

DanHodgson88 · Accepted Answer · 2011-09-01T21:48:00

Solution 4

My problem with this and it will also help you is that you should use parameters mate. It helps with security SQL injection etc and in my personal opinion keeps the code a bit neater along the way!

Posted 1-Sep-11 21:48pm

DanHodgson88

Comments

Herman<T>.Instance 2-Sep-11 4:59am

in another post he already stated he would to this in a proper way. He only shows his syntax problem with the query not with the method he uses in real

DanHodgson88 2-Sep-11 7:20am

cheers for that mate didn't realise that!