vb.net - vb.net中的数据类型不匹配标准表达式，插入时访问

Question

Imports System.Data.OleDb
Imports System.IO

Public Class insuranceform
    Dim read As String
    Dim datafile As String
    Dim connstring As String
    Dim cmd As New OleDbCommand
    Public da As New OleDbDataAdapter
    Dim str As String
    Public ds As New DataSet
    Public ds1 As New DataSet
    Public ds2 As New DataSet
    Dim myconnection As OleDbConnection = New OleDbConnection

    Dim er As Integer
    Private Sub insuranceform_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        read = "provider=microsoft.ace.oledb.12.0;data source="
        datafile = "C:\Users\DELL\source\repos\HRIS SYSTEM\loginformdatabase\BLUESTREAM.accdb"
        connstring = read & datafile
        myconnection.ConnectionString = connstring
        ds.Clear()
        DateTimePicker1.Value = DateTime.Now
        DateTimePicker2.Value = DateTime.Now
        DateTimePicker3.Value = DateTime.Now
        If myconnection.State = ConnectionState.Open Then
            myconnection.Close()
        End If
        myconnection.Open()
        er = 0
        'cn.Open()
        str = "select * from insurancedetail"
        cmd = New OleDbCommand(str, myconnection)
        da.SelectCommand = cmd
        da.Fill(ds, "insurancedetail")
    End Sub

    Private Sub Save_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ds.Clear()
        str = "select * from insurancedetail"
        cmd = New OleDbCommand(str, myconnection)
        da.SelectCommand = cmd
        da.Fill(ds, "insurancedetail")
        If er = 0 Then
            Try
                cmd.Connection = myconnection
                cmd.CommandText = "insert into insurancedetail(Name,EmployeeID,PAN,UniversalAccountNumber,AdharNo,CurrentAddress,PermanentAddress,Landline,MartialStatus,MobileNumber,EmergencyContactNo,BloodGroup,DoyouHaveHDFCbankaccount,NameOfdependentmember_F) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & TextBox7.Text & "','" & TextBox8.Text & "','" & ComboBox2.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "','" & TextBox11.Text & "','" & ComboBox1.Text & "','" & TextBox12.Text & "')"
                cmd.ExecuteNonQuery() 'if command is executed'
                Dim result As Integer = MessageBox.Show("New insurance detail Added. Want To Add Another One.", "Added", MessageBoxButtons.YesNo)
                If result = DialogResult.No Then
                    Me.Close()
                ElseIf result = DialogResult.Yes Then
                    ds.Clear()
                    TextBox1.Clear()
                    TextBox2.Clear()
                    TextBox3.Clear()
                    TextBox4.Clear()
                    TextBox5.Clear()
                    TextBox6.Clear()
                    TextBox7.Clear()
                    TextBox8.Clear()
                    TextBox9.Clear()
                    TextBox10.Clear()
                    TextBox11.Clear()
                    TextBox12.Clear()
                    TextBox13.Clear()
                    TextBox14.Clear()
                    TextBox15.Clear()
                    TextBox16.Clear()
                    TextBox17.Clear()
                    TextBox18.Clear()
                    TextBox20.Clear()
                    ComboBox1.ResetText()
                    ComboBox2.ResetText()
                    ComboBox3.ResetText()
                    ComboBox4.ResetText()
                    ComboBox5.ResetText()
                    DateTimePicker1.ResetText()
                    DateTimePicker2.ResetText()
                    DateTimePicker3.ResetText()
                    str = "select * from insurancedetail"
                    cmd = New OleDbCommand(str, myconnection)
                    da.SelectCommand = cmd
                    da.Fill(ds, "insurancedetail")
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            'insert close
        End If
        'myconnection close
    End Sub

标签： vb.netms-access-2013

Answer 1

太多的类级别变量。尤其不是 myconnection 和 cmd。到底什么是呃？程序不宜做太多。将一些代码移至其他过程，特别是如果它们将被多次调用。

对于数据库对象，请使用 Using 块。即使出现错误，它们也会确保您的对象已关闭和处置。您似乎没有使用 DataAdapter，因此只需直接填充 DataTable。它可以绑定到 DataGridView。

在保存之前没有理由再次填充 DataTable。您可能希望在保存后再次重新填充。

我猜测您数据库中的数据类型。您必须检查数据库以获取正确的数据类型，并为字符串类型获取字段的大小。将您的 TextBox 值转换为正确的类型。我只使用了你的几个字段来进行演示。确保按照它们在 sql 语句中出现的顺序添加参数。

Private dt As New DataTable
Private connString As String = "provider=microsoft.ace.oledb.12.0;data source=C:\Users\DELL\source\repos\HRIS SYSTEM\loginformdatabase\BLUESTREAM.accdb"

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    SetDatePickerValues()
    FillDataTable()
End Sub

Private Sub Save_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Using cn As New OleDbConnection(connString)
            Using cmd As New OleDbCommand("Insert Into insurancedetail(Name,EmployeeID,PAN) Values(@Name, @EmployeeID, @PAN);", cn)
                cmd.Parameters.Add("@Name", OleDbType.VarChar, 100).Value = TextBox1.Text
                cmd.Parameters.Add("@EmployeeID", OleDbType.Integer).Value = CInt(TextBox2.Text)
                cmd.Parameters.Add("@PAN", OleDbType.VarChar, 100).Value = TextBox3.Text
                cn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
        FillDataTable()
        Dim result As Integer = MessageBox.Show("New insurance detail Added. Want To Add Another One.", "Added", MessageBoxButtons.YesNo)
        If result = DialogResult.No Then
            Me.Close()
        Else
            ClearForm()
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Private Sub ClearForm()
    TextBox1.Clear()
    TextBox2.Clear()
    TextBox3.Clear()
    TextBox4.Clear()
    TextBox5.Clear()
    TextBox6.Clear()
    TextBox7.Clear()
    TextBox8.Clear()
    TextBox9.Clear()
    TextBox10.Clear()
    TextBox11.Clear()
    TextBox12.Clear()
    TextBox13.Clear()
    TextBox14.Clear()
    TextBox15.Clear()
    TextBox16.Clear()
    TextBox17.Clear()
    TextBox18.Clear()
    TextBox20.Clear()
    ComboBox1.ResetText()
    ComboBox2.ResetText()
    ComboBox3.ResetText()
    ComboBox4.ResetText()
    ComboBox5.ResetText()
    SetDatePickerValues()
End Sub

Private Sub FillDataTable()
    Try
        dt.Clear()
        Using cn As New OleDbConnection(connString)
            Using cmd As New OleDbCommand("Select * From insurancedetail", cn)
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Private Sub SetDatePickerValues()
    DateTimePicker1.Value = DateTime.Now
    DateTimePicker2.Value = DateTime.Now
    DateTimePicker3.Value = DateTime.Now
End Sub