Access Databases and AccessDataSource Control

Bind Datarow[] with Dropdownlist

JavedBoqo — Tue, 24 Nov 2009 08:07:41 GMT

hi,

i have two dropdownlists and i want to fill second dropdownlist after selecting item from first dropdownlist. i have fill data in datatable and i am searching for specific items depend on value selecting in 1st dropdownlist using following lines of code

DataRow[] dr=MYDT.Select("SMCODE ='"+cmbSalesman.SelectedValue.ToString()+"'"); "PNAME";"SMCODE";

cmbParty.DataSource = dr;

cmbParty.DataTextField =

cmbParty.DataValueField =

cmbParty.DataBind();

but it gives following runtime error:

DataBinding: 'System.Data.DataRow' does not contain a property with the name 'PNAME'.

any idea

LinkButton vs Button Events for Data Update

QUL001 — Sat, 21 Nov 2009 20:44:30 GMT

I have used a Button event to update an Access Database with VB code, as follows:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

GRName = TextBox1.Text

GRCompany = TextBox2.Text

con.Open()

cmd = New OleDbCommand("Insert Into Table1(Name, Company) Values (@NameParam, @CompanyParam)", con)

cmd.Parameters.AddWithValue("@NameParam", GRName)

cmd.Parameters.AddWithValue("@CompanyParam", GRCompany)

cmd.ExecuteNonQuery()

End Sub

However, the same procedure does not work for a LinkButton event, with the exact same code as above but substituting LinkButton for Button. Is there a problem with using a LinkButton control to execute such commands?

Thanks.

Problem Filling a DataSet

ryguy7272 — Tue, 17 Nov 2009 23:57:08 GMT

I am having a small problem filling a DataSet. I thought I had a handle on this, because I did something quite similar earlier today, but this version (slightly different from the other) is not working. Below is my code:

<% @Register Namespace="DataControls" Assembly="DataCalendar" TagPrefix="dc" %>

<% @Import Namespace="System.Data" %>

<% @Import Namespace="System.Data.Sql" %>

<% @Import Namespace="System.Data.SqlClient" %>

Function GetConnection() as SqlConnection

Dim conn As New SqlConnection("Server=EXCEL-4J2W8KYNP\SQLEXPRESS;database=PubCrawls;Trusted_Connection=Yes;")

return conn

End Function

Function GetEventData(startDate as DateTime, endDate as DateTime) as DataTable

dim conn as SqlConnection = GetConnection()

dim cmd as SqlCommand = new SqlCommand()

cmd.Connection = conn

cmd.CommandText = String.Format("Select EventDate, Count(*) From Events Where EventDate >= #{0}# And EventDate <= #{1}# Group By EventDate", _

startDate, endDate)

dim ds as DataSet = new DataSet()

dim da as SqlDataAdapter = new SqlDataAdapter(cmd)

da.Fill(ds)

conn.Close()

return ds.Tables(0)

End Function

...some more code down here...never even get this far...

The code fails on this line:

da.Fill(ds)

The message is:

Sql expression was unhandled by user code.

Incorrect syntax near ‘12’.

The 12-thing is baffling me. I don’t have any ‘12’ in my whole project. What does this error REALLY mean? What am I doing wrong?

Thanks!

Ryan---

SQL Query Help

Zamma — Mon, 23 Nov 2009 12:49:13 GMT

Site Code: ASP.Net (2.0)

Language: VB

Database: MS Access (2003)

Not sure if this is the best place to post but will give it a try anyway as the folks here seem to be very helpful, even if it just to point me in the direction of another forum.

I have a DataGrid which is showing information queried from a collection of tables referenced by one main table. The initial query works great and populates the DataGrid. I then have two DropDownLists which are used to select 'Active/Disabled' and 'All/Female/Male/Unisex' for filtering the data that is pulled in the query. The Active/Disable one works without problems but when I try to filter on Gender I am having issues. I pressume this is because the gender is stored in one of the referenced tables. This is the query:

sComm = "SELECT "
        sComm = sComm & "tbl_product.product_id,"
        sComm = sComm & "(SELECT tbl_model.model_name FROM tbl_model WHERE tbl_model.model_id=tbl_product.model) As model,"
        sComm = sComm & "(SELECT tbl_frame.frame_name FROM tbl_frame WHERE tbl_frame.frame_id=tbl_product.frame) As frame,"
        sComm = sComm & "(SELECT tbl_lens.lens_name FROM tbl_lens WHERE tbl_lens.lens_id=tbl_product.lens) As lens,"
        sComm = sComm & "(SELECT tbl_model.gender FROM tbl_model WHERE tbl_model.model_id=tbl_product.model) As gender,"
        sComm = sComm & "(SELECT tbl_model.extras FROM tbl_model WHERE tbl_model.model_id=tbl_product.model) As extras "
        sComm = sComm & "FROM tbl_product "

For clarification:

tbl_product is made up of references to the primary keys of 3 tables - tbl_model, table_frame and tbl_lens.

What I need to know is how to filter this query on gender. I thought a simple WHERE gender='Male' would do it but it doesn't seem to like this. Maybe a Join of some sort is needed?

Insert into Access from connection string in aspx.vb page

geo0071 — Tue, 17 Nov 2009 21:09:00 GMT

Hi,

I am hoping someone can help me here. I am trying to insert into an Access database using a connection string from the web.config file. I am trying to do this in a code behind page (aspx.vb). Here is an example, any suggestions are much appreciated. I know my web.config is set correctly, because I am able to use the connection string using the grid control. Here is a sample of the code. Thanks again.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionstring As New ConnectionStringsSection
        ConnectionString = "<%$ ConnectionStrings:BankingConnectionString %>"
        ProviderName = "<%$ ConnectionStrings:BankingConnectionString.ProviderName %>"
        Dim conn As New OleDb.OleDbConnection("Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\banking.mdb")
        Dim sql As String = "INSERT INTO customer_data (ssn, fname, lname) VALUES (@tbSSN, @tbFname, @tbLname)"
        Dim cmd As New OleDb.OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@tbSSN", tbSSN.Text)
        cmd.Parameters.AddWithValue("@FirstName", tbFname.Text)
        cmd.Parameters.AddWithValue("@LastName", tbLname.Text)
        conn.Open()
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        conn.Close()

lblMessage.Text = ("Thanks! We have created a record for the SSN of " + tbSSN.Text)
tbSSN.Text = ""

End Sub

Access date time function cannot be searched

Fliz — Sat, 21 Nov 2009 15:02:46 GMT

In my database I use the date/time fuction but I need to be able to search with the date as a parameter, but I have to specify it as a type which throws a data mismatch error.

<asp:ControlParameter ControlID="ODate" DefaultValue="%%" Name="OrderDate" PropertyName="Text" Type= "DateTime" />

is there a specific type to use to get it to work? Or do I have to do something more complex?

Thanks in advance

How to do the same as SQL but with access db

cubangt — Fri, 20 Nov 2009 18:04:14 GMT

I have a few applications that connect to SQL.. so my connections and inserts are done within the code behind. I then have some older apps that are still updating and inserting into Access. how can i accomplish the same thing as seen below but for Access?

        using (SqlConnection conn = new SqlConnection(this.connDB))
        using (SqlCommand cmd = new SqlCommand())
        {
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = "emp_insert_new_hire";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.AddWithValue("@dob", txtbirth.Text.ToString());
            cmd.Parameters.AddWithValue("@gen", gen.SelectedValue.ToString());
            cmd.Parameters.AddWithValue("@comby", User.Identity.Name.ToString().Split('\\')[1]);
            cmd.Parameters.AddWithValue("@eth", eth.SelectedValue.ToString());
            cmd.ExecuteNonQuery();
        }

Syntax error in UPDATE statement.

I-Weedy — Fri, 16 Feb 2007 17:24:08 GMT

Can anybody help me ?

Fo the past few days i have been trying to create an edit, update delete datalist form however I have consistently been met with a syntax error in UPDATE statement.

I have tried various update statements and am still met with the same error! Can somebody stop me from going mad?

Imports System

Imports

System.Data

Imports

System.Data.OleDb

Partial

Class aspx_pages_admin_adminLatestNews

Inherits System.Web.UI.Page

Private Sub Page_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) _

Handles MyBase.Load

'Put user code to initialize the page here

If Not IsPostBack Then

LoadData()

End If

End Sub

Public Sub LoadData()

Dim ds = New DataSet

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Inetpub/wwwroot/test.mdb;")

Dim OleDbDataAdapter1 = New OleDbDataAdapter("SELECT * FROM test", conn)

'Try

conn.Open()

OleDbDataAdapter1.Fill(ds,

"titles_record")

conn.Close()

DataList1.DataSource = ds

DataList1.DataBind()

End Sub

Sub Edit_Command(ByVal sender As Object, _

ByVal e As DataListCommandEventArgs)

' to rebind the DataList to the data source to

' refresh the control.

DataList1.EditItemIndex = e.Item.ItemIndex

LoadData()

End Sub

Sub Cancel_Command(ByVal sender As Object, _

ByVal e As DataListCommandEventArgs)

DataList1.EditItemIndex = -1

LoadData()

End Sub

Sub Update_Command(ByVal sender As Object, _

ByVal e As DataListCommandEventArgs)

Dim tbox As TextBox

Dim myID, myTitle, myArticle, myImage, myArticleDesc, myAuthor, myDateNews As String

'---retrieves the key for the row---

myID = DataList1.DataKeys(e.Item.ItemIndex)

'---find the textbox control containing the Title

tbox =

CType(e.Item.FindControl("txtTitle"), TextBox)

myTitle = tbox.Text

'---find the textbox control containing the Article

tbox =

CType(e.Item.FindControl("txtArticle"), TextBox)

myArticle = tbox.Text

'---find the textbox control containing the Image

tbox =

CType(e.Item.FindControl("txtImage"), TextBox)

myImage = tbox.Text

'---find the textbox control containing the ArticleDesc

tbox =

CType(e.Item.FindControl("txtArticleDesc"), TextBox)

myArticleDesc = tbox.Text

'---find the textbox control containing the Author

tbox =

CType(e.Item.FindControl("txtAuthor"), TextBox)

myAuthor = tbox.Text

'---find the textbox control containing the DateNews

tbox =

CType(e.Item.FindControl("txtDateNews"), TextBox)

myDateNews = tbox.Text

'---updates the database---

Dim myUpdate As String = "UPDATE test SET (Title, Article, Image, ArticleDesc, Author, DateNews)VALUES(@Title, @Article, @Image, @ArticleDesc, @Author, @DateNews)"

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Inetpub/wwwroot/test.mdb;")

Dim comm As OleDbCommand = New OleDbCommand(myUpdate, conn)

comm.Parameters.Add(

New OleDbParameter("@Title", myTitle))

comm.Parameters.Add(

New OleDbParameter("@number", myArticle))

comm.Parameters.Add(

New OleDbParameter("@nameID", myImage))

comm.Parameters.Add(

New OleDbParameter("@name", myArticleDesc))

comm.Parameters.Add(

New OleDbParameter("@number", myAuthor))

comm.Parameters.Add(

New OleDbParameter("@nameID", myDateNews))

Try

conn.Open()

comm.ExecuteNonQuery()

conn.Close()

DataList1.EditItemIndex = -1

LoadData()

Catch ex As Exception

Label1.Text = ex.Message

'Write error message

Exit Try

End Try

End Sub

Sub Delete_Command(ByVal sender As Object, _

ByVal e As DataListCommandEventArgs)

'---retrieves the key for the row---

Response.Write(DataList1.DataKeys(e.Item.ItemIndex))

'---codes to delete row here----

'-------------------------------

End Sub

End

Class

Errors when trying to upsize ms access database to ms sql

mausimo — Wed, 18 Nov 2009 21:13:45 GMT

Hello,

I am trying to upsize a existing ms access database to a sql database. The reason why i am doing this is because i know how to use ms sql no problem, where as ms access is so different i can not even figure out how to write queries....

When upsizing to sql server using the wizard: Use existing database option >> next button gives me the following error - ODBC --call failed.

When upsizing to sql server using the wizard: Create New Database option >> next button >> (on the next wizard screen:) if i use Database: (local) and have use Trusted Connection checked i get the error - ODBC connection to 'sql Server(local)' failed.

Does anyone know what is going wrong or what i am doing wrong?

Thanks,

-Mike

Noob learning about forms.

FreeFromTheSouth — Wed, 18 Nov 2009 17:40:07 GMT

I have a access datasource with a simple listbox that when the record is selected it postback a different column from the record's data on the existing label.

If I add another Label and I want to get another column from thet recortd to postback in the label as well what do I do to this codebehind to make it work?

Thanks,

Free

Codebehind

-------------------------------------------------------

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;

public partial class DataBind : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
            this.ListBox1.DataSource = this.AccessDataSource1;
            this.ListBox1.DataTextField = "AuthorLastName";
            this.ListBox1.DataValueField = "Title";
            this.ListBox1.DataBind();
    }
    protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        this.Label1.Text = ListBox1.SelectedItem.Value;
    }
}

-------------------------------------------------------

ASPX Code

-------------------------------------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataBind.aspx.cs" Inherits="DataBind" %>

<!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>Untitled Page</title>
    <style type="text/css">
        .style1
        {
            width: 250px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <asp:AccessDataSource ID="AccessDataSource1" runat="server"
            DataFile="~/App_Data/ASPDotNetStepByStep.mdb"
            SelectCommand="SELECT * FROM [DotNetReferences]"></asp:AccessDataSource>
        <br />
    <table align="left" style="height: 186px; width: 271px">
        <tr>
            <td>
                <asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True"
                    onselectedindexchanged="ListBox1_SelectedIndexChanged"
                    DataTextField="ID" DataValueField="Title" Width="181px"/>
                <br />
            </td>
        </tr>

        <tr>
            <td>
                <asp:Label ID="Label1" runat="server" Text=""/>
            </td>
        </tr>
    </table>

    </form>
</body>
</html>

To change SQL script to Access

Oskar92 — Tue, 17 Nov 2009 12:45:28 GMT

Hi i need to change alot of SQL coding to work with an Microsoft Access Database, was hoping that some blessed soul could write the whole code for me. It an script to an Calandar.

The link:http://aspnet.4guysfromrolla.com/articles/041603-1.2.aspx

At the buttom under attachment Download the complete source code (in zip-format)

Best regards

Oskar

Any release of 64 Bit Jet drivers on the horizon?

MartijnG — Thu, 17 Sep 2009 10:34:05 GMT

Can any of the Microsoft people comment if any 64 Bit Jet drivers will be developed?

I’m working on a large web application that runs in 64 bit with a big database backend but one small part of the application requires the ability to connect to some Access databases using oledb.

At the moment I’ve dropped the whole app down to 32 bit to make it work but it was faster on 64 bit. Unless there is any intention of MS developing 64 bit Jet Driver then I may queue these reporting & data manipulation processes using Access and run then from a backend windows console application and push the end report to the web interface for download on completion. Choices, choices.

Thanks for any feedback,

Access Database access fail

bryanfok — Mon, 16 Nov 2009 16:11:44 GMT

Hi all

My application has driven me crazy. It works fine at my home deve machine , it works fine in my work dev machine, and it works fine in my work local server. But when i deploy to live (a new server), it crashed!!!

It can display my login page, but when i tried to login, it couldnt connect to my access database. my access database is at C:\wwwroot\TUV_MGB_PROJECT_MANAGEMENT\App_Data\ASPNetDB.mdb

Error:

Server Error in '/' Application.

AccessFile is not valid: C:\wwwroot\TUV_MGB_PROJECT_MANAGEMENT\Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\wwwroot\TUV_MGB_PROJECT_MANAGEMENT\App_Data\ASPNetDB.mdb;Jet OLEDB:Database Password=Stx4alyz;

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: AccessFile is not valid: C:\wwwroot\TUV_MGB_PROJECT_MANAGEMENT\Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\wwwroot\TUV_MGB_PROJECT_MANAGEMENT\App_Data\ASPNetDB.mdb;Jet OLEDB:Database Password=Stx4alyz;

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Exception: AccessFile is not valid: C:\wwwroot\TUV_MGB_PROJECT_MANAGEMENT\Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\wwwroot\TUV_MGB_PROJECT_MANAGEMENT\App_Data\ASPNetDB.mdb;Jet OLEDB:Database Password=Stx4alyz;]
   Samples.AccessProviders.AccessConnectionHelper.EnsureValidMdbFile(String fileName) in C:\Documents and Settings\fokbr\My Documents\Visual Studio 2008\Projects\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT\Samples\AccessProviders\AccessConnectionHelper.cs:291
   Samples.AccessProviders.AccessConnectionHelper.BuildConnectionForFileName(String dbFileName) in C:\Documents and Settings\fokbr\My Documents\Visual Studio 2008\Projects\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT\Samples\AccessProviders\AccessConnectionHelper.cs:95
   Samples.AccessProviders.AccessConnectionHelper.GetConnection(String dbFileName, Boolean revertImpersonation) in C:\Documents and Settings\fokbr\My Documents\Visual Studio 2008\Projects\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT\Samples\AccessProviders\AccessConnectionHelper.cs:152
   Samples.AccessProviders.AccessRoleProvider.IsUserInRole(String username, String roleName) in C:\Documents and Settings\fokbr\My Documents\Visual Studio 2008\Projects\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT\Samples\AccessProviders\AccessRoleProvider.cs:98
   System.Web.Security.Roles.IsUserInRole(String username, String roleName) +607
   TUV_MGB_PROJECT_MANAGEMENT.Login.LoginButton_Click(Object sender, EventArgs e) in C:\Documents and Settings\fokbr\My Documents\Visual Studio 2008\Projects\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT_V2\TUV_MGB_PROJECT_MANAGEMENT\Login.aspx.cs:45
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +115
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +140
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

