Results 1 to 12 of 12

Thread: [RESOLVED] SQL command error

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2016
    Posts
    58

    Resolved [RESOLVED] SQL command error

    I have the following VB.NET code :

    VB.Net Code:
    1. Dim kode As String = ""
    2.         Dim beskrywing As String = ""
    3.  
    4.  
    5.         If Me.SlaagCheckBox.Checked = True Then
    6.          
    7.             kode = kode & "GrSl"
    8.             beskrywing = beskrywing & "Slaag"
    9.  
    10.             opdragskepkode.Connection = konneksie
    11.             opdragskepkode.CommandText = "UPDATE skedulering2 " &
    12.                                     "SET GroeperingsKode        = CONCAT( ''," & kode & ") , " &
    13.                                     "GroepperingsBeskrywing     = CONCAT( ''," & beskrywing & ") ;"
    14.      
    15.  
    16.  
    17.             opdragskepkode.ExecuteNonQuery()
    18.        
    19.         End If

    When I run my application I get the following error : Invalid column name 'Slaag'.

    For the life of me I cannot see my mistake. Any help would be much appreciated.
    Last edited by FunkyDexter; Oct 9th, 2018 at 08:49 AM.

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

    Re: SQL command error

    Does the database table skedulering2 have a field called Slaag?


    By the way, the variables kode and beskrywing don't seem to have a purpose, they just make the code harder to read. I recommend removing them, and altering the .CommandText to include the values directly, ie:
    Code:
                opdragskepkode.CommandText = "UPDATE skedulering2 " &
                                        "SET GroeperingsKode        = CONCAT( '', GrSl) , " &
                                        "GroepperingsBeskrywing     = CONCAT( '', Slaag) ;"

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,188

    Re: SQL command error

    Why don't you try displaying the actual commandtext either in a message box or via a debug statement so you can see the actual contents.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    105,037

    Re: SQL command error

    How about you take a look - and give us a look - at the actual SQL code that is being executed, rather than just looking at the code that constructs it? If the SQL doesn't work then it probably isn't what you intended it to be, but how would you know if you never look at it?

  5. #5

    Thread Starter
    Member
    Join Date
    Nov 2016
    Posts
    58

    Re: SQL command error

    Thank you Si. No the database table does not have a field called Slaag. The SET GroeperingsKode works, but the error occurs when I try to set GroeperingsBeskrywing.

    Also thank you for the tip, but there are other if statements in which I keep adding to kode and beskrywing. So although the variables seems unnecessary they are not.

    Regards

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

    Re: SQL command error

    Quote Originally Posted by GideonE View Post
    The SET GroeperingsKode works,
    In that case it seems that your database table does have a field called GrSl.

    but the error occurs when I try to set GroeperingsBeskrywing.
    No the database table does not have a field called Slaag.
    In that case why are you putting it into the SQL statement as if it is one?

    What were you actually trying to achieve there? (is it perhaps a variable in your VB code?)

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: SQL command error

    I added code tags to your post. It just makes it easier to read. To do this you use the little "VB" button above the post box and enter a highlight style of "VB.Net"

    I think your problem is that you're not wrapping kode and GroepperingsBeskrywing in single quotes. They're clearly strings in your code so they need to be "wrapped" in single quotes to be interpreted as proper sql.

    There're some other smells here though. Why are you using CONCAT to add a space - you could just add a space to the value of the variable? You've got no Where clause - do you really mean to update every record in the table? You're initialising the variables as empty strings and then immediately concatenating a string literal onto them - why not just initialise them with the value you want?

    I think you could do with stepping back from your code for a second and letting us know what the goal is. Chances are we can give you a much more elegant solution to the one you're currently groping towards.


    edit>Also, what database is this? I've assumed sql server but it's best to be sure or we'll give bad advice.
    Last edited by FunkyDexter; Oct 9th, 2018 at 09:05 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8

    Thread Starter
    Member
    Join Date
    Nov 2016
    Posts
    58

    Re: SQL command error

    If I use the following SQL code, I also get a Invalid column name 'Slaag' error :

    Code:
      UPDATE skedulering2
    
     SET   GroeperingsKode       = CONCAT( ''  ,GrSL) ,   GroepperingsBeskrywing    = CONCAT( ''  ,Slaag) ;

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,079

    Re: SQL command error

    Right, but you ALSO get a string.

    Put a breakpoint on this line:

    opdragskepkode.ExecuteNonQuery()

    When execution stops on that line, take a look at what is in opdragskepkode.CommandText. If you don't see any immediate problem, then post the contents of opdragskepkode.CommandText. Most likely, when you look at what actually ends up in the CommandText, you will realize that it isn't what you thought it would be, and will see the problem. If not, we can likely help. We all need to see what's in CommandText, though.

    I also don't understand why you are bothering with the CONCAT function, as it doesn't seem to do anything useful in this case, but that shouldn't really matter.
    My usual boring signature: Nothing

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

    Re: SQL command error

    That seems to be the same SQL statement you were using previously, so getting the same results isn't surprising.

    I recommend responding to the questions in posts #6 and #7, so that we can understand what is actually going on here, and help you get this working.

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: SQL command error

    If I use the following SQL code, I also get a Invalid column name 'Slaag' error
    That is because you haven't wrapped GrSL and Slaag in single quotes.

    For simplicity, let's remove the CONCAT for a second and look again:-
    Code:
    UPDATE skedulering2
     SET   GroeperingsKode       = GrSL ,   GroepperingsBeskrywing    = Slaag
    Anything on that Set line that's not wrapped in single quotes will be interpreted as a column name. So can you see how, in that sql statement, GrSL and Slaag would be interpreted as column names rather than strings? So the fact that SQL is telling you there isn't a column called Slaag is not surprising.

    What is slightly surprising is that you're not getting the same error on GrSL... but not really. I imagine you simply have a column called GrSl. You don't, however have one called Slaag.

    So you're sql statement, as it stands, is trying to set the value in column GroeperingsKode to the value in column GrSL and the value in column GroepperingsBeskrywing to the value in column Slaag - which doesn't exist.




    So that's why you're getting the error but doesn't really tell you how to fix it. But to tell you how to fix it we need to understand the goal. Are you trying to set one column to the value of another? Or are you trying to set the value in a column to a single, fixed value? Take a step back from your code and tell us what the goal is. Then we can tell you how to achieve it.

    And just as importantly: do you really mean to update every row in the table? It's possible that you do but it would quite unusual and, if you're making a mistake there, it's a potentially big one. If you're running this into a production environment then you're potentially about to shoot yourself in the face and I'd rather show you how to put the safety on than show you how to pull the trigger.
    Last edited by FunkyDexter; Oct 10th, 2018 at 02:57 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  12. #12

    Thread Starter
    Member
    Join Date
    Nov 2016
    Posts
    58

    Re: SQL command error

    Hi FunkyDexter. Thank you using single quotes was the answer I was looking for.

Tags for this Thread

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