How To Write A Function With Integer Parameter Accepting Null
Jan 19, 2007
I've got a function which inserts into a database, and has arguments for each item being inserted
A couple of the items are integer datatypes (in SQL), but they will accept nulls
When I add my parameters, it asks to explicitly use the SQL datatype (which is integer):
.Add("@myParam", SqlDbType.Int).Value = myParam
In the header of the function, I assumed I could make the argument optional -
Optional ByVal myParam as Integer=System.DBNull.Value
However, when the function runs, I always get an error:
System.InvalidCastException was unhandled by user code
Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
I make them all Optional (which won't happen, but various arguments may be, at different times
and I get this error, with the last item (which is on a separate line), in red:
Constant expression is required.
I have created a stored procedure and when i execute it in sql server2005 it runs fine...the user can put in a parameter or none at all andthe corresponding results are returned.@name varchar(20) = NULLselect person.addressfrom personwhere person.name = @nameMy problem comes when i try using the stored procedure in my report inms reporting services. When i run the report and try not entering avalue for the parameter i get an error saying that it is required thati enter the parameter. Any ideas?
Display based on customerid display max of item they purchased on a order display only number like cust id pursed 12 items in 3rd order so when i enter customerid it should display 12.
using row number in sql server 2012.creating storeprocedure accepting customer id as input parameter.
cid oid items 1 1 10 1 2 12 1 3 3 1 4 4
so if we enter 1 as custid it got to give us 12 as the result..
Hi,I have SQL Server DB, and I tried to insert some data. I used StorProc to do the insertion as follows:ALTER PROCEDURE dbo.InsertPage ( @PageID int, @ParID int, @ChiID int, @PageContent ntext )AS INSERT INTO MP_Page (PageID, ParID, ChiID, PageContent) VALUES (@PageID,@ParID,@ChiID,@PageContent) RETURNThe problem happened when I tired to insert int null value in field ChiID which is allow null and here is my codeprivate void InsertPage(PagesDB PageDB, out pagedetails pages) { if (txtChild.Text == "") { int varChildID; varChildID = int.Parse(txtChild.Text.Trim()); pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), varChildID, int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim()); } else { pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), int.Parse(txtChild.Text.Trim()), int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim()); } PageDB.InsertPage(pages); } the error message says Input string was not in a correct format.Any idea ??Thank you
I'm using a table to hold data that I can pass back to the original form page and re-populate the fields that were not validated correctly. The stored procedure either inserts or updates the record in the temp table I've created.
So, currently, as I'm testing, I'm just passing empty values to all the parameters and the @state parameter is failing and throwing the error.
I've double checked that the table has the state column set to integer datatype
The column is set as follows:
Name datatype length Allow Nulls ---------------------------------------------- State int 4 checked
I have tried setting the default value for every column to Null in the table and then also not using a default value. Either way, I still recieve the same error?
Not sure what else to look at?
It seems the problem might be that instead of a null value being passed to the parameter that it is actually empty. Can passing an empty value to a column of datatype integer cause this problem? If so, is there a way to correct it?
I am new to SQL Server and am trying to figure out why it behaves the way it is regarding integers with null values.
I have a table that contains integer datatypes which can be null. If i insert the record with a blank field i receive an INCORRECT SYNTAX NEAR ',' error. If i surround the form value with '', it inserts a 0. Why does SQL Server behave like this? And whats the proper way to handle inserts such as this?
I have cascading parameters in my report, and the vast majority of the fields on which the parameters are based are varchar fields. In each of the drop-down menus on the report, I would like to have an option at the top of the list called <All> which allows the user to select all of the possible values.
I have done this successfully for all the varchar fields, but there is one parameter which is based on an integer. This obviously means it won't accept the value <All>. So, as a temporary workaround I have created an option called -1, which when selected by the user, selects all the possible values in the list. However, this is likely to be confusing for users if they don't realise that the -1 option means "all". Is there any way I can make the -1 actually appear in the list as <All>? How would I go about this?
The available values for the parameters come from an underlying dataset query.
Hi have a problem to solve and I hope that this is not a SSRS Bug.
I created a Reports(using SQL Server Project) which has several parameters which values are passed to a SP.
One of these parameter is an Integer and it is an optional value, so if the user fill it is used by the SP, otherwise the SP uses NULL and run anyway.
I starts to define tha parameter:
Datatype = integer
Allow blank value
Available: Non queried
Default: Null
if I want to Preview the report I have to provide an integer to the parameter's field ...
If for instance I set:
Default: Not queried = 0
In the moment I deploy and I use the ReportViewer in my window application the parameter's field is unabled!!
So I tried this solution:
Datatype = integer
Allow blank value
Allow null value
Available: Non queried
Default: Null
In the preview the checkbox: NULL is checked and I click on the View Report.
But when I deploy it,in the ReportViewer in my window application the parameter's field this checkbox is unchecked.
Do I forget something during my setting??I have to control it programmatically??
N.B. By default the user will not user this parameter so the best is that he can click directly on "View Report" without any additional "work" on the parameter!!
I'm trying to build an SQL string that should look like this when executed: UPDATE [Table] SET Active = 'False' WHERE ID IN (3, 4, 5, 6, 7, etc.) I'm using the convention (in code behind):SqlCommand cmd = new SqlCommand("UPDATE [Table] SET Active = 'False' WHERE ID IN (@TheIDs)", connection);cmd.Parameters.Add("TheIDs", SqlDbType.Text).Value = theIDsAsAnArrayList; But logically enough I cannot insert them as a text string as they have to be integers seperated by commas. Question: How can I convert an Array of integers into ... well, a string without the quotes, if you know what I mean? As it cannot end up like this: UPDATE [Table] SET Active = 'False' WHERE ID IN ("3, 4, 5, 6, 7, etc.") Note the quotes around the integers. Any hints on doing this with security in mind are welcome. I know I can concatenate the whole lot as strings, but this is unsecure, so I'm not going for that approach.
I want to convert a string into an interger so that my parameter can get one value, and have a seperate matrix list the value before the value selected.
My parameter is year. The user picks the year. And i want the crashcounts for the year displayed in the matrix. Then i have another matrix with a dataset similiar. I want this seperate Matrix/Dataset to display the previous year.
SO if the user selects 2004 from the dropdown. 2004 is displayed in the first matrix, and 2003 is displayed in the second matrix. The year attribute is in string format, and i cant change it in the cube. So i was told it could be converted in reporting services. with this
Now my problem is that this value of this string is actually an integer. The reason this is a data type string is because its a dimension and these are always string only measures are integer...
Can someone help me make this MDX query sort on integer value instead of string.
[Time].[Week].MEMBERS contains values like 8,11,20 but is declared are string because its a dimension please help me out because i'm getting the feeling this is impossible with this microsoft tool...
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
I was comparing the parameters for two stored procs that I made using the SQL Server 2005 express management studio. Both of these sprocs only inserted one field into a single table. These were both of the type varchar.
One of the sprocs had "nocount on" and the other did not. I thought I would see the returns integer parameter in the sproc that did not have "nocount" set to on. I thought this is what returns an integer to validate an insert. Obviously, I am confused about how this works.
Can anyone help me to understand that difference between nocount on and the parameter that returns an integer.
my problem is that i have a integer report parameter that must be multivalued. The parameter is populated by query. The thing is that in the beginning, there is no data in the dataset of the specific parameter. The table which is source to the dataset will br populated after some time from an XML.
Reporting services prompts the user to select a value for the parameter. But there is no value to select, yet. I cannot have leave blank because it is a string and not an int and i cannot have null because the parameter is multivalued. Any suggestions?
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck. Error: Unterminated String Constant. What is the problem? Set @strSQL='Select * From(SELECT Row_Number() Over(Order By ' + @SortExpression + ') as Row_Count,Rank() Over (Order By ' + @SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID, dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAMEFROM dbo.EVENT_LOGS INNER JOINdbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID) as TableInfoWhere Row_Count Between ' + @startRowIndex + ' and ' + @maxRowIndex + ' ';Exec(@strSQL);
I'm building a report and I want to have a parameter that has multiple integer values (not text).I can get it to work just fine if I create a value for each and every numeric value, but I want the user to only see two choices in the dropdown menu (plus the "Select All" that gets automatically created when I tick the box for "Allow multiple values"). How can I make this work?
For example, for my TYPE parameter I'd like something like this: Label: Tigers Value: 2 Label: Lions Bears Oh My Value: 1,3,4,5,6
But I can't seem to set a multiple Value for an integer. And I can't seem to wrap the value with quotes and use Text as the Data Type because it says an error occured during processing (I assume because the field I'm going against is of the Integer format).My query would look something like this (the real one is a lot more complex):
SELECT v_R_System.Netbios_Name0 ,v_GS_COMPUTER_SYSTEM_EXT.PCSystemType0 FROM v_R_System INNER JOIN v_GS_COMPUTER_SYSTEM_EXT ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM_EXT.ResourceID WHERE v_GS_COMPUTER_SYSTEM_EXT.PCSystemType0 IN (@TYPE)
I've tried different Expressions, filters, options, syntax, and what-not, but I think I'm missing something basic.
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?
I have a User Defined Function in my SQL Server 2000 database which takes a string and adds an integer quantity to it. The function basically takes the string (string because of first character and spaces) and adds an integer to it creating a new number (starting number and ending number concept).If I pass in a string that has a letter for the first character, it works fine. However, if the first character is an integer it trims out all 0s and whitespace and ruins the necessary formatting. Note, the formatting is always the same - x xxx xxxx xx. Any help or ideas would be appreciated.Example:D 499 8900 01 plus a Quantity of 10 returns D 499 89000 11 which is perfect.However,0 076 0000 03 plus a Quantity of 1 returns 764 764 (it should be 0 076 0000 04)------------------------------------CREATE FUNCTION [dbo].[NEW_End_NR2]( @OldStr as char(20), @Quantity int )RETURNS @NewNR_Tbl TABLE (New_EndNr char(20) primary key)AS BEGIN DECLARE @NewStr char(20) DECLARE @addNr integerBEGIN Set @addNr = @Quantity set @NewStr = (select REPLACE(@OldStr,left(@OldStr, 1),'')) set @NewStr = (select REPLACE(@NewStr,' ', '')) + @addNr set @NewStr = (SELECT left(@OldStr, 1) + SPACE(1) + left(@NewStr,3) + SPACE(1) + left(REPLACE(@NewStr, left(@NewStr,3), ''), 4) + SPACE(1) + left(REPLACE(@NewStr, left(@NewStr,7), ''), 3)) BEGIN INSERT INTO @NewNR_Tbl (New_EndNr) VALUES(@NewStr) END END RETURNEND
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you can not select null in multivalue parameter.How can i do it. i am using share point list.
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
I have a Store Procedure that kept wanting to specify something like maidenName = null if there is not value for maidenName field. The maidenName is already set up to allow null value. How do I write the store procedure to automaticaly recognized that if a field has nothing in it's a null? Below is the error I kept getting if I do not have the expiration number. Msg 201, Level 16, State 4, Procedure spInsertName, Line 0 Procedure 'spInsertName' expects parameter '@expNo', which was not supplied.
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment ( @startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())), @enddate Date = (Convert(Date,@StartDate-90) ) RETURNS TABLE AS RETURN ( SELECT EquipID, EmpName, IssueDate FROM HR.Equipment WHERE IssueDate <=@StartDate and IssueDate >=@EndDate ) GO
Hi. I wrote a Work Order schedule on Report service and I want user click BOMName field to display the BOM Detail, but I don't want user export or printer, so on this report field(BOM_List), I put a url on navigation, so when user click this field, system will jump to BOM report, Does anybody knows how to set a parameter on URL? I wrote like this http://Server1/reportserver?%2fPD%2fBOMList&rs:command=render &rc:toolbar=false&FatherCode=First(Field!BomName.Value)
FatherCode is BOM Report parameter, BOMName is WO schedule report field.
