w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Stored procedure returns Null when insert
try this when SET NOCOUNT ON; then SP not return value Becouse of this your command.executenonqeury require result other wise it roll back operation so after insert statement write select scope_identity() return last inserted PK after insert write if you write select scope_identity() select scope_identity() now your processure like this ALTER PROCEDURE [dbo].[RegisterOnline_Insert] --@OrderType int, @SiteID as varchar(50), @CustName as varchar(100), @Address as varchar(255), @Phone as varchar(20), @HP as varchar(20), @Email as varchar(100), @Description as varchar(500), @IsValid as bit AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. DECLARE @House as numeric(10,0) DECL

Categories : Sql Server

Stored procedure error when being called
There several problems Since you can PREPARE only from user variable, you don't need a local variable q You're missing @ for @q variable when using CONCAT() set @q = concat(q,' AND ALIAS_SECTION_ID = ',section_id); ^ Since your IN parameters defined as VARCHAR you better quote their values when you build the query That being said your SP might look like this DELIMITER $$ CREATE PROCEDURE `ALIAS#SEARCH`( in section_id varchar(255), in category_id varchar(255), in content_id varchar(255) ) BEGIN SET @q = 'SELECT * FROM tbl_alias WHERE ALIAS_ACTIVE_STATUS = 1'; IF section_id IS NOT NULL THEN SET @q = CONCAT(@q, ' AND ALIAS_SECTION_ID = ''', section_id, ''''); END IF; IF category_id IS NOT NULL THEN SET @q = CONCAT(@q, ' AND ALIAS_CATEGORY_ID = '

Categories : Mysql

Stored procedure returns int instead of result set
When you generated your model class for your stored procedure, you chose scalar return result by mistake. you should remove your stored procedure from your entity model, then re-add the stored procedure. In the dialog for the stored procedure, you can choose the return type you are expecting. Do not just edit the generated code.. this may work now, but the generated code can be replaced if you make other changes to your model.

Categories : C#

Stored Procedure Returns zero results
The problem is the stored procedure. The expression: WHERE Shipment_Po.PONumber = '''+ @PON +''' Is not doing what you think. It is doing the following comparison: WHERE Shipment_Po.PONumber = '+@PON+' Or something like that. In other words, you are mixing dynamic SQL expressions with regular SQL. Try doing: WHERE Shipment_Po.PONumber = @PON If you are concerned about the cast to the right type: WHERE Shipment_Po.PONumber = (case when isnumeric(@PON) = 1 then cast(@PON as int) end)

Categories : Asp Net

Stored procedure slower when called from ASP.NET vs. SQL Mgmt Admin
I had a similar issue last year. Try to enable arithabort in your stored procedure: SET ARITHABORT ON Excerpt from msdn The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.

Categories : C#

stored procedure returns result with 2 params only
This might be a caps issue. If so then the following would fix it: (upper(fname) = upper(@fname) or @fname = '') and (upper(lname) = upper(@lname) or @lname = '') or it could be a white space issue in which case the following would fix it (rtrim(ltrim(fname)) = rtrim(ltrim(@fname)) or @fname = '') and (rtrim(ltrim(lname)) = rtrim(ltrim(@lname)) or @lname = '') or both.

Categories : Sql Server

Running Stored Procedure from Matlab Only Returns 1 row
Strangely, it the code started working again. All I did was write some diagnostic code to count the number of records in the tables that latest_hl_tradables() queries. Initially those only returned 1 row. Then when they started returning all the rows. I don't know what changed.

Categories : Mysql

Stored procedure return incremented number every time its called.
You could store the number in a table and UPDATE it, then SELECT (return) it, each time. If you decide to do this then you would first create the table, then create a stored procedure. You should at least attempt to create the stored procedure (having created the table).

Categories : Sql Server

Bit parameter with Null value in Stored Procedure
You could either set null values to 0 and check it like this: WHERE Isnull(Sub,0) = @SubscriberState or have a tri-state sort of bodge like: WHERE Isnull(Sub,3) = isnull(@SubscriberState,3)

Categories : SQL

How to send null value to a stored procedure?
Use the built in value: DBNull.Value http://msdn.microsoft.com/en-us/library/system.dbnull.value.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

Categories : Asp Net

Data is Null when trying to run basic stored procedure
First, try running the query in your management studio with the supplied car ID to see what results it is passing back. Secondly I would try to change the if statement as follows and see if you still have issues: If Not myReader.IsDBNull(0) Then Additionally, all subsequent fields that allow NULL values must be checked for IsDBNull before they are used. This may also cause some issues.

Categories : Vb.Net

How to call a SQL Server stored procedure in asp file which returns a table
var query = "YourStoredProcName"; var conStr = ConfigurationManager.ConnectionStrings["StringName"].ConnectionString; var con = new SqlConnection(conStr); var cmd = new SqlCommand(query, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@parameterName", parameterValue); //If any //Open connection - load result into datatable con.Open(); var reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); con.Close(); conStr can either be in that format (using the web.config stored connection string), or in as a raw string. I'd recommend this way though. After this runs you'll have a datatable (dt) that contains the result of your stored procedure, which you can manipulate as you please! You can find the connection string by adding the dat

Categories : C#

Using Multiple Parameters in SSRS from Stored Procedure Returns Blank
You can only use IN with static defined within your query. In this cas use = CREATE PROCEDURE [dbo].[usp_GetPerson]( @Category varchar(20)) AS BEGIN SELECT FirstName, LastName, Category FROM tblPerson WHERE (Category = @Category)

Categories : SQL

NHibernate stored procedure returns data from previous query
I'm going to take a stab at answering this, because I think I have a hunch of what's going on, and I want to set you on the right track. I've made a lot of assumptions here, so please don't be too harsh on me if I was completely wrong with my guesses. It feels like you're trying to use NHibernate as a tool to simply translate rows into objects. Instead NHibernate is a tool that translates between your object oriented domain model and your relational database domain model. It does a lot more that just turn rows into objects. In particular, the NHibernate feature that you're tripping over here is how NHibernate ensures that within a single NHibernate session, a single row in the database which represents a single entity will correspond to a single instance of an object. It uses its fir

Categories : C#

Deadlock in a simple PL/pgsql stored procedure that updates a single row (called by Hibernate)
Firstly, the exclusive lock you see in: ExclusiveLock on tuple (8,72) of relation 16890 of database 16751 This should hopefully be your "table01" - try: SELECT * FROM pg_class WHERE oid = 16890; Secondly, yes you have processes 23495 and 23481 blocking each other. PostgreSQL detects this after waiting 1 second and cancels 23495. Then, a few lines further down 23481 goes through after 1000.361ms. To test this, you'll want two terminals open, each running psql. That way you can control the pauses in each. Issue a BEGIN in each and try running the update in both terminals. Take a look at the pg_locks view to see what's going on. None of my experimenting has been able to reproduce this, and I can't see how it can. The ShareLock you see is probably the one preventing changes to the tab

Categories : Hibernate

Stored Procedure returns exception in .net but runs fine in sql server c# winforms
When you select the checkbox to pass null values, then it explicitly pass the NULL values alongwith the parameters. In your current code, you are not even passing the parameters that is why the exception. In current form your stored procedure is not ready to accept NULL values for startdate and enddate, you should modify your procedure as: @stardate datetime = NULL, @enddate datetime = NULL For passing NULL DateTime from C# use DateTime? or Nullable<DateTime>, which supports Null values.

Categories : C#

SQL Server stored procedure that returns a boolean if table exists, c# implementation
Assuming you have a stored procedure like this which selects either a 0 (table does not exist) or 1 (table does exist) CREATE PROCEDURE dbo.DoesTableExist (@TableName NVARCHAR(100)) AS BEGIN IF EXISTS (SELECT * FROM sys.tables WHERE Name = @TableName) SELECT 1 ELSE SELECT 0 END then you can write this C# code to get the value - use .ExecuteScalar() since you're expecting only a single row, single column: // set up connection and command using (SqlConnection conn = new SqlConnection("your-connection-string-here")) using (SqlCommand cmd = new SqlCommand("dbo.DoesTableExist", conn)) { // define command to be stored procedure cmd.CommandType = CommandType.StoredProcedure; // add parameter cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 100).

Categories : C#

Sending null parameter to querystring Stored Procedure
try like this CREATE PROCEDURE spGetAllProducts @catID int = null, @subcatID int = null AS BEGIN SELECT * FROM Products WHERE ((@catID '') OR (category_id = @catID)) AND ((@subcatID = '') OR (subcategory_id = @subcatID)) END I think as stored procedure takes it as empty string not as null ,I had similar problem and it worked for me

Categories : C#

How to check if a variable is NULL, then set it with a MySQL stored procedure?
@last_run_time is a 9.4. User-Defined Variables and last_run_time datetime one 13.6.4.1. Local Variable DECLARE Syntax, are different variables. Try: SELECT last_run_time; UPDATE Example: /* CODE FOR DEMONSTRATION PURPOSES */ DELIMITER $$ CREATE PROCEDURE `sp_test`() BEGIN DECLARE current_procedure_name CHAR(60) DEFAULT 'accounts_general'; DECLARE last_run_time DATETIME DEFAULT NULL; DECLARE current_run_time DATETIME DEFAULT NOW(); -- Define the last run time SET last_run_time := (SELECT MAX(runtime) FROM dynamo.runtimes WHERE procedure_name = current_procedure_name); -- if there is no last run time found then use yesterday as starting point IF(last_run_time IS NULL) THEN SET last_run_time := DATE_SUB(NOW(), INTERVAL 1 DAY); END IF; SELEC

Categories : Mysql

Getting a SqlException when I try to insert null values through a stored procedure
Set the default value for the parameter as null, try this: @CompanyID INT, @email VARCHAR(50) = null, @phone VARCHAR(50) = null, @fax VARCHAR(50) = null, @fName VARCHAR(50) = null, @lName VARCHAR(50), @sendVia VARCHAR(50), @default BIT, @repo TINYINT, @fail TINYINT, @borrow TINYINT, @loan TINYINT

Categories : C#

Stored procedure to not to retrieve null values from table
You should try this Alter PROCEDURE [dbo].[p_Get_HotelNamess] AS BEGIN SET NOCOUNT ON; SELECT Hotel_Info.Hotel_Name, Hotel_Info.HotelID FROM Hotel_Info WHERE Hotel_Info.Hotel_Name IS NOT NULL END

Categories : Sql Server

In stored procedure if null value found it should skip that insert query
Try IS NOT NULL instead of != NULL. In most relational databases, the comparison operators return false if either input is NULL, even if they both are. Here is a SQL Fiddle demonstrating the behavior. (Sorry. That SQL Fiddle really works.) Admittedly, this is confusing, as that fact should mean all your INSERTs are skipped. I don't see anything here that would insert empty rows.

Categories : Sql Server

Calling Stored Procedure inserts NULL in all columns in MySQL 5.5
You shouldn't put @ before the variable names. The variables @p_Code and p_Code are two different variables. @-variables are available for one connection's entire life so you can reach it from different procedures, triggers, normal queries and so on. Declared variables and procedure parameters are available in that scope only.

Categories : Mysql

Retrieve Stored Procedure Output Parameters in Entity Framework always null
You have to enumerate your result in order to actually execute the stored procedure. Try this: public List<searchProducts_Result> GetProducts() { var nameParam = new ObjectParameter("numbers", typeof(int)); List<searchProducts_Result> listobjs = new List<searchProducts_Result>(); // List<searchProducts_Result> objResult = null; searchProducts_Result outParam = new searchProducts_Result(); using (var db = new SPWebEntities()) { // by calling ToList() you execute the SP List<searchProducts_Result> objResult = db.searchProducts("asd", 2, 5, "15", nameParam).ToList(); if (nameParam.Value != null) outParam.UserID = nameParam.Value.ToString(); else outParam.UserID = "0"; listobjs.Add(out

Categories : C#

C# webservice returns null which called from php
HI maybe this WSDL to Php converter can help you http://www.wsdltophp.com/ it can make functions to make request and get responses correctly.

Categories : C#

Report is working fine but calling stored procedure in Management Studio not working
There are many string-based date formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not. The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings. The ISO-8601 format is supported by SQL Server comes in two flavors: YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations! or: YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has

Categories : Reporting Services

MySQL stored procedure OUT param returned as null (NO LOCAL VARIABLE DECLARED)
This is most unexpected ... as this works in sqlfiddle both with and without using a prepared statement: http://sqlfiddle.com/#!2/d4ddf/1, http://sqlfiddle.com/#!2/d4ddf/2 Maybe is there a problem with the permissions (would probably signal an error, thought) ?!? EDIT: still guessing -- are you sure to call foo from the right schema? O_o

Categories : Mysql

Command.ExecuteScalar always return null while Stored Procedure in Management Studio runs fine
The problem is in the way you return data. If you want to use ExecuteScalar, you should not RETURN but instead simply SELECT. Try to change the SP as following: CREATE PROCEDURE [dbo].[spCanUserEdit] ( @username nvarchar(255) ) AS BEGIN SET NOCOUNT ON; DECLARE @CanEdit bit SELECT @CanEdit = CanUserEdit FROM tblUsers WHERE username = LOWER(@username) SELECT @CanEdit RETURN 0 END GO If you can't change the SP, but the code, the solution is to read parameter '@ReturnValue' with ExecuteNonQuery.

Categories : C#

Stored procedure not working
Your stored procedure only takes one argument and you're calling it with two. To fix this you need to alter the procedure to take two arguments like this: create proc updateWork @EmployerName nvarchar(max), @Employer nvarchar(max) as begin update tblWorkData set EmployerName=@EmployerName where EmployerName=@Employer end I changed the whereclause as I guess you meant it to be. As it was before it didn't actually do anything at all. On a side note you might want to look into how to properly call procedures and how to add parameters in a way that isn't vulnerable to SQL injection.

Categories : Asp Net

Like not Working in Stored Procedure
Just put the single quotes in front of the first % and after the last %: SET @SS = CONCAT("SELECT * FROM `categorybanners` WHERE TIERCODES LIKE '%",pTier4,"%'"," and SITEID=",pSiteid," and CATLEVEL='tire4'"); So that your statement becomes: SELECT * FROM categorybanners WHERE TIERCODES LIKE '%t2_05%' and SITEID=46 and CATLEVEL='tire4'

Categories : PHP

CTE in stored procedure not working
An insert statement can use a CTE (with expression). In SQL Server, this goes before the insert, not before the select: WITH create_key AS( SELECT *, (CONVERT(varchar(25), a.NDC11, 101) + CONVERT(varchar(25), a.PharmacyID, 101) + CONVERT(varchar(50),ABS(a.TotalNetCost),101)) as REVERSAL_KEY FROM table1 a ), find_rev AS ( SELECT *, CASE WHEN MIN(TotalAmount) OVER (PARTITION BY REVERSAL_KEY) < 0 THEN 1 ELSE NULL END as Flag FROM create_key ), primary_flag as ( SELECT TOP 1500000 *, DENSE_RANK() OVER (ORDER BY REVERSAL_KEY) as [GROUP], ROW_NUMBER() OVER (PARTITION BY NDC11, PharmacyID, TotalNetCost ORDER BY REVERSAL_KEY) as PK FROM find_rev WHERE Flag = 1

Categories : SQL

Working with WHERE IN on Stored Procedure (MySQL)
Probably not really index friendly, but you may be able to use the find_in_set function... where find_in_set(product.id, ids) Otherwise you need to parse the delimitted string into a temp table and join with the table.

Categories : Mysql

Calling stored procedure from C# is not working
You should definitely use SqlCommand with your stored procedure. Try something like this. string connectionString = "your connection string"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("USP_VISUALIZAR_PIS_PROCESSO_IMPORTACAO_DETALHE_HISTORICO", connection)) { command.CommandTimeout = 0; command.CommandType = System.Data.CommandType.StoredProcedure; SqlParameter p1 = command.Parameters.Add("@DATA_HISTORICO", SqlDbType.DateTime); p1.Direction = ParameterDirection.Input; p1.Value = dataHistorico; SqlParameter p2 = command.Parameters.Add("@SIGLA_ESTADO", SqlD

Categories : C#

Fragment getActivity returns null because onAttach not called yet, how to synchronize?
I use a dirty trick like this (pseudocode) private YourType pendingActionParams; public void yourMethod(YourType parameters){ if(isVisible()){ performAction(parameters); }else{ pendingActionParams = parameters; } } @Override public void onActivityCreated (Bundle savedInstanceState){ if(pendingActionParams != null){ performAction(pendingActionParams); pendingActionParams = null; } }

Categories : Android

request.parameter(String) returns null when called from jquery
First you need to add the third parameter as json in $.post() like, $.post("./ProfileUser.jsp",{jsonData:jsonstr},'json'); Secondly, in JSP try it like, JSONObject json = new JSONObject(); if(!jsonData) { json.put("mobile", "111111"); } else{ //something; } response.setContentType("application/json"); response.getWriter().write(json.toString());

Categories : Jquery

Feedzirra::Feed.fetch_and_parse(url) returns null when called by Delayed::Job
The problem ended up being with the New Relic gem. See this bug report: https://github.com/pauldix/feedzirra/issues/167 The fix is to add disable_curb: true to the newrelic.yml

Categories : Ruby On Rails

getActivity().findViewById returns null, called from fragment onActivityCreated
Note that ListFragment has a getListView() method to return the ListView widget instance. Developer docs: ListFragment.getListView

Categories : Android

SQL Merge statement not working in Stored Procedure
You're Source shouldn't rely upon the target table. Try instead: MERGE [Users].[Addresses] AS Target USING (select @UserID,@Address1,@Address2,@Town,@County,@PostCode,@Country,@Type) AS Source (UserID,Address1,Address2,Town,County,PostCode,Country,Type) ON (Source.UserId = Target.UserId) WHEN MATCHED THEN UPDATE SET Target.Address1 = Source.Address1, Target.Address2 = Source.Address2, Target.Town = Source.Town, Target.County = Source.County, Target.Postcode = Source.Postcode, Target.Country = Source.Country WHEN NOT MATCHED BY TARGET THEN INSERT ([UserId], [Address1], [Address2], [Town], [County], [PostCode], [Country], [Modified], [Type]) VALUES(Source.UserId, Source.Address1, Source.Address2, Source.Town, Source.County, Source.PostCode, Source.Country

Categories : SQL

insert query not working in stored procedure
The problem is that the stored procedure arguments are the same as the names of the columns. Try this instead: DELIMITER $$ CREATE PROCEDURE `updateUser` (IN p_userId varchar(255), IN p_password varchar(100), IN p_male tinyint(1), IN p_birthday datetime, IN p_fbId varchar(160)) BEGIN START TRANSACTION; UPDATE `user` SET `male` = IFNULL(p_male, `male`), `password` = IFNULL(p_password, `password`), `birthday` = IFNULL(p_birthday, `birthday`) WHERE `id` = p_userId; IF fbId IS NOT NULL THEN ... END IF; COMMIT; END You are getting the error about male not accepting

Categories : Mysql

MySQL CASE not working in Stored Procedure
@unit_type is a user variable. We don't see anywhere that is being set, in the procedure. (It may be set in the session preceding the call; it will have whatever value was last assigned to it in the session.) We see the parameter passed in to the procedure is named unit_type. But we don't see any reference to that in the procedure. One quick fix would be to set @unit_type user variable, immediately prior to the SELECT, to the value from the parameter: SET @unit_type = unit_type; The same issue exists for all the parameters as well, there's no reference to them either. The other option to fix this would be to reference the procedure arguments in the SQL, replacing the references to the user_variables. (Looks like the quick edit there would be just to remove the @ characters.) The

Categories : Mysql



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.