Archived

This topic is now archived and is closed to further replies.

mySQL programming: Field not updatable?

This topic is 5670 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I created an application in VB for school. I use an mySQL database. But when I try to write to at least 2 tables(Haven't tested the others yet) I get the error:
Runtime error '3113':
Cannot update 'Leveranciersnummer'; field not updatable
  
This are the settings of the field:
Type: BigInt
Length/set: 10
Default: 0
Unsigned, not null
  
The other field in another table is a varchar(40) with NOT NULL set. When I try to paste in the SQL code generated by the program in the mySQL console it works fine. I'm logged in over ODBC as use root on the localhost. [EDIT] Here is some source:
  
'Open the database:
Set DB = OpenDatabase("Posthuis", dbDriverNoPrompt, True, _
                      "ODBC; DATABASE=Posthuis; DSN=Posthuis")

'Create a Query, it's the smallest I have for you guys:
strSQL = "INSERT INTO Brochure(LeveranciersNummer, Naam, Voorraad, " & _
"Opbrengst1000, MinVoorraad, AutoBestel, Actief) VALUES(" & _
LeverancierNummer & ", '" & txtNaam.Text & _
"', " & CInt(txtStartVrd.Text) & ", " & CDbl(txtOpbrengst) & ", " & _
txtMinVrd.Text & ", " & AutoBestel & ", " & Actief & ")"

'Execute the Query:
call DB.execute(strSQL)
  
I use an myODBC driver wich logins as root. Reading the userset for the program from the database works, but adding is a problem. Has this something to do with table locking? [/EDIT] Sand Hawk ---------------- -Earth is 98% full. Please delete anybody you can. [edited by - sand_hawk on June 5, 2002 4:45:52 PM]

Share this post


Link to post
Share on other sites
*Runs screaming into a wall pulling out his hair!*
Crap, nobody that is able to help me? This is for a schoolproject and I really need it. If some of you guys need more code, I''m willing to give it but I defenitley need some help here.

Sand Hawk

What''s the meaning of this post? Kicking it back to top...


----------------
-Earth is 98% full. Please delete anybody you can.

Share this post


Link to post
Share on other sites
if you set your LeveranciersNummer (whatever that means) field to be your primary key and Auto Increment (Identity) you don''t need to insert a value there, try:


  
INSERT INTO Brochure(Naam, Voorraad, " & _
"
Opbrengst1000, MinVoorraad, AutoBestel, Actief) VALUES(" & _
"
, ''" & txtNaam.Text & _
"
'', " & CInt(txtStartVrd.Text) & ", " & CDbl(txtOpbrengst) & ", " & _
txtMinVrd.Text & "
, " & AutoBestel & ", " & Actief & ")"


other than that I cannot help without seeing the CREATE TABLE statement for this table

Share this post


Link to post
Share on other sites
The Leveranciersnummer field(Wich means SupplierCode, it''s hooked to another table wich contains all the data for the supplier) can''t be primary, auto increment since it''s having a relation with another table. Here is my create table code:


CREATE TABLE `brochure` (
`Brochurenummer` bigint(10) unsigned NOT NULL auto_increment,
`Leveranciersnummer` bigint(10) unsigned NOT NULL default ''0'',
`naam` varchar(80) NOT NULL default ''0'',
`Voorraad` bigint(10) unsigned NOT NULL default ''0'',
`Opbrengst1000` double NOT NULL default ''0'',
`MinVoorraad` bigint(10) unsigned NOT NULL default ''0'',
`AutoBestel` enum(''Y'',''N'') NOT NULL default ''N'',
`Actief` enum(''Y'',''N'') NOT NULL default ''Y'',
PRIMARY KEY (`Brochurenummer`)
);


that''s it.

Sand Hawk


----------------
-Earth is 98% full. Please delete anybody you can.

Share this post


Link to post
Share on other sites
I've never worked with MySQL over ODBC myself, but I have heard that there can be some strange problems. Here's a snippet from the MySQL manual (Programs Known to Work with MyODBC) that may be relevant:

Visual Basic with ADO can't handle big integers. This means that some queries like SHOW PROCESSLIST will not work properly. The fix is to set/add the option OPTION=16834 in the ODBC connect string or set the "Change BIGINT columns to INT" option in the MyODBC connect screen. You may also want to set the "Return matching rows" option.


[edited by - spock on June 5, 2002 12:30:05 AM]

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
Have you tried inserting it WITHOUT the ''Leveranciersnummer''. This should be possible since you use a default value.
After this, update the record with

"UPDATE Brochure SET Leveranciersnummer=" & _
LeverancierNummer & " WHERE Leveranciersnummer=0"

Let us know if this solved your problem.

Dennis

Share this post


Link to post
Share on other sites
Nope. I tried updating another table(User table) like this:

UPDATE User SET Password = 'Password' WHERE Name = 'Patrick';

This says the field cannot be updated. (Or something similar to that, don't have the error msg here because the project is at school). It seems like we can't write to the DB but reading isn't a problem. If ppl want to see the ODBC settings I can make a screenshot. I will try later this eve to use AP tip since I have to leave now.

Sand Hawk


----------------
-Earth is 98% full. Please delete anybody you can.

[edited by - sand_hawk on June 6, 2002 9:29:30 AM]

Share this post


Link to post
Share on other sites
I tried AP's tip and it failed. If I remove LeveranciersNummer(Meaning == Supplier Code :D) it starts to whine about Naam(==Name) that cannot be update because the field is not updatable. This is really starting to bug me.

To provide more information about the problem I made a screendump of the ODBC driver screen and I uploaded an export of my database to my site.

Screenshot of ODBC
Database script

Maybe these 2 provide more information.

A extremely confused Sand Hawk
[EDIT]
Whoops, made mistake in HTML tags
[/EDIT]


----------------
-Earth is 98% full. Please delete anybody you can.

[edited by - sand_hawk on June 6, 2002 5:41:34 PM]

Share this post


Link to post
Share on other sites
When opening the workspace, you specified a default cursortype for the connections/result-sets.

The cursor can be client-sided or server-sided.

Make sure you use the correct cursor-type.
Perhaps this will help.



--
Dennis Aries

Share this post


Link to post
Share on other sites
*ashamed* It found the problem. We opened the database as readonly. I walked thru the code with a teacher and found out the DB was opened as Read-only. *smacks himself on the forehead*

Thanks for all the time you guys spend on me.

Sand Hawk


----------------
-Earth is 98% full. Please delete anybody you can.

Share this post


Link to post
Share on other sites