从 mdf(主 SQL Server 数据库文件)自动完成文本框

Question

我正在尝试使用自动完成文本框，该文本框来自SQL Serve .mdf数据库文件。

这是我的代码：

Dim cmd As New SqlCommand("Select col_name  FROM college ", cn)
If cn.State = ConnectionState.Closed Then cn.Open()
Dim ds As New DataSet
Dim sqda As New SqlDataAdapter(cmd)
sqda.Fill(ds, "college")
Dim col As New AutoCompleteStringCollection
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
    col.Add(ds.Tables(0).Rows(i)("col_name").ToString())
Next
TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
TextBox1.AutoCompleteCustomSource = col
TextBox1.AutoCompleteMode = AutoCompleteMode.Suggest

由于我使用的是SQL Server，因此数据未显示在列表中。

我认为它需要使用字符 N 喜欢

将数据插入到 SQL Server 中，如下所示：

cmd = New SqlCommand("insert into college (col_name) values (N'" & TextBox1.Text.Trim & "')", cn)

Answer 1

将数据库对象保留在本地，以便可以控制关闭和释放。用。。。即使存在错误，"结束使用"块也会处理此问题。

始终使用参数来避免 Sql 注入。

在.net Char中支持Unicode，所以字符串(Char的数组(也支持Unicode。只要 Sql Server 中的字段是 NVarChar 类型。我看不出问题

Private Sub SetUpAutoComplete()
    Dim dt As New DataTable
    Using cn As New SqlConnection("Your connection string")
        Using cmd As New SqlCommand("Select col_name  FROM college ", cn)
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    Dim col As New AutoCompleteStringCollection
    For Each row As DataRow In dt.Rows
        col.Add(row("col_name").ToString)
    Next
    'Just to check if we have some data
    Debug.Print(col.Count.ToString)
    TextBox3.AutoCompleteSource = AutoCompleteSource.CustomSource
    TextBox3.AutoCompleteCustomSource = col
    TextBox3.AutoCompleteMode = AutoCompleteMode.Suggest
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    SetUpAutoComplete()
End Sub
Private Sub InsertCollege()
    Using cn As New SqlConnection("Your Connection string")
        Using cmd As New SqlCommand("insert into college (col_name) values (@Name)", cn)
            cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = TextBox1.Text.Trim
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub