Category Archives: MS SQL

Insert Update Binary data in MS Sql Server

It’s easy to insert update the Binary data from a .net application (web/windows).

But If you need to insert/update or copy/paste the data which is already there in the database then it’s not possible using the SSMS Table Editor (see attached image), it would give an error “The changed value in the call was not reognized as valid. .Net Framework Data Type: Byte[]
Error Message: You cannot use the Result pane to set this Field data to values other then null”.

Insert Update Binary data in MS Sql Server

Solution
Use the SSMS Query editor to do this, i.e.
update TableName set BinaryColumnName = BinaryData

Note: The binary data value should not be enclosed between any single quotes

Entity Framework Function Import not returning Columns data

While working with Entity Framework (EF), sometimes the EF doesn’t recognize the columns returned by the Stored Procedure.


Reason

1) If the db procedure is using temporary table (e.g. #tempTable), EF don’t recognize the MetaData from the procedure.

2) If multiple select … statements exists in db procedure.


Solutions

Before executing procedure or beginning of db procedure set the FMTONLY to ON like below line:

SET FMTONLY ON

This will force EF to read the metadata from Temp tables


Disadvantage

There would be performance issues with approach because the way EF read the metadata from temp tables is to execute them multiple times.


Workaround

Instead of using the temp tables, create table variables and use them to store data temporarily. While using this option you don’t need to set the FMTONLY setting.


Performance

Using the table variables will be faster than FMTONLY option.


Find more detailed information at 

http://munishbansal.wordpress.com/2009/02/18/set-fmtonly-on-useful-in-tsql-ssis-packages-for-using-temp-tables/


Thanks


Change MS SQL 2005 login password using sql query

If you want to change the MS Sql 2005 ‘s password then just run the following sql query after logging into query anlyzer

sp_password ‘oldPassword’ , newPassword’

Note
Always use strong SQL passwords (i.e. having uppercase characters , lowercase characters, numeric values, special characters) for security purpose, this way it will be difficult to hack, hard to guess, hard to crack by brute force attacks.


Rename a table Column in MS SQL

How to rename a MS SQL server database table column name after the table creation??
You can rename a column regardless of it is null or contains any data.

We need to use a System stored procedure to rename. Any command with alter keywords won’t work.

Code
EXEC sp_rename ‘tableName.[oldColumnName]’, ‘newColumnName’, ‘COLUMN’

Example:
EXEC sp_rename ‘Ultra_tblPressRelease.[uShowStatus]’, ‘uShowInA2B’, ‘COLUMN’

The above line will rename the existing column named uShowStatus of table tblPressRelease to uShowInA2B.

There will be no adverse effect of this procedure on your table data.
But you have to update all your SQL Cursors and Stored Procedures with the new column name (if any).


ASp.Net 2.0 GridView Delete Button Confirmation pop-up

While using GridView in asp.net pages wants to confirm the deletion form user. To do this we can take him to another page having GUI to confirm and then delete the record on that page.

Instead of doing so much hardwork you can achieve this on the same page with a little bit of extra code.

Workaround

Add a client alert script to the delete button of every row.
In the delete event of GridView delete the record.

Code

==================================================
Page’s Aspx Design File Begins
==================================================
<asp:GridView ID=”gvFaq” runat=”server” AutoGenerateColumns=”False” CellPadding=”4″
ForeColor=”#333333″ GridLines=”None” OnRowDataBound=”gvFaq_RowDataBound” OnRowDeleting=”gvFaq_RowDeleting”>
<FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
<Columns>
<asp:BoundField DataField=”slno” HeaderText=”Sl No.” />
<asp:BoundField DataField=”cHeading” HeaderText=”Question” />
<asp:BoundField DataField=”cPosition” HeaderText=”Position” />
<asp:CommandField HeaderText=”Manage” ShowSelectButton=”True” />
<asp:CommandField HeaderText=”Delete” ShowDeleteButton=”True” />
</Columns>
<PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
<SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
<HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<EditRowStyle BackColor=”#999999″ />
<AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />
</asp:GridView>
==================================================
Page’s Aspx Design File Ends
==================================================

