Click here to Skip to main content
13,410,621 members (32,659 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
I try to read data from excel sheet for my drop down list using OleDB reader but it did not working. I try to combine the query for insert and select but it just function on INSERT only but for SELECT not. It's there any solution for my problem?

What I have tried:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .auto-style2{
            width:103px;
            text-align:right;
        }


    </style>
</head>
<body>
   <form id="form1" runat="server">
        <div>

            <table  class="auto-style1">
                <tr>
                    <td class="auto-style2">UserNTID:</td>
                    <td>
                        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                     <td class="auto-style2">SerialNumber:</td>
                     <td>
                        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                     </td>
                </tr>
                <tr>
                     <td class="auto-style2">Model:</td>
                    <td>
                        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                        </td>
                </tr>
                <tr>
                     <td class="auto-style2">Department:</td>
                    <td>
                        <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
                        </td>
                </tr>
                
                <tr>
                         <td class="auto-style2">
                             <asp:Label ID="DropDownList1" runat="server" Text="Country"></asp:Label>  
                         </td>
                    <td>
                         <asp:DropDownList 

                                    runat="server" 

                                    AutoPostBack="True"

                                    onselectedindexchanged="DropDownList1_SelectedIndexChanged">
                         <asp:ListItem Text="Select Country" Value="select" Selected="True"></asp:ListItem> 
                         </asp:DropDownList>
                          
                               
                     </td>
                  
                </tr>
            </table>
        </div>
    </form>
</body>
</html>








using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string ConStr = "";
        //getting the path of the file     
        string path = Server.MapPath("Book1.xlsx");
       
        //connection string for that file which extantion is .xlsx    
        ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";
        //making query    
        string query = "INSERT INTO [Sheet1$] ([UserNTID], [SerialNumber], [Model], [Department]) VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "')";
               query += "SELECT * FROM [Sheet1$]([Country]) VALUES('"+ DropDownList1 + "')";
        //Providing connection    
        OleDbConnection conn = new OleDbConnection(ConStr);
        //checking that connection state is closed or not if closed the     
        //open the connection    
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        //create command object    
        OleDbCommand cmd = new OleDbCommand(query, conn);
        int result = cmd.ExecuteNonQuery();
        if (result > 0)
        {
            Response.Write("<script>alert('Sucessfully Registration!')</script>");
        }
        else
        {
            Response.Write("<script>alert('Sorry!\n Register Failed')</script>");
        }
        conn.Close();
    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{

}
}
Posted 8-Nov-17 16:54pm
Updated 9-Nov-17 20:48pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Although your question is not completely clear to us,
at first glance, your SELECT query is wrong.
SELECT * FROM [Sheet1$]([Country]) VALUES('"+ DropDownList1 + "')


Secondly, I assume you want to bind the dropdownlist with data from Country table just for the registration is done. It's bit a weird requirement as usually you may want to bind the dropdownlist at first page load.

So, what you should do?

First of all, remove the SELECT query completely from there. (following line)
query += "SELECT * FROM [Sheet1$]([Country]) VALUES('"+ DropDownList1 + "')";


Think twice about where you want to bind the dropdown, ideally it should be at page_load event & with a check for not IsPostback.

Following lines of code can be used to bind your dropdown with data from Country table.

OleDbConnection conn = new OleDbConnection(ConStr);
if (conn.State == ConnectionState.Closed)
{
     conn.Open();
}
string query = "SELECT Country FROM [Sheet1$]";   
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataReader read = dbc.ExecuteReader();
DropDownList1.DataSource = read;
DropDownList1.DataTextField="Country";
DropDownList1.DataValueField="Country"; 
DropDownList1.DataBind();
read.Close();
if (conn.State == ConnectionState.Opened)
{
     conn.Close();
}


One more concern, you are saving different data sets to Sheet1 of your excel file which doesn't contain Country data as it seems. So, replace with the correct sheet name.

I am not sure if my suggestion will help you much as question was not so clear. In that case please try to explain clearly about the problem and what requirement you are trying to solve by the supplied code.

Hope, it helps.

Thanks!
  Permalink  
Comments
demii 23-Nov-17 2:12am
   
Thank you sir. You save my life!
Suvendu Shekhar Giri 23-Nov-17 3:08am
   
Glad that, it helped :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Check you have given ID of Dropdownlist to Label in your HTML as mentioned below:-

<asp:Label ID="DropDownList1" runat="server" Text="Country"></asp:Label>


Ideally it should be ID of Dropdownlist as you are binding data into it.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.180221.1 | Last Updated 10 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100