By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 2,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

Problem inserting and storing data from a web form to database

P: n/a
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,@Fa x,@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.

Feb 21 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Don't you need a .ExecuteNonQuery() somewhere in there?
Jeremy Reid
http://hgtit.com

Feb 21 '06 #2

P: n/a
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?

Feb 21 '06 #3

P: n/a
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?

Feb 21 '06 #4

P: n/a
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=using...+in+sql&tab=gw

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

Jeremy Reid
http://hgtit.com

Feb 21 '06 #5

P: n/a
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.ConfigurationMa nager.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.PermissionDema nd() +852067

System.Data.SqlClient.SqlConnectionFactory.Permiss ionDemand(DbConnection
outerConnection) +22

System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(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.RaisePostBackEven t(String
eventArgument) +107

System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection 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.

Feb 22 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.