无法更新数据库 [英] can not Update database

问题描述

当点击更新命令按钮时，错误是



从字符串转换UPDATE语句中的语法错误输入'Integer'无效。

 BtnSave.Enabled =  True  
 BtnAdd.Enabled =  True  
 BtnUpdate.Enabled =  True  
 BtnDelete .Enabled =  True  
 BtnPrevious.Enabled =  True  
 BtnNext.Enabled = < span class =code-keyword> True  
 BtnFirst.Enabled =  True  
 BtnLast.Enabled =  True  
 
 尝试 
 
  Dim  cmd 作为 新 OleDb.OleDbCommand（）
 con.ConnectionString =   Provider = Microsoft.Jet.OLEDB.4.0; Data Source = E：\ VB Project \\ \\ Tansportation \Transport2003.mdb 
 
 
 
 con.Open（）
 
 
 cmd.Connection = con 
 
 cmd.CommandText =   UPDATE TblBillStatmentEntry SET Date ='& TxtDate.Text&  '，FreightSlipNo ='& TxtFreightSlipNo.Text&  '，FromID ='& ComboBox1.Text&  '，To ='& TxtTo.Text&  '，RatesPerTon ='& TxtRatePerTon.Text&  '，RatesPerTrip ='& TxtRatePerTrip.Text&  '，WorkEffectiveDate ='& TxtWEF.Text&  '，MaterialID ='& ComboBox2.Text&  '，TruckNoID ='& ComboBox3.Text&  '，TonCategory ='& TxtTon.Text&  '，ConditionListID ='& ComboBox4.Text&  '，Weight ='& TxtExtra.Text&  '，Trips ='& TxtTrips.Text&  '，ActualWeight ='& TxtActualWeight.Text&  '，BillWeight ='& TxtBillWeight.Text&  '，Amount ='& TxtAmount.Text&  'WHERE ID ='& TxtID.Text&  ' 
 
 
 
 cmd.ExecuteNonQuery （）
 如果 con.State = ConnectionState.Open 那么 
 con.Close （）
 结束 如果 
 
 MsgBox（ 数据已更新）
  Catch  ex < span class =code-keyword> As 异常
 MsgBox（ 无法加载，ex.Message）
 
 结束 尝试 
 
 
 结束  Sub  

解决方案

当前的问题是Afzaal说：你为一个或多个列提供的值不能转换为整数，这是列所期望的。



但你有比这更大的问题！



你不能假设用户会输入有效的数据 - 所以不是假设他们会将它发送到SQL而不是让它排序，你真的应该检查和转换每个数值，每个基于日期的值，并单独报告问题，以便用户可以修复它们。

因为你有很多输入，只是告诉他不能加载根本没有很有帮助 - 用户将花费更多的时间来计算出哪些数据错误而不是添加代码来检查它！

这并不复杂：

< pre lang =vb> Dim textBox As New TextBox（）
Dim value As 整数
如果 不 整数 .TryParse（textBox.Text，value）然后
' 报告问题
...
结束 如果

其次，不要那样做SQL！不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。使用参数化查询，并将已检查和转换的值直接作为正确的数据类型传递。



第三，如果这确实是VB.NET，那么不要使用MsgBox - 对于初学者，你应该使用.NET MessageBox.Show，其次VB代码在服务器而不是客户端上运行 - 因此消息将显示在服务器上，客户端将不会得到任何响应...

When click on update command button the error is

"Conversion from string "Syntax error in UPDATE statement" to type 'Integer' is not valid."

        BtnSave.Enabled = True
        BtnAdd.Enabled = True
        BtnUpdate.Enabled = True
        BtnDelete.Enabled = True
        BtnPrevious.Enabled = True
        BtnNext.Enabled = True
        BtnFirst.Enabled = True
        BtnLast.Enabled = True

        Try

            Dim cmd As New OleDb.OleDbCommand()
            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= E:\VB Project\Tansportation\Transport2003.mdb"



            con.Open()

            
            cmd.Connection = con

            cmd.CommandText = "UPDATE TblBillStatmentEntry SET Date='" & TxtDate.Text & "',FreightSlipNo='" & TxtFreightSlipNo.Text & "',FromID='" & ComboBox1.Text & "',To='" & TxtTo.Text & "',RatesPerTon='" & TxtRatePerTon.Text & "',RatesPerTrip='" & TxtRatePerTrip.Text & "',WorkEffectiveDate='" & TxtWEF.Text & "' ,MaterialID='" & ComboBox2.Text & "', TruckNoID='" & ComboBox3.Text & "',TonCategory='" & TxtTon.Text & "',ConditionListID='" & ComboBox4.Text & "',Weight='" & TxtExtra.Text & "',Trips='" & TxtTrips.Text & "',ActualWeight='" & TxtActualWeight.Text & "',BillWeight='" & TxtBillWeight.Text & "',Amount='" & TxtAmount.Text & "' WHERE ID='" & TxtID.Text & "' "


           
cmd.ExecuteNonQuery()
            If con.State = ConnectionState.Open Then
                con.Close()
            End If

            MsgBox("Data Updated")
        Catch ex As Exception
            MsgBox("Cant load", ex.Message)

        End Try

       
    End Sub

解决方案

The immediate problem is as Afzaal said: the value you give for one or more columns is not convertible to an integer, which the column expects.

But you have bigger problems than that!

You can't assume that users will enter valid data - so rather than assuming they will and sending it to SQL regardless and letting it sort it out, you really should check and convert each numeric value, each date based value, and report individually on the problems so the user can fix them.
Since you have a lot of inputs, just telling him "Cant load" isn't at all helpful - and users will spend a lot more time trying to work out which data is wrong than you would spend adding the code to check it!
It's not complex:

Dim textBox As New TextBox()
Dim value As Integer
If Not Integer.TryParse(textBox.Text, value) Then
        ' Report problem
        ...
End If

Secondly, don't do SQL like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead, and pass the checked and converted values directly as the right datatype.

Thirdly, if this really is VB.NET, then don't use MsgBox - for starters, you should be using the .NET MessageBox.Show instead, and secondly VB code runs on the server, not the client - so the message will show at the server, and the client will not get any response...

这篇关于无法更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！