Database Design FIle

==================================================
Code Behind File Begins
==================================================
1) Import Namespaces
using System.Data.SqlClient;

2) Declare global variables which will be used in page
DataSet ds = new DataSet();
SqlDataAdapter da;
SqlConnection myConnection;
String connStr = “your database connection string goes here”‘;
String sql = string.Empty;

3) Page Load event
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
fillGridView();
}
}

4) GridView filling method
private void fillGridView()
{

try
{
myConnection = new SqlConnection(connStr);
sql = “select * from tblFaq order by slno desc”;

da = new SqlDataAdapter(sql, myConnection);
ds.Clear();
da.Fill(ds, “tblFaq”);

gvFaq.DataSource = ds.Tables[0];
Page.DataBind();

}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}

5) In the RowDataBound event add a java script event to the delete cell.
protected void gvFaq_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[4].Attributes.Add(“onClick”, “return confirm(‘Are you sure you want to delete the record?’);”);
}
}

6) Handle the RowDeleting event to delete the record of current row.

protected void gvFaq_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
myConnection = new SqlConnection(connStr);
myConnection.Open();
sql = “delete from tblFaq where slno = ” + Convert.ToInt32(gvFaq.Rows[e.RowIndex].Cells[0].Text);
SqlCommand oldcom = new SqlCommand(sql, myConnection);
oldcom.ExecuteNonQuery();
myConnection.Close();
fillGridView();
Response.Write(“Record Deleted”);
}
catch (Exception ex)
{
lblError.Text = ex.Message;
}
}

==================================================
Code Behind File Ends
==================================================
The output will look like this

For Source Code visit http://dotnetcoderoom.blogspot.com/2008/08/aspnet-20-gridview-delete-button.html


Update database data using SQL Cursors based on conditions

Recently one of my MSSQL database got SQL Injection attacks.
Hacker put some JavaScript code in all row data to get hits on his website (which is apparently blocked, and soon my site will also be got banned if it keep happening for a long time).

Because the database has a huge amount of data, i can’t update table data using ExecuteNonQuery commands as it will result in a great overload on database and network resources.

So I decided to make something logical which is fast and logical way to handle this situation, and at last i ended up with writing a cursor.

This cursor finds a specific string and replaces it with null.

Scenario:

Create a table with sample data

create table tblDataUpdateTest
(
slno int identity(1,1),
cName varchar(50),
cRemarks varchar(50),
)

insert into tblDataUpdateTest (cName, cRemarks) values (‘name1′,’some text here BADCODE’)
insert into tblDataUpdateTest (cName, cRemarks) values (‘name2′,’raBADCODEhul’)
insert into tblDataUpdateTest (cName, cRemarks) values (‘name3′,’dj BADCODEis devil’)

select * from tblDataUpdateTest


Create Cursor to update database table

Declare @@counter int
set @@counter=0
Declare @@slno int
Declare @@cRemarks varchar(100)

Declare tmepTbl cursor
For
Select slno,cRemarks from tblDataUpdateTest

Open tmepTbl /* Opening the cursor */

fetch next from tmepTbl
into @@slno,@@cRemarks

while @@fetch_Status-1
begin

Update tblDataUpdateTest
set cRemarks = Replace(@@cRemarks,’BADCODE’,”)
where slno = @@slno

fetch next from tmepTbl
into @@slno, @@cRemarks

set @@counter=@@counter+1
end
close tmepTbl
Deallocate tmepTbl

Conclusion:

By running this cursor all the occurrences of BADCODE will be eliminated from the specified table.

Note:
I used word BADCODE here but in my actual data it was a java script tag which was creating all the problem.

For more articles updates and sourcecodes please visit dotnetcoderoom