Cant insert character like ' and -

Question

User1052662409 posted

 Hi all,

        my inserting query is

insert into tblComment(DP,DC,OP,OC,CP,CC,FP,FC,CLRP,CLRC,IP,IC,user)values('" + txtP1.Text + "','" + txtC1.Text + "','" + txtP2.Text + "','" + txtC2.Text + "','" + txtP3.Text + "','" + txtC3.Text + "','" + txtP4.Text + "','" + txtC4.Text + "','" + txtP5.Text + "','" + txtC5.Text + "','" + txtP6.Text + "','" + txtC6.Text + "',' "+(string)Session["uname"]+" ')";

but when I insert character like ' or - it shows error. can any body tell me that When I insert these types of value, is there any method in C# to replace them or to insert them.

I m using access database as backend. and the data type of these fields is memo.

So please if you know the solution, just write here the query.

Thanx a lot

Answer 1

User-1381397825 posted

Hi,

to replace singel quote with this way

 txtP2.Text.Replace("'","''");

Answer 2

User1634106055 posted

Try using parameters with your command - they work great with SQL and it looks like they can be used with access.  They should also help protect from SQL injection.  Parameterizing will allow you to insert any valid database characters without having to worry about text qualifiers.

Retrieving Data Using the AccessDataSource Web Server Control:  http://msdn.microsoft.com/en-us/library/8e5545e1.aspx

Using Parameters with Data Source Controls:  http://msdn.microsoft.com/en-us/library/xt50s8kz.aspx

<!---->

SqlCommand:Parameters Property:  http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

 

I realize you're not using SQL - but you should be able to parameterize your access query too...

Answer 3

User1052662409 posted

 thanx a lot dear,

And what abt charachter like - , i have to replace it too. then what will b the query. is it like replace("'","''","-")  or somthing else pls write it.

Thanx

 

Answer 4

User1052662409 posted

 Hi Trdudei,

This is my class file, I dont know much abt parameters.

So please can you tell me how to add parameters to this class file

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

/// <summary>
/// Summary description for gaurav
/// </summary>
public class gaurav
{
    public OleDbConnection con;
    public OleDbCommand com;

    private void Open_Connection()
    {
        string conStr=ConfigurationManager.ConnectionStrings["contest"].ConnectionString;
        if(con==null)
        {
            con=new OleDbConnection(conStr);
            con.Open();
        }
        com=new OleDbCommand();
        com.Connection=con;
    }

    private void Close_Connection()
    {
        con.Close();
    }

    private void Dispose_Connection()
    {
        if(con!=null)
        {
            con.Dispose();
            con=null;
        }
    }
    public int Execute_Sql(string strSql)
    {
        this.Open_Connection();
        com.CommandText=strSql;
        com.CommandType=CommandType.Text;
        int row=com.ExecuteNonQuery();
        this.Close_Connection();
        this.Dispose_Connection();
        return row;
    }

    public DataSet My_Dataset(string strSql)
    {
        this.Open_Connection();
        OleDbDataAdapter da=new OleDbDataAdapter(strSql,con);
        DataSet ds=new DataSet();
        da.Fill(ds);
        this.Close_Connection();
        this.Dispose_Connection();
        return ds;
    }
   
    public DataTable My_Datatable(string strSql)
    {
        this.Open_Connection();
        OleDbDataAdapter da=new OleDbDataAdapter(strSql,con);
        DataTable dt=new DataTable();
        da.Fill(dt);
        this.Close_Connection();
        this.Dispose_Connection();
        return dt;
    }

    public bool Record_Is_Exit(string strSql)
    {
        this.Open_Connection();
        com.CommandText=strSql;
        com.CommandType=CommandType.Text;
        int val=Convert.ToInt32(com.ExecuteScalar());
        this.Close_Connection();
        this.Dispose_Connection();
        if(val==1)
            return
                true;
        else
            return
                false;
    }

  




    public gaurav()
    {
        //
        // TODO: Add constructor logic here
        //
    }
}
 

Answer 5

User-1381397825 posted

Hey

- this is not a problem in sql statement only single quote is a problem

 

replace("'","''","-")   this wrong. you have only parameters for replace function

Answer 6

User2115139740 posted
try using sql parameter  

SqlConnection sc = null; 
SqlCommand command = null;

 try 
{ 
sc = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\foo\App_Data\Database.mdf;Integrated Security=True;User Instance=True"); 
string InsertQuery="insert into tblComment values (@p1)";
 sc.Open(); 
command = new SqlCommand(InsertQuery, sc);
 command.CommandType = CommandType.Text; 
command.Parameters.AddWithValue("@p1", txtP1.Text);
 //add all your parameter here 
command.ExecuteNonQuery();
 } 
finally 
// clean up 
{ 
if(command != null)
 command.Dispose(); 
if(sc != null)
 sc.Close(); 
}

 

Answer 7

User1052662409 posted

 Thanx, thax a lor Maamir

Answer 8

User-821857111 posted

Escaping apostrophes by doubling them like that is not the answer. You should not create your SQL dynamically by concatenating strings like that.  It is poor practice, and would not be acceptable in any professional development company. As someone else has mentioned, you should use parameters: http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

 

Answer 9

User1052662409 posted

Hi Dear,

I agreed with you. But I dont know hot to use parameters in my class file.

can you tell me ? 

This is my class file

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

/// <summary>
/// Summary description for gaurav
/// </summary>
public class gaurav
{
    public OleDbConnection con;
    public OleDbCommand com;

    private void Open_Connection()
    {
        string conStr=ConfigurationManager.ConnectionStrings["contest"].ConnectionString;
        if(con==null)
        {
            con=new OleDbConnection(conStr);
            con.Open();
        }
        com=new OleDbCommand();
        com.Connection=con;
    }

    private void Close_Connection()
    {
        con.Close();
    }

    private void Dispose_Connection()
    {
        if(con!=null)
        {
            con.Dispose();
            con=null;
        }
    }
    public int Execute_Sql(string strSql)
    {
        this.Open_Connection();
        com.CommandText=strSql;
        com.CommandType=CommandType.Text;
        int row=com.ExecuteNonQuery();
        this.Close_Connection();
        this.Dispose_Connection();
        return row;
    }

    public DataSet My_Dataset(string strSql)
    {
        this.Open_Connection();
        OleDbDataAdapter da=new OleDbDataAdapter(strSql,con);
        DataSet ds=new DataSet();
        da.Fill(ds);
        this.Close_Connection();
        this.Dispose_Connection();
        return ds;
    }
   
    public DataTable My_Datatable(string strSql)
    {
        this.Open_Connection();
        OleDbDataAdapter da=new OleDbDataAdapter(strSql,con);
        DataTable dt=new DataTable();
        da.Fill(dt);
        this.Close_Connection();
        this.Dispose_Connection();
        return dt;
    }

    public bool Record_Is_Exit(string strSql)
    {
        this.Open_Connection();
        com.CommandText=strSql;
        com.CommandType=CommandType.Text;
        int val=Convert.ToInt32(com.ExecuteScalar());
        this.Close_Connection();
        this.Dispose_Connection();
        if(val==1)
            return
                true;
        else
            return
                false;
    }

  




    public gaurav()
    {
        //
        // TODO: Add constructor logic here
        //
    }
}

Thanx 

 

Answer 10

User-821857111 posted

I agreed with you. But I dont know hot to use parameters in my class file.

can you tell me ? 

 

    public int Execute_Sql(string strSql, string[] params, object[] values)
    {
        this.Open_Connection();
        com.CommandText=strSql;
        com.CommandType=CommandType.Text;
        for (int i = 0; i < params.Length; i++)
        {
            com.Parameters.AddWithValue(params[i], values[i]);
        }
        int row=com.ExecuteNonQuery();
        this.Close_Connection();
        this.Dispose_Connection();
        return row;
    }

The bits that you need to add are done in bold.  When you call the method, you just need to construct two arrays - one for the parameter names and one for the values and pass those in to the function.

 

Answer 11

User1052662409 posted

 Thanx a lot dear,

 Is there anything else I have to do with my class regarding parameters.

Or Can I use it, as you have edited in my class.

pls reply

Thanx

Answer 12

User-821857111 posted

You can use it like that.  You should also keep the method you have that doesn't have arrays of parameters and values as arguments.  That means you will have an overloaded version of the function, so you can choose which one to call depending on whether parameters are required or not.

 

Answer 13

User1052662409 posted

 Thanx a lot, but can you please make me understand by giving example for what you have told me in these lines

"The bits that you need to add are done in bold.  When you call the method, you just need to construct two arrays - one for the parameter names and one for the values and pass those in to the function."

i didnt gey it yet.

wen i added the code you have written in bold i get many errors like

public int Execute_Sql(string strSql, string[] params, object[] values)
    {
        this.Open_Connection();
        com.CommandText=strSql;
        com.CommandType=CommandType.Text;
        for (int i = 0; i < params.Length; i++)
        {
            com.Parameters.AddWithValue(params[i], values[i]);
        }
        int row=com.ExecuteNonQuery();
        this.Close_Connection();
        this.Dispose_Connection();
        return row;
    } 

Errors are:---

 Identifier expected, 'params' is a keyword  

  Class, struct, or interface method must have a return type 

  Invalid token '=' in class, struct, or interface member declaration

   Invalid token '++' in class, struct, or interface member declaration  

 Array size cannot be specified in a variable declaration (try initializing with a 'new' expression)   
 

and amny more

let me know pls

thanx [:)]