Sql exec ERROR:: UPDATE char:tiburoncin SQLSTATE:22001, [Microsoft][ODBC SQL server driver][SQL server]string or binary data would be truncated.

Question

0.00/5 (No votes)

See more:

C++

<pre>bool GS_CThreadSQL::UpdateUserRECORD (classUSER *pUSER)
{
	// update character DB !!!
	// "UPDATE tblGS_AVATAR SET nZoneNO=xxx, binBasicI=xx, binBasicA=xx WHERE txtNAME=xxx;"
	m_sBE.m_btCharRACE = (BYTE)pUSER->m_nCharRACE;

	if ( pUSER->Get_HP() <= 0 ) {
		// Á¸¹øÈ£°¡ ¾øÀ¸¸é ???
		// Á×¾úÀ¸¸é ÀúÀåµÈ ºÎÈ°Àå¼Ò¿¡¼ ...
		if ( pUSER->m_nZoneNO == BEGINNER_ZONE_NO ) {
			m_sBE.m_nZoneNO   = BEGINNER_ZONE_NO;
			m_sBE.m_PosSTART  = pUSER->m_PosCUR;
		} else {
			m_sBE.m_nZoneNO	  = pUSER->m_nReviveZoneNO;
			m_sBE.m_PosSTART  = pUSER->m_PosREVIVE;
		}
	} else {
		m_sBE.m_nZoneNO   = pUSER->m_nZoneNO;
		m_sBE.m_PosSTART  = pUSER->m_PosCUR;
	}

	// ºÎÈ° Àå¼Ò...
	m_sBE.m_nReviveZoneNO = pUSER->m_nReviveZoneNO;
	m_sBE.m_PosREVIVE	  = pUSER->m_PosREVIVE;
	m_sBE.m_btCharSlotNO  = pUSER->m_btPlatinumCHAR;

	if ( m_sBE.m_nZoneNO < 0 || m_sBE.m_nReviveZoneNO < 0 ) {
		g_LOG.CS_ODS( 0xffff, "**** Invalid ZoneNO [ %s ] Race: %d, Zone: %d ReviveZone: %d \n",
				pUSER->Get_NAME(),
				m_sBE.m_btCharRACE,
				m_sBE.m_nZoneNO, 
				m_sBE.m_nReviveZoneNO );
	}

	::CopyMemory( m_sBE.m_PartITEM, pUSER->m_PartITEM,	sizeof(tagPartITEM)*MAX_BODY_PART);
	::CopyMemory( m_sBE.m_RideITEM, pUSER->m_RideITEM,	sizeof(tagPartITEM)*MAX_RIDING_PART);

	pUSER->m_BasicINFO.m_cFaceIDX = (char)pUSER->m_PartITEM[ BODY_PART_FACE ].m_nItemNo;
	pUSER->m_BasicINFO.m_cHairIDX = (char)pUSER->m_PartITEM[ BODY_PART_HAIR ].m_nItemNo;

	this->m_pSQL->BindPARAM( 1, (BYTE*)&this->m_sBE,			sizeof( tagBasicETC )		);
	this->m_pSQL->BindPARAM( 2, (BYTE*)&pUSER->m_BasicINFO,		sizeof( tagBasicINFO )		);
	this->m_pSQL->BindPARAM( 3, (BYTE*)&pUSER->m_BasicAbility,	sizeof( tagBasicAbility )	);
	this->m_pSQL->BindPARAM( 4, (BYTE*)&pUSER->m_GrowAbility,	sizeof( tagGrowAbility )	);
	this->m_pSQL->BindPARAM( 5, (BYTE*)&pUSER->m_Skills,		sizeof( tagSkillAbility )	);
	this->m_pSQL->BindPARAM( 6, (BYTE*)&pUSER->m_Inventory,		sizeof( CInventory )		);
	this->m_pSQL->BindPARAM( 7, (BYTE*)&pUSER->m_Quests,		sizeof( tagQuestData )		);
	this->m_pSQL->BindPARAM( 8, (BYTE*)&pUSER->m_HotICONS,		sizeof( CHotICONS )			);
	this->m_pSQL->BindPARAM( 9, (BYTE*)&pUSER->m_WishLIST,		sizeof( tagWishLIST )		);

#ifdef __KCHS_BATTLECART__  // MQ_PARAM_INT16,		DATA_VER_2,
	this->m_pSQL->MakeQuery( "UPDATE tblGS_AVATAR SET binBasicE=",
												MQ_PARAM_BINDIDX,	1,
			MQ_PARAM_ADDSTR, ",binBasicI=",		MQ_PARAM_BINDIDX,	2,
			MQ_PARAM_ADDSTR, ",binBasicA=",		MQ_PARAM_BINDIDX,	3,
			MQ_PARAM_ADDSTR, ",binGrowA=",		MQ_PARAM_BINDIDX,	4,
			MQ_PARAM_ADDSTR, ",binSkillA=",		MQ_PARAM_BINDIDX,	5,
			MQ_PARAM_ADDSTR, ",blobINV=",		MQ_PARAM_BINDIDX,	6,
			MQ_PARAM_ADDSTR, ",blobQUEST=",		MQ_PARAM_BINDIDX,	7,
			MQ_PARAM_ADDSTR, ",binHotICON=",	MQ_PARAM_BINDIDX,	8,
			MQ_PARAM_ADDSTR, ",binWishLIST=",	MQ_PARAM_BINDIDX,	9,
			MQ_PARAM_ADDSTR, ",btLEVEL=",		MQ_PARAM_INT,		pUSER->m_GrowAbility.m_nLevel,
			MQ_PARAM_ADDSTR, ",intMoney=",		MQ_PARAM_INT64,		pUSER->GetCur_MONEY(),
			MQ_PARAM_ADDSTR, ",intJOB=",		MQ_PARAM_INT,		pUSER->m_BasicINFO.m_nClass,
			MQ_PARAM_ADDSTR, ",dwRegTIME=",		MQ_PARAM_INT,		this->m_dwCurTIME,
			MQ_PARAM_ADDSTR, ",dwPartyIDX=",	MQ_PARAM_INT,		pUSER->GetPARTY() ? pUSER->m_pPartyBUFF->m_wPartyWSID : 0,
			MQ_PARAM_ADDSTR, ",dwItemSN=",		MQ_PARAM_INT,		pUSER->m_dwItemSN,
			MQ_PARAM_ADDSTR, ",intDataVER=",	MQ_PARAM_INT16,		DATA_VER_2,
			MQ_PARAM_ADDSTR, "WHERE txtNAME=",	MQ_PARAM_STR,		pUSER->Get_NAME(),
												MQ_PARAM_END );
#else
	this->m_pSQL->MakeQuery( "UPDATE tblGS_AVATAR SET binBasicE=",
												MQ_PARAM_BINDIDX,	1,
			MQ_PARAM_ADDSTR, ",binBasicI=",		MQ_PARAM_BINDIDX,	2,
			MQ_PARAM_ADDSTR, ",binBasicA=",		MQ_PARAM_BINDIDX,	3,
			MQ_PARAM_ADDSTR, ",binGrowA=",		MQ_PARAM_BINDIDX,	4,
			MQ_PARAM_ADDSTR, ",binSkillA=",		MQ_PARAM_BINDIDX,	5,
			MQ_PARAM_ADDSTR, ",blobINV=",		MQ_PARAM_BINDIDX,	6,
			MQ_PARAM_ADDSTR, ",blobQUEST=",		MQ_PARAM_BINDIDX,	7,
			MQ_PARAM_ADDSTR, ",binHotICON=",	MQ_PARAM_BINDIDX,	8,
			MQ_PARAM_ADDSTR, ",binWishLIST=",	MQ_PARAM_BINDIDX,	9,
			MQ_PARAM_ADDSTR, ",btLEVEL=",		MQ_PARAM_INT,		pUSER->m_GrowAbility.m_nLevel,
			MQ_PARAM_ADDSTR, ",intMoney=",		MQ_PARAM_INT64,		pUSER->GetCur_MONEY(),
			MQ_PARAM_ADDSTR, ",intJOB=",		MQ_PARAM_INT,		pUSER->m_BasicINFO.m_nClass,
			MQ_PARAM_ADDSTR, ",dwRegTIME=",		MQ_PARAM_INT,		this->m_dwCurTIME,
			MQ_PARAM_ADDSTR, ",dwPartyIDX=",	MQ_PARAM_INT,		pUSER->GetPARTY() ? pUSER->m_pPartyBUFF->m_wPartyWSID : 0,
			MQ_PARAM_ADDSTR, ",dwItemSN=",		MQ_PARAM_INT,		pUSER->m_dwItemSN,
			MQ_PARAM_ADDSTR, "WHERE txtNAME=",	MQ_PARAM_STR,		pUSER->Get_RNAME(),
												MQ_PARAM_END );
#endif

	if ( this->m_pSQL->ExecSQLBuffer() < 0 ) {
		// °íÄ¡±â ½ÇÆÐ !!!
		// log ...
		g_LOG.CS_ODS(LOG_NORMAL, "SQL Exec ERROR:: UPDATE Char:%s %s \n", pUSER->Get_NAME(), m_pSQL->GetERROR() );
	} else {
		// ÄÉ¸¯ ¹é¾÷µÈ ½Ã°£ ·Î±× ³²±â±â..
		g_pThreadLOG->When_BackUP( pUSER, "CHAR" );
	}

	if ( BANK_CHANGED != pUSER->m_btBankData )
		return true;
	
	// Ã¢°í(ÀºÇà) µ¥ÀÌÅ¸ ±â·Ï...
	this->m_pSQL->BindPARAM( 1, (BYTE*)&pUSER->m_Bank,		sizeof( tagBankData )		);
	this->m_pSQL->MakeQuery( "UPDATE tblGS_BANK SET blobITEMS=",
													MQ_PARAM_BINDIDX,	1,
			MQ_PARAM_ADDSTR, "WHERE txtACCOUNT=",	MQ_PARAM_STR,	pUSER->Get_ACCOUNT(),
													MQ_PARAM_END );
	if ( this->m_pSQL->ExecSQLBuffer() < 0 ) {
		// °íÄ¡±â ½ÇÆÐ !!!
		// log ...
		g_LOG.CS_ODS(LOG_NORMAL, "SQL Exec ERROR:: UPDATE Bank:%s %s \n", pUSER->Get_ACCOUNT(), m_pSQL->GetERROR() );
	} else {
		pUSER->m_btBankData = BANK_LOADED;
		// Ã¢°í ¹é¾÷µÈ ½Ã°£ ·Î±× ³²±â±â..
		g_pThreadLOG->When_BackUP( pUSER, "BANK" );
	}

	return true;
}

