查询表达式"26-feb-12"中的字符串中的语法错误，其中代码＝ 8. [英] syntax error in string in query expression "26-feb-12"where code=8'.

问题描述

Private Sub upd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd.Click
        Try
            con.Open()
            Dim cmd As New OleDbCommand("update products set productname='" & pro.Text & "', company='" & com.Text & "', buyingprice='" & buy.Text & "', sellingprice='" & sell.Text & "', pdate='" & date1.Text & "' where code= " & CInt(code.Text) & "  ", con)
            cmd.ExecuteNonQuery()
            MessageBox.Show(" CHANGE SUCCESSFULLY")
            Dim da As New OleDbDataAdapter("select * from products", con)
            Dim dt As New DataTable
            da.Fill(dt)
            DataGridView1.DataSource = dt
            DataGridView1.Refresh()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Close()
    End Sub

请帮助ms访问数据库和vb.net ....... .....作为主键的代码....作为数字........

标准表达式中的另一个数据类型不匹配

please help ms access database and vb.net ............ code as primary key ....as number..........

another datatype mismatch in criteria expression

推荐答案

实际上，我建议您使用参数查询

Actually, I adviced you to use a PARAMETERIZED QUERY here[^]! Parameterize it and your code will be so readable that all your errors will be solved!

我完全同意Naerling-使用参数化查询-将为您省去很多麻烦.如果您的公司名称带有单引号或任何其他特殊字符怎么办-您的查询将被炸毁.参数化查询使用null，布尔值，日期，字符串中的特殊字符解决了许多问题.

在将数据写入数据库之前，也许还需要对数据进行一些验证.

将文本框上的输入长度限制为数据库字段的大小

I totally agree with Naerling - Use a parametrized query - It will save you lots of headaches. What if your company name has a single quote or any other special character - your query will bomb out. Parameterized queries solve so many problems with nulls, boolean values, dates, special characters in strings.

Maybe also do a bit of validation on the data before you write it to the database.

Limit input length on your text boxes to the size of your database field

Me.com.Text.MaxLength = 10

遮盖包含数字字段的文本框.

您可以使用MaskedTextBox代替TextBox

或

您可以处理按键事件并限制输入

Mask the text boxes that contain numeric fields.

You can use a MaskedTextBox instead of a TextBox

Or

You can handle the keypress event and limit the input

Private Sub buy.Text_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles buy.Text.KeyPress
 
        ' Allow Backspace or delete
        If Asc(e.KeyChar) = 8 Or Asc(e.KeyChar) = 127 Then
            Exit Sub
        End If

        
        'Ignore characters that are not numeric
        If Asc(e.KeyChar) < 47 Or Asc(e.KeyChar) > 57 Then
            e.Handled = True
            Exit Sub
        End If
    End Sub

或对输入有更多控制

Or with more control over the input

Private Sub buy.Text_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles buy.Text.KeyPress
        Dim decPlaces As Integer = 0
        Dim MaxIntVal As Long = 999999
        Dim AllowNeg As Boolean = False

        '' Allow Backspace or delete
        If Asc(e.KeyChar) = 8 Or Asc(e.KeyChar) = 127 Then
            Exit Sub
        End If

        ''If decimal places are not allowed, ignore ''.''
        If decPlaces = 0 And e.KeyChar = "." Then
            e.Handled = True
            Exit Sub
        End If

        ''If the box already has ''.'' dont alow another one
        If sender.Text.Contains(".") And e.KeyChar = "." Then
            e.Handled = True
            Exit Sub
        End If

        ''If negative values are not allowed, ingnore ''-''
        If Not AllowNeg And e.KeyChar = "-" Then
            e.Handled = True
            Exit Sub
        End If

        ''If there is not already a ''-'' then put the ''-'' in front
        If e.KeyChar = "-" And Not (sender.text.contains("-")) Then
            sender.text = "-" & sender.text
            e.Handled = True
            Exit Sub
        End If


        If sender.Text.Length >= 1 And e.KeyChar = "-" Then
            e.Handled = True
            Exit Sub
        End If

        If Asc(e.KeyChar) = 45 Or e.KeyChar = "." Then '' ''-''  or ''.'' respectively
            Exit Sub
        End If

        ''Limit the number of decimal places
        If sender.Text.Contains(".") Then
            If sender.Text.Length > sender.Text.IndexOf(".") + decPlaces Then
                e.Handled = True
                Exit Sub
            End If
        End If

        ''Limit the value to a maximum
        If sender.Text.Length > 2 Then
            If Not (Asc(e.KeyChar) < 47) And Not (Asc(e.KeyChar) > 57) Then
                If Math.Truncate(Math.Abs(CDbl(sender.Text & e.KeyChar))) > MaxIntVal Then
                    e.Handled = True
                    Exit Sub
                End If
            End If

        End If

        ''Ignore characters that are not numeric
        If Asc(e.KeyChar) < 47 Or Asc(e.KeyChar) > 57 Then
            e.Handled = True
            Exit Sub
        End If
    End Sub

也许也值得一提，Access确实支持@name参数，但是与SQL不同，您必须以正确的顺序添加参数.我只是使用?"作为参数

Perhaps worth mentioning as well, Access does support @name parameters, but unlike SQL you have to add the parameters in the correct order. I simply use ''?'' for the parameters

"UPDATE products set productname=@productname, company = @company WHERE ..."

您必须先添加@productname参数，然后再添加@companyname等

我使用一个称为InsertRecord的通用函数，如下所示:

You must add @productname parameter first then @companyname etc

I use a generic function called InsertRecord as follows:

Private Sub Update()
        Dim params() As String = {Me.pro.text, Me.com.text, Me.code.Text}
        Dim strInsert As String = "UPDATE products SET productname = ?, company = ? WHERE code = ?)"
        If Not InsertRecord(strInsert, params) then
           'handle your error here....
        End If
        'Your code to fill the datatable with the updated records again
    End Sub

params数组必须与Update字符串中的?"顺序相同

然后数据库写:

The params array must be in the same order as the ''?''s in the Update string

Then the database write:

Public Function InsertRecord(ByVal strInsert As String, params() As String) As Boolean
       Dim cnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data source= C:\yourPath\YourDB.accdb"

       Dim cn As New OleDb.OleDbConnection(cnStr)
       Dim da As New OleDb.OleDbDataAdapter
       Dim cmd As New OleDb.OleDbCommand(strInsert, cn)
       'Add the parameters
       For i As Integer = 0 To params.Length - 1
           If params(i) = Nothing Then params(i) = ""
           cmd.Parameters.AddWithValue("?", params(i))
       Next
       da.InsertCommand = cmd
       Try
           cn.Open()
           da.InsertCommand.ExecuteNonQuery()
           cn.Close()
           Return True
       Catch ex As Exception
           messagebox.show( ex.Message)
           cn.Close()
           Return False
       Finally
           cn.Close()
       End Try

   End Function

这篇关于查询表达式"26-feb-12"中的字符串中的语法错误，其中代码＝ 8.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！