HOW WE CAN MAKE STRING ARRAY SPLIT WITH USE OF CHARINDEX FUNCTION OF SQL IN STORED PROCEDURE WHEN WE MAKE LOGIC IN ASP.NET

STEP 1: Make a New Web Site In Visual Studio 2008 As Given Below In Picture:


Image01STEP 2: Now Make a new database and Keep his name dbemployee or if already composed then add him in your serverexplore as given below:

OrientProgrammer

Step 3: Now Make connection string in web config file into connection string section

<ConnectionStrings><add name=”Aryan” ConnectionString=”server=servername;database=databasename;uid=sa;pwd=123″/></ConnectionStrings>

If Password is implemented on your SQL SERVER at installation time then define in this section other wise you have no need of it’s.

STEP 4: Now Make A New Table with TBEMP Name and Define Two Columns In his.

First Column =Empno –Int and set It’s Identity true

Second Column=Empname –varchar(50) and Now Save your Table

Also Remeber to Set Empno Identity Is True then Save to Table

STEP 5: MAKE A NEW STROED PROCEDURE IN WHICH WILL MAKE SPLITING LOGIC AND WRITE IN HIS AS GIVE BELOW

CREATE PROC DBO.Proc_InsSplitValues

(

@STRING VARCHAR(8000),

@DELIMITER CHAR(1)

)

AS

BEGIN

DECLARE @IDX INT

DECLARE @SLICE VARCHAR(8000)

SELECT @IDX =1

IF (LEN(@STRING)<1) OR @STRING IS NULL RETURN

WHILE @IDX!=0

BEGIN

SET @IDX=CHARINDEX(@DELIMITER,@STRING)

IF @IDX!=0

SET @SLICE=LEFT(@STRING,@IDX-1)

ELSE

SET @SLICE=@STRING

IF(LEN(@SLICE)>0)

INSERT INTO TBEMP(EMPNAME) VALUES(@SLICE)

SET @STRING=RIGHT(@STRING,LEN(@STRING)-@IDX)

IF LEN(@STRING)=0 BREAK

END

RETURN

END


STEP 6: SAVE YOUR STORED PROCEDURE AND ON DEFAULT.ASPX PAGE PLACE A TEXT BOX AND A BUTTON



STEP 7: DOUBLE CLICK ON PAGE AND MAKE SQLCONNECTION WITH GIVEN CODE AS WRITTEN BELOW.


Note→ Add to these namespace  if these namespace are not included in your page

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

Now write down given code.

public partial class _Default : System.Web.UI.Page

{

SqlConnection con = new SqlConnection();

protected void Page_Load(object sender, EventArgs e)

{

con.ConnectionString = ConfigurationManager.ConnectionStrings[“Aryan”].ConnectionString;

try

{

if (con.State == ConnectionState.Closed)

{

con.Open();

}

else

{

}

}

catch (SqlException ex)

{

Response.Write(ex.Message);

}

}

}


STEP 7: Now Double Click On Submit Button And Write These lines of Code


protected void Button1_Click(object sender, EventArgs e)

{

try

{

SqlCommand cmd = new SqlCommand(“Proc_InsSplitValues”, con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue(“@STRING”, TextBox1.Text.Trim().ToUpper());

cmd.Parameters.AddWithValue(“@DELIMITER”, “,”);

cmd.ExecuteNonQuery();

cmd.Dispose();

TextBox1.Text = string.Empty;

Response.Write(“Values Saved Successfully in your Table Check them out!”);

}

catch (SqlException ex)

{

Response.Write(ex.Message);

}

}

Now Run Your Code and Give Input Into TextBox As Given

Pawan,Aryan,Orient,Chandigarh,Patiala

click on submit Button

Now Check Save Values As Give Below


Run This Code and Enjoy In Programming World


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s