SQLTeam.com | Weblogs | Forums

Possible problem with sql2010express


#1

Hello Everyone,

Having a problem i can do this in the server

select * from table_name where column_name between 29 and 37

this displays the correct number of rows.

when i try to do this via vb.net2013 community version, i get this error

Conversion from string "select * from petrol_table where" to type 'Long' is not valid.

Dim DA As SqlDataAdapter = New SqlDataAdapter("select * from petrol_table where Total_liters between " & LR1 And LR2, CON)

Am i doing something wrong.?

Thanks for reading


#2

My guess is that you need to CONVERT the LR1 and LR2 from a Long datatype (native to VB.Net) into a String, and you need to treat "AND" as a String

"select * from petrol_table where Total_liters between " & ConvertLongToString(LR1) & " And  " & ConvertLongToString(LR2)

Sorry, I don't know what the correct function name is for ConvertLongToString() in VB.Net


#3

P.S> Strongly recommend you NEVER use "SELECT *" but instead use a list of all (and only) the columns that you need. There are all sorts of future-problems with using SELECT * which will cause you huge amounts of grief, fixing them, in the future.


#4

Thank you for your answer, found the problem..heheeh

Dim DA As SqlDataAdapter = New SqlDataAdapter("select * from petrol_table where Total_liters between " & SR1 & " And " & SR2, CON)

changed this line to the above and it works....will consider changing the select option..

Thanks again for you reply.

BTW the var.SR1 & SR2 i changed because i thought they might be the problem. os SR1 & SR2 are strings and it works.


#5

hi Kristen,

i use the select * , because i want to show the whole Row of the DataBase.

Rdgs.


#6

I had a client who did that. Someone decided to add "Notes" to every table in the database, their call centre used the notes and found them very helpful. After a few months performance fell and they could not work out why, they paid me a lot of money to tell them it was because they were using SELECT * in their code which was now included the (huge) NOTES columns - which were unused in (most) places in the application. So SQL was retrieving it from the database, passing it to the APP and the APP was ignoring it because it wasn't needed.

If you need all 10 (say) columns now, name them. If you add a column change the application to add its name ONLY where it is actually used.

Other problem I have seen is where someone decides that the sequence of columns in the table should be changed to group columns "Logically" together in the table. Then the SELECT * presents the columns in the new order - and whatever is receiving the columns gets them in an unexpected order and raised error or does something unexpected. (Of course you can specify columns from a resultset by name, in your APP, but that is generally slower than using an ordinal position, so more often ordinal position is used.

If you retrieve a column that is not used in your APP then SQL will still retrieve it from the database. Also, SQL may not use some indexes as it cannot get All Columns from the index (whereas if you said SELECT Col1, Col2 (only) and there was an index that had both columns then SQL would use that - much faster to get that from the index than actually getting the row from the database table itself.

There are legitimate reasons for using SELECT * - for example "Export this table, all columns, whatever they are, in whatever order they might be", but that's about the only legitimate situation I can think of just now :slight_smile:


#7

Thanks for the tip. :slight_smile: