Stored Procedure And Insert Data...
Feb 22, 2008
I have this stored procedure.. but it doesn't insert data... Why???
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.DETTAGLIO_TURNI_DIFENSORI
AS
INSERT INTO Albo_Turno_Dettaglio
(
idalboturno,
idalbo,
idturno,
data
)
VALUES
(
'885261', -- ID chiave
'15', -- da cursore
'778',
'2008-04-01 00:00:00.000' -- problemi inserimento data??
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 9 Replies
ADVERTISEMENT
Jan 29, 2008
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
View 6 Replies
View Related
Nov 30, 2000
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?
View 2 Replies
View Related
Dec 1, 2005
Hello all,
I am having a lot of trouble with stored procedures. Could anyone help me out.
I have a table which contains a number of meetings. What I want to do is search this table, get out all the meetings for today and put them in a seperate table meetings today.
I can select the values, and I can insert the values.
But how do I store the values so that i can pass the results of the select to the insert?
Im also having a lot of trouble with storing date values.
ANy help would be greatly appreciated.
Regards,
Padraic Hickey
View 2 Replies
View Related
May 5, 2008
Following sp is not executing because an extra single quote is being inserted for the datetime parameter(@joined_date).
This is suppose to be a single quote but there are two single quotes. I am calling this stored procedure from web application.
sp is executed in sql server 2005.
Can someone help me to solve this.
declare @p12 uniqueidentifier
set @p12=NULL
declare @p13 uniqueidentifier
set @p13=NULL
exec sp1
@usr_crmid='6ea898c3-ca4c-4acf-a01e-510280deef2d',
@session_crmid='9d18a96e-36e8-4820-a6f4-8861477bce6c',
@card_account_number='634009480603130049',
@name_1=N'Name Unknown',
@joined_store_code=-1,
@joined_date=''2008-05-05 12:56:06:000'', => @joined_date='2008-05-05 12:56:06:000'
(correct one)
@usr_name=N'ngcadmin',
@official_id=NULL,
@preferred_store_code=-1,
@foreign_flag='0',
@reason_code='0',
@customer_crmid=@p12 output,
@card_account_crmid=@p13 output
select @p12, @p133
View 7 Replies
View Related
Jan 25, 2008
Hopefully someone can help me with this, or tell me if it's even possible for SQL Server 2005. I'm new to this and learning as I go along.
Here's what I want to do - I have a couple of queries running off a linked server. I want to take the results of those queries and insert them into a table in SQL Server, but first I want to delete the current contents of that table. I want to build a stored procedure that I can run monthly to temporarily store this data for review; once I'm ready to review the next month I want the procedure to delete the data in the table and then insert the next month's data. Hopefully that makes sense.
Is it possible to build? Anyone have any examples of such a thing, or can you point me someplace that might be able to help?
Thanks.
View 5 Replies
View Related
Jul 4, 2007
Hi all,I have heard that we must insert into two tables simultaneously when there is a ONE-TO-ONE relationship.
Can anyone tell me how insert into two tables at the same time, using SP?
Thanks
Tomy
View 1 Replies
View Related
Nov 3, 2007
Hi
I am currently developing my first database driven application and I have stumbled over some quite simple issue. I'll describe my database design first:
I have one table named images(id (identity), name, description) and one table named albums (id, name, description). Since I'd like to establish a n:n connection between these, I defined an additional table ImageInAlbum (idImage, idAlbum). The relation between these tables works as expected (primary keys, foreign keys appear to be ok).
Now I'd like to insert data via a stored procedure in sql server 2005 and I'm not sure how this procedure will look like.
To add a simple image to a given album, I am trying to do the following:
* Retrieve name, description from the UI
* Insert a new row into images with this data
* Get the ID from the newly created row
* Insert a new row into "ImageInAlbum" with the ID just retrieved and a fixed Id from the current album.
I know how I would do the first two things, but I am not used to Stored Procedures syntax yet to know how to do the other things.
Any help is appreciated ... even if it means telling me that I am doing something terribly wrong
View 9 Replies
View Related
Sep 22, 2015
I received this stored procedure that I modified to run on my system. Specifically, I only changed the database and filter text and left the rest alone. When I execute the stored procedure, I get the error:
Msg 515, Level 16, State 2, Procedure ObjectNotesInsert, Line 18
Cannot insert the value NULL into column 'RefRowPointer', table 'pSCI_App.dbo.ObjectNotes'; column does not allow nulls. INSERT fails. The statement has been terminated.
Here is the actual stored procedure I am running. I should add that I can execute each step and get results and if I hard code the resulting values into the procedure, the execution works.
USE [pSCI_App]
GO
/****** Object: StoredProcedure [dbo].[_JAMTestSp] Script Date: 09/21/2015 11:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
View 30 Replies
View Related
Jun 26, 2007
I want to insert data calling a stored procedure and call this from a Data Flow destination object. Is it possible?
I understand that Ole Db Command transformation object can call stored procedure, but that will not rollback in the event of error in the middle.
I understand that Ole Db Destination object will rollback in middle of import, but I don't see how to do the insert by calling stored procedure. "Sql Command" option in Ole Db Destination object does not seem to present solution to the problem.
Am I missing something here or is Ssis / Microsoft demanding that Insert stored procedure not be used when using Data Flow destination object to insert data into target table?
View 8 Replies
View Related
Feb 28, 2008
Hi,
I'm reasonably new to ASP.NET 2.0
I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure. I also want the stored procedure to return an output value. I do not need to perform a select or a delete.
For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control. I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures).
I desperately need the syntax to:
a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need).
b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters).
c) syntax on how to set the values for these parameters (again...coming from form controls and session variables)
d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource.
If anybody has sample code or a link or two, I would be most appreciative.
Thank you in advance for any help!
View 5 Replies
View Related
Apr 25, 2006
Hi,
I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
View 1 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Nov 7, 2006
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
View 3 Replies
View Related
Feb 27, 2007
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.....
View 3 Replies
View Related
Oct 31, 2007
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
View 8 Replies
View Related
Nov 17, 2007
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)
Values (@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'.
View 6 Replies
View Related
Dec 5, 2007
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(); } }
View 2 Replies
View Related
Jan 28, 2008
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!
View 3 Replies
View Related
Apr 9, 2008
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
View 1 Replies
View Related
Feb 21, 2006
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.
-- Srinivas Gupta.
View 2 Replies
View Related
Sep 17, 2004
For example:
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
Is posible this ?
View 2 Replies
View Related
Aug 30, 2006
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)
SELECT @sqls = 'INSERT INTO' + @cuser + '(UserName, Pending) VALUES ("recordone", "recordtwo")'
EXECUTE sp_executesql @sqls
RETURN
//END
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
View 10 Replies
View Related
Jun 20, 2006
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
View 1 Replies
View Related
Oct 14, 2006
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.
View 6 Replies
View Related
Dec 27, 2006
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
View 5 Replies
View Related
Mar 15, 2007
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.
View 4 Replies
View Related
Apr 2, 2007
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....
View 5 Replies
View Related
Apr 3, 2007
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"; }}
View 5 Replies
View Related
May 8, 2007
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";
DataView reader = (DataView)dsSelect.Select(DataSourceSelectArguments.Empty);
Session["CustNo"] = reader[0]["CustNumber"].ToString().Trim();
Session["PriceCode"] = reader[0]["PriceCode"].ToString().Trim();
Session["orderNo"] = reader[0]["OrderNo"].ToString().Trim();
My stored procedure is here:ALTER PROCEDURE [dbo].[getTempCustNo]
AS
BEGIN
declare @CustNo int;
declare @prodDesc VARCHAR(max);
declare @prodNo int;
declare @stdPrice float;
declare @orderNo int;
declare @priceCode int;
-- populate values
SELECT @custNo = ISNULL(MAX(OD_CUSTNO)+1, 100000000), @prodNo = 999, @orderNo = 1, @priceCode = 1000
FROM [dbo].[tblOrderDet]
WHERE OD_CUSTNO >= 100000000;
-- get the price of our product
SELECT @stdPrice = PR_RATE_1
FROM tblPrices
WHERE PR_PRODNO = @ProdNo
AND PR_RCDCOD = @priceCode;
-- get the description
SELECT @ProdDesc = PM_PRDESC + ' ' + PM_VOLUME FROM dbo.tblPromas WHERE PM_PRODNO = @ProdNo;
--Add a delivery charge to the customer's order table
INSERT INTO tblOrderDet (OD_ORDRNO, OD_CUSTNO, OD_PRODNO, OD_QUANTITY, OD_ORG_QUANTY, OD_PRICE, OD_DESCR, OD_LOW)
VALUES (@orderNo, @CustNo, @prodNo, 1, 0, @stdPrice, @prodDesc, 'N');
--Return the customer values
SELECT @custNo AS 'CustNumber', @orderNo AS 'OrderNo', @priceCode AS 'PriceCode';
END
View 3 Replies
View Related
Jan 22, 2008
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
View 13 Replies
View Related
Apr 7, 2004
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)
GO
View 4 Replies
View Related
Jul 27, 2004
I have an SP to add a record to the database but i get the error shown below. Any help appreciated.
Stored Procedure:
CREATE PROCEDURE addUser
@username char(15),
@password char(12)
AS
INSERT INTO users(username, password) VALUES (@username, @password)
GO
Code calling SP:
Dim myConnection As New System.Data.SqlClient.SqlConnection(ConnectionString)
Dim cmd As New SqlCommand("addUser", myConnection)
MyDataAdapter = New SqlDataAdapter()
'MyDataAdapter = New SqlDataAdapter("addUser", myConnection)
With cmd 'MyDataAdapter
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@username", SqlDbType.Char).Value = username
.Parameters.Add("@password", SqlDbType.Char).Value = password
End With
Try
myConnection.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlException
Finally
myConnection.Close()
End Try
I get an error message on the line: cmd.ExecuteNonQuery()
System.FormatException: Input string was not in a correct format.
View 2 Replies
View Related
