使用vb更新访问数据库 [英] updating access db using vb

问题描述

g

我正在尝试使用带按钮的表单在后台更新访问数据库。我收到一个错误，指出缺少一个更新参数。

 私人  Sub  Button1_Click（ ByVal  sender  As  System。对象， ByVal  e  As  System.EventArgs）句柄 Button1.Click 
 
 如果 Electric_Cables.Checked =  True  然后 
 如果 RadioButton1.Checked =  True  然后 
 mycon.ConnectionString =  < span class =code-string> Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\ Warehouses\prjct_mntrng.mdb 
 暗淡 command 作为 新 OleDbCommand（）
 command.CommandText =   UPDATE Electric_Cables SET Actual_Start ='& 日期。现在&  'WHERE Asset_ID = + TextBox1.Text +    
 mycon.Open（）
 command.Connection = mycon 
 command.ExecuteNonQuery（）
 MessageBox.Show（ 条目已成功注册。）
  ElseIf  RadioButton2.Checked =  True  然后 
 mycon.ConnectionString =   Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\ Warehouses \ prjct_mntrng.mdb 
  Dim 命令作为 新 OleDbCommand（）
 command.CommandText =   UPDATE Electric_Cables SET Actual_Finish ='& 日期。现在&  'WHERE Asset_ID = + TextBox1.Text +    
 mycon.Open（）
 command.Connection = mycon 
 command.ExecuteNonQuery（）
 MessageBox.Show（ 条目已成功注册。）
  ElseIf  RadioButton1.Checked =  False   AndAlso  RadioButton2.Checked =  False  然后 
 MessageBox.Show（ < span class =code-string>检查报告开始或报告完成选项是否成功进入。）
 结束 如果 

解决方案

这就是使用字符串连接来构建SQL查询的结果。就像已经说过的那样，在两个查询中你的关键字WHERE之前没有空格。



现在停止工作，谷歌为vb .net sql参数化查询。你的代码存在巨大的安全问题，你需要在继续这个项目之前以正确的方式学习如何做。



有多大的问题这是？好吧，有一天你可以走进工作，找到你正在查询GONE的数据库。是的，Access文件仍然存在，但是其中的表都已被删除。

替换：

   UPDATE Electric_Cables SET Actual_Start ='& 日期。现在&  'WHERE Asset_ID =& TextBox1.Text&   

with：

 PARAMETERS [AssetId]  INT  
  UPDATE  Electric_Cables  SET  Actual_Start = #Date（）＃ WHERE  Asset_ID = [AssetId] 

如您所见，我做了3次更改：

1） Date.Now 已替换为 Date（） MS数据库引擎的内置函数，

2）日期被<$ c $包围c>＃ sign - 它是日期字段的标准！

3）命名参数正在使用中。



我强烈建议使用 OleDbCommand [ ^ ]与p arameter（s）。



看看这里：

PARAMETERS声明（Microsoft Access SQL） [ ^ ]

OleDbCommand.Parameters Property [ ^

gHi,
i am trying to update an access db in the background using a form with buttons. i get an error that states one of the parameters for updating is missing.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If Electric_Cables.Checked = True Then
            If RadioButton1.Checked = True Then
                mycon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Warehouses\prjct_mntrng.mdb"
                Dim command As New OleDbCommand()
                command.CommandText = "UPDATE Electric_Cables SET Actual_Start= '" & Date.Now & "'WHERE Asset_ID=" + TextBox1.Text + ""
                mycon.Open()
                command.Connection = mycon
                command.ExecuteNonQuery()
                MessageBox.Show("Entry Registered Successfully.")
            ElseIf RadioButton2.Checked = True Then
                mycon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Warehouses\prjct_mntrng.mdb"
                Dim command As New OleDbCommand()
                command.CommandText = "UPDATE Electric_Cables SET Actual_Finish= '" & Date.Now & "' WHERE Asset_ID=" + TextBox1.Text + ""
                mycon.Open()
                command.Connection = mycon
                command.ExecuteNonQuery()
                MessageBox.Show("Entry Registered Successfully.")
            ElseIf RadioButton1.Checked = False AndAlso RadioButton2.Checked = False Then
                MessageBox.Show("Check Report Start or Report Finish option for successful Entry.")
            End If

解决方案

That's what you get for using string concatentation to build SQL queries. Like has already been said, you don't have a space before your keyword 'WHERE' in both queries.

Stop working on this right now and Google for "vb.net sql parameterized queries". You've got HUGE security problems with your code and you need to learn how to do it the right way before you continue with this project.

How big of a problem is this? Well, you could walk into work one day and find the database that you're querying GONE. Yep, the Access file is still there, but the tables in it have all been dropped.

Replace:

"UPDATE Electric_Cables SET Actual_Start= '" & Date.Now & "' WHERE Asset_ID=" & TextBox1.Text & ""

with:

PARAMETERS [AssetId] INT
UPDATE Electric_Cables SET Actual_Start= #Date()# WHERE Asset_ID=[AssetId]

As you can see, i've made 3 changes:
1) Date.Now has been replaced with Date() inbuilt function for MS database engine,
2) Date is surrounded with # sign - it's standard for date fields!
3) Named parameters are in use.

I strongly recommend to use OleDbCommand[^] with parameter(s).

Have a look here:
PARAMETERS Declaration (Microsoft Access SQL)[^]
OleDbCommand.Parameters Property[^]

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