-
Oct 9th, 2018, 06:42 AM
#1
Thread Starter
Addicted Member
[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.
Last edited by FunkyDexter; Oct 9th, 2018 at 08:49 AM.
-
Oct 9th, 2018, 07:03 AM
#2
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) ;"
-
Oct 9th, 2018, 07:35 AM
#3
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.
-
Oct 9th, 2018, 07:37 AM
#4
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?
-
Oct 9th, 2018, 07:49 AM
#5
Thread Starter
Addicted Member
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
-
Oct 9th, 2018, 08:20 AM
#6
Re: SQL command error
Originally Posted by GideonE
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?)
-
Oct 9th, 2018, 08:55 AM
#7
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Oct 9th, 2018, 09:22 AM
#8
Thread Starter
Addicted Member
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) ;
-
Oct 9th, 2018, 09:32 AM
#9
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
-
Oct 9th, 2018, 09:32 AM
#10
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.
-
Oct 10th, 2018, 02:02 AM
#11
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Oct 18th, 2018, 06:18 AM
#12
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|