pass in or retrieve a computername using vb.net & sql sp

I have two textboxes on a Winform. I want to be able to enter a Computername like "PapaSmurf" into the first textbox, an application name like "adobe" into the second textbox, have it retrieve the data via a DataGrid/DataTable and a Stored Procedure.

My Stored Procedure (I'm using SQL 2005) looks like this:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%Microsoft%' AND WORKSTATION_NAME LIKE '%B001321D14F%'.

It's obvious that I can't use a static Workstation_Name.

It looks like I will need to setup a parameter but how do I set it up so that whatever is in the ComputerName textbox on the form get's passed into the Stored Procedure Parameter or maybe I have it backwards and the SP needs to somehow pull it from the Winform textbox.

Any ideas?

Wallace

You can use parameters to pass in the fields use ADO.net in VB.net

change the stored procedure to:

ALTER PROCEDURE [dbo].[uspSelectAnyPkgName]

@.ComputerName as varchar(50),

@.WorkStation as varchar(50)

AS

SELECT software.NAME, software.version, software.build, software.install_status,

software.install_date, software.package_name, network.workstation_name

FROM platform_validation_tool.dbo.software, platform_validation_tool.dbo.network

WHERE NAME LIKE '%' + @.ComputerName +'%' AND WORKSTATION_NAME LIKE '%' + @.WorkStation + '%'.

Then the ado.net:

Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnection").ToString)

sqlConn.Open()

Try

Dim command As SqlCommand = New SqlCommand("uspSelectAnyPkgName", sqlConn)

command.Parameters.AddWithValue("@.ComputerName", ComputerName.Text)

command.Parameters.AddWithValue("@.WorkStation", WorkStation.Text)

command.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

sqlConn.Close()

sqlConn = Nothing

GC.Collect()

End Try

You'll probably want to use a datareader to populate a grid since you're selecting.

Adamus

|||

Hi Adam,

It appears as if this may work but I left out one important detail. I have no idea on how to write a loop (since I am new to vb.net) in the Stored procedure or in vb.net, which ever it may be, to get for instance, an application name that starts with "KB" (Like as in the Microsoft Knowledge Base) from ComputerName "MYPC".

The "command.ExecuteNonQuery()" gives me the error, "Line 1: Incorrect syntax near 'uspSelectAnyPkgName'".

I used the following SQL DataAdapter and Fill method but it grabbed every software package name and every computer name other than:

SqlDA_SinglePkgName.Fill(AllTablesDataSet1.uspSelectAnyPkgName)

So, it appears that the Fill method or the command.ExecuteNonQuery are not the appropriate methods or I'm not passing something in.

Ideally, I just want the specific software package on one computer to be pulled from the DB.

Your help again would be appreciated.

Thanks,

Wallace

|||

Adamus,

Thank you for your help. This worked great.

Wally