asp.net and mysql -- PLEASE HELP!

[SelArom]

I cannot figure this one out! I have a database with a table called main categories. I can insert new main categories all I want but when I try to delete them, nothing happens! I've tried two approaches:

1) deleting from the dataset:
Dim deleteMainCat As dsCategories.maincategoriesRow = dsCategories.maincategories.Rows.Find(lstMainCat.SelectedValue)
dsCategories.maincategories.Rows.Remove(deleteMainCat)
daMainCategories.Update(dsCategories.maincategories)
lstMainCat.DataBind()

and

2) deleting using sql
Dim deleteCommand1 As New OdbcCommand("DELETE From maincategories WHERE MaincatID=@MaincatID", connDB)
deleteCommand1.Parameters.Add(New OdbcParameter("@MaincatID", OdbcType.Int))
deleteCommand1.Parameters("@MaincatID").Value = lstMainCat.SelectedValue
connDB.Open()
deleteCommand1.ExecuteNonQuery()
connDB.Close()
daMainCategories.Fill(dsCategories.maincategories)
lstMainCat.DataBind()

but neither one actually changes the database! When I do the first option (deleting using the dataset), it removes the main category from the dataset (so it LOOKS LIKE it worked), but when I open the database, the category is still there! and when I try it just using sql (option 2), it doesn't do anything at all.

I have the same problem with the UPDATE command, exactly the same behavior. WHAT GIVES? Can anyone help?!!!

-SelArom

 

[agnitrate]

Do you need a semicolon to terminate the command?

Also, try assigning your SQL statement to a string and print it out for debugging. Take the printed string and input it directly into the MySQL console to see if it works. If yes, there's something wrong with that oddly styled ASP code. If not, well... I'm pretty sure that you're doing something wong but I can't say because I don't know ASP. Give it a shot though and see if it works.

 

[SelArom]

Originally posted by: agnitrate
Do you need a semicolon to terminate the command?

Also, try assigning your SQL statement to a string and print it out for debugging. Take the printed string and input it directly into the MySQL console to see if it works. If yes, there's something wrong with that oddly styled ASP code. If not, well... I'm pretty sure that you're doing something wong but I can't say because I don't know ASP. Give it a shot though and see if it works.

I think it's a problem with using mysql with asp.net, because when I was using the dataset with an xmlfile (as opposed to a database), the inserts, deletes, and updates all worked fine. I think there's something I'm not configuring, but I don't know what it is! someone pleasehelp, I'm losing my mind here

-SelArom

 

[HJB417]

I don't know what odbc driver your using, but the official mysql odbc driver doesn't support named parameters. I recommend using the official mysql .net data provider (mysql connector .net). I made my own .net data provider using the mysql c api so making your own is always an option.

 

[SelArom]

Originally posted by: HJB417
I don't know what odbc driver your using, but the official mysql odbc driver doesn't support named parameters. I recommend using the official mysql .net data provider (mysql connector .net). I made my own .net data provider using the mysql c api so making your own is always an option.

I'm using the odbc connector with a mysql connection string:

DRIVER={MySQL ODBC 3.51 Driver};UID=****;PASSWORD=****;DESC=mySQL;DATABASE=backstageMagicShop;SERVER=localhost;PORT=3306

is that the mysql odbc driver? how am I supposed to do updates if I can't use named parameters? I'll try using the mysql connector .net hopefully that will clear things up. how did you make your own? thanks for the info!

-SelArom

 

[SelArom]

WTFFFF I just can't get this SHT to work, what am I doing wrong? I'm not using the .net data provider, I'm using the odbc data provider, and I know that it supports named parameters, because the Insert command was generated automatically and it uses named parameters. here is the code it generated:

'OdbcInsertCommand1
'
Me.OdbcInsertCommand1.CommandText = "INSERT INTO maincategories(MaincatName, MaincatDescription) VALUES (?, ?)"
Me.OdbcInsertCommand1.Connection = Me.connDB
Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("MaincatName", System.Data.Odbc.OdbcType.VarChar, 100, "MaincatName"))
Me.OdbcInsertCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("MaincatDescription", System.Data.Odbc.OdbcType.VarChar, 2147483647, "MaincatDescription"))

but when I try to get it to generate the update and delete commands it says it cannot determine the rows that uniquly identify the rows which is total BULLSH!T because all of the FRGIINGN tables have FRIGNIGN primary keys!!

-SelArom

 

[HJB417]

calm your nipples.
parameters with ? are positional/unamed parameters =]
the official mysql odbc driver (which is what you're using) supports this.
ado.net generates parameterized queries that use positional/unamed parameters. That is why the auto generated code works.

 

[HJB417]

Also, you may find it easier to use a commandbuilder when working with datasets or datatables. It will automatically generated insert/update/delete statements when you call DataAdapter.Update.

 

[SelArom]

Originally posted by: HJB417
calm your nipples.
parameters with ? are positional/unamed parameters =]
the official mysql odbc driver (which is what you're using) supports this.
ado.net generates parameterized queries that use positional/unamed parameters. That is why the auto generated code works.

I figured as much that the positional parameters worked (since they are generated automatically by the code). But why doesn't it generate the Update and Delete commands?!

I tried going into the properties and typing in the commands manually. I typed
UPDATE maincategories set MaincatName=?, MaincatDescription=? WHERE MaincatID=?
and
DELETE FROM maincategories WHERE MaincatID=?

and it asked me if I wanted to autogenerate parameters and I said yes and here's what it autogenerated:

'OdbcUpdateCommand1
Me.OdbcUpdateCommand1.CommandText = "UPDATE maincategories set MaincatName=?, MaincatDescription=? WHERE MaincatID=?"
Me.OdbcUpdateCommand1.Connection = Me.connDB
Me.OdbcUpdateCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("MaincatName", System.Data.Odbc.OdbcType.VarChar, 100, "MaincatName"))
Me.OdbcUpdateCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("MaincatDescription", System.Data.Odbc.OdbcType.VarChar, 2147483647, "MaincatDescription"))
Me.OdbcUpdateCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_MaincatID", System.Data.Odbc.OdbcType.Int, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "MaincatID", System.Data.DataRowVersion.Original, Nothing))

'OdbcDeleteCommand1
Me.OdbcDeleteCommand1.CommandText = "DELETE FROM maincategories WHERE MaincatID=?"
Me.OdbcDeleteCommand1.Connection = Me.connDB
Me.OdbcDeleteCommand1.Parameters.Add(New System.Data.Odbc.OdbcParameter("MaincatID", System.Data.Odbc.OdbcType.Int, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "MaincatID", System.Data.DataRowVersion.Original, Nothing))

which looks right to me! Here's my update code:
Dim updateMainCat As dsCategories.maincategoriesRow = dsCategories.maincategories.Rows.Find(lstMainCat.SelectedValue)
updateMainCat.MaincatName = txtMainCatRename.Text
daMainCategories.Update(dsCategories.maincategories)
lstMainCat.DataBind()

and I don't get any error messages, just nothing happens at all! I just don't get it!

-SelArom

PS I tried using the command builder, and nothing happened there either. what gives? I've searched online for hours for a solution, and it seems I'm the ONLY LOSER on the PLANET with this problem!

 

[SelArom]

OKAY

i got the update working! It turns out i was reseting the textbox on every page load, so it was overwriting with the same data!

BUT

the delete still doesn't work! If I put the command directly, like this:
connDB.Open()
Dim deletecom As OdbcCommand = connDB.CreateCommand
deletecom.CommandType = CommandType.Text
deletecom.CommandText = "Delete from maincategories where MaincatID=5"
deletecom.ExecuteNonQuery()
connDB.Close()

it WORKS. but when I change it back to the dataadapter:
daMainCategories.Fill(dsCategories.maincategories)
Dim deleteMainCat As dsCategories.maincategoriesRow = dsCategories.maincategories.Rows.Find(lstMainCat.SelectedValue)
dsCategories.maincategories.Rows.Remove(deleteMainCat)
daMainCategories.Update(dsCategories.maincategories)

it removes the row from the dataset, but it does NOT remove the row in the database when I call the damaincategories.update command. What gives now?

-SelArom