vb.net passing to sql sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

vb.net passing to sql sp

Author

Topic

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-12-30 : 14:36:14

Greetings again. I think I am close on this. But having a few issue's. My goal is to have a button call send some values to the SP
and then run the SP itself. In this case its to change the name of user to another. I included what I have done so far and could use some pointers.. thank you...

Call Stored Procedure

Public Sub Button_Click(ByVal sender As Object, ByVal args As EventArgs)

Dim sqlConnection1 As New SqlConnection("server=PLUTO\PLUTO;Database=core8label;Trusted_Connection=no;User Id=sa;Password=timex2112")

Dim us1ID As New SqlParameter("@us1id", SqlDbType.int, 15)
Dim us1 As New SqlParameter("@us1", SqlDbType.char, 50)
Dim email As New SqlParameter("@email", SqlDbType.char, 50)
Dim lit1a As New SqlParameter("@lit1a", SqlDbType.char, 50)
Dim lit2a As New SqlParameter("@lit2a", SqlDbType.char, 50)

Dim cmd As New SqlCommand
Dim rowsAffected As Integer

cmd.CommandText = "dbo.Changeover"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()

rowsAffected = cmd.ExecuteNonQuery()

sqlConnection1.Close()

End Sub

-----------------------------------------------------------------------------------------------

IN SQL SERVER:

CREATE PROCEDURE dbo.changeover
(@us1id INT, @us1 Char, @email Char, @lit1a Char, @lit2a Char) AS

Select * from label01 where us1id = "@us1id"
UPDATE label01
SET us1 = '@lit1a'
SET email = '@lit2a'
WHERE name = '@us1' AND '@email';

--------------------------------------------------------------------------------------------------

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-12-30 : 14:46:02

I updated the SP Part.. I think it should be more like this..
but still not sure...

IN SQL SERVER:

CREATE PROCEDURE dbo.changeover
(@us1id INT, @us1 Char, @email Char, @lit1a Char, @lit2a Char) AS

Select * from label01 where us1id = "@us1id"
UPDATE label01 SET us1 = '@lit1a' & SET email = '@lit2a'
WHERE us1id = '@us1id';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:51:12

your sp is syntactically wrong it should be


CREATE PROCEDURE dbo.changeover
(@us1id INT, @us1 Char, @email Char, @lit1a Char, @lit2a Char) AS


Select * from label01 where us1id = "@us1id"
UPDATE label01
SET us1 = @lit1a,
email = @lit2a
WHERE name = @us1 
AND email=@email;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 15:01:09

Do not use SELECT * except when using EXISTS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2008-12-30 : 15:17:05

I wont learn anything if I dont ask.. But why is it on most
examples I see, They use the Select *

But it seems if I want to be proper, I would use something.
Select us1, email, etc etc...

What is the logic behind this..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 15:24:47

SELECT * is just a quick way to return data in ad-hoc type stuff. But there is a performance penalty as SQL Server has to lookup the column list for you, so it is better for stored procedures and inline sql to explicitly write out the column list. Only use SELECT * for ad-hoc queries such as those inside Management Studio/Query Analyzer or if you are using EXISTS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 02:34:52

quote:
Originally posted by sinjin67

I wont learn anything if I dont ask.. But why is it on most
examples I see, They use the Select *

But it seems if I want to be proper, I would use something.
Select us1, email, etc etc...

What is the logic behind this..

as far as possible, dont use select *. i used it as i dont know your column names and also what all you want

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-31 : 03:19:24

<
i used it as i dont know your column names and also what all you want
>

Thats why I always suggest

SELECT Columns from your_table.......

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources