C# OleDb Insert Integer with Parameter

Question

I have a problem with insert command in C# WinForm. I need to insert special char so I need parameter or some alternative. But I can't insert the data into db. My 2nd column RecordNo allows unique and is a integer. This is where I'm facing trouble. After saving one rec when I change the RecordNo and try to save data it is showing data is duplicate. Plzz help me fix my problem.

My Code:

    private void saverec_Click(object sender, EventArgs e)
    {
        try
        {
            int recva = Convert.ToInt32(recno_tb.Text);
            myconn.ConnectionString = connestr;
            dtcmd.CommandText = "INSERT INTO FormEntry (RecordNo) values ("+ recva + ")";
            dtcmd.Connection = myconn;
            myconn.Open();
            dtcmd.ExecuteNonQuery();
            myconn.Close();
            dataconnect();
            myconn.ConnectionString = connestr;
            dtcmd.CommandText = "UPDATE FormEntry SET ImageName = @imagena,EmailId = @email WHERE [RecordNo] = " + recva + "";                dtcmd.Parameters.Add("@imagena", OleDbType.VarChar).Value = imgname_tb.Text;
            //dtcmd.Parameters.AddWithValue("@recno", recno_tb.Text);
            dtcmd.Parameters.AddWithValue("@email", OleDbType.VarChar).Value = email_tb.Text;
            dtcmd.Connection = myconn;
            myconn.Open();
            dtcmd.ExecuteNonQuery();
            myconn.Close();
            dataconnect();
            addnew_Click(sender, e);
            recno_tb.Text = (recva + 1).ToString();
            email_tb.Focus();
        }
        catch (Exception ex)
        {
            if (ex.Message.ToString() == "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.")
            {
                MessageBox.Show("Record already exists. Try entering new record.\nYou can also find and edit the record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else if (ex.Message.ToString() == "Field 'FormEntry.RecordNo' cannot be a zero-length string.")
            {
                MessageBox.Show("Record No can't be null", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        myconn.Close();

    }

I had googled it but no use. So plz help me.

To start with, I'd suggest using parameterized SQL *everywhere*, not just in a single place. Next, I would suggest creating a new connection and command everywhere you need it, with `using` statements to close everything appropriately. Next, I'd suggest reading up on .NET naming conventions, and renaming your methods and variables accordingly. After that, update the question. I suspect the problem *may* be that `OleDbCommand` only supports positional parameters, not named ones... but it's hard to say right now. — Jon Skeet, Aug 27 '16 at 08:16

score -2 · Answer 1 · answered Aug 27 '16 at 09:18

This is the answer. I was using global connect var. I just changed it and I have used Parameter AddWithValue as Add, I think this is also a reason.

The code is:

private void saverec_Click(object sender, EventArgs e)
        {
            try
            {
                int recna = Convert.ToInt32(recno_tb.Text);
                OleDbConnection myconn = new OleDbConnection();
                myconn.ConnectionString = connestr;
                var insequ = "INSERT INTO FormEntry (ImageName, RecordNo, EmailId) VALUES (?,?,?)";
                OleDbCommand dtcmd = new OleDbCommand(insequ, myconn);
                dtcmd.Parameters.AddWithValue("@imagena", OleDbType.VarChar).Value = imgname_tb.Text;
                dtcmd.Parameters.AddWithValue("@recno", OleDbType.Integer).Value = recna;
                dtcmd.Parameters.AddWithValue("@email", OleDbType.VarChar).Value = email_tb.Text;
                myconn.Open();
                dtcmd.ExecuteNonQuery();
                myconn.Close();
                dataconnect();
                addnew_Click(sender, e);
                recno_tb.Text = (recna + 1).ToString();
                email_tb.Focus();
            }
            catch (Exception ex)
            {
                if (ex.Message.ToString() == "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.")
                {
                    MessageBox.Show("Record already exists. Try entering new record.\nYou can also find and edit the record.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else if (ex.Message.ToString() == "Field 'FormEntry.RecordNo' cannot be a zero-length string.")
                {
                    MessageBox.Show("Record No can't be null", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            myconn.Close();

        }

You're misusing `AddWithValue` here - the second parameter to `AddWithValue` is the value, not the type. You're still not using `using` statements for the command or connection, either. — Jon Skeet, Aug 27 '16 at 09:27
You're misusing `AddWithValue` here - the second parameter to `AddWithValue` is the value, not the type. You're still not using `using` statements for the command or connection, either. — Jon Skeet, Aug 27 '16 at 09:31

C# OleDb Insert Integer with Parameter

1 Answers1