.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
ASPEvil
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Post New Web Links

help inserting an empty string and a default date thru a stored procedure

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :ASP.Net
 

I want to insert an empty string in a nvarchar field and a default date like a base date in a datetime field inside a stored procedure without passing it the value thru a parameter

how is this done?

INSERT INTO deals_SoldItems (

deals_SoldItems.[TransactionID],

deals_SoldItems.[EmptyStringField],

deals_SoldItems.[BaseDateField]

)

VALUES (

@TransactionID,

"",

"01/01/1900

)

 

this dont work

cheers

 




View Complete Post


More Related Resource Links

Inserting rows via stored procedure and under certain conditions

  

I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

MyTable
ID (PK)
PersonID (FK)
LocationID (FK)
TotalItemsSold

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Using IN clause(string parameter) in Stored Procedure

  
Hi guys. I need to use IN clause in a selection query. From all the resources that I found from google, it's all about adding a function to split a integer parameter into a list. But I got a string parameter, for example 'A1,B1,C1' I tried to use the below where single quotes in @CustCode is double up. ___________________________________ Declare @SQL VarChar(1000)   SELECT @SQL = ' SELECT [something]' SELECT @SQL = @SQL + ' FROM [Table]' SELECT @SQL = @SQL + ' WHERE CustomerCode IN ('  + @CustCode + ')'   EXECUTE (@SQL) _____________ Executing the stored procedure in MSSQL works fine, but it didn't when executing from Visual Studio 2005. Please advice.

Stored Procedure Default Return Value

  
Tried searching and have not found the answer to this one.  Think I know from testing but want to be sure.   I have a SP on  MSSQL 2000 that I created.  Within the table I call 6 update statements.  All my params are input. I developing software using VS 2008 PRO with a typed dataset.  I added the stored procedure to my tableadapter and it created the subsequent update() method with an int return type.  I coded a statement to test the return value like so... int Success = tableadapter.update(value1,value2,etc) I examined the Success variable and found it to be 6. I looked at another SP that calls 7 update statements within it and the return value after being called was 7. I did not specify any return value in my SP.  I can only presume that the SP is by default returning the aggregate amount of rows affected.  My question is if my presumption is correct.  My reason for asking is I would like to know this value is reliable for testing that all 6 update statements in the SP were  successful.    Thank you.

inserting data after caliculation using stored procedure

  
hi my requirement is like i want to insert data to table after caliculationliketable name salaryhikecolumns salaryinput,20%hike, 25%hike and 30%hikesalaryinput column details  to be input by the user and hike is to be caliculated and inserted with 20%hike, 25%hike and 30%hike are columns of same table.plz help me.thank you very much.

call to stored procedure occasionally returns empty recordset

  

Hi

I have a devil of an intermittent problem that I have so far found nothing to help me solve.

I have a web application/module in Dotnetnuke that requires data for a simple form from another SQL server DB on a different server.  The app uses a connection string in the web.config like this:

<add name="CompanyUpdate" connectionString="Data Source=mail.mydatabase.be;Initial Catalog=MA_Online;Persist Security Info=True;User ID=Gus;Password=xxxxxx" providerName="System.Data.SqlClient" />

The application calls a stored proc and most of the time data is returned as expected. There should always be a row returned from the SP.  However, if the form is idle for a while or the module has only just loaded the call to the stored proc returns as empty recordset. A 'table not found' error occurs.

There is no connection issue otherwise there would be a connection error instead of an empty recordset right?. But I cannot work out why sometimes no data is returned. I can go onto the server and run management studio and run the sp over and again and always get data.

I don't have control over the target DB but the person who does thinks it's something to do with connection pooling. I tried turning this off in the connection string and found that I got an empty recordset every time the sp was called wit

Need a string output from stored procedure

  

hai everyone,,

I got a situation now... i had a stored proc: How can i get the final select statement in the code behind.


Here is the stored proc.

ALTER PROCEDURE [dbo].[tgms_reclass_PO_bip](@po VARCHAR(20), @old_gl VARCHAR(20), @new_gl VARCHAR(20),@output varchar(500) out )
AS


IF (SELECT COUNT(*) FROM mm2po WHERE scode = @po) = 0
BEGIN
    --SET @output = '1'
    SET @output = 'Invalid PO Number'
END



/*****************************
Check if Old GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@old_gl,'-','')) = 0)
BEGIN
    --SET @output = '2'
    SET @output = 'Invalid Old GL Account'
END



/*****************************
Check if New GL Code is valid
******************************/
IF (@output = '' AND (SELECT COUNT(*) FROM acct WHERE scode = REPLACE(@new_gl,'-','')) = 0)
BEGIN
    --SET @output = '3'
    SET @output = 'Invalid New GL Account'
END



/**********************

Forms Library Changed event and Validation event returns empty string on date picker

  

Hi there,

I'm trying to do date validation on a date and time picker through code (i want to make sure that the date is later than today and that the next date is at least one day later than that).

But when i try to get the value in the chagned event, i keep getting empty string as value.

What am i doing wrong here??

This is the code:

public void LeaveRequestFrom_Changed(object sender, XmlEventArgs e)
    {
      // Write your code here to change the main data source.
      XPathNavigator xroot = MainDataSource.CreateNavigator();
      XPathNavigator fromdate = xroot.SelectSingleNode(
        "/my:myFields/my:LeaveRequestFrom", NamespaceManager);


      if (fromdate.Value != "")
      {
        //Make sure the from date is in the future
        if (DateTime.Parse(fromdate.Value) < DateTime.Today.AddDays(1))
        {
          fromdate.SetValue("");
        }
      }
}

 


The world

how to pass XML file path as input string to a Stored Procedure

  
 

Hello All,

I need to pass the file path (or hardcode the XML file path ) of an XML file to a stored procedure. This SP will then read the values from the XML file and based on these values the Insertion / updation will be done thereafter.

I am not able to pass the XML file path to this stored procedure.  Right now I an doing as givn below :

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<configuration>
 <appSettings>
  <add key="approvalMode" value="On" />
 </appSettings>
</configuration>
'
EXEC sp_xml_preparedocument @idoc OUTPUT,'c:\Inetpub\wwwroot\HealthandSafety\Aspx\ApprovalMode.xml'

SELECT *
FROM OPENXML (@idoc,'/configuration/appSettings/add',0)
      WITH ([key]  varchar(10),
            value varchar(20))
EXEC sp_xml_removedocument @idoc

Here any change in the xml file needs to be changed in the SP too. This will lead to double work as later on this SP will be made as an SQL job.

Is there a way I can pass or hardcode the XML file path in the SP rather than duplicating the XML file contents again

Please advice!!


Problem with decryption data in stored procedure if local date on my machine was changed

  

Hi people!

Can anybody help me with my problem?

I have one machine (Server 2003 x64 SP2, SQL Server Standard Edition (64-bit) version 9.00.4053.00). Also I have a stored procedure in database that doing a validation of users, and contains the next code:

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
  SELECT
  [UserId],
  [AccountNumber],
  [Login],
  CONVERT(varchar, DecryptByKey(EncryptedPassword)) AS 'Password'
  FROM [User]
  WHERE ([Login] = @Login)
        AND (CONVERT(varchar, DecryptByKey(EncryptedPassword)) = @Password);
CLOSE SYMMETRIC KEY PasswordFieldSymmetricKey;

All works correctly but if i change the "Date and Time Properties" on my machine i receive the next error

(0 row(s) affected)
Msg 15315, Level 16, State 1, Procedure user_login, Line 111
The key 'PasswordFieldSymmetricKey' is not open. Please open the key before using it.

If i set the current date it will work correctly again.
One strange thing:
if execute the query "SELECT * FROM sys.symmetric_keys" i have the next date of creation for my symmetric keys

2010-11-04 11:18:49.300

And if i set a date less then date of

problem inserting using a stored procedure and identity column.

  

I recently moved a .net site from one machine to another, now for some reason one of the stored procedures is throwing an exception when attempting to insert! 

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'dbo.tbl_Events' can only be specified when a column list is used and IDENTITY_INSERT is ON

BTW, the column in question does have the identity set to Yes in management studio

I was using originally SQL 2005, now its on SQLexpress 2008

stored procedure:

ALTER

@EventID

PROCEDURE [dbo].[proc_EventsAddEdit]int,

@EventName

varchar(200),<

Looping through Textbox Values and Inserting into SQL Server via Stored Procedure

  

Hello All,

I have a database table as follows:

USE [Diel_inventory]
GO
/****** Object:  Table [dbo].[QUOTEDETAILPARTS]    Script Date: 05/09/2010 17:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QUOTEDETAILPARTS](
	[QuoteDetailPartID] [int] IDENTITY(1,1) NOT NULL,
	[QuoteDetailID] [int] NOT NULL,
	[PartNumber] [float] NULL,
	[Quantity] [int] NULL,
 CONSTRAINT [pkQuoteDetailPartID] PRIMARY KEY CLUSTERED 
(
	[QuoteDetailPartID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[QUOTEDETAILPARTS]  WITH CHECK ADD  CONSTRAINT [fkQuoteDetailID] FOREIGN KEY([QuoteDetailID])
REFERENCES [dbo].[QUOTEDETAIL] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[QUOTEDETAILPARTS] CHECK CONSTRAINT [fkQuoteDetailID]


This table receives data from my web application via a stored procedure, snippet pasted below:

 

nsert into dbo.QuoteDetailParts
	(QuoteDetailID, PartNumber, Quantity)
	values (@QuoteDetailPartID, @PartNumber, @Quantity) 
	
	set @QuoteDetailPartID=scope_identity()

 

In my quote.aspx page, I have a wizard control that collects numerous data points.  In one of the wizard steps, I hav

stored procedure for getting date,timme

  

hi,

i wish to create a stored procedure which can retrieve the date or time less than or greater than current sys date.. in my table, date and time are as separate fields..

thanks in advance


!< Ganesh >! Do not tell the world whom you are...Show it... !!

Problem in Stored procedure when inserting data through openxml

  

I have stored proc where i insert some value from openxml but it is not inserting that xml data. Below is my stored proc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_MKTG_SERVICE_INSERT_NEW]
@ID AS INT,
@STATUS AS NVARCHAR(20),
@USERNAME AS NVARCHAR(50),
@DATEOFVISIT AS DATETIME,
@SUMMARY AS NVARCHAR(500),
@HOMESERVICE AS NVARCHAR(5),
@EXPIRYDATE AS DATETIME,
@SERVICELIST XML
AS
 SET NOCOUNT ON

BEGIN TRAN
IF EXISTS(SELECT * FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID)
BEGIN
	DELETE FROM NEW_DTL_SERVICE WHERE PARTNERID=@ID
END

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
		DECLARE @IDOC INT
		EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT,@SERVICELIST
		INSERT INTO NEW_DTL_SERVICE
		(PARTNERID,[SERVICENAME],[TYPE],DISCOUNT,COMMISSION,CONDITION)
		SELECT @ID,SERVICENAME,TYPE,DISCOUNT,COMMISSION,CONDITION
		FROM OPENXML(@IDOC,'/NEWDATASET/TABLE1',2)
		WITH (SERVICENAME NVARCHAR(50),TYPE NVARCHAR(50),DISCOUNT NVARCHAR(50),COMMISSION NVARCHAR(50),CONDITION NVARCHAR(500))
		EXEC SP_XML_REMOVEDOCUMENT @IDOC
IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
EXEC TIEDUPPARTNER @STATUS,@ID

IF (@@ERROR <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END
COMMIT TRAN


and following is my xml which i generate to insert.

dropdown list fill with Linq and convert string to datetime and ordering and stored procedure call.

  

I wanted to share this more than anything,  it took me most of the morning searching and finding various post on various sites but I got it figured out and working.  It might be better coded, but it is a start for some one trying to understand how linq and ASP.Net things work.

What I have is a database table with a datetime in string format and a stored procedure which returns all the data.
What I want to do is load a dropdown list with MonthName and Year for a selection choice on generating monthly report.

The stored procedure is in a Linq to SQL class dbml and the connection string is dynamic, i.e., made througha call to another class.

here is the code.  Enjoy understanding how it works.

// Miscellaneous Details are filled in for helping you get the big picture.
// Some of the using statements are for other things in the code behind, 
// but I left it in so don't get confused,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections.ObjectModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using MYProject.LinqtoSQLStuff;

namespace MYProject.ChooseReportPage
{
	 public partial class TheReportPage : System.Web.UI.Page  
	 {
            
	     protected void Page_Load(object sender, EventArgs e)
            {

   

Passing a SSIS Variable with a string datatype to a Stored Procedure requiring a uniqueidentifier

  

I have a SSIS package that uses a SQL Server stored procedure as a data source. The parameter in the stored procedure is pulling data from a column with a uniqueidentifer (guid) data type. I created a package level variable in SSIS with a string datatype. SSIS variables do not offer a uniqueidentifier datatype.

I have the following text in the SQL Command text box:

exec dbo.spBillingReferenceSource
@BatchID = ? 

When I click the parameters button the mappings are @BatchID for the parameter (which is the exact name of the parameter in the stored procedure) and User::BatchID (the SSIS variable) 

When I run the stored procedure in preview mode in the SSIS dataflow, it returns an error message -- "No value given for one or more of the required parameters"

If I run the stored procedure by typing the value in the datasource box such as listed below, it works and delivers the proper records. I have even modified the stored procedure to convert the column to use varchar(36). However, it still returns the same error message. Does anyone have any suggestions on how I can get this to work. Thank you.

exec


Send Email from SQL Server Express Using a CLR Stored Procedure

  
Problem
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Creating A Stored Procedure Which Searches Team Names

  

Hi

I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.



Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend