使用vb.net，字符串sql格式不正确会导致插入失败? [英] String sql is not in correct format insertion fails using vb.net?How
问题描述
我是vb.net的新手，我具有sqlcommand字符串，如图所示
I am new in vb.net i have sqlcommand string as shown
Dim query As [String] = "insert into tblDefProducts( Creation_Date, Product_code,line_item_id , item_name , category_id , subcategory_id , Gender ,LifeType, supplier_id , Acquire_type , Purchase_type , Manufacture_type , Pur_Con_Unit, Pur_Con_factor ,Tax_At_Retail_Price, Sale_Tax ,Average_cost ,Product_group_id,status,Technical_details )
values( " & DateTimePicker2.Value.ToString() & ",'" & PCode_TXT.Text & "','" & LineItem_TXT.Text & "' , '" & PName_TXT.Text & "','" & CategoryM_CMB.Text & "','" & CategoryS_CMB.Text & "','" & ProductGender_TXT.Text & "', '" & ProductLifeT_CMD.Text & "','" & ProductSupplier_CMB.Text & "','" & PAquireType_CMB.Text & "', '" & PPurchaseType_CMB.Text & "','" & PManufacturing_CMB.Text & "','" & PConnUnit_CMB.Text & "' ," & Convert.ToInt32(PConFactory_TXT.Text) & "," & Convert.ToInt32(PSalesTaxPurchase_TXT.Text) & "," & Convert.ToInt32(PSalesTaxSales_TXT.Text) & "," & Convert.ToInt32(PPRICE_TXT.Text) & " , " & PTypeA & " , " & pActive & ",'" & PTechnicalDetail_TXT.Text & "')"
2个问题
输入字符串的格式不正确，第二次从datetimepicker1插入datetime值到数据库列creation_date的数据类型为DateTime，但datetimepicker给出值#1/1/1900#还必须使用sql进行解析
Input string is not in a correct format second insertion of datetime value from datetimepicker1 to database column creation_date have datatype DateTime but datetimepicker gives value #1/1/1900# also have to parse using sql
推荐答案
使用字符串替换来传递参数并不是一个好主意，原因有很多:它容易受到SQL Injection攻击的影响，更容易出错并且更难以阅读和理解.保持.
Using string contaternation to pass parameters is not a good idea for a number of reasons: Its vulnerable to SQL Injection attacks, more prone to errors and harder to read and maintain.
在您的问题中，问题很可能是由于某些值周围缺少".
In you question the problem is likly due to missing '' around some of your values.
想象一下，如果您只希望用户能够更新表中的某些字段，但是用户在文本框中输入了诸如"Hello"，ReadOnlyField ='World之类的内容，然后将其合并到查询中.ReadOnlyField世界将被更新.使用参数可以避免这种情况.
Imagine if you only wanted the user to be able to update certain fields in your table but the user typed something like "Hello', ReadOnlyField = 'World" into a text box which you then concaternate into your query. The ReadOnlyField world be updated. With parameters this would be prevented.
如果使用参数代替，则不必担心"，因为参数是类型变量而不是字符串.
If you use parameters instead you don't need to wory about '' as parameters are typed variables and not strings.
您也不需要将所有内容都转换为字符串，因为大多数.NET primitves具有SQL等效项.
You also don't need to convert everything to a string as most .NET primitves has SQL equivalents.
有关VB.Net SqlCommand.Parameters的示例和文档，请参见此处
See here for examples and documentation on VB.Net SqlCommand.Parameters
这篇关于使用vb.net，字符串sql格式不正确会导致插入失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
