Access Databases and AccessDataSource Control

Database and .net pages on separate servers?

2013-06-18T20:49:53.27-04:00

Hi there,

We are looking to update our websites to asp.net and to store the database on a separate server from the web files. The database will be access for now and sql server eventually. How can you connect to an access database on a separate server? Is storing the database on a separate server a good or a bad idea, performance wise?

Thanks in advance!

"Data type mismatch in criteria expression."

2013-06-14T10:35:18.45-04:00

Hi all,

I am using Ms Access and below is my Query.

string strSql = "SELECT * from travelapproval  WHERE Format([datefrom1], mm/dd/yyyy hh:nn:ss) <=  Date()   AND Format([dateto1], mm/dd/yyyy hh:nn:ss) >=  Date()  and approval=?";

I am using datefrom1 and dateto1 are text type. It shows

"Data type mismatch in criteria expression."

Below is my Ms Access table.

How can I do that?

Getting Access data behind the scenes.

2013-06-15T08:32:43.957-04:00

VS 2007, C# (ASP.NET). Product database, fields ProdID / email.

Product page has the productID which is carried over to the email page.

On email page a note can be added to an autogenerated email.

Problem, getting the email address from the product db without the user seeing it.

on the page is an access source to the table in question.

code behind that is not working:

protected void Page_Load(object sender, EventArgs e)

{

//string RetrievedValue;

string ItemNumber = Request.QueryString["ProductID"];

this.txtSubject.Text = "Product # " + Request.QueryString["ProductID"];

//RetrievedValue = " Product # " + this.txtSubject.Text ;

}

void MailTest_Click(object sender, EventArgs e)

