cast not valid

Hello:
I want to make a query that return all record that don't have one valid
cast, something like this:
select * from table where cast(field as bigint) is valid
how can I do that or some variant?. the real problem is to import some table
in a dts but when I try to convert the str to bigint, raise one execption,
that I want to jump and eliminate this record but continue, instead the dts
stop.
Best regards,
Owen.Owen wrote:
> Hello:
> I want to make a query that return all record that don't have one
> valid cast, something like this:
> select * from table where cast(field as bigint) is valid
> how can I do that or some variant?. the real problem is to import
> some table in a dts but when I try to convert the str to bigint,
> raise one execption, that I want to jump and eliminate this record
> but continue, instead the dts stop.
> Best regards,
> Owen.
CAST is not a BOOLEAN function. That is, it does not return whether a
value _can_ be converted from one type to another. It explicitly tries
to convert and throws an exception if a failure occurs. You could use
the ISNUMERIC() function or roll your own integer check function or use
the one here:
http://www.aspfaq.com/show.asp?id=2390
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SELECT *
FROM Table
WHERE (x NOT LIKE '%[^0-9]%'
AND LEN(x) BETWEEN 1 AND 18)
OR x IS NULL
David Portas
SQL Server MVP
--

Cast from string OPEN to type Double is not valid.

Hi.. Please help me resolve this error "Cast from string 'OPEN' to type 'Double' is not valid.". Error here If CallStatus = 10 Then ...
Code:
Public Sub UpdateCallStatus()
Dim CALLID, RequestorID, CommentsFromITD, MessageFromITD, MessageToITD, CallStatus, strSQL As String
CALLID = Request.QueryString("CallID")
RequestorID = Session("USER_ID")
CommentsFromITD = lblcomments.Text
MessageFromITD = lblmessage.Text
MessageToITD = txt_desc.Text
CallStatus = Trim(Request.Form(ddl_callstatus.UniqueID))

Dim ObjCmd As SqlCommand
Dim ObjDR As SqlDataReader

Try
If CallStatus = 10 Then
strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & ""
ObjCmd = New SqlCommand(strSQL, ObjConn)
ObjConn.Open()
ObjDR = ObjCmd.ExecuteScalar()
gbVariables.insertuserevents(CALLID, RequestorID, "Call Closed")
Response.Redirect("UserCallClosed.aspx")
ObjConn.Close()
Else
strSQL = "UPDATE CALLS SET STATUS_ID=" & CallStatus & " WHERE CALL_ID= " & CALLID & ""
ObjCmd = New SqlCommand(strSQL, ObjConn)
ObjConn.Open()
ObjDR = ObjCmd.ExecuteScalar()
ObjConn.Close()

strSQL = "SELECT STATUS_LABEL FROM STATUS WHERE STATUS_ID = " & CallStatus & ""
ObjCmd = New SqlCommand(strSQL, ObjConn)
ObjConn.Open()
ObjDR = ObjCmd.ExecuteScalar()
ObjConn.Close()

gbVariables.insertuserevents(CALLID, RequestorID, CallStatus)
CallStatus = ""
End If
Catch ex As Exception
lblmsg.Text = ex.Message.ToString
End Try
End Sub
Thanks...

Yes, you cannot compare string with Double values.

CallStatus = Trim(Request.Form(ddl_callstatus.UniqueID)) will return you a string ( unique id of the dropdownlist) and you are comparing it with double value (10) in the next if condition, which is wrong.

Check what value are you supposed to get in CallStatus, and with what value should you compare it.

THanks