CODEWITHSUNDEEP
c#sql
Bunion
Bunion

Reputation: 461

oledbException was unhandled error appering

I'm trying to populate a text box with a forename and surname using the code below:

using (OleDbConnection connName = new OleDbConnection(strCon))
            {

                String sqlName = "SELECT forename, Surname FROM customer WHERE [customerID]=" + txtCustomerID.Text;

                // Create a command to use to call the database.
                OleDbCommand commandname = new OleDbCommand(sqlName, connName);
                 connName.Open();
                // Create a reader containing the results


                using (OleDbDataReader readerName = commandname.ExecuteReader())
                {
                    readerName.Read(); // Advance to the first row.
                    txtName.Text = readerName[0].ToString();
                }
                connName.Close();                 

             }

However I'm getting the error: OleDbException was unhandled.

"no required values for one of more required parameters"

at the ExecuteReader and I'm not sure how to go about fixing this.

EDIT: this code below is nearly the exact same bar for the information in the query but this exception is not coming up for it.

  string strCon = Properties.Settings.Default.PID2dbConnectionString;

        using (OleDbConnection conn = new OleDbConnection(strCon))
        {
            String sqlPoints = "SELECT points FROM customer WHERE [customerID]=" + txtCustomerID.Text;
            conn.Open();

            // Create a command to use to call the database.
            OleDbCommand command = new OleDbCommand(sqlPoints, conn);
            // Create a reader containing the results

            using (OleDbDataReader reader = command.ExecuteReader())
            {
                reader.Read(); // Advance to the first row.
                txtPoints.Text = reader[0].ToString(); // Read the contents of the first column
            }
            conn.Close();
        }

Upvotes: 0

Views: 216

Answers (2)

semao
semao

Reputation: 1757

You have to encode parameters used in string queries.

  String sqlName = String.Format("SELECT forname, Surname FROM customer WHERE customerID={0}",txtCustomerID.Text);

But I advice you against using SQL queries hard-coded in strings. Its easy way for SQL Injection attack. You should use parammeters instead.

Upvotes: 0

GarethD
GarethD

Reputation: 69819

The usual reason for this is a null or empty string i.e. txtCustomerID.Text has no value so the query being sent to the server is:

SELECT forename, Surname FROM customer WHERE [customerID]=

You can avoid errors like this and SQL Injection, use strongly typed parameters and avoid data truncation using parameterised queries (I have assumed customer ID is an int field)

    using (OleDbConnection connName = new OleDbConnection(strCon))
    {
        String sqlName = "SELECT forename, Surname FROM customer WHERE customerID = @CustomerID";

        // Create a command to use to call the database.
        using (OleDbCommand commandname = new OleDbCommand(sqlName, connName))
        {

            //Check the input is valid
            int customerID = 0;
            if (!int.TryParse(txtCustomerID.Text, out customerID))
            {
                txtName.Text = "Customer ID Text box is not an integer";
                return;
            }


            connName.Open();

            // Add the parameter to the command
            commandname.Parameters.Add("@CustomerID", OleDbType.Integer).Value = customerID;

            // Create a reader containing the results
            using (OleDbDataReader readerName = commandname.ExecuteReader())
            {
                readerName.Read(); // Advance to the first row.
                txtName.Text = readerName[0].ToString();
            }
            connName.Close();                 
        }
    }

Upvotes: 1

Related Questions