question on SQL INSERT string

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • orked
    New Member
    • Jan 2009
    • 49

    question on SQL INSERT string

    hi
    i make web application by c#,asp.net
    i want to make page that regist in DB
    but when i run the project tell me
    Incorrect syntax near 'value' i want to now what is the problem
    thanks
  • amirghaffarie1362
    New Member
    • Jan 2009
    • 19

    #2
    could u plaese send the code u r using ?

    Comment

    • orked
      New Member
      • Jan 2009
      • 49

      #3
      [code=c#]

      protected void Button1_Click(o bject sender, EventArgs e)
      {
      SqlConnection dbCon = new SqlConnection() ;
      dbCon.Connectio nString = @"Data Source=.\SQLEXP RESS;AttachDbFi lename=|DataDir ectory|\db.mdf; Integrated Security=True;U ser Instance=True";
      dbCon.Open();
      SqlCommand sqlcom = new SqlCommand();
      sqlcom.Connecti on = dbCon;
      sqlcom.CommandT ype = CommandType.Tex t;
      sqlcom.CommandT ext = sqlcom.CommandT ext = "insert into client (Name,SSN,Salar y)" + "value(@NameTex tBox.Text ,@SSNTextBox.Te xt ,@SalaryTextBox .Text )";
      //sqlcom.CommandT ext = "insert into client (Name,SSN,Salar y) value ('" + NameTextBox.Tex t + "','" + SSNTextBox.Text + "','" + SalaryTextBox.T ext + "')";
      SqlDataReader rdrData;
      rdrData = sqlcom.ExecuteR eader();
      GridView1.DataS ource = rdrData;
      GridView1.DataB ind();
      }
      [/code]

      Comment

      • amirghaffarie1362
        New Member
        • Jan 2009
        • 19

        #4
        its not correct
        sqlcom.CommandT ext = sqlcom.CommandT ext = "insert into client (Name,SSN,Salar y)" + "value(@NameTex tBox.Text ,@SSNTextBox.Te xt ,@SalaryTextBox .Text )";

        it should be
        sqlcom.CommandT ext = "insert into client (Name,SSN,Salar y) value ('" + NameTextBox.Tex t + "','" + SSNTextBox.Text + "','" + SalaryTextBox.T ext + "')";

        Comment

        • orked
          New Member
          • Jan 2009
          • 49

          #5
          the same error appear,no exchange

          Comment

          • Bassem
            Contributor
            • Dec 2008
            • 344

            #6
            Hi,
            don't forget @ to the frist of your string, or could use \ as a scape char.

            Comment

            • Bassem
              Contributor
              • Dec 2008
              • 344

              #7
              Oh i forgot, how did you declare your flieds SSN and Salary in your DB, it may not be suitable with your users inputs.
              Consider ControlParamete r, is the most suitable to collect data form users without having to worry about many.... .
              Have attention to this !

              Kind Regards,
              Bassem

              Comment

              • Plater
                Recognized Expert Expert
                • Apr 2007
                • 7872

                #8
                I think the words is VALUES not VALUE

                Comment

                • orked
                  New Member
                  • Jan 2009
                  • 49

                  #9
                  when i write values make another error
                  and when i remove it the same error appear again

                  Comment

                  • Bassem
                    Contributor
                    • Dec 2008
                    • 344

                    #10
                    Yes, it is VALUES and make your correction on that.

                    Comment

                    • Plater
                      Recognized Expert Expert
                      • Apr 2007
                      • 7872

                      #11
                      The string:
                      [code=c#]
                      "insert into client (Name,SSN,Salar y) values ('" + NameTextBox.Tex t + "', '" + SSNTextBox.Text + "', '" + SalaryTextBox.T ext + "' )";
                      [/code]
                      Should be of correct format.
                      Assuming you have a table called "client", that table contains the columns Name - SSN - Salary, they all expect a string type of some sort as a value and that there are no other required columns in the table you are leaving out

                      Comment

                      • Frinavale
                        Recognized Expert Expert
                        • Oct 2006
                        • 9749

                        #12
                        Originally posted by amirghaffarie13 62
                        its not correct
                        sqlcom.CommandT ext = sqlcom.CommandT ext = "insert into client (Name,SSN,Salar y)" + "value(@NameTex tBox.Text ,@SSNTextBox.Te xt ,@SalaryTextBox .Text )";

                        it should be
                        sqlcom.CommandT ext = "insert into client (Name,SSN,Salar y) value ('" + NameTextBox.Tex t + "','" + SSNTextBox.Text + "','" + SalaryTextBox.T ext + "')";
                        You should not be dynamically creating your SQL command as suggested by amirghaffarie.

                        You should use parameters instead:

                        Code:
                        sqlcom.CommandText = "INSERT INTO CLIENT(Name, SSN, Salary)" +
                                             "VALUES(@clientName, @clientSSN, @clientSalary)"; 
                        
                        sqlCom.Parameters.Add("@clientName", SqlDbType.VarChar).Value = NameTextBox.Text ;
                        sqlCom.Parmaeters.Add("@clientSSN", SqlDbType.VarChar).Value = SSNTextBox.Text;
                        sqlCom.Parameters.Add("@clientSalary",SqlDbType.VarChar).Value = SalaryTextBox.Text;
                        The reason for using parameters is because the information entered by the user will not be compiled with your SQL command.

                        If you create your SQL statement without parameters, anything entered by the user will be compiled with your sql command. This could leave you vulnerable to an SQL insertion attack.

                        When you encounter a new error, please post what the error message says so that we can continue to help you :)

                        -Frinny

                        Comment

                        • orked
                          New Member
                          • Jan 2009
                          • 49

                          #13
                          Hi,
                          thanks for all one ,the page run without error,if i face any error ,i will send again

                          regards
                          orked

                          Comment

                          Working...