Update Field In SQL Server

[Paytor]

Hello!

I have a field in the database where i store the size of some images. The field's type is float. I wan't to be able to update these numbers but i get:Error converting datatype varchar to float. Why?

This is the code i use:

str = "UPDATE Table1 SET"
str = str & ", Size = '" & (Text1.Text) & "' "
str = str & " WHERE Id = " & (Text2.Text)

Any ideas? Thank's!

[szlamany]

The comma in front of SIZE is not required. Commas are only needed if you are going to update two or more fields.

[szlamany]

btw - SIZE is a reserved word. You really should avoid that as a column name.

[Paytor]

Ok i named the field tSize. The part i posted is only a part of my update code. I also update the name of the picture and some other attributes. The thing is that the code i use work's in Ms Access where the tSize is double but in SQL Server where the field is float i get the error i posted.

I also tryed this but it doesn't work either. I get another error.

str = "UPDATE Table1 SET"
str = str & ", Size = " & (Text1.Text)
str = str & " WHERE Id = " & (Text2.Text)

Any ideas? Thank's!

[szlamany]

I made two posts - did you not see the first one?

You have this:

str = "UPDATE Table1 SET"
str = str & ", Size = " & (Text1.Text)
str = str & " WHERE Id = " & (Text2.Text)

The COMMA IN FRONT OF SIZE does not belong.

It should be:

str = "UPDATE Table1 SET"
str = str & " Size = " & (Text1.Text)
str = str & " WHERE Id = " & (Text2.Text)

Notice the COMMA is removed from the " SIZE = " string - but the space remains.

The reason for the CONVERT FROM VARCHAR error was because you had single-quotes wrapping around the numeric value - this is from your first post:

str = str & ", Size = '" & (Text1.Text) & "' "

Those single quotes make no sense whatsoever - wrapping a value in single quotes in a SQL string is only done for character data.

[Paytor]

In the update query i leave the comma out only for the first update. The comma is used because i'm making i very large update query with all the attributes that i update. Before the size i have other updates that i make and that's why i had the comma before it.

Let's leave the comma out. This is not the problem. Even if i make it without the single quotes i still get error:Incorect syntax near '58'.

str = str & " Size = " & (Text1.Text)

I have tryed without singe quotes before->see post#4

I don't know where the problem is. Why does it work in Access where the size in the table is type double and in SQL Server where the size is type float it doesn't work?

[szlamany]

Are you still using the SIZE as a column name?

Do a DEBUG.PRINT of the STR variable and then paste that here.

[Paytor]

No i use tSize now!

[Paytor]

If leave the single quotes like this:

:str = str & " tSize = '" & (Text1.Text) & "' "

then it works in MS Access where the tSize is double but in SQL Server 2000 where the tSize is float i get the error i posted in the beggining. Why is that? I don't get it. Don't know what to do!!!

[szlamany]

Do a DEBUG.PRINT of the STR variable and then paste that here.

Please!!

And the whole string - not a cut down version of it...

[szlamany]

If leave the single quotes like this:

:str = str & " tSize = '" & (Text1.Text) & "' "

then it works in MS Access where the tSize is double but in SQL Server 2000 where the tSize is float i get the error i posted in the beggining. Why is that? I don't get it. Don't know what to do!!!

If feel like I am going in circles and it just doesn't make any sense anymore!

Never wrap a numeric value that's going into a numeric datatype in single quotes.

Who cares why ACCESS allows you to make a blunder like that. Simply do not ever do it! It's not proper form!!

We've already gone over this.

Take the single quotes off and if it works in ACCESS without them then never put them back again.

MS SQL is stricter when it comes to some forms of datatype coercion. VARCHAR() cannot be converted to MONEY implicitly, for instance. I would imagine that it cannot be converted to FLOAT either - that's what your error suggests.

[si_the_geek]

If leave the single quotes like this:
...
then it works in MS Access where the tSize is double but in SQL Server 2000 where the tSize is float i get the error i posted in the beggining. Why is that?

