Solved

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

Posted on 2016-09-07
5
55 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

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 34

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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…

739 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