locked
Cant insert character like ' and - RRS feed

  • 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

    Wednesday, January 28, 2009 10:08 PM

Answers

  • User-1381397825 posted

    Hi,

    to replace singel quote with this way

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 28, 2009 10:27 PM

All replies

  • User-1381397825 posted

    Hi,

    to replace singel quote with this way

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 28, 2009 10:27 PM
  • 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
    <!---->
     
    I realize you're not using SQL - but you should be able to parameterize your access query too...
    Wednesday, January 28, 2009 10:31 PM
  • 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

     

    Wednesday, January 28, 2009 10:40 PM
  • 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
            //
        }
    }
     

    Wednesday, January 28, 2009 10:52 PM
  • 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

    Wednesday, January 28, 2009 10:53 PM
  • 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(); 
    }
     
    Wednesday, January 28, 2009 10:53 PM
  • User1052662409 posted

     Thanx, thax a lor Maamir

    Wednesday, January 28, 2009 10:59 PM
  • 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

     

    Thursday, January 29, 2009 3:37 AM
  • 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 

     

    Thursday, January 29, 2009 10:14 PM
  • 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.

     

    Friday, January 30, 2009 2:31 AM
  • 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

    Friday, January 30, 2009 2:42 AM
  • 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.

     

    Friday, January 30, 2009 3:24 AM
  • 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 [:)]

    Tuesday, April 28, 2009 11:41 PM