Avatar of ube100
ube100Flag for United Kingdom of Great Britain and Northern Ireland asked on

Syntax error converting the varchar value 'NULL' to a column of data type int.

Hi,

I'm updating a table in sql server from a front end written in C++. This some thing like this:

                                                  sprintf(sSqlQuery,
                  "UPDATE %s set "
                  "date_rec = '%s',"
                  "tpc = '%s',"
                  "cpc = '%s',"
                  "acquisition_strategy_code = '%s',"
                  "title = %d, "
                  "first_name = '%s',"
                  "middle_initials = '%s'"
                                                   " WHERE Id = %ld ",
                  CARDAPPLICATIONS_TABLE,
                  lDateRec,
                  tpc,
                  cpc,
                  acquisition_strategy_code,
                  title,
                  first_name,
                  middle_initials,
                  lRecordId
                  );      
When I execute this statement I get's the following error:  Syntax error converting the varchar value 'NULL' to a column of data type int.
Say for the title I got null passed in but in the table that is defined as integer so database throwing the above error. This is only a part of the sql string but I got really hughe sql string so I want to  convert all those values with 'NULL' to NULL then the database will accept this as a NULL keyword rather than as NULL string.
Can smoe one advice me please.

ube100
C++Microsoft SQL Server

Avatar of undefined
Last Comment
Infinity08

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
brad2575

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Infinity08

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ube100

Infinity08,

Title in the database and the code are type int. My problem is if the value come out as null for the title when its get pass to the db as 'null' then update will fail.....
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ube100

These values are I'm retreiving from a staging table and then inserting them into a live table. Also this error refering to the country code which is also a interger filed in the code as well as database. When I receive a code for the country from the staging tbl I will pass that to following funtion:

int      GetCountryCode(char *sAlnovaCode)
{
      TRACESUB(1, "GetCountryCode()");

      COUNTRY      *pLocalCountryCodes;
      int                  i;
      int                  iCode;

      pLocalCountryCodes = pCountries;

      if (strlen(sAlnovaCode) == 0)
      {
            TRACE(7, "Country Code is blank so return 0");
            iCode = NULL;
            return iCode;
      }

      iCode = NULL;

      for (i = 0; i < giCountries; i++)
      {
            if (strcmp(pLocalCountryCodes[i].sAlnovaCountryCode, sAlnovaCode) == 0)
            {
                  iCode = pLocalCountryCodes[i].iDBCountryCode;
                  sprintf(gsBuffer, "Found DB CountryCode %d for Alnova CountryCode %s", iCode, sAlnovaCode);
                  TRACE(9, gsBuffer);
                  break;
            }
      }
      
      return iCode;
}
This funtion will return a null value if the country code is zero so I will have to deal with null value regardless whether country code is int ot not...
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Infinity08

May I ask why you gave a C grade ? That means that the answers given were really bad. If that's the case, then why didn't you ask for clarification ? It took you a month to close this question.

If that's not the case, then I'd appreciate if you would change the grade.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes