3) purchases: has relationships to both the users and products tables.
How can I write a stored procedure that inserts to the Purchases table and
populates to the other two tables. I was thinking that the records have to be
populated first on the "users" and "products" table then
when my INSERT is done on the "purchases" table it will refer to the
other two tables. I tried query builder but it wouldn’t allow me to insert from
one single INSERT statement to one table. Any advice that on how to approach
this? Thanks you!
I have a query that select rows from the employees,salary_head and salary_group tables this is the query SELECT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level], dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id, dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no FROM dbo.employees INNER JOIN dbo.salary_head INNER JOIN dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id i also have a table called payrollers1 with the following fields payroll_id int auto payperiod_id int employee_id level_id designation_id banks_id bankaccount_no salarygroup_id Amount I am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_i d,amount rows into the payroller table. As for the payperiod_id i want the Stored procedure to look up the max payperiod value. I am totally new to stored procedure and do not know how to write this code. Can somebody help me with this code.
Hi,is it possible to create an "INSERT INTO ..... "Select from storedprocedure" Query?I want to create an temporary table. In this table I want to enter the data,which I can get from an stored procedure.But in the FROM-clause a stored procedure is not allowed?
How do most people handle database searches for things like a product database? What I mean by that is your typical product table may look like: ProductIDProductTitleProductDescriptionProductCategoryIDEtc... Assuming you have a Full Text Catalog set up on the ProductDescription field, would you use Dynamic Sql or a Stored Procedure? I'm wrestling with this because I haven't found a good way to either parse a parameter in SQL to make a stored procedure work with the same flexibility as Dynamic TSQL, and I would prefer not to have any direct access to the table from the application. My other option (which to me isn't a great one) is to have a fairly large number of optional "keyword" parameters in the stored procedure and then parse them on the application side...this is less than favorable in that in theory someone could pass more keywords than I've allotted in my stored procedure. How would you approach this challenge? Thanks in advance. Ryan
SQL SERVER 2000Hi allThis is my first attempt at writing a stored procedure. I have managed toget it working but its unlikely to be the best way of handling the problem.While writing it I found some things that I don't understand so if any onecould shed any light it would be much appreciated. I have posted these atthe end.Sorry about the length but I thought it might be worthwhile posting the codeThe purpose of the procedures is as follows : we have a view of lots of bitsof information that need automatically mailing to different people. eachelement of information has a name allocated against it. If we had 100 piecesof data, 50 could go to manager 1 25 could go to manager 2 and 25 to manager3 etc...Both SP's look at the same viewThe first SP generates a distinct list of managers and for each managercalls the second SPThe second SP filters the view for the data belonging to the selectedmanager, and builds an HTML mail. It then sends all the bits of informationbelonging to that manager off in an EMAIL to him/her. ( It uses a brilliantbit of code from sqldev.net to handle the mail)the first mail then repeats for all the managers in the listCODE ---- SP 1ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION_2ASbeginSET NOCOUNT ONdeclare @no_of_managers as intdeclare @current_record as intdeclare @manager_name as varchar(100)-- count how many distinct managers we need to send the mail toselect @no_of_managers = COUNT(DISTINCT manager_name) FROMdbo.vw_client_notification_email_1-- open a cursor to the same distinct listdeclare email_list cursor for select distinct manager_name fromdbo.vw_client_notification_email_1 dscopen email_list-- for each distinct manager get the managers name and pass it to the storedprocedure that generates the mail.set @current_record = 0while (@current_record) < @no_of_managersbeginfetch next from email_list into @manager_nameEXECUTE dbo.pr_admin_client_weekly_notification @manager_nameset @current_record = @current_record+1end-- close the cursorclose email_listdeallocate email_listendCODE ---- SP2ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION(@current_manager_name as varchar(100))-- a unique managers name is passed from the calling procedureas beginSET NOCOUNT ON-- declarations for use in the stored procedureDECLARE @to as varchar(100)DECLARE @entry varchar(500)DECLARE @region as varchar(100)DECLARE @type as varchar(100)DECLARE @site_ref as varchar(100)DECLARE @aborted as varchar(100)DECLARE @weblink as varchar(1000)DECLARE @manager_name as varchar(100)DECLARE @manager_email as varchar(100)DECLARE @body VARCHAR(8000)DECLARE @link varchar(150)DECLARE @web_base VARCHAR(150)-- set up a connection to the view that contains the details for the mailDECLARE email_contents cursor for select region,type,site_ref,aborted_visit,link,manager_name,manager_e mail fromvw_client_notification_email_1 where manager_name = @current_manager_nameopen email_contents--some initial textset @body = '<font color="#FF8040"><b>Reports W/E ' +convert(char(50),getdate()) + '</b></font><br><br> <a href = http://xxxx > Click here to logon to xxxxx </a><br><br> '--fetch the first matching record from the table and build the body of themessagefetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailset @web_base = 'http://'set @weblink = @web_base + @linkif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'set @body = @body + '<font size="2"><b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ ' <b>Unique Report Reference </b>' + @link + '<br>'-- continue reading the records for this particular message and adding on tothe body of the textwhile(@@fetch_status = 0)beginfetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'if (@@fetch_status = 0) set @body = @body + '<b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ '<b>Unique Report Reference </b>' + @link + '<br>'end-- close the cursorset @body = @body + '</font>'close email_contentsdeallocate email_contents-- generate the mailDECLARE @rc int EXEC @rc = master.dbo.xp_smtp_sendmail@FROM = N'FROM ME',@TO = @manager_email,@server = N'server',@subject = N'Weekly Import',@message = @body,@type = N'text/html'endQuestionsis the way I've done it OK. I thought I would be able to do it in a singleSP but I really struggled nesting the cursor things.@@fetchstatus seems to be global, so if your using nested cursors, how doyou know which one you are refering to. If you have multiple calls to thesame SP how does it know which instance of the SP it refers to.When I first wrote it, I used a cursor in SP1 to call SP2, but I couldn'tget the while loop working - I have a feeling it was down to the @@fetchstatus in the 'calling' procedure being overwritten by the@@fetchstatus in the 'called' procedure.The whole @@fetchatus thing seems a bit odd. In the second procedure, I haveto fetch, then check, manipulate then fetch again, meaning that the samemanipulation code is written twice. thats why in the first procedure I usedthe select distint count to know how long the record set is so I only haveto run the manipulation code once. Is what I have done wrong?its possible that the body of the mail could be > 8K, is there anotherdatatype I can use to hold more than 8Kmany thanks for any help or adviceAndy
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom )
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
Hi, I am having trouble inserting 2 fields in a row using a stored procedure. This works fine: Exec ('Insert Into NumbersPull (Number)'+ @SQL) but when I try to insert another value into field 2 it errors out: I try this: Exec ('Insert Into NumbersPull (Number,resultID) Select ('+ @SQL + '),' + @resultID' ) and get this error: ERROR: Line 2: Incorrect syntax near ')'. Thanks, Doug
Having problem do INSERT values to my SQL DB with an StoredProcedure. SELECT works fine. My StoredProcedure : CREATE PROCEDURE insert_test @id int ,@Rubrik char(25), @Info char(60) , @Datum datetime ASINSERT INTO test_news(ID, Rubrik, Info, Datum) VALUES (@id, @Rubrik, @Info, @Datum)GO The StoredProcedure works fine in the SQL Query Analyzer
My Code; int num= 1234; string rub = "KLÖKÖLKÖLKÖL"; string ino = "slökdjfkasdkfjsdakf";SqlConnection myConnection = new SqlConnection("server='SOLDANER\DAER'; trusted_connection=true; database='SeWe'"); SqlCommand myCommand = new SqlCommand("insrt_test", myConnection); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add( "@id",num);myCommand.Parameters.Add( "@Rubrik",rub );myCommand.Parameters.Add( "@Info", ino);myCommand.Parameters.AddWithValue( "@Datum", DateTime.Now ); Even tried AddWithValues Dont get any error.....
OK I have a stored procedure that inserts information into a database table. Here is what I have so far: I think I have the proper syntax for inserting everything, but I am having problems with two colums. I have Active column which has the bit data type and a Notify column which is also a bit datatype. If I run the procedure as it stands it will insert all the information correctly, but I have to manually go in to change the bit columns. I tried using the set command, but it will give me a xyntax error implicating the "=" in the Active = 1 How can I set these in the stored procedure?1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 -- ============================================= 6 -- Author:xxxxxxxx 7 -- Create date: 10/31/07 8 -- Description:Insert information into Registration table 9 -- ============================================= 10 ALTER PROCEDURE [dbo].[InsertRegistration] 11 12 @Name nvarchar(50), 13 @StreetAddress nchar(20), 14 @City nchar(10), 15 @State nchar(10), 16 @ZipCode tinyint, 17 @PhoneNumber nchar(20), 18 @DateOfBirth smalldatetime, 19 @EmailAddress nchar(20), 20 @Gender nchar(10), 21 @Notify bit 22 23 AS 24 BEGIN 25 -- SET NOCOUNT ON added to prevent extra result sets from 26 -- interfering with SELECT statements. 27 SET NOCOUNT ON; 28 29 INSERT INTO Registration 30 31 (Name, StreetAddress, City, State, ZipCode, PhoneNumber, DateOfBirth, EmailAddress, Gender, Notify) 32 33 VALUES 34 35 (@Name, @StreetAddress, @City, @State, @ZipCode, @PhoneNumber, @DateOfBirth, @EmailAddress, @Gender, @Notify) 36 37 --SET 38 --Active = 1 39 40 END 41 GO
I'm trying to make sure that a user does not allocate more to funds than they have to payments. Here is what my stored procedure looks like now: I listed th error below ALTER PROCEDURE [dbo].[AddNewFundAllocation] @Payment_ID Int,@Receipt_ID Int,@Fund_ID Int,@Amount_allocated money,@DateEntered datetime,@EnteredBy nvarchar(50)ASSELECT (SUM(tblReceiptsFunds.Amount_allocated) + @Amount_allocated) AS total_allocations, Sum(tblReceipts.AmountPaid) as total_paymentsFROM tblReceiptsFunds INNER JOIN tblReceipts ON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@Payment_IDIF (total_allocations<total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)
ELSE BEGIN PRINT 'You are attempting to allocate more to funds than your total payment.' END I get the following error when I try and save the stored procedure: Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26 Invalid column name 'total_allocations'. Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26 Invalid column name 'total_payments'.
I'm trying to insert the details in the "registration form" using stored procedure to my table. My stored procedure is correct, but I dunno what is wrong in my code or I dunno whether I've written correct code. My code is below. Please let me know what is wrong in my code or my code is itself wrong..... protected void RegisterSubmitButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds;Integrated Security=SSPI"); SqlCommand cmd; cmd = new SqlCommand("registeruser", conn); SqlParameter par = null; par= cmd.Parameters.Add("@fname", SqlDbType.VarChar, 30); par.Value = RegisterFirstNameTextBox; par = cmd.Parameters.Add("@lname", SqlDbType.VarChar, 30); par.Value = RegisterLastNameTextBox; par = cmd.Parameters.Add("@uname", SqlDbType.VarChar, 30); par.Value = RegisterUserNameTextBox; par = cmd.Parameters.Add("@pwd", SqlDbType.VarChar, 20); par.Value = RegisterPasswordTextBox; par = cmd.Parameters.Add("@email", SqlDbType.VarChar, 40); par.Value = RegisterEmailAddressTextBox; par = cmd.Parameters.Add("@secque", SqlDbType.VarChar, 50); par.Value = RegisterSecurityQuestionDropDownList; par = cmd.Parameters.Add("@secans", SqlDbType.VarChar, 40); par.Value = RegisterSecurityAnswerTextBox; try { conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception("Execption adding account. " + ex.Message); } finally { conn.Close(); } }
Hi, I have two tables "people' and "dept". What I need is a stored procedure to insert into both tables. I need first, insert into "people" table, and then, insert into "dept" table since the first insert returns people id(peo_id), which is an input parameter for "dept" table. Here is my stored procedure, but I got error:Create PROCEDURE [dbo].[insert_people] @peo_last_name varchar(35), @peo_mid_initial varchar(1) = null,@peo_first_name varchar(10), @peo_address1 varchar(50) = null, @peo_city varchar(30) = null, @peo_state varchar(2) = null, @peo_zip varchar(10) = null, @peo_ph1 varchar(30) = null, @peo_ph2 varchar(30) = null, @peo_email varchar(40) = null, @dept_id int, @peo_id int
AS SET @peo_id = (INSERT INTO people (peo_last_name, peo_mid_initial, peo_first_name, peo_address1, peo_city, peo_state, peo_zip, peo_ph1, peo_ph2, peo_email) VALUES (@peo_last_name, @peo_mid_initial, @peo_first_name, @peo_address1, @peo_city, @peo_state, @peo_zip, @peo_ph1, @peo_ph2, @peo_email)) INSERT INTO dept (dept_id, peo_id) VALUES (@dept_id, @peo_id) GO Could somebody help out? Thanks a lot!
I have a table with UserID, UserName, UserPassword I have a stored procedure as follows:ALTER PROCEDURE UserInsert @UserName varchar(50), @UserPassword varchar(50) AS BEGIN INSERT Users (UserName, UserPassword) VALUES (@UserName, @UserPassword) END I have a GridView bound to the Users Table and seperate textboxes (UserName, UserPassword) on a webform. Couple of Questions... 1. how and/or what code do I use to execute the Stored Procedure to insert what is in the textboxes into the Table using a button click event? 2. Since UserID is autogenerated on new records....does UserID need to be in the code? Many Thanks
Hi, I need to insert a new user if the user (user_login) does not exist in the table (abcd_user) using a stored procedure. I created a stored procedure called "insert_into_abcd_user". Here is the complete strored procedure... CREATE PROCEDURE [dbo].[insert_into_abcd_user] ( @first_name [VARCHAR](30), @last_name [VARCHAR](30), @email [VARCHAR](60), @user_login [VARCHAR](50)) AS INSERT INTO [dbo].[abcd_USER] ([first_name], [last_name], , [user_login])VALUES (@first_name, @last_name, @email, @user_login) I need to to insert a new user if the user (user_login) does not exist int the table (abcd_User). Any one shade on my code? I appreciate your help in advance.
INSERT INTO Table_1 SELECT Source_Table.Field_1, Source_Table.Field_2, Source_Table.Field_3, ???? OUTPUT parameter returnet from a stored procedure ??????? FROM Source_Table
I have created a stored procedure which simply inserts two records into a table. Here is my stored procedure:- //BEGIN ALTER PROCEDURE [dbo].[pendingcol] @cuser varchar(100) AS Declare @sqls nvarchar(1000)
This is the code i am using to call my stored procedure using VB.NET:- //BEGIN 'variables Dim user As String user = Profile.UserName
'connection settings Dim cs As String cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True" Dim scn As New SqlConnection(cs)
'parameters Dim cmd As New SqlCommand("pendingcol", scn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@cuser", SqlDbType.VarChar, 1000) cmd.Parameters("@cuser").Value = user
'execute scn.Open()
cmd.ExecuteNonQuery()
scn.Close() //END
Now when i execute this code i get an error point to cmd.ExecuteNonQuery() that says " The name "recordone" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. "
As far as i can see theres nothing wrong with the VB code, im guessing that the problem lies somewhere in my stored proc!
Can anyone please enlighten me on where i may be going wrong? Cheers
For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.
Hello, I have created a web page with a FormView that allows me to add and edit data in my database. I configured the SqlDataSource control and it populated the SELECT, INSERT, UPDATE and DELETE statements. I've created a stored procedure to do the INSERT and return to new identity value. My question is this: Can I configure the control to use this stored procedure? If so, how? Or do I have to write some code for one of the event handlers (inserting, updating???) Any help would be appreciated. -brian
So I have simply procedure: Hmmm ... tak sobie myślę i ...
Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w ten sposob co napisalem kilka postow wyzej (select ID as UID from aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic @user_id int, @topic_katId int, @topic_title nvarchar(256), @post_content nvarchar(max)as insert into [forum_topics] (topic_title, topic_userId,topic_katId) values (@topic_title, @user_id, @topic_katId) insert into [forum_posts] (topic_id, user_id,post_content) values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id". Can anyone help me ? I tried lots of ways but nothing works.
I'm doing this more as a learning exercise than anything else. I want to write a stored procedure that I will pass a key to it and it will look in the database to see if a row exists for that key. If it does, then it needs to update the row on the DB, if not, then it needs to insert a new row using the key as an indexed key field on the database.for starters can this even be done with a stored procedure?if so, can someone provide some guidance as to how?thanks in advance,Burr
In my stored procedure I need to select some data columns and insert them into a #temp tbl and then select the same data columns again using a different from and put them into the same #temp tbl. It sounds like a union, can I union into a #temp tbl? Any help here is appreciated.
Want help in creating the stored procedure of company where id is the PrimaryKey in the table companymaster which is created in sql server 2005.1 ALTER PROCEDURE companyinsert 2 3 @companyid int, 4 @companyname varchar(20), 5 @address1 varchar(30) 6 7 AS 8 9 INSERT INTO companymaster 10 ( companyname, address1) 11 VALUES (@companyname,@address1) Procedure or Function 'companyinsert' expects parameter '@companyid', which was not supplied.
The id is to be created autogenerate in the sequence number.There should be no duplicated companyname with different ids in same table.Apart from the above error can anyone pls give me or tell me the code or modify the stored procedure according to the above..thanxs....
Feeling really dumb tonight - below is my stored procedure and code behind which completes (it puts "completed" in TextBox4) but does not insert anything into database. Questions:1) do in need to include the primary key field in the insert stored procedure?2) do I need a DataAdapter to actually get it running and open and close the connection? STORED PROCEDURE running on SQL 2000 server: ______________________________________ CREATE PROCEDURE newuser003.InsertCompanyInfo @CS_CompanyName nchar(100),@CS_City nchar(500),@CS_Phone varchar(20) AS INSERT into tblCompanyInfo_Submit(CS_CompanyName, CS_City, CS_Phone)VALUES ('@CS_CompanyName', '@CS_City', '@CS_Phone')RETURN C# CODE BEHIND: ______________________________________________________ public partial class ContractorSubmision : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("CompanyInfoSubmit", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CS_CompanyName", TextBox1.Text); cmd.Parameters.AddWithValue("@CS_City", TextBox2.Text); cmd.Parameters.AddWithValue("@CS_Phone", TextBox3.Text); TextBox4.Text = "Completed"; }}
Hello, I am having an issue with the insert command in a stored procedure in SQL Server 2005. The procedure is designed to generate a Customer number and insert an initial value (a delivery charge) into an order table. Finally, it returns the customer number back to the caller. When I execute the stored procedure from SQL Server Management Studio, it works perfectly. However, when I call it from ASP.net, the procedure properly generates a Customer Number and returns it to my application, but the insert never takes place. Can someone help me figure out why it won't insert? My ASP.Net (C#) code is here: SqlDataSource dsSelect = new SqlDataSource(); dsSelect.ConnectionString = ConfigurationManager.ConnectionStrings["obeConn"].ConnectionString; dsSelect.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; dsSelect.SelectCommand = "getTempCustNo";
Hi, I am strugling to execute a insert stored procedure on a button click. The stored procedure is taking values from a temp table and inserting them into a identical table. The procedure is expecting 1 value from a Query string, the stored procedure works as expected when hard coded.
Im completely new to this and have no idea where to begin, i have been looking through the forums for several hours and am still none the wiser.
please can someone point me in the right direction
ok I have a stored procedure...... I pass in the variables that are requried....What is the best way to add a record using my stored procedure in VB.net code in a button click event...... How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn
CREATE procedure dbo.Appt_AddAppt ( @ClinicID int, @AccountNum nvarchar(10), @DOS nvarchar(12), @POSCODE nvarchar(5) ) as Insert into Clinic_Appointments (ClinicID,AcctNumber,DateOfService,PlaceOfService,PlaceOfServiceID) Values (@ClinicID,@AccountNum,@DOS,@POSCODE,@ClinicID)
I am trying to take some SQL queries written by Visual Studio, one insert and one update and combine them into a single stored procedure. The insert procedure should be included in the update procedure and a check should be done for an existing record based upon the primary key. If it exist, an update command should be performed, else an insert. I also need to wrap the procedure in a transaction and rollback if any errors have occurred, else commit the transaction. If I have the following Insert and Update statements, can anyone help me write the stored procedure I need? Again, the current statements were automatically created and could be modified as needed.
INSERT INTO tblClub(ClubPKID, ClubName) VALUES (@ClubPKID, @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @@IDENTITY)
UPDATE tblClub SET ClubPKID = @ClubPKID, ClubName = @ClubName WHERE (ClubPKID = @Original_ClubPKID) AND (ClubName = @ClubName); SELECT ClubPKID, ClubName FROM tblClub WHERE (ClubPKID = @ClubPKID)
Having a little trouble not seeing why this insert is not happening.... --snip-- DECLARE c_studId CURSOR FOR SELECT studentId FROM students FOR READ ONLY OPEN c_studId FETCH NEXT FROM c_studId INTO @studentId IF( @@FETCH_STATUS = 0 ) BEGIN SET @studRec = 'Found' END CLOSE c_studId DEALLOCATE c_studId BEGIN TRAN IF (@studRec <> 'Found') BEGIN INSERT INTO students (studentId) VALUES (@studentId) END Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath
I have a table that has data and is indexed by the Visit #. Each month I receive new data from a text file that I have to import into this table. On all occasions the text file will have one of the existing Visit #'s contained in it.
I wrote a simple stored procedure that has the INSERT/SELECT statement but the problem I am having is when I execute the Stored Procedure and I run into a record from the text file that already exist in my table the stored procedure errors and stops at that point.
How do I write a stored procedure that will ignore that text record and continue reading the text file and insert records that do not exist?
FoodID Integer PRIMARY KEY, FoodName varchar(255), FoodDesc text
How do I insert into this table while checking that the FoodName do not replicate? I'm aware that with a stored procedure I'm able to you the IF EXIST statement to help me solve this problem. But if I do not wish to use the stored procedure, am I able to create a SQL string to insert while checking the condition?
Thanks in advance to the people that replied to my request! Thanks so much...
