DataType converting, char to float

I need to convert Data Types in a table where all fields are char, so that the ones containing numeric values is decimal or float.
I've tried this query:
SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.

Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..

Thanks.--Find bad values
select YourCol
from YourTable
where isnumeric(YourCol)=0

/*
Then choose, what numeric type do you want.
Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
rounded up to 1+4n bytes.
Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
*/

--if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)

--Sending test values would be your benefit.|||If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.

The following post discusses this problem:

post (http://dbforums.com/showthread.php?threadid=554550)|||Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
This is the code:

Dim InsertQuery As String
Set oConn = New Connection

Set oRec = New Recordset
oRec.Open InsertQuery, oConn

is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?

-jr|||You need to post an actual insert statement and the data types as defined by the table.|||InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"

Is a test-query I use

And the table is defined as follows:
Part - char - size 11 - Do not allow nulls
Serial - char - size 8 - Do not allow nulls
[Time] - datetime - Do not allow nulls
S0 - decimal - Precision: 2 - Do not allow nulls|||Your post has 2 main problems
1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
2. You use VB Now() function, which is setting-specific. Use getdate() on server.

create table testNum(
Part char (11) not null
,Serial char(8) not null
,[Time] datetime not null
,S0 decimal(15,2) not null
)
GO
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
--faster
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)|||What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.|||Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.|||Just one more thing..
When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?|||This will happen in enterprise manager - run a query in query analyzer and you will see them.

DataItem access in ascx

I am currently building an ecommerce site using aspx, vbscript, & sql.
In my productlist control containing a datalist I am trying to use an ifstatement to determine if products are from a certain supplier... andif they are; display a phone number to call for pricing rather than theprice, since this particular supplier doesnt allow pricing of theirproducts online.
This is what I have now:
<%
Dim objConn
objConn = Server.CreateObject("ADODB.Connection")
Dim strConnect
strConnect = ConfigurationSettings.AppSettings("ConnectionString")
Dim supplier
objConn.Open(strConnect)
supplier = objConn.Execute("Select SupplierID From Product")
If supplier = "C" Then
Response.Write("Call 1-800-POO-STAIN")
Else
Response.Write(" Sug. Retail:")
DataBinder.Eval(Container.DataItem, "Price")
End IF
objConn.Close
objConn = Nothing
%>
This code gives me the following error message:
The component 'ADODB.Connection' cannot be created. Apartment threaded components can only be created on pages with an <%@. Page aspcompat=true %> page directive

Can anybody help me get this working?
I haven't worked with VBScript for acouple years now and I'm a little rusty.
Any help would be greatly appreciated

What you have is Classic ASP code. You should change the extension to ASP, or convert the code to use VB.NET and the ADO.NET components.
Alternately, add this to the top of the page:
<%@. Page aspcompat=true %>
|||

The problem is I am using this in an ascx control. aspcompat is not supported by the 'control' directive.

Any other suggestions?

|||the page that will hold the ascx needs to have the aspcompat attribute.

Dataflow with 2 lookup using the same Table

Hi there

I'm getting into trouble everytime I want to setup a dataflow containing 2 or more flows, where I want to make a lookup into 1 other table.

This fails, of some reason I do not know of.

ex.

Table 1 -> Lookup Currency Table -> Table2 with currency
Table 3 -> Lookup Currency Table -> Table 4 with currency

One of the lookup will fail.

How do you avoid this?

/Bjarne

You meant, you have more than 1 source component in your data flow that goes independently of each other toward different lookup transformations?

How are you setting the lookup transformations? You shoud use a query to get only the columns you need and since the lookup table is used concurrently; you may want to use nolock hint to avoid deadlocks.

Are you actually receiving any error message?

Rafael Salas

DataBinding to radio button

I have a windows form containing some text boxes and radio buttons bount to an SQL Server database, with code like the following:

this.tbBorrowerLastName.DataBindings.Add (new Binding("Text", dsData.Tables["Results"],"BorrowerLastName"();

this.rbMale.DataBindings.Add(new Binding("Checked", dsData.Tables["Results"],"Male"))

If I add a new row and try to move there using the code below, nothing happens (neither movement to a new position nor error message), unless I remove the radio button bindings. (The same thing occurs if I attempt to move to any record that has a null value in the database field for a radio button).

myLastRow = dsData.Tables["Results"].NewRow();

dsData.Tables["Results"].Rows.Add(myLastRow);

I attempt to move by changing the value of the position property of the BindingContext object.

How can I retain the radio button bindings and still be able to add and move to a new row?

Any help would be appreciated.

I was able to figure my own solution.

The problem was a null value for the checked property of the radio buttons. I tried adding a default value to Visual Studio and to the database, but neither of those solved the problem.

Inbetween the two lines of code in my earlier post (beginning myLastRow and dsData.Tables respectively), I added:

myLastRow["Male"]=true;

myLastRow["Female"]=false;

Problem solved.