Custom Data Types In A Stored Procedure
Jun 12, 2008
I am new to working with custom data types. I am trying to use one as an input parameter for a stored procedure, but I'm not sure what the syntax is.
I the design table view, the data type shows up as this:
DISPOSAL_AREA_NAME_TYPE:varchar(40)
What is the proper way to reference it in a stored procedure? Here is what I have, but it errors out:
CREATE PROCEDURE webservices_BENEFICIAL_USES_DM_SELECT
@DISPOSAL_AREA_NAME [DISPOSAL_AREA_NAME_TYPE:varchar(40)] = ""
AS
BEGIN
View 4 Replies
ADVERTISEMENT
May 17, 2007
I created a stored procedure based custom conflict resolver in SQL 2005, I return the winning result set and also save that result set to a test table to compare the values. The values saved to the test table are correct but some of the values saved as the conflict winter are truncated.
Example a char(3) filed is updated at the subscriber as €˜111€™ and updated at the publisher as €˜222€™, in my custom conflict resolver if I use the value from the subscriber the conflict resolver updates the field as €™11 €˜, if I use the publisher value the conflict resolver updates the field as €™22 €˜. Now the same records is saved to the test table correctly as either €˜111€™ or €˜222€™ depending on the logic I used. So the result set has the correct values, its after the custom conflict resolver is called where the values is somehow truncated. Has anybody run into this before and what steps can I take to avoid this.
Thank you,
Pauly C
View 1 Replies
View Related
Oct 12, 2007
Hello, I receive this error "Incorrect syntax near 'GetGalleryPaged'." I'm trying to use custom paging on a stored procedure. ....... Dim mySqlConn As New SqlConnection(ConnStr) Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn) objDA.SelectCommand.Parameters.Add("@startRowIndex", SqlDbType.Int, 1) objDA.SelectCommand.Parameters.Add("@@maximumRows", SqlDbType.Int, 9) Dim objDS As New DataSet() Dim objPds As PagedDataSource = New PagedDataSource objDA.Fill(objDS, "Gallery") <<----error here mySqlConn.Close() objPds.DataSource = objDS.Tables(0).DefaultView objPds.AllowPaging = True....... ALTER PROCEDURE dbo.GetGalleryPaged ( @startRowIndex int, @maximumRows int)AS SELECT idgallery, g_picpath FROM ( SELECT idgallery, g_picpath, ROW_NUMBER() OVER (ORDER BY idgallery DESC) AS RowRank FROM Gallery ) AS GalleryWithRowNumber WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows) ORDER BY idgallery DESC cheers,imperialx
View 5 Replies
View Related
Jul 13, 2000
Can I automate a stored procedure I made myself?
If so could someone kindly tell me how as I have tried to no avail.
Thanks in advnace
View 1 Replies
View Related
Dec 29, 2007
Hi:
I am trying to create a stored procedure that filters some customers. The field in wich I am trying to apply the filter is the age field. The problem is that I need to be able to select the comparison operator =,<,>,=<,>=,<>.
I was trying to do it with the following code:
Select CustomerName From Customer Where
(CustomerAge & @Operator & @Age)
But sql Server shows an error telling me that @Operartor couldnt be converted to int.
I dont know if I am in the right track or way off, how is this done?
View 3 Replies
View Related
Oct 22, 2006
Hi, I am trying to implement filtering on a custome paged stored Procedure, here is my curent Stored Procedure which doesn't error on complie or run but returns no records. Anyone got any ideas on how to make this work???<Stored Procedure>set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: Peter Annandale-- Create date: 22/10/2006-- Description: Get Filtered Names-- =============================================ALTER PROCEDURE [dbo].[proc_NAMEFilterPaged] -- Add the parameters for the stored procedure here @startRowIndex int, @maximumRows int, @columnName varchar(20), @filterValue varchar(20)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;SELECT CODE, LAST_NAME, Name, TYPE, NUMBER FROM (SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER, ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum FROM dbo.NAME n LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED WHERE @columnName LIKE @filterValue ) as NameInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) -1END </Stored Procedure> Any assistance would be greatly appreciated.. Regards..Peter.
View 1 Replies
View Related
Jan 7, 2012
I need creating a store procedure which generates custom IDs for each asset. I am programming for Fixed Assets in VB6 with SQL Server 2005. For example, when a new Asset is added ,I need to auto generate the ID based on existing IDs. New ID should not exist in tblAssets table.
Table Name : tblAssets
Fields : AssetID > Int,Primary Key,this is internal ID (identity seed)
AssetExtID >nvarchar(50),this is external ID, need to generate/user entered.
Below is the example of data in tblAssets :
AssetID AssetExtID ProjectID ItemName Qty UOM UnitCost .....
1 PROSP-00001 PROSPERITY SPLIT-AC 2 NOS $200
2 PROSP-00002 PROSPERITY LAPTOP 1 NOS $500
3 UNIII-00001 UNION III LAPTOP 5 NOS $400
4 UNIII-00002 UNION III RECEIVER 2 NOS $312
The AssetExtID depends on the ProjectID which is in tblProjects.
I will take care of the first 5 characters to generate. But the number part I need to generate by checking existing data. The AssetExtID should not be duplicate. Its unique for each asset.
View 4 Replies
View Related
Mar 13, 2001
I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message
Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3
Can you have user defined data types in stored procedures.
Store Procedure creation text
CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO
Dave
View 3 Replies
View Related
May 8, 2008
PLEASE PLEASE PLEASE......
I did not get a single response for the last 6 hours... And during this time I was searching and trying to understand the problem but I am really stuck. If this is the wrong forum to ask this question, please redirect me. Really begging for replies...[:'(]
If I use the custom SQL statements in SqlDataSource, the application runs fine within the development environment (VS2005) but errors out if I publish the web site and access outside of the environment. In order to find-out the problem, I made the following test:
I created a select statement in one SqlDataSource to fill-in a GridView. I used the exact same statement to create a stored procedure and used that SP in second SqlDataSource and I fill a second GridView. When I debug or run the application, both grids are filled OK and everything works fine. However, when I publish the web site and try to do same only the stored procedure works fine and when I try to fill the grid using the built-in SQL, the page gives error. The error mesage is as follows when I use the address 'localhost':
Server Error in '/' Application.
The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.
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.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322.......da da da .......
If I access the page using the IP address the message chages to below but it is not the issue, I just give it if it helps to find the problem:
Server Error in '/' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
My SqlDataSource s are like this: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HomerConnectionString %>"
SelectCommand="TestRemoteAccess" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="Param1" PropertyName="Text" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:HomerConnectionString %>"
SelectCommand="SELECT FirstName, LastName, Business FROM Contacts WHERE (ContactID = @Param1)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox2" Name="Param1" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
Environment: SQL Server 2005, VS2005, Vista
View 1 Replies
View Related
Oct 20, 2007
Hi,
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped:
[Department, DEP_CLEGALCODE]
One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
thanks.
View 6 Replies
View Related
Mar 28, 1999
When replicating a table which has an identity column I get the error: "Procedure cp_insert_tblname expects parameter @C1, which was not supplied.". The stored procedure appears to be called without any parameters so my insert stored procedure does not work. I know I'm missing something basic here!! Do I have to add the field names when telling replication to use a custom stored procedure. If not, how do arguments get passed to my SP, as globals somehow?
Any info greatly appreciated!!
Thanks,
Jay
View 1 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Dec 1, 2006
I have a SqlDataSource that has a parameter that I am trying
to set.
The item is stored in a session field called “GameObject�
Game Object is a simple class that has a several variables.
I am trying to access the .name data member of the class.
<asp:SessionParameter Name="GameCode" SessionField="((GamingSystem)Session[‘GameObject’]).Name" />
This does not work. Is there a way to declarative bind to
the item that I am for.
View 3 Replies
View Related
Aug 14, 2007
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
View 6 Replies
View Related
Apr 2, 2007
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
View 5 Replies
View Related
Oct 8, 2007
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source?
Something like this:
CREATE PROCEDURE TestHardDisk
AS
BEGIN
DECLARE CURSOR HardDisk_Cursor
FOR Exec xp_FixedDrives
-- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source
OPEN CURSOR HardDisk_Cursor
FETCH NEXT FROM HardDisk_Cursor
INTO @Drive, @Space
WHILE @@FETCH_STATUS = 0
BEGIN
...
END
END
View 6 Replies
View Related
Jul 23, 2005
What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer
View 4 Replies
View Related
Jan 29, 2008
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
View 6 Replies
View Related
Jun 11, 2015
I am trying to find a reference for a client that lists the fields available to be substituted into a data driven subscription from the query, along with the expected data types.  For example, the field on whether or not to include a link to the report seems to be expecting a bit data type.I have searched and can't seem to find anything.  I guess I could walk through the interface and try different data types, but if  a list exists, that would be better.Â
View 4 Replies
View Related
Oct 14, 2005
Does anyone know of any cross-references between SQL Server data types and the new data types introduced with SQL Server Integration Services?
View 6 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Oct 15, 2013
I have written the following stored procedure to export a csv file. I am wanting to put a If statement in here so if view UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW returns nothing then the procedure does not run.
INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW
INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_LINE_VIEW
DECLARE @BcpHeader AS VARCHAR (2000)
[Code] ....
View 2 Replies
View Related
Sep 16, 2015
One of my report has different data types like decimal,percentage and integer values.
When I exported the report to excel , all the values are showing as "general" data type.
How to get excel data type same as ssrs report data type by default when exported to excel?
View 2 Replies
View Related
Jun 10, 2015
I Have a table with #Sample like below
=================================
#Sample
id int,
SSN varchar(20),
State varchar(2)
Â
Sample Data:
ID SSN STATE
1 999-000-000 AB
2 979-000-000Â BC
3 995-000-000Â CD
=================================
We used filter logic based on the SSN & State.
We are passing these values through variables like
Declare @State varchar(2)
Declare @SSN varchar(20)
While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'
Now the Result is displayed with the state data Like 'AB' only.
Output: 1 999-000-000 AB
instead it should give system generated error.
Here I have 2 Questions:
1. Why it is taking 1st 2 Charecters?
2. Why it does not have any system generated for length?
I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind?Â
View 5 Replies
View Related
Apr 16, 2014
I have an source file and i have to load it into the data base by changing datatype of the columns in ssis
View 1 Replies
View Related
Mar 13, 2008
I'm in the process of converting a rather huge VSAM database into a set of SQL tables.
I am using the same data names from the mainframe (like XDB-NAME to RDB-NAME).
I load the files using Import Export Data and it makes the tables with such column names as col001, col002, col003, etc... and always sets the data types to varchr(255).
And I have to cut and paste the data names from the manframe side to the server side (and the data types to.)
So, is there an easier way to do this? Or am I doomed to cut-n-paste my days away...
Thanks for any help.
View 2 Replies
View Related
Jun 1, 2006
Sorry if I haven't choose appropriate forum for this question.
I have MSSQL05 beta. I know how to list all stored procedures in selected database (everything is in localhost). I need to list parameter names and types for selected stored procedure(s).
How can I do that or anything that can return parameter names and types?
It's windows application.
View 3 Replies
View Related
Mar 31, 2007
I have created the following stored procedure and tried to retrieve it's output value in C#, however I am getting exceptions. Can anyone tell me what I am doing wrong? Thanks! 1 ALTER PROCEDURE [dbo].[GetCustomerById]
2
3 @CustId NCHAR(5),
4 @CustomerName NVARCHAR(50) OUTPUT
5
6 AS
7 BEGIN
8
9 SELECT @CustomerName = ContactName
10 FROM Customers
11 WHERE CustomerId = @CustId
12
13 END
14
15 RETURN
16
17
18
19
20
21
22 SqlConnection conn = GetConnection(); //retrieves a new SqlConnection
23 SqlCommand cmd = new SqlCommand();
24 cmd.Connection = conn;
25 cmd.CommandType = CommandType.StoredProcedure;
26 cmd.CommandText = "GetCustomerById";
27
28 SqlParameter paramCustId = new SqlParameter();
29 paramCustId.ParameterName = "@CustId";
30 paramCustId.SqlDbType = SqlDbType.NChar;
31 paramCustId.Direction = ParameterDirection.Input;
32 paramCustId.Value = "ALFKI";
33
34 SqlParameter paramCustomerName = new SqlParameter();
35 paramCustomerName.ParameterName = "@CustomerName";
36 paramCustomerName.SqlDbType = SqlDbType.NVarChar;
37 paramCustomerName.Direction = ParameterDirection.Output;
38
39 cmd.Parameters.Add(paramReturn);
40 cmd.Parameters.Add(paramCustId);
41 cmd.Parameters.Add(paramCustomerName);
42
43 conn.Open();
44 SqlDataReader reader = cmd.ExecuteReader();
45
46 string custName = cmd.Parameters["@CustomerName"].Value.ToString();
View 6 Replies
View Related
Jan 16, 2008
Hi,I've got some XML which exists as a text variable in a temp table in SQL Server 2000.I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.If I try doing this:declare @idoc intexec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)I get an error, with or without the brackets round the select statement.The temp table is created using an SP, but I can't call that directly either. This:declare @idoc intexec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchIDAlso throws an error.I can't put it into a
local variable because they can't be of type text. I can't pass it into
the SP somewhere as it's being generated on the fly.How can I get my xml into sp_xml_preparedocument?Cheers,Matt
View 3 Replies
View Related
Jan 31, 2008
Can someone tell me why my stored procedure is repeating the Name in the same column?
Here's my stored procedure and output:
select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name],
Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate,
Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate
from LibraryRequest
join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null
Output:
ID Title Name Request Date Ship Date
29 Heads, You Win Brenda Smith 1/18/2008 1/18/200835 Still More Games Brenda Smith 1/22/2008 1/22/200851 The Key to.. Brenda Smith Brenda Smith 1/29/2008 1/29/200852 PASSION... Brenda Smith Brenda Smith 1/29/2008 1/29/200853 LEADERSHIP Brenda Smith Brenda Smith 1/29/2008 1/29/2008
Going crazy ugh...
View 3 Replies
View Related
Feb 14, 2008
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure.
for example:
alter procedure [dbo].[AddDetails]
(
@IndustryName nvarchar(50),
@CompanyName nvarchar(50),
@PlantName nvarchar(50),
@Address nvarchar(100),
@Createdby int,
@CreatedOn datetime
)
as
begin
insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn)
insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn)
insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp)
end
Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this.
Please help me, i need the solution soon.
View 11 Replies
View Related
Feb 27, 2008
Hi iam working with two dropdownlists,one gets the data dynamically when the pageload.later after selecting particular item in the dropdownlist1 i must get data to the dropdownlist2 depending on 1.
For example:
Dropdownlist1 is for industry and 2 is for company.
when i select particual industry in ddl1 i must get companies based on this industry in ddl2.Both the Industry name and company name are maintained in two different tables industry and company with common field ID.please help me with a stored procedure to sort out this problem...
View 6 Replies
View Related
Jul 27, 2004
Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code.
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 )--,
--@fldFirstName char( 30 ) OUTPUT,
--@fldLastName char( 30 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
GO
############### login page ################
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
Dim strFirstName, strLastName As String
sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
myConn = New SqlConnection(sqlConn)
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
myCmd.Parameters.Add(Trim("@fldUsername"), Trim(strUsername))
myCmd.Parameters.Add(Trim("@fldPassword"), Trim(strPassword))
myCmd.Parameters.Add("@fldFirstName", strFirstName)
myCmd.Parameters.Add("@fldLastName", strLastName)
myCmd.Parameters.Add("@fldPassword", strPassword)
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
myConn.Close()
'If strPassword = 55555555 Then
' Session("intDefaultPass") = 1
'End If
Session("strFullName") = strFirstName & " " & strLastName
Session("strPassword") = strPassword
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
Return intResult
End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after?
Michael
View 4 Replies
View Related
