Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

inserting from form into sql database using c# 1

Status
Not open for further replies.
thefrstgatsby

thefrstgatsby

Technical User
May 8, 2004
113
CA
I have a simple form (text fields and pull down menus), and I want to insert the values from the form into an sql database.

Keep in mind I am very very new to ASP.NET.

What do I need to do to insert it. step by step would be very helpful, or if you know a resource that has this specific task, I'd appreciate it.

 
Sort by date Sort by votes
1. Make a Stored Procedure for the INSERT in your database (assuming SQL Server).

2. In your code, create a collection of SqlParameter objects that use the input from your controls ( via textBox1.Text, dropDownList1.SelectedValue, etc. ).

3. Create a SqlConnection and SqlCommand. Set the SqlCommand CommandType to StoredProcedure.

4. Add the SqlParameters to the SqlCommand object.

5. Open the SqlConnection.

6. Run the SqlCommand (via command.ExecuteNonQuery() or whatever).

7. Close the SqlConnection.

The code specifics are demonstrated here:


 
Upvote 0 Downvote
ok, I managed to get the program to connect to the sql database but how do I set up the insert statement? I have dropdown, radiobuttons and text fields
 
Upvote 0 Downvote
--stored procedure:

CREATE PROCEDURE whatever
@ParameterName1 int,
@ParameterName2 varchar(50)
AS

BEGIN

INSERT INTO SomeTableName(
field1,
field2 )
VALUES(
@ParameterName1,
@ParameterName1 )
END



//code:

SqlParameter pIntParameter = new SqlParameter( "@ParameterName1", SqlDbType.Int );
pIntParameter.Value = Convert.ToInt32( textBox1.Text );

SqlParameter pVarCharParameter = new SqlParameter( "@ParameterName2", SqlDbType.VarChar, 50 );
pVarCharParameter.Value = dropDownList1.SelectedValue;

mySqlCommand.Parameters.Add( pIntParameter );
mySqlCommand.Parameters.Add( pVarCharParameter );

mySqlCommand.ExecuteNonQuery();
 
Upvote 0 Downvote
I appreciate what you have done BoulderBum, but unfortunately, I still don't know what is going on in that code. I have seen fifteen thousand ways of doing it, but none seem to work, or perhaps I'm missing some steps. Here is that I have managed to create using vb studio.net 2003, this is a dumbed down version of my actual real form, but if you can help me with one field, then it's just a matter of applying that to the rest.

The aspx code is as follows:



<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="inserttest.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm1</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content=" </HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:TextBox id="newInsert" style="Z-INDEX: 101; LEFT: 54px; POSITION: absolute; TOP: 64px" runat="server"></asp:TextBox>
<asp:Button id="Button1" style="Z-INDEX: 102; LEFT: 83px; POSITION: absolute; TOP: 135px" runat="server"
Text="Button"></asp:Button>
</form>
</body>
</HTML>



And the behind code is:



using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace inserttest
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;
protected System.Data.SqlClient.SqlConnection sqlConnection1;
protected System.Web.UI.WebControls.TextBox newInsert;
protected System.Web.UI.WebControls.Button Button1;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Main", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("prim", "prim"),
new System.Data.Common.DataColumnMapping("inserted", "inserted")})});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT prim, inserted FROM Main";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
//
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = "INSERT INTO Main(inserted) VALUES (@insert); SELECT prim, inserted FROM Main";
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@inserted", System.Data.SqlDbType.VarChar, 10).Value=newInsert.Text);


//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "Server=[server];Database=surveyform;User ID=sa;Password=private;Trusted_Connection=False";
this.sqlConnection1.InfoMessage += new System.Data.SqlClient.SqlInfoMessageEventHandler(this.sqlConnection1_InfoMessage);
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void sqlConnection1_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{

}

private void Button1_Click(object sender, System.EventArgs e)
{
this.sqlInsertCommand1.Connection.Open();
this.sqlInsertCommand1.ExecuteNonQuery();
}
}
}


It connects to the database, but I get the following error:


The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.





Any ideas?
 
Upvote 0 Downvote
Try:

SqlParameter p = new System.Data.SqlClient.SqlParameter("@inserted", System.Data.SqlDbType.VarChar, 10);
p.Value = newInsert.Text;
this.sqlInsertCommand1.Parameters.Add( p );
 
Upvote 0 Downvote
I managed to get it to connect and insert, the prblem was, I was setting the value to the form value outside of the click button function, which obviously would set it to null, causing an error.

At any rate, I know you use .text to set the value from a text field, what do you use for pull down lists and radio buttons?
 
Upvote 0 Downvote
For a single radio button, you have the .Checked property.

For a RadioButtonList you can check the .SelectedItem (and its properties) as well as .SelectedIndex and .SelectedValue.

The DropDownList is the the same as RadioButtonList as they both derive from ListControl.
 
Upvote 0 Downvote
One last thing, when declaring new sqlparameter types, as in varchar in this example, what do I type in for int? I tried int, and it said that it was not a valid parameter

this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@field", System.Data.SqlDbType.VarChar, 10, "field"));
 
Upvote 0 Downvote
The error I get is when I try to submit in the webpage.

It says:

System.FormatException: Input string was not in a correct format.
 
Upvote 0 Downvote
Try

SqlParameter p = new System.Data.SqlClient.SqlParameter("@intParam", System.Data.SqlDbType.Int, 0, "field");
 
Upvote 0 Downvote
The problem was because I had the value set to "" if nothing was picked and for some reason it wouldn't allow you to enter nothing in the database int fields.

Another question, for some reason, the page is not accepting leftmargin, rightmargin, etc in the body tag. Any reasons as to why this may be happening?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Makumbi256
  • Locked
  • Question
How to make days vertical and grid view editable using asp.net vb
Replies
0
Views
844
Makumbi256
Makumbi256
chikwendu
  • Locked
  • Question
Mouse jumps to DropDownList when clicking a button
Replies
0
Views
160
chikwendu
chikwendu
G12Consult
  • Locked
  • Question
Javascript from Content Pages
Replies
0
Views
797
G12Consult
G12Consult
G12Consult
  • Locked
  • Question
Retrieve data from JSON
Replies
1
Views
842
gmmastros
gmmastros
AhmedAzizBarbary
  • Locked
  • Question
How to pass list of checkboxes not checked on page model razor page?
Replies
0
Views
1K
AhmedAzizBarbary
AhmedAzizBarbary

Part and Inventory Search

Sponsor

Back
Top