Creating Connection String Dynamically

For creating the connection string dynamically first you create a notepad file and save it at desktop with the extension .udl

For Example:

Right Click on desk top and New -> Text Document

Go to File-> Save As. Save this file with extension .udl

Now Just Double Click on your udl file. And select provider

Select Microsoft OLEDB Provider for SQLServer and click on Next Button;

Now in Give the server name for your machine you can give '.' or your computer name.

Submit user name & password of your database, Select the Database Name Check the connection with click on test connection and then click on ok. Now open that udl file in notepad you an find connection string in that. 

Retrieve the Image From the Database

If we want to retrive the image from the database then first we pick the image from the database and then convert it into the image :

Here I am store the image in to database in an byte array now I am converting that byte array in to an image :

Image MyImage = ConvertImage(imageData);

public static Image ConvertImage(object value)

{

     byte[] img;

     Image newImage;

     img = (byte[])value;

     //Read image data into a memory stream

     using (MemoryStream ms = new MemoryStream(img, 0, img.Length))

     {

        ms.Write(img, 0, img.Length);

        //Set image variable value using memory stream.

        newImage = Image.FromStream(ms, true);

     }

     return newImage;

}

picturebox1.Image = MyImage;

Save the Image in to database

Here is a code which we can use for storing the image into database for this we can convert the image into a byte array and then store that array in to data base. The method is as follows:

byte[] imageData = CreateImage.convertPicBoxImageToByte(pb1);

public static byte[] convertPicBoxImageToByte(System.Windows.Forms.PictureBox pbImage)

{

    System.IO.MemoryStream ms = new System.IO.MemoryStream();

    pbImage.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

    return ms.ToArray();

}

Now save that imageData variable in database as a normal way.

Sql directives order

When we are optimizing the SQL statements to improve the performance I thought this tip would help the figure out where to tweak their code.

This is how the order in which sql directives (clauses) get executed when you write a select statement.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

Insert Multiple records into a SQL-Server Database

To insert multiple records into a SQL Server database, we need to call the INSERT INTO  query every time.
Suppose if we want to insert 10 records, we need to call INSERT INTO statement 10 times.
As every time we invoke the INSERT INTO statement the INSERT INTO statements are repeated multiple times.
The work around is to use one Single INSERT INTO statement in SQL Server.
There are two methods Available to Insert Multiple Records in Single INSERT INTO Statement


1. Using UNION ALL Keyword
2. SQL Server 2008 Row Construction Method


Assume the database TEST_DB contains a table called Student with the following fields:


1. StdID
2. First Name
3. Last Name
4. Country
5. State
6. ZipCode


To Insert 5 records we have to call INSERT INTO 5 times as follows:


MULTIPLE INSERT STATEMENTS


USE Test_DB
GO


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1001, 'Student1','User1', 'INDIA','Bhopal','462001')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1002, 'Student2','User2', 'INDIA','Bhopal','462001')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1003, 'Student3','User3', 'INDIA','Banglore','560068')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1004, 'Student4','User4', 'INDIA','Banglore','560068')


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
        VALUES (1005, 'Student5','User5', 'INDIA','Banglore','560068')


GO


1). USING UNION ALL


The same result can be accomplished using the following Code.


USE Test_DB
GO


INSERT INTO Employee  (StdID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
SELECT 1001, 'Student1','User1', 'INDIA','Bhopal','462001'
UNION ALL
SELECT 1002, 'Student2','User2', 'INDIA','Bhopal','462001'
UNION ALL
SELECT 1003, 'Student3','User3', 'INDIA','Banglore','560068'
UNION ALL
SELECT 1004, 'Student4','User4', 'INDIA','Banglore','560068'
UNION ALL
SELECT 1005, 'Student5','User5', 'INDIA','Banglore','560068'


GO


2). USING SQL Server 2008 Row Construction Method


USE TEST_DB
GO


INSERT INTO Employee  (EmpID, [FIRST NAME], [LAST NAME], [Country], [State],[ZIPCode])
VALUES (1001, 'Student1','User1', 'INDIA','Bhopal','462001'),
(1002, 'Student2','User2', 'INDIA','Bhopal','462001'),
(1003, 'Student3','User3', 'INDIA','Banglore','560068'),
(1004, 'Student4','User4', 'INDIA','Banglore','560068'),
(1005, 'Student5','User5', 'INDIA','Banglore','560068')

Find Duplicate Entry

In this way we can find duplicate entry in the database


-- create table for test data
create table test_data (
id int,
product char(10)
)


-- insert test data


insert into test_data values(1,'item 1')
insert into test_data values(2,'item 2')
insert into test_data values(3,'item 3')
insert into test_data values(3,'item 4')
insert into test_data values(5,'item 5')
insert into test_data values(5,'item 6')
insert into test_data values(5,'item 7')
insert into test_data values(8,'item 8')


-- find duplicate id
select id, count(*) #_of_dups
from test_data
group by id
having count(*) > 1


-- drop table test_data
drop table test_data

Detail members can only contain static inner members

Hi,
'The tablix 'Table1' has a detail member with inner members. Detail members can only contain static inner members. 

This is the one of the generic Error in SSRS 2008. This error will come when we are putting a table inside another table and forget to gives the group property of a parent table. It is always coming when we are having a table inside another table and if parent table does not have any group. So whenever we are using a table in side another table that time it is necessary to give the group property to the parent table.