MySQL InsertCommand problems

[Glasgow]

I know I must be doing something really dumb but I can't work out the problem. The code is below. The Update line (4 from the bottom) causes a MySQL (v4 I think) failure: "Column 'MbrMemberId' cannot be null ". The field does appear to be present in the dataset but somehow I guess I'm failing to substitute the parameters. I've tried question marks instead of '@' but no joy. Any help would be appreciated.

        Dim oConnection As OdbcConnection = New OdbcConnection(DbConnect)
        Dim sSQL As String = "SELECT * FROM Mbr"
        Dim DadMbr As OdbcDataAdapter = New OdbcDataAdapter(sSQL, oConnection)
        Dim DstMbr As DataSet = New DataSet(), RowNew As DataRow
        DadMbr.MissingSchemaAction = MissingSchemaAction.AddWithKey 'this will convince lookup code that table has a primary key
        DadMbr.Fill(DstMbr, "Mbr")
        
        RowNew = DstMbr.Tables(0).NewRow()
        With RowNew
            .Item("MbrMemberId") = TxtLoginRG.Text
            .Item("MbrPassword") = TxtPasswordRG.Text
            .Item("MbrName") = TxtName.Text
            .Item("MbrEmail") = TxtEmail.Text
            .Item("MbrPhone") = TxtPhone.Text
            Response.Write("MEMBER ID: " & .Item("MbrMemberId"))
        End With
        DstMbr.Tables(0).Rows.Add(RowNew)
        Response.Write("<br/>UPDATE SUCCESSFUL")
        DadMbr.InsertCommand = New OdbcCommand( _
                               "INSERT INTO Mbr(MbrMemberId,  MbrPassword,  MbrName,  MbrEmail,  MbrPhone) " & _
                                    "VALUES   (@MbrMemberId, @MbrPassword, @MbrName, @MbrEmail, @MbrPhone)", oConnection)
        '                     "INSERT INTO Mbr (MbrMemberId, MbrPassword) VALUES  (@MemberId, @Password)", oConnection)
        DadMbr.InsertCommand.Parameters.Add("@MbrMemberId", OdbcType.VarChar, 10, "MbrMemberId")
        DadMbr.InsertCommand.Parameters.Add("@MbrPassword", OdbcType.VarChar, 30, "MbrPassword")
        DadMbr.InsertCommand.Parameters.Add("@MbrName", OdbcType.VarChar, 30, "MbrName")
        DadMbr.InsertCommand.Parameters.Add("@MbrEmail", OdbcType.VarChar, 30, "MbrEmail")
        DadMbr.InsertCommand.Parameters.Add("@MbrPhone", OdbcType.VarChar, 30, "MbrPhone")
        DadMbr.Update(DstMbr, "Mbr")
        Response.Write("<br/>DATABASE UPDATE SUCCESSFUL")
        DgdMbr.DataSource = DstMbr
        DgdMbr.DataBind()

 

[jmeckley]

try removing the MbrMemberId column and parameter from the insert statement. while you want to select this field, you don't want to insert it because it's an auto-incrementing field.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Glasgow]

Thanks Jason but I'm afraid it's not an autoincrement field - it's text entered by the user - NB this app is just really a training exercise.

 

[jmeckley]

Ok, then try removing [tt]DadMbr.MissingSchemaAction = MissingSchemaAction.AddWithKey[/tt] I understand this to pertain to auto-incrementing PKs. if that doesn't solve the problem, then ensure "MbrMemberId" is spelled correctly in all instances and confirm [tt]TxtLoginRG.Text[/tt] has a value.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Glasgow]

Thanks. I tried the MissingSchema action change to no avail. I think I'm doing something very fundamentally wrong here as, on further investigation, I realise that none of the database fields are being populated with the parameters. I tend to think I may be missing a step.

This whole InsertCommand thing seems well over the top to me when I think about how simple this was in "classic" ADO.

 

[jmeckley]

do you have any try/catch statements anywhere that could be swallowing an exception? if so remove them and see if an exception is thrown. if so post the stacktrace here. another approach is to remove the DataAdapter and use the connection/command objects explicitly to read/write data. You can also check out my FAQ on database connection management (see my signature below). In some ways this may be more confusing, but if you have questions please ask

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Glasgow]

Thanks again. I have no Try/Catch in this code. I had contemplated reverting to explicit INSERT's but really wanted to find out what I might be doing wrong using the documented approach (even though I don't like it a lot).

In the meantime I've also posted on a MySQL forum in case it's something to do with using MySQL 4.

Are there any advantages/disadvantages to using an explicit INSERT via a command object?

 

[jbenson001]

it's best to use stored procedures if your database allows.

 

[jmeckley]

really wanted to find out what I might be doing wrong using the documented approach (even though I don't like it a lot).

there are many approaches to data access. .net provides the developer with basic building blocks via the ADO.Net objects. you can then build other frameworks with them. here are some data access projects for .net built on top of ADO.Net
Active Record
NHibernate
Entity Framework 4.0
Linq2Sql
LLBL Gen Pro ($)
MS Data Access Block
You could even write your own, like the one I created for the FAQ.

The point of all that is there is not right way. And some/most of the time the examples given by MS are crap anyway.

In all the development I have done (with or without a 3rd party framework) I have never seen the DataAdapter objects used except for example code from MS and the obnoxious data wizards in VS.

The idea behind DataAdapter is to define the read/write statements in a single object and then reference that object whenever you want to preform a database call. this way you only write the sql in one place. but there are other ways of accomplishing that which give you greater control.

did you look through the FAQ I mentioned in the previous post? if not take a look at how read and write operations are done. a command is defined and then executed with the appropriate executor:
command.ExecuteReader(): for record(s)
command.ExecuteNonReader(): for inset, update delete
command.ExecuteScalar(): for a single value
the DataAdapter is using this command members under the hood.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Glasgow]

Thanks for the input Jason - I may just abandon the adapter approach.

jbenson001 Thanks. As I'm talking to a remote database hosted by an ISP I think stored procedures may be a no-no. Anyway, at the moment I'm really just using this as an exercise for getting to grips with ASP.NET and ADO.NET. I'd be aiming to streamline things (and introduce stored procedures etc when permissable) once I've got something that works - a target I seem to be some way off achieving!

 

[jmeckley]

stored procs, or dynamic sql isn't the issue. the adapter is not executing the insert statement as Glasgow expects. Last I used MySql there was not a concept of stored procs anyway.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Glasgow]

For the moment I've coded an explicit INSERT and it's working fine and, flankly, feels a lot simpler and more elegant. Ultimately I'd be interested to know where I've gone off the rails but I need to move forward for now.

Thanks for your input.