That's because Access is nice to newbies.. it realises that you did something silly, and it automatically converts the data type for you.

szlamany already explained it, but as you didn't seem to notice/understand, I strongly suggest that you read this FAQ article, which explains how to put values into SQL statements.

Note that what you are doing there is similar to this in VB:

Dim i as Integer
  i = "37"  'should be [u]i = 37[/u] !!

edit: over 7 miniutes? I was a bit slow!

[Paytor]

I do undesrtand what you are saying! It doesn't work either without the single quotes!

str = str & " tSize = " & (Text1.Text)

I said it many times! That's why i'm so confused. If i make the tSize integer then it work's like this. But with float it doesn't? Is this not strange?

I will post the whole STR as szlamany wants a little bit later!

Thank's!

[si_the_geek]

I do undesrtand what you are saying! It doesn't work either without the single quotes!

The fact that you put them back in after being told to take them out is what is worrying us - having them in there in the first place was worrying enough on its own.

If i make the tSize integer then it work's like this. But with float it doesn't? Is this not strange?

It is strange.. but depending on regional settings (and what you have entered in the textbox) it can certainly be understandable.

I will post the whole STR as szlamany wants a little bit later!

That will probably make the issue(s) obvious to us, including what I mentioned above (if it is actually an issue here).

[szlamany]

I was burned once when a Spanish teacher set her PC to Spain settings - decimals became commas - commas became periods - .4 was getting posted at 4 for an attendance value (or something like that)...

At any rate - post that SQL string

and tell us the actual datatypes you have

And I'll test your SQL right on my laptop here for you!

[Paytor]

I found where te mistake is.

When i store the picture size i use the functions Round and FileLen to calculate the size of the picture. I used round to so i don't have 4,678950 but only the first two digits 4,68. The problem is that the size is stored into the database field(double in Access and float in SQL Server) with the comma like this: 4,68 so when i try to update it i get the error:Incorect syntax near '58'.

So i tryed to change the round function without calculating the digits after the comma and if a the actual size is 4,78 i will store-> 5. Now it work's. I didn't change anything else. The fields type is still double in Access and float in SQL Server.

Now that we know where the mistake is there anything that i can do so that the update works when i store the exact size of a picture with the 2 first digits after the comma? I wonder if there wasn't a comma between them and only a dot like this->4.78 would i still get the error? But how can i store it in this form(with a dot between them)?

Thank's!

[si_the_geek]

If you had posted the actual SQL string (as requested multiple times, and you should have done initially), we would probably have noticed that straight away. If you don't know how to do that, see this FAQ thread.

After a quick look in VB's help, it seems that the answer may simply be to use the built-in conversion functions - in this case CStr. If not, the Format function may work.

[Paytor]

Thank's i will try it! If not i will round the number to the nearest integer so the 4,67 becomes 5.

[szlamany]

You do realize that the , is not the standard decimal separator - right?

Your MS SQL server is not liking that character - you do understand that - right?

[Paytor]

I understand that.

I mean i will round the number before i save it in the database. So it is stored without the comma,! That's what i mean!

[si_the_geek]

There is absolutely no need to round it beyond what you ideally want (2 decimal places). If my suggestions don't solve it, there are several other (less efficient) options that will.

[szlamany]

I certainly don't want to offend - but rounding the value is "working" around a problem. The issue is the , or . decimal character - you really should overcome that issue and not simply get around it by avoiding it.

It will help you understand your regional setting issues better for future work.

[Paytor]

ok i will try what you suggested and let you know!

Thank's!

[Paytor]

I don't know where exactly to pu the format or CStr functions. Do you mean something like this:

str = str & " tSize = " & CStr((Text1.Text)) -> But the text has number and not String that this function requires.

What do you think?

[szlamany]

If MS SQL wants a period as a decimal separator and your textbox/users are putting a comma - how about:

str = str & " tSize = " & Replace(Text1.Text, "," ,".")

[Paytor]

i will try it thank you!