Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Specified Cast Is Not Valid


Can't seem to find why I'm getting this error: Specified cast is not valid.

Ok, using a stored procedure for SQL Server 2000 and here is the main part of it:

 SELECT id, rank, firstName, lastName, service, status, createdTime
 FROM   accessRequest
 WHERE  lastName LIKE @tLastName
     AND    firstName LIKE @tFirstName

And the C# code behind from the class file:

SqlDataReader spResults;

conn.Open();

spResults = command.ExecuteReader();

while( spResults.Read() )

{

AccessRequestSearch request = new AccessRequestSearch( (int)spResults.GetInt32( 0 ), spResults.GetString( 2 ), spResults.GetString( 3 ), spResults.GetString( 1 ), spResults.GetString( 4 ), spResults.GetString( 5 ), Convert.ToDateTime(spResults.GetString( 6 )));

searchResults.Add( request ); // Add to Array List

}

spResults.Close();

The part in red is where I think it's happening because that is what I just added to the request.  createdTime in the table is set as DateTime.

Can anyone see what I am missing here?

More info is available if needed.

Thanks,

Zath


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Specified Cast Is Not Valid
im doing a sum on a table and it either returns a number in decimal format or 'null' .  The problem is when it returns null i want it to just make the text say '0.00'.  So i did a test on the object that if it returns NULL just print  '0.00' but if it is not null it tells me that there is a number there and i want to store that as a decimal and print it out.  But i get an error for a type cast when im not it should not even be going to that part of the code. In the code below the first executescaler will return null so it should just go straight to the else.  But it gives me the type cast error in the if that shouldnt be seen.  The error and code are below. //Borrower NSF FEES
cmd.CommandText = "select sum(itemamount) from postmtdtls where loanid='" + LoanID + "' and Transactioncode = '310'";
object temp = cmd.ExecuteScalar();
if (temp != null)
{
decimal B_NSFFees = ((decimal)cmd.ExecuteScalar());
borrowerPayoff_NSFFees.Text = String.Format("{0:#,#.##}", B_NSFFees).ToString(); //Borrower NSF FEES
}
else
{
borrowerPayoff_NSFFees.Text = "0.00"; //borrowerPayoff_NSFFees.Text = "0.00";
}  Server Error in '/WebSite5' Application. Specified cast is not valid. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Specified cast is not valid.Source Error: Line 774: if (temp != null)
Line 775: {
Line 776: decimal B_NSFFees = ((decimal)cmd.ExecuteScalar());
Line 777: borrowerPayoff_NSFFees.Text = String.Format("{0:#,#.##}", B_NSFFees).ToString(); //Borrower NSF FEES
Line 778: }Source File: c:ProgrammingFilesWebSite5InvestorPool.aspx.cs    Line: 776 Stack Trace: [InvalidCastException: Specified cast is not valid.]
InvestorPool.GetLoanInfo(String LoanID) in c:ProgrammingFilesWebSite5InvestorPool.aspx.cs:776
InvestorPool.MortAccountText(Object sender, EventArgs e) in c:ProgrammingFilesWebSite5InvestorPool.aspx.cs:660
System.Web.UI.WebControls.TextBox.OnTextChanged(EventArgs e) +75
System.Web.UI.WebControls.TextBox.RaisePostDataChangedEvent() +124
System.Web.UI.WebControls.TextBox.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7
System.Web.UI.Page.RaiseChangedEvents() +138
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4507
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

View Replies !   View Related
Error: Specified Cast Is Not Valid.
 
 
  sqlConnection1.Open();
System.Data.SqlClient.SqlDataReader Dil1;
Dil1 = sqlDilGetir.ExecuteReader();
ddlDil1.Items.Add(new ListItem("1. Dil", ""));
while (Dil1.Read())
{
ddlDil1.Items.Add(new ListItem(Dil1.GetString(1), Dil1.GetString(0)));
}
Dil1.Close();
sqlConnection1.Close(); 

View Replies !   View Related
SSRS2000 - Specifies Cast Not Valid
Hi,



I get the above error everytime I try to export to Excel; exporting to PDF works fine.



The only way I can get rid of the error is by deleting all the data from within the report (not ideal!). Anyone got any ideas on what could be causing this?



Many thanks,



Mani

View Replies !   View Related
Data Import Wizard, Specified Cast Is Not Valid
I want to import database from pervasive to SQL Server 2005. And in data Import wizard when i select the data source as .Net Framework Data provider for ODBC and give the required information e.g. dsn, driver etc. it gives the error :
Cannot get the supported data types from the database connection,

Additional Information
Specified cast is not valid. (System.Data)
Whats the problem ????

View Replies !   View Related
Cannot Show The Editor For This Task. Specified Cast Is Not Valid
This error is pretty persistent. I re-installed VS 2005 and SQL Server 2005 but did not help. Every time I try to open a Script Task editor it gives me the same error regardless of the project or package. DO I NEED TO REBUILD MY SYSTEM ?

===================================

Cannot show the editor for this task. (Microsoft Visual Studio)

===================================

Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)

------------------------------
Program Location:

   at Microsoft.VisualBasic.Vsa.Dt.VsaIDE.get_ExtensibilityObject()
   at Microsoft.SqlServer.VSAHosting.DesignTime.get_ExtensibilityObject()
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskUI.Dispose()
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskMainWnd.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()
   at Microsoft.DataTransformationServices.Design.DtrPackageDesigner.DoDefaultActionForTask(TaskHost task)

 

-------

Any help is appreciated.

Gulden

 

.......

View Replies !   View Related
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 SubThanks...

View Replies !   View Related
Specified Cast Is Not Valid Error In Datagrid/update Script
I have a datagrid script where I modify data in an sql dbase in asp.net, when i hit the "update" button, I get a Specified cast is not valid error on my 'descript' declaration, whereas 'descript' is a multiline text box and a varchar datatype (everything else is either a char or a datetime datatype). Am I assigning 'descript' a wrong datatype? Tried making it a nvarchar, still get same result'''''''''''''''''''''''''''''''Sub MyDataGrid_UpdateCommand(s As Object, e As DataGridCommandEventArgs ) Dim conn As SQLConnection Dim MyCommand As SQLCommand Dim strConn as string = "Server=sql.mydomain.com;Initial Catalog=mydb;User ID=DBxxx;Password=xxxxx;" Dim company As textbox = E.Item.cells(2).Controls(0) Dim address As textbox = E.Item.cells(3).Controls(0) Dim city As textbox = E.Item.Cells(4).Controls(0)  Dim state As textbox = E.Item.cells(5).Controls(0) Dim county As textbox = E.Item.cells(6).Controls(0) Dim zip As textbox = E.Item.cells(7).Controls(0) Dim phone As textbox = E.Item.cells(8).Controls(0)'''''' the following line declaring the descript var is the line of the error Dim descript As textbox = E.Item.cells(9).Controls(0) Dim web As textbox = E.Item.cells(10).Controls(0) Dim email As textbox = E.Item.cells(11).Controls(0) Dim datesold As textbox = E.Item.cells(12).Controls(0) Dim dateexpire As textbox = E.Item.cells(13).Controls(0)  Dim strUpdateStmt As String        strUpdateStmt =" UPDATE CPAs SET" & _        " company = @company, address = @address, city = @city, state = @state, " & _        "county = @county, zip = @zip, phone = @phone, descript =@ descript, " & _  "web = @web, email = @email, datesold = @datesold, dateexpire = @dateexpire" & _  " WHERE cpaID = @cpaID" conn = New SqlConnection(strConn) MyCommand = New SqlCommand(strUpdateStmt, conn) MyCommand.Parameters.Add(New SqlParameter("@company", company.text)) MyCommand.Parameters.Add(New SqlParameter("@address", address.text)) MyCommand.Parameters.Add(New SqlParameter("@city", city.text)) MyCommand.Parameters.Add(New SqlParameter("@state", state.text)) MyCommand.Parameters.Add(New SqlParameter("@county", county.text)) MyCommand.Parameters.Add(New SqlParameter("@zip", zip.text)) MyCommand.Parameters.Add(New SqlParameter("@phone", phone.text)) MyCommand.Parameters.Add(New SqlParameter("@descript", descript.text)) MyCommand.Parameters.Add(New SqlParameter("@web", web.text)) MyCommand.Parameters.Add(New SqlParameter("@email", email.text)) MyCommand.Parameters.Add(New SqlParameter("@datesold", datesold.text)) '', dateexpire =@dateexpire MyCommand.Parameters.Add(New SqlParameter("@dateexpire", dateexpire.text))  MyCommand.Parameters.Add(New SqlParameter("@cpaID", e.Item.Cells(1).Text )) conn.Open() MyCommand.ExecuteNonQuery() MyDataGrid.EditItemIndex = -1        conn.close BindDataEnd Sub'''''''''''''''''''''''''''''netsports

View Replies !   View Related
Specified Cast Is Not Valid - Txn Replication Snapshot Agent Error
I have set up transactional replication with immediate updating subscriptions on my test SQL 2005 servers. One of the snapshot agents failed with "Specified cast is not valid" error message. I have created the subscription for that database by initializing it from backup instead. I would like to find out why this snapshot agent is failing and how to fix it.

SQL Server 2005 Standard Edition SP2, Windows Server 2003 R2 64-bit, SP2.




Error messages:

Source: Microsoft.SqlServer.Replication
Target Site: Int64 ExtractInt16RangeHighKeyValueAsInt64(StepInfo)
Message: Specified cast is not valid.
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.ArticleBcpPartitioningResolver.ExtractInt16RangeHighKeyValueAsInt64(StepInfo stepInfo)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.ArticleBcpPartitioningResolver.InterpolateStepInfos(Int32 targetNumberOfPartitions)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.ArticleBcpPartitioningResolver.ResolveArticleBcpPartitioningUsingKeyDistributionHistogram(BaseArticleWrapper article, Boolean usePrimaryKeyOnly)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: Microsoft.SqlServer.Replication, Error number: 0)
Get help: http://help/0


Thanks for your help.

View Replies !   View Related
Got 'Specified Cast Is Not Valid' Opening Mining Model Viewer
Hi all

I have a problem I created mining model with Microsoft Sequence Clustering algorithm, when I tried to open it in viewer, I got an error 'Specified cast is not valid'.
After looking in SQL profiler I see, that viewer execute MDX query to get node attributes of modelELECT FLATTENED TOP 1 (SELECT [ATTRIBUTE_NAME] FROM [NODE_DISTRIBUTION] WHERE VALUETYPE = 1) FROM [Dating Usepath].CONTENT WHERE NODE_TYPE = '13' - this query returns empty result set.

I modified it and got the following:
SELECT FLATTENED (SELECT [ATTRIBUTE_NAME] FROM [NODE_DISTRIBUTION] WHERE VALUETYPE = 1),NODE_TYPE FROM [Dating Usepath].CONTENT

Expression.ATTRIBUTE_NAME NODE_TYPE
Op To Immo 5
Rating Range 5
Action 5
Age Int 5
Op To Immo 5
Rating Range 5
Action 5
Age Int 5
Op To Immo 5
Rating Range 5
13
13
13
13
13
13
13
13
13
13
13

It's only the bottom of resut set, but it shows that the names of attributtes actually empty.

What I did wrong, and what I possible change in the model to view Cluster diagram correctly.

P.S. When I created model on the same data with Microsoft Clustering algorithm everything is ok.

View Replies !   View Related
Cast From String &&"1/31/2007&&" To Type 'Date' Is Not Valid.
Hi,

I hv a major problem, I have a working Asp.NET application running in Win2003 without any Service packs. My SQL server is also running on win2003 in other machine. This setup is working perfectly without any probelm.

Now I have a new server with Win2003 R2 SP2 andI installed my asp applicationto this server and now I getting following error inall the pageswhich are havingdate fields. Eventhough I enter "31/01/2007" it gives me following error

-----
Cast from string "1/31/2007" to type 'Date' is not valid. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Cast from string "1/31/2007" to type 'Date' is not valid.

Source Error:





An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:





[InvalidCastException: Cast from string "1/31/2007" to type 'Date' is not valid.]
Microsoft.VisualBasic.CompilerServices.DateType.FromString(String Value, CultureInfo culture) +162
Microsoft.VisualBasic.CompilerServices.DateType.FromString(String Value) +53
Microsoft.VisualBasic.CompilerServices.DateType.FromObject(Object Value) +156
SharjahVisa.SearchList.Page_Load(Object sender, EventArgs e) +526
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750











Version Information:Microsoft .NET Framework Version:1.1.4322.2379; ASP.NET Version:1.1.4322.2379

-----

All my three servers are set to dd/MM/yyyy format in reginal settings with UK as country. I tried inserting culture="en-GB"in globalization part of the webconfig also. still it gives me same error. But the same application working fine in old server. can some one pls help me on this error. I am not a developer.-- sysadmin



Thanks

View Replies !   View Related
Cast From String &"&" To Type 'Date' Is Not Valid
Hello, I am using an SQLDataReader called DrCoInfo to check if any matching records exist in a table.

My code is as such:

Line 119:DrCoInfo = comm1.ExecuteReader()
Line 120:While DrCoInfo.Read()
Line 121: If DrCoInfo("DateOfMan") <> "" Then
Line 122: BValid = True
Line 123: End If

However, it fails to work with the message :System.InvalidCastException: Cast from string "" to type 'Date' is not valid.

