How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type
I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?
View Complete Forum Thread with Replies
Related Forum Messages:
Bit Type Parameter For Stored Procedure
I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI The syntax I am trying to use is: MyStoredProcedure.Parameters.Add(New SqlParameter("@Pdi",SqlDbtype.bit)) MyStoredProcedure.Parameters("@pdi").value = -1 When I do my ExecuteNonQuery I get error 8114 What am I doing wrong?
View Replies !
Is It Possible To Capture An OUT Type Parameter From A PL/SQL Stored Procedure?
When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure? For example, if I have a simple procedure as below to insert a row into the database. Ideally I would like it to return back the parameter named NewId to my ASP.NET server. I'd like to capture this in the VB.NET code. 1 create or replace procedure WriteName(FirstName in varchar2, LastName in varchar2, NewId out pls_integer) is2 3 NameId pls_integer;4 5 begin6 7 select name_seq.nextval into NameId from dual;8 9 insert into all_names(id, first_name, last_name)10 values(NameId, FirstName, LastName);11 12 NewId := NameId;13 14 end WriteName; 1 <asp:SqlDataSource 2 ID="SqlDataSaveName" 3 runat="server" 4 ConnectionString="<%$ ConnectionStrings:ConnectionString %>"5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 6 SelectCommand="WRITENAME"7 SelectCommandType="StoredProcedure">8 <SelectParameters>9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />11 </SelectParameters>12 </asp:SqlDataSource>This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameter NewId returned from Oracle. 1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty) If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.
View Replies !
Data Type Of Parameter Passing To A Stored Procedure
Hi, I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server. The exact error message that occurs is: ADODB.Parameters (0x800A0E7C) Parameter object is improperly defined. Inconsistent or incomplete information was provided. The error occurs in the following code line: .parameters.Append cmd.CreateParameter ("@EMedical", advarwchar, adParamInput) I need to know what to write instead of advarwchar? Thanks in advance
View Replies !
SqlDataSource Using A Type Parameter Of Table
I am not sure (and I've been known to overlook things) if it is possible to use a Table parameter type as parameter to a stored procedure? Is it possible? I would prefer not to create a SQL batch statement calling an stored proc w/ one parameter over and over. Instead i'd rather just create the stored proc to take a table parameter and pass the table into the stored proc. I know I could use a CSV string of IDs instead of a table... or I could create a big SQL batch and use that. But I'd rather not. Could anyone provide some guidance? Thanks!Joe
View Replies !
How Can I Change The Data Type Of The Parameter For The Deployed Stored Procedure ??
Hi I have Try to Create Stored Procedure in C# with the following structure [Microsoft.SqlServer.Server.SqlProcedure] public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image) { } But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature @ImageID uniqueidentifier @ImageFileName nvarchar(4000) @Image varbinary(8000) But I don€™t want that Data types .. I want it to be in the following format @ImageID uniqueidentifier @ImageFileName nText @Image Image How Can I Control the data type for each parameter ?? Or How Can I Change the data type of the parameter for the Deployed Stored Procedure ?? Or How Can I defined the new Data type ?? Or What's the solution to this problem ?? Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP ALTER PROCEDURE [dbo].[sp_AddImage] @ImageID [uniqueidentifier], @ImageFileName nText, @Image Image WITH EXECUTE AS CALLER AS EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage] GO And thanks with my best regarding Fraas
View Replies !
Unable To Cast Object Of Type 'System.String' To Type 'System.Web.UI.WebControls.Parameter'.
I'm getting this error on a vb.net page the needs to execute two separate stored procedures. The first one, is the main insert, and returns the identity value for the ClientID. The second stored procedure inserts data, but needs to insert the ClientID returned in the first stored procedure. What am I doing wrong with including the identity value "ClientID" in the second stored procedure? Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'. 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: Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'.Source Error: Line 14: If li.Selected Then Line 15: InsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.Value Line 16: InsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID") Line 17: Line 18: Source File: C:InetpubwwwrootIntranetExternalAppsNewEmploymentClientNewClient.aspx.vb Line: 16 Here is my code behind... What am I doing wrong with grabbing the ClientID from the first stored procedure insert? Protected Sub InsertNewClient_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)ClientID.Text = e.Command.Parameters("@ClientID").Value.ToString()ViewState("ClientID") = e.Command.Parameters("@ClientID").Value.ToString()End SubProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.ClickInsertNewClient.Insert()For Each li As ListItem In CompanyTypeID.Items If li.Selected ThenInsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.ValueInsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID")InsertClientCompanyType.Insert()End IfNextEnd Sub
View Replies !
How Get The Varchar Type Value And Resultset Type Value From A Procedure
db server is ms sqlserver. the following is my procedure code: CREATE PROCEDURE [test_cc] @ww char(20) output AS begin select @ww='666666666' select col_name from table_name end GO then,following is some of my jdbc invoking procedure code: try { Connection conn = DBC.getConnection(); if (conn == null) { System.out.println("Failed"); } else { conn.setAutoCommit(false); CallableStatement proc = conn.prepareCall("{call test_cc (?)}"); proc.registerOutParameter(1,Types.VARCHAR); proc.execute(); ResultSet rs = proc.getResultSet(); String aa = proc.getString(2); if (rs == null) { System.out.println(" RS is null"); #-----1 } else { ResultSetMetaData metadata = rs.getMetaData();#---2 while (rs.next()) { .............. } } } catch (Exception e) { } but the result that run is :i can get the variable aa value,it is '666666666',but the performing can`t get into #1 point,at #2 point,it throw the exception :object has bean closed. please tell me ,how to get the variable aa value,and get resultset
View Replies !
Just Want To Make Sure (declare Data Type)
I've reviewed the books on line looking to see if SQL Server 2005 would now include a feature that Oracle has for years. When you declare a variable in a procedure, function, etc you can declare them as a particular table and column type. Example: DECLARE @GHDEP2_SSNDEPENDENTID GHDEP2.SSNDEPENDENTID%TYPE; @GHDEP2_SSNDEPENDENTID1 GHDEP2.SSNDEPENDENTID%TYPE; ghdep2 is the table and ssndependentid is the column. This is great because I'll never run in to a truncation issue as the variable will always be whatever the table is. This may slow things down a bit doing that sort of check but I don't care. I just want to confirm that I can't do this in SQL 2005. All signs point to no.
View Replies !
How To Declare Type Varchar In Markup For SQLDataSource?
How do you set the type to varChar like this using markup (xml tags in the .aspx page)? If I were creating a SQLDataSource control declaratively and one of the parameters needed to be set to VarChar rather than just a string I would set a parameter type to varchar using this syntax: myAdapter.UpdateCommand.Paramters.Add("@Title", SqlDbType.VarChar, 80, "Title"); SQLDataSource control as a default will create parameters as strings even though the table it is pointing to has type varchar in it such as below: <asp:Parameter Name="Title" Type="String" /> If I leave it like that, I get the following error below (as expected) since it really needs to be type SqlDbType.VarChar "The data types text and nvarchar are incompatible in the equal to operator" Below is example default markup the SqlDataSource control creates with an update. Where in this markup should I change things so it will update correctly to varchar rather than just trying to throw a string in the table? <asp:SqlDataSource ID="IISLearning_Content_SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IISLearningConnectionString %>" SelectCommand=""></asp:SqlDataSource> <asp:SqlDataSource ID="MySqlDataSource" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [MyTable]" UpdateCommand="UPDATE [MyTable] SET [Title] = @Title WHERE [ID] = @original_ID AND [Title] = @original_Title"> <UpdateParameters> <asp:Parameter Name="Title" Type="String" /> <asp:Parameter Name="original_ID" Type="Int32" /> <asp:Parameter Name="original_Title" Type="String" /> </UpdateParameters> </asp:SqlDataSource>
View Replies !
ERROR: Procedure Expects Parameter '@statement' Of Type 'ntext/nchar/nvarchar'.
/* INFO USED HERE WAS TAKEN FROM http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 */ DECLARE @X VARCHAR(10) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @Num_Members SMALLINT SELECT @X = 'x.dbo.v_NumberofMembers' DECLARE @SQLString AS VARCHAR(500) SET @SQLString = 'SELECT @Num_MembersOUT=Num_Members FROM @DB' SET @ParmDefinition = '@Num_MembersOUT SMALLINT OUTPUT' EXECUTE sp_executesql <-LINE 11 @SQLString, @ParmDefinition, @DB = @X, @Num_MembersOUT = @Num_Members OUTPUT Just Need Help On This Error Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. I dont know why im getting a errrror b/c I followed http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 exactly
View Replies !
Input Parameter Exceeds The Limit Of The Data Type's Length In Stored Procedure
Hi guys, is there any way to solve my problem as title ? Assuming my stored proc is written as below : CREATE PROC TEST @A VARCHAR(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,...,5000' AS BEGIN DECLARE @B nvarchar(MAX); SET @B = 'SELECT * FROM C WHERE ID IN ( ' + @A + ')' EXECUTE sp_executesql @B END GO
View Replies !
Using A Declare Variable To Represent A Data Type Length
I'm trying to use a variable set by DECLARE to represent a data type length with CAST. Don't ask why. Here is what I'm got: declare @n int set @n = (select max(len(col_1)) from tblOne) select cast(Col_2 as varchar(@n)) from tblOne This should make Col_2 the length of the longest entry in Col_1. It does not work. I must use a set number for the length after the data type like this: declare @n int set @n = (select max(len(col_1)) from tblOne) select cast(Col_2 as varchar(28)) -- (28 is longest current entry in Col_1) from tblOne Is there a way around this? I'm new to SQL.
View Replies !
Miss Match Between Column Type In A Cursor. Msg 8114, Level 16, State 5
hi I have a float column . only in cursor I get a type missmatch. does any one knows it ? the error Msg 8114, Level 16, State 5, Line 14 Error converting data type varchar to float. the code DECLARE @RON_FLOAT FLOAT DECLARE RON_CURSOR CURSOR FOR SELECT RON_FLOAT FROM RON_TABLE1 OPEN RON_CURSOR FETCH NEXT FROM RON_CURSOR INTO @RON_FLOAT WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'VALUE IS ' + @RON_FLOAT FETCH NEXT FROM RON_CURSOR INTO @RON_FLOAT END CLOSE RON_CURSOR DEALLOCATE RON_CURSOR the code for the table CREATE TABLE [dbo].[RON_TABLE1]( [RON_FLOAT] [float] NULL, [RON_CHAR] [nchar](10) COLLATE Hebrew_BIN NULL )
View Replies !
Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table. Any help would be greatly appreciated. Current Table Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours --------------------------------------------------------------------------------------------------------- Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40 Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20 Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35 Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40 Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40 Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40 Proposed Table Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year --------------------------------------------------------------------------------------------------------------------------------------------------- Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007 Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007 Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008 Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008 Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008 Thanks, Mike Misera
View Replies !
How To Get The Parameter Type In VB.net?
I am currently using the following embedded code to get the selected values from a Parameter and display in a textbox. I however have a number of reports and I don't want to include this in all of them. I have created a dll with some other functions but when i try to include the following function it throws an error on the Parameter type. What am I doing wrong, I am not all that familiar with dot net? Public Function ShowParameterValues(ByVal parameter as Parameter) as String Dim s as String If parameter.count <= 5 then If parameter.IsMultiValue and parameter.count > 1 then s = " " For i as integer = 0 to parameter.Count-1 if i = parameter.count - 1 s = s + CStr(parameter.Label(i)) else s = s + CStr(parameter.Label(i)) + ", " end if Next Else s = " " + CStr(parameter.Label(0)) End If else s = " Only a maximum of 5 selection values can be displayed. " End if Return s End Function
View Replies !
Incorrect Syntax Near 'm'. An Expression Of Non-boolean Type Specified In A Context Where A Condition Is Expected, Near 'type'
This is nutty. I never got this error on my local machine. The only lower case m in the sql is by near the variable Ratingsum like in line 59. [SqlException (0x80131904): Incorrect syntax near 'm'.An expression of non-boolean type specified in a context where a condition is expected, near 'type'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +269 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 view_full_article.btnRating_Click(Object Src, EventArgs E) +565 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746</pre></code> Here is my button click sub in its entirety: 1 Sub btnRating_Click(ByVal Src As Object, ByVal E As EventArgs) 2 'Variable declarations... 3 Dim articleid As Integer 4 articleid = Request.QueryString("aid") 5 Dim strSelectQuery, strInsertQuery As String 6 Dim strCon As String 7 Dim conMyConnection As New System.Data.SqlClient.SqlConnection() 8 Dim cmdMyCommand As New System.Data.SqlClient.SqlCommand() 9 Dim dtrMyDataReader As System.Data.SqlClient.SqlDataReader 10 Dim MyHttpAppObject As System.Web.HttpContext = _ 11 System.Web.HttpContext.Current 12 Dim strRemoteAddress As String 13 Dim intSelectedRating, intCount As Integer 14 Dim Ratingvalues As Decimal 15 Dim Ratingnums As Decimal 16 Dim Stars As Decimal 17 Dim Comments As String 18 Dim active As Boolean = False 19 Me.lblRating.Text = "" 20 'Get the user's ip address and cast its type to string... 21 strRemoteAddress = CStr(MyHttpAppObject.Request.UserHostAddress) 22 'Build the query string. This time check to see if IP address has already rated this ID. 23 strSelectQuery = "SELECT COUNT(*) As RatingCount " 24 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 25 strSelectQuery += " AND ip = '" & strRemoteAddress & "'" 26 'Open the connection, and execute the query... 27 strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString 28 conMyConnection.ConnectionString = strCon 29 conMyConnection.Open() 30 cmdMyCommand.Connection = conMyConnection 31 cmdMyCommand.CommandType = System.Data.CommandType.Text 32 cmdMyCommand.CommandText = strSelectQuery 33 intCount = cmdMyCommand.ExecuteScalar() 34 intSelectedRating = Int(Me.rbRating.Text) 35 conMyConnection.Close() 36 'Close the connection to release these resources... 37 38 If intCount = 0 Then 'The user hasn't rated the article 39 'before, so perform the insert... 40 strInsertQuery = "INSERT INTO tblArticleRating (rating, ip, itemID, comment, active) " 41 strInsertQuery += "VALUES (" 42 strInsertQuery += intSelectedRating & ", '" 43 strInsertQuery += strRemoteAddress & "', " 44 strInsertQuery += articleid & ", '" 45 strInsertQuery += comment.Text & "', '" 46 strInsertQuery += active & "'); " 47 cmdMyCommand.CommandText = strInsertQuery 48 conMyConnection.Open() 49 cmdMyCommand.ExecuteNonQuery() 50 conMyConnection.Close() 51 Me.lblRating.Text = "Thanks for your vote!" 52 Comments = comment.Text.ToString 53 54 If Len(Comments) > 0 Then 55 emailadmin(comment.Text, articleid) 56 End If 57 'now update the article db for the two values but first get the correct ratings for the article 58 strSelectQuery = _ 59 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount " 60 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 61 conMyConnection.Open() 62 cmdMyCommand.CommandText = strSelectQuery 63 dtrMyDataReader = cmdMyCommand.ExecuteReader() 64 dtrMyDataReader.Read() 65 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString) 66 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString) 67 Stars = Ratingvalues / Ratingnums 68 conMyConnection.Close() 69 'Response.Write("Values: " & Ratingvalues) 70 'Response.Write("Votes: " & Ratingnums) 71 72 UpdateRating(articleid, Stars, Ratingnums) 73 Else 'The user has rated the article before, so display a message... 74 Me.lblRating.Text = "You've already rated this article" 75 End If 76 strSelectQuery = _ 77 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount " 78 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 79 conMyConnection.Open() 80 cmdMyCommand.CommandText = strSelectQuery 81 dtrMyDataReader = cmdMyCommand.ExecuteReader() 82 dtrMyDataReader.Read() 83 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString) 84 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString) 85 Stars = Ratingvalues / Ratingnums 86 If (Ratingnums = 1) And (Stars <= 1) Then 87 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote" 88 ElseIf (Ratingnums = 1) And (Stars > 1) Then 89 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote" 90 ElseIf (Ratingnums > 1) And (Stars <= 1) Then 91 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes" 92 ElseIf (Ratingnums > 1) And (Stars > 1) Then 93 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes" 94 End If 95 96 'Response.Write(String.Format("{0:f2}", Stars)) 97 'Response.Write("Values: " & Ratingvalues) 98 'Response.Write("Votes: " & Ratingnums) 99 If (Stars > 0) And (Stars <= 0.5) Then 100 Me.Rating.ImageUrl ="./images/rating/05star.gif" 101 ElseIf (Stars > 0.5) And (Stars < 1.0) Then 102 Me.Rating.ImageUrl = "./images/rating/05star.gif" 103 ElseIf (Stars >= 1.0) And (Stars < 1.5) Then 104 Me.Rating.ImageUrl = "./images/rating/1star.gif" 105 ElseIf (Stars >= 1.5) And (Stars < 2.0) Then 106 Me.Rating.ImageUrl = "./images/rating/15star.gif" 107 ElseIf (Stars >= 2.0) And (Stars < 2.5) Then 108 Me.Rating.ImageUrl = "./images/rating/2star.gif" 109 ElseIf (Stars >= 2.5) And (Stars < 3.0) Then 110 Me.Rating.ImageUrl = "./images/rating/25star.gif" 111 ElseIf (Stars >= 3.0) And (Stars < 3.5) Then 112 Me.Rating.ImageUrl = "./images/rating/3star.gif" 113 ElseIf (Stars >= 3.5) And (Stars < 4.0) Then 114 Me.Rating.ImageUrl = "./images/rating/35star.gif" 115 ElseIf (Stars >= 4.0) And (Stars < 4.5) Then 116 Me.Rating.ImageUrl = "./images/rating/4star.gif" 117 ElseIf (Stars >= 4.5) And (Stars < 5.0) Then 118 Me.Rating.ImageUrl = "./images/rating/45star.gif" 119 ElseIf (Stars >= 4.5) And (Stars <= 5.0) Then 120 Me.Rating.ImageUrl = "./images/rating/5star.gif" 121 End If 122 dtrMyDataReader.Close() 123 conMyConnection.Close() 124 End Sub If you want to reduplicate the error, click over here and try to submit a rating: http://www.link-exchangers.com/view_full_article.aspx?aid=51 Thanks for helping me figure this out.
View Replies !
Float Type Steals My Decimal Points And Money Type Kills My Query
Happy Friday! A while since I have posted a question, and this one is probably real easy. I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1 If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated. anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
View Replies !
Sqlbulkcopy Error : The Given Value Of Type SqlDecimal From The Data Source Cannot Be Converted To Type Decimal Of The Specified
Hi, The table in SQL has column Availability Decimal (8,8) Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected we need to store values as is Error: base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."} "System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range. --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) at MS.Internal.MS COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269" Code in C# SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default); DataRow dr; DataTable dt = new DataTable(); DataColumn dc; try { dc = dt.Columns.Add("Availability", typeof(decimal)); €¦. dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal €¦€¦€¦. } bulkCopy.DestinationTableName = "dbo.[Tbl_Fact_App_Server_AvailPerMinute]"; bulkCopy.WriteToServer(dt); thx
View Replies !
Bulk Insert Task Failing On Data Type Conversion For A Destination Column Of Type Bit
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns. I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work. I hope someone can help me work through this. Thanks in advance, SK SSIS package "Package3.dtsx" starting. Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).". Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).". Task failed: Bulk Insert Task 1 Task failed: Bulk Insert Task Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package3.dtsx" finished: Failure.
View Replies !
Msg 6522, Level 16, State 2, Line 1: System.InvalidCastException: Conversion From Type 'SqlBoolean' To Type 'Boolean' Is Not Val
I created a function called Temperature in VB to be used as a UDF in SQL2005. I get the error listed below. Any thoughts? CREATE FUNCTION Temperature(@FluidName SQL_variant, @InpCode SQL_variant, @Units SQL_variant, @Prop1 SQL_variant, @Prop2 SQL_variant) RETURNS Float AS EXTERNAL NAME Fluids_VB6.[Fluids_VB6.FluidProperties.Fluids].Temperature Then ran function: select dbo.temperature('R22','t','e','225.6','0') Got this: Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'Temperature': System.InvalidCastException: Conversion from type 'SqlBoolean' to type 'Boolean' is not valid. System.InvalidCastException: at Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(Object Value) at Fluids_VB6.FluidProperties.Fluids.Setup(Object& FluidName) at Fluids_VB6.FluidProperties.Fluids.CalcSetup(Object& FluidName, Object& InpCode, Object& Units, Object& Prop1, Object& Prop2) at Fluids_VB6.FluidProperties.Fluids.CalcProp(Object& FluidName, Object& InpCode, Object& Units, Object& Prop1, Object& Prop2) at Fluids_VB6.FluidProperties.Fluids.Temperature(Object FluidName, Object InpCode, Object Units, Object Prop1, Object Prop2) Thanks Buck
View Replies !
Conversion From Type 'DBNull' To Type 'String' Is Not Valid
Hello Friends How are you?? Friends i am getting problem in SQL Server 2005. I am deployng web application on production server as well as Databse also. In production server i inserted new field in all tables which is rowguid and its type is uniqueidentifier. The default binding for this field is newsequentialid(). In some pages it works ok but in some places it generates error like 'Conversion from type 'DBNull' to type 'String' is not valid'. Can anybody help me to solve this problem. Its urgent so plz reply me as soon as possible. I'll be very thankfull to you. Thanks in Advance. Regards,
View Replies !
Conversion From Type 'SqlInt32' To Type 'Integer' Is Not Valid]
Hi all, I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine. The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings. Any suggestions. Best Regards Wafi Mohtaseb
View Replies !
I Really Need A Debate! Type - Attributes Vs Super Type - Sub Types
I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other! Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value: tbAutombbileCategories CategoryID | Category ------------------------------- 1 | Car 2 | Truck 3 | Motorcycle tbAutomobileAttributes AttributeID | fkCategoryID | Attribute ------------------------------------------- 1 | 1 (car) | Doors 2 | 2 (truck) | Cab 3 | 2 (truck) | Capacity tbAutomobile VIN | Category | Make | Model ------------------------------------- 1 | 1 | Honda | Accord 2 | 2 | Ford | F150 tbAutomobileAttributeValues fkVIN | fkAttributeID | Value --------------------------------- 1 | 1 | 2 2 | 1 | 0 2 | 2 | 1000 Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below? tbCategories CategoryID | Category -------------------------- 1 | Cars 2 | Trucks tbAutomobile (Super Type) VIN | fkCategoryID | Make | Model ------------------------------------- 1 | 1 |Honda | Accord tbCars fkVIN | Doors | ----------------- 1 | 2 tbTrucks fkVIN | Cab | Capacity --------------------------- 2 | 0 | 1000 Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO. Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc... Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype. E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks? tbTrucks fkVIN | Cab | Capacity | fkCapID -------------------------------------- 2 | Y | 1000 | 1 tbCaps CapID | Vendor | Price | et.... Any thoughts at all? I thought this would have been a pretty damn hot topic! Mike B
View Replies !
Operand Type Clash: Int Is Incompatible With Void Type
this code work fine on one server (version 7.00.842) and gives following error on another (version 7.00.623) --------------------- Error: Server: Msg 206, Level 16, State 2, Procedure V_PrevisionOccupation, Line 5 Operand type clash: int is incompatible with void type --------------------- start of the code if object_id('V_PrevisionOccupation') is not Null drop view V_PrevisionOccupation go create view dbo.V_PrevisionOccupation as select T1.Id_property, (select T92.BeginDate from acFiscalPeriod T91 join acFiscalPeriod T92 on T92.id_FiscalPeriod = T91.id_FiscalPeriod +1 and T92.ID_FiscalYear = T91.ID_FiscalYear where T91.ID_FiscalYear = T3.ID_FiscalYear and T91.BeginDate <= T1.ADate and datediff(mm, T91.BeginDate, T1.ADate)=0) as PeriodDate, T1.ADate, .... blia, blia ,blia ------------------------ 1. T91.id_FiscalPeriod is not Null 2. when put: select T1.Id_property, (select T91.BeginDate from acFiscalPeriod T91 -- join acFiscalPeriod T92 on T92.id_FiscalPeriod = T91.id_FiscalPeriod +1 and T92.ID_FiscalYear = T91.ID_FiscalYear where T91.ID_FiscalYear = T3.ID_FiscalYear and T91.BeginDate <= T1.ADate and datediff(mm, T91.BeginDate, T1.ADate)=0) as PeriodDate, it works ? Do I miss something in code or it is version error ?
View Replies !
Connection Type Limitations When Using XML Data Type In SQL Task
I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form: SELECT ( SELECT MT.MessageId 'MessageId', MT.MessageType 'MessageType', FROM MessageTable MT ORDER BY MT.messageid desc FOR XML PATH('MessageStatus'), TYPE ) FOR XML PATH('Report'), TYPE For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error: <ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT> Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections? Thanks Walter
View Replies !
Use Of Variant Type Parameter In ExecuteNonQuery()
In the following code snippet, the stored procedure executes up to the iRet = vciSqlCommand.ExecuteNonQuery(); command and fails with error: System.Data.SqlClient.SqlException: Incorrect syntax near 'value'. That would point to the " = @data_Param where section. So what is the proper syntax? Code Snippet SqlParameter data_Param = new SqlParameter("@data_Param", SqlDbType.Variant); SqlCommand vciSqlCommand = new SqlCommand(); switch ((vcidatatype)FieldDef.ivcidatatype) { // read in the binary data!!!!!! case vcidatatype.eENGUNITS: data_Param.Value = Encoding.ASCII.GetString(r.ReadBytes(FieldDef.iLen)); break; case vcidatatype.eLANADR: data_Param.Value = r.ReadInt16(); break; case vcidatatype.eBYTESIZE: data_Param.Value = r.ReadByte(); break; case vcidatatype.eFLOATSIZE: data_Param.Value = r.ReadSingle(); break; case vcidatatype.eINT32SIZE: data_Param.Value = r.ReadUInt32(); break; case vcidatatype.eFILLERBYTES: r.ReadBytes(FieldDef.iLen); break; // eat up empty bytes default: FieldDef.ivcidatatype = (Int32)vcidatatype.eFILLERBYTES; break; } if (FieldDef.ivcidatatype != (Int32)vcidatatype.eFILLERBYTES) { // all but filler vciSqlCommand.CommandText = "update " + acPointType + "set " + FieldDef.sFldName + " = @data_Param where VEC = " + acVECName + " and name = " + acPointName; vciSqlCommand.Connection = conn; conn.Open(); iRet = vciSqlCommand.ExecuteNonQuery(); conn.Close();
View Replies !
Pop-Up Calendar For DateTime Parameter Type
Hello, We have noticed that the calendar months in the Pop-Up Calendar option, based upon month word length, will shift in size and this will cause the left and right arrows to shift. For example, 'May' is much shorter than 'December'. My user is complaining because she cannot quickly move from one month to another without having to reposition the mouse over the navigation arrow. Is the Calendar feature working as designed? Thank you,
View Replies !
Parameter Control Chart Type?
Can I use a parameter to control the type of charts that is displayed? For example, I want the user to choose (from a drop-down) whether they see a Line or Bar graph. I'm using SQL Server 2005 SP2 and VS2005.
View Replies !
Multi Select Type-in Parameter
Hi All Can anyone tell me whether or not it is possible to multi select when you have a parameter that is set as non-querried in order for it to be typed instead of selected. My users prefer typing the values and selecting more than one. But at the moment I cant give them both.. I'm using SSRS with SSAS cube all in BI all 2005 Please help. I suspect that if it's possible it may just be a syntax thing but I am yet to find it. Thanks in advance Gerhard Davids
View Replies !
Function With A Parameter Of Type Object.
Hello, I would like to create a function that accepts a value of any type as parameter. I mean, it could receive a value of type int, datetime, char,... In .net, I would use the type "object". Is there an equivalent for SQL server function ? Regards, mathmax
View Replies !
Argument Data Type For Dateadd Parameter
I am using the following code in my SQL stmt in my OLE BD Source stmt: WHERE ICINVENTORY.ICINVLastChgAt > ? AND ICINVENTORY.ICINVLastChgAt <= DATEADD(mi,?,?) My parameters are as follows: 0 - User:LastSalesLoadDate DateTime variable 1 - User:Load Interval Int16 (or Int32) 2 - User:LastSalesLoadDate When I try to close the program I get the following error: "Argument data type datetime is invalid for argument 2 of dateadd function. If I can't use a datetime data type for the date time part of the dateadd, what can I use? The exact same code runs without error in an EXECUTE SQL task. Thanks.
View Replies !
OLEDB Command Parameter Type Is Different In Different Situations
Hi All, I am using OLEDB Command transformation in a data flow to update the table. Find the columns for the table EMP as follows. EmpID - int EmpName - varchar(40) EmpSal - float Status - varchar(20) I am using the following command to update the table. Update EMP set status = 'Disabled' where EmpID = ? and EmpSal = ? when I use the above condition the type of the second parameter is taking as "double precision float" as the incoming input column type is "double - precision float". This is fine. But when I use the following condition the type of the param is taking as different one. Update EMP set status = 'Disabled' where EmpID = ? and ( EmpSal + ? ) = 0 It is taking as "four byte signed integer" even though the incoming input column type is "double precision float" thus I am loosing the precision and getting error when the limit exeeds. If I use 0.0 then it is taking as "Numeric" type. If I use convert function like (EmpSal + ? ) = Convert(float, 0) then it is taking as "double precision float". Update EMP set status = 'Disabled' where EmpID = ? and ( EmpSal + ? ) = Convert(float, 0) My question is how it behaves in the above situation. Can any body clarify please? Venkat.
View Replies !
Enable Parameter Of Type Date To Be Blank.
Is it possible to enable parameter of type date to be blank (not null)? I created parameter of type date, gave it no default value, when I pressed the preview tab I got error message "The property 'DefaultValue' of report parameter 'DateParamName' doesn't have the expected type" Thanks in advance!
View Replies !
CREATE TYPE Failed Because It Could Not Find Type
Hello -- I am having a UDT problem. When I run the Create Type command. I receive the "could not find type" error. I have seen other posts on here related to the default namespace in VB.NET. When I add the namespace I receive "Incorrect syntax near '.'." What is the format of the EXTERNAL NAME parameter. Thanks for any help. Code below... Incorrect Syntax Error: DROP ASSEMBLY CadSqlUdtsCREATE ASSEMBLY CadSqlUdtsAUTHORIZATION [dbo]FROM 'E:CAD.NETCADUDTsReleaseCadSqlUdts.dll'WITH PERMISSION_SET = SAFEGOCREATE TYPE dbo.ReportingAreaUDTEXTERNAL NAME CadSqlUdts.[CadSqlUdts.CadSqlUdts].ReportingAreaUDT;GO Could Not Find Type Error: DROP ASSEMBLY CadSqlUdtsCREATE ASSEMBLY CadSqlUdtsAUTHORIZATION [dbo]FROM 'E:CAD.NETCADUDTsReleaseCadSqlUdts.dll'WITH PERMISSION_SET = SAFEGOCREATE TYPE dbo.ReportingAreaUDTEXTERNAL NAME CadSqlUdts.ReportingAreaUDT;GO What's up??
View Replies !
Niewbie: Accept The Input Parameter Of Astring Type
Very simple problem but I still can not sovle, could you help me? I have a following store procedure: create procedure countauthors @state as declare @countauthors int select @countauthors=count(*) from authors where state=@state return @countauthors declare @result int exec @result=countauthors'ca' But it don't work for me. Is something wrong here? many thanks :)
View Replies !
Data Type In Parameter Mapping For An Execute SQL Task
Hi, I am trying to use an integer as input parameter for my task I get suck on the parameter data type. The input parameter is define as @Control_ID variable as Int32 in SSIS. When I got into the parameter mapping of Execute SQL Task, I don't find the Int32 data type. I used to try Short, Numeric, Decimal and so on, but all of those data type didn't work. and it returns the following error message: SSIS package "DCLoading.dtsx" starting. Error: 0xC002F210 at Update Control_ID, Execute SQL Task: Executing the query "use DCAStaging update DCA_HFStaging set [dbo].[Control_ID] = P0 where [Control_ID] is null " failed with the following error: "The multi-part identifier "dbo.Control_ID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Update Control_ID Warning: 0x80019002 at DCLoading: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "DCLoading.dtsx" finished: Failure. Any help?
View Replies !
Execute Sql Task To Set Output Parameter Of Type Integer
I'm having a heckuva time with creating output parameters based on a query. Here's what I'm doing. Every day, we copy rows from our mysql table to our sql server table. We only want to copy those rows that are new, so as to not have to recopy the entire table. So I have a DataReader Source set to Ado.net/odbc provider that connects to the mysql db. The destination is an OLE connection to the local sql server. I create an Execute SQL Task. The connection is set to the OLE connection The type is direct input The SQL Statement is "select max(id) from copy_table" In Parameter Mapping, I create a user variable called maxId that is an int64. That variable is now used as the Variable Name. The Direction is Output. The Parameter Name is 0. Whatever data type I use for the mapping does not work with the variable type. If the parameter was set to ULARGE_INTEGER, here's the error [Execute SQL Task] Error: Executing the query "SELECT MAX(stats_id) AS max_id FROM copy_table" failed with the following error: "Type name is invalid.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. If parameter is set to LONG: [Execute SQL Task] Error: An error occurred while assigning a value to variable "maxId": "The type of the value being assigned to variable "User::maxId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". I found that if variable and parameter were dates to use datetime for the variable and DBTIMESTAMP for the parameter. There are an awful lot of combinations between all the possible variable types, and the possible parameter types. Does anyone know the secret combination for your typical integer? Thanks, Lori
View Replies !
Execute SQL Task With An INPUT Parameter Of Type DBTIMESTAMP
Hi Everyone, I'm trying to do something that should be fairly straightforward, but SSIS seems to be getting confused. I have a stored procedure which takes a timestamp as an input parameter. (NOTE: It's not a DateTime that's being stored as a DBTIMESTAMP, it really is a timestamp in the SQL sense.) The command should be something like this: Code Block EXEC dbo.UpdateSynchTimestamp ? I tried to use my variable to pass the value through Parameter Mapping, but I got an unusual error: [Execute SQL Task] Error: Executing the query "EXEC dbo.UpdateSynchTimestamp ?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_DBTIMESTAMP)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. This is strange for a number of reasons: 1) The DBTIMESTAMP parameter has the Direction set to "Input", so it should not be interpreted as an Output or ReturnValue. 2) The Execute SQL Task has Result Set = "None", so it should not be trying to return anything. If I change the code to include a value hard-coded it works: Code Block EXEC dbo.UpdateSynchTimestamp 0x00000000000013BD It is only when a variable is involved that it breaks. Finally, here's the Stored Procedure itself: Code Block CREATE PROCEDURE [dbo].[UpdateSynchTimestamp] @NewValue TIMESTAMP AS BEGIN SET NOCOUNT ON; UPDATE ServerSettings SET [Value] = @NewValue WHERE [Key] = 'SynchTimestamp' END Doe anyone have any suggestions as to why this isn't working for me? For the time being, I have a Script Task which constructs the command text and stores it in a variable. I can't even use an Expression because the DBTIMESTAMP is not supported. Thanks for reading this!
View Replies !
Paraneter Problem: String Type Parameter Not Able To Preserver Its Value
Hi Friends, We are facing one amazing problem. I have one report which is a recursive report to itself. This report has two parameter 1) paramLevel Integer type 2)paramCurrentColumn String Type. 3) parameColumnSequence String type Every drill down to the same(Itself) recursive dynamic report we increment 1) paramLevel = paramLevel +1, 2) parameColumnSequence= "Sales_Area, Sales_Man, Sales_Manager" 3) paramCurrentColumn = Split(Parameters!parameColumnSequence.Value,",").GetValue(Parameters!paramLevel .Value -1 ) next level column name which i decide at run time aftre spliting a column sequence string. When I publish the report and hit the url first time reports appear ok and all the parameters value are displayed properly as follows paramLevel = 1 paramCurrentColumn = "Sales Area" parameColumnSequence= "Sales_Area, Sales_Man, Sales_Manager" When I click the view report button again (which is a submit/post HTTP html requst ) the values String parameter are not preserved. Note: I am not drilling down to next level I am just refereshing the report at same level Logically I m at the same level so both parameter the value should be same. But actually paramLevel = 1 paramCurrentColumn = " " (default value) parameColumnSequence= " " (default value) Problem is the String parameter is not able to preserve the value while integer is able to. Hope you understand the problem? Waiting for your help. Novin
View Replies !
Date Type Parameter: OLEDB Driver Problem
Hi Friends, Inititally we were using ODBC driver for DB2. My reports we working fine(preview mode) with date type parameters. But When I changed it to OLEDB driver for AS400 DB2 it is throwing the exception while previewing the same report. Driver Name: "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider" ERROR Message: "The parameter fromDate doesnot have expected value?" Please help me. Thanks in advance Novin
View Replies !
String Type Multi-Valued Parameter Issue
Hi There. I have a Multi-valued parameter that is a string type and it freaks out when a do a select all from my drop down list. I suspect it has something to do with size as it works great if a pick a reasonable amount of items. My parameter list is populated by 1463 items of 12 characters each. Is there a threshold I should have to worry about? Thanks, Mike
View Replies !
Type-ahead In Multi-select Parameter Lists?
When running reports in preview mode in Visual Studio I can use type-ahead to get to selections in a dropdown multiple select parameter list by typing the first letter; typing w for instance will go to the first word in the list starting with 'w' and will automatically check it. Very useful if the lists are long enough to require scrolling. When the report is deployed to the Report Server Web page the type-ahead feature disappears. Is this because the dropdown is presented as HTML? Is there any way to get the type-ahead feature short of using a Report Viewer within a .NET application. I prefer the simplicity of the Web deployment rather than creating a VB or C# application just to show the report. I noticed that, for single-select parameters, the type-ahead still works; I'm guessing that is because they don't have the checkboxes in front of the words. Thanks for any help.
View Replies !
Problem Using Data Type Text As Output Parameter
Hello, My datalayer of C# code is invoking a stored procedure that returns a varchar(max) SQL data type. In my ASP.NET code, I have: SqlCommand myCommand = new SqlCommand("usp_GetTestString", myConnection); myCommand.Parameters.Add(new SqlParameter("@TestString", SqlDbType.Text)); myCommand.Parameters["@TestString"].Direction = ParameterDirection.Output; myConnection.Open(); myCommand.ExecuteNonQuery(); return Convert.ToString(myCommand.Parameters["@TestString"].Value); The query fails to execute and returns an error: String[1]: the Size property has an invalid size of 0. If I change the SqlDbType.Text parameter type to SqlDBType.Varchar, 100 (or any other fixed varchar length), it works but limits the length my unlimited field text. Any suggestions on how I can use db type text or varchar(max)? The field I need to retrieve is string characters of unlimited length and hence the datatype varchar(max).
View Replies !
Column, Parameter, Or Variable #1: Cannot Find Data Type SqlDatareader
Hello Everyone,A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index) { if(dr.GetSqlValue(index) == null) return SqlInt32.Null; else return dr.GetSqlInt32(index) }I than allways get the following ErrorMessage:Column, parameter, or variable #1: Cannot find data type SqlDatareader.Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.Would appreciate any kind of help! Thanks
View Replies !
SqlDataSource.Select Error: Unable To Cast Object Of Type 'System.Data.DataView' To Type 'System.String'.
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. 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: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String) Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String) Line 57: Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.] ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56 ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743 Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
View Replies !
Unable To Cast COM Object Of Type 'System.__ComObject' To Class Type 'System.Data.SqlClient.SqlConn
Dear all, I am stuck with a SSIS package and I can€™t work out. Let me know what steps are the correct in order to solve this. At first I have just a Flat File Source and then Script Component, nothing else. Error: [Script Component [516]] Error: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction) Script Code (from Script Component): ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data.SqlClient Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Dim nDTS As IDTSConnectionManager90 Dim sqlConnecta As SqlConnection Dim sqlComm As SqlCommand Dim sqlParam As SqlParameter Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim valorColumna As String Dim valorColumna10 As Double valorColumna = Row.Column9.Substring(1, 1) If valorColumna = "N" Then valorColumna10 = -1 * CDbl(Row.Column10 / 100) Else valorColumna10 = CDbl(Row.Column10 / 100) End If Me.Output0Buffer.PORCRETEN = CDbl(Row.Column11 / 100) Me.Output0Buffer.IMPRETEN = CDbl(Row.Column12 / 100) Me.Output0Buffer.EJERCICIO = CInt(Row.Column2) Me.Output0Buffer.CODPROV = CInt(Row.Column7) Me.Output0Buffer.MODALIDAD = CInt(Row.Column8) Me.Output0Buffer.NIFPERC = CStr(Row.Column3) Me.Output0Buffer.NIFREP = CStr(Row.Column4) Me.Output0Buffer.NOMBRE = CStr(Row.Column6) Me.Output0Buffer.EJERDEV = CDbl(Row.Column13) With sqlComm .Parameters("@Ejercicio").Value = CInt(Row.Column2) .Parameters("@NIFPerc").Value = CStr(Row.Column3) .Parameters("@NIFReP").Value = CStr(Row.Column4) .Parameters("@Nombre").Value = CStr(Row.Column6) .Parameters("@CodProv").Value = CInt(Row.Column7) .Parameters("@Modalidad").Value = CInt(Row.Column8) .Parameters("@ImpBase").Value = valorColumna10 .Parameters("@PorcReten").Value = CDbl(Row.Column11 / 100) .Parameters("@ImpReten").Value = CDbl(Row.Column12 / 100) .Parameters("@EjerDev").Value = CDbl(Row.Column13) .ExecuteNonQuery() End With End Sub Public Overrides Sub AcquireConnections(ByVal Transaction As Object) Dim nDTS As IDTSConnectionManager90 = Me.Connections.TablaMODELO80 sqlConnecta = CType(nDTS.AcquireConnection(Nothing), SqlConnection) End Sub Public Overrides Sub PreExecute() sqlComm = New SqlCommand("INSERT INTO hac_modelo180(Ejercicio,NIFPerc,NIFReP,Nombre,CodProv,Modalidad,ImpBase,PorcReten,ImpReten,EjerDev) " & _ "VALUES(@Ejercicio,@NIFPerc,@NIFReP,@Nombre,@CodProv,@Modalidad,@ImpBase,@PorcReten,@ImpReten,@EjerDev)", sqlConnecta) sqlParam = New SqlParameter("@Ejercicio", Data.SqlDbType.SmallInt) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@NIFPerc", Data.SqlDbType.Char) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@NIFReP", Data.SqlDbType.Char) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@Nombre", Data.SqlDbType.VarChar) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@CodProv", Data.SqlDbType.TinyInt) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@Modalidad", Data.SqlDbType.SmallInt) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@ImpBase", Data.SqlDbType.Decimal) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@PorcReten", Data.SqlDbType.Decimal) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@ImpReten", Data.SqlDbType.Decimal) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@EjerDev", Data.SqlDbType.Decimal) sqlComm.Parameters.Add(sqlParam) End Sub Public Sub New() End Sub Public Overrides Sub ReleaseConnections() nDts.ReleaseConnection(sqlConnecta) End Sub Protected Overrides Sub Finalize() MyBase.Finalize() End Sub End Class Thanks a lot for your help
View Replies !
|
TRACKER
