[Rohella]

Hi guys,
once one problem is solved a new one enters. I am trying to do a simple query of selecting an ID number from one table and matching it with the ID number in the other table, however the catch is the two tables are stored on two different sql databases.

I have filled a Gridview

Dim sqlconn As New SqlConnection(My.Settings.ConnectionString)
                sqlconn.Open()
        Dim command As New SqlCommand("SELECT InvoiceNumber, NetTotal FROM Invoices", sqlconn)
        Dim da As New SqlDataAdapter(command)

        Dim ds As New DataSet
        da.Fill(ds)
        GridInvoice.DataSource = ds
        GridInvoice.DataBind()
        sqlconn.Close()

This post has been edited by modi123_1: 18 November 2010 - 12:02 PM
Reason for edit:: added code tags

[Rohella]

Ooops sorry, accidently pressed enter.

I have filled a grid view like the above (sorry I didnt click on the tags icon to highlight the code). Now I would like to return the Invoice details (stored on table 1 database 1) according to the GroupNo (stored on table 1 Database1 and table 1 databse 2). I know how to write it in sql but just dont know how to establish a connection between the two in vb.net.

Thank you so much
x

[Shadar]

OK...This depends on how you are doing it. Are you already loading the entire table of the second database into memory or do you want to query the table based off of what is selected in the datagridview.

If you are only selecting one record at a time, and NOT loading the second table into memory, you don't need to establish a relationship.
Since you already know how to write the SQL, it is simply a matter of getting the group number of the record selected in the datagridview, which would look something like this.
There are several ways to do this, I usually assign the value to the variable in the rowenter just because it is easier.

Private Sub dgv_RowEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv.RowEnter 

Dim strCaseID As Integer
Dim strSQL as String

strCaseID = dgv.Rows(e.rowindex).Cells("FieldName").Value
strSQL = "Select * from DB2Table where FieldName = " & strCaseID

end sub

Note I am not checking to ensure a value is there. If it can be null you need to check for that prior to trying to assign it to a variable. I am just getting the value, assigning the value to a variable and then concatenating it on the end of my SQL statement to be passed into the DB.

This post has been edited by Shadar: 18 November 2010 - 07:22 AM

[Rohella]

I do have Tble 1 Db2 'Group No' filled in a gridview already so what your saying is I can match a row from that gridview to the other gridview which has the Tble 1 db1 data ('Group No')? Thats a much better solution than to establish a relationship using SQL. There is one problem though, the "RowIndex", "Cell" part of the code does not work, the option does not appear in vb.net. Is there an alternative? Im using ASP.NET in VB, Microsoft Visual Studio 2008.

Thank you for your help

[Shadar]

dgv.CurrentRow.Cells("field").Value

This post has been edited by Shadar: 19 November 2010 - 10:04 AM

[Rohella]

Hi Guys,
I solved this one, all you have to do is add the other database name in the sql statement, like so:

SELECT     Database1.dbo.TableName.ColumnName, Database2TableName.Name, 'The reason why Database 2 isnt defined is the fact that it has been defined in the connection
 
                      FROM  Database2TableName INNER JOIN
                      Database2TableName2 INNER JOIN
                      WHERE     (Database1.dbo.TableName.ColumnName = '') 

Cheers Guys

Keep the posts coming
x