Deleting A Row Using A Stored Procedure From A GridView
Sep 14, 2007
I am trying to do something that I would think is simple. I have a stored procedure used for deleting a record, and I want to call it from the "Delete" command of a Delete button on a GridView. This incredible simple SP accepts one value, the unique record ID to delete the record like this:CREATE PROCEDURE usp_DeleteBox
/* *******************************************
Delete a record using the Passed ID.
********************************************** */
(
@pID as int = Null
)
AS
DELETE FROM [Boxes]
WHERE ID = @pID
When I configured the data source for the GridView, I selected the "Delete" tab and selected my Stored Procedure from the list. As mentioned on another post I saw here, I set the "DataKeyNames" property of the GridView to my id field (called "ID", naturally).
When I click the Delete button on a row, I get this error message: "Procedure or function usp_DeleteBox has too many arguments specified." If I leave the "DataKeyNames" property empty, it does nothing when I click delete.
Can someone tell me the correct way to configure this? I am sure I am missing something obvious, and I would appreciate any suggestions. Thank you!
View 3 Replies
ADVERTISEMENT
Jan 19, 2007
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
View 9 Replies
View Related
Oct 23, 2006
Info: (vs 2005, sql server 2005, asp 2.0, C# project)I need to pass a variable from my web form to a stored procedure which should return a dataset to a gridview.My stored proc works fine but it’s not returning properly (or at all). Here is my stored proc:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[HrsUsed]
-- Add the parameters for the stored procedure here
@idUser INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DATENAME(mm, leaveDate) + ' ' + DATENAME(yyyy, leaveDate) AS 'Leave Date', totalV, totalS
FROM tblLeave
WHERE idUser = @idUser
ORDER BY leaveDate DESC
RETURN
END
Here is my code: try
{
SqlConnection cxnHrsUsed = new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString);
SqlCommand cmdHrsUsed = new SqlCommand("HrsUsed", cxnHrsUsed);
cmdHrsUsed.CommandType = CommandType.StoredProcedure;
cmdHrsUsed.Parameters.Add("@idUser", SqlDbType.Int).Direction = ParameterDirection.Input;
cmdHoursUsed.Parameters["@idUser"].Value = Session["sessionUserID"];
cmdHrsUsed.Connection.Open();
gvHrsUsed.DataSource = cmdHrsUsed.ExecuteReader();
gvHrsUsed.DataBind();
cmdHrsUsed.Connection.Close();
}
catch (Exception ex)
{
lblStatus.Text = ex.Message;
} I can see the caption of the gridview, but no data below it. Any ideas?
View 3 Replies
View Related
Apr 17, 2006
Help!I am trying to fill my datagrid using the SQLDataSource, using a stored procedure.The stored procedure expects a parameter which I can collect via the querystring, or a string. How can I pass the parameter through the SQLDatasSource?My SQLDataSource is SQLData1. I already have:SQLData1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureSQLData1.SelectCommand = "dbo.get_players"Thanks in advance,Karls
View 2 Replies
View Related
Feb 19, 2007
I have a stored procedure in my SQL 2005 Server database named Foo that accepts two parameters, @paramA and @paramB.In my ASP.NET page, I have these:<asp:GridView id="gv" runat="server" AutoGenerateColumns="true" DataSourceID="DS" AllowSorting="true" DataKeyNames="ID"/><asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:CS1 %>" SelectCommand="Foo" SelectCommandType="StoredProcedure" OnSelecting="DS_Selecting"> <asp:Parameter Name="paramA" Type="String" /> <asp:Parameter Name="paramB" Type="String" /></asp:SqlDataSource>In my setup, paramA and paramB are set in DS_Selecting(), where I can access the Command.Parameters[] of DS.Now, here's the problem. As you can see, the GridView allows for sorting. When you click on a header title to sort, however, the GridView becomes empty. My question is, how can I get the GV sorted and in the correct direction (i.e. asc/desc)? My first step in my attempt was to add another parameter to the SqlDataSource and sotred procedure Foo (e.g. @SortByColumn), then changed Foo appropriately: ALTER PROCEDURE Foo @paramA nvarchar(64), @paramB nvarchar(64), @SortColumn nvarchar(16) = 'SearchCount' AS SELECT * FROM Searches ORDER BY CASE WHEN @SortColumn='SearchCount' THEN SearchCount WHEN @SortColumn='PartnerName' THEN PartnerName ELSE ID ENDThat works find and dandy. But wait--I want to get the correct ORDER BY direction too! So I add another parameter to the SqlDataSource and Foo (@SortDirection), then alter Foo: ... SELECT * From Searchces ORDER BY CASE /* Keep in mind that CASE short-circuits */ WHEN @SortColumn='SearchCount' AND @SortDirection='desc' SearchCount DESC WHEN @SortColumn='SearchCount' SearchCount WHEN @SortColumn='PartnerName' AND @SortDirection='desc' PartnerName DESC WHEN @SortColumn='PartnerName' PartnerName WHEN @SortColumn='ID' AND @SortDirection='desc' ID DESC ELSE ID END ...But including DESC or ASC after the column name to sort by causes SQL to error. What the heck can I do, besides convert all my stored procedures into in-line statements inside the ASP page, where I could then dynamically construct the appropriate SQL statement? I'm really at a loss on this one! Any help would be much appreciated!Am I missing a much simpler solution? I am making this too complicated?
View 2 Replies
View Related
May 7, 2008
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?
View 4 Replies
View Related
Nov 10, 2007
Hi,I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @TransactionID AND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @Itemid) " It gives the error that Quantityavailable is not a SET type?Thanks if you can suggest a remedy! Jon
View 2 Replies
View Related
Oct 26, 2007
I am attempting to setup a Master-Details with GridView/DetailsView but I can't seem to find any information on using a stored procedure that requires parameters with the SqlDataSource control. SelectCommandType specifies that you are using a stored proc. SelectCommand specifies the name of the proc, but I haven't found any information on how to pass a parameter to the stored procedure.Is it even possible or do I have to forget about using the DetailsView control altogether?
View 4 Replies
View Related
Feb 25, 2008
Like the subject says I have tested the SP in Gridview. Everything looks fine. But when run nothing shows up on the page.
I have tried using QueryStrings to pass the data and Controls.
Here is the Gridview Code:<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
CellPadding="4" DataSourceID="SqlDataSource1">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<RowStyle BackColor="White" ForeColor="#003399" />
<Columns><asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" /><asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" /><asp:BoundField DataField="EthnicID" HeaderText="EthnicID"
SortExpression="EthnicID" /><asp:BoundField DataField="Gender" HeaderText="Gender"
SortExpression="Gender" /><asp:BoundField DataField="Height" HeaderText="Height"
SortExpression="Height" /><asp:BoundField DataField="Weight" HeaderText="Weight"
SortExpression="Weight" />
<asp:BoundField DataField="Hair" HeaderText="Hair" SortExpression="Hair" />
<asp:BoundField DataField="Eyes" HeaderText="Eyes" SortExpression="Eyes" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
</Columns>
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:GridView>
Here is the Datasource:<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SQL2005_311004_modelsystemConnectionString %>"
SelectCommand="spSearch" SelectCommandType="StoredProcedure">
<SelectParameters><asp:QueryStringParameter DefaultValue="" Name="FirstName"
QueryStringField="fn" Type="String" />
<asp:QueryStringParameter Name="LastName" QueryStringField="ln" Type="String" />
<asp:Parameter Name="EthnicID" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="Height" Type="String" />
<asp:Parameter Name="weight" Type="String" />
<asp:Parameter Name="Hair" Type="String" />
<asp:Parameter Name="Eyes" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="State" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
I hope you can help me with this one!!!
View 2 Replies
View Related
Mar 7, 2008
Here is the Stored procedure
ALTER procedure [dbo].[ActAuditInfo](@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) output
)asbegindeclare @AuidtID as varchar(30)Select @IndustryName=Industry_Name from Industry whereIndustry.Ind_Id_PK =(Select Audit_Industry from Audits whereAd_ID_PK=@AuidtID)Select @CompanyName=Company_Name from Company whereCompany.Cmp_ID_PK =(Select Audit_Company from Audits whereAd_ID_PK=@AuidtID)Select @PlantName=Plant_Name from Plant where Plant.Pl_ID_PK=(Select Audit_Plant from Audits where Ad_ID_PK=@AuidtID)Select @GroupName=Groups_Name from Groups whereGroups.G_ID_PK =(Select Audit_Group from Audits whereAd_ID_PK=@AuidtID)Select @UserName=Login_Uname from RegistrationDetails whereRegistrationDetails.UID_PK =(Select Audit_Created_By fromAudits where Ad_ID_PK=@AuidtID)SELECT Ad_ID_PK, Audit_Name, @IndustryName, @CompanyName, @PlantName,@GroupName, Audit_Started_On, Audit_Scheduledto, @UserName FROMAudits where Audit_Status='Active'end
U can see here different parameters,my requirement is that iam havingID's of Industry,company,plant,group,username stored in a table calledPcra_Audits and i must display their related names in the front end.so this is the query iam using for that.
Data in the database:Commercial83312 2 2 2 1 1 InactiveHere u can see 2,2,2,1,1 these are the IDs ofindustry,company,plant,group and username and Commercial83312 is tehaudit ID.now i want to display this data in teh front end as i cannot displaythe IDs i am retrieving the names of the particular IDs from therelated tables.Like iam getting name of the IndustryID from Industry Table,in thesame way others too.when iam running this procedure iam getting the gridview blank.iam passing the output parameters:@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) outputinto the function in the frontend and iam calling that into the pageload method.please help me with this.
View 1 Replies
View Related
Feb 22, 2008
Hi
i have a search page having four text boxes like name,accountnumber,ssn..etc we have to search the database by these values. but even if we give value in one text box keeping the others null the stored procedure have to serach and get the values. and we display it using gridview control.
here is the stored procedure i wrote.but its not working.its not giving any erros...but its not showing any values.
ALTER Procedure [dbo].[usp_CheckUser]
@name nvarchar(50),
@ssn nvarchar(50),
@accountnumber nvarchar(50)
AS
BEGIN
if(@name!=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where name=@name AND ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn!=null AND @accountnumber!=null)
select * from Userinfo where ssn=@ssn AND accountnumber=@accountnumber
else if(@name=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber
else if(@name!=null AND @ssn=null AND @accountnumber!=null)
select * from Userinfo where accountnumber=@accountnumber AND name=@name
else if(@name!=null AND @ssn=null AND @accountnumber=null)
select * from Userinfo where name=@name
else if(@name!=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where name=@name AND ssn=@ssn
else if(@name=null AND @ssn!=null AND @accountnumber=null)
select * from Userinfo where ssn=@ssn
end
table name is userinfo
please help me with this. its very urgent.
thanx for your help in advance.
ramya
View 7 Replies
View Related
Aug 1, 2005
Hi, does anyone know how to delete data from the SQL database by
calling the stored procedure in the Visual Basic.NET? Because I did the
Delete hyperlink bounded inside a datagrid. I have already displayed
the appointment date, time in the datagrid so I do not have to input
any values inside it. These are my stored procedures code for deleting:
ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS
BEGIN
IF NOT EXISTS
(SELECT MemNRIC, AppDate, AppTime
FROM DasAppointment
WHERE (MemNRIC = @MemNRIC) AND (AppDate = @AppDate) AND (AppTime = @AppTime))
RETURN -400
ELSE IF EXISTS
(SELECT MemNRIC
FROM DasAppointment
WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 10))
RETURN -401
ELSE
DELETE FROM DasAppointment
WHERE MemNRIC = @MemNRIC
END
IF @@ERROR <> 0
RETURN @@ERROR
RETURN
DECLARE @status int
EXEC @status = spCancelReservation '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status
Can someone pls help? Thanks!
View 11 Replies
View Related
Oct 9, 2007
I can not get this stored procedure to delete my records...
I have a
contact table
RecordID
FirstName
LastName
etc
and a Address table
RecordID
Street
Zip
Town
Country
And a Relation table
RecordID
ContactID
AddressID
CreateDate
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[bc_Contact_Delete]
@ContactID int
AS
--SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete all Adresses
DELETE FROM [Address]
WHERE
RecordId in (SELECT ca.AdressId from [ContactAddress] ca
where
ca.ContactID = @ContactID)
-- Delete all Relations
DELETE FROM [ContactAdress]
WHERE ContactID = @ContactID
--- Delete Kontakt
DELETE FROM [Contact] WHERE (([RecordId] = @ContactID))
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
RETURN
My Errormessage is
The DELETE statement conflicted with the REFERENCE constraint "FK_bc_ContactAdress_bc_Address". The conflict occurred in database "bContacts", table "dbo.ContactAddress", column 'AdressID'.
Can someone please post me an advice?
View 5 Replies
View Related
Mar 14, 2008
Hi
I have a search page having four fields. Giving any one of the field as input should retrieve search results in Gridview.
In GridView1 i have child Gridview for displaying details related to Gridview 1.
now i have to write storedprocedure for getting values in two grids.
there is one matching column in two tables i.e CNo.from first select statement we have to capture CNo and basing on that retrieve second table values.
I have a stored procedure for that but my problem is
i stored CNo in temp table i.e @MyTable .after doing select statements from two tables
i want to delete @MyTable. But iam not able to. so pls help me
My stored procedure code is here:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[search1]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN
DECLARE @MyTable table (CNo varchar(255))
INSERT @MyTable
Select CNo From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))
--Now do your two selects
SELECT *
FROM customer c
INNER JOIN @MyTable T ON c.CNo = T.CNo
Select *
From refunds r
INNER JOIN @MyTable t ON r.CNo = t.CNo
END
The output of storedprocedure is like this:
Iam getting all the columns of two tables but the CNo column is repeating twice in both the tables.
so please some one help me.
The CNo colum shouldnot repeat.
Thankyou
View 4 Replies
View Related
Jul 24, 2007
Hi,
I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID].
I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03.
I have come up with something like that but it does not seems to be correct.
CREATE PROCEDURE [sp_delete_test01_1] (@id [int])
AS
DELETE [test01] DELETE [test02] DELETE [test03]
WHERE ( [id] = @id)GO
Your advise please. Many Thanks.
View 4 Replies
View Related
Jun 12, 2008
Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
View 2 Replies
View Related
Dec 19, 2006
Dear Forum,
I have a gridview control which is associated to a storedprocedure with a parameter(Customer Number) to be supplied. In the Define Custom Statement or stored procedure section I selected stored procedure and selected the stored procedure. The Define Parameter window I defaulted the Parameter Source as 'none' and default value as '%'. In the next screen, I do a test query which retuns the following error
There was an error executing the query. Please check the syntax of the command and if present, the type and values of the parameters and ensure that they are correct.
[Error 42000] [Microsoft][ODBC SQL Server Drive][SQL Server] Procedure 'SP_TransactionDetails' expects parameter '@cnum' which was not supplied.
I am using SQL server studio 2005 version2.0.
But the same procedure, if I use as SQL Statement, it works.
Can somebody help me.
Thanks,
Hidayath
View 3 Replies
View Related
Mar 22, 2008
Dear people,
When i test my page for uploading image too my sql database everthing goes ok (i think) en when i look into my database table i see 3 colums filled
1 column with: Image_title text
1 column with:Image_stream <binary data>
1 column with image_type image/pjpeg
How can i show this image in a gridview column..... i have search for this problem but non of them i find i can use because its a too heavy script, or something i dont want.
Is there a helping hand
Below is the script for uploading the image.....and more
1
2 Imports System.Data
3 Imports System.Data.SqlClient
4 Imports System.IO
5
6 Partial Class Images_toevoegen
7 Inherits System.Web.UI.Page
8
9
10 Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
11
12 Dim imageSize As Int64
13 Dim imageType As String
14 Dim imageStream As Stream
15
16 ' kijkt wat de groote van de image is
17 imageSize = fileImgUpload.PostedFile.ContentLength
18
19 ' kijk welke type image het is
20 imageType = fileImgUpload.PostedFile.ContentType
21
22 ' Reads the Image stream
23 imageStream = fileImgUpload.PostedFile.InputStream
24
25 Dim imageContent(imageSize) As Byte
26 Dim intStatus As Integer
27 intStatus = imageStream.Read(imageContent, 0, imageSize)
28
29 ' connectie maken met de database
30 Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("Personal").ConnectionString)
31 Dim myCommand As New SqlCommand("insert into tblMateriaal(Image_title,Image_stream,Image_type,ArtikelGroep,ArtikelMaat,Aantal,Vestiging,ArtikelNaam,ContactPersoon,DatumOnline) values(@Image_title,@Image_stream,@Image_type,@ArtikelGroep,@ArtikelMaat,@Aantal,@Vestiging,@ArtikelNaam,@ContactPersoon,@DatumOnline)", myConnection)
32
33 ' Mark the Command as a Text
34 myCommand.CommandType = CommandType.Text
35
36 ' geef alle parameters mee aan het command
37 Dim Image_title As New SqlParameter("@Image_title", SqlDbType.VarChar)
38 Image_title.Value = txtImgTitle.Text
39 myCommand.Parameters.Add(Image_title)
40
41 Dim Image_stream As New SqlParameter("@Image_stream", SqlDbType.Image)
42 Image_stream.Value = imageContent
43 myCommand.Parameters.Add(Image_stream)
44
45 Dim Image_type As New SqlParameter("@Image_type", SqlDbType.VarChar)
46 Image_type.Value = imageType
47 myCommand.Parameters.Add(Image_type)
48
49 Dim ArtikelGroep As New SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar)
50 ArtikelGroep.Value = ddl1.SelectedValue
51 myCommand.Parameters.Add(ArtikelGroep)
52
53 Dim ArtikelMaat As New SqlParameter("@ArtikelMaat", System.Data.SqlDbType.NVarChar)
54 ArtikelMaat.Value = ddl2.SelectedValue
55 myCommand.Parameters.Add(ArtikelMaat)
56
57
58 Dim Aantal As New SqlParameter("@Aantal", System.Data.SqlDbType.NVarChar)
59 Aantal.Value = ddl3.SelectedValue
60 myCommand.Parameters.Add(Aantal)
61
62 Dim Vestiging As New SqlParameter("@Vestiging", System.Data.SqlDbType.NVarChar)
63 Vestiging.Value = ddl4.SelectedValue
64 myCommand.Parameters.Add(Vestiging)
65
66 Dim ArtikelNaam As New SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar)
67 ArtikelNaam.Value = tb6.Text
68 myCommand.Parameters.Add(ArtikelNaam)
69
70 Dim ContactPersoon As New SqlParameter("@ContactPersoon", System.Data.SqlDbType.NVarChar)
71 ContactPersoon.Value = tb1.Text
72 myCommand.Parameters.Add(ContactPersoon)
73
74 Dim DatumOnline As New SqlParameter("@DatumOnline", System.Data.SqlDbType.NVarChar)
75 DatumOnline.Value = tb2.Text
76 myCommand.Parameters.Add(DatumOnline)
77
78 Try
79 myConnection.Open()
80 myCommand.ExecuteNonQuery()
81 myConnection.Close()
82
83 Response.Redirect("toevoegen.aspx")
84 Catch SQLexc As SqlException
85 Response.Write("Insert Failure. Error Details : " & SQLexc.ToString())
86 End Try
87
88
89 End Sub
90 End class
View 2 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
Apr 19, 2004
I am trying to write a stored procedure to delete something from a table but I keep getting sytax error.
This is the code I am using:
DELETE [tablename].[tablerow], [tablename].[tablerow]
FROM [tablename]
WHERE (((tablename.tablerow)="void"));
This works as an Access query but not as a stored procedure.
View 1 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
May 2, 2007
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece.
thanks.
View 2 Replies
View Related
Jan 27, 2006
Is there a way to delete records from table passing parameter as tablename? I won't to delete all records from a table dependent on table selected. i'm trying to do this with stored procedure...Table to delete depends on the checkbox selected.
Current code(works)
Public Function DelAll()
MZKDB = MZKHRFin
If sloption = "L" Then
sqlConn.ConnectionString = "Server=" & MZKSrv & ";Initial Catalog=" & MZKDB & ";Integrated Security=SSPI;"
ElseIf sloption = "S" Then
sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";"
End If
sqlConn.Open()
sqlTrans = sqlConn.BeginTransaction()
sqlCmd.Connection = sqlConn
sqlCmd.Transaction = sqlTrans
Try
sqlCmd.CommandText = sqlStr
sqlCmd.ExecuteNonQuery()
sqlTrans.Commit()
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Prior records have been deleted from the database." & vbCrLf
SetCursor()
Catch e As Exception
Try
sqlTrans.Rollback()
Catch ex As SqlException
If Not sqlTrans.Connection Is Nothing Then
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction." & vbCrLf
SetCursor()
End If
End Try
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Records were NOT deleted from the database." & vbCrLf
SetCursor()
Finally
sqlConn.Close()
End Try
ResetID()
End Function
If cbGenFY.Checked Then
sqlStr = "DELETE FROM FIN_FiscalYear"
TableName = "dbo.FIN_FiscalYear"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFY()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenFund.Checked Then
sqlStr = "DELETE FROM FIN_Fund"
TableName = "dbo.FIN_Fund"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFund()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenFunc.Checked Then
sqlStr = "DELETE FROM FIN_Function"
TableName = "dbo.FIN_Function"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFunc()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenObject.Checked Then
sqlStr = "DELETE FROM FIN_Object"
TableName = "dbo.FIN_Object"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenObject()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenCenter.Checked Then
sqlStr = "DELETE FROM FIN_Center"
TableName = "dbo.FIN_Center"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenCenter()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenProject.Checked Then
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "dbo.FIN_CodeBook"
DelAll()
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "dbo.FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "dbo.FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_Project"
TableName = "dbo.FIN_Project"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenProject()
timeStepStop = Date.Now
TableName = "dbo.FIN_Project"
DispOneCounts()
End If
If cbGenProgram.Checked Then
sqlStr = "DELETE FROM FIN_Program"
TableName = "dbo.FIN_Program"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenProgram()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenGL.Checked Then
sqlStr = "DELETE FROM FIN_gl"
TableName = "FIN_gl"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenGL()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenRevenue.Checked Then
sqlStr = "DELETE FROM FIN_Revenue"
TableName = "FIN_Revenue"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenRevenue()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenBank.Checked Then
sqlStr = "DELETE FROM FIN_VendorBankAccnt"
TableName = "dbo.FIN_VendorBankAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_VendorBank"
TableName = "dbo.FIN_VendorBank"
DelAll()
sqlStr = "DELETE FROM FIN_bankAdd"
TableName = "dbo.FIN_bankAdd"
DelAll()
sqlStr = "DELETE FROM FIN_bankTERMS"
TableName = "dbo.FIN_bankTerms"
DelAll()
sqlStr = "DELETE FROM FIN_bank"
TableName = "dbo.FIN_bank"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenBank()
timeStepStop = Date.Now
TableName2 = "dbo.FIN_bankTERMS"
TableName3 = "dbo.FIN_BankAdd"
TableName4 = "dbo.FIN_VendorBank"
TableName5 = "dbo.FIN_VendorBankAccnt"
DispTwoCounts()
End If
If cbFinAP.Checked Then
sqlStr = "DELETE FROM FIN_Period"
TableName = "FIN_Period"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenPeriod()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbFinVM.Checked Then
sqlStr = "DELETE FROM FIN_vendorClass"
TableName = "FIN_vendorClass"
DelAll()
sqlStr = "DELETE FROM FIN_vendorAdd"
TableName = "FIN_vendorAdd"
DelAll()
sqlStr = "DELETE FROM FIN_vendor"
TableName = "FIN_vendor"
DelAll()
sqlStr = "DELETE FROM FIN_AddressType"
TableName = "FIN_AddressType"
DelAll()
sqlStr = "DELETE FROM FIN_VendorStatus"
TableName = "FIN_VendorStatus"
DelAll()
sqlStr = "DELETE FROM States"
TableName = "States"
DelAll()
sqlStr = "DELETE FROM Country"
TableName = "Country"
sqlStr = "DELETE FROM FIN_IndustrialCodes"
TableName = "FIN_IndustrialCodes"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenIndCodes()
timeStepStop = Date.Now
DispOneCounts()
DelAll()
ClearCounts()
timeStepStart = Date.Now
FinVendStat()
timeStepStop = Date.Now
TableName = "FIN_VendorStatus"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinAddrType()
timeStepStop = Date.Now
TableName = "FIN_AddressType"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenCountry()
timeStepStop = Date.Now
TableName = "Country"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenState()
timeStepStop = Date.Now
TableName = "States"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinVM()
timeStepStop = Date.Now
TableName = "FIN_Vendor"
TableName2 = "FIN_VendorAdd"
DispTwoCounts()
End If
If cbFinbudget.Checked Then
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "FIN_CodeBook"
DelAll()
ClearCounts()
TableName = "FIN_Budget"
timeStepStart = Date.Now
FinBudget()
timeStepStop = Date.Now
DispOneCounts()
ClearCounts()
TableName = "FIN_Codebook"
TableName2 = "FIN_budgetAccnt"
timeStepStart = Date.Now
FinCodeBook()
timeStepStop = Date.Now
DispTwoCounts()
ClearCounts()
End If
View 4 Replies
View Related
Oct 20, 2006
Hi All!
I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:
1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day
I've got everything except removing the stored procedure so I will really appreciate the help.
Thank you all!
View 1 Replies
View Related
Jul 25, 2006
I am trying to use several tables that have one 10-character text field in
common. Most of the records have a numeric expression, but some tables have leading
0's, and some don't.
I can't cast the field to numbers because there are some records that have
letters also.
What function can I use to get rid of all the 0s at the left of each record?
(Sort of a LTRIM function that gets rid of 0s instead of spaces).
Thanks!
View 3 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
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 Replies
View Related
Sep 19, 2006
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
View 1 Replies
View Related
Aug 9, 2007
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
View 9 Replies
View Related
Jul 18, 2014
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
View 9 Replies
View Related
Dec 28, 2005
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies
View Related
Sep 26, 2014
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
View 3 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
