Solved

Executing several SQL INSERT statement in ASP.NET VB.NET

Posted on 2016-09-07
5
52 Views
Last Modified: 2016-09-09
Hello!

By pressing a button on the my page, I am executing several SQL Insert statement to save data from my unbounded form to SQL Database. The data is fed from a date control and several combo boxes feed by SQL server. I am very new to ASP.NET and I am sure my code is not optimal at all. So here are my questions and problems:

1. Is there a better way to execute several Inserts (In this example I have included only two but I will have about 15 at the end)?
2. Regardless what I select from the combo-boxes. Only the top record from each combo box is selected and saved to SQL - A quick search I think this has to do with PostBack. But I have no idea ho to fix it and where.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class MeetingScheduler
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub SaveSchedule_Click(sender As Object, e As EventArgs) Handles SaveSchedule.Click

        Dim constring As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
            Dim con As New SqlConnection(constring)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.CommandType = System.Data.CommandType.Text

            If Me.MeetingDate.Text = "" Then
                MsgBox("Please select the date and try again", vbExclamation, "Attention!")
                Me.MeetingDate.Focus()
                Exit Sub
            End If

        If Not String.IsNullOrEmpty(Me.ComboBox1.Text) Then
                cmd.CommandText = "INSERT INTO MEETINGSCHEDULE (MeetingDate, MeetingID, Assignment, PersonName, Sort) VALUES ('" & MeetingDate.Text.ToString & "', 5 , 'Chairman', '" & ComboBox1.Text & "', 2)"
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End If

        If Not String.IsNullOrEmpty(Me.ComboBox2.Text) Then
                cmd.CommandText = "INSERT INTO MEETINGSCHEDULE (MeetingDate, MeetingID, Assignment, PersonName, Sort) VALUES ('" & MeetingDate.Text.ToString & "', 2 , 'Presenter', '" & ComboBox2.Text & "', 5)"
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End If

Open in new window

0
Comment
Question by:Cobra967
  • 3
  • 2
5 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 41790339
Refactoring is the key. Extract the SQL saving into a separate method. Also use parameterized command to avoid SQL injection.

E.g.
Public Class MeetingScheduler
    Inherits System.Web.UI.Page

    Protected Sub SaveSchedule_Click(sender As Object, e As EventArgs) Handles SaveSchedule.Click
        If Me.MeetingDate.Text = "" Then
            MsgBox("Please select the date and try again", vbExclamation, "Attention!")
            Me.MeetingDate.Focus()
            Exit Sub
        End If

        Dim MeetingDateAsDate As Date = MeetingDateAsDate = CDate(MeetingDate.Text)
        Dim PersonName As String

        Using con As SqlConnection = GetSqlConnection()
            If Not String.IsNullOrEmpty(Me.ComboBox2.Text) Then
                PersonName = ComboBox1.Text
                Save(con, MeetingDateAsDate, 5, "Chairman", PersonName, 2)
            End If
            If Not String.IsNullOrEmpty(Me.ComboBox2.Text) Then
                PersonName = ComboBox2.Text
                Save(con, MeetingDateAsDate, 2, "Presenter", PersonName, 5)
            End If
        End Using
    End Sub

    Private Shared Function GetSqlConnection() As SqlConnection
        Dim constring As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Dim con As New SqlConnection(constring)
        Return con
    End Function

    Private Sub Save(con As SqlConnection, MeetingDate As Date, MeetingID As Long, Assignment As String, PersonName As String, Sort As Long)
        Using cmd As New System.Data.SqlClient.SqlCommand
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = "INSERT INTO MEETINGSCHEDULE (MeetingDate, MeetingID, Assignment, PersonName, Sort) " &
                "VALUES (@MeetingDate, @MeetingID, @Assignment, @PersonName, @Sort);"
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@MeetingDate", MeetingDate)
            cmd.Parameters.AddWithValue("@MeetingID", MeetingID)
            cmd.Parameters.AddWithValue("@Assignment", Assignment)
            cmd.Parameters.AddWithValue("@PersonName", PersonName)
            cmd.Parameters.AddWithValue("@Sort", Sort)
            con.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub
End Class

Open in new window


Also name your controls before using them. And, of course, add error handling.
0
 

Author Comment

by:Cobra967
ID: 41790586
Thank you very much ste5an for the help. However, I think I still some refinement in the coding:

1. On line 11, did you meant to say: Dim MeetingDateAsDate As Date = CDate(MeetingDate.Text)?
2. Should there be a con.Close() statement after line 44? (I am getting an error message when compiling without con.Close()
3. The code still saves in SQL the first item of the list from each combo box instead of what I select from each one.

Thank you!
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41790910
1. Yes. The reason is that you need/should validate input before using it. As it is named date it should be using the correct data type, thus the conversion. (Typo).

2. Yes.

3. Take a look at the SelectedItem property.
0
 

Author Comment

by:Cobra967
ID: 41791500
Fort item 3, it works just fine once setting the ComboBox properties for the following items: TextField=PersonName and ValueField=PersonName
0
 

Author Closing Comment

by:Cobra967
ID: 41791503
Thank you very much!
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 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