[RESOLVED] SQL command error

I have the following VB.NET code :

VB.Net Code:

Dim kode As String = ""
        Dim beskrywing As String = ""
 
 
        If Me.SlaagCheckBox.Checked = True Then
          
            kode = kode & "GrSl"
            beskrywing = beskrywing & "Slaag"
 
            opdragskepkode.Connection = konneksie
            opdragskepkode.CommandText = "UPDATE skedulering2 " &
                                    "SET GroeperingsKode        = CONCAT( ''," & kode & ") , " &
                                    "GroepperingsBeskrywing     = CONCAT( ''," & beskrywing & ") ;"
      
 
 
            opdragskepkode.ExecuteNonQuery()
        
        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.

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) ;"

---

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.

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?

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

Quote:

---

Originally Posted by GideonE

The SET GroeperingsKode works,

---

In that case it seems that your database table does have a field called GrSl.

Quote:

---

but the error occurs when I try to set GroeperingsBeskrywing.

---

Quote:

---

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?)

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.

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) ;

---

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.

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.

Quote:

---

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.

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