Binding data to a ASP textbox - 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

Development Tools

ASP.NET

Binding data to a ASP textbox

Author

Topic

kpeck
Starting Member

13 Posts

Posted - 2005-04-26 : 11:15:56

I have a web app that takes user entered data (in this case a MAC address) calls a stored procedure with output parameters. and is suppoesed to return the switch address and the Slot/Port info from a SQL database. Its not working... Maybe there would be an easier way to do this!?

This is what I've got....

the Function that calls the stored procedure

Public Function GetSwitchInfo(ByVal MyProc As String, ByVal MMAC As String) As Integer

strConn = SQL_CONNECTION_STRING

Dim scnClientUpdate As New SqlConnection(strConn)
Dim scmd As New SqlCommand(MyProc, scnClientUpdate)
Dim sda As New SqlDataAdapter(scmd)
Dim dsSWitchInfo As New DataSet

scmd.CommandType = CommandType.StoredProcedure
With scmd.Parameters
.Add(New SqlParameter("@MMAC", SqlDbType.VarChar, 17)).Value = MMAC
.Add(New SqlParameter("@Switch", SqlDbType.VarChar, 17)).Direction = ParameterDirection.Output
.Add(New SqlParameter("@Port", SqlDbType.VarChar, 17)).Direction = ParameterDirection.Output

End With

Try
sda.Fill(dsSwitchInfo)
DataGrid1.DataSource = dsSwitchInfo.Tables(MyProc).DefaultView
DataGrid1.DataBind()

Catch exp As Exception
strErrorMsg = "Error! SwitchInfo NOT " & _
"updated. Error message: " & exp.Message
Exit Function
End Try

MySwitchInfo = scmd.Parameters("@Switch").Value()

MyPortInfo = scmd.Parameters("@Port").Value()

sda.Dispose()
scnClientUpdate.Close()

End Function

The stored procedure

CREATE PROCEDURE spSwitch

@MMAC varchar(17),
@Switch varchar(17) OUTPUT,
@Port varchar(17) OUTPUT

AS

Select @Switch = Device FROM UserTrackerInfo WHERE (MACAddress = @MMAC)

Select @Port = Port FROM UserTrackerInfo WHERE (MACAddress = @MMAC)

GO

I also tried what I thought would be the simple way.. But even thought it does work, I can't seem to figure out how to bind the results to an ASP textbox.

'Dim mySwitch As SqlCommand = New SqlCommand("SELECT Device as [Switch Address], Port as [Slot/Port] FROM dbo.UserTrackerInfo WHERE (MACAddress = '" & MMAC & "')", myConn)

myConn.Open()
Dim SwitchReader As SqlDataReader = mySwitch.ExecuteReader()
DataGrid1.DataSource = SwitchReader
DataGrid1.DataBind()

myConn.Close()
SwitchReader.Close()

Any help would be appreciated

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-04-27 : 01:45:49

Ummm...Where's the textbox?

And why are you using a datagrid if you are only returning one pair of values? A datagrid is (normally) used when you have a list of values to return. perhaps there is a better way to display this data

This set of statements in your proc:
"Select @Switch = Device FROM UserTrackerInfo WHERE (MACAddress = @MMAC)
Select @Port = Port FROM UserTrackerInfo WHERE (MACAddress = @MMAC)"
This can be re-written as
"Select @Switch = Device, @Port = Port FROM UserTrackerInfo WHERE (MACAddress = @MMAC)"

That's half as many reads from the database!

Your stored proc does not return a resultset, so your dataset will be empty. The values you are looking for are contained in your output parameters. I see you are assigning the values to two variables (" MySwitchInfo = scmd.Parameters("@Switch").Value()") but you are not doing anything with them.

If you want you datagrid binding to work, get rid of the output params and use this select statement in the proc:
"Select Device, Port FROM UserTrackerInfo WHERE (MACAddress = @MMAC)"

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources