none
i have a problem while update query in c# access data , please help me it says data type mismatch RRS feed

  • Question

  •  "public static int updatestrecord(String St_name, String St_fname, String Address, int Contact_no, int Student_id)
            {
                String query= "update St_record set St_name='" + St_name+ "',St_fname='"+St_fname+"',Address='"+Address+"',Contact_no='"+Contact_no+"' where Student_id='"+Student_id+"'";
                OleDbCommand com= new OleDbCommand(query,conn);
                int rows = com.ExecuteNonQuery();
                return rows;
            }"this is a database class code for update button ".

    and thats a button code of update "

    private void update_Click(object sender, EventArgs e)
            {
                int Student_id = Int32.Parse(textBox1.Text);
                String St_name = textBox2.Text;
                String St_fname = textBox3.Text;
                String Address = textBox4.Text;
                int Contact_no = Int32.Parse(textBox5.Text);
                
                int row = Databaseconn.updatestrecord(St_name,St_fname,Address,Contact_no,Student_id);
                if (row > 0)
                {
                    MessageBox.Show("record updated");
                }
                else MessageBox.Show("error");

            }

    Thursday, February 21, 2019 9:12 AM

All replies

  • Hello,

    I would recommend using parameters rather than string concatenation for parameters. The ? mark signifies a parameter and note when adding parameters they are ordinal position for Access.

    public static int updatestrecord(string St_name, string St_fname, string Address, int Contact_no, int Student_id)
    {
        var query = "UPDATE St_record " +
                    "SET St_name=?,St_fname=?,Address=?,Contact_no=? " +
                    "WHERE Student_id=?";
    
        var com = new OleDbCommand(query, conn);
    
        com.Parameters.AddWithValue("?", St_name);
        com.Parameters.AddWithValue("?", St_fname);
        com.Parameters.AddWithValue("?", Address);
        com.Parameters.AddWithValue("?", Contact_no);
        com.Parameters.AddWithValue("?", Student_id);
    
    
        return com.ExecuteNonQuery();
    }
    private void update_Click(object sender, EventArgs e)
    {
        int Student_id = int.Parse(textBox1.Text);
        string St_name = textBox2.Text;
        string St_fname = textBox3.Text;
        string Address = textBox4.Text;
        int Contact_no = int.Parse(textBox5.Text);
    
        int row = Databaseconn.updatestrecord(St_name, St_fname, Address, Contact_no, Student_id);
        MessageBox.Show(row > 0 ? "record updated" : "error");
    }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, February 21, 2019 10:19 AM
    Moderator
  • Hi

    //Method to  insert,update,delete

    public void ExecuteCommand(String stored_Proc,SqlParameter[] param)

    {

       SqlCommand sqlcmd=new SqlCommand(stored_Proc,sqlconnection);

      sqlcmd.commandType=CommandType.StoredProcedure;

      sqlcmd.CommandText=store_procedure;

      sqlcmd.Connection=sqlconnection;

     if (param!=null)

      {

         sqlcmd.Parameters.AddRange(param);

      }

      sqlcmd.ExecuteNonQueery();

    }

    Best Regards.

    Thursday, February 21, 2019 11:45 AM
  • Hi

    //Method to  insert,update,delete

    public void ExecuteCommand(String stored_Proc,SqlParameter[] param)

    {

       SqlCommand sqlcmd=new SqlCommand(stored_Proc,sqlconnection);

      sqlcmd.commandType=CommandType.StoredProcedure;

      sqlcmd.CommandText=store_procedure;

      sqlcmd.Connection=sqlconnection;

     if (param!=null)

      {

         sqlcmd.Parameters.AddRange(param);

      }

      sqlcmd.ExecuteNonQueery();

    }

    Best Regards.

    Wrong database type, they are asking for MS-Access which a) does not support Stored procedures b) there is at least one typo in your reply this 

    sqlcmd.ExecuteNonQueery();

    should had been

    sqlcmd.ExecuteNonQuery();

    and lastly, since for Access parameters are positional, how you have setup parameters could lead to issues if out of order but again, wrong database, not SQL-Server but MS-Access.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 21, 2019 11:50 AM
    Moderator
  • Hi Jubilee,

    Thank you for posting here.

    Based on your description, I have reproduced your problem.

    You could try the following change.

    Change

    String query= "update St_record set St_name='" + St_name+ "',St_fname='"+St_fname+"',Address='"+Address+"',Contact_no='"+Contact_no+"' where Student_id='"+Student_id+"'";

    Into

    string s1 = "'" + St_name + "'";string s2 = "'" + St_fname + "'";string s3 = "'" + Address + "'";string s4= "'" + Contact_no + "'"; string s5 = "'" + Student_id + "'";
    String query = "update test2 set St_name="+s1+"," + "St_fname=" + s2+","+ "Address=" + s3+","+ "Contact_no=" + s4+ "WHERE Student_id = "+s5;
    

    Test Result:

    Hope my change could be helpful.

    Best regards,

    Jack



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 22, 2019 7:02 AM
    Moderator
  • Hi 

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find a solution quickly if they face a similar issue.

    Best Regards,

    Jack.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, February 25, 2019 8:40 AM
    Moderator
  • Hi Karen

    My result is good working  in my larg  project  in SQL SERVER.

    if you like work in access can change CommandType.

    Thanks. 

    Monday, February 25, 2019 9:02 AM