Error converting data type varchar to bigint.

Question

1.00/5 (1 vote)

See more:

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

What I have tried:

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

Posted 20-Jun-18 21:10pm

nar86

Updated 26-Apr-19 9:21am

Add a Solution

Comments

Richard MacCutchan 21-Jun-18 3:28am

Why are you using VARCHAR(50) for a numeric value? You need to make both fields the same type so they can be compared.

Richard MacCutchan 21-Jun-18 3:29am

I repeat my earlier suggestion: Get some programming books and learn the basics.

3 solutions

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 · 2018-06-20T21:48:00

IT's pretty simple: You are storing data in the wrong format.
When you use VARCHAR or NVARCHAR instead of a numeric or date based column, you are almost guaranteed that at some point someone will enter bad data - and it won;t get spotted until it's actually used, by which time it's far too late to work out what it should be and correct it.
And it normally shows up as a conversion error when you try to use the data, just as it does in your table.

Always store data in t6eh most appropriate type, or you will continue to have this problem.

Change your DB, convert the values that can be converted, and correct the ones that can't.

Member 7870345 · Answer 2 · 2018-06-20T21:55:00

In my opinion the error ocurrs because there are records in the farmerregistration table that conteins 'no number' in the farmerid field (for example: A12334, 213 F... or something like this).

So, the where a.transactionid = b.farmerid treats to convert this 'no numbers' values to a bigint and causes an error.

Try to use:

SQL

where ltrim(rtrim(a.transactionid))=ltrim(rtrim(convert( varchar(50), b.farmerid)))

(Explanation, instead of implicit convert varchar to bigint, convert explicitly bigint to varchar and then compare the alltrim [ltrim, rtrim] of that values)

Member 14335040 · Answer 3 · 2019-04-26T09:21:00

I'm not sure if this will help, as I'm very very new to this. I was just attempting to fix what appears to be a similar issue. I created a search query and was getting the same error.

I went into my Database's Design page, changed to design for that field from BigInt to nvarchar(50).

Then I went into the original Query, right clicked on it, and clicked Configure. I had to re-click through everything, but when I clicked on Finish and the Query updated, my program began to work.

Best of luck. I apologize if this is total jibberish.. just a complete newbie here.

Error converting data type varchar to bigint.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0