{

DataView dv = AccessDataSource1.Select(DataSourceSelectArguments.Empty) ;

string empno = dt.ItemEmailReply.ToString();

string sTo = empno;

//string sTo = '<%# Eval("ItemNum") %>';

//<asp:Label ID="ItemNumLabel" runat="server" Text='<%# Eval("ItemEmailReply") %>' />

string sFrom = txtFrom.Text.Trim();

string sSubject = txtSubject.Text.Trim();

string sBody = txtBody.Text.Trim();

string sMailServer = "nnn.n.n.n";

MailMessage MyMail = new MailMessage();

MyMail.From = sFrom;

MyMail.To = sTo;

MyMail.Subject = sSubject;

MyMail.Body = sBody;

MyMail.BodyEncoding = Encoding.UTF8;

MyMail.BodyFormat = MailFormat.Text;

Inserting DateTime into Acess Database via C# and Asp.net

2011-09-03T05:41:02.86-04:00

This might be a very common and old problem, but still I've struggled with quite a lot and finally I'm posting it here:

I've to insert DateTime in an Access database, but I'm having trouble doing so. My C# code:

protected void Button5_Click(object sender, EventArgs e)
    {
        Panel1.Visible = true;
        Panel2.Visible = false;


        String eventname =
            TextBox6.Text;
        String description = TextBox7.Text;

             String coord = TextBox8.Text;

             String venue = TextBox9.Text;

             String time = TextBox10.Text;

             TextBox6.Text = "";
             TextBox7.Text = "";
             TextBox8.Text = "";
             TextBox9.Text = "";
             TextBox10.Text = "";


        AccessDataSource1.InsertParameters["eventname1"].DefaultValue
            = eventname;
        AccessDataSource1.InsertParameters["description1"].DefaultValue
            = description;
        AccessDataSource1.InsertParameters["coordinator1"].DefaultValue
            = coord;
        AccessDataSource1.InsertParameters["venue1"].DefaultValue
            = venue;
        AccessDataSource1.InsertParameters["time1"].DefaultValue
            = time;
        AccessDataSource1.InsertParameters["time1"].DefaultValue
           = time;
        AccessDataSource1.Insert();

        DataList1.EditItemIndex = -1;
        DataList1.DataBind();
    }

And the corresponding Asp.Net script:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
        DataFile="~/events.accdb" SelectCommand="SELECT * FROM [upcoming]"

        InsertCommand="INSERT INTO [upcoming] ([eventname], [description],[coordinator], [venue],[time])
         VALUES (@eventname1, @description1,@coordinatior1, @venue1, @time1)" >


        <InsertParameters>
          <asp:Parameter Name="eventname1" Type="String" />
          <asp:Parameter Name="description1" Type="String" />
          <asp:Parameter Name="coordinator1" Type="String" />
          <asp:Parameter Name="venue1" Type="String" />
          <asp:Parameter Name="time1" Type="DateTime" />

        </InsertParameters>

    </asp:AccessDataSource>
<asp:Panel ID="Panel2" runat="server">
   <table>
            <tr><td><asp:Label ID="Label6" runat="server" Text="Name:"></asp:Label></td>
           <td class="style1"> <asp:TextBox ID="TextBox6" runat="server" 
                    Height="24px" Width="548px"></asp:TextBox></td></tr>

            <tr><td><asp:Label ID="Label7" runat="server" Text="Description:"></asp:Label></td>
           <td class="style1"> <asp:TextBox ID="TextBox7" runat="server" 
                    Height="155px" TextMode="MultiLine" 
                   Width="555px"></asp:TextBox></td></tr>

            <tr><td><asp:Label ID="Label8" runat="server" Text="Event Coordinators:"></asp:Label></td>
           <td class="style1"> 
               <asp:TextBox ID="TextBox8" runat="server" 
                 Width="548px"></asp:TextBox></td></tr>

           <tr><td> <asp:Label ID="Label9" runat="server" Text="Venue:"></asp:Label></td>
            <td class="style1">
                <asp:TextBox ID="TextBox9" runat="server"  
                 Width="546px"></asp:TextBox></td></tr>

            <tr><td><asp:Label ID="Label10" runat="server" Text="Date &Time:"></asp:Label></td>
            <td class="style1">
                <asp:TextBox ID="TextBox10" runat="server" 
                 Height="23px" Width="543px"></asp:TextBox></td>
                </tr>

           <tr><td> 
               <asp:Button ID="Button5" runat="server" Text="Insert" 
                   onclick="Button5_Click" /></td>
           <td class="style1"> <asp:Button ID="Button6" runat="server" CommandName="Cancel" 
                   Text="Cancel" onclick="Button6_Click" /></td></tr>
            <table/>
</asp:Panel>

Please help me in inserting the datetime through the given mechanism.

Connection String in Access database

2013-06-06T16:19:35.55-04:00

I am trying to get my connection string to work with a Microsoft Access database.

This is what I have thus far:

In web.config:

In ASP.net:

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim strConn As String = System.Configuration.ConfigurationManager.ConnectionStrings("myConnString ").ConnectionString
        Dim Conn As New OleDbConnection(strConn)
        etc

Need a Help with insert. update and delete data using a access database

2013-06-07T16:29:36.097-04:00

Hi All I need someone to teach me some features in Visual Studio from scratch online to complete the following task .

1. To be able to create a form to add data to an access database

2. To be able to Search data from access database in a gridview or list view .

3. To be able to create a user login page base on the access level of the user.

Implementing a audit trail for our application

2013-06-01T01:27:42.813-04:00

I want to add an audit trail for our system so when any Add/Delete/Update operation happen i will log it, with the following info:-

the CRUD operation type. is it add, delete or update.
the record ID which have been modified.
Date and time.

Now i found two approaches to follow; either to have a single audit trail table with the following fields:-

ID .such as 123445.
CRUD_description. Such as Delete
Record_ID. Such as Qaeop12771
Date. Such as 1june2O13

Or to have two tables one for a lookup table for the CRUD operation such as

CRUD_ID. such as 3.
CRUD_Description.such as Delete.

And then the Audit trial will reference the above table:-

ID. such as 123445.
CRUD_ID (this will be a foreign key to the CRUD table) such as 3.
Record_ID. Such as Qaeop12771
Date. Such as 1june2O13

So which approach is better ??

Second question If i will follow the second approach . Then is it preferred to use the CRUD_ID inside my code for example if the oprration is delete i might have my code look like:-

Inset into audit_trail (ID, CRUD_ID, Record_ID, Date) values ( 123445, 3,12771,1june2O13)

// CRUD_ID 3 represents delete opration

Best Regards

display only the dashboards of the authentificated user

2013-05-21T11:45:31.15-04:00

Hi everybody,

i want to ask for your help, i have a problem in my database , with the JDash.net dashboard

this is my problem , my web page http://img5.imageshack.us/img5/1/sanstitrekg.png , when i click in "Ajouter un profil" a have a popup , with a textbox and a button , where i enter the name of the dashboard and confirm the creation ..

for example i have two users " pazzo" & "milan" , with the user pazzo i create two dashboards " Sport " & "Music" , with the user milan i create " News" & "People"

this is the result in my database ( dashboard ) table , http://img254.imageshack.us/img254/3367/sanstitre1awj.png ,

but when i authentificates whith the user pazzo for example , i have this http://img812.imageshack.us/img812/6386/sanstitre2yqz.png

i wanna to display only the dashboards of the user "pazzo" ( Sport & Music) ,

i'm usin the default table of createuserwizard
i need your help please!!

getting a record thet is between 2 dates

2013-05-31T08:05:49.063-04:00

het guys

my employer wants to have a form that gets you the expenses between 2 dates.

i have looked a lot on the internet but i couldn't find anything that could solve my problem.

because the problem is that the 2 dates only have a specified month and year and both are in a different field.

could you guys please help me?

How can i bind variables to database in sql server?

2013-05-30T09:44:02.017-04:00

I have designed a page using inline programming that is using strbuild.append.

I have declared strings which i want to be fetched from the database.

Eg. String address ="";

strbuild.append("address is"+address);

i want address from the database.

Please tell me about it including establishing the connection as well

binding table to database

2013-06-01T09:07:55.653-04:00

i have assigned the following variables as:

string[] s_no=""'

string[] emp_name="";

i have a table at backend which contains the above entities.

how can i access those multiple value and bind it to a single variable?

should i do it using array only or by loop?

in both cases how?

Forcing a field uniqueness on the database is it a bad design

2013-06-01T01:12:45.31-04:00

I have the following tables.

Available_Ip_Address (IP_Address_ID (Primary Key ), description, status ).
Virtual_Machine (VM_ID (Primary Key ), description, IP_Address (foreign key ) )

The scenario is as follow:-

we maintain all the available IP address of a single table.
we maintain the virtual machine info in another table , but specific IP address can be assigned to a single VM at any time.
so i have added a drop down list when adding a VM which contain all the IP address that have their status as “available”.
but since two users can create a VM and they might select the same available IP address
so i found that the best way to protect our system is to make the foreign key IP_Address in the Virtual_Machine table unique.
then if two users select the same IP address an exception will be raised in the Database for one of them and i can propagate the exception and then display a user friendly message to the user that the IP address might have being already taken.

So is my approach considered a valid design, or i should look for adding a timestamp field on the IP_address field so if the status of the IP address was changed since it was last queried then the record will have new timestamp and a exception will be raised on the application level.

Best Regards

</div>

Access Data Provider version issues

2013-05-29T15:39:47.927-04:00

I have a website with a data connection. The data connection reads as such:

Works great on my machine, but when I transfer it to another developer's box, it give the error:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Which is, of course, because he doesn't have access installed on his computer.

But now the main issue is when we send this to the customer for his review. The customer may not have Access, or not the same version as mine. So how do I take care of this in deployment? Is there a way to make sure the VS 2012 publish tool includes the appropriate drivers, or is there a more generic driver I should be using - something more intrinsic to the .net framework?

How to retrieve data from database to radiobuttonlist?

2013-05-28T22:06:02.523-04:00

Supposed I have the form below with values coming from a database, how do I know which Item was selected selected? Please not value store on db is as text not true or false.

---- aspx page-----

<form runat="server">
<asp:RadioButtonList id="radiolist1" runat="server">
   <asp:ListItem  GroupName="Software" AutoPostBack="true">Asp.net</asp:ListItem>
   <asp:ListItem  GroupName="Software" AutoPostBack="true">Coldfusion</asp:ListItem>
   <asp:ListItem  GroupName="Software" AutoPostBack="true">C</asp:ListItem>
   <asp:ListItem  GroupName="Software" AutoPostBack="true">Other</asp:ListItem>
</asp:RadioButtonList>
<br>
<asp:Button text="Update" OnClick="submit" runat="server"/>

---aspx.cs ----

protected void Page_Load(object sender, EventArgs e)  {
        using (OracleConnection con = new OracleConnection(connectionString))  {
            
             
            string sql_select = "SELECT softwareType FROM HABBIESTABLE WHERE USER='" + id.ToString() + "'";
            OracleCommand cmnd = new OracleCommand(sql_select, con);
            con.Open();
            OracleDataReader reader = cmnd.ExecuteReader();

            while (reader.Read()) {                
  
                // I don't know what I am doing here below :( 
                ListItem item = new ListItem();
                item.Text = reader["softwareType"].ToString();
                item.Value = reader["RadioButtonMyItems"].ToString();               
                item.Selected = Convert.ToBoolean(reader["softwareType"]);
                RadioButtonCourses.Items.Add(item);
            } 

 }

I can't get it to work. Can you help?!

Thank you everyone

Copy data from a stored procedure into an access table.

2013-05-24T11:27:56.317-04:00

Hi i need to take data from a SQL Server table and copy the data in an access 2003.
I am using vb.net
I have Stored procedure that return around 20 000 rows.
I create a Datset that conain all the info from the stored prcedure.
Is there an easy way to copy all the data from my Dataset into my access table.
Right now it working but i am looping throught all my row i'am sure thre a better way of doing that?

Is there a way to do something like :

insert into xxx IN 'Datset or Stored Procedure Name'
Tks

Here what i have :

Dim myconnection As SqlConnection
Dim sqlGetEleveFromStaging As SqlDataAdapter
Dim oDs As New DataSet

myconnection = New SqlConnection(ConfigurationManager.ConnectionStrings("xxx").ConnectionString)

sqlGetEleveFromStaging = New SqlDataAdapter("Info", myconnection)
sqlGetEleveFromStaging.SelectCommand.CommandType = CommandType.StoredProcedure
sqlGetEleveFromStaging.Fill(oDs)

Dim connectionStudDemo As OleDbConnection
connectionStudDemo = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source= " + MapPath("xxx.mdb"))
connectionStudDemo.Open()

For a = 0 To oDs.Tables(0).Rows.Count - 1
 strSQl = "insert into eleves (xxx) VALUES ('" & oDs.Tables(0).Rows(a).Item("xxx") & "')
Next

AccessDataBaseEngine.exe + not able to read more than 8000 records from excel sheet

2012-06-21T07:08:53.833-04:00

Hi,

I installed AccessDataBaseEngine.exe on Windows Server 2008 R2 (VM) and using it for reading excel files using Asp.net C#; I've an excel file which has almost 80000 records, but I am able to read only 8128 records from the excel sheet. I am using the following connection string for reading records from excel sheet -
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=***UploadedExcelFilePath***;Extended Properties=Excel 12.0;"

Any ideas how to resolve this???

how to connect to local server database from ftp?

2013-05-22T06:29:05.1-04:00

hi..

I am using ASP.net 4.0 and MS Access 2010 as the database.My database name is HR Portal and i have put it in the web hosting server.I have a central Database in the local server of my Company office.Both the databases contain some common tables.My issue is that whenever any insertion or updation takes place in the HR Portal(which is online),that updations should be made in the Central database located in the local server.Since i cannot connect to the central database from ftp,is there any other option to bring the updates on the central database.Someone please help me to solve this....

Operation must use an updateable query

2013-05-15T12:55:30.507-04:00

hi..

I am using ASP.net 4.0 and MS access 2010 as database.my website was working fine locally.When i hosted it in the web,during insert operation,this error is coming: "Operation must use an updateable query".What should i do to solve this error.I tried to change the permissions in the app_data folder,but alrealy read and write options are given for the user and administrator.Should i login as administrator to solve this issue.Please someone help me..I'm really in trouble.

Help in displaying data from MS Access with array

2013-05-22T02:04:00.91-04:00

Hello, I have created a code which display a text based on the select keyword. It was successful when I used textbox, but now I am trying it with array instead. So i change the textbox part to array value, but somehow I cannot display the words. Can anyone please help me.

This is my code:

<div>
     <asp:Textbox ID="test" TextMode="MultiLine" Height="200px" Width="200px"  runat="server" />
    <asp:Button ID="button" runat="server" OnClick="Button1_Click" Text="Submit" />
    </div>

 protected void Button1_Click(object sender, EventArgs e)
    {
        string[] array = new string[2];

        array[0] = "Animal";
        array[1] = "Eat";

        string ConnectString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=" + @"D:\\Database1.accdb;";
        OleDbConnection objCon;
        OleDbCommand objCmd;
        string GetSql = string.Empty;
        GetSql = "(SELECT * FROM table1 where Word = '" + array.ToString().ToUpper() + "' )";
        using (objCon = new OleDbConnection(ConnectString))
        {
            objCon.Open();
            using (objCmd = new OleDbCommand(GetSql, objCon))
            {
                OleDbDataReader Dr = objCmd.ExecuteReader();
                if (Dr.HasRows)
                {
                    while (Dr.Read())
                    {
                        string val = Dr.IsDBNull(Dr.GetOrdinal("Verb")) ? null : Dr["Verb"].ToString();
                        test.Text = array.ToString().ToUpper() + " " + val;
                    }
                }
                else
                {
                    test.Text = "ERROR";
                }
            }
            objCon.Close();
        }
    }

Receive an error when trying to connect to MS Access

2013-05-21T06:19:42.74-04:00

Hai, this is the error that I receive:

'D:/Database1.accdb;' is a physical path, but a virtual path was expected.

Should I add anything in the web.config?

This is my code, please help:

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

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

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string ConnectString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=" +  MapPath(@"D:\\Database1.accdb;");
        OleDbConnection objCon;
        OleDbCommand objCmd;
        string GetSql = string.Empty;
        GetSql = "(SELECT * FROM table1 where Word = '" + test.Text.ToUpper() + "' )";
        using (objCon = new OleDbConnection(ConnectString))
        {
            objCon.Open();
            using (objCmd = new OleDbCommand(GetSql, objCon))
            {
                OleDbDataReader Dr = objCmd.ExecuteReader();
                if (Dr.HasRows)
                {
                    while (Dr.Read())
                    {
                        string val = Dr.IsDBNull(Dr.GetOrdinal("Verb")) ? null : Dr["Verb"].ToString();
                        test1.Text = test.Text.ToUpper() + " " + val;
                    }
                }
                else
                {
                    test1.Text = "ERROR";
                }
            }
            objCon.Close();
        }
    }
}