Solved

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

Posted on 2016-09-07
5
42 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 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
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
Comment Utility
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 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now