new post one

New_post

Upload Excel Sheet data to Sql Server in Asp.net

protected void btnUpload_Click(object sender, EventArgs e)
{
        if (FileUpload1.HasFile)
        {
            string sPath = Server.MapPath("~/BulkFolder/" + FileUpload1.FileName);
            FileUpload1.SaveAs(sPath);

            ImporttoSQL(sPath);
        }
}


private void ImporttoSQL(string sPath)
    {
        try
        {
       
            string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);

            string sDestConstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
            using (sSourceConnection)
            {
                string sql = string.Format("Select [Sno],[Subject],[Question],[op1],[op2],[op3],[op4],[Answer] FROM [{0}]", "Sheet1$");
                OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
                sSourceConnection.Open();
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
                    {
                        bulkCopy.DestinationTableName = "Questions";
                       
                        bulkCopy.WriteToServer(dr);
                        Label1.Visible = true;
                    }
                }
            }
        }
        catch (Exception y)
        {
            Label1.Visible = true;
            Label2.Visible = true;
            Label3.Visible = true;
            Label1.Text = "Cannot upload the quetions. Please check that";
            Label2.Text = "1. All the values are present. No column is missing.";
            Label3.Text = "2. Answer is in the numeric value or file format is wrong.";
        }

    }

Html Code


   
   

   

 
               

               
                    
                              

                                   Question storage

                              
                                                                          NavigateUrl="~/Faculty/Default.aspx">Home
                              

    Formats allowed

       

                  
           

               
• File format with 2003.

               
• File format with 2007.

                                          
• File format with 2010.

           

 
       

       

   

   
          
   
       

                    Visible="False">
   

       

                                  
       

                                  
       

       

                        
       

   

Output

Student Registration in Android

A simple Student Registration with SQLite

Here we are going to create a simple Student registration Android Application using SQLite database.

 

Here is the output which we will achieve at the end.

 

 

 MainActivity.java

import android.app.Activity;

import android.app.AlertDialog.Builder;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

import android.widget.Toast;

public class MainActivity extends Activity implements OnClickListener{

    EditText Rollno,Name,Course;

    Button Add,Deletee,Update,View,ViewAll;

    SQLiteDatabase db;

   

    @Override

        protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.activity_main);

       

        Rollno=(EditText)findViewById(R.id.Rollno);

        Name=(EditText)findViewById(R.id.Name);

        Course=(EditText)findViewById(R.id.course);

        Add=(Button)findViewById(R.id.Add);

        Deletee=(Button)findViewById(R.id.Deletee);

        Update=(Button)findViewById(R.id.update);

        View=(Button)findViewById(R.id.View);

        ViewAll=(Button)findViewById(R.id.ViewAll);

       

        Add.setOnClickListener(this);

        Deletee.setOnClickListener(this);

        Update.setOnClickListener(this);

        View.setOnClickListener(this);

        ViewAll.setOnClickListener(this);

     

       

        db=openOrCreateDatabase("mydatabase", Context.MODE_PRIVATE, null);

        db.execSQL("CREATE TABLE IF NOT EXISTS mystudents(rollno VARCHAR,name VARCHAR,Course VARCHAR);");       

    }

   

    @Override

    public void onClick(android.view.View arg0) {

        if(arg0==Add)

        {

           

            if(Rollno.getText().toString().length()==0)

            {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter Roll Number", Toast.LENGTH_SHORT);

                    t.show();

            }

            else if(Name.getText().toString().length()==0)

                {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter Name", Toast.LENGTH_SHORT);

                    t.show();

                }

            else if(Course.getText().toString().length()==0)

                {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter Course", Toast.LENGTH_SHORT);

                    t.show();

                }

            else

                {

                db.execSQL("INSERT INTO mystudents VALUES('"+Rollno.getText()+"','"+Name.getText()+

                     "','"+Course.getText()+"');");

           

                    Toast t = Toast.makeText(getApplicationContext(), "Added Sucessfully", Toast.LENGTH_SHORT);

                      t.show();

                      Rollno.setText("");

                    Name.setText("");

                    Course.setText("");

                    Rollno.requestFocus();

            }

           

        }

               

    if(arg0==Deletee)

        {

            if(Rollno.getText().toString().length()==0)

            {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter Roll Number", Toast.LENGTH_SHORT);

                t.show();

            }

            Cursor c=db.rawQuery("SELECT * FROM mystudents WHERE rollno='"+Rollno.getText()+"'", null);

            if(c.moveToFirst())

            {

                db.execSQL("DELETE FROM mystudents WHERE rollno='"+Rollno.getText()+"'");

                Toast t = Toast.makeText(getApplicationContext(), "Deleted Sucessfully", Toast.LENGTH_SHORT);

                t.show();

                Rollno.setText("");

                Name.setText("");

                Course.setText("");

                Rollno.requestFocus();

            }

            else

            {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter correct Roll Number", Toast.LENGTH_SHORT);

                t.show();

            }

           

        }

       

    if(arg0==Update)

        {

            if(Rollno.getText().toString().length()==0)

            {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter Roll Number", Toast.LENGTH_SHORT);

                t.show();

                return;

            }

            Cursor c=db.rawQuery("SELECT * FROM mystudents WHERE rollno='"+Rollno.getText()+"'", null);

            if(c.moveToFirst())

            {

                db.execSQL("UPDATE mystudents SET name='"+Name.getText()+"',Course='"+Course.getText()+

                        "' WHERE rollno='"+Rollno.getText()+"'");

                Toast t = Toast.makeText(getApplicationContext(), "Updated Sucessfully", Toast.LENGTH_SHORT);

                t.show();

            }

            else

            {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter correct Roll Number", Toast.LENGTH_SHORT);

                t.show();

            }

           

        }

       

    if(arg0==ViewAll)

        {

            Cursor c=db.rawQuery("SELECT * FROM mystudents", null);

            if(c.getCount()==0)

            {

                Toast t = Toast.makeText(getApplicationContext(), "No Record", Toast.LENGTH_SHORT);

                t.show();

                return;

            }

            StringBuffer buffer=new StringBuffer();

            while(c.moveToNext())

            {

                buffer.append("Rollno: "+c.getString(0)+"\n");

                buffer.append("Name: "+c.getString(1)+"\n");

                buffer.append("Course: "+c.getString(2)+"\n\n");

            }

            ShowAll("Student Details", buffer.toString());

        }

    if(arg0==View)

        {

            if(Rollno.getText().toString().length()==0)

            {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter Roll Number", Toast.LENGTH_SHORT);

                t.show();

                return;

            }

            Cursor c=db.rawQuery("SELECT * FROM mystudents WHERE rollno='"+Rollno.getText()+"'", null);

            if(c.moveToFirst())

            {

                Name.setText(c.getString(1));

                Course.setText(c.getString(2));

            }

            else

            {

                Toast t = Toast.makeText(getApplicationContext(), "Please enter correct Roll Number", Toast.LENGTH_SHORT);

                t.show();

            }

        }

       

    }

     public void ShowAll(String t,String m)

        {

            Builder b=new Builder(this);

            b.setCancelable(true);

            b.setTitle(t);

            b.setMessage(m);

            b.show();

        }  

}

 Strings.Xml

    StudentRegistration
    Settings
    Hello world!   
    Rollno :
    Name :
    Course :
    Add
    Delete
    Update
    View
    View All
    Show Information

activity_main.Xml

             android:id="@+id/myLayout"
             android:stretchColumns="0"
             android:layout_width="fill_parent"
             android:layout_height="fill_parent">
                          android:layout_x="30dp"
                  android:layout_y="50dp"
                  android:layout_width="wrap_content"
                  android:layout_height="wrap_content"/>
                          android:inputType="number"   
                  android:layout_x="150dp"
                  android:layout_y="50dp"
                  android:layout_width="150dp"
                  android:layout_height="40dp"/>
                          android:layout_x="30dp"
                  android:layout_y="100dp"
                  android:layout_width="wrap_content"
                  android:layout_height="wrap_content"/>
                          android:inputType="text"   
                  android:layout_x="150dp"
                  android:layout_y="100dp"
                  android:layout_width="150dp"
                  android:layout_height="40dp"/>
                          android:layout_x="30dp"
                  android:layout_y="150dp"
                  android:layout_width="wrap_content"
                  android:layout_height="wrap_content"/>

                    android:id="@+id/course"
            android:layout_width="150dp"
            android:layout_height="40dp"
            android:layout_x="150dp"
            android:layout_y="150dp"
            android:inputType="text" />