How to Save Records from any ComboBox on a Form to an Access Table Hosted on SharePoint?
Question
Answers
All replies
-
The C# script was tested on the desktop, but I would load it onto a server for the actual production. Here's the design view.
Here's the code.
using System; using System.Data; using System.Data.OleDb; using System.Web.UI; using System.Windows.Forms; namespace WebApplication4 { public partial class WebForm1 : System.Web.UI.Page { private object n; public object MessageBox { get; private set; } public object MassageBox { get; private set; } public object MessageBoxIcon { get; private set; } public object MessageBoxButton { get; private set; } protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\path_here\Risk_DB.mdb"; string rc_name = TextBox1.Text.Trim(); string rc_LoB = TextBox2.Text.Trim(); string rc_RBP = TextBox3.Text.Trim(); OleDbCommand cmd = new OleDbCommand(@"INSERT INTO tbl_IHC_Risk_Card (rc_name, rc_LoB, rc_RBP) VALUES (@rc_name, @rc_LoB, @rc_RBP)") { Connection = conn }; conn.Open(); if (conn.State == ConnectionState.Open) { // you should always use parameterized queries to avoid SQL Injection cmd.Parameters.Add("@rc_name", OleDbType.VarChar).Value = rc_name; cmd.Parameters.Add("@rc_LoB", OleDbType.VarChar).Value = rc_LoB; cmd.Parameters.Add("@rc_RBP", OleDbType.VarChar).Value = rc_RBP; try { cmd.ExecuteNonQuery(); Type cstype = this.GetType(); // Get a ClientScriptManager reference from the Page class. ClientScriptManager cs = Page.ClientScript; // Check to see if the startup script is already registered. if (!cs.IsStartupScriptRegistered(cstype, "PopupScript")) { String cstext = "alert('Data Added to Database!!');"; cs.RegisterStartupScript(cstype, "PopupScript", cstext, true); } conn.Close(); } catch (OleDbException ex) { Type cstype = this.GetType(); ClientScriptManager cs = Page.ClientScript; // Check to see if the startup script is already registered. if (!cs.IsStartupScriptRegistered(cstype, "PopupScript")) { String cstext = "alert('There was an issue writing to the database! Please check your connection string!!')"; cs.RegisterStartupScript(cstype, "PopupScript", cstext, true); } conn.Close(); } } else { } } } }All I was doing is testing the functionality to make sure it works, and it's fine. If I go this way, I'll enhance the code to do what I need it to do. Honestly, i think this is overkill. As I read more about SharePoint, I'm thinking the best thing to do is load the database with the VBA and have users do the following.
#1) Export database to desktop.
#2) Choose items from multiple ComboBoxes (bound to Table1), Run VBA script, and save data (write to Table2).
#3) Re-upload the exported database to SharePoint.
#4) Done.
Do you think this is the best practice? I'm just looking for the easiest way to help non-sophisticated users manage a process that is somewhat sophisticated.
I'll do some research on SharePoint Lists, as you mentioned. Maybe that's an option. I definitely want things to be synced. I guess the only thing I'm concerned about is having people export the database (to each user's desktop) and then having people re-upload the database and then there are concurrency issues, or perhaps someone uploading the database and wiping out someone else's changes. That would be a disaster!!
Any additional feedback would be great. I'm spending all day (Sunday) researching this so hopefully tomorrow (Monday) I can start doing the work on it.
Thanks again.
Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.
- Edited by ryguy72 Sunday, February 28, 2016 6:00 PM
-
That actually does look better now!
Before, I had several fields in one table; now I have one field per table, and this translates to several lists in SharePoint. I guess this is the way to go with it. Now, how do I actually use those lists to do something useful? I'd like to see several ComboBox objects on a form, so a user can select an item from each ComboBox, and then when each selection is made, run a Macro (non-VBA) and enter those records in a Access table in SharePoint? That's really all I need to do!
Thanks again.
Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.