This is my connection string in web.config.

Can someone help me solve this please.

Bryan

"LIKE" QUERY execution through C# code

ranacseruet — Tue, 17 Nov 2009 08:54:49 GMT

I am trying to execute access query that search a table. I am using ----- LIKE 'a*' -------- sytax. this is executing correctly when i am running on database. but when i am executing through asp.net/c# using enterprise library, returned dataset's table has 0 rows. Why is this? Can anyone give me the solution?

Regards

Select Statement Today/Now syntax

TRU — Mon, 16 Nov 2009 14:36:26 GMT

Below is my select statement for an events list.

SelectCommand

="SELECT * FROM [hdEvents] ORDER BY [eventsDate]"

I would like to modify this statement so that expired dates do not display.

So, something like Where eventsdate is >= Today or Now.

What is the proper syntax to make this happen.

Thanks,

TRU

access data from multiple tables in vwd2008

tracy6001 — Mon, 16 Nov 2009 12:54:30 GMT

hello,

i am trying to get data from two tables named "ofotentry,fabotentry"

using two text boxes in my form in which i am giving two dates ...acoording to that date range i need name,date and total hrs fields from the two tables...

my asp.net coding is like this

SELECT fabotentry.Name, fabotentry.[Date], fabotentry.TotalHrs, ofotentry.Name1 AS Expr1, ofotentry.[Date1] AS Expr2, ofotentry.TotalHrs1 AS Expr3 FROM fabotentry, ofotentry WHERE (fabotentry.[Date] <= ?) AND (fabotentry.[Date] >= ?) AND (ofotentry.[Date1] <= ?) AND (ofotentry.[Date1] >= ?)

my vb.net coding is like this

Dim dataset1 As New DataSet()
Try
            Dim con As OleDbConnection
            Dim cmd As OleDbCommand
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator.HOME\My Documents\Visual Studio 2008\WebSites\production\otentry.mdb;")
            Dim sqlstring As String = "select Name,Date,TotalHrs,Name1,Date1,TotalHrs1 from fabotentry,ofotentry where Date between '" & tb1.Text & "' and '" & tb2.Text & "'"
cmd = New OleDbCommand(sqlstring, con)
Dim oledbdataadapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlstring, con)
con.Open()

            oledbdataadapter1.Fill(dataset1, "ofotentry")
            oledbdataadapter1.Fill(dataset2, "fabotentry")
            GridView1.DataSource = dataset1.Tables("ofotentry", "fabotentry")

GridView1.DataBind()

con.Close()

MY PROBLEM IS "NOTHING DISPLAYED" FOR THIS QUERY

WHAT TO DO???

THANKS

Trouble getting table to update and delete

Fliz — Mon, 16 Nov 2009 12:39:23 GMT

I am trying to get a table to update and delete from a asp.net page (VB) but when the page loads, no tables or anything appear to allow this to take place. Is there a problem with my sql? or is it something else?

<%@ Page Explicit="True" Language="VB" MasterPageFile="main.master" Debug="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="Syetm.Data.OleDb" %>
<asp:content id="script" ContentPlaceHolderID="script" runat="server">

    Protected Sub DetailsView_ItemDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeletedEventArgs)
        Response.Redirect("customers.aspx")
    End Sub

    Protected Sub DetailsView_ItemCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewCommandEventArgs)
        If e.CommandName = "Cancel" Then
            Response.Redirect("customers.aspx")
        End If
    End Sub
</script>

