Bind stored procedure data into Gridview

First, let's create a stored procedure with a name: 'Dynamic_table'

and sqlconnection 'conn' and then do the following:

SqlCommand cmd = new SqlCommand("Dynamic_table", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tableName",

SqlDbType.VarChar).Direction = ParameterDirection.Input;
cmd.Parameters["@tableName"].Value = Session["tableValue"];

cmd.Connection.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

GridView1.DataSource = dt;
GridView1.DataSourceID = string.Empty;
GridView1.DataBind();
cmd.Connection.Close();

Happy Programming !!!

Stored Procedure to select dynamic table @ runtime

Create Proc Dynamic_table
@tableName varchar(50)

as

begin

declare @sql nvarchar(100)

Set @sql='Select * from '+@tableName

exec sp_executesql @sql

end

SQL query to add Zero prefix

Let say,
I have a Database column 'ISSN' and data in it, should be 8 digits long. When I import data from excel to SQL table, it truncates the initial 1-3 zeros because it doesn't like zero as first digits when you mark the column as int but if you mark as text that's fine.
Now, let's write SQL query so that if the ISSN is 5 digits long then add 3 zeros to prefix.
e.g If the ISSN is 45678 then it should be '00045678'
If it is 6 digits long, then add two zeros to prefix....If 8 digits long keep as it is.

Hence, the SQL query is:

update table
set ISSN = RIGHT('00000000'+ISSN,8)

Happy Programming !!!

Special characters globally changes in SQL server

I wrote a SQL query in this blog to replace special characters globally. Now , I am going to modify this query with another ASCII UTF-8 table concept.

Look ASCII UTF-8 table and find the character whose description is 'Acute accent, spacing acute', it's not apostrophe.

If you have this character in your database table data, it doesn't allow you to update your table. It can be replaced manually but if you have this character in many places you should have query to replace those bad characters.

Let's look that table, you can see the Raw Encoding for that bad character is:

0xB4

which is in hexdecimal notation.

let's convert to base 10 value:

B means 11=>16*11=176

B4=>176+4=180

so,

180 = ' (x b4)

147, 148 = " (x 93, 94)

Hence the queries:

//replace by apostrophe
UPDATE table
SET ColumnName= replace(ColumnName, CHAR(180), '''')

UPDATE table
SET ColumnName= replace(ColumnName, CHAR(148), '"');

UPDATE table
SET ColumnName= replace(ColumnName, CHAR(147), '"');

Happy Coding !!!