Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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 …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

28 Experts available now in Live!

Get 1:1 Help Now