sql error PRIMARY KEY must be unique

sql error PRIMARY KEY must be unique - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: sql error PRIMARY KEY must be unique (/showthread.php?tid=643852)

sql error PRIMARY KEY must be unique - lackmail - 28.10.2017

Hello everyone i am trying to make a vehicle ownership system with sqlite i have done it so it save the first car but when ever i try to add more than one car it gives me this error :

Код:

[Warning] db_query: Query failed: PRIMARY KEY must be unique

Код:

	new DBResult: Result;

	Result = db_query(VehicleDB, "SELECT last_insert_rowid()"); // it will return the value for field "userid" generated
	VehicleInfo[LoadedVehicles][vID] = db_get_field_int(Result);
	db_free_result(Result);
	
	new Query[208];
	format(Query, sizeof Query, "INSERT INTO vehicles (id, owner, ownerid, price, forsale, model, posx, posy, posz, posa) VALUES (%d, '%s', '%d', '%d', '%d', '%d','%f', '%f', '%f', '%f')", VehicleInfo[LoadedVehicles][vID], "NONE", -1, price, 1, model, cPos[0], cPos[1], cPos[2], cPos[3]);
	db_query(VehicleDB, Query);

Re: sql error PRIMARY KEY must be unique - Jefff - 28.10.2017

Show CREATE TABLE part, also you dont need this VehicleInfo[LoadedVehicles][vID] if `id` is PRIMARY KEY

Re: sql error PRIMARY KEY must be unique - lackmail - 28.10.2017

Quote:

Originally Posted by Jefff

Show CREATE TABLE part, also you dont need this VehicleInfo[LoadedVehicles][vID] if `id` is PRIMARY KEY

Код:

        db_query(VehicleDB, "CREATE TABLE IF NOT EXISTS vehicles (id INTEGER PRIMARY KEY AUTOINCREMENT, owner VARCHAR(24) COLLATE NOCASE, ownerid INTEGER DEFAULT -0 NOT NULL, forsale INTEGER DEFAULT 1 NOT NULL, model INTEGER DEFAULT 0 NOT NULL, color1 INTEGER DEFAULT 0 NOT NULL, color2 INTEGER DEFAULT 0 NOT NULL, price INTEGER DEFAULT 0 NOT NULL, posx FLOAT, posy FLOAT, posz FLOAT, posa FLOAT)");

Re: sql error PRIMARY KEY must be unique - Jefff - 28.10.2017

OnGameModeInit

pawn Код:

new DBResult:Result = db_query(VehicleDB, "SELECT * FROM `vehicles`");
VehicleInfo[LoadedVehicles][vID] = db_num_rows(Result);
db_free_result(Result);

adding new vehicle into database

pawn Код:

new Query[208];
VehicleInfo[LoadedVehicles][vID]++;
format(Query, sizeof Query, "INSERT INTO vehicles (owner, ownerid, price, forsale, model, posx, posy, posz, posa) VALUES ('NONE', %d, %d, %d, %d, '%f', '%f', '%f', '%f')", -1, price, 1, model, cPos[0], cPos[1], cPos[2], cPos[3]);
db_query(VehicleDB, Query);

Re: sql error PRIMARY KEY must be unique - lackmail - 29.10.2017

i get this error after adding your code and it saves nothing not even the first car

Код:

[Warning] db_query: Query failed: 10 values for 9 columns

This was my code before:

Код:

CMD:aveh(playerid, params[])
{
    new model,color,color2,Float:cPos[4], price;
    if(sscanf(params, "iiii", model, price, color,color2)) return SendClientMessage(playerid, -1, "/aveh [model] [price] [color] [color]");
    if(model < 400 || model > 611) return SendClientMessage(playerid,-1, "vehicle IDs between 400 - 611!");
    if(color> 255 || color< 0) return SendClientMessage(playerid, 0xFFFFFF, "Car color ID's: 0-255");
    if(color2> 255 || color2< 0) return SendClientMessage(playerid, 0xFFFFFF, "Car color ID's: 0-255");
    GetPlayerPos(playerid, cPos[0], cPos[1], cPos[2]);
    GetPlayerFacingAngle(playerid, cPos[3]);
    CreateVehicle(model, cPos[0], cPos[1]-3, cPos[2], cPos[3], color, color2, -1);
	LoadedVehicles ++;

	new DBResult: Result;

	Result = db_query(VehicleDB, "SELECT last_insert_rowid()"); // it will return the value for field "userid" generated
	VehicleInfo[LoadedVehicles][vID] = db_get_field_int(Result);
	db_free_result(Result);

	new Query[208];
	format(Query, sizeof Query, "INSERT INTO vehicles (id, owner, ownerid, price, forsale, model, posx, posy, posz, posa) VALUES (%d, '%s', '%d', '%d', '%d', '%d','%f', '%f', '%f', '%f')", VehicleInfo[LoadedVehicles][vID], "NONE", -1, price, 1, model, cPos[0], cPos[1], cPos[2], cPos[3]);
	db_query(VehicleDB, Query);

    return 1;
}

stock LoadVehicles()
{
	new DBResult: Result, id, buf[255];

	format(buf, sizeof buf, "SELECT * FROM vehicles");
	Result = db_query(VehicleDB, buf);

	if (db_num_rows(Result))
	{
		id = LoadedVehicles;
		VehicleInfo[id][vID] = db_get_field_assoc_int(Result, "id");
		VehicleInfo[id][vOwner] = db_get_field_assoc_int(Result, "owner");
    	VehicleInfo[id][vOwnerID] = db_get_field_assoc_int(Result, "ownerid");
    	VehicleInfo[id][vPrice] = db_get_field_assoc_int(Result, "price");
    	VehicleInfo[id][vForSale] = db_get_field_assoc_int(Result, "forsale");
    	VehicleInfo[id][vModel] = db_get_field_assoc_int(Result, "model");
		VehicleInfo[id][vPosX] = db_get_field_assoc_float(Result, "posx");
		VehicleInfo[id][vPosY] = db_get_field_assoc_float(Result, "posy");
     	VehicleInfo[id][vPosZ] = db_get_field_assoc_float(Result, "posz");
      	VehicleInfo[id][vPosA] = db_get_field_assoc_float(Result, "posa");

	//	sscanf(buf, "p<|>e<s[25]ddddddddfffff>", VehicleInfo[id][vOwner], VehicleInfo[id][vOwnerID], VehicleInfo[id][vForSale], VehicleInfo[id][vModel], VehicleInfo[id][vCol1], VehicleInfo[id][vCol2], VehicleInfo[id][vPrice], VehicleInfo[id][vPosX], VehicleInfo[id][vPosY], VehicleInfo[id][vPosZ], VehicleInfo[id][vPosA]);
		CreateVehicle(VehicleInfo[id][vModel],VehicleInfo[id][vPosX],VehicleInfo[id][vPosY],VehicleInfo[id][vPosZ],VehicleInfo[id][vPosA],VehicleInfo[id][vCol1], VehicleInfo[id][vCol2], 60*10000);
		format(buf, sizeof buf, "Vehicle Created model: %d Position: %f, %f, %f", VehicleInfo[id][vModel], VehicleInfo[id][vPosX], VehicleInfo[id][vPosY], VehicleInfo[id][vPosZ]);
		LoadedVehicles = LoadedVehicles+1;
		print(buf);
	}
	db_free_result(Result);
}

This is the code you wanted me to do:

Код:

CMD:aveh(playerid, params[])
{
    new model,color,color2,Float:cPos[4], price;
    if(sscanf(params, "iiii", model, price, color,color2)) return SendClientMessage(playerid, -1, "/aveh [model] [price] [color] [color]");
    if(model < 400 || model > 611) return SendClientMessage(playerid,-1, "vehicle IDs between 400 - 611!");
    if(color> 255 || color< 0) return SendClientMessage(playerid, 0xFFFFFF, "Car color ID's: 0-255");
    if(color2> 255 || color2< 0) return SendClientMessage(playerid, 0xFFFFFF, "Car color ID's: 0-255");
    GetPlayerPos(playerid, cPos[0], cPos[1], cPos[2]);
    GetPlayerFacingAngle(playerid, cPos[3]);
    CreateVehicle(model, cPos[0], cPos[1]-3, cPos[2], cPos[3], color, color2, -1);
	LoadedVehicles ++;

	new Query[208];
	VehicleInfo[LoadedVehicles][vID]++;
	format(Query, sizeof Query, "INSERT INTO vehicles (owner, ownerid, price, forsale, model, posx, posy, posz, posa) VALUES (%d, '%s', '%d', '%d', '%d', '%d','%f', '%f', '%f', '%f')", "NONE", -1, price, 1, model, cPos[0], cPos[1], cPos[2], cPos[3]);
	db_query(VehicleDB, Query);
    return 1;
}

stock LoadVehicles()
{
	new id, buf[255];

	new DBResult:Result = db_query(VehicleDB, "SELECT * FROM `vehicles`");
	VehicleInfo[LoadedVehicles][vID] = db_num_rows(Result);
	db_free_result(Result);

	if (db_num_rows(Result))
	{
		id = LoadedVehicles;
		VehicleInfo[id][vID] = db_get_field_assoc_int(Result, "id");
		VehicleInfo[id][vOwner] = db_get_field_assoc_int(Result, "owner");
    	VehicleInfo[id][vOwnerID] = db_get_field_assoc_int(Result, "ownerid");
    	VehicleInfo[id][vPrice] = db_get_field_assoc_int(Result, "price");
    	VehicleInfo[id][vForSale] = db_get_field_assoc_int(Result, "forsale");
    	VehicleInfo[id][vModel] = db_get_field_assoc_int(Result, "model");
		VehicleInfo[id][vPosX] = db_get_field_assoc_float(Result, "posx");
		VehicleInfo[id][vPosY] = db_get_field_assoc_float(Result, "posy");
     	VehicleInfo[id][vPosZ] = db_get_field_assoc_float(Result, "posz");
      	VehicleInfo[id][vPosA] = db_get_field_assoc_float(Result, "posa");

	//	sscanf(buf, "p<|>e<s[25]ddddddddfffff>", VehicleInfo[id][vOwner], VehicleInfo[id][vOwnerID], VehicleInfo[id][vForSale], VehicleInfo[id][vModel], VehicleInfo[id][vCol1], VehicleInfo[id][vCol2], VehicleInfo[id][vPrice], VehicleInfo[id][vPosX], VehicleInfo[id][vPosY], VehicleInfo[id][vPosZ], VehicleInfo[id][vPosA]);
		CreateVehicle(VehicleInfo[id][vModel],VehicleInfo[id][vPosX],VehicleInfo[id][vPosY],VehicleInfo[id][vPosZ],VehicleInfo[id][vPosA],VehicleInfo[id][vCol1], VehicleInfo[id][vCol2], 60*10000);
		format(buf, sizeof buf, "Vehicle Created model: %d Position: %f, %f, %f", VehicleInfo[id][vModel], VehicleInfo[id][vPosX], VehicleInfo[id][vPosY], VehicleInfo[id][vPosZ]);
		LoadedVehicles = LoadedVehicles+1;
		print(buf);
	}
	db_free_result(Result);
}

Re: sql error PRIMARY KEY must be unique - lackmail - 29.10.2017

i found out the issue there was an extra '%d'

Код:

format(Query, sizeof Query, "INSERT INTO vehicles (owner, ownerid, price, forsale, model, posx, posy, posz, posa) VALUES (%d, '%s', '%d', '%d', '%d', '%d','%f', '%f', '%f', '%f')", "NONE", -1, price, 1, model, cPos[0], cPos[1], cPos[2], cPos[3]);

it saves correctly but it only loads the first car:

Код:

stock LoadVehicles()
{
	new id, buf[255];

	new DBResult:Result = db_query(VehicleDB, "SELECT * FROM `vehicles`");
	VehicleInfo[LoadedVehicles][vID] = db_num_rows(Result);

	if (db_num_rows(Result))
	{
		id = LoadedVehicles;
		VehicleInfo[id][vID] = db_get_field_assoc_int(Result, "id");
		VehicleInfo[id][vOwner] = db_get_field_assoc_int(Result, "owner");
    	VehicleInfo[id][vOwnerID] = db_get_field_assoc_int(Result, "ownerid");
    	VehicleInfo[id][vPrice] = db_get_field_assoc_int(Result, "price");
    	VehicleInfo[id][vForSale] = db_get_field_assoc_int(Result, "forsale");
    	VehicleInfo[id][vModel] = db_get_field_assoc_int(Result, "model");
		VehicleInfo[id][vPosX] = db_get_field_assoc_float(Result, "posx");
		VehicleInfo[id][vPosY] = db_get_field_assoc_float(Result, "posy");
     	VehicleInfo[id][vPosZ] = db_get_field_assoc_float(Result, "posz");
      	VehicleInfo[id][vPosA] = db_get_field_assoc_float(Result, "posa");

		sscanf(buf, "p<|>e<s[25]ddddddddfffff>", VehicleInfo[id][vOwner], VehicleInfo[id][vOwnerID], VehicleInfo[id][vForSale], VehicleInfo[id][vModel], VehicleInfo[id][vCol1], VehicleInfo[id][vCol2], VehicleInfo[id][vPrice], VehicleInfo[id][vPosX], VehicleInfo[id][vPosY], VehicleInfo[id][vPosZ], VehicleInfo[id][vPosA]);
		CreateVehicle(VehicleInfo[id][vModel],VehicleInfo[id][vPosX],VehicleInfo[id][vPosY],VehicleInfo[id][vPosZ],VehicleInfo[id][vPosA],VehicleInfo[id][vCol1], VehicleInfo[id][vCol2], 60*10000);
		format(buf, sizeof buf, "Vehicle Created model: %d Position: %f, %f, %f", VehicleInfo[id][vModel], VehicleInfo[id][vPosX], VehicleInfo[id][vPosY], VehicleInfo[id][vPosZ]);
		LoadedVehicles = LoadedVehicles+1;
		print(buf);
	}
	db_free_result(Result);
}

EDIT

I have figured it out this is what i have done and it works thanks jeff for the help

Код:

stock LoadVehicles()
{
	new buf[255];

	new DBResult:Result = db_query(VehicleDB, "SELECT * FROM `vehicles`");
	new rows = db_num_rows(Result);
	if(!rows) return print("No Saved Vehicles!");
	for(new i; i < rows; i++)
	{
		VehicleInfo[i][vID] = db_get_field_assoc_int(Result, "id");
		VehicleInfo[i][vOwner] = db_get_field_assoc_int(Result, "owner");
    	VehicleInfo[i][vOwnerID] = db_get_field_assoc_int(Result, "ownerid");
    	VehicleInfo[i][vPrice] = db_get_field_assoc_int(Result, "price");
    	VehicleInfo[i][vForSale] = db_get_field_assoc_int(Result, "forsale");
    	VehicleInfo[i][vModel] = db_get_field_assoc_int(Result, "model");
		VehicleInfo[i][vPosX] = db_get_field_assoc_float(Result, "posx");
		VehicleInfo[i][vPosY] = db_get_field_assoc_float(Result, "posy");
     	VehicleInfo[i][vPosZ] = db_get_field_assoc_float(Result, "posz");
      	VehicleInfo[i][vPosA] = db_get_field_assoc_float(Result, "posa");

		CreateVehicle(VehicleInfo[i][vModel],VehicleInfo[i][vPosX],VehicleInfo[i][vPosY],VehicleInfo[i][vPosZ],VehicleInfo[i][vPosA],VehicleInfo[i][vCol1], VehicleInfo[i][vCol2], 60*10000);
		format(buf, sizeof buf, "Vehicle Created model: %d Position: %f, %f, %f", VehicleInfo[i][vModel], VehicleInfo[i][vPosX], VehicleInfo[i][vPosY], VehicleInfo[i][vPosZ]);
		LoadedVehicles++;
		print(buf);
  		db_next_row(Result);
	}
	db_free_result(Result);
	return 1;
}