</asp:content>
<asp:content id="Title" ContentPlaceHolderID="Title" runat="server">DVD-Wonders Customers</asp:content>
<asp:content id="Header" ContentPlaceHolderID="Header" runat="server">
<h1>Edit Customer Table</h1>
</asp:content>
<asp:content id="PageBody" ContentPlaceHolderID="PageBody" runat="server">
To make changes to the customer table click Edit, make the changes, then click Update. To Delete a record , click the Delete button.
<br />
<br />
<asp:DetailsView ID="DetailsView" runat="server" Height="50px" Width="100%" AutoGenerateRows="False" DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" ForeColor="#FFFFFF" BorderColor="#0000CC" CellPadding="2" OnItemUpdated="DetailsView_ItemUpdated" OnItemDeleted="DetailsView_ItemDeleted" OnItemCommand="DetailsView_ItemCommand">
    <Fields>
        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" SortExpression="CustomerID" />
            <asp:BoundField DataField="CustomerTitle" HeaderText="Customer Title" SortExpression="CustomerTitle" />
           <asp:BoundField DataField="CustomerFName" HeaderText="Customer First Name" SortExpression="CustomerFName" />
            <asp:BoundField DataField="CustomerSName" HeaderText="Customer Surname" SortExpression="CustomerSName" />
            <asp:BoundField DataField="CustomerAddress1" HeaderText="Customer Address 1" SortExpression="CustomerAddress1" />
             <asp:BoundField DataField="CustomerAddress2" HeaderText="Customer Address 2" SortExpression="CustomerAddress2" />
             <asp:BoundField DataField="CustomerAddress3" HeaderText="Customer Address 3" SortExpression="CustomerAddress3" />
             <asp:BoundField DataField="CustomerPostcode" HeaderText="Customer Postcode" SortExpression="CustomerPostcode" />
             <asp:BoundField DataField="CustomerCounty" HeaderText="Customer County" SortExpression="CustomerCounty" />
             <asp:BoundField DataField="CustomerPhoneNo" HeaderText="Customer Phone No" SortExpression="CustomerPhoneNo" />
             <asp:BoundField DataField="CustomerMobile" HeaderText="Customer Mobile" SortExpression="CustomerMobile" />
              <asp:BoundField DataField="CustomerEmail" HeaderText="Customer Email" SortExpression="CustomerEmail" />
           <asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" />
      </Fields>
    <RowStyle BackColor="#FFFFFF" />
    <FieldHeaderStyle BackColor="#FFFFFF" Font-Bold="True" />
</asp:DetailsView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
        ConnectionString="<%$ ConnectionStrings:DVDConnectString %>"
        DeleteCommand="DELETE FROM [Customers] WHERE [CustomerID] = ? AND [CustomerTitle] = ? AND [CustomerFName] = ? AND [CustomerSName] = ? AND [CustomerAddress1] = ? AND [CustomerAddress2] = ? AND [CustomerAddress3] = ? AND [CustomerPostcode] = ? AND [CustomerCounty] = ? AND [CustomerPhoneNo] = ? AND [CustomerMobile] = ? AND [CustomerEmail] = ?" ProviderName="<%$ ConnectionStrings:DVDConnectString.ProviderName %>" SelectCommand="SELECT [CustomerID], [CustomerTitle], [CustomerFName], [CustomerSName], [CustomerAddress1], [CustomerAddress2], [CustomerAddress3], [CustomerPostcode], [CustomerCounty], [CustomerPhoneNo], [CustomerMobile], [CustomerEmail] FROM [Customers] WHERE ([CustomerID] = ?)"
        UpdateCommand="UPDATE [Customers] SET [CustomerID] = ?, [CustomerTitle] = ?, [CustomerFName] = ?, [CustomerSName] = ?, [CustomerAddress1] = ?, [CustomerAddress2] = ?, [CustomerAddress3] = ?, [CustomerPostcode] = ?, [CustomerCounty] = ?, [CustomerPhoneNo] = ?, [CustomerMobile] = ?, [CustomerMobile] WHERE [CustomerID] = ? AND [CustomerTitle] = ? AND [CustomerFName] = ? AND [CustomerSName] = ? AND [CustomerAddress1] = ? AND [CustomerAddress2] = ? AND [CustomerAddress3] = ? AND [CustomerPostcode] = ? AND [CustomerCounty] =? AND [CustomerPhoneNo] = ? AND [CustomerMobile] = ? AND [CustomerEmail] = ?">
        <DeleteParameters>
            <asp:Parameter Name="CustomerID" Type="String" />
            <asp:Parameter Name="CustomerTitle" Type="String" />
            <asp:Parameter Name="CustomerFName" Type="String" />
            <asp:Parameter Name="CustomerSName" Type="String" />
            <asp:Parameter Name="CustomerAddress1" Type="String" />
            <asp:Parameter Name="CustomerAddress2" Type="String" />
            <asp:Parameter Name="CustomerAddress3" Type="String" />
            <asp:Parameter Name="CustomerPostcode" Type="String" />
            <asp:Parameter Name="CustomerCounty" Type="String" />
   <asp:Parameter Name="CustomerPhoneNo" Type="String" />
            <asp:Parameter Name="CustomerMobile" Type="String" />
            <asp:Parameter Name="CustomerEmail" Type="String" />
        </DeleteParameters>
        <UpdateParameters>
             <asp:Parameter Name="CustomerID" Type="String" />
             <asp:Parameter Name="CustomerTitle" Type="String" />
             <asp:Parameter Name="CustomerFName" Type="String" />
             <asp:Parameter Name="CustomerSName" Type="String" />
             <asp:Parameter Name="CustomerAddress1" Type="String" />
             <asp:Parameter Name="CustomerAddress2" Type="String" />
             <asp:Parameter Name="CustomerAddress3" Type="String" />
             <asp:Parameter Name="CustomerPostcode" Type="String" />
             <asp:Parameter Name="CustomerCounty" Type="String" />
             <asp:Parameter Name="CustomerPhoneNo" Type="String" />
             <asp:Parameter Name="CustomerMobile" Type="String" />
             <asp:Parameter Name="CustomerEmail" Type="String" />
        </UpdateParameters>

</asp:SqlDataSource>
</asp:content>

thanks

@@Identity - Accessdatasource return ID Inserted record

colinejarvis — Fri, 13 Nov 2009 09:55:17 GMT

Hi,

I hope someone can help solve the problem of returning the value of the unique key when inserting a new record into an access database. I understand that if using an MSAccess database then @@Identity needs to be used. Iam usining access2007 and also coding in C#.

I have the following simple code to insert a contact name into Table1 with the unique key as Contactid which is set to autonumber in the MSAccess table:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ZZZ.aspx.cs" Inherits="ZZZ" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

function pageLoad() {

}

</script>

</head>

<body>

<div>

<asp:ScriptManager ID="ScriptManager1" runat="server" />

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"

DataKeyNames="ContactID" DataSourceID="AccessDataSource1" DefaultMode="Insert"

Height="50px" Width="125px">

<asp:BoundField DataField="ContactID" HeaderText="ContactID"

InsertVisible="False" ReadOnly="True" SortExpression="ContactID" />

<asp:BoundField DataField="ContactName" HeaderText="ContactName"

SortExpression="ContactName" />

<asp:CommandField ShowInsertButton="True" />

<asp:TemplateField>

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

</InsertItemTemplate>

</asp:TemplateField>

</Fields>

</asp:DetailsView>

<asp:AccessDataSource ID="AccessDataSource1" runat="server"

ConflictDetection="CompareAllValues" DataFile="~/App_Data/CRM.mdb"

DeleteCommand="DELETE FROM [Table1] WHERE [ContactID] = ? AND (([ContactName] = ?) OR ([ContactName] IS NULL AND ? IS NULL))"

InsertCommand="INSERT INTO [Table1] ([ContactName]) VALUES (?)"

OldValuesParameterFormatString="original_{0}"

SelectCommand="SELECT [ContactID], [ContactName] FROM [Table1]"

UpdateCommand="UPDATE [Table1] SET [ContactName] = ? WHERE [ContactID] = ? AND (([ContactName] = ?) OR ([ContactName] IS NULL AND ? IS NULL))">

<asp:Parameter Name="ContactName" Type="String" />

</InsertParameters>

</asp:AccessDataSource>

</div>

</form>

</body>

</html>

Code Behind:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class ZZZ : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

I also have a label1 which I would like to display the newly inserted record.

I would be grateful if someone could spare me the time to adjust the code for this simple example to get the Contact ID Value.

Many thanks in anticipation

Kind regards

Colin

How do I make an identity field in Access?

sfskseg32 — Sun, 15 Nov 2009 01:36:23 GMT

Hello,

