[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Updating an arraylist to SQL Database

Posted on 2006-06-05
5
Medium Priority
?
316 Views
Last Modified: 2010-04-17
I had posted here last week regarding an issue "VeryNiceMan" had helped me.

http://www.experts-exchange.com/Programming/Q_21864141.html 

NOW i'm stuck trying to get this update a database.  For some reason my eyes just aren't seeing where to begin.

I've tried (and successfully i think) gotten things into an arraylist, but it's not updating the database as i would like to see it.  

Here's some of my "updated" code.

-- This is where i dynamically create controls based on a checkboxlist of 13 items.  This part seems to be working as intended.

Private Sub CreateMyControls()
        Dim i As Integer
        For i = 0 To cblPatientGoals.Items.Count - 1
            If cblPatientGoals.Items(i).Selected Then
                Dim myLabel As New Label
                myLabel.Text &= cblPatientGoals.Items(i).Text

                Dim myRadioButton As New RadioButtonList
                myRadioButton.Items.Add(New ListItem("Met", "Met"))
                myRadioButton.Items.Add(New ListItem("Unmet", "Unmet"))
                myRadioButton.RepeatDirection = RepeatDirection.Horizontal
                Dim myTextBox As New TextBox
                myTextBox.Style("Width") = "350px"

                myRadioButton.ID = "cGoals" & (i+1)
                myTextBox.ID = "tGoals" & (i+1)
                myLabel.ID = "lGoals" & (i+1)

                Panel1.Controls.Add(New LiteralControl("<tr><td>"))
                Panel1.Controls.Add(myLabel)
                Panel1.Controls.Add(New LiteralControl("</td>"))
                Panel1.Controls.Add(New LiteralControl("<td>"))
                Panel1.Controls.Add(myRadioButton)
                Panel1.Controls.Add(New LiteralControl("</td>"))
                Panel1.Controls.Add(New LiteralControl("<td>"))
                Panel1.Controls.Add(myTextBox)
                Panel1.Controls.Add(New LiteralControl("</td></tr>"))
            End If
        Next
    End Sub



--This is the code that (obviously) executes when the Submit Button is pushed.  This is where i need some direction.

In my arraylist where i write out "Response.Write(CStr(al.Item(x)))" i can see the items i want to update, but it's only updating one of the 2 items in the checkboxlist 6 times.  I'll paste more info as needed.  

Sub Button1_Click(Sender as Object, E as Eventargs)
    Dim i As Integer
    Dim t As TextBox
    Dim l As Label
    Dim c As RadioButtonList
      Dim al As New ArrayList()

    'For x = 0 To cblPatientGoals.Items.Count - 1
      'If cblPatientGoals.Items(x).Selected Then
      
      For i = 0 To Panel1.Controls.Count - 1        
            
            If Panel1.Controls(i).GetType Is GetType(Label) Then
        l = CType(Panel1.Controls(i), Label)
        'Response.Write(l.ID & "-->" & l.Text.Trim & "<BR>")
            al.Insert(0, l.text.trim)
        End If
            
            If Panel1.Controls(i).GetType Is GetType(RadioButtonList) Then
        c = CType(Panel1.Controls(i), RadiobuttonList)
        'Response.Write(c.ID & "-->" & c.SelectedItem.Text & "<BR>")
            al.Insert(1, c.SelectedItem.Text)
            'Response.Write(c.ID & "-->" & c.Items(0).Selected & "-->" & c.Items(1).Selected & "<BR>")
        End If
            
            If Panel1.Controls(i).GetType Is GetType(TextBox) Then
        t = CType(Panel1.Controls(i), TextBox)
        'Response.Write(t.ID & "-->" & t.Text.Trim & "<BR>")
            al.Insert(2, t.text.trim)
        End If        
        
       Next
      
      Dim x As Integer  
      For x = 0 To al.Count - 1
       Response.Write(CStr(al.Item(x)))
      
      Dim myConnection as New SQLConnection(ConfigurationSettings.appSettings("connString"))
    myConnection.Open()
      Dim myInsert as String = "INSERT INTO tblEducationGoals (goal_id, goal_MetUnmet, goal_text)" & _
      "VALUES ('" & al.item(0) & "', '" & al.item(1) & "', '" & al.item(2) & "')"
      Dim Cmd as New SQLCommand(MyInsert, myConnection)
      cmd.ExecuteNonQuery
      MyConnection.Close()
      
      
      Next
End Sub


Thanks again.

dc.
0
Comment
Question by:isdc
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
lojk earned 1000 total points
ID: 16840802
Ok apologies if slightly inaccurate, read my code carefully, it should make sense (it *should* be copy/pasteable)



First comment i would make is to change your CheckType commands

If TypeOf(Control) is ControlType Then ....

always seems to work quite nicely for me. I dont think you need all the gettype chatter, not in VB at least...

Also see my assist to

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21874199.html

Here i use a collection to add a group of objects for later reference. (it is possible to make a collection of classes strongly typed but dont worry about that for now....)

Hmm your adding control section is odd, you add multiples of several controls but then never allow for yourself to (easily) determine which is which or indeed check which you are accessing.

In your section that says

                myRadioButton.ID = "cGoals" & (i+1)
                myTextBox.ID = "tGoals" & (i+1)
                myLabel.ID = "lGoals" & (i+1)


i would say do this

                myRadioButton.ID = "cGoals" & (i+1)
                myTextBox.ID = "tGoals" & (i+1)
                myLabel.ID = "lGoals" & (i+1)
                myRadioButton.tag = (i+1)
                myTextBox.tag = (i+1)
                myLabel.tag = (i+1)




Rather than use an arraylist I would recommend that you declare a class (outside of your form class!) like this

public class clsResult
GoalID as integer
GoalMet as String
GoalText as String
End Class

then

Sub Button1_Click(Sender as Object, E as Eventargs)
    Dim i As Integer
    Dim t As TextBox
    Dim l As Label
    Dim c As RadioButtonList
    dim ResultsCollection as new collection

For o = 0 To cblPatientGoals.Items.Count - 1 ' you need to go through every control, this many times
dim oResult as new clsResult
     For i = 0 To Panel1.Controls.Count - 1        
         
          If typeof(Panel1.Controls(i)) is Label  Then
        l = CType(Panel1.Controls(i), Label)
      if l.tag=o then
      oResult.goalid= l.text.trim
      end if
        End If
         
          If typeof(Panel1.Controls(i)) is RadioButtonList Then
        c = CType(Panel1.Controls(i), RadiobuttonList)

      if c.tag=o then    
      oresult.GoalMet= c.SelectedItem.Text
      end if
              End If
         
          If typeof(Panel1.Controls(i)) Is  TextBox Then
        t = CType(Panel1.Controls(i), TextBox)

      if t.tag=o then
           oresult.GoalText= t.text.trim
      end if
        End If        
       
      Next '''' Add a breakpoint to this line and put a watch on the oResult Variable

''' So we checked each control to make sure it was related and used a class to store the values we need
'now lets add that result to our collection
     ResultsCollection.add( oResult )
next

       
     For x as integer = 0 To resultscollection.Count - 1 'notice my shorthand for dimming a counter
      dim tUpdateResult as clsResult
      tUpdateResult =ctype(resultscollection.item(x),clsResult)
     
     Dim myConnection as New SQLConnection(ConfigurationSettings.appSettings("connString"))
    myConnection.Open()
     Dim myInsert as String = "INSERT INTO tblEducationGoals (goal_id, goal_MetUnmet, goal_text)" & _
     "VALUES ('" & tUpdateResult.goalid & "', '" & tUpdateResult.GoalMet & "', '" & tUpdateResult.GoalText & "')"
     Dim Cmd as New SQLCommand(MyInsert, myConnection)
     cmd.ExecuteNonQuery
     MyConnection.Close()
     
     
     Next
End Sub


0
 
LVL 9

Expert Comment

by:lojk
ID: 16840809
sorry guess that shoudl say....

public class clsResult
GoalID as STRING '''' at no point did you say it was an integer, just assumed!
GoalMet as String
GoalText as String
End Class
0
 

Author Comment

by:isdc
ID: 17058273
Yes, apologies on my lack of attentiveness to this. Involvement in other things prevented me from trying this.  I accept this as well and i thank everyone for the assistance with this.  I am happy to see a board that is monitored so closely.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question