Search and retrieve data from an Sql Server table

[malg]

Could anyone advise the code to achieve the following: I have an SQL Server table called Product, which includes the following 4 fields:
ProductID
ProductName
ProductSize
ProductQuantity

I know the ProductName, and I want to extract the data from the other 3 columns of the same row.

Let’s assume the connection is still open (I have the following code ready to go):
Dim conn As New SqlConnection(MyConnectionString)
Dim strSql As String = ""
Dim Create1Row As SqlCommand = New SqlCommand(strSql, conn)

Thanks

 

[Bob Langlade]

Since you only want to extract data we don't need an SqlDataAdapter and you can use an SqlDataReader :

[SIZE=2]

[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] GetProductByID([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] ProductID [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])[/SIZE][INDENT][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Connection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DA [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter
Connection.ConnectionString = [/SIZE][SIZE=2][COLOR=#800000]"Data Source=ServerName;Initial Catalog=Northwind;Persist Security Info=True;User ID=<user name>;Password=<your password>"
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try

[/COLOR][/SIZE][SIZE=2][INDENT]Connection.Open()

[/INDENT][/SIZE][INDENT][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand([/SIZE][SIZE=2][COLOR=#800000]"SELECT Productname, Productsize, ProductQuantity from Products"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#800000]"WHERE ProductID=@ProductID"[/COLOR][/SIZE][SIZE=2], Connection)
MyCommand.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#800000]"@ProductID"[/COLOR][/SIZE][SIZE=2], ProductID)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyReader [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataReader = MyCommand.ExecuteReader
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] MyReader.Read [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Assumes your ProductID is unique

[/COLOR][/SIZE][SIZE=2][INDENT]Console.WriteLine(MyReader(0)) [/INDENT][/SIZE][INDENT][SIZE=2][COLOR=#008000]'Product Name
[/COLOR][/SIZE][SIZE=2]Console.WriteLine(MyReader(1)) [/SIZE][SIZE=2][COLOR=#008000]'Product Size
[/COLOR][/SIZE][SIZE=2]Console.WriteLine(MyReader(2)) [/SIZE][SIZE=2][COLOR=#008000]'Product Quantity
[/COLOR][/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]Else

[/COLOR][/SIZE][SIZE=2][INDENT]Console.WriteLine([/INDENT][/SIZE][INDENT][SIZE=2][COLOR=#800000]"Product not found"[/COLOR][/SIZE][SIZE=2])
[/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[INDENT]MessageBox.Show(ex.Message)

[/INDENT][/SIZE][SIZE=2][COLOR=#0000ff]Finally

[/COLOR][/SIZE][SIZE=2][INDENT]Connection.Close()

[/INDENT][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

 

[malg]

Thanks

Thanks Bob

I will give a try as soon as I can.

 

[Bob Langlade]

Just noticed that I had left a line to create an SqlDataAdapter instance that is no use (second line in the sub).