Insert to SQL from form

GeeTee991100 · December 14th, 2010, 10:16 AM

Hi Imar,

I could not find this in your book so I hope you dont mind if I ask you a simple question.

How do I simply insert into a SQL database using an asp.net form. Just a straight form, no gridview, no listview or anything like that. I've tried a bunch of things recommended on other forums and none of them work.

I'm sorry to bother you with this question but would be very grateful if you could help me.

Thank you

Imar · December 14th, 2010, 10:32 AM

No, I don't mind, but it's easier for everyone if you post questions that are not directly related to the book in a more general ASP.NET category from this list: http://p2p.wrox.com/asp-net-3-5-436/ This way, others can join and learn from the discussion as well.

Anyway, you have a few options. One is to use LINQ to SQL (or Entity Framework or another ORM) which would work like this (LINQ to SQL example):

Code:

 
protected void Button1_Click(object sender, EventArgs e)
{
 using (PlanetWroxDataContext context = new PlanetWroxDataContext())
 {
  PhotoAlbum photoAlbum = new PhotoAlbum();
  photoAlbum.Name = TextBox1.Text;
  context.PhotoAlbums.InsertOnSubmit(photoAlbum);
  context.SubmitChanges();
 }
}

Alternatively, you can use plain SQL in combination with a SqlConnection and SqlCommand objects. This could be a good start: http://www.csharp-station.com/Tutori.../Lesson03.aspx although you may want to read a bit more on these subjects.

In the sequel to this book, Beginning ASP.NET 4 I added a section on this topic as I figured out it would be useful ;-)

Cheers,

Imar

GeeTee991100 · December 14th, 2010, 10:35 AM

Thank for the reply

GeeTee991100 · December 14th, 2010, 11:01 AM

Hi,

I'm using the straight forward connecton string followed by insert in VB but I'm having problems. I cant seem to get my connection string sright and some of the methods of the code I'm, vasing it on seem to have changed.

As you might have gathered I'm brand new to ASP.net and dont know where to look to trouble shoot it as all the forums I've looked at recommend the same code that doesn't seem to work for me. Would you mind having a look at my code to see if you can see anything glaringly wrong?

Code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="InputMain.aspx.vb" Inherits="InputMain" %>

<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Participants Input</title>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style2
        {
            width: 202px;
        }
        .style3
        {
            width: 134px;
        }
        .style4
        {
            width: 86px;
        }
        .style5
        {
            width: 223px;
        }
        .style6
        {
            width: 64px;
        }
    </style>
</head>
<body>
    <h1 align="left">Participants Input</h1>
    <form id="form1" runat="server">
    <div>
        <table class="style1">
            <tr>
                <td class="style3">
    
        <asp:Label ID="Label1" runat="server" Text="Project No" Width="150px"></asp:Label>
                </td>
                <td class="style2">
        <asp:TextBox ID="txtProjectNo" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
                        ControlToValidate="txtProjectNo" Display="Dynamic" 
                        ErrorMessage="Please enter a project number">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label2" runat="server" Text="First Name" Width="150px"></asp:Label>
                </td>
                <td class="style2">
        <asp:TextBox ID="txtFirstName" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
        <asp:Label ID="Label3" runat="server" Text="Surname" Width="100px"></asp:Label>
                </td>
                <td class="style5">
        <asp:TextBox ID="txtSurname" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style6">
        <asp:Label ID="Label4" runat="server" Text="Nick Name" Width="100px"></asp:Label>
                </td>
                <td>
        <asp:TextBox ID="txtNickName" runat="server" Width="200px" 
            style="text-align: left" Height="22px"></asp:TextBox>
                </td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" 
                        ControlToValidate="txtFirstName" ErrorMessage="Please enter first name">*</asp:RequiredFieldValidator>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" 
                        ControlToValidate="txtSurname" ErrorMessage="Please enter surname">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label15" runat="server" Text="Address" Width="150px"></asp:Label>

                </td>
                <td class="style2">
        <asp:TextBox ID="txtAddress" runat="server" Width="200px" Height="114px" 
            TextMode="MultiLine"></asp:TextBox>

                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator11" runat="server" 
                        ControlToValidate="txtAddress" ErrorMessage="Please enter address">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label5" runat="server" Text="************" Width="150px"></asp:Label>
                </td>
                <td class="style2">
                    <asp:DropDownList ID="ddlSex" runat="server">
                        <asp:ListItem>Male</asp:ListItem>
                        <asp:ListItem>Female</asp:ListItem>
                    </asp:DropDownList>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator12" runat="server" 
                        ControlToValidate="ddlSex" ErrorMessage="Please enter male or female">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label6" runat="server" Text="Ethnic Origin" Width="150px"></asp:Label>
                </td>
                <td class="style2">
        <asp:TextBox ID="txtEthnicOrigin" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" 
                        ControlToValidate="txtEthnicOrigin" 
                        ErrorMessage="Please enter ethnic origin">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label7" runat="server" Text="DOB" Width="150px"></asp:Label>
                </td>
                <td class="style2">
        <asp:TextBox ID="txtDOB" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" 
                        ControlToValidate="txtDOB" ErrorMessage="Please enter valid date">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label9" runat="server" Text="Telephone No" Width="150px"></asp:Label>
                </td>
                <td class="style2">
        <asp:TextBox ID="txtTelephoneNo" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" 
                        ControlToValidate="txtTelephoneNo" ErrorMessage="Please enter ethnic origin">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label10" runat="server" Text="Emergency Contact" Width="150px"></asp:Label>
                </td>
                <td class="style2">
        <asp:TextBox ID="txtEmergencyContact" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" 
                        ControlToValidate="txtEmergencyContact" ErrorMessage="Please enter emergency contact">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label11" runat="server" Text="Emergency Number" Width="150px"></asp:Label>
                </td>
                <td class="style2">
        <asp:TextBox ID="txtEmergencyNumber" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" 
                        ControlToValidate="txtEmergencyNumber" ErrorMessage="Please enter emergency phone number">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label12" runat="server" Text="Disability" Width="150px"></asp:Label>
                </td>
                <td class="style2">
        <asp:TextBox ID="txtDisability" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator9" runat="server" 
                        ControlToValidate="txtDisability" ErrorMessage="Please enter disability">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
        <asp:Label ID="Label13" runat="server" Text="School/College/Other" 
            Width="150px"></asp:Label>      
                </td>
                <td class="style2">
        <asp:TextBox ID="txtSclClgOth" runat="server" Width="200px"></asp:TextBox>
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator10" runat="server" 
                        ControlToValidate="txtSclClgOth" ErrorMessage="Please enter School/College/Other">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style3">
                    &nbsp;</td>
                <td class="style2">
        <asp:Button ID="Button1" runat="server" Text="Submit" style="height: 26px" />
                </td>
                <td class="style4">
                    &nbsp;</td>
                <td class="style5">
                    &nbsp;</td>
                <td class="style6">
                    &nbsp;</td>
                <td>
                    <asp:ValidationSummary ID="ValidationSummary1" runat="server"                         
                        HeaderText="Please correct the following errors before you press the Send button" 
                        ShowMessageBox="True" ShowSummary="False" />
                </td>

            </tr>
        </table>  
    </div>
    </form>
</body>
</html>

I define the two imports at the top but it doesnt seem to pick them up. So in my code behind I still have to define the whole method string for sqlconnection and thats where I get stuck.

Code:

Partial Class InputMain
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' Connection setup

        'Dim strConnection As String = System.Configuration.ConfigurationManager.AppSettings("ParticipantsConnectionString")


        Dim objConnection As New System.Data.SqlClient.SqlConnection
        Dim strConnection As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=f:\WebSites\App_Data\participants.mdf;Integrated Security=True;User Instance=True"
        Dim strSQL As String = "INSERT INTO PersonTable(IDNumber, FirstName, Surname, NickName, [M/F], EthnicOrigin, DOB, Age, Address, TelephoneNumber, EmergencyContact, EmergencyNumber, Disability, [School/College/Other], [Travel/Other]) " & _
                                "VALUES (txtProjectNo, txtFirstName, txtSurname, txtNickName, txtSex, txtEthnicOrigin, txtDOB, txtAge, txtAddress, txtTelephoneNumber, txtEmergencyContact, txtEmergencyNumber, txtDisability, txtSclClgOth, null)"

        Dim dbComm As New System.Data.SqlClient.SqlCommand(strSQL, objConnection)

        Dim iID As Integer

        Try
            objConnection.Open()
            iID = dbComm.ExecuteScalar()
        Catch ex As Exception
            Response.Write(ex.Message)
            Response.End()
        Finally
            'If objConnection.State = ConnectionState.Open Then
            objConnection.Close()
            'End If
        End Try

        Response.Write("The ID of the new record is: " & iID.ToString())


    End Sub

End Class

I understand that I'm not defining my connection string properly but I dont know what it should be. Sorry to bother you and I will in future post general queries on the other boards but I'm a bit stuck at the moment.

Imar · December 14th, 2010, 11:24 AM

Hi there,

The two imports at the top of the page apply to the markup section only. You need them again as Imports statements in the Code Behind (and you can remove them from the markup as they are not used).

But what's the error you're getting / where do you get stuck exactly? There are quite some problems in your code (invalid SQL statement that never gets the values from the TextBox controls, open for SQL Injection attacks, incorredct code to retrieve the new ID and a bit more), but let's start at the beginning...

Imar

GeeTee991100 · December 14th, 2010, 11:51 AM

That version of the code is totally screwed up cuz I was trying to define my connection string. That is now fine and as you predicted I'm not getting the correct values from the textboxes into my SQL but I know why that is the case.

Thank you for your help.

Imar · December 14th, 2010, 11:54 AM

>> but I know why that is the case.

You do, or you don't?

Do yo get an error and if so which one?

Imar

GeeTee991100 · December 14th, 2010, 12:02 PM

Quote:

Originally Posted by Imar

>> but I know why that is the case.

You do, or you don't?

Do yo get an error and if so which one?

Imar

I get invalid column name on every column and I would presume that is because I'm hardcoding the column name string and not building it bit by bit in vb.

Imar · December 14th, 2010, 12:26 PM

I think the column names are fine; it's the values that are causing problems. Consider this:

Code:

Dim strSQL As String = "INSERT INTO PersonTable(IDNumber, FirstName, Surname, NickName, [M/F], EthnicOrigin, DOB, Age, Address, TelephoneNumber, EmergencyContact, EmergencyNumber, Disability, [School/College/Other], [Travel/Other]) " & _
"VALUES (txtProjectNo, txtFirstName, txtSurname, txtNickName, txtSex, txtEthnicOrigin, txtDOB, txtAge, txtAddress, txtTelephoneNumber, txtEmergencyContact, txtEmergencyNumber, txtDisability, txtSclClgOth, null)"

What do you think you get when this code executes? The *value* of the text box controls? Nope; for two reasons.

First, the IDs of the controls are hard coded in the string. So you end up with the literal text:

"VALUES (txtProjectNo, txtFirstName ..."

To fix this, concatenate the *values* of the TextBox controls to your SQL statement (shown later)

Secondly, you're using the controls itself instead of their Text values.

A better (but far from perfect) solution would be:

Code:

Dim strSQL As String = "INSERT INTO PersonTable(IDNumber, FirstName)" & _
"VALUES ({0}, '{1}')"
strSql = string.Format(strSql, txtProjectNo.Text, txtFirstName.Text)

This way, the Text properties of the controls end up in the SQL statement. I also enclosed the value for first name in quotes, as that's required for text columns.

But why is this far from perfect? It opens your application for SQL injection. Assume for the first name I enter:

'); DROP TABLE USERS; --

Your SQL statement would end up as:

INSERT INTO PersonTable(IDNumber, FirstName) VALUES (1, ''); DROP TABLE USERS; --

Poof, you just lost your User table. Ouch....

So, instead of hard coding user input like this, use SQL parameters instead:

http://www.csharp-station.com/Tutori.../Lesson06.aspx
http://www.java2s.com/Code/VB/Databa...sqlcommand.htm

Hope this helps you one step further; if not, feel free to post follow up questions.

Cheers,

Imar

Imar · December 14th, 2010, 12:31 PM

One other thing: avoid using special characters such as spaces and slashes in column names as you'll run into lots of problems. So, instead of M/F, use something like Gender and instead of Travel/Other use something like Preference (or whatever this column represents).

Cheers,

Imar