Conversion failed when converting the varchar value error

Question

I am keep getting this error after added inner join

SELECT 
   character.dbo.user_character.character_name,
   character.dbo.user_character.wLevel,
   character.dbo.user_character.byPCClass,
   character.dbo.user_character.wMapIndex,
   character.dbo.user_character.wPosX,
   character.dbo.user_character.wPosY,
   ban_info.dbo.account_login.ip
FROM
   character.dbo.user_character
INNER JOIN ban_info.dbo.account_login ON (character.dbo.user_character.wPosY=ban_info.dbo.account_login.ip)
WHERE (character.dbo.user_character.user_no='12100601280065')

Error

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '127.0.0.1' to data type int.

How can I FIX IT>?

score 0 · Answer 1 · answered Dec 12 '13 at 08:35

0

Take a look at this; http://daipratt.co.uk/mysql-store-ip-address/. . It requires some tweaking to store an IP Address.

answered Dec 12 '13 at 08:35

MichaelP

181
5
22

I am using mssql, not mysql – user2875727 Dec 12 '13 at 08:35
Simple searching will get you very far.... [click here](http://stackoverflow.com/questions/6690101/storing-ip-addresses-in-microsoft-sql-server) – MichaelP Dec 12 '13 at 08:37

score 0 · Answer 2 · answered Dec 12 '13 at 08:37

0

Your problem is just comparing an Ip address (that is not an integer) stored on ban_info.dbo.account_login.ip by integer value stored on character.dbo.user_character.wPosY in following line :

INNER JOIN ban_info.dbo.account_login ON (character.dbo.user_character.wPosY = ban_info.dbo.account_login.ip)

answered Dec 12 '13 at 08:37

Mostafa Armandi

879
1
11
24

Okay, I changed it, and seems it fixed the problem, I changed the wPosY to varchar and it's work now, no errors, but it gives me now blank row – user2875727 Dec 12 '13 at 08:43
@user2875727 Because you are comparing two mismatched values for example 52 is not equal by 192.168.0.12 . – Mostafa Armandi Dec 12 '13 at 08:48
ah, got it, so I tried to do compare between two same lines like ban_info.dbo.account_login.ip=ban_info.dbo.account_login.ip and now for all the rows it show me the first ip that in the columns – user2875727 Dec 12 '13 at 08:54

score 0 · Answer 3 · answered Dec 12 '13 at 08:38

0

I think you should convert IP address to int in php (or db) and then compare it. There is function ip2long() in PHP

answered Dec 12 '13 at 08:38

merkushin

481
9
17

Conversion failed when converting the varchar value error

3 Answers3