Insert into table from two listbox controls
I have a requirement to insert user_id and quiz_iz into a table column, I have the below code which works for inserting multiple selected values from one control into a table column, but I cannot figure out how to insert in 2 fields from values listed on both controls when hitting Submit,.
I am using odbc connection to mysql, thats were the table i need to insert is located....
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
using System.Data;
using System.Data.Odbc;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
private void InsertRecords(StringCollection sc, StringCollection sc2)
{
string ConnectionString = @"driver={MySQL ODBC 5.1 Driver};server=localhost;database=db_mydb;uid=;pwd=;";
OdbcConnection conn = new OdbcConnection(ConnectionString);
StringBuilder sb = new StringBuilder(string.Empty);
StringBuilder sb2 = new StringBuilder(string.Empty);
foreach (string item in sc)
{
const string sqlStatement = "INSERT INTO jos_jquarks_users_quizzes (quiz_id,user_id) VALUES";
sb.AppendFormat("{0}('{1}'); ", sqlStatement, item);
sb2.AppendFormat("{0}('{1}'); ", sqlStatement, item);
}
try
{
conn.Open();
OdbcCommand cmd = new OdbcCommand(sb.ToString(), conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
Page.ClientScript.开发者_JAVA技巧RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
StringCollection sc = new StringCollection();
StringCollection sc2 = new StringCollection();
foreach (ListItem item in ListBox1.Items)
{
if (item.Selected)
{
sc.Add(item.Text);
}
}
foreach (ListItem item in ListBox2.Items)
{
if (item.Selected)
{
sc2.Add(item.Text);
}
}
InsertRecords(sc , sc2);
}
}
You should use nested foreach maybe.
foreach (string item in sc)
{
foreach (string item in sc2)
{
insert sc and sc2
}
}
精彩评论