Checkboxlist Formview

[tsp1lrk72]

Hello- okay I'm trying out the formview... with the checkboxlist control, I can response.write the comma delimited string of values which is fine, how can i insert this to my SQL table?

 Protected Sub InsertButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim FormView As FormView = CType(FormView1, FormView)
        Dim cblist As CheckBoxList = CType(FormView.Row.FindControl("HchkEvents"), CheckBoxList)
        Dim i
        For i = 0 To cblist.Items.Count - 1
            If cblist.Items(i).Selected Then
                Response.Write(cblist.Items(i).Value.ToString + ",")
            End If

        Next



    End Sub

My SQL statement somethig like this:

Dim sSql As String
        sSQL = "INSERT INTO tblFPCHotel" _
              & "(HchkEvents)" _
               & "VALUES(what goes here?)"
        Dim cmd As New Data.SqlClient.SqlCommand(sSQL)
               Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        conn.Open()
        cmd.Connection = conn
        cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()
        cmd.CommandText = sSQL
        conn.Close()

I know it's simple- just missing something simple...

 

[jmeckley]

you should not be calling Response.Write. this is a low level API call. by the time your code executes a button.click event handler you should be working with server controls and templates to output the html, not response.write.

as to the sql statement you need to define the parameters.

var command = connection.CreateCommand();
command.CommandText = "insert [TABLE] ([column 1], [column 2]) values (@foo, @bar)";
var foo = command.CreateParameter();
foo.Name = "foo";
foo.Value = something;
command.Parameters.Add(foo);

var bar = command.CreateParameter();
bar.Name = "bar";
bar.Value = something;
command.Parameters.Add(bar);

command.ExecuteNonQuery();

you should also be properly disposing of connections and commands (any disposable really). research the "using" keyword and try/catch/finally blocks for more information.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[tsp1lrk72]

Thanks- I don't want to use Response.Write- I only did that so I could see if my values were there and they are. I just wanted to know how to insert those values (the comma delimited string)into a field in the database.

 

[jmeckley]

there are logging libraries you can use (log4net) to capture this information so you don't need to litter your code or UI with debug messages.

if you want to insert multiple rows via a command, you can do this

var command = connection.CreateCommand();
var index = 0;
forach(var item in checkboxlist.items)
{
  if (item.Checked = false) continue;

  var pName = string.format("p{0}", index++);
  command.CommandText += string.Format("insert [TABLE] ([column 1]) values (@{0});", pName);
  var p = command.CreateParameter();
  p.name = pName;
  p.Value = int.Parse(item.Value); //parse if necessary
  command.Parameters.Add(p);
}
command.ExecuteNonQuery();

that would send a batch of sql statements to the database that would look something like this

insert [TABLE] ([column 1]) values (@p0);
insert [TABLE] ([column 1]) values (@p1);
insert [TABLE] ([column 1]) values (@p2);
insert [TABLE] ([column 1]) values (@p3);

@p0 = 100;
@p1 = 23;
@p2 = 13;
@p3 = 42;

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[tsp1lrk72]

Thanks- not multiple rows, I want to insert the string into one field

 

[jmeckley]

same principle applies

var command = connection.CreateCommand();
command.CommandText = "insert [TABLE] ([column 1]) values (@foo)";

var value = string.Join(", ", checkboxlist
               .Items
               .Cast<ListItem>()
               .Where(item => item.Checked)
               .Select(item => item.Value)
               .ToArray());

var foo = command.CreateParameter();
foo.Name = "foo";
foo.Value = value;
command.Parameters.Add(foo);

command.ExecuteNonQuery();

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[tsp1lrk72]

Thanks- most appreciated!