Any suggestions? Thanks.

View Replies !   View Related
Using Cast
Hello,

I am trying to use the Cast keyword with little luck. At the end of the select below, I want the last columm (Average) to be a numeric field (numeric 7,5). I just can't seem to get cast working.

Any thoughts,

thanks,

William


Insert [Bankers_Scores_solic] ([ID], EqorMA, Region, Period,IBFirm, UndNeed ,QualIdea ,ProfAggr ,SpKnow ,negskill ,Perchem, RepPri, Repcomp, TranType, [Count Of Scores], [Sum of Scores], Average)
SELECT [ID],
EqorMA,
Region,
Period,
IBFirm,
UndNeed,
QualIdea,
ProfAggr,
SpKnow,
negskill,
Perchem,
RepPri,
Repcomp ,
TranType,
(Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
AS [Count Of Scores]
,
(ISNULL([UndNeed],0))
+(ISNULL([QualIdea],0))
+(ISNULL([ProfAggr],0))
+(ISNULL([SpKnow],0))
+(ISNULL([negskill],0))
+ (ISNULL([Perchem],0))
AS [Sum of Scores]
,
Cast(((
(ISNULL([UndNeed],0))+
(ISNULL([QualIdea],0))+
(ISNULL([ProfAggr],0))+
(ISNULL([SpKnow],0))+
(ISNULL([Perchem],0)) +
(ISNULL([negskill],0)
))
/((Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
))
AS Average numeric(7,5))
FROM tmp_Solicitation_Scores

View Replies !   View Related
CAST
I have

CASE
WHEN (a.type_id in (9))
THEN a.duration
ELSE 0 --CAST ( expression AS data_type )
END 'Time'


I need the duration to be displayed if type is 9 only.
so otherwise id ideally want 'N/A' displayed..
but it won't display it as its not an int ..

is there a way i can just display it temporarily
instaed of 0 ?

View Replies !   View Related
CAST
Hi
Can anyone tell me how I can CAST these fields AS DECIMAL (19, 2) please?

(SELECT ([Total students] - [withdrawn] - [transferred] - [cancelled]) / [total Students] * 100 AS [Percentage Retained])

Thanks
Daniel

View Replies !   View Related
Need Help With A CAST
I am having a little trouble with the CAST in my SELECT statement below. Any help is greatly appreciated.



SELECT
group_id_ AS [Group ID],
vendor_id_ AS [Vendor ID] ,
project_id_ [Project ID],
resource_id_ [Resource ID],
vendor_price_ [Old Price],
new_price_ [New Price],
(CAST (new_price_/vendor_price_)-1 AS DECIMAL (4, 2)) AS [Difference]
FROM hbs_vnpq
WHERE (group_id_ = '210') AND (vendor_id_ = '08416') AND (new_price_ >0) AND (vendor_price_ >0)
ORDER BY [Difference]





Server: Msg 1035, Level 15, State 10, Line 7
Incorrect syntax near 'CAST', expected 'AS'.

View Replies !   View Related
DDL Is Not Valid
hello,everybody ,i use the AnalysisServicesExecuteDDLTask ,but it dap an error "DDL is not valid",i don't konwwhat is the problem and how to solve?thank you!

View Replies !   View Related
If / IIf - Cast Exception
Hi over there,I hope this question is not too simple, but I didn't manage to figure out why...I would need an explanation for following issue:I'm reading data from a database (MSSQL) and it the column "PersonBirthday" is DBNull.I wanted to prevent the error (Textbox.Text = DBNull) with an IIF. The thing is I get thistypecast exception:"Conversion from type 'DBNull' to type 'Date' is not valid." This code is NOT working, why?    txtPersonBirthday.Text = IIf(IsDBNull(.Item("PersonBirthday")) =
True, String.Empty,
CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd")) When I'm using this code, which is for me obviously the same, just with an if-block it works,and I want to know why - please explain.           If IsDBNull(.Item("PersonBirthday")) Then                txtPersonBirthday.Text = String.Empty            Else                txtPersonBirthday.Text = CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd")            End IfThanks in advance,cheers,uquandux                    If IsDBNull(.Item("PersonBirthday")) Then                txtPersonBirthday.Text = String.Empty            Else                txtPersonBirthday.Text = CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd")            End If 

View Replies !   View Related
Cast Or Convert
 Hi,I want to turn int to double/decimal  in microsoft sqlSHould i use cast or convert?if so, how i do it thanks, 

View Replies !   View Related
Cast Problem
If I run the following query in SQL Server Management Studio it returns the correct results: (Searching the table for the field "SpecimenID (an INT)" against the data entered (a Text Field - "7575-01")  from the submitted form.
SELECT     ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other,                       OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTimeFROM         ClinicalSpecimenWHERE     (SpecimenID = CAST('7575-01' AS VARCHAR(50)))ORDER BY SpecimenID DESCHowever, when I try to use the same logic in the ASPX.VB code behind page, as follows below, I either get an error message (Syntax error converting the varchar value '' to a column of data type int.) or record not found.... Can someone please explain what I am missing here....
MySQL = "SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent & "') AS VARCHAR(50))"
*"AccessionPresent" is the value of the text field retrieved from the form.
I guess what I am really asking is how can I search for an INT value in a table using a VARCHAR Field.
Thank you for any or all assistance !!!

View Replies !   View Related
Using CAST From Varchar To Int
I am pretty new to SQL for SQL Server 2005. In a view I have a column CAST(Field1 as int). Field1 is a varchar.All works well except when the data is not numeric such as a '?' or '*'.How can I get around this, with a case statement, coalese?I only want to perform the CAST on valid numeric values.The valid values in the varchar field are blank, null,'0','1','2','3','4','5','?','*' and maybe othersThanks

View Replies !   View Related
Varbinary Cast
Can anyone tell me what varbinary casts to ?? can I do this int[] temp=(int)DataReader["varbinaryColumn"]. Or is byte[] temp the appropiate protocol.

View Replies !   View Related
CAST Command
I am using MS-Access as a front end for my MS-SQL DB. I have a sql view thatuses the following:SELECT TOP 100 PERCENT RECID, PATNUMBER AS [PAT #], SVCCODE AS [ServiceCode], QTY, PROF_CHRGS AS [Pro Fee's'], AMOUNT,BILLDATE AS [Bill Date], CHKAMT AS [Check Amt], PSDATEAS [Service Date], POSTDATE AS [Post Date], TRNSCODE AS [T Code],TRLR AS [T Code Desc], SUBSTRING(CAST(SVCCODE ASvarchar), 1, 4) AS [Dept #]FROM dbo.PAT_TransactionsORDER BY PATNUMBER, SVCCODEMy problem is the cast command. Will this sql view works and cast theSVCCODE field into a varchar, I need to cast the reseult of this,SUBSTRING(CAST(SVCCODE AS varchar), 1, 4) AS [Dept #], back in to a decimalformat. I am dropping this view onto a form and need to link to a field onthe form that is in decimal format.The only way I could get this to work was to create another view, based onthe one above, and cast the [Dept #] field back into the decimal format. Isthere any better way to do this? Can I nest the Cast command?Hope this makes sense.Thanks in advance.Mikem charney at dunlap hospital dot org

View Replies !   View Related
ISNUMERIC And CAST
SELECT ISNUMERIC('. ')1SELECT CAST('. ' AS [insert any numeric type])Syntax error converting the varchar value '. ' to a column of data type[insert any numeric type].Any thoughts as to why SQL Server 7.0 considers '. ' to be numeric, yet can'tconvert it to any numeric data type? The BOL even has this to say: "ISNUMERICreturns 1 when the input expression evaluates to a valid integer, floatingpoint number, money or decimal type; otherwise it returns 0. A return value of1 guarantees that expression can be converted to one of these numeric types."An application that has worked for years, failed when a field of type CHAR(3)(that should be an area code) contained a period only. I have error-checkingthat tests ISNUMERIC on that field, and if it fails, alert accordingly. If itpasses, then convert it to a number, and compare it to acceptable ranges forArea Code. (200 < AC < 999)oops...

View Replies !   View Related
Check If Cast Is Possible
I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,

STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,

PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,

PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.

View Replies !   View Related
CAST Statement
It must be something I'm overlooking but I keep getting an error message that this statement can't parse.

UPDATE product SET supplier = LEFT(supplier,LEN(supplier-4)) + CAST( '2100' AS varchar(4)) WHERE actualid = 'IS2100-CO2-CO2-0-4-I'

Any help would be greatly appreciated.

View Replies !   View Related
Cast Errors
Update and insert procedures to datetime fields in our database (6.50.416 on NT4.0 SP6) cause sporatic casting error problems. The operation might work one day but the next we get the error! Can anybody explain this behavior?

Typical procedure:

Create Procedure uPolicyDriver
@DOB datetime
As

update Driver
set Birthdate = @DOB

return (0)
-------------------------------------------------------------------
The error is as follows:

Server Error

22005[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

The table we try to update is as follows:

CREATE TABLE Driver (
BirthDate datetime NULL ,
ClientID int NOT NULL ,
DateLicensed datetime NULL ,
DriverID int IDENTITY (1, 1) NOT NULL ,
DriverLicense varchar (20) NULL ,
DriverNumber tinyint NULL ,
DriverTraining tinyint NULL ,
Employer varchar (20) NULL ,
EmployerAddress varchar (26) NULL ,
EmploymentStatus tinyint NULL ,
LastMVR datetime NULL ,
Level1 bit NOT NULL ,
LicenseClass tinyint NULL ,
LicenseCountry tinyint NULL ,
MaritalStatus tinyint NULL ,
MVRReceived bit NOT NULL ,
MVRRequested bit NOT NULL ,
NameID int NULL ,
Occupation varchar (22) NULL ,
PolicyID int NOT NULL ,
ProvLicensed varchar (20) NULL ,
RelationshipToApplicant tinyint NULL ,
YearsCommonLaw tinyint NULL ,
YearsLicOC int NULL
)

View Replies !   View Related
Help With Conver/Cast
I have a table with a column called Sample_Date_and_Teime with a row definition of char(16). What is stored in this table is a date (ie. 1020621141517000) The first char is the century, the next are the year, the next two are the month, the next two are the day, the next 2 the hour, the next two are the minutes, and the next 2 are the seconds and the last 3 are to be ignored... what I need to do is write a select statement that converts this column into a datetime so that I can then do a insert into a new table by selecting from this table based on a date range. PLEASE HELP:confused: :confused: :confused:

View Replies !   View Related
CAST/Convert
I need Query syntax to cast/convert values as follws.

Val.: 00005000010260002180 - Result must be: 5.1.2600.2180
Val.: 00005000000213400001 - Reslut must be : 5.0.2134.1

Dots must also be contained in result

View Replies !   View Related
Cast And Round
I am using a statement in which a where clause has a statement like this

Where ..
and cast(round(colname,1) As Numeric(9,1)) =>9.5

When i execute this query i get this error

Server: Msg 8114, Level 16, State 5, Procedure sp_rpt_get_count_hb_great_9andhalf, Line 22
Error converting data type varchar to float.


Any help is appreciate

View Replies !   View Related
Cast Formula
I have a quick question. created a report with this formula below. It works when I put a date range from 1-1-2007 to 2-1-2007, but when I put in a date range of 03-01-2008 through 03-31-2008. i get an error message of arithmetic overflow error converting numeric to data type numberic.

So instead of haveing the formula below be (4,2) I put it as (5,2) and now it work. Why is that?

CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings

View Replies !   View Related
CAST Problem
I have a problem with CASTING

I have a CASE stmt, i check a var that is an int in the db, but based on values i wanna display strings.

so

CASE var1
WHEN var1 = 51 THEN 'Level 1'
WHEN var1 = 52 THEN 'Level 2'
ELSE var1
END as x


This gives me Conversion failed when converting the varchar value 'Level 1' to data type int.

Fair enough
but when i try to cast it it still gives me the same error ?

CASE CAST(var1 as varchar(10))
WHEN var1 = 51 THEN 'Level 1'
WHEN var1 = 52 THEN 'Level 2'
ELSE var1
END as x

View Replies !   View Related
Error When Using Cast
Hi, I'm very new to SQL. Trying to sum two fields, but I have to change the datatype first. Here's the code and the error message I receive. Any help would be appreciated.

SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(TS_USER_07 * TS_STATUS) AS value
FROM TEST_54_VW
GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07
WHERE CAST(TS_USER_07 AS INTEGER)

Incorrect syntax near the keyword 'WHERE'.

View Replies !   View Related
DATE Cast
Hello Everyone,

How do I change the format of the Date:

2006-06-01 00:00:00.000

to this:

06-01-2006.

I believe this can be done with a Cast.

Am I correct?

TIA


Kurt

View Replies !   View Related
Help In Cast Function!
Hi,

I have a table with the following structure:
Table: Meeting
ID integer not null
Desc text null

Here Col 'Desc' is a text type stores description. I have used the following UPDATE query:
UPDATE Meeting SET Desc='New Updated text......' + ' ' + CAST(Desc as varchar) WHERE ID=100

Issues:
1. Since i did'nt specify lenth for varchar it takes only 30 chars from existing value and added to the edited text.
2. If i give like this CAST(Desc as varchar(8000)); it might take only first 8000 chars; remaining would be truncated correct? I assume that will happen.
3. If i don't use CAST i get error msg: "Invalid operator for type.."
Is there any work around in this situation? or better way to do it.

Looking for your help.
Thanks
BoB

View Replies !   View Related
Cast String To Int
Hi ,

I have a problem with casting a string number to int.
for exmaple i have the number '0110' (string) and i would like it to be 110. or '0001' to be 1.
i tried to do:
cast(MyNUM as int) .
but i get "Syntax error converting the nvarchar value 'O322' to a column of data type int."

is anyone have any idea how to solve it ?

thanks

View Replies !   View Related
Cast DATETIME
Hi all,

i need help for deleting the time from a datetime type

what i mean is that instead of
2007-08-15 01:30:00.000
i would get
2007-08-15 00:00:00.000

many thanks

View Replies !   View Related
Safe Way To Cast
I have a table that stores a value called "LocationNumber" as a varchar. I didn't create the database, I just use it. Anyway I usually sort the list by Casting the LocationNumber to an Integer. If I don't cast it, it trys to sort it lexiographically i.e. 0, 1, 21, 3, 345, 9 instead of 0, 1, 2, 3, 9, 21, 345.

It works 99.9% of the time, but the other 0.1% of the time it runs into some text that somehow made its way into the database field, and it causes an error.

Is there a way to safely cast a number in SQL? So that it just returns 0 when the value is not a number.

View Replies !   View Related
Cast Problems With CLR
hi,

i have looked at several guides, and have come up with some code, although for some reason i am unable to get it to work, and receive an error:

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'.
System.InvalidCastException:
at UserDefinedFunctions.FillRow(Object obj, String& PID)
.


Below is my code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Collections;

public partial class UserDefinedFunctions
{
const string SELECT =
@"SELECT pointData.PID
FROM pointData
INNER JOIN pointDevices ON pointData.PID = pointDevices.PID
WHERE pointDevices.UUID = @UUID AND
DATEADDED >= @DATE_START AND
DATEADDED <= @DATE_STOP
ORDER BY DATEADDED";

[SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.Read, TableDefinition="PID nvarchar(50)")]
public static IEnumerable createJourney(SqlString UUID, DateTime DATE_START,DateTime DATE_STOP, SqlDouble JOURNEY_DELAY, SqlDouble JOURNEY_DISTANCE)
{
ArrayList RAW_PID_LIST = new ArrayList();

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();

SqlCommand command = new SqlCommand(SELECT,conn);

command.Parameters.AddWithValue("@UUID",UUID);
command.Parameters.AddWithValue("@DATE_START",DATE_START);
command.Parameters.AddWithValue("@DATE_STOP",DATE_STOP);

SqlDataReader reader = command.ExecuteReader();

using (reader)
{
while (reader.Read())
{
RAW_PID_LIST.Add(reader[0]);
}
}
}

return RAW_PID_LIST;
}

private static void FillRow(Object obj, out string PID)
{
object[] row = (object[])obj;

PID = (string)row[0];
}
};


could someone give me a clue as to why i might be getting this error please.

thank you.

View Replies !   View Related
CAST Error
I am keep getting error when I use CAST function in Expression.

If I run (DT_I4)("1")  I got following error.

 

TITLE: Expression Builder
------------------------------

Cannot convert expression value to property type.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=CannotAssignExpressionToProperty&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Cannot convert 'System.Int32' to 'System.Int64'.

------------------------------
BUTTONS:

OK
------------------------------

View Replies !   View Related
Cast Function
I'm trying to cast the variable Alumni and keep getting a "The type of the value being assigned to variable "User::
Alumni" differs from the current variable type"error. The resultset from Records is a varchar(max) and the variable Alumni is a string. I found a blog that says to cast the result column to a max size limit. I want to do it this way so I can put the result in the message of a send mail task.

Here is the blog.
http://blogs.conchango.com/stevewright/archive/2006/01/20/2686.aspx


SELECT Records as Alumni


this doesn't work or I dont' have the syntax right?
SELECT Records as CAST(Alumni as varchar(max))



thanks,

View Replies !   View Related
Cast Error, Please Help!
I'm encountering a Cast error, and find I am very much out of my league... I'm using a Derived Column Transformation to convert a column that comes from an Excel spreadsheet from a non-standard date format to DT_Date, though I picked this format simply because it was the first date format I found in the pick list. When I upload the ETL into Management Studio and run it I get the following error:




Code Block

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
01/03/2008 07:27:52,Force_Receipt_To_Clear_From_Excel,Error,0,WFM-SQL1-RDM,Force_Receipt_To_Clear_From_Excel,(Job outcome),,The job failed. The Job was invoked by User Removed. The last step to run was step 1 (Run Force_Receipt_To_Clear_From_Excel).,00:00:35,0,0,,,,0
01/03/2008 07:27:52,Force_Receipt_To_Clear_From_Excel,Error,1,Sever_Name_Removed,ETL_NAME_REMOVED,Run ETL_NAME_REMOVED,,Executed as user: Removed. ....00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 07:27:52 Error: 2008-01-03 07:28:25.12 Code: 0xC0202009 Source: Data Flow Task OLE DB Destination [232] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". End Error Error: 2008-01-03 07:28:25.14 Code: 0xC020901C Source: Data Flow Task OLE DB Destination [232] Description: There was an error with input column "ASSIGNED_TO_DISPATCH" (2020) on input "OLE DB Destination Input" (245). The column status returned was: "Conversion failed because ... The package execution fa... The step failed.,00:00:35,0,0,,,,0


The data that is being converted appears as one of two things. First it can appear as a string value with 0 characters (that does not appear a Null oddly). Second it will appear as a string value representing a date in the following format 01022008 7:38 which is a date in the following the format 2 Digit Month,2 Digit Day, 4 Digit Year, Space Time in Military Time.



The expression I'm using to convert the String data type to a Date is:



Code Block

LEN([Completed Date Time]) == 0 || ISNULL([Completed Date Time]) ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING([Completed Date Time],1,2) + "/" + SUBSTRING([Completed Date Time],3,2) + "/" + SUBSTRING([Completed Date Time],5,10))






View Replies !   View Related
Convert AND Cast
We have a 3rd party application that connects to our SQL 2005 as a external database source.

Our external database has a 'datein' field that for some reason is stored as a varchar(50).

I can use cast to convert the field to datetime, but how would I then format to the yyyy-mm-dd format in the same select statement(using in view)?

I need to convert this field to a format of YYYY-MM-DD in order for the 3rd party application to search on it correctly.
Below is an example of the data --
FieldName = DateIn
DateType = Varchar(50)
2006-07-06
Sep 12 2007 11:16AM
2007-07-19
Oct 2 2007 12:24PM
2007-08-19
Oct 31 2007 8:00PM
Sep 15 2007 9:25AM
2007-08-18
Oct 16 2007 7:01PM
Oct 30 2007 11:15AM
Aug 29 2007 8:54AM
Aug 29 2007 8:38PM
Aug 25 2007 2:33PM

View Replies !   View Related
Instance Specified Not Valid
Hi,

I'm trying to install MSDE SP3 which I downloaded from MS at http://www.asp.net/msde/.
Following the instructions as best I can I unzipped the file into a Folder now called MSDE located on C: I then went to a command prompt and typed in the command given to start the install:

setup SAPWD=testsite SecurityMode=SQL

Hit enter and the installer started up and ran for a few secs and then came back with

The Instance named specified is invalid

There was no mention of including an instance name or the format for it.

Suggestions please?

Also, if I want to completly uninstall MSDE if I need to how would I do it?

Thanks,
Bob

View Replies !   View Related
Valid T-SQL Syntax?
Version 1:--------------------------------If Condition = AUPDATE query 1UPDATE query 2ELSE IF Condition = BUPDATE queryVersion 2:--------------------------------If Condition = ABEGINUPDATE query 1UPDATE query 2ENDELSE IF Condition = BBEGINUPDATE queryENDOr are they functionally equivalent?Thanks

View Replies !   View Related
Trigger ... Is This Valid?
Is the following valid syntax? Thanks for your help!

IF (SELECT chargeID FROM inserted = 0) BEGIN

END

OR, should I be doing something like this:

DECLARE @thisChargeID as int
SET @thisChargeID = (SELECT chargeID FROM inserted)
IF (@thisChargeID = 0) BEGIN

END

View Replies !   View Related
Valid User
Hello:
I am getting an error:
User id 12 is not a valid user in database Dest_db
While using Database object transfer.

Using mixed security on the source database.
copy data and replace existing data options are selected.

View Replies !   View Related
Valid Backup To Use
I have a question regarding the backup to use in case of consistency errors.

I'm backing up my dbs daily and logs hourly with 'verify the integrity of the backup when completed' checked. Today I found out one of my tables has 1 page id error (dbcc checkdb message from yesterday), I'm wondering if I can use yesterday's full backup even if it's done after the dbcc, that way I don't have to apply a lot less log backups. In other words, does or does not the full backup inherit the corruption if it is done after consistency errors and the backup passes the integrity verification.

Thanks,
Markham

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved