Clever Geek Handbook

How to edit / update records from database using linq to sql textbox?

I am using visual base 2008 Express from linq to sql for my database work like edit entries. I haven't used any sql server, but I'm just using the built-in sql server in Visual Basic 2008 Express. I tried to revise the codes, there is no syntax error, but there is a runtime error and it pops up a window message with an error message. I want to be able to edit the records that were retrieved from the database into textboxes and when you press button5, no matter what new value in the textboxes should replace the previous one. Account field- this field in my table 1 in memrec.dbml that I configured for the primary key is true and the rest of the fields are false in its primary key. The code below still encountered an error when starting the program and it pops up a window that says:

The NotSupportedException was unhandled . Sql Server does not handle comparing NText, Text, Xml, or Image data types.

It highlights a yellow background on the line:

db.SubmitChanges()

This is what I see in every property of the field in table1 in the memrec.dbml properties window:

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.

What do you see as a possible error?

Is this in terms of settings?

Here's my codes:

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

Thanks for taking the time with me to resolve this issue .....

+2


source to share


3 answers


Where memrec.Account.Contains (accnt) _

This code will execute "as" on your database, and this action is not allowed on the NText

and field Text

.

Try changing the field type from Text

to Varchar(500)

(or more if you think 500 won't be enough)



or

If you intended to find a known value, try changing it where:

Where memrec.Account = accnt
+2


source


Imports System.Data Imports System.Data.SqlClient

Public class 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


ERR: Exit Sub End Sub

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

End class

+1


source


The code is a bit hard to read, but from what I could understand, it should work. You just need to place the call to the SubmitChanges () method outside of the for loop.

0


source






More articles:


All Articles