Insert Records Using from Text box to SQL database

Hi,

I am having three text box which accepts user data & insert that in sql database. But I am not able to do this , I think this is the simplest of all . Can somebody plz tell me how this can be done from scratch that is connection string & settings in web.config ? Plz guide using C#

Thanks

Regards,

-Sunny,

Here's a simple example using a SqlDataSource.

WEB.CONFIG

<connectionStrings><addname="NorthwindConnectionString"connectionString="Data Source=AMERUS-CW6GLINJ\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"providerName="System.Data.SqlClient" /></connectionStrings>

ASPX

CompanyName:<asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"insertcommand="INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@.CompanyName, @.Phone)"selectcommand="SELECT * FROM [Shippers]"><insertparameters><asp:controlparameter controlid="txtCompanyName" name="CompanyName" /><asp:controlparameter controlid="txtPhone" name="Phone" /></insertparameters></asp:sqldatasource>

CODE-BEHIND

protected void btnSubmit_Click(object sender, EventArgs e){SqlDataSource1.Insert();}

|||

One more thing Can you please tell me how can I auto update date & time in above code ?

I mean user need not to enter Date & time, it should be directly uploaded on database when user click on button & it should be visible to admin when the user has clicked the & entered the information.

Thanks You,

Regards,

-Sunny.

|||

Assuming you had a Parameter for your DateTime field, I'd simply set it within the SqlDataSource.Inserting event handler. Then set it to DateTime.Now.

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e){e.Command.Parameters["@.DateCreated"].Value = DateTime.Now;}

|||

I tried below code but its not inserting any date in my database

<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>" insertcommand="INSERT INTO [TryNow] ([Name],, [organization] ) VALUES (@.CompanyName, @.Phone, @.Org)" selectcommand="SELECT * FROM [TryNow]"> <insertparameters><%--<asp:ControlParameter Controlid="txtDate" Name="DateCreated" />--%> <asp:controlparameter controlid="txtName" name="CompanyName" /> <asp:controlparameter controlid="txtEmail" name="Phone" /> <asp:controlparameter controlid="txtOrg" name="Org" /> <asp:Parameter Name="DateCreated" /> </insertparameters> </asp:sqldatasource>

Code Behind :

protected void btnSubmit_Click(object sender, EventArgs e) { SqlDataSource1.Insert(); }protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e) { e.Command.Parameters["@.DateCreated"].Value = DateTime.Now; }

Can you please tell me where I am going wrong ?

Thank You,

Regards,

-Sunny.

|||

Dose this technique also closes the SQL connection or must you do something else?

|||

Yes this creates connection & I used same code, nothing else is been getting done here.

Please let me know why date field is not getting updated here. Thank you

Regards,

-Sunny.

|||

I can't really help you with the "Date and Time", but my question was if the created SQL connection was closed at the end of the INSERT or was there something we needed to do to make sure it closes?

PS: I'm not just lazy I just don't know how to use the "Date and Time". I did try, without succes.

|||how do i get this working i keep getting the following error Cannot insert the value NULL into column 'user_id', table 'cse.cse.priti_userid'; column does not allow nulls. INSERT fails.The statement has been terminated.although my database seems to fine...also where it says @.username do i need to set it as a variable?|||

The problem is pretty simple.
Your INSERT statement is trying to insert nothing (NULL) into "user_id" and in your database "user_id" dose not allow nulls ().
If user_id is a int and has identity set to yes then just ignore it in your INSERT statement. When a column is set to identity it's all automatic.

I'm not sure about the @.username question? When you are learning SQL go simple try 1 or 2 columns at a time and allow nulls, when you get the hang of it then you can start to normalize.

Did this help? let me know.

|||kinda. how do you get data from a textbox to insert into a database?|||

Well this is the code you have in this thread. It's kind of hard to break it down any more, let me see your source code of your ASPX page ASPX.CS page and your WEB.CONFIG page.

|||

the coding i have so far is as follows

front page

<%@. Page Language="C#" AutoEventWireup="true" CodeFile="register.aspx.cs" Inherits="Default2" %><!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 runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> username <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> password <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> email address<asp:TextBox ID="email" runat="server"></asp:TextBox><br /> security question <asp:TextBox ID="securityq" runat="server"></asp:TextBox> <br /> security answer <asp:TextBox ID="securitya" runat="server"></asp:TextBox><br /> <br /> <asp:Label ID="Label1" runat="server"></asp:Label><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="register" />  <br /> </div> </form></body></html>

backend

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
 protected void Page_Load(object sender, EventArgs e)
 {
 }
 protected void TextBox1_TextChanged(object sender, EventArgs e)
 {
 }
 protected void TextBox5_TextChanged(object sender, EventArgs e)
 {
 }
 protected void Button1_Click(object sender, EventArgs e)
 {
 string un;
 string pw;
 string mysql;
 un = TextBox1.Text;
 pw = TextBox2.Text;
 mysql = "Insert into priti_userid(user_id,password)" + " Values('" + un + "','" + pw + "')";
 Label1.Text = "Thanks for signing up";
 }
 protected void password_TextChanged(object sender, EventArgs e)
 {
 }
}

webconfig

<?xml version="1.0"?>
<!--
 Note: As an alternative to hand editing this file you can use the
 web admin tool to configure settings for your application. Use
 the Website->Asp.Net Configuration option in Visual Studio.
 A full list of settings and comments can be found in
 machine.config.comments usually located in
 \Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration>
<appSettings/>
<connectionStrings>
 <add name="cseConnectionString" connectionString="Data Source=SQLB1.webcontrolcenter.com;Initial Catalog=cse;Persist Security Info=True;User ID=cse;Password=salford"
 providerName="System.Data.SqlClient" />
 </connectionStrings>
<system.web>
<!--
 Set compilation debug="true" to insert debugging
 symbols into the compiled page. Because this
 affects performance, set this value to true only
 during development.
 -->
<compilation debug="true"/>
<!--
 The <authentication> section enables configuration
 of the security authentication mode used by
 ASP.NET to identify an incoming user.
 -->
<authentication mode="Windows"/>
<!--
 The <customErrors> section enables configuration
 of what to do if/when an unhandled error occurs
 during the execution of a request. Specifically,
 it enables developers to configure html error pages
 to be displayed in place of a error stack trace.
 <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
 <error statusCode="403" redirect="NoAccess.htm" />
 <error statusCode="404" redirect="FileNotFound.htm" />
 </customErrors>
 -->
</system.web>
</configuration
 

|||

The things you should consider is when you code use strong names so when your page is 1000 lines you are sure of what you are using (securityq = securityQuestionTextBox ).

When you post here remember to remove your passwords a specially if your database is live.

If you want to use the method suggested up here in this post your code should go like this:

No change to the web.config

ASPX

<form id="form1" runat="server">
<div>
username
<asp:TextBox ID="userNameTextBox" runat="server"></asp:TextBox><br />
password
<asp:TextBox ID="passwordTextBox" runat="server"></asp:TextBox><br />
email address<asp:TextBox ID="email" runat="server"></asp:TextBox><br />
security question
<asp:TextBox ID="securityQuestionTextBox" runat="server"></asp:TextBox>
<br />
security answer
<asp:TextBox ID="securityAnswerTextBox" runat="server"></asp:TextBox><br />
<br />
<asp:Label ID="statusLabel" runat="server"></asp:Label><br />
<br />
<asp:Button ID="sendButton" runat="server" Text="register"
onclick="sendButton_Click" /> <br />

<!--You need to add a datasource and have it match you Connection String in your web.config.-->

<asp:sqldatasource id="SqlDataSource" runat="server" connectionstring="<%$ ConnectionStrings:cseConnectionString%>"

insertcommand="INSERT INTO [priti_userid] ( [user_id], [password], [securityQuestion], [securityAnswer])
VALUES (@.userName, @.password, @.securityQuestion, @.securityAnswer)"

selectcommand="SELECT * FROM [priti_userid]">

<insertparameters>
<asp:controlparameter controlid="userNameTextBox" name="userName" />
<asp:controlparameter controlid="passwordTextBox" name="password" />
<asp:controlparameter controlid="securityQuestionTextBox" name="securityQuestion" />
<asp:controlparameter controlid="securityAnswerTextBox" name="securityAnswer" />
</insertparameters>

</asp:sqldatasource>


</div>
</form>

Code Behind

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public partialclass _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void sendButton_Click(object sender, EventArgs e)
{
SqlDataSource.Insert();
statusLabel.Text ="Thanks for signing up";
}
}

If you want to do the insert in C# well good luck that's where I am, when I make it work I'll let you know.

|||

thanks for the above...trying to get it working but it the sqldatasource.insert function will not work.'inserting is not supported by data source 'sqldatasource' unless insertcommand is specified' ?