Parameter problem with INSERT.

I'm having trouble using Odbc parameters in my INSERT statement.

Sub Button1_Click(sender As Object, e As EventArgs)
 Dim cnn As New OdbcConnection(ConnectionString)
 Dim myStr As String = "INSERT INTO ccc_producer SET timestamp=NOW(), name=@dotnet.itags.org.name"
 Dim cmd As New OdbcCommand(myStr, cnn)
 cmd.Parameters.Add(new OdbcParameter("@dotnet.itags.org.name", name.Text))
 cnn.Open()
 cmd.ExecuteNonQuery()
 cnn.Close()
 response.redirect("producer_list.aspx")
 End Sub

All I get is that the name column can't be NULL (which i can't), but why isn't the value of my textbox ("name") assigned to the parameter?

Is there something special with Odbc? All examples I have to look at are using SqlClient classes.

/RaymondRaymond,

How many fields are in your ccc_producer table?

Jeff
Try:

cmd.Parameters.Add("@.name", OdbcType.<data type>, <length>).Value = name.Text

Brian
Hello, try something like:

Dim prm As OdbcParameter = cmd.Parameters.Add("@.name", OdbcType.Char, 50
prm.Value = name.Text

regards.
OK, I tried this

cmd.Parameters.Add("@.name", OdbcType.VARCHAR, 100).Value = name.Text

but with the same result.

The table contains 25 columns, but only name has to be NOT NULL. (I'm using MySQL).

/Raymond
Is the compiler getting confused with the parameter name and textbox name. Did you try using a different textbox id. Nothing else seem to be wrong
OK, I change the names...

Sub Button1_Click(sender As Object, e As EventArgs)
 Dim cnn As New OdbcConnection(ConnectionString)
 Dim myStr As String = "INSERT INTO ccc_producer SET timestamp=NOW(), name=@.param_name"
 Dim cmd As New OdbcCommand(myStr, cnn)
 cmd.Parameters.Add("@.param_name", OdbcType.VARCHAR, 100).Value = tb_name.Text
 cnn.Open()
 cmd.ExecuteNonQuery()
 cnn.Close()
 response.redirect("producer_list.aspx")
 End Sub

Error says:

No change in outcome.[OdbcException: ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.17-max-debug]Column 'name' cannot be null]
System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode) +32
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +1189
System.Data.Odbc.OdbcCommand.ExecuteNonQuery() +152
ASP.producer_admin_aspx.Button1_Click(Object sender, EventArgs e) +153
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +83
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292

/Raymond
Hey,

It's your query, change to something along the lines of this format:

INSERT INTO ccc_producer (<name for timestamp>, name) values (@.timestamp, @.param_name)

Brian
Hmmm...nope. Changed to the following:

Dim myStr As String = "INSERT INTO ccc_producer (timestamp, name) values(NOW(), @.param_name)"

Same thing.

/Raymond
You should pass the timestamp as parameter or some other way. This way nothing goes inside. "Now()" would just be a string
You may want to see if MySQL has a similar command to MS SQL's GetDate() function.
The MySQL func to get the timestamp is NOW(), and I'm using that.

/Raymond
Raymond, I am far from an expert in MySQL but for the little I know you are not supposed to specify an insert value for "timestamp" type fields (if that's the type of the timestamp column): it's supposed to self-populate, at insertion.

now() is the correct function (for what I know) for MySQL but if you want to use it then perhaps you should change the date field to a DATETIME type ... see if that works (if that's what you want to do).

Perhaps a better experienced person in MySQL might step in here ... I am just going off the top of my head. MS SQL is really my field.
The timestamp column is of type DATETIME, also I've use this funtion without any problems before.

Before I used to build my query with strings like:

Dim myStr As String = "INSERT INTO ccc_producer SET timestamp = NOW(), name = '" & tb_name.Text & "'"

That also works (as long as you don't input '). Now I wanted to try using parameters instead, and I just can't seem to get it working!

/Raymond
Try using ? (question mark) instead of @.name in your SQL Statement. Never used MySql, but can't you set default values in SQL? If you can, forget 'timestamp=now()', and set 'timestamp' to get the value from the NOW() function in your Table design instead.