Default. aspx
Copy codeThe Code is as follows:
<% @ Page Language = "C #" AutoEventWireup = "true" CodeFile = "AccessToSQL. aspx. cs" Inherits = "AccessToSQL" %>
<! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> No title page </title>
<Style type = "text/css">
. Style1
{
Height: 16px;
}
. Style3
{
Height: 23px;
}
</Style>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>
</Div>
<Table align = "center" border = "1" bordercolor = "honeydew" cellpadding = "0"
Cellspacing = "0">
<Tr>
<Td colspan = "2"
Style = "FONT-SIZE: 9pt; COLOR: # ffffff; HEIGHT: 16px; BACKGROUND-COLOR: # ff9933; TEXT-ALIGN: center">
Write Data from the Access database to the SQL Server database </td>
</Tr>
<Tr>
<Td style = "BACKGROUND-COLOR: # ffffcc; TEXT-ALIGN: center">
<Asp: GridView ID = "GridView2" runat = "server" CellPadding = "4" ForeColor = "#333333"
GridLines = "None" style = "font-size: small" Width = "331px">
<FooterStyle BackColor = "#990000" Font-Bold = "True" ForeColor = "White"/>
<RowStyle BackColor = "# FFFBD6" ForeColor = "#333333"/>
<PagerStyle BackColor = "# FFCC66" ForeColor = "#333333" HorizontalAlign = "Center"/>
<SelectedRowStyle BackColor = "# FFCC66" Font-Bold = "True" ForeColor = "Navy"/>
<HeaderStyle BackColor = "#990000" Font-Bold = "True" ForeColor = "White"/>
<AlternatingRowStyle BackColor = "White"/>
</Asp: GridView>
</Td>
<Td style = "WIDTH: pixel PX; BACKGROUND-COLOR: # ffffcc; TEXT-ALIGN: center">
<Asp: GridView ID = "GridView1" runat = "server" CellPadding = "4" Font-Size = "9pt"
ForeColor = "#333333" GridLines = "None" Width = "228px">
<FooterStyle BackColor = "#990000" Font-Bold = "True" ForeColor = "White"/>
<RowStyle BackColor = "# FFFBD6" ForeColor = "#333333"/>
<SelectedRowStyle BackColor = "# FFCC66" Font-Bold = "True" ForeColor = "Navy"/>
<PagerStyle BackColor = "# FFCC66" ForeColor = "#333333" HorizontalAlign = "Center"/>
<HeaderStyle BackColor = "#990000" Font-Bold = "True" ForeColor = "White"/>
<AlternatingRowStyle BackColor = "White"/>
</Asp: GridView>
</Td>
</Tr>
<Tr>
<Td style = "HEIGHT: 23px; BACKGROUND-COLOR: # ff9900; TEXT-ALIGN: center"
Valign = "top">
<Asp: Button ID = "Button3" runat = "server" Font-Size = "9pt" onclick = "button#click"
Text = "Access data is written into SQL database"/>
<Asp: Label ID = "Label1" runat = "server" Text = "Label" Visible = "False"
Style = "font-size: x-small"> </asp: Label>
</Td>
<Td style = "WIDTH: pixel PX; HEIGHT: 23px; BACKGROUND-COLOR: # ff9900; TEXT-ALIGN: center">
<Asp: Button ID = "Button2" runat = "server" Font-Size = "9pt" onclick = "Button2_Click"
Text = "display imported data in SQL database"/>
</Td>
</Tr>
</Table>
</Form>
</Body>
</Html>
Default. aspx. cs
Copy codeThe Code is as follows:
Using System;
Using System. Collections;
Using System. Configuration;
Using System. Data;
Using System. Linq;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. HtmlControls;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Xml. Linq;
Using System. Data. OleDb;
Using System. Data. SqlClient;
Public partial class AccessToSQL: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{
AccessLoadData ();
}
}
Public OleDbConnection CreateCon ()
{
String strconn = "Provider = Microsoft. Jet. OLEDB.4.0; Data source =" + Server. MapPath ("UserScore. mdb") + "; User Id = admin; Password = ;";
OleDbConnection odbc = new OleDbConnection (strconn );
Return odbc;
}
Public SqlConnection CreateSQLCon ()
{
String sqlcon = ConfigurationSettings. receivettings ["strCon"];
SqlConnection mycon = new SqlConnection (sqlcon );
Return mycon;
}
Protected void button#click (object sender, EventArgs e)
{
String SQL = "";
OleDbConnection con = CreateCon (); // create a database connection
Con. Open ();
DataSet ds = new DataSet (); // create a DataSet
SQL = "select * from Score ";
OleDbDataAdapter myCommand = new OleDbDataAdapter (SQL, con); // create a data adapter
MyCommand. Fill (ds, "Score ");
MyCommand. Dispose ();
DataTable DT = ds. Tables ["Score"];
Con. Close ();
MyCommand. Dispose ();
For (int j = 0; j <DT. Rows. Count; j ++) // obtain the corresponding information from the data in the circular ACCESS.
{
String sqlstr = "";
String ID = DT. Rows [j] [0]. ToString ();
String UserName = DT. Rows [j] [1]. ToString ();
String PaperName = DT. Rows [j] [2]. ToString ();
String UserScore = DT. Rows [j] [3]. ToString ();
String ExamTime = DT. Rows [j] [4]. ToString ();
String selsql = "select count (*) from AccessToSQL where user name = '" + UserName + "'";
If (ExScalar (selsql)> 0) // determines whether the data has been added
{
Label1.Visible = true;
Label1.Text = "<script language = javascript> alert ('the data in this Access database has been imported into the SQL database! '); Location = 'accesssql. aspx'; </script> ";
}
Else
{
String AccessPath = Server. MapPath ("UserScore. mdb"); // obtain the ACCESS database path
// Use the OPENROWSET function to access all the connection information required for remote data in the ole db Data Source
Sqlstr = "insert into AccessToSQL (ID, user name, exam, score, exam time) Values ('" + ID + "', '" + UserName + "', '"+ PaperName +"', '"+ UserScore +"', '"+ ExamTime + "')";
Sqlstr + = "select * from openrowset ('Microsoft. Jet. OLEDB.4.0 ','" + AccessPath + "'; 'admin';'', Score )";
SqlConnection conn = CreateSQLCon ();
Conn. Open ();
SqlCommand mycom = new SqlCommand (sqlstr, conn );
Mycom. ExecuteNonQuery (); // execute the add operation
If (j = DT. Rows. Count-1)
{
Label1.Visible = true;
Label1.Text = "<script language = javascript> alert ('data imported successfully. '); location = 'accessstosql. aspx'; </script> ";
}
Else
{
Label1.Visible = true;
Label1.Text = "<script language = javascript> alert ('data import failed. '); location = 'accessstosql. aspx'; </script> ";
}
Conn. Close ();
}
}
}
Public void AccessLoadData ()
{
OleDbConnection myConn = CreateCon ();
MyConn. Open (); // Open the data link to obtain a dataset.
DataSet myDataSet = new DataSet (); // create a DataSet object
String StrSql = "select * from Score ";
OleDbDataAdapter myCommand = new OleDbDataAdapter (StrSql, myConn );
MyCommand. Fill (myDataSet, "Score ");
GridView2.DataSource = myDataSet;
GridView2.DataBind ();
MyConn. Close ();
}
Public int ExScalar (string SQL)
{
SqlConnection conn = CreateSQLCon ();
Conn. Open ();
SqlCommand com = new SqlCommand (SQL, conn );
Return Convert. ToInt32 (com. ExecuteScalar ());
Conn. Close ();
}
Protected void Button2_Click (object sender, EventArgs e)
{
String sqlstr = "select * from AccessToSQL ";
SqlConnection conn = CreateSQLCon ();
Conn. Open ();
SqlCommand mycom = new SqlCommand (sqlstr, conn );
SqlDataReader dr = mycom. ExecuteReader ();
Dr. Read ();
If (dr. HasRows)
{
GetDataSet (sqlstr );
}
Else
{
Label1.Visible = true;
Label1.Text = "<script language = javascript> alert ('the database has no data information. Please import it first and then query it! '); Location = 'accesssql. aspx'; </script> ";
}
Dr. Close ();
Conn. Close ();
}
Public DataSet GetDataSet (string sqlstr)
{
SqlConnection conn = CreateSQLCon ();
SqlDataAdapter myda = new SqlDataAdapter (sqlstr, conn );
DataSet ds = new DataSet ();
Myda. Fill (ds );
GridView1.DataSource = ds;
GridView1.DataBind ();
Return ds;
}
}
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
