Please take a look at line 18, how can I pass a variable into this instead of hard coding "Ann" in as the parameter.
I am wanting to do something along the following lines:
18. cmdLastName.Parameters.Add( "@.firstname", " & myvariable & " )
1. <%@. Import Namespace="system.Data.SqlClient" %>
2. <%@. Import Namespace="System.Data" %>
3. <%
4. Dim conPubs As SqlConnection
5. Dim cmdLastName as SqlCommand
6. Dim paramLastName As SqlParameter
7. Dim paramphone As SqlParameter
8. Dim strLastName As String
9. Dim phone as string
12. conPubs = New SqlConnection
13. ("server=localhost;uid=WebUserRX;pwd=ViglenWebUser;database=pubs")
14. cmdLastName = New SqlCommand("GetLastName", conPubs)
15. cmdLastName.CommandType = CommandType.StoredProcedure
17. 'FirstName Input Parameter
18. cmdLastName.Parameters.Add( "@.firstname", "Ann" )
'LastName Output Parameter
paramLastName = cmdLastName.Parameters.Add( "@.lastname", SqlDbType.Varchar )
paramphone = cmdLastName.Parameters.Add( "@.phone", SqlDbType.Varchar )
paramLastName.Size = 40
paramLastName.Direction = ParameterDirection.Output
paramphone.Size = 40
paramphone.Direction = ParameterDirection.Output
'Execute Command
conPubs.Open()
cmdLastName.ExecuteNonQuery()
'Retrieve value of Output Parameter
If Not IsDBNull( cmdLastName.Parameters( "@.lastname" ).value ) then
strLastName = cmdLastName.Parameters( "@.lastname" ).value
phone = cmdLastName.Parameters( "@.phone" ).value
else
strLastName = "Unknown"
End if
conPubs.Close
%>
The last name is <%=strLastName%><br>
Phone = <%=phone%
ThanXThere are several ways. I think the easiest to code would be like this (you'd need to use the correct datatype and length):
18. cmdLastName.Parameters.Add("@.firstname", SqlDbType.VarChar,99).Value=myvariableTerri|||Thanks that works but do you know how I can return more than one record, do I need a loop around line 32? I have a dropdownlist which when autoposted sends a variable to a stored procedure, then only one row is returned. What I would like to do is when the dropdown is autoposted all the records (all rows) are returned and put into a datagrid.
When I use SQL within an aspx page I use a datareader and simply bind the result of the SQL to a web control e.g
myDropDownList2.DataSource = myDataReader
myDropDownList2.DataBind()
But via the Stored Procedure method I cannot see how this is achieved. Any code samples would be much appreciated.
1. <%
2. Dim conPubs As SqlConnection
3. Dim cmdLastName as SqlCommand
4. Dim paramLastName As SqlParameter
5. Dim strLastName As String
6. Dim mystr as string
8. mystr = myDropDownList2.SelectedItem.Value
9. Response.write("mystr= " & mystr & "<br>")
11. conPubs = New SqlConnection
12. ("server=marketstore;uid=steve;pwd=;database=Contents")
13. cmdLastName = New SqlCommand("GetLastNames", conPubs)
14. cmdLastName.CommandType = CommandType.StoredProcedure
17. 'FirstName Input Parameter
18. cmdLastName.Parameters.Add("@.firstname", SqlDbType.VarChar,99).Value = mystr
20. 'LastName Output Parameter
21. paramLastName = cmdLastName.Parameters.Add( "@.lastname", SqlDbType.Varchar )
24. paramLastName.Size = 40
25. paramLastName.Direction = ParameterDirection.Output
27. 'Execute Command
28. conPubs.Open()
29. cmdLastName.ExecuteNonQuery()
31. 'Retrieve value of Output Parameter
32. If Not IsDBNull( cmdLastName.Parameters( "@.lastname" ).value ) then
33. strLastName = cmdLastName.Parameters( "@.lastname" ).value
34. else
35. strLastName = "Unknown"
36. End if
37. conPubs.Close
%>|||Sure, you can take the resultset of a stored procedure, put them into a DataReader, and then bind the DataReader to the drop-down list. Replacing your lines 20-37:
20. Dim myReader As SqlClient.SqlDataReader
21. 'Execute Command
22. conPubs.Open()
23. myDataReader = cmdLastName.ExecuteReader()
24. myDropDownList2.DataSource = myDataReader
25. myDropDownList2.DataBind()
26. conPubs.Close
The SELECT statement in your stored procedure would be something like:
SELECT lastname FROM myTable WHERE firstname = @.firstname
where currently you probably have something like this:
SELECT @.lastname = lastname FROM myTable WHERE firstname = @.firstname
You would not use an Output parameter when you are returning more than one row. Instead you would return a set of records.
Terri|||Thanks I have that working now, but now I have a new issue, I am wanting to use a DataAdapter instead of a Datareader because i wish to add paging to my site, do you know the equivalent for 'Parameters' in DataAdapter terms as I wish to pass this variable into my stored proc and I am getting this error message, thanks in advance.
Compiler Error Message: BC30456: 'Parameters' is not a member of 'System.Data.SqlClient.SqlDataAdapter'.
Source Error:
Line 23: Dim paramvar As New SqlParameter
Line 24:
Line 25:
Line 26: paramvar = objDA.Parameters.Add("@.myvar", SqlDbType.VarChar,99).Value = "Steve"
Source File: C:\Inetpub\wwwroot\aspnet\datalistpaging2.aspx Line: 26|||You don't add parameters to a data adapter. You add them to the SQLCommand just as before.
Please review theTutorials section of this site, especially Server Side Data Access, and "Parameterized Selects" within that topic. You should be able to work out what you need for yourself from that.
Here's a stab at your code, but I did not test it.
1. <%
2. Dim conPubs As SqlConnection
3. Dim cmdLastName as SqlCommand
4. Dim paramLastName As SqlParameter
5. Dim strLastName As String
6. Dim mystr as string
8. mystr = myDropDownList2.SelectedItem.Value
9. Response.write("mystr= " & mystr & "<br>")
11. conPubs = New SqlConnection
12. ("server=marketstore;uid=steve;pwd=;database=Contents")
13. cmdLastName = New SqlCommand("GetLastNames", conPubs)
14. cmdLastName.CommandType = CommandType.StoredProcedure
17. 'FirstName Input Parameter
18. cmdLastName.Parameters.Add("@.firstname", SqlDbType.VarChar,99).Value = mystr
20. Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(cmdLastName)
21. Dim myDataSet As DataSet = New DataSet()
22. myDataAdapter.Fill(myDataSet,"LastNames")
23. myDropDownList2.DataSource = myDataSet.Tables("LastName").DefaultView
24. myDropDownList2.DataBind()
25. conPubs.Close
Terri
