Hi everyone,
I am new to VB .Net 2005. I can setup the forms and connect the database using the datagrid view. But I can not find any books or code they will explain how to do a query.
I need HELP on setting up the code for a Database table with four fields. 1. Part Number, 2. dimension 1 3. dimension 2 4. Material. I have a form with a search button and a textbox to enter the Part number I want to query. If I want to search the DB for all part numbers that are 1000, I want it to query the DB place the Part number, Dimension 1 and Dimension 2 in the textboxes and then fill in the Datagrid view with all the part numbers that are 1000. The only difference is in the datagrid view for material field. All Part number with 1000 will have the same Dimensions.  

Example:

Part number: 1000
    Dimension: 12    x   10

Part number         Dimension 1      Dimension 2         Material
   1000                  12                     10                      Copper
   1000                  12                     10                       Brass
   1000                  12                     10                      Tin  

Can someone HELP ME with this

____________________________
jose diego

Hi fuchs,

Check this out....


Protected Sub Button1_Click _
(ByVal sender As Object, ByVal e As System.EventArgs)

Dim sConnString As String = “Provider= SQLOLEDB.1;” & _
Data Source=andreavb.ipowermysql.com;uid=sa;pwd=;Initial Catalog=northwind;”
Dim myConn As New OleDbConnection()
myConn.ConnectionString = sConnString
myConn.Open()

Dim sqlStatement As String
sqlStatement = “SELECT * FROM Products where field1 = expression and field2 ='” & me.texbox1.text & "'"

Dim myComm As OleDbCommand = New OleDbCommand(sqlStatement, myConn)

Dim myReader As oleDbDataReader = Nothing
Dim dsCommand As New OleDbDataAdapter()

dsCommand.SelectCommand = myComm
Dim dsNWind As New DataSet()
dsCommand.Fill(dsNWind, “Products”)
MsgBox(dsNWind.Tables.Products.Rows(0).ProductName.ToString)
Dim drProduct As DataRow
For Each drProduct In dsNWind.Tables.Item(“Products”).Rows
MsgBox(drProduct.Item(“ProductName”).ToString)
Exit For
Next
End Sub


Wish you all the best.

SM

Thanksss for ther reply.....
Sorry but I see I left out one important part...
The Database I and trying to connect and query to is a Foxpro database on may hard drive c:\DB\dietrans.dbf.

The database has several fields in it.
I want to query the DB, pull out the Part_No, the Dimen1, and Dimen2 into TxtPartNo, TxtDimen1, and TxtDimen2 textboxes on the form. Then populate the datgridview with only the information that relates to the PartNo I searched.
I hope this helps...

Thankss Again.....

____________________________
jose diego

Al right, let me start from beginning.

Here is the connection string for VFoxpro

• OLE DB, OleDbConnection (.NET)


o  Database container (.DBC):
"Provider=vfpoledb.1;Data Source=C:\MyDbFolder\MyDbContainer.dbc;Collating Sequence=machine"

o Free table directory:
"Provider=vfpoledb.1;Data Source=C:\MyDataDirectory\;Collating Sequence=general"

o Force the provider to use an ODBC DSN:
"Provider=vfpoledb.1;DSN=MyDSN"
Read more (Microsoft msdn) >>

• ODBC
o Database container (.DBC):
"Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\myvfpdb.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO"

o Free Table directory:
"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\myvfpdbfolder;Exclusive=No;Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO"
"Collate=Machine" is the default setting, for other settings check the list of supported collating sequences >>

========

dim sCnn as string = <enter your connection string here>
Public cnn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCnn)

    Public cmd As New OleDbCommand
    Public da As New OleDb.OleDbDataAdapter
    Public ds As New DataSet
    Public dt As New DataTable

2. use below query command under button_click event as follows:

dim lssql as string
lsSql = "select * from <tablename> where <field1> = '" & Me.combo1.Text & "'

dataDisplayOnGrid(lsSql)

====

    Sub dataDisplayOnGrid(ByVal sSql As String)
        Dim ds As New DataSet
        If cnn.State <> ConnectionState.Open Then
            cnn.Open()
        End If
        cmd = cnn.CreateCommand
        cmd.CommandText = sSql
        Try
            cmd.ExecuteNonQuery()
            da.SelectCommand = cmd
            da.Fill(ds, sSql)
            dt = ds.Tables.Item(sSql)
            Me.DataGridView1.DataSource = ds.Tables(0)
            cnn.Close()
        Catch ex As Exception
            MsgBox(Err.Description & ", ssql = " & sSql)
        End Try
    End Sub


====



Wish you happy programming.


regards,

Shahid Mojid