0

I'm trying to retrieve the last ID, in a table, using SQL query and then append the result together with a TextBox value (which is a String):

Dim searchforID = New OleDbCommand("select ID from [table1] where ID = (select max(id) from [table1])", con)
Dim variable1 = searchforID + TextBox1.Text

But it throws this error:

Compiler Error Message: BC30452: Operator '+' is not defined for types 'System.Data.OleDb.OleDbCommand' and 'String'.


Trying to fix it

-- So i tried to convert the returned record ID like this: 
Dim variable1 = Convert.ToString(searchforID) + TextBox1.Text

There is no error now but unfortunately the result returned is "System.Data.OleDb.OleDb" + the textbox value, as this example:
System.Data.OleDb.OleDbtest

-- I too thought to convert the returned record ID to integer throws an error:

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.OleDb.OleDbCommand' to type 'System.IConvertible'.

So why does retrieving the last record ID fail? Might my SQL query be false?

Thank you.

So straight forward solution for problem if someone may find it useful:

Dim searchforID = New OleDbCommand("select MAX(ID) from [table1]", con)
Dim variable1 = searchforID.ExecuteScalar() & TextBox1.Text

Also the answers and comments below have useful information.

compliance
  • 365
  • 7
  • 23
  • 2
    you cant do like this. You need to return Id from table using execute scalar command – DevelopmentIsMyPassion Mar 06 '13 at 10:29
  • 1
    `searchforID` is an `OleDbCommand` which you must execute against the database. You must retrieve the _result_ of the query, not the query itself. – John Willemse Mar 06 '13 at 10:30
  • @AshReva You hit the point. I understand that ExecuteScalar is good for a one result, do you know if there are other methods than ExecuteScalar that do the same job too? – compliance Mar 06 '13 at 11:02
  • @JohnWillemse Thanks so much, this makes sense, so the _Command_ (searchforID) is actually the query and I must execute this _command_ to return a readable result (string). is that right? – compliance Mar 06 '13 at 11:08
  • Yes, that's how it works. The two answers to your question explain this in detail and give good examples. – John Willemse Mar 06 '13 at 11:32

2 Answers2

3
Dim variable1 = searchforID.ExecuteScalar() & TextBox1.Text

searchforID is ICommand, not a value, if you want to get a result, you need to execute the command and get result. ExecuteScalar returns first cell value of the first row from query

Arsen Mkrtchyan
  • 49,896
  • 32
  • 148
  • 184
  • Thanks so much, there was no need to convert, just adding this `ExecuteScalar` did the job, I wouldn't know that. But if I understand it right, is the type of the result returned with `ExecuteScalar` a String? In other words does it automatically convert the Command type to a string value to be able to work with? – compliance Mar 06 '13 at 10:58
  • @ArsentMkrt I would like to add something, please replace `+` in the answer with `&`. As I notice, with `+` an error is thrown `Input string was not in a correct format` unless i either use the ampersand `&` either i keep `+` but I convert searchforID command (as in Unicorn's answer). – compliance Mar 06 '13 at 11:21
  • also i wish i could mark two answers, yours is just what i need to get rid of the error and straight forward, but i thought to go with the other answer for the additional information (although i didn't need to return result to textbox). – compliance Mar 06 '13 at 11:47
  • @compliance, oops yes sorry, I am c# guy, that was the reason of my misprint. no problem about the answer ;) – Arsen Mkrtchyan Mar 06 '13 at 22:13
  • don't be sorry about the ampersand, thanks for your help so much. pst, i see we can't use `;` in VB. – compliance Mar 07 '13 at 20:49
1
Dim cmd = New OleDbCommand("select MAX(ID) from [table1]", con)
Dim variable1 = cmd.ExecuteScalar().ToString() & TextBox1.Text

First, if you have command, you should execute the command. There are several ways to execute a DbCommand, depending the type of the command.

Second, if you want the maximum id, go straight and query the maximum! The nested subquery is redundant.

Third, using & to concatenate strings is safer than +. See here for more information.

Community
  • 1
  • 1
Mohammad Dehghan
  • 17,853
  • 3
  • 55
  • 72
  • Thank you, ExecuteScalar() is just what it needed to work. Thank you anyway for the additional information: 1) about seeing result in the textbox, 2) about converting result to string. – compliance Mar 06 '13 at 10:50
  • But i have 2 questions please. is there a difference between `cmd.ExecuteScalar().ToString()` and `Convert.ToString(cmd.ExecuteScalar())`. Plus, I see there's no `cmd.ExecuteScalar().ToInt()` any idea why? Thank you. – compliance Mar 06 '13 at 10:54
  • @compliance There is actually no difference between them. `ToString` method is available on all the objects. There can be always a reason to convert some object to its string representation. This is not true for `int`. Converting to integer does **not** alwasy has a meaning. Also it may not be alwasy successful. – Mohammad Dehghan Mar 06 '13 at 11:00
  • Ok. Thank you too for the straight forward query. – compliance Mar 06 '13 at 11:49
  • Oh, that method before you edited it was a good information too (how to return result as a textbox value), anyway i thought why not add the following regarding converting types: i found `ctype(myVar, dataType)` so in my case, _if i need to convert_, i can convert command type like this `CType(searchforID.ExecuteScalar(), String)`. – compliance Mar 06 '13 at 13:09