I am trying to create an insert option for my page. I am using the details view control and a MS Access Database. Currently I cannot insert anything because I don't know how to handle the primary key insertion. I saw on another page that I need it to be an identity field. How do I make it one? Also, my current non working insert string looks like this:

INSERT INTO [customers] ([ID], [employee], [firstname], [lastname], [cell], [home], [work], [payment], [status], [notes]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

where ID is the primary key. Once I do make it an identity, would I just remove it from the insert string?

Thanks!

inset into access database

lamish — Sat, 14 Nov 2009 13:22:16 GMT

Hi guys!

OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|db.mdb;Persist Security Info=False");
OleDbCommand cmd = new OleDbCommand("INSERT INTO upload (aID, mID, bildnamn, datum) VALUES ('10', '81', '" + UploadControl1.FileName + "', '" + Convert.ToString(DateTime.Now) + "')", cn);

cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

this is my code.

Whne i launch it nothing happens. i dont get any error. when i look at the database to see if something was inserted i notice that nothing was.

anyone seeing a error whit my code?

SQL query: Inserting multiple results into a new table

lorengphd1 — Fri, 13 Nov 2009 20:54:30 GMT

I have a MS ACCESS 2000 sql query that returns several results, sometimes up to 30 or so. I want to insert these results into a new table all at once, each with their own unique ID [once in the new table]. How is this accomplished?

Thanks!

Loren

Can LINQ use on Access database ? How ?

hkbeer — Sat, 07 Nov 2009 15:29:55 GMT

Can LINQ use on Access database ? How ?

I read several books and no one touches on this.

Pls help... Thanks

Insert record from table1 into table2 using button click

hkbeer — Thu, 12 Nov 2009 09:11:43 GMT

Hi I have 2 tables, table 1 & table 2 in mdb file.

I have standard append query called queryA contains : insert into table2 select * from table1.

Now I want to make ASP.net page with a button onclick will perform exactly what queryA above does.

I tried adding accessdatasource as follows:-

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        AccessDataSource1.DataBind()
        Page.DataBind()

    End Sub

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Data.mdb" SelectCommand="INSERT INTO table2 SELECT *
FROM table1"></asp:AccessDataSource>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />

When button click, no record is appended.

Even I modified to

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Data.mdb" SelectCommand="Select * from QueryA"></asp:AccessDataSource>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />

No record is appended to Table2 again.

What should I do to make that append happen ?

Thannks

Using a datasource to insert a string

joshmariam — Wed, 11 Nov 2009 08:31:41 GMT

I have an access datasource and I want to take a string and input it into an access database.

I've had some troubles doing this and I really haven't found a solution. Can anyone tell me how to do this?

Reading xlsx file in C# using Microsoft.ACE.OLEDB returning blank value

bbenadict — Mon, 09 Nov 2009 13:00:04 GMT

Hi,

I have to read an xlsx file in which there are multiple columns. One of it is a 'Date' column type. I am using OLEDB with Microsoft.ACE.OLEDB.12.0 as the provider. The problem is, suppose if there is a non date value (Error data) in the column the oledb is replacing the error data with null. I have a requirement to show the all data (good data as well as the error) to user who is reading the excel file into our database.i.e. i dont wnt the engine to convert the value to null

When reading xls file,which can be done by using Microsoft.Jet.4.0 provider, we can overcome this by changing property of IMEX=1 and changing the 'Typeguessrows=1' in the registry. But unfortunately this provider cannot be used for reading an xlsx file.

Can anyone help me how to overcome this problem??? I have also listed down the code below which i am using for reading the file

DataSet ds = new DataSet();
            //Provider String Extended properties:
            //"Excel 12.0": Use Excel (xls or xlsx) as source
            //"Header Yes": Header is included in the Excel sheet
            //"IMEX=1": When reading from the excel sheet ignore datatypes and read all data in the sheet.
            //Without setting IMEX=0, the excel reader looks for the datatype in the excel sheet.
            //The code below in comments was used for reading xls file
            //OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
            // The code below is the one i am using for reading an xlsx file
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=0\"");
            con.Open();
            try
            {
                //Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference
                DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetname = dt.Rows[0]["TABLE_NAME"].ToString();

                OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + sheetname + "]", con);
                myCommand.Fill(ds);
                
            }
            catch (Exception ex)
            {
                string exce = ex.Message;
            }
            finally
            {
                con.Close();
            }
            return ds;