Create a grid mixing Excel and SQL Table

Hi!
            Thank you For reading my problem. I use this code To create a grid With a Excel file.

Dim MyConnExcel = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2""")
If MyConnExcel.State = ConnectionState.Closed Then MyConnExcel.Open()
Dim MyCommExcel As OleDbCommand = New OleDbCommand("SELECT SERVERS FROM [Sheet1$]", MyConnExcel)
da = New OleDbDataAdapter(MyCommExcel)
ds = New DataSet()
da.Fill(ds)

grvExcelData.DataSource = ds.Tables(0)
grvExcelData.DataBind()
grvExcelData.Visible = True

da.Dispose()
MyConnExcel.Close()
MyConnExcel.Dispose()

Open in new window

And I use this other code To create a grid from a SQL table
Dim MyConnSQL = New SqlConnection(strConexionBHInv)
If MyConnSQL.State = ConnectionState.Closed Then MyConnSQL.Open()
Dim MyCommSQL As SqlCommand = New SqlCommand("SELECT SERVERS FROM MYSERVERS", MyConnSQL)
da = New SqlDataAdapter(MyCommSQL)
ds = New DataSet()
da.Fill(ds)

grvGridData.DataSource = ds.Tables(0)
grvGridData.DataBind()
grvGridData.Visible = True

da.Dispose()
MyConnSQL.Close()
MyConnSQL.Dispose()

Open in new window

The question Is:
          how I can create a unique grid matching "server" column (programatically)?
          If I don't explain well something like this
 
        
ExcelSheet
==========

SERVERS			IP
sql_srv		192.168.1.1
exchange_srv	192.168.1.2
dc_srv		192.168.1.3


SQL Table:
=========
SERVERS		DOMAIN
sql_srv		MELODY
exchange_srv	MELODY
dc_srv          CUSTOMY




THE GRID THAT I WANT!!!
          
SERVERS		IP			DOMAIN
sql_srv		192.168.1.1		MELODY
exchange_srv	192.168.1.2		MELODY
dc_srv         192.168.1.3		CUSTOMY

Open in new window

ShermanGutiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jagssiduralaCommented:
We have a concept called "relations" between 2 tables of a dataset.

For your requirement you need to follow the below steps.

1) Both results set should have Id column so that we make a relationship on this column.
2) Store excel sheet result set in ds.Tables(0) and sql result set in ds.Tables(1).
3) make a relationship between these tables. refer following link

http://www.dotnetjohn.com/articles.aspx?articleid=63
ShermanGutiAuthor Commented:
Your example is great but how can I make a gridview with the relationship?
afsarsalCommented:
Hi,

You can do that something like this.

You have 2 datasets, one from excel, one from database.

So we should create a new datatable and join this 2 datatables in this new datatable.
I'll consider that excel datatable name is dtExcel, database datatble name is dtDB

           DataTable dtNew = new DataTable();
            dtNew.Columns.Add("SERVERS");
            dtNew.Columns.Add("IP");
            dtNew.Columns.Add("DOMAIN");

            for (int i = 0; i < dtExcel.Rows.Count; i++)
            {
                dtNew.NewRow();
                dtNew.Rows[i]["SERVERS"] = dtExcel.Rows[i]["SERVERS"];
                dtNew.Rows[i]["IP"] = dtExcel.Rows[i]["IP"];

                dtNew.Rows[i]["DOMAIN"] = dtDB.Select("SERVERS=" + dtExcel.Rows[i]["SERVERS"])[0]["DOMAIN"].ToString();
            }

grvGridData.DataSource = dtNew.DefaultView;
grvGridData.DataBind();
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

ShermanGutiAuthor Commented:
Afsarsal:

          I have this error:
                "No row at position 0" in line: dtNew.Rows[i]["SERVERS"] = dtExcel.Rows[i]["SERVERS"];

              what can I do? Thank youu!
ShermanGutiAuthor Commented:
This is my real code:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dtExcel As New DataTable
        Dim dtDB As New DataTable

        Dim objConn1 As New SqlConnection(ConfigurationManager.ConnectionStrings("BHInventarioWEBConnectionString").ConnectionString)
        Dim objCmd1 As New SqlCommand("SELECT * FROM excel_prueba", objConn1)
        objCmd1.CommandType = CommandType.Text
        objConn1.Open()
        Dim DR1 As SqlDataReader = objCmd1.ExecuteReader()
        dtExcel.Load(DR1)
        DR1.Close()


        Dim objConn2 As New SqlConnection(ConfigurationManager.ConnectionStrings("BHInventarioWEBConnectionString").ConnectionString)
        Dim objCmd2 As New SqlCommand("SELECT * FROM BH_Equipos_Activos", objConn2)
        objCmd2.CommandType = CommandType.Text
        objConn2.Open()
        Dim DR2 As SqlDataReader = objCmd2.ExecuteReader()
        dtDB.Load(DR2)
        DR2.Close()

        Dim dtNew As DataTable = New DataTable
        dtNew.Columns.Add("SERVERS")
        dtNew.Columns.Add("IP")
        dtNew.Columns.Add("DOMAIN")
        Dim i As Integer = 0
        Do While (i < dtExcel.Rows.Count)
            dtNew.NewRow()
            dtNew.Rows(i)("SERVERS") = dtExcel.Rows(i)("SERVERS")
            dtNew.Rows(i)("IP") = dtExcel.Rows(i)("IP")
            dtNew.Rows(i)("DOMAIN") = dtDB.Select(("SERVERS=" + dtExcel.Rows(i)("SERVERS")))(0)("DOMAIN").ToString
            i = (i + 1)
        Loop
        grvGridData.DataSource = dtNew.DefaultView
        grvGridData.DataBind()


    End Sub





this gives me:
"No row at position 0" in line: dtNew.Rows[i]["SERVERS"] = dtExcel.Rows[i]["SERVERS"]
ShermanGutiAuthor Commented:
Is there a way to do a INNER JOIN with Datatables 1 and 2?
afsarsalCommented:
Hi,

I guess the problem is, record count of 2 datatables are not equal.
For example dtExcel has 10 recordsi dtDB has 8 records.

So in do while statement
Do While (i < dtExcel.Rows.Count)

at 9. row, it will give you "No row at position 0" error.
So just check if there is a record.

Do While (i < dtExcel.Rows.Count)
IF dtExcel.Rows(i)("SERVERS") is not null THEN
            dtNew.NewRow()
            dtNew.Rows(i)("SERVERS") = dtExcel.Rows(i)("SERVERS")
            dtNew.Rows(i)("IP") = dtExcel.Rows(i)("IP")
            dtNew.Rows(i)("DOMAIN") = dtDB.Select(("SERVERS=" + dtExcel.Rows(i)("SERVERS")))(0)("DOMAIN").ToString
END IF
            i = (i + 1)
        Loop


By the way if dtExcel has 10 records and dtDB has 8 records.
Your do while sould start with dtExcel , vice verse it should start with dtDB

Do While (i < dtExcel.Rows.Count)

or

Do While (i < dtDB.Rows.Count)
ShermanGutiAuthor Commented:
thank you afsarsal! But I still get No row at position 0. This error appears from the beginning of Do while in line dtNew.Rows(i)("SERVERS") = dtExcel.Rows(i)("SERVERS")

Now I'm using Products and Categories from NorthwindDB and I still have this error.

This is the code with norhwindDB

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim objConn As SqlConnection
        Dim da As SqlDataAdapter
        Dim ds As DataSet
        Dim dtExcel As New DataTable
        Dim dtDB As New DataTable

        objConn = New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString)
        da = New SqlDataAdapter("SELECT * FROM Categories", objConn)
        ds = New DataSet()

        objConn.Open()
        da.Fill(ds)
        da.Fill(dtExcel)

        objConn.Close()

        Dim objConn2 As SqlConnection
        Dim da2 As SqlDataAdapter
        Dim ds2 As DataSet

        objConn2 = New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString)
        da2 = New SqlDataAdapter("SELECT * FROM Products", objConn2)
        ds2 = New DataSet()

        objConn2.Open()
        da2.Fill(ds2)
        da.Fill(dtDB)

        Dim dtNew As DataTable = New DataTable
        dtNew.Columns.Add("CategoryID")
        dtNew.Columns.Add("CategoryName")
        dtNew.Columns.Add("Description")
        Dim i As Integer = 0
        Do While (i < dtExcel.Rows.Count)
            If dtExcel.Rows(i)("CategoryID") IsNot Nothing Then
                dtNew.NewRow()
                dtNew.Rows(i)("CategoryID") = dtExcel.Rows(i)("CategoryID")
                dtNew.Rows(i)("CategoryName") = dtExcel.Rows(i)("CategoryName")
                dtNew.Rows(i)("Description") = dtDB.Select(("CategoryID=" + dtExcel.Rows(i)("CategoryID")))(0)("Description").ToString
            End If
            i = (i + 1)
        Loop
        grvGridData.DataSource = dtNew.DefaultView
        grvGridData.DataBind()

    End Sub








THANK YOU A LOT!
afsarsalCommented:
Hi,

Are you sure that there is data in database.

Can you check these pls:

dtExcel.Rows.Count
and
dtDB.Rows.Count
ShermanGutiAuthor Commented:
Yes! It is not 0

Look! I have made this example with northwind.mdb
I've attached the sample on a rar file
ShermanGutiAuthor Commented:
Sorry! I say ZIP file! not rar
WebSite-Example.zip
afsarsalCommented:
Hi again,

Sorry it's my fault, we should declare new row not this way.

Try this pls:

        Do While (i < dtExcel.Rows.Count)
            If dtExcel.Rows(i)("CategoryID") IsNot Nothing Then
                Dim drNew = dtNew.NewRow()
                drNew("CategoryID") = dtExcel.Rows(i)("CategoryID")
                drNew("CategoryName") = dtExcel.Rows(i)("CategoryName")
                drNew("Description") = dtDB.Select(("CategoryID=" + dtExcel.Rows(i)("CategoryID")))(0)("Description").ToString
                dtNew.Rows.Add(drNew)
            End If
            i = (i + 1)
        Loop
ShermanGutiAuthor Commented:
jeje Sorry but now it says Index was outside the bounds of the array

I now my english is very very creepy. I have made an JPG to understand the result that I need and a ZIP file with the code modified.

can you help me a little more?
WebSite-Example-2.zip
Example-of-results.jpg
ShermanGutiAuthor Commented:
Inside the .ZIP I have attached the .JPG image with more resolution to a fine view
afsarsalCommented:
I think the reason of "Index was outside the bounds of the array" error is NULL values in Products table.
There are many CategoryID=NULL in Products. You should check it in your for statement.
Or at the beginning don't get NULL ones.

da2 = New SqlDataAdapter("SELECT * FROM Products WHERE CategoryID is not null", objConn2)
ShermanGutiAuthor Commented:
Ouh mmm i'm trying to do that but... I still get "Index was outside...."
ShermanGutiAuthor Commented:
Bueno pude arreglarlo yo solo
Es raro que nadie en ExpertsExchange pueda haberme dado una mano con esto


<%@ Page Language="VB" AutoEventWireup="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="VB" runat="server">

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim MyConn1 As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("App_Data/Northwind.mdb"))
        Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Products", MyConn1)
        Dim ds As DataSet = New DataSet()
        Dim dtExcel As New DataTable

        MyConn1.Open()
        da.Fill(ds)
        da.Fill(dtExcel)

        grvExcelData.DataSource = ds.Tables(0)
        grvExcelData.DataBind()

        MyConn1.Close()

        Dim MyConn2 As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("App_Data/Northwind.mdb"))
        Dim da2 As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Categories", MyConn2)
        Dim ds2 As DataSet = New DataSet()
        Dim dtDB As New DataTable

        MyConn2.Open()
        da2.Fill(ds2)
        da2.Fill(dtDB)

        grvDB.DataSource = ds2.Tables(0)
        grvDB.DataBind()

        Dim dtNew As DataTable = New DataTable
        dtNew.Columns.Add("CategoryID")
        dtNew.Columns.Add("CategoryName")
        dtNew.Columns.Add("QuantityPerUnit")
        For Each drExcel As DataRow in dtExcel.Rows
            For Each drDB As DataRow in dtDB.Rows
           
                If drExcel("CategoryID").ToString  isnot DBNull.Value and drDB("CategoryID").ToString isnot DBNull.Value and drExcel("CategoryID").ToString = drDB("CategoryID").ToString then

                    Dim drNew = dtNew.NewRow()
                    drNew("CategoryID") = drExcel("CategoryID")
                    drNew("CategoryName") = drDB("Description")
                    drNew("QuantityPerUnit") = drDB("CategoryName")
                    dtNew.Rows.Add(drNew)
                End If
            Next
        Next
       
        grvGridData.DataSource = dtNew.DefaultView
        grvGridData.DataBind()        
       
       

    End Sub


</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Label ID="lblDisplay" Runat="Server" Text="Hello!" />
        <asp:GridView ID="grvGridData" runat="server" AutoGenerateColumns="true" Visible="true" >
        </asp:GridView>      
        <asp:GridView ID="grvExcelData" runat="server" AutoGenerateColumns="true" Visible="true">
        </asp:GridView>
 
        <asp:GridView ID="grvDB" runat="server" AutoGenerateColumns="true" Visible="true">
        </asp:GridView>

    </div>
    </form>
</body>
</html>
ShermanGutiAuthor Commented:
And here the final example with northwind Access database
WebSite-Example.zip

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShermanGutiAuthor Commented:
Yo resolvi mi propio problema.
Nadie en experts exchange me ayudo hasta llegar al objetivo. Solo fueron ayudas parciales.
ShermanGutiAuthor Commented:
was me
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.