我使用Visual Basic 2008速成版通过LINQ为我的数据库操作的sql如编辑记录。 我没有使用任何SQL Server，但我只是使用内置在Visual Basic 2008 Express中的SQL Server。 我试着修改了代码，在语法没有错误，但有在运行时错误，它会弹出式窗口消息称它的错误消息。 我要的是编辑这是从数据库中检索到的文本框，当你点击button5无论在文本框中输入新的值应该替换原来的记录。 帐户字段是memrec.dbml在我的表1场，我设置了主键是真实和字段的其余均在其主键错误。 下面的代码还是发现了一个错误，当你运行该程序，并弹出，一个窗口，说：
NotSupportedException异常是未处理 - SQL Server不处理NTEXT，文本，XML或图像数据类型的比较。
它突出就行了黄色背景：
db.SubmitChanges()
这是我所看到的在memrec.dbml属性窗口上表1的每个字段的属性：
Access - Public
Type - String(System.String)
Server Data Type - Text
Auto-Generated Value - False
Auto-Sync - Never
Delay Loaded - False
Inheritance Modifier - (none)
Nullable - True
Read Only - False
Time Stamp - False
Update Check - Never
Primary Key - False ' Except for the Account field.
你怎么看越好的错误？
它是在其设置方面？
这里是我的代码：
Private Sub Button5_Click(------------------) Handles Button5.Click
If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or _
TextBox4.Text = "" Then
MsgBox("Please Fill It Up Completely", MsgBoxStyle.Exclamation)
Exit Sub
Else
Dim accnt As String
accnt = TextBox1.Text
Dim db As New memrecDataContext()
Dim editrecord = _
From memrec In db.Table1s _
Where memrec.Account.Contains(accnt) _
Select memrec
For Each memrec In editrecord
If memrec.Account = accnt Then
memrec.Account = TextBox1.Text
memrec.Name = TextBox2.Text
memrec.Address = TextBox3.Text
memrec.Gender = TextBox4.Text
db.SubmitChanges()
Exit Sub
End If
Exit For
Next
MsgBox("No Records Match", MsgBoxStyle.Information)
End If
End Sub
感谢您抽出时间陪我来解决这个问题.....
凡memrec.Account.Contains（ACCNT）_
此代码将执行“像”，在您的数据库和这样的行动中不能包含在NText和Text字段。
尝试从改变你的字段类型Text到Varchar(500)或更大，如果你认为500是不够的）
要么
如果你打算寻找一个已知值，请尝试更改你在哪里：
Where memrec.Account = accnt
进口System.Data进口System.Data.SqlClient的
公共类frmRpro
Private Sub frmRpro_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
lock()
End Sub
Private Sub autonum()
Call main()
Con.Open()
Dim UserSelect As SqlCommand
Dim myreader As SqlDataReader
Dim sql As String
Dim sum As Integer
Dim no As Integer
'sql = "select * from packwell_customers where customer_code like '" & (txt1.Text) & "%'"
'sql.Max()
sql = "select max(rcv_reference) from DOrproduct"
UserSelect = New SqlCommand(sql, Con)
myreader = UserSelect.ExecuteReader
If (myreader.Read = True) Then
no = 1
txt1.Text = myreader(0)
sum = txt1.Text + no
txt1.Text = 0 & sum
clear()
End If
Con.Close()
End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cnew.Click
If cnew.Text = "New" Then
cnew.Text = "Save"
cedit.Text = "Cancel"
clear()
autonum()
unlock()
cdel.Enabled = False
csearch.Enabled = False
txt1.Enabled = False
'Dim myDate As Date = Now
'txt3.Text = Format(myDate, "MM/dd/yyyy")
ElseIf txt1.Text = "" Or txt2.Text = "" Or txt3.Text = "" Or txt4.Text = "" Or txt5.Text = "" Or txt6.Text = "" Or txt7.Text = "" Or txt8.Text = "" Or txt9.Text = "" Or txt10.Text = "" Or txt11.Text = "" Or txt12.Text = "" Or txt13.Text = "" Or txt14.Text = "" Or txt15.Text = "" Or txt16.Text = "" Or txt17.Text = "" Or txt18.Text = "" Or txt19.Text = "" Or txt20.Text = "" Or txt21.Text = "" Or txt22.Text = "" Or txt23.Text = "" Or txt24.Text = "" Or txt25.Text = "" Or txt26.Text = "" Then
MsgBox("Incomplete data")
Else
cnew.Text = "Save"
cnew.Text = "New"
cedit.Text = "Update"
save()
clear()
lock()
cdel.Enabled = True
csearch.Enabled = True
End If
txt2.Focus()
End Sub
Private Sub save()
Call main()
Con.Open()
Dim adapter As New SqlDataAdapter
Dim sql As String
sql = "insert into DOrproduct (rcv_reference,customer,expected_date,date_arrived,time_arrived,customer_bol,reference,cust_order,releaseno,carrier,trailerno,issuedto,date_rcv,product,accounting,trader,lot,pkg,supply_rcv,status,opened_by,odate,arrivedby,adate,closeby,cdate) values('" & txt1.Text & "','" & txt2.Text & "', '" & txt3.Text & "', '" & txt4.Text & "', '" & txt5.Text & "', '" & txt6.Text & "', '" & txt7.Text & "', '" & txt8.Text & "', '" & txt9.Text & "', '" & txt10.Text & "', '" & txt11.Text & "', '" & txt12.Text & "', '" & txt13.Text & "', '" & txt14.Text & "', '" & txt15.Text & "', '" & txt16.Text & "', '" & txt17.Text & "', '" & txt18.Text & "', '" & txt19.Text & "', '" & txt20.Text & "', '" & txt21.Text & "', '" & txt22.Text & "', '" & txt23.Text & "', '" & txt24.Text & "', '" & txt25.Text & "', '" & txt26.Text & "')"
Try
adapter.InsertCommand = New SqlCommand(sql, Con)
adapter.InsertCommand.ExecuteNonQuery()
MsgBox("Row inserted !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Con.Close()
End Sub
Private Sub clear()
'txt1.Text = ""
txt2.Text = ""
txt3.Text = ""
txt4.Text = ""
txt5.Text = ""
txt6.Text = ""
txt7.Text = ""
txt8.Text = ""
txt9.Text = ""
txt10.Text = ""
txt11.Text = ""
txt12.Text = ""
txt13.Text = ""
txt14.Text = ""
txt15.Text = ""
txt16.Text = ""
txt17.Text = ""
txt18.Text = ""
txt19.Text = ""
txt20.Text = ""
txt21.Text = ""
txt22.Text = ""
txt23.Text = ""
txt24.Text = ""
txt25.Text = ""
txt26.Text = ""
End Sub
Private Sub unlock()
txt1.Enabled = True
txt2.Enabled = True
txt3.Enabled = True
txt4.Enabled = True
txt5.Enabled = True
txt6.Enabled = True
txt7.Enabled = True
txt8.Enabled = True
txt9.Enabled = True
txt10.Enabled = True
txt11.Enabled = True
txt12.Enabled = True
txt13.Enabled = True
txt14.Enabled = True
txt15.Enabled = True
txt16.Enabled = True
txt17.Enabled = True
txt18.Enabled = True
txt19.Enabled = True
txt20.Enabled = True
txt21.Enabled = True
txt22.Enabled = True
txt23.Enabled = True
txt24.Enabled = True
txt25.Enabled = True
txt26.Enabled = True
End Sub
Private Sub lock()
txt1.Enabled = False
txt2.Enabled = False
txt3.Enabled = False
txt4.Enabled = False
txt5.Enabled = False
txt6.Enabled = False
txt7.Enabled = False
txt8.Enabled = False
txt9.Enabled = False
txt10.Enabled = False
txt11.Enabled = False
txt12.Enabled = False
txt13.Enabled = False
txt14.Enabled = False
txt15.Enabled = False
txt16.Enabled = False
txt17.Enabled = False
txt18.Enabled = False
txt19.Enabled = False
txt20.Enabled = False
txt21.Enabled = False
txt22.Enabled = False
txt23.Enabled = False
txt24.Enabled = False
txt25.Enabled = False
txt26.Enabled = False
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Dim frm As New frmRID
frm.Show()
frm.MdiParent = frmMain
Me.Hide()
End Sub
Private Sub locked()
txt1.Enabled = True
txt2.Enabled = True
End Sub
Private Sub unlocked()
txt1.Enabled = False
txt1.Enabled = False
End Sub
Private Sub cedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cedit.Click
If cedit.Text = "Update" Then
If txt1.Text = "" Or txt2.Text = "" Or txt3.Text = "" Or txt4.Text = "" Or txt5.Text = "" Or txt6.Text = "" Or txt7.Text = "" Or txt8.Text = "" Or txt9.Text = "" Or txt10.Text = "" Or txt11.Text = "" Or txt12.Text = "" Or txt13.Text = "" Or txt14.Text = "" Or txt15.Text = "" Or txt16.Text = "" Or txt17.Text = "" Or txt18.Text = "" Or txt19.Text = "" Or txt20.Text = "" Or txt21.Text = "" Or txt22.Text = "" Or txt23.Text = "" Or txt24.Text = "" Or txt25.Text = "" Or txt26.Text = "" Then
MsgBox("you have no record to update please click command Search before update")
Else
updatesave()
csearch.Text = "Search"
clear()
lock()
End If
ElseIf cedit.Text = "Cancel" Then
cnew.Text = "New"
cedit.Text = "Update"
lock()
clear()
cdel.Enabled = True
csearch.Enabled = True
End If
End Sub
Private Sub updatesave()
Call main()
Con.Open()
Dim adapter As New SqlDataAdapter
Dim sql As String
sql = "update DOrproduct set rcv_reference ='" & txt1.Text & "', customer='" & txt2.Text & "', expected_date='" & txt3.Text & "', date_arrived='" & txt4.Text & "', time_arrived='" & txt5.Text & "', customer_bol='" & txt6.Text & "', reference='" & txt7.Text & "', cust_order='" & txt8.Text & "', releaseno='" & txt9.Text & "' ,carrier='" & txt10.Text & "' ,trailerno='" & txt11.Text & "' ,issuedto='" & txt12.Text & "' ,date_rcv='" & txt13.Text & "' ,product='" & txt14.Text & "' ,accounting='" & txt15.Text & "' ,trader='" & txt16.Text & "' ,lot='" & txt17.Text & "' ,pkg='" & txt18.Text & "' ,supply_rcv='" & txt19.Text & "' ,status='" & txt20.Text & "' ,opened_by='" & txt21.Text & "' ,odate='" & txt22.Text & "' ,arrivedby='" & txt23.Text & "' ,adate='" & txt24.Text & "' ,closeby='" & txt25.Text & "', cdate='" & txt26.Text & "' where rcv_reference ='" & txt1.Text & "'"
Try
adapter.UpdateCommand = Con.CreateCommand
adapter.UpdateCommand.CommandText = sql
adapter.UpdateCommand.ExecuteNonQuery()
MsgBox("records updated !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Con.Close()
End Sub
Private Sub csearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles csearch.Click
unlock()
If csearch.Text = "Search" Then
csearch.Text = "Cancel"
cnew.Enabled = False
txt1.Focus()
ElseIf csearch.Text = "Cancel" Then
csearch.Text = "Search"
cnew.Enabled = True
clear()
lock()
End If
End Sub
Private Sub cdel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cdel.Click
On Error GoTo err
If cdel.Text = "Delete" Then
If txt1.Text = "" Or txt2.Text = "" Or txt3.Text = "" Or txt4.Text = "" Or txt5.Text = "" Or txt6.Text = "" Or txt7.Text = "" Or txt8.Text = "" Or txt9.Text = "" Or txt10.Text = "" Or txt11.Text = "" Or txt12.Text = "" Or txt13.Text = "" Or txt14.Text = "" Or txt15.Text = "" Or txt16.Text = "" Or txt17.Text = "" Or txt18.Text = "" Or txt19.Text = "" Or txt20.Text = "" Or txt21.Text = "" Or txt22.Text = "" Or txt23.Text = "" Or txt24.Text = "" Or txt25.Text = "" Or txt26.Text = "" Then
MsgBox("you have no record to Delete please click command Search before update")
Else : cdel.Text = "Delete"
Select Case MsgBox("Are you sure You want to Delete this Record", MsgBoxStyle.YesNo)
Case MsgBoxResult.Yes
deleterecord()
csearch.Text = "Search"
clear()
lock()
Case MsgBoxResult.No
GoTo err
End Select
End If
End If
错误：退出小组结束子
Private Sub deleterecord()
Call main()
Con.Open()
Dim adapter As New SqlDataAdapter
Dim sql As String
sql = "delete DOrproduct where rcv_reference ='" & txt1.Text & "'"
Try
adapter.DeleteCommand = Con.CreateCommand
adapter.DeleteCommand.CommandText = sql
adapter.DeleteCommand.ExecuteNonQuery()
MsgBox("Records deleted !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Con.Close()
End Sub
Private Sub txt1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt1.TextChanged
searchrcv_reference()
End Sub
Private Sub searchrcv_reference()
Call main()
Con.Open()
Dim cmd As New SqlCommand("SELECT rcv_reference FROM DOrproduct", Con)
Dim ds As New DataSet
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "My List") 'list can be any name u want
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)("rcv_reference").ToString())
If txt1.Text = True Then
showsearch()
End If
Next
txt1.AutoCompleteSource = AutoCompleteSource.CustomSource
txt1.AutoCompleteCustomSource = col
txt1.AutoCompleteMode = AutoCompleteMode.Suggest
Con.Close()
End Sub
Private Sub showsearch()
Call main()
Con.Open()
Dim UserSelect As SqlCommand
Dim myreader As SqlDataReader
Dim sql As String
sql = "select * from DOrproduct where rcv_reference like '" & (txt1.Text) & "%'"
UserSelect = New SqlCommand(sql, Con)
myreader = UserSelect.ExecuteReader
If (myreader.Read = True) Then
'txt1.Text = myreader(0)
txt2.Text = myreader(1)
txt3.Text = myreader(2)
txt4.Text = myreader(3)
txt5.Text = myreader(4)
txt6.Text = myreader(5)
txt7.Text = myreader(6)
txt8.Text = myreader(7)
txt9.Text = myreader(8)
txt10.Text = myreader(9)
txt11.Text = myreader(10)
txt12.Text = myreader(11)
txt13.Text = myreader(12)
txt14.Text = myreader(13)
txt15.Text = myreader(14)
txt16.Text = myreader(15)
txt17.Text = myreader(16)
txt18.Text = myreader(17)
txt19.Text = myreader(18)
txt20.Text = myreader(19)
txt21.Text = myreader(20)
txt22.Text = myreader(21)
txt23.Text = myreader(22)
txt24.Text = myreader(23)
txt25.Text = myreader(24)
txt26.Text = myreader(25)
End If
Con.Close()
End Sub
Private Sub txt2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt2.TextChanged
showCustomer()
End Sub
Private Sub showCustomer()
Call main()
Con.Open()
Dim cmd As New SqlCommand("SELECT customer_name,customer_code FROM packwell_customers", Con)
Dim ds As New DataSet
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "My List") 'list can be any name u want
Dim col2 As New AutoCompleteStringCollection
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
col2.Add(ds.Tables(0).Rows(i)("customer_code").ToString())
col2.Add(ds.Tables(0).Rows(i)("customer_name").ToString())
Next
txt2.AutoCompleteSource = AutoCompleteSource.CustomSource
txt2.AutoCompleteCustomSource = col2
txt2.AutoCompleteMode = AutoCompleteMode.Suggest
Con.Close()
End Sub
末级
该代码是一个有点难以阅读，但是从我能理解，它应该工作。 你只需要放的SubmitChanges（）方法调用外的for循环。
