how to Use a SQL select for a variable

how to Use a SQL select for a variable

• DavidWSeams

  When I try the above example

  Dim Rate() As DataRow = StrangDataSet.Product.Select("Product_No='300'")
  MessageBox.Show(Rate(0)(0).ToString)

  I get an error message: Index was outside the bounds of the array.
  So I tried MessageBox.Show(Rate.ToString) I get a message box to appear but not with the data I want I get messagebox that sayes "Strang.StrangDataSet+ProductRow[]"

  I like this method if I can get it working but will I be able to do something Select "Price" Where "Product_No='300'"

  Thanks,

  Terry

  
  

• bradman

  hi,

  when you search in a column that string type you use "columnName = 'string value'" but when you search in numeric typed column you use "columnName = value"

  you get this error because the select method returned nothing

  in sqlstatment you use it like that " select [columnName] from [tableName] where [condition] orderby [columnName for sorting]"

  in the dataview you use ("tableName","condition","columnName for sorting",dataviewRowState)

  its the same actualy but why you want to retrieve data that allready exist in your form if you are insist to do that i don't know your database type

  so i'll write the code to retrieve data from MsAccess DAtabase

  you first set the connection, then set the sql statment, build a command to use both the sqlstring and the connection string, use datareader to execute the command something like this

  Dim dbconn as oledbconnection
  Dim dbcomm as oledbcommand
  Dim dbreader as oledbdatareader
  dbconn = new oledbconnection("provider=microsoft.jet.oledb.4.0;data source=c:\DataBases\ecommerce.mdb")
  dbconn.open()
  sqlstr = "select * from products where price = 50 order by itemnumber"
  dbcomm = new oledbcommand(sqlstr, dbconn)
  dbreader = dbcomm.executereader()

  while dbreader.read()

  variable = dbreader("columnName")

  'continue reading the rest of columns in the row

  end while
  dbreader.close()
  dbconn.close()

  if you use sql server you have to chane the connection string check this link for more information

  http://msdn.microsoft.com/vbasic/reference/data/default.aspx pull=/library/en-us/dnsql90/html/mandataaccess.asp#mandataac_topic5
  

  hope this helps

  
  
  

• Alain de la Kethulle

  Could you be a little more specific I am just not sure what you are talking about, I also need to do this for about 25 different items would this still be the best way

  Thanks,

  Terry

  
  

• littlePT

  hi,

  first if you have data exist in a dataset you don't need to retrieve it again from  your database

  you have many options , you can use select method to return an array of datarows

          Dim dr() As DataRow = MyDataTable.Select("Column2 = 'otherthing'")
          MessageBox.Show(dr(0)(0).ToString)

  in the messagebox the first index isthe index of the datarow array the second is the index of the columns in the datarow

  or you can use dataview to filter your table its a special form of the table that you can filter or sort your data, the dataview will give you the same ability that you have in SQL statment but againest the dataset not the database, you can search for numeric value, string value , even wildcard search,  bindingsource depend on it,

   it will be something like that

  Dim myDv As New DataView(MyDataSet.Tables("mytableName"), "ColumNameToFilterByitsValue = 'stringvalue' and MyotherColumnNameToFilterByitsValue like '%wildcardfilter%' and MyothercolumNameToFilterByItsValue < intValue", "MyColumnNameThatIuseToSortBy", DataViewRowState.CurrentRows)

  hope this helps

  
  
  

• Raven25

  You need to create a DataView, and set the RowFilter value similar to your WHERE clause.

  
  
• how to Use a SQL select for a variable