VB如何将串口读取的数据存入access

longzai0803 2011-05-21 12:54:01
小弟最近做一个小程序,碰到了个问题,找遍图书馆也没找到答案,只好求助万能的网络了。
小弟做的是基于RFID的仓储信息系统,硬件已经用串口助手调试好了,发送过来的是一串16位的数,但是现在我只能将他们显示出来却没有办法将他们实时更新存储进access数据库。
希望各位大侠不吝赐教,小弟也是菜鸟一个啊,基本的语言都没学好就被老师逼着做这个,希望哪位大哥能帮帮忙,谢谢啦。
...全文
806 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
longzai0803 2011-05-23
  • 打赏
  • 举报
回复
刚刚才把其他的程序改好 现在才来看看 果然还是您回答了
感谢的话就不多说了 O(∩_∩)O~ 我再试试吧 有不懂的还忘您不吝赐教
麒皑鹭 2011-05-23
  • 打赏
  • 举报
回复
1、你说的那个重复的问题,是因为数据表中的主关键字不能有重复值。我之前设置的表只有一个字段,没有设主键,就是怕有重复数据。你可以再建一个主键,用时间(精确到毫秒)来区分,或者用编号。

比如建立时间主键字段 DateID,保存数据字段Data,则SQLString="INSERT INTO tb(DateID,Data) VALUES('" & MyDate & "','" & data & "')"


2、将数据都转换成 string 类型,然后记得数据表中的字段类型要改为文本。应该不会出现类型不匹配的问题。

3、对于你说的连续接收到的重复数据时,会有数据保存被遗漏的问题,我弄得不明白,没搞过串口通信。
希望你弄清楚你的程序如何判断一次接收数据完成的,那么在每完成一次数据接收后就调用保存数据事件保存数据,这样就不会遗漏了。
longzai0713 2011-05-23
  • 打赏
  • 举报
回复
不能回复三次
都结贴了 不知道还有没有人看
longzai0713 2011-05-23
  • 打赏
  • 举报
回复
Imports System.Data.OleDb
Public Class frmMain
Dim strDataReceived As String
Dim CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Test.mdb"
'或者写出实际的数据库文件地址,无密码的:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Test.mdb"
Dim MyConnection As New OleDbConnection(CONNECTION_STRING) '实例化连接对象
Dim MyCommand As OleDbCommand
Dim SQLString As String

Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PortNameList As String() = IO.Ports.SerialPort.GetPortNames
Dim Port As String

cboPortNo.Items.Clear()
For Each Port In PortNameList
cboPortNo.Items.Add(Port)
Next
End Sub

Private Sub btnPort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPort.Click
Try
If SerialPort.IsOpen = True Then
SerialPort.Close()
btnPort.Enabled = True
btnClose.Enabled = False
End If
With SerialPort
.PortName = cboPortNo.Text.Trim
.BaudRate = cboBps.Text.Trim
.DataBits = cboDataBits.Text.Trim

Select Case cboParity.Text.Trim
Case "Even"
.Parity = IO.Ports.Parity.Even
Case "Odd"
.Parity = IO.Ports.Parity.Odd
Case "None"
.Parity = IO.Ports.Parity.None
Case "Mark"
.Parity = IO.Ports.Parity.Mark
Case "Space"
.Parity = IO.Ports.Parity.Space
End Select

Select Case cboStopBits.Text.Trim
Case "None"
.StopBits = IO.Ports.StopBits.None
Case "1"
.StopBits = IO.Ports.StopBits.One
Case "1.5"
.StopBits = IO.Ports.StopBits.OnePointFive
Case "2"
.StopBits = IO.Ports.StopBits.Two
End Select

Select Case cboHandShake.Text.Trim
Case "Xon/Xoff"
.Handshake = IO.Ports.Handshake.XOnXOff
Case "Hardware"
.Handshake = IO.Ports.Handshake.RequestToSend
Case "None"
.Handshake = IO.Ports.Handshake.None
End Select
End With
SerialPort.Open()

btnPort.Enabled = False
btnClose.Enabled = True
Catch ex As Exception
MsgBox(ex.ToString, MsgBoxStyle.OkOnly)
Exit Sub
End Try
End Sub
'********************************************************************************
'保存数据事件
Private Sub SaveData(ByVal _data As String)
'或者用字符串, 数据库中相应字段类型也要改为文本
Dim data As String = _data

Dim m As Integer = 0 '记录SQL语句执行行数

SQLString = "INSERT INTO tb(ID1) VALUES('" & data & "')" 'Test.mdb中 tb表

MyConnection.Open() '打开数据连接

MyCommand = New OleDbCommand(SQLString, MyConnection) '定义新的操作命令

Try '尝试执行
m = MyCommand.ExecuteNonQuery() '执行插入操作,并返回受影响的行数
Catch ex As Exception '获取异常
MsgBox(ex.ToString) '显示异常信息
End Try

If m = 1 Then
MsgBox("保存数据成功")
Else
MsgBox("保存数据失败")
End If

MyConnection.Close() '关闭连接
MyCommand.Dispose() '释放对象
End Sub
'****************************************************************************************


Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Try
If SerialPort.IsOpen = True Then
SerialPort.Close()
btnClose.Enabled = False
btnPort.Enabled = True
End If
Catch ex As Exception
MsgBox(ex.ToString, MsgBoxStyle.OkOnly)
Exit Sub
End Try
End Sub

Private Sub btnSend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSend.Click
Dim strDataOut As String

Try
If SerialPort.IsOpen = True Then
strDataOut = txtDataOut.Text
SerialPort.Write(strDataOut + vbCr)
End If
Catch ex As Exception
MsgBox(ex.ToString, MsgBoxStyle.OkOnly)
Exit Sub
End Try
End Sub

'==============================
' Serial Port
' Receiving of data
'==============================
Private Sub SerialPort_DataReceived(ByVal sender As Object, ByVal e As System.IO.Ports.SerialDataReceivedEventArgs) Handles SerialPort.DataReceived
strDataReceived = strDataReceived + SerialPort.ReadExisting()
'***********************************
'添加保存数据事件
SaveData(txtDataReceived.Text)
'***********************************
End Sub

Private Sub tmrDataReceived_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tmrDataReceived.Tick
If SerialPort.IsOpen = True Then
txtDataReceived.Text = strDataReceived
End If

End Sub

Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

End Sub
End Class
longzai0803 2011-05-23
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 wjq726 的回复:]
textbox.text 是string类型,需要转换才行:Dim data As Int64 = CType(textbox2.text, Int64)
[/Quote]

你好 ,刚才我换了个程序,加上您的代码,终于可以保存成功了 真是太感谢啦!

不过保存的时候还是有点小问题 ,每次开始保存的时候都会有“由于将在索引、 主关键字、或关系中创建重复的值”这个问题是数据库的原因,我自己应该可以解决

有时又提示"标准表达式中数据类型不匹配"从串口传过来的是如a110099ce176这串 用string应该是对的吧

最重要的是每次传过来的都是这串数,但怎么样才能将他们保存在数据表中不同的单元格呢,是否因为它没有结束符而很难办到呢?

可能我的问题实在太菜了 不要见笑 下午大部分时候都能保存成功 真的非常感谢你 我的代码在下面 麻烦看看 就把这当做这次开发的笔记簿吧 O(∩_∩)O~
longzai0803 2011-05-23
  • 打赏
  • 举报
回复
调了一上午 居然都不能显示了 不过还是谢谢了
longzai0803 2011-05-22
  • 打赏
  • 举报
回复

Imports System.Data.OleDb
Public Class Form1
Dim strData As String
Dim strDat As String
Dim strDate As String
Dim mRecvByte() As Byte
'Application.StartupPath:程序启动目录,带密码
Dim CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Test.mdb"
'或者写出实际的数据库文件地址,无密码的:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Test.mdb"

Dim MyConnection As New OleDbConnection(CONNECTION_STRING) '实例化连接对象
Dim MyCommand As OleDbCommand
Dim SQLString As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
For i As Integer = 0 To My.Computer.Ports.SerialPortNames.Count - 1 '初始化串口数
cboCommPorts.Items.Add(My.Computer.Ports.SerialPortNames(i))
Next
Button2.Enabled = False



End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click '连接
If SerialPort1.IsOpen Then
SerialPort1.Close()
End If
Try
With SerialPort1 '初始化串口设置
.PortName = cboCommPorts.Text
.BaudRate = 9600
.Parity = IO.Ports.Parity.None '无校验位
.DataBits = 8
.StopBits = IO.Ports.StopBits.One '停止位1位
.ReadBufferSize = 1024 '接收缓冲区大小

End With
SerialPort1.Open()
lblMessage.Text = SerialPort1.PortName & " connected"
Button2.Enabled = True
Button1.Enabled = False
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try





End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click '断开
Try
SerialPort1.Close()
lblMessage.Text = SerialPort1.PortName & " disconnected"
Button2.Enabled = False
Button1.Enabled = True
Catch ex As Exception

End Try
End Sub

Public Sub updateTextBox(ByVal sender As System.Object, ByVal e As System.EventArgs) 'ascII.10进制与16进制显示
strDat = ""
textbox1.Text = ""
Dim i As Integer '处理为16进制
For i = 0 To UBound(mRecvByte) 'mSize - 1
'strData = strData & IIf(mRecvByte(i) > 15, Hex(mRecvByte(i)), "0" & Hex(mRecvByte(i))) & " " 滚动显示模式
strData = IIf(mRecvByte(i) > 15, Hex(mRecvByte(i)), "0" & Hex(mRecvByte(i))) & " " '静止显示模式
strDate = mRecvByte(i) '处理为10进制
Next
Dim sj As Byte
For i = 1 To Len(strData) Step 3 '处理为ASCII字符
sj = Val("&H" & Mid(strData, i, 2))
If sj < 32 Or sj > 128 Then '当接收字节中有Chr(0)时,其后字符被切割
'strDat = strDat & "."
strDat = "."
Else
'strDat = strDat & Chr(sj)
strDat = Chr(sj)
End If
Next
textbox1.Text = strDat '显示字符
TextBox2.Text = strData '显示为16进制
TextBox3.Text = strDate '显示为10进制
End Sub


Private Sub SerialPort1_DataReceived(ByVal sender As System.Object, ByVal e As System.IO.Ports.SerialDataReceivedEventArgs) Handles SerialPort1.DataReceived
Dim mSize As Integer = Me.SerialPort1.BytesToRead
ReDim mRecvByte(mSize - 1)
SerialPort1.Read(mRecvByte, 0, mSize)
BeginInvoke(New EventHandler(AddressOf updateTextBox), SerialPort1.BytesToRead()) '使用委托方式显示接收到的字符串


End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click '退出
Me.Close()
End Sub



Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'Int64最大值为 9,223,372,036,854,775,807;即十六进制的 0x7FFFFFFFFFFFFFFF
'可以保存15位数据
'Dim data As Int64 = 123456789012345

'或者用字符串, 数据库中相应字段类型也要改为文本
Dim data As String = TextBox2.Text

Dim m As Integer = 0 '记录SQL语句执行行数

SQLString = "INSERT INTO tb(ID1) VALUES('" & data & "')" 'Test.mdb中 tb表

MyConnection.Open() '打开数据连接

MyCommand = New OleDbCommand(SQLString, MyConnection) '定义新的操作命令

Try '尝试执行
m = MyCommand.ExecuteNonQuery() '执行插入操作,并返回受影响的行数
Catch ex As Exception '获取异常
MsgBox(ex.ToString) '显示异常信息
End Try

If m = 1 Then
MsgBox("保存数据成功")
Else
MsgBox("保存数据失败")
End If

MyConnection.Close() '关闭连接
MyCommand.Dispose() '释放对象
End Sub
End Class
longzai0803 2011-05-22
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 wjq726 的回复:]

补充一下,tb表没有设置主键。
以上仅为示例。
[/Quote]

您好,我刚刚测试了下您的代码,我一运行就会保存数据到数据库

可是我还有个问题,我如果把这段代码直接加入到串口接收程序窗体的 Private Sub Form1_Load 里,然后将 Dim data As Int64 = 123456789012345 改成 Dim data As Int64 = textbox.text
按照我的理解,这样就可以把textbox中的数据存储到数据库,可是每次运行的时候开始都会显示存储错误,因为这样就是先存后打开串口,所以每次都先显示存储错误。
然后我又尝试了添加一个按钮,将您的 Private Sub Form4_Load 中的代码放入botton的事件中,这样就变成了按一次这个按钮就存储textbox中的数据到数据库中,您说对吗?

我想要实现的是打开串口后,数据在textbox中显示出来并同时存入数据库。是不是一定要使用timer之类的控件呢?
我把我的代码贴出来 您能帮忙看下吗?
我的邮箱longzai0803@gmail.com QQ:84041820
很希望能得到您的帮助
麒皑鹭 2011-05-22
  • 打赏
  • 举报
回复

textbox.text 是string类型,需要转换才行:Dim data As Int64 = CType(textbox2.text, Int64)
麒皑鹭 2011-05-22
  • 打赏
  • 举报
回复
代码如下,注意改动。

Imports System.Data.OleDb
Public Class Form5
Dim strData As String
Dim strDat As String
Dim strDate As String
Dim mRecvByte() As Byte
'Application.StartupPath:程序启动目录,带密码
Dim CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Test.mdb"
'或者写出实际的数据库文件地址,无密码的:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Test.mdb"
Dim MyConnection As New OleDbConnection(CONNECTION_STRING) '实例化连接对象
Dim MyCommand As OleDbCommand
Dim SQLString As String


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
For i As Integer = 0 To My.Computer.Ports.SerialPortNames.Count - 1 '初始化串口数
cboCommPorts.Items.Add(My.Computer.Ports.SerialPortNames(i))
Next
Button2.Enabled = False
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click '连接
If SerialPort1.IsOpen Then
SerialPort1.Close()
End If
Try
With SerialPort1 '初始化串口设置
.PortName = cboCommPorts.Text
.BaudRate = 9600
.Parity = IO.Ports.Parity.None '无校验位
.DataBits = 8
.StopBits = IO.Ports.StopBits.One '停止位1位
.ReadBufferSize = 1024 '接收缓冲区大小

End With
SerialPort1.Open()
lblMessage.Text = SerialPort1.PortName & " connected"
Button2.Enabled = True
Button1.Enabled = False
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click '断开
Try
SerialPort1.Close()
lblMessage.Text = SerialPort1.PortName & " disconnected"
Button2.Enabled = False
Button1.Enabled = True
Catch ex As Exception

End Try
End Sub

'********************************************************************************
'保存数据事件
Private Sub SaveData(ByVal _data As String)
'或者用字符串, 数据库中相应字段类型也要改为文本
Dim data As String = _data

Dim m As Integer = 0 '记录SQL语句执行行数

SQLString = "INSERT INTO tb(ID1) VALUES('" & data & "')" 'Test.mdb中 tb表

MyConnection.Open() '打开数据连接

MyCommand = New OleDbCommand(SQLString, MyConnection) '定义新的操作命令

Try '尝试执行
m = MyCommand.ExecuteNonQuery() '执行插入操作,并返回受影响的行数
Catch ex As Exception '获取异常
MsgBox(ex.ToString) '显示异常信息
End Try

If m = 1 Then
MsgBox("保存数据成功")
Else
MsgBox("保存数据失败")
End If

MyConnection.Close() '关闭连接
MyCommand.Dispose() '释放对象
End Sub
'****************************************************************************************

Public Sub updateTextBox(ByVal sender As System.Object, ByVal e As System.EventArgs) 'ascII.10进制与16进制显示
strDat = ""
textbox1.Text = ""
Dim i As Integer '处理为16进制
For i = 0 To UBound(mRecvByte) 'mSize - 1
'strData = strData & IIf(mRecvByte(i) > 15, Hex(mRecvByte(i)), "0" & Hex(mRecvByte(i))) & " " 滚动显示模式
strData = IIf(mRecvByte(i) > 15, Hex(mRecvByte(i)), "0" & Hex(mRecvByte(i))) & " " '静止显示模式
strDate = mRecvByte(i) '处理为10进制
Next
Dim sj As Byte
For i = 1 To Len(strData) Step 3 '处理为ASCII字符
sj = Val("&H" & Mid(strData, i, 2))
If sj < 32 Or sj > 128 Then '当接收字节中有Chr(0)时,其后字符被切割
'strDat = strDat & "."
strDat = "."
Else
'strDat = strDat & Chr(sj)
strDat = Chr(sj)
End If
Next
textbox1.Text = strDat '显示字符
TextBox2.Text = strData '显示为16进制
TextBox3.Text = strDate '显示为10进制

'***********************************
'添加保存数据事件
SaveData(TextBox2.Text)
'***********************************

End Sub


Private Sub SerialPort1_DataReceived(ByVal sender As System.Object, ByVal e As System.IO.Ports.SerialDataReceivedEventArgs) Handles SerialPort1.DataReceived
Dim mSize As Integer = Me.SerialPort1.BytesToRead
ReDim mRecvByte(mSize - 1)
SerialPort1.Read(mRecvByte, 0, mSize)
BeginInvoke(New EventHandler(AddressOf updateTextBox), SerialPort1.BytesToRead()) '使用委托方式显示接收到的字符串
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click '退出
Me.Close()
End Sub
End Class

longzai0803 2011-05-22
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 wjq726 的回复:]
textbox.text 是string类型,需要转换才行:Dim data As Int64 = CType(textbox2.text, Int64)
[/Quote]

原来是这样的啊,这个还真不知道
太感谢了,刚在自己电脑上试了下 程序没什么错误 星期天实验室没开门
明天我去实验室试试 要有问题还得向您请教
真是太麻烦您了
longzai0803 2011-05-21
  • 打赏
  • 举报
回复
O(∩_∩)O谢谢啦
刚刚才看到
我回去研究哈
有不懂的还要麻烦你O(∩_∩)O哈!
麒皑鹭 2011-05-21
  • 打赏
  • 举报
回复
补充一下,tb表没有设置主键。
以上仅为示例。
麒皑鹭 2011-05-21
  • 打赏
  • 举报
回复

Imports System.Data.OleDb
Public Class Form4
'Application.StartupPath:程序启动目录,带密码
Dim CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\Test.mdb;Jet OLEDB:Database Password=admin"

'或者写出实际的数据库文件地址,无密码的:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Test.mdb"

Dim MyConnection As New OleDbConnection(CONNECTION_STRING) '实例化连接对象
Dim MyCommand As OleDbCommand
Dim SQLString As String

Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Int64最大值为 9,223,372,036,854,775,807;即十六进制的 0x7FFFFFFFFFFFFFFF
'可以保存15位数据
Dim data As Int64 = 123456789012345

'或者用字符串,数据库中相应字段类型也要改为文本
'Dim data as string="123456789012345"

Dim m As Integer = 0 '记录SQL语句执行行数

SQLString = "INSERT INTO tb(ID1) VALUES('" & data & "')" 'Test.mdb中 tb表

MyConnection.Open() '打开数据连接

MyCommand = New OleDbCommand(SQLString, MyConnection) '定义新的操作命令

Try '尝试执行
m = MyCommand.ExecuteNonQuery() '执行插入操作,并返回受影响的行数
Catch ex As Exception '获取异常
MsgBox(ex.ToString) '显示异常信息
End Try

If m = 1 Then
MsgBox("保存数据成功")
Else
MsgBox("保存数据失败")
End If

MyConnection.Close() '关闭连接
MyCommand.Dispose() '释放对象
End Sub
End Class


Test.mdb 数据库有 tb表,一个字段:ID1,字段类型为数字 双精度,小数位数0.
如果程序中data用string的话,就用文本。

16,722

社区成员

发帖
与我相关
我的任务
社区描述
VB技术相关讨论,主要为经典vb,即VB6.0
社区管理员
  • VB.NET
  • 水哥阿乐
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