Passing Compound Primary Key Values To A Stored Procedure Using ADO.NET
Apr 10, 2008
Hi all,
I've had this problem for a while now and I'm looking for a better solution.
I'm pulling through a dataset from a SQL Server 2005 database and populating it into a DataGridView. The end user updates information on the grid and I then want to updates the results in the SQL Server table. My table has a compound primary key made up of two fields.
I'm currently looping through the data grid and for each value identified to update I'm executing a stored procedure that takes the two fields as parameters. However I know that this method isn't very efficient, especially if the user wants to update a few hundred records as it will execute the stored procedure a few hundred times.
Is there a way I can pass the two parameters in an ArrayList or something like that? I need SQL Server to be able to take the parameter. Is XML the way to go or is there any additional support in the .NET framework for such problems?
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails @Id string as Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it. How can i solve this problem?
Hi All, I'm a newbie learning windows applications in visual basic express edition, am using sqlexpress 2005 So i have a log in form with username and password text fields.the form passes these values to stored procedure 'CheckUser' Checkuser then returns a value for groupid. If its 1 they are normal user, if its 2 its admin user. Then opens another form called Organisations, and closes the log in form. However when i run the project, and enter a username and password and press ok ti tells me that there is incorrect syntax beside a line. I have no idea, and I'm sure that there is probably other things wrong in there. here is the code for the login button click event: Public Class Login Dim connString As String = "server = .SQL2005;" & "integrated security = true;" & "database = EVOC" 'Dim connString As String = _ '"server = .sqlexpress;" _ '& "database = EVOC;" _ '& "integrated security = true;"
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login_Log_In.Click ' Create connection Dim conn As SqlConnection = New SqlConnection(connString) ' Create command
Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = conn cmd.CommandText = "CheckUser"
Dim inparam1 As SqlParameter = cmd.Parameters.Add("@Username", SqlDbType.NVarChar) inparam1.Value = Login_Username.ToString Dim inparam2 As SqlParameter = cmd.Parameters.Add("@Password", SqlDbType.NVarChar) inparam2.Value = Login_Password.ToString inparam1.Direction = ParameterDirection.Input inparam2.Direction = ParameterDirection.Input 'Return value Dim return_value As SqlParameter = cmd.Parameters.Add("@return_value", SqlDbType.Int) return_value.Direction = ParameterDirection.ReturnValue cmd.Connection.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader Dim groupID As Integer
groupID = return_value.Value
If groupID < 0 Then MessageBox.Show("Access is Denied") Else Dim Username = Me.Login_Username Dim org As New Organisations org.Show() End If
conn.Close()
End Sub The stored procedure code is ok as i know it works as it should, but if it helps the code is: ALTER PROCEDURE [dbo].[CheckUser] -- Add the parameters for the stored procedure here @UserName nvarchar(50) = N'', @Password nvarchar(50) = N'' ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF (SELECT COUNT(*) FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)=1BEGINPRINT 'User ' + @Username + ' exists' SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@PasswordRETURN (SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)ENDELSE BEGINPRINT 'User ' + @Username + ' does not exist' RETURN (-1)ENDEND All help greatly appreciated to get me past this first hurdle in my first application!! CheersTom
Hello. im tryinng to build an application that has a girdview with values from 2 different tables. The select query i have used in a stored procedure works great but when i try to write something to update into 2 different tables I cant figure out how to do it. This is the first time im writing stored procedures but from what i can tell i need to do 2 seperate updates to insert mu values. im using the AdventureWorksLT database provided by microsoft to experiment with and my gridview consists of 2 tables populated by the following stored procedure:ALTER PROCEDURE dbo.GetSomething AS SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName, CustomerAddress.AddressID, CustomerAddress.AddressType FROM SalesLT.Customer, SalesLT.CustomerAddress WHERE Customer.CustomerID = CustomerAddress.CustomerID
RETURN What id like to do is to make the GridView editable and then send the all thee values back so the changes are saved in both tables. Could anyone help me write a stored procedure that gets the values from the fields in the gridview that is beeing changed and send them back to the tables?
I'm want to pass empty parm value to a stored procedure. I'm new at this stuff and not sure how to do this. To make it work I can put values in parms and it works great. I don't want to populate some of the fields in the table with values. The fields are cnty,dist,beat,convertedkey. Anyone know how I can get it done? Code below. Thanks for your help!
int shift = Convert.ToInt16(dr.Cells[1].Value.ToString());
int prior = Convert.ToInt16(dr.Cells[2].Value.ToString());
hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e) { try {foreach (ListItem item in authorsListBox4.Items) {if (item.Selected) { AddConfSqlDataSource.Insert(); } }saveStatusLabel.Text = "Save Successfull: The above publication has been saved"; }catch (Exception ex) {saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message; } }
I thought this would be quite simple but can't find the correct syntax:ALTER Procedure usp_Product_Search_Artist_ProductTitle
(@ProductTitle varchar(255))
AS
SELECT ProductTitle
FROM tbl_Product
WHERE CONTAINS(dbo.tbl_Product.ProductTitle, @ProductTitle) My problem is that if I pass "LCD Flat Screen" as teh @ProductTitle parameter the query falls over as it contains spaces. Guess i'm looking for something like: WHERE CONTAINS(dbo.tbl_Product.ProductTitle, '"' + @ProductTitle + "'")Thanks in advance.R
I need to know how to create a compound primary key in Transact-SQL It's really simple, it's a table with 3 column, two of those make the primary key Right now the CREATE TABLE look like this:/* SectionsContent Table */ CREATE TABLE SectionsContent ( SectionID Int NOT NULL CONSTRAINT FK_SectionsContent_SectionID FOREIGN KEY REFERENCES PsychoCMS.dbo.Sections(ID), DocumentID Int NOT NULL CONSTRAINT FK_SectionsContent_DocumentID FOREIGN KEY REFERENCES PsychoCMS.dbo.Documents(ID), Position int NOT NULL )
Hello,if you create this table:create table hello (int a, int bconstraint pk_hello primary key clustered ( a, b ))and then insert the following recordsa,b1,11,21,32,12,22,33,13,23,3and then doselect a,b from hellothe output seems to be:a,b1,12,13,11,22,23,21,32,33,3which is wrong and (i think) is reflecting the actual index orderand physical order on diskit should be:a,b1,11,21,32,12,22,33,13,23,3i have tested this on a table with 500,000 recordsand sure enough if you declare the clustered primary key fields inreverse order:constraint pk_hello primary key clustered ( b, a )two things happen:- the select with no order by returns the records in the expected order- queries relying on that order run MUCH FASTERhas anyone else seen / noticed this?
BEGINNER QUESTIONI have a table which has a compound primary key consisting of two columns.One of these columns is a foreign key which is generated in another table byan identity.I want to be able to generate the other primary key column valueautomatically when an insert occurs but assume that I cannot use an identitybecause it would have to be unique for this table.There will be potentially more than one user accessing this table so I wantto avoid generating the key on the client side.How can I do this? Will it require some hardcore T-SQL?I hope this is clear (I suspect it isn't) I'd be happy to supply more info.I would be extremely grateful for any help!Mark.
Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!
I have successfully Create a Site, Inserting Updating and Deleting information in my DB all with Stored Procedures, But I need the ability to pass their username into my Stored Procedures. How and where do I code this in my ASPX file?
My SP would be something like this
Create procedure test
@UserName varchar(50)
As
Select *
From table
Where username = @username
All of the data is tied to the user in one way or another but I do not know what code to put in my page?
I have two variables in my code behind .cs, string Zip;int MenuCode; I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object? error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string" <asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors. Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors. The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails. SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity() END and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SELECT @NewpublicationID Thanks Gemma
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
Hi i have some radio buttons, what i want to achieve is that if one of the radio buttons is clicked then the value is sent to the stored procedure, as as now the radio buttonlist is like this; <asp:RadioButtonList ID="SortButtons" runat="Server" RepeatDirection="Horizontal" RepeatLayout="Flow" Width="69px" OnSelectedIndexChanged="SortButtons_SelectedIndexChanged"><asp:ListItem Text="Price" Value="productPrice"></asp:ListItem><asp:ListItem Text="Quantity " Value="Qty"></asp:ListItem></asp:RadioButtonList> My question is how will my stored procedure take in the radio button that has been clicked and order it by that value, e.g if price is clicked how will the stored procedure take taht in, this is how my normal unordered stored procedure currently looks; CREATE PROCEDURE stream_CategoryResults@subCategoryID INT ASSELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
Hey huys, I got this stored procedure. First of all: could this work?
--start :) CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0' AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values (@EmployeeID, @loginType, @domainUserID) GO --end :)
then I got this VB.Net code in my ASP.net page....
---begin :) Private Sub matchUser() Dim insertMatchedUser As SqlClient.SqlCommand Dim daMatchedUser As SqlClient.SqlDataAdapter SqlConnection1.Open() 'conn openned daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1) daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text) daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output SqlConnection1.Close() 'conn closed
End Sub ---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
Hi, that's the problem: I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields. When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler: exec sel_despesa_procura @codigo=NULL,@fornecedor=NULL,@descricao=NULL,@vencto_ini=''2005-10-10 00:00:00:000'',@vencto_fim=''2005-10-20 00:00:00:000'',@pagto_ini=NULL,@pagto_fim=NULL,@valor=NULL,@valor_pago=NULL,@centro_custo=NULL,@pago=N'0,1' The fields are defined as follows: <SelectParameters>...<asp:ControlParameter ControlID="txtFiltroVencIni" Name="vencto_ini" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="txtFiltroVencFim" Name="vencto_fim" PropertyName="Text" Type="DateTime" />...</SelectParameters> The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error: Msg 102, Level 15, State 1, Line 1Incorrect syntax near '2005'. I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.
I have the following stored procedure ALTER PROCEDURE dbo.TextEntered @searchString varchar(30) /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ AS Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME
RETURN doesn't work!!!! I would like to produce the following where s is @searchString: Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME Any ideas how I might acomplish this I have tried almost everything !
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
make it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.
Hello, I'd greatly appreciated if someone can help me out on this problem: I'd like to pass a string (which is a multiple value in this case - "Smith", "Lee", "Jones", "Hanson") as an input parameter to a stored procedure. I'd like to use this string as part of the select statement:
exec sp_GetLN "smith, lee, jones, hanson"
In the stored procedure: @strLN varchar <- "smith, lee, jones, hanson"
/*What do I need to do here to SET/REPLACE the original string so that the syntax can be accepted by the select statement??*/
SELECT * from tblCustomers WHERE LASTNAME IN (@strLN)
It looks simple but I've been trying to get the syntax to work. What can I do to change the quote from ' to "?? Can I use char(34) like VB?? Your help is greatly appreciated~
Is it possible to have a stored procedure that passes recordsets which are created and passed from different tables and joins the information in those recordsets to form one group of information?
Hi all, I want to use a stored procedure to access data in different databases. I was wondering if it is possible to pass the database name to the stored procedure as a parameter and use it in the sql statement. Thank you for all the help you can give me. Regards, Eve
I am using VB6 and SQL Server 6.5. I want to pass a boolean to a SQL Server stored procedure. The code below is the main part that tries to pass 'True' to the stored procedure, but an error occurs when the line rst.Open.... is reached ? The error is as follows: The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.
I have tried putting 'True' directly in the rst.Open... line instead of assigning it to a boolean variable but the same error occurs.
The stored procedure works fine when run in SQL Server.
Hi, I'm fairly new to TSQL so I'll try to explain as best I can. I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) :
CREATE PROCEDURE ExplodeDilutions @MyLSN AS VarChar(8)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.* FROM TSample WHERE (TSample.ISmpShortCode = @MyLsn)
The value I'm passing (@MyLSN) is 24/12359 (one LS Number)
The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like:
CREATE PROCEDURE ExplodeDilutions @MyLSN AS VarChar(50)
AS
declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.* FROM TSample WHERE (TSample.ISmpShortCode IN (@MyLsn))
The value I'm passing (@MyLSN) is '24/12359','24/12360'
This is where the problem begins because the stored procedure now doesn't return a recordset.
If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned. Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail.
So if someone could point me in the right direction then that would be much appreiciated.
I've created a stored procedure and I need to pass it a list of order IDs.The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.
It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?
EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123' SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails @Id string as Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it. How can i solve this problem?