SQL DATABASE MSSQL 2008 r2

SQL

intCharID	int	Unchecked
txtACCOUNT	nvarchar(20)	Unchecked
txtNAME	nvarchar(30)	Unchecked
btLEVEL	int	Checked
intMoney	bigint	Checked
dwRIGHT	int	Checked
binBasicE	binary(96)	Checked
binBasicI	binary(32)	Unchecked
binBasicA	binary(48)	Unchecked
binGrowA	binary(384)	Unchecked
binSkillA	binary(384)	Unchecked
blobQUEST	binary(1024)	Checked
blobINV	binary(2048)	Unchecked
binHotICON	binary(96)	Checked
dwDelTIME	int	Checked
binWishLIST	binary(256)	Checked
dwOPTION	int	Checked
intJOB	smallint	Unchecked
dwRegTIME	int	Unchecked
dwPartyIDX	int	Checked
dwItemSN	int	Checked
intDataVER	smallint	Unchecked
txtCharName	nvarchar(30)	Checked
binSkillB	binary(240)	Checked
		Unchecked

What I have tried:

i been trying to figure why i keep getting problem for days and can't seem to figure it out any one able to help on this please and thank you

Posted 23-Aug-20 14:39pm

roseon1

Updated 23-Aug-20 20:13pm

Add a Solution

Comments

Garth J Lancaster 23-Aug-20 22:00pm

iirc, this problem usually occurs, because you have a database table field defined as for example nvarchar(30) eg your txtCharName, but you have a c++ string that is larger than 30 characters - let's say 35 just as an example. So you're getting an error that "Loremipsumdolorsitamet,consectetur" is going to be truncated leaving '...consect' on the end but missing the full 'consectetur'

Does that make sense ?

You're using an 'odd' ie different database layer than I'm used to, so, suggestions for fixing I can only give rough thoughts .. make sure your update parameter sizes match the table field sizes for binary/blobs & string (nvarchar) types .. and/or increase the nvarchar types

roseon1 23-Aug-20 23:57pm

thank that help and now i having trouble getting this i been trying to extract the SQL query error to this but i cant figure the proper setting

SQLSTATE:42000, Diagnostic information:[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to bigint.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-08-23T20:13:00

The first error is because the string doesn't fit in the column, and SQL doesn't like to "thrown away data" unless told to with a DELETE command. We can't fix that: either validate your data in your presentation language so it fits, or enlarge the column and validate your data in future.

The second is because you are passing a string based value to a numeric column, and SQL - rightly - says "that's not a number" and refuses to deal with it.

The solution to both problems is the same: validate your data in your presentation language (uses make mistakes!) and pass the validated and converted values to SQL instead of passing everything as a string and hoping that SQL will sort it out. One big advantage of this is that dates get parsed using the users preferred date format into a DateTime value instead of relying on SQL to try and work out what day, month and year 01-02-03 is supposed to represent (and getting it wrong half the time). Remember, SQL has no access to your user and his settings, so if you have a US user, a European user, and a Japanese user typing 01-02-03 in the date box, they mean three different things: 2nd Jan 2003, 1st Feb 2003, and 3rd of Feb 2001. Once that hits the DB, it's very difficult to work out what date the user intended to enter!