Archived

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

mySQL programming: Field not updatable?

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

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 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

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

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 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 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 on other sites

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

Share on other sites
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 on other sites
Nope. I tried updating another table(User table) like this:

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 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.

Screenshot of ODBC
Database script

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 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

1. 1
2. 2
Rutin
24
3. 3
4. 4
JoeJ
18
5. 5

• 14
• 23
• 11
• 11
• 9
• Forum Statistics

• Total Topics
631768
• Total Posts
3002242
×

Important Information

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!