Executing several SQL INSERT statement in ASP.NET VB.NET
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.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Refactoring is the key. Extract the SQL saving into a separate method. Also use parameterized command to avoid SQL injection.
E.g.
Also name your controls before using them. And, of course, add error handling.
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
Also name your controls before using them. And, of course, add error handling.
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!
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!
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.
2. Yes.
3. Take a look at the SelectedItem property.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial