store procedure - 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

store procedure

Author

Topic

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-04-17 : 03:39:41

hello
iam new to store procedures.can anyone tell me how do i put in this sql statement into store procedure
SELECT * FROM PortMaster WHERE PortCode= '" & Request.QueryString("PortCode") & "' "

TheProblem is coming in ,how do i put "Request.QueryString("PortCode")" into sql queranalyzer.iam using this in .aspx page.
depending on querystring the record is selected.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 03:49:53

You want to create a stored procedure ?

create procedure your_sp_name
@portcode varchar(30)
as
begin
    SELECT * FROM PortMaster WHERE PortCode= @portcode
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 04:02:40

And execute that in asp page

set rs= con.Execute("your_sp_name '" & Request.QueryString("PortCode") & "'")

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-04-17 : 04:52:48

the problem is thatif i give this command in query analyzer CREATE PROCEDURE UpdatePortRecord (@PortCode Varchar(20),@PortName Varchar(100),@Country Varchar(100),@QueryString Varchar(10))
as
Update PortMaster SET PortCode=@PortCode,PortName=@PortName,Country=@Country WHERE PortCode=@QueryString
then it says
"Procedure 'UpdatePortRecord' expects parameter '@PortCode', which was not supplied."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 04:57:22

quote:
Originally posted by hotshot_21

the problem is thatif i give this command in query analyzer CREATE PROCEDURE UpdatePortRecord (@PortCode Varchar(20),@PortName Varchar(100),@Country Varchar(100),@QueryString Varchar(10))
as
Update PortMaster SET PortCode=@PortCode,PortName=@PortName,Country=@Country WHERE PortCode=@QueryString
then it says
"Procedure 'UpdatePortRecord' expects parameter '@PortCode', which was not supplied."

Your create procedure looks fine. How did you call the procedure ?
Follow as specify by Madhivanan

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-04-17 : 05:09:17

this is the call procedure i am using

Dim objcmdupdate As New SqlCommand("UpdatePortRecord '" & Request.QueryString("PortCode"), myconn)

i get error saying
Could not find storedprocedure 'UpdatePortRecord 'ty'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 05:13:04

Your stored procedure is expected 4 parameters. You only pass in one !

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-04-17 : 05:18:19

here is the rest of the code after that statement
objcmdupdate.CommandType = CommandType.StoredProcedure
objcmdupdate.Parameters.Add(New SqlParameter("@PortCode", SqlDbType.VarChar, 20))
objcmdupdate.Parameters("@PortCode").Value = tbPortCode.Text
objcmdupdate.Parameters.Add(New SqlParameter("@PortName", SqlDbType.VarChar, 100))
objcmdupdate.Parameters("@PortName").Value = tbPortName.Text
objcmdupdate.Parameters.Add(New SqlParameter("@Country", SqlDbType.VarChar, 100))
objcmdupdate.Parameters("@Country").Value = drpCountry.SelectedItem.Text
objcmdupdate.Parameters.Add(New SqlParameter("@QueryString", SqlDbType.VarChar, 100))
objcmdupdate.Parameters("@QueryString").Value = Request.QueryString("PortCode")
myconn.Open()
objcmdupdate.ExecuteNonQuery()
myconn.Close()

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 05:21:38

quote:
Originally posted by hotshot_21

this is the call procedure i am using

Dim objcmdupdate As New SqlCommand("UpdatePortRecord '" & Request.QueryString("PortCode"), myconn)

i get error saying
Could not find storedprocedure 'UpdatePortRecord 'ty'

As you used command object, that should be

Dim objcmdupdate As New SqlCommand("UpdatePortRecord" , myconn)

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-04-17 : 05:25:50

quote:
Originally posted by madhivanan

quote:
Originally posted by hotshot_21

this is the call procedure i am using

Dim objcmdupdate As New SqlCommand("UpdatePortRecord '" & Request.QueryString("PortCode"), myconn)

i get error saying
Could not find storedprocedure 'UpdatePortRecord 'ty'

As you used command object, that should be

Dim objcmdupdate As New SqlCommand("UpdatePortRecord" , myconn)

Madhivanan

Failing to plan is Planning to fail

i tried using that as well but it still says i have to supply @PortCode parameter but than how can i supply same parameter twice.as can be seen

update PortMaster SET PortCode=@PortCode,PortName=@PortName,Country=@Country WHERE PortCode=@PortCode

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 05:30:35

why do you need to set the PortCode to the same again ?

update PortMaster SET PortCode=@PortCode,PortName=@PortName,Country=@Country WHERE PortCode=@PortCode

Are you trying to change it to a different port code ? (as posted in your 2nd post)

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-04-17 : 05:34:31

see i hav to update records where "PortCode=Request.querystring("PortCode")" thats y
and yes i may change the port code

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 05:38:17

"and yes i may change the port code"
In this case, use different variable

update PortMaster 
SET    PortCode = @NewPortCode,
       PortName = @PortName,
       Country  = @Country 
WHERE  PortCode = @OldPortCode

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-04-17 : 05:47:16

got it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources