Results 1 to 8 of 8

Thread: [RESOLVED] Error when saving SQL view

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2017
    Posts
    48

    Resolved [RESOLVED] Error when saving SQL view

    Hi Guys


    I have some code to edit a SQL database. It works perfectly when I am accessing a database table, but one edit I want to make is in a database view. I can access the info in the view, populate my datagrid through my binding data source, but when I try to update the database "Adapter.Update(table)" I get this error...

    System.InvalidOperationException: 'Dynamic SQL generation is not supported against multiple base tables.'


    Relevant bits of code are below, I want to change a delaminated string and update just the "splitline(3)" value. Is there something I need to do differently for a database view?


    Code:
    Public Class
        Dim connection As New SqlConnection("Initial Catalog=Database;" & "Data Source=LocalHost;;User Id=;Password=;Integrated Security=SSPI;")
        Public table As New DataTable
        Dim sql As String
        Public AdapterAs New SqlDataAdapter
    
    
    
    
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            connection.Open()
    
            
            sql = "SELECT Distinct * FROM dbo.AllLabLotTest_VW"
    
    
    
            Adapter = New SqlDataAdapter(sql, connection)
    
            Dim builder As New SqlCommandBuilder(Adapter)
    
            Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            Adapter.Fill(table)
            BindingSource1.DataSource = table
            DataGridView1.DataSource = BindingSource1
    
            connection.Close()
    
            DataGridView1.Refresh()
    
    
        End Sub
    
    
    
       Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            Dim Lablottestinfo, NewLine As String
            Dim Splitline As String()
    
            x = 0
    
            For Each row As DataRow In table.Rows
    
                If IsDBNull(table.Rows(x).Item(11)) Then Exit Sub
    
                Lablottestinfo = table.Rows(x).Item(11)
                Splitline = Lablottestinfo.Split(New Char() {"|"c})
    
    
                NewLine = Splitline(0) & "|" & Splitline(1) & "|New Value|" & Splitline(3) & "|" & Splitline(4) & "|" & Splitline(5)
    
                table.Rows(x).Item(11) = NewLine
    
                x = x + 1
            Next
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'Save the changes.
            Adapter.Update(table)
        End Sub
    Last edited by NigeH; Sep 25th, 2021 at 07:18 AM.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,089

    Re: Error when saving SQL view

    By the sound of that error your view must have several tables and the UPDATE logic will not work.

    I haven't used a SqlDataAdapter in a while, but can't you create your own UPDATE STATEMENT for it so that it only updates the one table. Look into that.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Member
    Join Date
    Nov 2017
    Posts
    48

    Re: Error when saving SQL view

    Quite possibly.

    I've not done my own update statement before - will take a look.

  4. #4

    Thread Starter
    Member
    Join Date
    Nov 2017
    Posts
    48

    Re: Error when saving SQL view

    Has anyone got an example of an update statement I could try - struggling to get this to work.

    Most update statements I have found reply on a condition of the string in the column, but in my case it is different on every row as I want to update one part of the "|" delaminated string.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,734

    Re: Error when saving SQL view

    Update statements are generally in this format:
    Code:
    UPDATE tablename SET fieldToUpdate = newValue WHERE aUniqueField = valueOfTheUniqueField
    but in my case it is different on every row as I want to update one part of the "|" delaminated string.
    In that case you should use VB code to create a new value for that field (read the existing value, and use the string based functionality in VB to replace the appropriate part), and use that value in the Update statement.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,648

    Re: Error when saving SQL view

    I think that you mean "delimited" rather than "delaminated". The latter would mean something with the outer layer stripped off.

  7. #7

    Thread Starter
    Member
    Join Date
    Nov 2017
    Posts
    48

    Re: Error when saving SQL view

    Quote Originally Posted by jmcilhinney View Post
    I think that you mean "delimited" rather than "delaminated". The latter would mean something with the outer layer stripped off.
    OK, OK, well spotted.

  8. #8

    Thread Starter
    Member
    Join Date
    Nov 2017
    Posts
    48

    Re: Error when saving SQL view

    Quote Originally Posted by si_the_geek View Post
    Update statements are generally in this format:
    Code:
    UPDATE tablename SET fieldToUpdate = newValue WHERE aUniqueField = valueOfTheUniqueField
    Took me a few goes at getting the syntax right, but got there!


    In that case you should use VB code to create a new value for that field (read the existing value, and use the string based functionality in VB to replace the appropriate part), and use that value in the Update statement.
    I already had that bit, but I have now put the update command in the loop too.


    Code:
     Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            Dim OriginalString, NewString As String
            Dim Splitline As String()
            Dim UpdateQuery As String
    
            Dim connection2 As New SqlConnection("Initial Catalog=MyDB;" & "Data Source=LocalHost;;User Id=MyID;Password=MYPassword;Integrated Security=SSPI;")
            x = 0
    
            For Each row As DataRow In table.Rows
    
    
                If IsDBNull(table.Rows(x).Item(11)) Then Exit Sub
    
                OriginalString = table.Rows(x).Item(11)
                Splitline = OriginalString.Split(New Char() {"|"c})
    
    
                NewString = Splitline(0) & "|" & Splitline(1) & "|NewUpdatedValueHere|" & Splitline(3) & "|" & Splitline(4) & "|" & Splitline(5)
    
                table.Rows(x).Item(11) = NewString
    
                UpdateQuery = "Update dbo.AllLabLotTest_VW Set Lablottestinfo = '" & NewString & "' WHERE Lablottestinfo = '" & OriginalString & "'"
    
                Dim UpDateCMD As SqlCommand = New SqlCommand(UpdateQuery, connection2)
    
                connection2.Open()
                UpDateCMD.ExecuteNonQuery()
                connection2.Close()
                x = x + 1
            Next
    
    
        End Sub
    Thanks for the help :-)
    Last edited by NigeH; Sep 25th, 2021 at 08:07 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width