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.
Imports System.DataImports System.Data.SqlClientImports System.ConfigurationPublic 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
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!
ste5an
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).
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!