Null value getting stored in mySQL table when insert query fired from ASP.NET using mySQL odbc connector

Question

After spending several hours i am unable to figure out that why null values are being inserted into mySQL table using ASP.NET web page. I am using odbc connector for this.Below is the code for the same.

 public int Insert(string FirstName, string LastName, int age)
{
    OdbcConnection conn = new OdbcConnection(connStr);
    conn.Open();
    OdbcCommand odcmd_Insert = new OdbcCommand("INSERT INTO webuse(firstName,lastName,age) VALUES(@param1,@param2,@param3)",conn);

    odcmd_Insert.Connection = conn;
    odcmd_Insert.CommandType = System.Data.CommandType.Text;

    try
    {

        odcmd_Insert.Parameters.Add(new OdbcParameter( "@param1", FirstName));
        odcmd_Insert.Parameters.Add(new OdbcParameter( "@param2", LastName));
        odcmd_Insert.Parameters.Add( new OdbcParameter("@param3", age));

        return odcmd_Insert.ExecuteNonQuery();


    }
    catch (OdbcException e)
    {
        throw;
    }
    finally {

        odcmd_Insert.Dispose();
        conn.Close();
        conn.Dispose();
    }

}

I have debugged the code and all things seems well but all columns are updated with null values. Please help i am a noob to ASP.NET.

set the debug point and check either are you getting those values or not. — Amit Kumar
– Amit Kumar, Commented Jul 28, 2014 at 11:41
i have already checked all values are correctly recieved in the respective parameters. — Neha
– Neha, Commented Jul 28, 2014 at 11:45

Banana · Accepted Answer · 2018-02-22 13:22:54Z

1

Please try your argument like as below. I have used the MySqlConnection. you can use ODBC connection as well.

try
{
    // Connection string for a typical local MySQL installation
    string cnnString = "Server=localhost;Port=3306;Database=ci_series;Uid=root;Pwd=";

    // Create a connection object 
    MySqlConnection connection = new MySqlConnection(cnnString);

    // Create a SQL command object
    string cmdText = "INSERT INTO webuse(firstName,lastName,age) VALUES(?param1,?param2,?param3)";


    MySqlCommand cmd = new MySqlCommand(cmdText, connection);

    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add("?param1", MySqlDbType.VarChar).Value = firstName;
    cmd.Parameters.Add("?param2", MySqlDbType.VarChar).Value = lastName;
    cmd.Parameters.Add("?param3", MySqlDbType.VarChar).Value = age;

    connection.Open();

    int result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{

}

The command should be as

string cmdText = "INSERT INTO webuse(firstName,lastName,age) VALUES(?param1,?param2,?param3)";

edited Feb 22, 2018 at 13:22

Banana

2,4438 gold badges37 silver badges62 bronze badges

answered Jul 28, 2014 at 11:44

Janty

1,7142 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Janty Over a year ago

@neha ..Please follow the link stackoverflow.com/questions/8069539/mysql-odbc-parameter-issue

Janty Over a year ago

The @param1 is actually being interpreted by MySQL as an undefined server-side variable, hence the "cannot be null" error.

Neha Over a year ago

odbc only understands "?" as the parameter. this was the error

faby · Accepted Answer · 2014-07-28 11:43:49Z

0

I think that your OdbcCommand should be (replace in query @paramN with ?)

OdbcCommand odcmd_Insert = new OdbcCommand("INSERT INTO webuse(firstName,lastName,age) VALUES(?,?,?)",conn);

odcmd_Insert.Parameters.Add(new OdbcParameter( "@param1", FirstName));
odcmd_Insert.Parameters.Add(new OdbcParameter( "@param2", LastName));
odcmd_Insert.Parameters.Add( new OdbcParameter("@param3", age));

Instead of the parameter it takes a ? in the CommandText (leave the name in the actual parameters param1,param2,param3)

answered Jul 28, 2014 at 11:43

faby

7,6043 gold badges30 silver badges45 bronze badges

2 Comments

faby Over a year ago

same behavior in result?

faby Over a year ago

look here dailycoding.com/Posts/addparameterizedqueriesinmysqloledb.aspx

Collectives™ on Stack Overflow

Null value getting stored in mySQL table when insert query fired from ASP.NET using mySQL odbc connector

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related