Data Adding using sqlcommand

Question

4.00/5 (1 vote)

See more:

.NET3.5

Hi All,

I Used Three Data In DataBase like product,price,discount........i give default value off discount ...0.......I miss Default

field to enter input.. When I Run The Program . It Shows Error like(Incorrect Syntax Near ')'....Please Help Me For This

sql is

SQL

create table products(product nvarchar(max),
                  price numeric(18,2),
                  discount numeric(18,2) default 0)

used command is

C#

Com=New sqlcommand("insert into pro values('" & textbox1.text &"'," & textbox2.text &"," & textbox3.text &")",con)

Thanks
S.Priyan

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

Posted 8-Apr-11 23:28pm

Member 7732172

Updated 8-Apr-11 23:39pm

OriginalGriff

v2

Add a Solution

4 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)

OriginalGriff · Answer 1 · 2011-04-08T23:38:00

First off, don't do it like that. Your code leave you open to a deliberate or accidental SQL Injection attack, which could easily destroy your whole database. Use Parametrized queries instead.
Secondly, please use sensible names for controls: You may have a good idea what data is in "textbox2" now, but I don't - and neither will you in a months time! Call it "tbAddress" or "tbProductDescription" and you code becomes much more readable.
Thirdly, your syntax for the SQL INSERT is wrong. You need to specify the columns in your table.

Putting these together, try this:

Com=New sqlcommand("INSERT INTO pro (Column1, Column2, Column3) VALUES(@C1, @C2, @C3)", con);
Com.Parameters.AddWithValue("@C1", textbox1.text);
Com.Parameters.AddWithValue("@C2", textbox2.text);
Com.Parameters.AddWithValue("@C3", textbox3.text);

Where "Column1", "Column2" etc. are the field names in your "pro" table. You may also with to change "@C1", "@C2" and "@C3" to use sensible names as well - I can't because I don't know what you fields contain...

Abhinav S · Answer 2 · 2011-04-08T23:41:00

Debug through your source code.
Put a debug point on the command line and try to have a look at the string.

Also look at all the text box values and make sure none of them are null.

Try running the query in the back end to see if the table has been created first (before running the insert).

Wendelius · Answer 3 · 2011-04-08T23:44:00

First of all, never ever concatenate values to the SQL statement. Use SqlParameter[^].

After that your code could look something like:

C#

SqlCommand command = new SqlCommand();
command.CommandText = "INSERT INTO Products (Product, Price, Discount) VALUES (@p1, @p2, @p3)";
command.Connection = con;

SqlParameter parameter;
parameter = new SqlParameter("p1", System.Data.SqlDbType.VarChar, 2000);
parameter.Value = textbox1.Text;
command.Parameters.Add(parameter);

parameter = new SqlParameter("p2", System.Data.SqlDbType.Decimal);
parameter.Value = textbox2.Text;
command.Parameters.Add(parameter);

parameter = new SqlParameter("p2", System.Data.SqlDbType.Decimal);
parameter.Value = textbox3.Text;
command.Parameters.Add(parameter);

try {
   rowsAffected = command.ExecuteNonQuery()
} catch ...

Now you won't have problems with apostrophes, no SQL injections etc.

Member 7732172 · Answer 4 · 2011-04-09T00:01:00

com = New SqlCommand("INSERT INTO products (product,price ,discount ) VALUES(@C1, @C2, @C3)", con)
com.Parameters.AddWithValue("@C1", TextBox1.Text)
com.Parameters.AddWithValue("@C2" TextBox2.Text)
com.Parameters.AddWithValue("@C3", TextBox3.Text)
com.ExecuteNonQuery()
MsgBox("added")

If i change coding like that it also shows error"Error converting data type nvarchar to numeric."

please Help Me For This....

Thanks...those who read & reply
S.Priyan

Data Adding using sqlcommand

4 solutions

Solution 1

Solution 2

Solution 4

Solution 5

Add your solution here

Preview 0