将数据类型varchar转换为bigint时出错。 [英] Error converting data type varchar to bigint.
问题描述
农民登记表如下
农民登记表中的农民数据类型Varchar（50）
farmerid名字区域区域村庄
1055662 Lacina OUNGAlo Diawala Nord Diwala
交易表如下
transactionid数据类型交易表中的Bigint
transactionid数量价格支付到期
1055662 1 200 200 100
从上面我想输出如下
名字区域区域数量价格支付
Lacina OUNGALo Diawala Nord 1 200 200
我的查询如下
选择一个.firstname，a.Region，a.Zone，a.Section，b.Qty，b.Price，b.paid
来自farmerregistration a，
transac b。其中a.transactionid = b.farmerid
注意在farmerregistration表中的farmerid数据类型是varchar（50）
在事务表transactionid数据类型中是bigint
当我运行上面的代码时显示错误如下
将数据类型varchar转换为bigint。
如何解决这个错误。从我上面的查询中我需要做些什么改变。
我尝试过：
农民登记表如下
农民登记表中的农民数据类型Varchar（50）
农民名字区域区村庄
1055662 Lacina OUNGAlo Diawala Nord Diwala
交易表如下
transactionid数据类型交易表中的Bigint
transactionid数量价格支付到期
1055662 1 200 200 100
$ b上面的$ b我希望输出如下
名字区域区段数量价格支付
Lacina OUNGALo Diawala Nord 1 200 200
我的查询如下
选择 a.firstname，a.Region，a.Zone，a.Section，b.Qty，b.Price，b.paid
来自农民注册a，
交易b其中a.transactionid = b.farmerid
注意在farmerregistration表中，farmerid数据类型是varchar（50）
在事务表中，transactionid数据类型是bigint
当我运行上面的代码时显示如下错误
将数据类型varchar转换为bigint时出错。 br />
如何解决这个错误。从我上面的查询我必须做出哪些更改。
IT很简单：你以错误的格式存储数据。
当你使用VARCHAR或NVARCHAR而不是数字或基于日期的列，你几乎可以保证在某些时候有人会输入不良数据 - 它会赢;在实际使用之前就会被发现，到时为止已经太晚了它应该是什么并且纠正它。
当你尝试使用数据时它通常会显示为转换错误，就像在你的表中一样。
始终以t6eh最合适的类型存储数据，否则你将继续遇到这个问题。
更改数据库，转换可以使用的值转换，并纠正那些不能的。
在我看来，错误是错误的，因为在农民注册表中有记录在农民字段中表示无数字（例如：A12334， 213 F ......或类似的东西）。
所以，其中a.transactionid = b.farmerid将此'无数字'值转换为bigint并导致错误。
尝试使用：
其中 ltrim（rtrim（a.transactionid））= ltrim（rtrim（ convert （< span class =code-keyword> varchar （ 50 ），b.farmerid）））
（解释，而不是隐式转换varchar到bigint，将显式bigint转换为varchar，然后比较该值的alltrim [ltrim，rtrim]）
< blockquote>我不确定这是否会有所帮助，因为我对此非常陌生。我只是试图修复看似类似的问题。我创建了一个搜索查询，并得到了同样的错误。
我进入了我的数据库设计页面，更改为从BigInt到nvarchar（50）的该字段设计。
然后我进入原始查询，右键单击它，然后单击配置。我不得不重新点击一切，但当我点击完成并更新查询时，我的程序开始工作。
好运。如果这完全是胡言乱语，我道歉...这里只是一个完整的新手。
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.
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.
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, thewhere a.transactionid = b.farmeridtreats to convert this 'no numbers' values to a bigint and causes an error.
Try to use:
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)
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.
这篇关于将数据类型varchar转换为bigint时出错。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
