[RESOLVED] Urgent: getting column cannot be null, unable to pass string(?), MySQL VB.NET

[cjmrdc]

Howdy, I originally posted this in the VB.NET section however think it should be here, I have resolved/closed my post there (although it is NOT resolved) right off the bat - this is the code I'm using:

        Dim aAccount, aCAE, aFlag, aMonth, aDate As String
        aAccount = "TestAccount"
        aCAE = "TestCAE"
        aFlag = "TestFlag"
        aMonth = "TestMonth"
        aDate = "TestDate"

        Dim cmd As New MySqlCommand("INSERT INTO flags (Account, CAE, Flag, Month, Date) VALUES (@Account, @CAE, @Flag, @Month, @Date)", conn)
        cmd.Parameters.AddWithValue("@Account", aAccount)
        cmd.Parameters.AddWithValue("@CAE", aCAE)
        cmd.Parameters.AddWithValue("@Flag", aFlag)
        cmd.Parameters.AddWithValue("@Month", aMonth)
        cmd.Parameters.AddWithValue("@Date", aDate)
        cmd.ExecuteNonQuery()

I can read from the table just fine, however when inserting I get
"Column 'Account' cannot be null" -
I realize that I have NULL fields disabled, and that's correct - I don't want to insert a null field, I want to insert the string I created just before I called the statement. If I disable null values in the DB, it updates all the fields with a blank entry (obviously), I assume I'm having a problem passing the string through. The fields are set to TEXT, so that's not the issue. If I comment out the line that adds account, the error moves to CAE, and so on and so forth. What am I doing wrong here? This is urgent as it's something I'm working on at work and need to have finished by tomorrow, this is the last thing I need to get working.

Thanks

[CompositeID]

I believe that the parameters in MySql are "?" not like the ones in SQL Server where the parameters have a prefix of "@". I believe what you are looking for is:

Dim aAccount, aCAE, aFlag, aMonth, aDate As String
Dim sqlQuery As String = "INSERT INTO flags (Account, CAE, Flag, Month, Date) VALUES (?, ?, ?, ?, ?)", 
aAccount = "TestAccount"
aCAE = "TestCAE"
aFlag = "TestFlag"
aMonth = "TestMonth"
aDate = "TestDate"
 
 
Dim cmd As New MySqlCommand(sqlQuery, conn)
cmd.Parameters.AddWithValue("?Account", aAccount)
cmd.Parameters.AddWithValue("?CAE", aCAE)
cmd.Parameters.AddWithValue("?Flag", aFlag)
cmd.Parameters.AddWithValue("?Month", aMonth)
cmd.Parameters.AddWithValue("?Date", aDate)
cmd.ExecuteNonQuery()

The parameter names in these queries are strictly for recalling a value from an actual parameter.

[cjmrdc]

CompositeID, thanks for your response.
Changing to ? instead of @Column (and even completely changing to every bit of code provided), produces a Format Exception.

System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Should the addwithvalue strings still contain @Column? or should they be ?Column? I'm kind of running into a wall here, obviously.

[cjmrdc]

CompositeID, thanks for your response.
Changing to ? instead of @Column (and even completely changing to every bit of code provided), produces a Format Exception.

System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Should the addwithvalue strings still contain @Column? or should they be ?Column? I'm kind of running into a wall here, obviously.

I answered my own question here, cmd.Parameters.AddWithValue("?Account", aAccount) works fine.

Thanks so much.

[CompositeID]

Sorry about that. I completely forgot to change the names of the parameters in the collection.