INSERT INTO table by pulling data from another table

Question

I have inherited this project from someone else and need to clean it up to meet new requirements - they have designed it like so..

I have 2 tables in my Access database (I have included the data types of the fields in curly brackets):

Events:

EventID {AutoNumber} | EventTitle {Short Text} | EventDescription {Long Text} | EventDate {Date/Time} | EventCategory {lookup with a row source of Categories/CategoryTitle}

Categories:

CategoryID {AutoNumber} | CategoryTitle {Short Text} | CategoryImage {Short Text} | CategoryColor {Short Text}

The 'Categories' table holds all of the 'Master' information about a particular category such as it's name, it's icon (for example a tick, cross, etc).

And I have a query (inside of Access) to pull the data in these two tables together, with the fields below in order:

The SQL for the Query is:

SELECT Events.EventID, Events.EventTitle, Events.EventDescription,
Events.EventDate, Categories.CategoryTitle, Categories.CategoryImage,
Categories.CategoryColor FROM Events INNER JOIN Categories ON
Events.EventCategory = Categories.CategoryID ORDER BY
Events.EventDate;

Other areas of the website rely on the query to retrieve information.

In Visual Studio, I am trying to insert values from asp textbox and dropdown controls into the fields below from my database:

EventTitle (to Events.EventTitle)
EventDescription (to Events.EventDescription)
EventDate (to Events.EventDate)
EventCategory (to Events.EventCategory)

Number 4 is problematic as it is currently an Access lookup to the "Master" event table which holds information on the actual categories. Presumably, it was never intended that data was inserted into the table this way; the only possible way to set the Category of an event is by using the dropdown combobox inside of the Access database to select the event.

And finally here is the sub routine I am writing to insert into the database:

 Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
     Dim conn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("BookMeetConnString").ConnectionString)
     conn.Open()
     Dim cmd As New OleDbCommand("INSERT INTO Events (EventTitle, EventDescription, EventDate, EventCategory) VALUES (@f1,@f2,@f3,@f4)",
    conn)
     cmd.Parameters.AddWithValue("@f1", tb_eventtitle.Text)
     cmd.Parameters.AddWithValue("@f2", tb_eventdescription.Text)
     cmd.Parameters.AddWithValue("@f3", DateTime.ParseExact(tb_eventdate.Text, "dd/MM/yyyy",
     CultureInfo.InvariantCulture))
     cmd.Parameters.AddWithValue("@f4", dd_eventcategory.SelectedValue)
     cmd.ExecuteNonQuery()
     conn.Close()
 End Sub

There are other ASP.NET databinded controls elsewhere on the Website that are dependent on the contents of the 'Categories' table remaining the same, so how can I INSERT INTO the 'Events' table with the fields I've listed above (specifically EventCategory)?

EDIT:

Here is my finished sub:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles
  Button1.Click
     Dim conn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("BookMeetConnString").ConnectionString)
     conn.Open()
     Dim cmd As New OleDbCommand("INSERT INTO Events (EventTitle, EventDescription, EventDate, EventCategory) VALUES (@f1,@f2,@f3,@f4)",
     conn)
     cmd.Parameters.AddWithValue("@f1", tb_eventtitle.Text)
     cmd.Parameters.AddWithValue("@f2", tb_eventdescription.Text)
     cmd.Parameters.AddWithValue("@f3", DateTime.ParseExact(tb_eventdate.Text, "dd/MM/yyyy",
     CultureInfo.InvariantCulture))
     cmd.Parameters.AddWithValue("@f4", dd_eventcategory.SelectedIndex + 1)
     cmd.ExecuteNonQuery()
     conn.Close()
 End Sub

Does the EventCategory lookup field allow multiple selections? — Gord Thompson
@Gord Thompson: No, just single selections i.e. "Public", "Private" or "Group Only" — adaam
@dasblinkenlight: Sorry yes that was a mistake, I'll repost my working code (I was just playing about with selecting from multiple tables but realized the solution to my problems would require much more legwork than simply performing a select query) — adaam

Gord Thompson Gord Thompson · Accepted Answer · 2013-04-10T19:27:56

Lookup fields can be deceiving. Say you have your [Categories] table

CategoryID  CategoryTitle
1           Pub Crawl
2           Booze Up
3           Big Drunk

and your [Events] table looks like this when you open it in Datasheet View

EventID EventTitle  EventCategory
1       Thursday    Pub Crawl

If you wanted to add a new "Friday" event you'd think that it would be something like

INSERT INTO Events (EventTitle, EventCategory) VALUES ("Friday", "Big Drunk")

but that fails with "Microsoft Access set 1 field(s) to Null due to a type conversion failure...". Instead, you have to do

INSERT INTO Events (EventTitle, EventCategory) VALUES ("Friday", 3)

where 3 is the numeric CategoryID, not what you see in Datasheet View.

So, what you need to do is make sure that you retrieve the (numeric) CategoryID from your combo box (not the CategoryTitle) and insert that into your Events table.

BTW, this is why the more seasoned Access developers avoid lookup fields: they hide the actual workings of the database and lead to confusion, frustration, and, yes, drive some users to drink.... ;)

INSERT INTO table by pulling data from another table

1 Answers