MySQL Database Null Issue

Question

I am currently entering data into a database from a calendar, and I have realized that any new entry I input turns out to be null. All of the data entered is of the same dataype as the column, and I'm confused to as how this is happening. None of the values I'm inputting are null or empty. I have debugged and watched the values to know that.

public static void insertEvent(string date, string title, string eventLocation, string detailsShort, string detailsLong, DateTime startTime, DateTime endTime, DateTime entered, string enteredBy)
        {
            try
            {

                string queryString = "INSERT INTO cor(cal_date,event_title,event_location,details_short,details_long,time_start,time_end,entered,entered_by)    VALUES (cal_date=cal_date, event_title=event_title, event_location=event_location, details_short=details_short, details_long=details_long,time_start=time_start, time_end=time_end, entered=entered, entered_by=entered_by)";
                OdbcConnection conn = new OdbcConnection(MyConString);
                conn.Open();
                OdbcCommand command = new OdbcCommand(queryString, conn);
                command.Parameters.Add("cal_date", OdbcType.DateTime, 30).Value = date;
                command.Parameters.Add("event_title", OdbcType.VarChar, 100).Value = title;
                command.Parameters.Add("event_location", OdbcType.VarChar, 100).Value = eventLocation;
                command.Parameters.Add("details_short", OdbcType.VarChar, 300).Value = detailsShort;
                command.Parameters.Add("details_long", OdbcType.VarChar, 300).Value = detailsLong;
                command.Parameters.Add("time_start", OdbcType.DateTime, 30).Value = startTime;
                command.Parameters.Add("time_end", OdbcType.DateTime, 30).Value = endTime;
                command.Parameters.Add("entered", OdbcType.DateTime, 30).Value = entered;
                command.Parameters.Add("entered_by", OdbcType.VarChar, 30).Value = enteredBy;
                command.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception)
            {

            }
        }

+1 for strict data-type languages. Did you try outputting any exception messages? — Mike Purcell
– Mike Purcell, Commented Nov 30, 2011 at 23:19
There are no exceptions from the the query. Is there anything from the DB i can take a look at? — bluesky
– bluesky, Commented Nov 30, 2011 at 23:23
@MasterP Might want to change your root password; anyone can view the edit history of the question and still see it. — Dan J
– Dan J, Commented Dec 1, 2011 at 1:06

Bill Karwin · Accepted Answer · 2011-12-01 00:05:48Z

1

INSERT INTO cor(cal_date, ...) VALUES (cal_date=cal_date, ...);

The problem is the expression cal_date=cal_date (and similar expressions for every column).

As you're inserting a new row, there is no value for any column yet. So any reference to the column is NULL. The expression NULL=NULL also yields NULL. So you're not inserting values, you're inserting NULL expressions for all columns.

If you change the expression to cal_date=@cal_date it doesn't fix the problem. You're comparing the current value for cal_date (which is NULL) to the value of the parameter @cal_date. An expression like NULL=<anything> always yields NULL.

You should just use parameters, not expressions:

INSERT INTO cor(cal_date, event_tile, ...) VALUES (@cal_date, @event_title, ...);

update: Read the example code in "Pass Parameters to OdbcCommand". That example shows using ? placeholders as @ZombieHunter's answer recommends -- not named parameter placeholders. However, when calling Parameters.Add(), somehow one uses the named parameter with @ prefix. Go figure.

nonqueryCommand.CommandText = "INSERT INTO MyTable VALUES (?, ?)";
nonqueryCommand.Parameters.Add("@MyName", OdbcType.VarChar, 30);
nonqueryCommand.Parameters.Add("@MyNumber", OdbcType.Int);

edited Dec 1, 2011 at 0:05

answered Nov 30, 2011 at 23:44

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

bluesky Over a year ago

The addition of the @ symbol to differentiate the variables still caused the null values.

CodeZombie · Accepted Answer · 2011-11-30 23:50:10Z

0

I'm not sure if ODBC supports named parameters together regular SQL statements. The ODBC statements I've seen so far use "?" as placeholder for the parameters.

Certain DBMSs allow an application to specify the parameters to a stored procedure by name instead of by position in the procedure call. Such parameters are called named parameters. ODBC supports the use of named parameters. In ODBC, named parameters are used only in calls to stored procedures and cannot be used in other SQL statements.
http://msdn.microsoft.com/en-us/library/windows/desktop/ms715435%28v=VS.85%29.aspx

Why don't you use the native MySQL provider for .NET? That would be much faster and probably supports more MySQL specific features.

Also I strongly recommend not using empty catch blocks unless you have a good reason for "consume" that exception.

edited Nov 30, 2011 at 23:50

answered Nov 30, 2011 at 23:41

CodeZombie

5,3753 gold badges32 silver badges37 bronze badges

3 Comments

bluesky Over a year ago

the empty catch is just for debugging.

bluesky Over a year ago

All of my queries use this connector and I've been stomped so long that I don't want to give up without figuring out the answer. lol.

CodeZombie Over a year ago

All right. So just use the ´?´ instead as parameter placeholder and add the parameters using command.Parameters.AddWithValue() in the right order.

Collectives™ on Stack Overflow

MySQL Database Null Issue

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related