I don't know what is errore in this code

Question

1.00/5 (2 votes)

See more:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace TestCon
{
    public partial class index : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            con.Open();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("insert into tbl_user values('"+TextName.Text+"','"+TextAdd.Text+"','"+TextDate.Text+"')",con);
            cmd.ExecuteNonQuery();
            con.Close();
            LabSave.Visible = true;
            LabSave.Text = "Data Save";
            TextName.Text = "";
            TextAdd.Text = "";
            TextDate.Text = "";

What I have tried:

give me error when run web application in this Sentences

cmd.ExecuteNonQuery();
           con.Close();

Posted 27-Jun-18 0:53am

Member 12267555

Updated 27-Jun-18 9:06am

Add a Solution

Comments

F-ES Sitecore 27-Jun-18 6:56am

Whenever your code throws an error always say what the error is as well as the line it is thrown on.

Regardless, your problem will almost certainly be solved by using parameterised queries (google "ado.net parameterised queries")

4 solutions

Solution 2

See Solution 1.

I came up with a similar solution :

C#

using SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString)
{
	con.Open();
	SqlCommand cmd = new SqlCommand("insert into tbl_user values(@name, @add, @date)",con);
	cmd.Parameters.AddWithValue("@name", TextName.Text);
	cmd.Parameters.AddWithValue("@add", TextAdd.Text);
	cmd.Parameters.AddWithValue("@date", TextDate.Text);
	cmd.ExecuteNonQuery();
	// rest of your code 
	// ...
	
}//This closes the using and therefore closes your connection and properly disposes of it

Note the comment at the very end ... for more information see using Statement (C# Reference) | Microsoft Docs[^]

If you still get an error after using Parameters then check the contents of your TextDate.Text - can you actually make a date of that. You might want to consider putting some validation in place that ensures it is a valid date.

Posted 27-Jun-18 1:22am

CHill60

Comments

Member 12267555 27-Jun-18 7:56am

not working sorry

CHill60 27-Jun-18 19:12pm

Do you want to give me a clue about how it is not working? Hint - The actual error message is a really good place to start, your actual code (after trying to apply the solutions offered) Is also a good idea. Help us to help you

#realJSOP 27-Jun-18 10:30am

Yeah, he's a programmer alright...

CHill60 27-Jun-18 19:08pm

Was going to make a bitter comment about where our jobs have gone, but I've let that go now :-)

#realJSOP 28-Jun-18 5:06am

BTW, you can use DateTime.TryParse to convert text to a DateTime, or just do Convert.ToDateTime and if it throws an exception, it's not a valid date.

Solution 3

Quote:
give me error when run web application in this Sentences

It is a good idea to give the text of error as it tells what is the error.

C#

SqlCommand cmd = new SqlCommand("insert into tbl_user values('"+TextName.Text+"','"+TextAdd.Text+"','"+TextDate.Text+"')",con);

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Posted 27-Jun-18 1:22am

Patrice T

Updated 27-Jun-18 1:25am

v2

Comments

Member 12267555 27-Jun-18 7:56am

error in
con.Open();

Patrice T 27-Jun-18 8:11am

This is certainly not the exact error message.

Member 12267555 27-Jun-18 8:17am

i don't know but if you have a new idea tell me
thx

Member 12267555 27-Jun-18 8:18am

All you need to do is create a data logging page that is linked to the SQL Server database

Member 12267555 27-Jun-18 8:19am

i am using sql server 2012 & vb 2012 c#

#realJSOP 27-Jun-18 9:55am

We're wasting our time. He wants us to do his work for him.

Patrice T 27-Jun-18 11:45am

I know :)

Solution 4

Check your database table fields. There is exactly the same number of fields in DB table as in your SQL command? However, without an exact error message it is just a wild guess.

And heed the other responders hints about the USING and the SQL injection problems, it maybe lead to serious problems.

EDIT:
If you define the command like this:

SQL

insert into tbl_user (field1, field2, field3) values (@param1, @param2, @param3);

then:
* code isn't crash when you add more fields to tbl_user table,
* you not forced to add all the unnecesary parameters to this command.

Posted 27-Jun-18 9:07am

Liktor Janos

Updated 27-Jun-18 9:15am

v2

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)

#realJSOP · Accepted Answer · 2018-06-27T01:17:00

0) Do NOT open your db connection when the page loads. Wait until you actually need it to be open before doing so.

1) Use parameterized queries to avoid SQL injection attacks

Better code:

C#

SqlConnection con = null;
SqlCommand cmd = null;
try
{
    using (con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"]))
    {
        con.Open();
        string query = "insert into tbl_user (column1, column2, ...) values (@col1, @col2, ...)";
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("@col1", col1Value),
            new SqlParameter("@col2", col2Value),
            ...
        };
        using (cmd = new SqlCommand(query, con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddRange(parameters);
            cmd.ExecuteNonQuery();
        }
    }
}
catch (Exception ex)
{
    // do something with exception
}