Problem inserting and storing data from a web form to database

Discussion in 'ASP .Net' started by hfk0, Feb 21, 2006.

  1. hfk0

    hfk0 Guest

    Hi,

    I'm new to ASP.net, SQL Server and visual studio.net, and I'm having
    problem inserting and storing data from a web form to a SQL database.

    I created a simple ASP.NET web form, a simple SQL database, a database
    connection (using the SQlDataSource Web Control from the Toolbox), and
    created the following stored procedure in Visual Studio.Net 2005:


    CREATE PROCEDURE dbo.StoredProcedure1

    @BadgeN varchar(20),
    @FirstN varchar(25),
    @LastN varchar(25),
    @Creds varchar(20),
    @Dept varchar(50),
    @Org varchar(50),
    @Addr1 varchar(50),
    @Addr2 varchar(50),
    @Addr3 varchar(50),
    @City varchar(50),
    @State varchar(2),
    @ZIP varchar(5),
    @Country varchar(25),
    @Phone varchar(25),
    @Fax varchar(25),
    @Email varchar(50)

    AS

    INSERT INTO Participants
    (BadgeN, FirstN, LastN, Creds, Dept, Org,
    Addr1, Addr2, Addr3, City, State, ZIP, Country, Phone, Fax, Email)
    VALUES
    (@BadgeN,@FirstN,@LastN,@Creds,@Dept,@Org,@Addr1,@Addr2,@Addr3,@City,@State,@ZIP,@Country,@Phone,@Fax,@Email)

    RETURN



    Here's the source code for the web form:



    <form id="form1" runat="server">
    <div>

    First Name:
    <asp:TextBox ID="FirstN" runat="server"></asp:TextBox><br />
    Last Name:
    <asp:TextBox ID="LastN" runat="server"></asp:TextBox><br />
    Organization:
    <asp:TextBox ID="Org" runat="server"></asp:TextBox><br />
    Address:
    <asp:TextBox ID="Addr1" runat="server"></asp:TextBox><br />
    City:
    <asp:TextBox ID="City" runat="server"></asp:TextBox><br />
    State:
    <asp:DropDownList ID="State" runat="server">
    <asp:ListItem Selected="True">Select your state
    ....</asp:ListItem>
    <asp:ListItem>Alabama</asp:ListItem>
    <asp:ListItem>Alaska</asp:ListItem>
    <asp:ListItem>Alberta</asp:ListItem>
    <asp:ListItem>American Samoa</asp:ListItem>
    </asp:DropDownList><br />
    Phone:
    <asp:TextBox ID="Phone" runat="server"></asp:TextBox><br />
    Email:
    <asp:TextBox ID="Email" runat="server"></asp:TextBox><br />
    <br />
    <asp:Button ID="Button1" runat="server" Text="Submit" />
    <asp:Button ID="Button2" runat="server" Text="Clear" /><br />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:RegConnectionString %>"
    SelectCommand="StoredProcedure1"
    InsertCommandType="StoredProcedure"
    ProviderName="System.Data.SqlClient">
    <SelectParameters>
    <asp:parameter Name="BadgeN" Type="String" />
    <asp:ControlParameter ControlID="FirstN" Name="FirstN"
    PropertyName="Text" Type="String" />
    <asp:ControlParameter ControlID="LastN" Name="LastN"
    PropertyName="Text" Type="String" />
    <asp:parameter Name="Creds" Type="String" />
    <asp:parameter Name="Dept" Type="String" />
    <asp:ControlParameter ControlID="Org" Name="Org"
    PropertyName="Text" Type="String" />
    <asp:ControlParameter ControlID="Addr1" Name="Addr1"
    PropertyName="Text" Type="String" />
    <asp:parameter Name="Addr2" Type="String" />
    <asp:parameter Name="Addr3" Type="String" />
    <asp:ControlParameter ControlID="City" Name="City"
    PropertyName="Text" Type="String" />
    <asp:ControlParameter ControlID="State" Name="State"
    PropertyName="SelectedValue"
    Type="String" />
    <asp:parameter Name="ZIP" Type="String" />
    <asp:parameter Name="Country" Type="String" />
    <asp:ControlParameter ControlID="Phone" Name="Phone"
    PropertyName="Text" Type="String" />
    <asp:parameter Name="Fax" Type="String" />
    <asp:ControlParameter ControlID="Email" Name="Email"
    PropertyName="Text" Type="String" />
    </SelectParameters>
    </asp:SqlDataSource>



    </div>
    </form>

    When viewing the page in a browser, the data entered into the web form
    didn't get stored to the sQL database. I checked the connection and
    tested the stored procedure in Visual Studio (using the Query Builder
    feature in Visual Studio), and everything seemed to work fine.

    Am I missing some codes here?

    Can visual studio.net generate the code to do this, or do I manually
    have to write to code to do this. If it has to be done manually, could
    someone please post sample code on how to do this?

    My text box names are labeled the same as the field names in the
    database.

    Thanks in advance for any help.
     
    hfk0, Feb 21, 2006
    #1
    1. Advertising

  2. Don't you need a .ExecuteNonQuery() somewhere in there?


    Jeremy Reid
    http://hgtit.com
     
    blackstaronline.net, Feb 21, 2006
    #2
    1. Advertising

  3. hfk0

    hfk0 Guest

    Hi Jeremy,

    I'm sorry but I'm pretty new on this (I just started learning .net
    using visual studio for a few weeks only).

    Could you please clarify what to do? Did you mean to add this on the
    stored procedure?
     
    hfk0, Feb 21, 2006
    #3
  4. hfk0

    hfk0 Guest

    Hi Jeremy,

    I'm sorry but I'm pretty new on this (I just started learning .net
    using visual studio for a few weeks only).

    Could you please clarify what to do? Did you mean to add this on the
    stored procedure?
     
    hfk0, Feb 21, 2006
    #4
  5. Here you go, I took this from another post I was reading so use it for
    reference, don't rely on it....its not my code. ha ha

    Dim myConnection As New SqlConnection(strSQLConnectionString)
    Dim myCommand As New SqlCommand("sp_AddLeaver", myConnection)
    myCommand.CommandType = CommandType.StoredProcedure

    ....
    myCommand.Parameters.Add(your params)
    ....

    ....
    myConnection.Open()
    myCommand.ExecuteNonQuery()
    myConnection.Close()

    Try this search for
    http://www.google.com/search?q=usin...stored procedure to insert data in sql&tab=gw

    !!!Anyone else with more experience in SP please post some help.!!!

    Jeremy Reid
    http://hgtit.com
     
    blackstaronline.net, Feb 21, 2006
    #5
  6. hfk0

    hfk0 Guest

    OK...

    I did some research and came up with the following codes (placed in a
    separate file from the aspx page):

    Imports System.Data
    Imports System.Data.SqlClient

    Partial Class test2
    Inherits System.Web.UI.Page

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

    'this routine inserts the user info in the database
    Dim cnBKTest As New
    SqlConnection(System.Configuration.ConfigurationManager.AppSettings("RegConnectionString"))
    Dim cmdTest As New SqlCommand("spRegisterUser", cnBKTest)

    cmdTest.CommandType = CommandType.StoredProcedure

    cmdTest.Parameters.Add(New SqlParameter("@FirstN",
    SqlDbType.VarChar, 50))
    cmdTest.Parameters.Add(New SqlParameter("@LastN",
    SqlDbType.VarChar, 50))
    cmdTest.Parameters.Add(New SqlParameter("@Org",
    SqlDbType.VarChar, 50))
    cmdTest.Parameters.Add(New SqlParameter("@Addr1",
    SqlDbType.VarChar, 50))
    cmdTest.Parameters.Add(New SqlParameter("@City",
    SqlDbType.VarChar, 50))
    cmdTest.Parameters.Add(New SqlParameter("@State",
    SqlDbType.VarChar, 50))
    cmdTest.Parameters.Add(New SqlParameter("@Phone",
    SqlDbType.VarChar, 50))
    cmdTest.Parameters.Add(New SqlParameter("@Email",
    SqlDbType.VarChar, 50))
    cmdTest.Parameters("@FirstN").Value = "FirstN.text"
    cmdTest.Parameters("@LastN").Value = "LastN.text"
    cmdTest.Parameters("@Org").Value = "Org.text"
    cmdTest.Parameters("@Addr1").Value = "Addr1.text"
    cmdTest.Parameters("@City").Value = "City.text"
    cmdTest.Parameters("@State").Value = "State.text"
    cmdTest.Parameters("@Phone").Value = "Phone.text"
    cmdTest.Parameters("@Email").Value = "Email.text"

    cnBKTest.Open()
    cmdTest.ExecuteNonQuery()
    cnBKTest.Close()

    End Sub
    End Class

    Now I'm getting the following error:

    Server Error in '/ephtn' Application.
    The ConnectionString property has not been initialized.
    Description: An unhandled exception occurred during the execution of
    the current web request. Please review the stack trace for more
    information about the error and where it originated in the code.

    Exception Details: System.InvalidOperationException: The
    ConnectionString property has not been initialized.

    Source Error:

    Line 30: cmdTest.Parameters("@Email").Value = "Email.text"
    Line 31:
    Line 32: cnBKTest.Open()
    Line 33: cmdTest.ExecuteNonQuery()
    Line 34: cnBKTest.Close()


    Source File: c:\inetpub\wwwroot\ephtn\test2.aspx.vb Line: 32

    Stack Trace:

    [InvalidOperationException: The ConnectionString property has not been
    initialized.]
    System.Data.SqlClient.SqlConnection.PermissionDemand() +852067

    System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection
    outerConnection) +22

    System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
    outerConnection, DbConnectionFactory connectionFactory) +105
    System.Data.SqlClient.SqlConnection.Open() +111
    test2.Button1_Click(Object sender, EventArgs e) in
    c:\inetpub\wwwroot\ephtn\test2.aspx.vb:32
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String
    eventArgument) +107

    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
    eventArgument) +7
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
    sourceControl, String eventArgument) +11
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
    +33
    System.Web.UI.Page.ProcessRequestMain(Boolean
    includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
    +5102

    I don't understand why it's giving me the connection-related error
    message...I tested the connectionstring in the web.config file and it
    worked fine...

    Any help would be appreciated.
     
    hfk0, Feb 22, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?U2FrZXQgTXVuZHJh?=

    Temporarirly storing web form data to be inserted into Database ?

    =?Utf-8?B?U2FrZXQgTXVuZHJh?=, Mar 31, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    2,125
    Joseph Byrns
    Apr 1, 2005
  2. hfk0
    Replies:
    3
    Views:
    387
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    Feb 28, 2006
  3. geeteshss
    Replies:
    0
    Views:
    426
    geeteshss
    Jun 20, 2007
  4. Jonathan Wood
    Replies:
    1
    Views:
    522
    Jonathan Wood
    Jun 2, 2008
  5. Replies:
    0
    Views:
    121
Loading...

Share This Page