Problem inserting and storing data from a web form to database

  • Thread starter Thread starter hfk0
  • Start date Start date
H

hfk0

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.
 
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?
 
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?
 
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
 
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.
 
Back
Top