Booking form

Will · May 5th, 2009, 07:03 AM

I am building a booking form and I need the information in the booking form written into the database but I am unsure as to how to go about this.

I have several fields including textboxes, a calendar control to pick the date and drop down lists. The complete booking form needs to be written to several different tables in my database.

Could anyone help me out by providing links to relevant information, page references to either professional asp.net or beginning asp.net books or maybe roughly outline what I need to do?

I am familiar with the steps laid out in beginning asp.net to insert update and delete items from the database however I am unsure how to proceed as all I want to do is take information from the form and put it into the database- that isn't directly delt with in the book (not that I could see anyway).

I think my main confusion is that instead of working with something like the gridview or listview I am looking to do this programmaticaly in the code behind. With ADO.NET I think?

My code preference is vb.net.

Thanks alot in advance if you take the time to help me.

Will · May 6th, 2009, 04:59 AM

Ha after about a day of googling I realised I would need an INSERT statement. But I am still having abit of trouble making it work.

So hear is my stab at it..

Code:

Imports System.Data.SqlClient
Partial Class Bookings
    Inherits System.Web.UI.Page

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        'declare string
        Dim strFirstName As String

        'Assign textbox value to string variable
        strFirstName = txtFirstName.Text

        'Create a connection object
        Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database.mdf;Integrated Security=True;User Instance=True")

        'create a command object
        Dim sqlComm As New SqlCommand()

        'INSERT statement?
        sqlComm.CommandText = "INSERT INTO Clients (FirstName) VALUES (strFirstName);"
        sqlComm.CommandType = Data.CommandType.Text
        sqlComm.Connection = sqlConn

        'Open the connection
        sqlConn.Open()


    End Sub
End Class

I know that I still need to close the connection but not sure when? Is it directly after I open it?

It would also appear that although I am not getting any errors the information I put into the one textbox isn't being written to the database. As it is just one textbox text at the moment for practice I have allowed nulls in the rest of the table so I don't think thats the problem.

The next dilema I have is that I need to insert different bits of information to different tables. Do I just create different sqlcommands for each different insertion and put them all in the same button click event handler?

And the last problem that I can think of right now is maybe my method for entering child names. Currently I am thinking of having a dropdownlist which the user will use to select the amount of children they want to book in I will then programmatically add in the specified amount of textboxes; ie if a user selects 3 children then 6 textboxes will appear one for each childs first and last name. Is this a good way of doing this or is there a better one?

Again thanks alot for any help in advance you can provide.

Will · May 6th, 2009, 05:07 AM

Ha to me again. Ok I realised pretty much after I posted that that I hadn't actually told it to execute the command so now I added in:

Code:

sqlConn.ExecuteNonQuery()

sqlConn.Close()

And it works!!..sort of.

My problem now is that instead of inserting the value of the object strFirstName it just inserts strFirstName into the table..go figure.

Now I know that it is because I had the strFirstName object in " ' ' " so it just inserted in the text value so how would I get it to insert the value of the object?

Will · May 6th, 2009, 05:16 AM

And again I just realised If I am going to have to execute multiple sql commands would it be better to store them in a class file in the App_code folder?

sorry for the quick succession of posts but wrtiting it all out helps me think about what I actually have to do better. Plus it might help someone else with the same problem I guess.

Will · May 6th, 2009, 01:44 PM

Ok so thanks to a hint from gibianchi (sorry if I spelt that wrong) I learnt that I should use a parameterised query so I now I have working code which is below:

Code:

'declare variables
        Dim strFirstName As String
        Dim strLastName As String
        Dim strEmailAddress As String

        'Assign value to string variable
        strFirstName = txtFirstName.Text
        strLastName = txtLastName.Text
        strEmailAddress = txtEmail.Text


        'Create a connection object
        Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ChillOutOfSchool.mdf;Integrated Security=True;User Instance=True")

        'create a command object
        Dim sqlComm As New SqlCommand()

        'INSERT statement?
        sqlComm.CommandText = "INSERT INTO Clients (FirstName, LastName, EmailAddress)VALUES (@FirstName, @LastName, @EmailAddress);"
        sqlComm.Parameters.AddWithValue("@FirstName", strFirstName)
        sqlComm.Parameters.AddWithValue("@LastName", strLastName)
        sqlComm.Parameters.AddWithValue("@EmailAddress", strEmailAddress)

        'sqlComm.CommandType = Data.CommandType.Text
        sqlComm.Connection = sqlConn

        'Open the connection
        sqlConn.Open()

        sqlComm.ExecuteNonQuery()

        sqlConn.Close()

So anyway that works now thought I would post for completeness(sp?)