.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> VB.Net >> Post New QuestionBookmark and Share Subscribe to Forum

Stored procedure with vb.net

Posted By: srini     Posted Date: January 11, 2011    Points:2   Category :VB.Net
Hi,

I want to know,how to use stored procedures in vb.net application. I know,how to create stored procedure,but dont know how to use it.

I need two things,

1. Want to select the data using stored procedure in vb.net appl.(Tried with code for select data's from database)
2. Want to insert the data using stored procedure in vb.net appl..(Tried with code for insert data's into the database)

Note:

Database: SQL Server 2005


Responses
Author: suwarna             
Posted Date: January 11, 2011     Points: 5   

first create stored procedure
create PROCEDURE dbo.StoredProcedure2

(
@progid char(10),
@progfn text,
@progln text

)
AS
INSERT INTO programmer
(
programmer_id,
programmer_fname,
programmer_lname
)
VALUES
(
@progid,
@progfn,
@progln

)


then


write code in application .net
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
con = New SqlConnection("Data Source=mca-16e44b82e4d\sqlexpress;Initial Catalog=master;Integrated Security=True")
con.Open()


Dim command As SqlCommand = New SqlCommand("StoredProcedure2", con)
command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@progid", SqlDbType.Char).Value = TextBox1.Text.ToString()
command.Parameters.Add("@progln", SqlDbType.Text).Value = TextBox2.Text.ToString()
command.Parameters.Add("@progfn", SqlDbType.Text).Value = TextBox3.Text.ToString()
Dim icount As Integer
icount = command.ExecuteNonQuery

Catch ex As Exception
MsgBox(ex.ToString)

End Try
End Sub
End Class



Author: srini             
Posted Date: January 12, 2011     Points: 5   

Hi Suwarna,

Thanks for your Reply!

But it doesn't insert the values into stored procedure.

Code is:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Try
con = New SqlConnection("Data Source=.\SQLExpress;Initial Catalog=loginpage;Integrated Security=True")
con.Open()
cmd = New SqlCommand("LoginDetails", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Username", SqlDbType.Text).Value = txtUser.Text.ToString()
cmd.Parameters.Add("@Password", SqlDbType.Text).Value = txtPass.Text.ToString()
Dim icount As Integer
icount = cmd.ExecuteNonQuery()

MessageBox.Show("Records Added")

Catch ex As Exception
MessageBox.Show("Sorry")
End Try
end sub


Stored Procedure Query is:

create procedure [dbo].[LoginDetails]
as
select * from login

Login Table looks like:

Username Password

admin admin
user user



Please help me!!!!!!!!!!!!!

Srini
Author: suwarna             
Posted Date: January 12, 2011     Points: 5   

i had given procedure for insert record for select record no need to specify the yhe foollowing parameter bec u havent use in create procedure
cmd.Parameters.Add("@Username", SqlDbType.Text).Value = txtUser.Text.ToString()
cmd.Parameters.Add("@Password", SqlDbType.Text).Value = txtPass.Text.ToString()

Author: suwarna             
Posted Date: January 12, 2011     Points: 5   

for insert record

create procedure [dbo].[LoginDetails]

create PROCEDURE dbo.StoredProcedure2

(
@username datatype,
@admin datatype,

)
AS
INSERT INTO programmer
(
username,
admin
)
VALUES
(
@username,
@admin
)


Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Try
con = New SqlConnection("Data Source=.\SQLExpress;Initial Catalog=loginpage;Integrated Security=True")
con.Open()
cmd = New SqlCommand("LoginDetails", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Username", SqlDbType.Text).Value = txtUser.Text.ToString()
cmd.Parameters.Add("@Password", SqlDbType.Text).Value = txtPass.Text.ToString()
Dim icount As Integer
icount = cmd.ExecuteNonQuery()



this code is for vb.net




Author: srini             
Posted Date: January 12, 2011     Points: 5   

Hi,

Thanks once again.

Please give the suggestion for this query,

create PROCEDURE LoginDetails1

(
@username varchar(20),
@admin varchar(20),

)
AS
INSERT INTO login
(
username,
admin
)
VALUES
(
@username,
@admin
)


Its shows error.

Please modify it and send me soon.

Details:

Fields: Username,Password- Both in varchar(20)


Srini
Author: srini             
Posted Date: January 12, 2011     Points: 5   

I used this query,

Create procedure LoginDetails
(
username varchar(20) not null,
password varchar(20) not null
)
as
INSERT INTO [login]
(
[username],
[password],
)
VALUES
(
@username,
@password
)
Help to resolve this!!!!!!!!

Srini


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend