ASP Stored Proc help plz {Resolved}

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

SQL Server 2000 Forums

SQL Server Development (2000)

ASP Stored Proc help plz {Resolved}

Author

Topic

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-17 : 17:34:18

Error: Procedure 'sp_procedure_params_rowset' expects parameter '@procedure_name', which was not supplied.

Stored Proc statement:
CREATE Procedure dbo.Insert_Chart_Assignment( @tmpFirstName varchar(50),@tmpLastName varchar(50),@tmpMidInit char(1),@tmpDOB datetime,@tmpDate datetime)AS SET NOCOUNT ON INSERT INTO chartassignment(fname, lname, minitial, dob, [date]) VALUES(@tmpFirstName ,@tmpLastName,@tmpMidInit ,@tmpDOB ,@tmpDate)
SELECT SCOPE_IDENTITY()
GO

ASP Code:
If (Page.IsValid) Then
Dim mycommand As New SqlClient.SqlCommand
Dim mybuilder As New SqlClient.SqlCommandBuilder
'Dim myconnection As New SqlClient.SqlConnection(oSQLConn)
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Connection = oSQLConn

oSQLConn.Open()
mybuilder.DeriveParameters(mycommand)
mycommand.Parameters("@tmpFirstName").Value = tmpFirstName
mycommand.Parameters("@tmpLastName").Value = tmpLastName
mycommand.Parameters("@tmpMidInit").Value = tmpMidInit
mycommand.Parameters("@tmpDOB").Value = tmpDOB
mycommand.Parameters("@tmpDate").Value = tmpDate
mycommand.CommandText = "Insert_Chart_Assignment"
mcnum = mycommand.ExecuteScalar()
oSQLConn.Close()

txtmedchart.Text = mcnum
Else
lblOutput.Text = "*Some of the required fields are empty"
End If

WHAT AM I MISSING?

Jack of all trades, Master of none!

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-05-17 : 18:33:40

Try this: mycommand.ExecuteNonQuery()

Brenda

If it weren't for you guys, where would I be?

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-17 : 18:58:47

I get the same error.

Jack of all trades, Master of none!

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-05-17 : 20:20:18

[code]
If (Page.IsValid) Then
Dim mycommand As New SqlClient.SqlCommand
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Connection = oSQLConn 'not quite sure where this is comming form, but lets assume it's not NOTHING

oSQLConn.Open()
mycommand.Parameters.add("@tmpFirstName, SQLDbType.VarChar, 50).value = tmpFirstName 'tmpFirstName is that a variable or a text box??

'Do the rest of your params liek the example above.
'pay attention to the datatypes, and lengths.
mcnum = mycommand.ExecuteScalar()
oSQLConn.Close()

txtmedchart.Text = mcnum
Else
lblOutput.Text = "*Some of the required fields are empty"
End If

[/code]

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 11:05:00

quote:

Originally posted by MichaelP


If (Page.IsValid) Then
Dim mycommand As New SqlClient.SqlCommand
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Connection = oSQLConn  'not quite sure where this is comming form, but lets assume it's not NOTHING

oSQLConn.Open()
mycommand.Parameters.add("@tmpFirstName, SQLDbType.VarChar, 50).value = tmpFirstName  'tmpFirstName is that a variable or a text box??

'Do the rest of your params liek the example above.
'pay attention to the datatypes, and lengths.
mcnum = mycommand.ExecuteScalar()
oSQLConn.Close()

txtmedchart.Text = mcnum
Else
lblOutput.Text = "*Some of the required fields are empty"
End If

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

My connection string is right. tmpFirstname is a text box. I just assigned the text box to it.

Jack of all trades, Master of none!

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 11:15:11

I get the same error

Here is what the revised code looks like:

Private Sub btnsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
Dim mcnum As Integer
Dim tmpFirstName As String
Dim tmpLastName As String
Dim tmpMidInit As String
Dim insert_query As String
Dim tmpDOB As String
Dim tmpDate As Date
Dim sqlCommand As SqlCommand
Dim oSQLConn As SqlConnection = New SqlConnection
oSQLConn.ConnectionString = "Data Source=******;" & _
"Initial Catalog=****;" & _
"Integrated Security=SSPI"

If (Page.IsValid) Then
Dim mycommand As New SqlClient.SqlCommand
Dim mybuilder As New SqlClient.SqlCommandBuilder
mycommand.CommandType = CommandType.StoredProcedure
mycommand.Connection = oSQLConn
oSQLConn.Open()
mybuilder.DeriveParameters(mycommand)
mycommand.Parameters.Add("@tmpFirstName", SqlDbType.VarChar, 50).Value = txtfname.Text
mycommand.Parameters.Add("@tmpLastName", SqlDbType.VarChar, 50).Value = txtlname.Text
mycommand.Parameters.Add("@tmpMidInit", SqlDbType.Char, 1).Value = txtmidinit.Text
mycommand.Parameters.Add("@tmpDOB", SqlDbType.DateTime).Value = txtdob.Text
mycommand.Parameters.Add("@tmpDate", SqlDbType.DateTime).Value = DateTime.Now.ToShortDateString()
mycommand.CommandText = "Insert_Chart_Assignment"
mcnum = mycommand.ExecuteNonQuery()
oSQLConn.Close()
txtmedchart.Text = mcnum
lblOutput.Text = ""
Else
lblOutput.Text = "*Some of the required fields are empty"
End If

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 11:34:22

I took out the mybuilder stuff and it got rid of the errors.

This post is resolved!

Jack of all trades, Master of none!

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-05-18 : 12:49:28

Yeah, that's exactly what my example showed you to do.
Follow the Jedi, young Padiwan. :)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SubPar_Coder
Starting Member

23 Posts

Posted - 2005-05-18 : 13:10:20

LOL!

You going to opening night on the 19th?

Jack of all trades, Master of none!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources