Using a stored Procedure to insert a new record

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
0
alewis25
4/7/2004 9:50:19 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
684 Views

Similar Articles

[PageSpeed] 13
Get it on Google Play
Get it on Apple App Store

You will need a SQLConnection object, and SQLCommand object.  Example in asp.net code:


Dim ObjCmd As SqlCommand
Dim ObjConn as New SqlConnection
ObjConn.Connectionstring = "{insert connection info herer}"
ObjConn.Open()
ObjCmd = New SqlCommand("Appt_AddAppt", ObjConn)
WITH ObjCmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@ClinicID", {input value})
.Parameters.Add("@AccountNum", {input value})
.Parameters.Add("@DOS", {input value})
.Parameters.Add("@POSCODE", {input value})
..ExecuteNonQuery()
END WITH
ObjCmd.Dispose()
ObjCmd = Nothing
ObjConn.Close()

Above is the calling code from ASP.NET. The order of your parameters must match the order in the SP. You on need the Command object because you are not returning any data. If you were returning a result set then you would use a DataReader or DataSet.
HTH
wholden
-----------------------------
0
wes
4/7/2004 10:03:27 PM
I understand passing the basic parameteres..in which you answered......The question I had was my insert values...i.e my new record needs the values (ClinicID,AcctNumber,DateOfService,PlaceOfService,PlaceOfServiceID) 

Values (@ClinicID,@AccountNum,@DOS,@POSCODE,@ClinicID) ..I guess what you said will work to create a new record lets say....Clinic 45


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
0
alewis25
4/7/2004 11:21:29 PM
I guess I do not fully understand your question.  I think you are asking if the syntax of the SP paramaters are correct?  The INSERT values are correct for the SP.  I hope this helps


wholden
-----------------------------
0
wes
4/8/2004 1:10:21 AM
heres an article on HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual Basic .NET @ MSDN 

hth
***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
4/8/2004 4:51:51 AM
Reply:

Similar Artilces:

Use Stored Procedure or insert template to insert a new record?
Hi there, I am using stored procedure for all my database website before I use .net 2. When I come to .net 2, I found almost all the videoes are using SP for select functions, and using insert template to add new ones. What's the differenence and which way is better? Thanks!  >which way is better?Whenever you can, use stored procedures - preferably your own, not some of the autogenerated horrors! If you want some help in generating them - see my post in  http://forums.asp.net/p/1091006/1635955.aspx#1635955 plus the helper bits in http://forums.asp.net/p/1089533/1631113.a...

Inserting a new record from DetailsView using a stored procedure
I am using an <asp:DetailsView ...> on my <asp:Content ...> page.I have the AutoGenerateInsertButton="True".The DataSourceID for the DetailsView is a SQLDataSource.My Insert command uses a stored procedure that takes the fields a input parameters:   InsertCommand="AddMovie" InsertCommandType="StoredProcedure"I am using a stored procedure to do add a record to my table.My question is: After I click on the "New" button and add data to the DetailsView, how do the values entered get to my stored procedure? What am I missing here? ChuckChuck Ops, my stored procedure ...

View Inserted Record Using Object Datasource, Details View and Stored Procedure
I am trying to display the record just inserted into a database using an object datasource, a stored procedure and a details view Below is the stored procedureALTER PROCEDURE [dbo].[Employees_Insert]( @LastName varchar(50), @FirstName varchar(30) = null, @JobTitle varchar(50))AS SET NOCOUNT OFF;INSERT INTO [dbo].[Employees] ([LastName], [FirstName], [JobTitle])VALUES (@LastName, @FirstName, @JobTitle);  SELECT  LastName, FirstName, JobTitleFROM Employees WHERE (EmployeeID = SCOPE_IDENTITY()) ________________________________________________________________...

Insert multiple records using one stored procedure call. SQL SERVER 7
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?   Thanks, GaryGary Gary, I'm not clear on exactly what you're asking. 28 dropdowns equates to 28 parameters in your stored procedure if you're just inserting a record. Can you provide more details? BruceBrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. Ok Chuck, never mind about the dropdowns. All Im ask is how to insert multiple records into a database u...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

Using stored procedures to insert recorders
Hi I am not familiar with stored procedures, but I tried to use it with this code and it worked fine   ALTER PROCEDURE dbo.p_insert_shortleave      @Leave_Date datetime,      @Start_time datetime,      @End_time datetime,      @Reason varchar(150),      @Submission_date datetime,      @L_D_id_Priority varchar(20)AS      INSERT INTO HR_Leave_request (Leave_Date, Start_time, End_time, Reason)   ...

sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...

Problem with inserting a Record using textbox and stored procedure
Below is the code for a form with several textboxes that I would like to use the values from to insert a record using a stored procedure. I am not having any sucess. After entering all the values and clicking the submit button no record gets entered. Any help would be greatly appreciated. Thanks in advance Gary <%@ Page Language="VB" Debug="true" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> Sub Register_Click(ByVal sender A...

How to Insert New record into sql Table using Linq?
Hi, after I did Linq to my SQL, How Do I added New Record to my table? Thanks.. Check out these videos they can explain in great detail on how LINQ to SQL works and get you going. http://www.asp.net/learn/linq-videos/ To give you a quick answer, if you are using the GUI then hover over the datasource and click Edit datasource.  Then in the second page click advanced and click enable insert update and delete.  That will allow you to perform an insert, in a round about way.Remember to mark as answer if this post answered your question. Is this written ok? RecordNums = db.Ph...

Inserting Information into an SQL Database using Stored Procedures
Hello, I'm building an ecommerce website which requires customers to create an account before they go ahead with a purchase. I have a createaccount.aspx page in Visual Web Developer 2005 with text boxes where users can enter their details (email, password, name and address).  I'm trying to insert the information which users type into the text boxes into an SQL database table called Customers. I've dragged and dropped an SQL data source onto my page and have set it to operate on my AddCustomer stored procedure. I've confirgured my data source such that the parameter for...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

Error when attempting to insert record using stored procedure
I am trying to save user data to a sql table but keep getting the following error; Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'. I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows; Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, ...

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

how to insert the range of ip address in SQL using stored procedures
hi     i need to insert the list of ipaddress using stored procedures. the user will give the from and to range of IP ADDRESS.i've to insert all  the possible ip address between those values. how to do this.. With Thanks.,R.Chezhian.,=============================================Please Mark the helpful post(s) as Answered LEts say the range is from 172.25.50.1 to 172.25.50.30 you can write a WHILE loop to loop through and insert records. There are other ways too but at this time of the night this is the best I can think of without putting any pressure on m...

Web resources about - Using a stored Procedure to insert a new record - asp.net.sql-datasource

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Are patients charged drastically different prices for the same medical procedure? - Videos - CBS News ...
Patients are often charged drastically different prices for the same medical procedures and research may link later bedtimes with gaining weight. ...

Vin Scully To Miss Dodgers Postseason After Medical Procedure
... , who has been calling Dodgers games since the Truman administration, will miss the team’s playoff run as he recovers from a medical procedure. ...

FDA finds flaws in Theranos' nanotainers and quality control procedures
... to Theranos' blood testing technology comes from the US Food and Drug Administration, which found flaws in the company's quality control procedures. ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Hall Of Fame Broadcaster Vin Scully Has Medical Procedure, Will Miss All Of 2015 Postseason
While the Dodgers will be in the 2015 MLB postseason, their Hall of Fame broadcaster, won't. Here's details on Vin Scully.

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 11/26/2015 2:57:38 AM