Question

I am attempting an INSERT statement with a nested SELECT statement. Both of the database fields are of int type.

My statement:

energy_command = New SqlCommand("INSERT INTO energy ([ApplicationID], [usage], [policy], [audit], [lighting], [over_lighting], [controls], [turn_off], [energy_star], [weatherize],[thermostat], [natural_light], [air], [renewable_assessment], [on_site_renewable], [HVAC],[renewable_power], [efficient_enroll], [efficient_attain], [other]) " & 
"VALUES ( (SELECT ApplicationID FROM general where Business_Name = """ & txtName.Text & """) ,  " & track_usage & " ,  " & develop_strategy & " ,  " & perform_audit & " ,  " & replace_bulbs & " ,  " & reduce_lighting & " ,  " & lighting_controls & " ,  " & not_in_use_policy & " ,  " & energy_star & " ,  " & weatherize & " ,  " & program_thermo & " ,  " & natural_light & " ,  " & air_quality & " ,  " & site_assessment & " ,  " & renewable_power & " ,  " & HVAC & " ,  " & renewable_energy & " ,  " & energy_programs & " ,  " & energy_certification & " ,  " & energy_other & " )", connection)`

My error:

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value (SELECT ApplicationID FROM general where Business_Name = "a") to data type int.

我的唯一想法是,它试图将整个<代码>SlectT的说明插入INT field。我错了吗? 我如何确定这一点?

Answer 1

由于你只引述了询问,因此,这被视为一种扼杀。仅此,你就应当删除你所提问题的单一文字。

VALUES ((SELECT ApplicationID FROM general where Business_Name = " " & txtName.Text & " "), ....

Answer 2

Just remove the apostrophes around the query.

变化:

 (SELECT ApplicationID FROM general where Business_Name = """ & txtName.Text & """)

:

(SELECT ApplicationID FROM general where Business_Name = """ & txtName.Text & """)

Answer 3

另外,如果你希望把数据列入另一个表格的<代码>SlectT的表格,则需要not使用VALUES关键词,但使用这一风格:

INSERT INTO dbo.YourTargetTable(Co1, Col2, ..., ColN)
   SELECT
      Src1, Src2, ...., SrcN
   FROM  
      dbo.YourSourceTableHere
   WHERE
      YourConditionHere

不需要<代码>VALUES。

友情链接