0

I have a column RES_LOCALIZADOR which is a numeric string.. it should be a bigint though.

When executing this query, although it doesnt fail, it doesnt return any value when it should. I have added CAST fucntion to prevent an error I had before (The conversion of the varchar value overflowed an int column in sql server)

            var str = new StringBuilder();
        str.AppendLine(" SELECT WEB_codigo ");
        str.AppendLine(" FROM[intranetMinorista].[dbo].[RESERVA] R WITH(NOLOCK) ");
        long value = 206627660;
        str.AppendFormat(" WHERE CAST(RES_LOCALIZADOR AS BIGINT)=" +value);

Any help?

Thanks

Nacho Martin Martin
  • 1
  • 4
    You should use parameterization. See [here](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements). It not only protects against sql injection, it also handles datatypes better. Did you try to execute the query in SSMS? – HoneyBadger Jan 26 '18 at 08:03
  • Even though parameters are good and all I doubt they will improve the situation in this specific case... `long.ToString()` is not the typical producer of injection code. I would also be interested in the SSMS result. I'm not using SQL server to much... is it legal to have `FROM[intranetMinorista]` without a space after `FROM`? – grek40 Jan 26 '18 at 08:10
  • What happens if you select the data a "select web_codigo, CAST(RES_LOCALIZADOR AS BIGINT) from [intranetMinorista].[dbo].[RESERVA]" do you see the values you epect? – BugFinder Jan 26 '18 at 08:21
  • Thanks for your answer, I have changed to use parametrizacion but still doesnt work. The SSMS result is ok, it returns the value I expect. – Nacho Martin Martin Jan 26 '18 at 08:24
  • @BugFinder by doing so, it works, it returns good results, it looks like the problem is with values in the WHERE cluase. In my case, the problem is with a VARCHAR that contains numeric values. Also I am havving same problem with BIGINT. IT works fine with int column. – Nacho Martin Martin Jan 26 '18 at 08:32
  • why would you store numeric values that you wish to use in a string field? – BugFinder Jan 26 '18 at 08:54
  • Can you have a look in the profiler to check the actual query send to the database? – HoneyBadger Jan 26 '18 at 09:14
  • Off-topic; [no lock](https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/) can return unpredictable results. However, doubt that is the issue here. – David Rushton Jan 26 '18 at 09:49
  • @BugFinder thanks, can you please let me know where is the profiler? i am new with Visual Studio – Nacho Martin Martin Jan 26 '18 at 10:54
  • I assume that last comment was directed at me. I don't know where or if Visual Studio has a database profiler, but if you are in SQL Server Management Studio you'll find it under the "Tools" menu. It lists all queries which are fired at the database. – HoneyBadger Jan 26 '18 at 12:03
  • Can you please include the exact query text that is working inside SSMS into your question? – grek40 Jan 26 '18 at 12:10
  • 1
    @grek40 Hi Greg, I have just placed the queries I am having problems with. They work on SSMS but not when executing from the code – Nacho Martin Martin Jan 26 '18 at 12:58
  • Please edit the new information into the question and remove the answer. What was the error that you got without the `CAST`? You should ONYL EVER compare the same query in Code and SSMS. Your current question query is different, which makes all comparison useless. – grek40 Jan 26 '18 at 16:30
  • Thanks everyone for the answers.. It looks like there is a problem with that table.. the clien I am working for have a bit of chaos. I have tried in other tables and work fine. Not sure what can affect that particular table..Anyway, appreciate evyerone looking at this – Nacho Martin Martin Jan 26 '18 at 17:57

1 Answers1

-1

SELECT WEB_codigo FROM [intranetMinorista].[dbo].[RESERVA] WHERE RES_localizador = 206627660

RES_localizador IS A VARCHAR IN BDDD

Also I am having problems when the column is of type BIGINT SELECT WEB_codigo FROM [intranetMinorista].[dbo].[RESERVA] WHERE RES_CODIGO = 20946346

Res_codigo is a bigint in BBDD

Nacho Martin Martin
  • 1
  • Please edit your question to add information, new information shouldn't be a comment, even less an answer. What is BDDD/BBDD? – HoneyBadger Jan 26 '18 at 12:59