search/filter the records for the entered value

Below Code will help u to select a GridView Row in Run-Time and also search/filter the record according to the enter value in a textbox..kindly refer the image below..
VB.NET CODE
Private Sub frmDynamicSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ds = clsDBManager.FillDataSet("Select * from tbUserMst")
If Not ds.Tables(0) Is Nothing AndAlso ds.Tables(0).Rows.Count > 0 Then
DataGridView1.DataSource = ds.Tables(0)
_strDynamicSearchColumn = "UserName"
FindGridTable = ds.Tables(0).Copy
dtSearchResult = ds.Tables(0).Copy
Else
MessageBox.Show("No Records Available", "Santosh Test", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub HilightCell()
For i As Integer = 0 To DataGridView1.Rows.Count - 2
If DataGridView1.Rows(i).Cells(1).Value.ToString.StartsWith(txtUserName.Text.Trim) Then
'dgvDisplay.Rows(i).DefaultCellStyle.BackColor = Color.Tan
DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Cyan
Else
DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.White
End If
Next
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If txtUserName.Text = "" Then
MessageBox.Show("Please Enter a Value to Search", "Santosh Test", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
HilightCell()
End If
End Sub
C#.NET CODE
private void frmDynamicSearch_Load(object sender, System.EventArgs e)
{
ds = clsDBManager.FillDataSet("Select * from tbUserMst");
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
DataGridView1.DataSource = ds.Tables[0];
_strDynamicSearchColumn = "UserName";
FindGridTable = ds.Tables[0].Copy();
dtSearchResult = ds.Tables[0].Copy();
}
else
{
MessageBox.Show("No Records Available", "Santosh Test", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}





private void HilightCell()
{
for (int i = 0; i <= DataGridView1.Rows.Count - 2; i++) { if (DataGridView1.Rows[i].Cells[1].Value.ToString().StartsWith(txtUserName.Text.Trim())) { //dgvDisplay.Rows(i).DefaultCellStyle.BackColor = Color.Tan DataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.Cyan; } else { DataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.White; } } } private void Button2_Click(object sender, System.EventArgs e) { if (txtUserName.Text == "") { MessageBox.Show("Please Enter a Value to Search", "Santosh Test", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { HilightCell(); } } OUTPUT:
How Was The Coding
Regards
Santosh

Save Image in DataBase in .NET

This Example will help u to insert image in DataBase
VB CODE
Dim ImagePath As String = "F:\santosh\Image"
Dim ImageInput As String = "Sample.jpg"
Dim ImageOutput As String = "SampleOut.jpg"
Dim dtReturnImage As DataTable
Shared StrConn As String = ""
Private Sub btnSaveImage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveImage.Click
Dim ReturnValue As Integer = 0
Dim Img As FileStream
Dim BinaryReader As BinaryReader
Dim ImgArray() As Byte
Img = New FileStream(ImagePath & "\" & ImageInput, FileMode.Open, FileAccess.Read)
BinaryReader = New BinaryReader(Img)
ImgArray = BinaryReader.ReadBytes(Img.Length)
BinaryReader.Close()
Img.Close()
Dim cls As New clsCommon
ReturnValue = InsertImage(0, ImgArray)
If ReturnValue > 0 Then
Dim dt As New DataTable
dt = cls.ReturnImage(ReturnValue)
For Each dr As DataRow In dt.Rows
ImgArray = CType(dr("ImageFile"), Byte())
Dim oOutput As FileStream = File.Create(ImagePath & "\" & ImageOutput, ImgArray.Length)
oOutput.Write(ImgArray, 0, ImgArray.Length)
oOutput.Close()
Next
End If
End Sub
Private Function InsertImage(ByVal ImageID As Integer, ByVal Image() As Byte) As Integer
Dim InsertSqlCommand As New SqlCommand()
Dim RetunId As Integer = 0
Dim com As New SqlCommand
Dim con As New SqlConnection
con = GetConnectObj()
com.Connection = con
com.CommandType = CommandType.StoredProcedure
com.CommandText = "SaveImage"
Dim objOutputParam As New SqlParameter("@NewID", SqlDbType.Int)
objOutputParam.Direction = ParameterDirection.Output
Dim objImageParam As New SqlParameter("@ImageFile", SqlDbType.Image)
objImageParam.Direction = ParameterDirection.Input
objImageParam.Value = Image
Dim objImageIDParam As New SqlParameter("@ImageID", SqlDbType.Int, 12, ParameterDirection.Input)
objImageIDParam.Value = ImageID
com.Parameters.Add(objImageIDParam)
com.Parameters.Add(objImageParam)
com.Parameters.Add(objOutputParam)
con.Open()
com.ExecuteNonQuery()
RetunId = objOutputParam.Value
Return RetunId
End Function
Public Function ReturnImage(ByVal ImageIdvalue As Integer) As DataTable
dtReturnImage = FillDatatable(" select ImageFile from Images where ImageID = " & ImageIdvalue & "")
Return dtReturnImage
End Function
Private Function ConnectionString() As String
StrConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Santosh_Practice;Data Source=SANTOSH\SQLEXPRESS" 'Myconnection is Key in App.Config
Return StrConn
End Function
Private Function GetConnectObj() As SqlConnection
Return New SqlConnection(ConnectionString())
End Function
Private Function FillDatatable(ByVal Query As String) As DataTable
Dim objDT As New DataTable()
Dim objda As New SqlDataAdapter(Query, ConnectionString())
objda.Fill(objDT)
Return objDT
End Function
C# CODE
private string ImagePath = "F:\\santosh\\Image";
private string ImageInput = "Sample.jpg";
private string ImageOutput = "SampleOut.jpg";
private DataTable dtReturnImage;
public static string StrConn = "";
private void btnSaveImage_Click(object sender, System.EventArgs e)
{
int ReturnValue = 0;
FileStream Img = null;
BinaryReader BinaryReader = null;
byte[] ImgArray = null;
Img = new FileStream(ImagePath + "\\" + ImageInput, FileMode.Open, FileAccess.Read);
BinaryReader = new BinaryReader(Img);
ImgArray = BinaryReader.ReadBytes(Img.Length);
BinaryReader.Close();
Img.Close();
clsCommon cls = new clsCommon();
ReturnValue = InsertImage(0, ImgArray);
if (ReturnValue > 0)
{
DataTable dt = new DataTable();
dt = cls.ReturnImage(ReturnValue);
foreach (DataRow dr in dt.Rows)
{
ImgArray = (byte[])(dr["ImageFile"]);
FileStream oOutput = File.Create(ImagePath + "\\" + ImageOutput, ImgArray.Length);
oOutput.Write(ImgArray, 0, ImgArray.Length);
oOutput.Close();
}
}
}
private int InsertImage(int ImageID, byte[] Image)
{
SqlCommand InsertSqlCommand = new SqlCommand();
int RetunId = 0;
SqlCommand com = new SqlCommand();
SqlConnection con = new SqlConnection();
con = GetConnectObj();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "SaveImage";
SqlParameter objOutputParam = new SqlParameter("@NewID", SqlDbType.Int);
objOutputParam.Direction = ParameterDirection.Output;
SqlParameter objImageParam = new SqlParameter("@ImageFile", SqlDbType.Image);
objImageParam.Direction = ParameterDirection.Input;
objImageParam.Value = Image;
SqlParameter objImageIDParam = new SqlParameter("@ImageID", SqlDbType.Int, 12, ParameterDirection.Input);
objImageIDParam.Value = ImageID;
com.Parameters.Add(objImageIDParam);
com.Parameters.Add(objImageParam);
com.Parameters.Add(objOutputParam);
con.Open();
com.ExecuteNonQuery();
RetunId = objOutputParam.Value;
return RetunId;
}
public DataTable ReturnImage(int ImageIdvalue)
{
dtReturnImage = FillDatatable(" select ImageFile from Images where ImageID = " + ImageIdvalue + "");
return dtReturnImage;
}
private string ConnectionString()
{
StrConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Santosh_Practice;Data Source=SANTOSH\\SQLEXPRESS"; //Myconnection is Key in App.Config
return StrConn;
}
private SqlConnection GetConnectObj()
{
return new SqlConnection(ConnectionString());
}
private DataTable FillDatatable(string Query)
{
DataTable objDT = new DataTable();
SqlDataAdapter objda = new SqlDataAdapter(Query, ConnectionString());
objda.Fill(objDT);
return objDT;
}

Any Doubt Plz post a comment or send mail to santosh.mcao8@gmail.com

Take Backup At your own Time

hi i am giving a very nice code to take backup at your own setting time.
1.Drag a Timer Control
2.make it Enabled is true.


VB.NET Code
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Management.Smo
Imports System.IO
Public Class TestForm
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Dim BackUpTime As Date = DateTime.Now
If BackUpTime.ToLongTimeString = "8:09:18 PM" Then
TakeBackUp("DMS", "D:\Mail\TestForU.bak") 'DMS is the correct database name exist in server
End If
End Sub
Private Sub TakeBackUp(ByVal BackupDBName As String, ByVal FileNamePath As String)
Try
Dim sqlServerInstance As New Server(New Microsoft.SqlServer.Management.Common.ServerConnection _
(New System.Data.SqlClient.SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=;Data Source=SANTOSH\SQLEXPRESS"))) 'This is only for connection if u give Initial Catalog null it will work
Dim objBackup As New Backup
objBackup.Devices.AddDevice(FileNamePath, DeviceType.File)
objBackup.Database = BackupDBName
objBackup.Action = BackupActionType.Database
objBackup.SqlBackup(sqlServerInstance)
MessageBox.Show("The backup of database " & "'" & BackupDBName & "'" & " completed sccessfully", "Microsoft SQL Server Management Studio", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
End Class

C#.NET Code
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;
using System.IO;
public class TestForm
{
//TODO: INSTANT C# TODO TASK: Insert the following converted event handler wireups at the end of the 'InitializeComponent' method for forms, 'Page_Init' for web pages, or into a constructor for other classes:
Timer1.Tick += Timer1_Tick;

private void Timer1_Tick(object sender, System.EventArgs e)
{
System.DateTime BackUpTime = DateTime.Now;
if (BackUpTime.ToLongTimeString() == "8:09:18 PM")
{
TakeBackUp("DMS", "D:\\Mail\\TestForU.bak"); //DMS is the correct database name exist in server
}
}
private void TakeBackUp(string BackupDBName, string FileNamePath)
{
try
{
Server sqlServerInstance = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection (new System.Data.SqlClient.SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=;Data Source=SANTOSH\\SQLEXPRESS"))); //This is only for connection if u give Initial Catalog null it will work
Backup objBackup = new Backup();
objBackup.Devices.AddDevice(FileNamePath, DeviceType.File);
objBackup.Database = BackupDBName;
objBackup.Action = BackupActionType.Database;
objBackup.SqlBackup(sqlServerInstance);
MessageBox.Show("The backup of database " + "'" + BackupDBName + "'" + " completed sccessfully", "Microsoft SQL Server Management Studio", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}

OUTPUT:-

Regards
Santosh

Calculate the running total in a datagridview

Please follow the below code,this will help to calculate the running total in a datagridview
VB Code
Dim dtRunningTot As New DataTable
Shared StrConn As String = ""
Private Function ConnectionString() As String
StrConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Santosh_Practice;Data Source=SANTOSH\SQLEXPRESS" 'Myconnection is Key in App.Config
Return StrConn
End Function
Private Function GetConnectObj() As SqlConnection
Return New SqlConnection(ConnectionString())
End Function
Private Function FillDatatable(ByVal Query As String) As DataTable
Dim objDT As New DataTable()
Dim objda As New SqlDataAdapter(Query, ConnectionString())
objda.Fill(objDT)
Return objDT
End Function
Private Sub frmRunningTotal_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dtRunningTot = FillDatatable("Select salary from RunningTotal")
DataGridView1.DataSource = dtRunningTot
For i As Integer = 0 To DataGridView1.Rows.Count - 2
For j As Integer = 0 To DataGridView1.Columns.Count - 1
If i = 0 Then
DataGridView1.Rows(i).Cells("RunningTotal").Value = Val(DataGridView1.Rows(i).Cells("Salary").Value)
Else
DataGridView1.Rows(i).Cells("RunningTotal").Value = Val(DataGridView1.Rows(i - 1).Cells("RunningTotal").Value) + Val(DataGridView1.Rows(i).Cells("Salary").Value)
End If
Next
Next
End Sub
C# Code
private DataTable dtRunningTot = new DataTable();
public static string StrConn = "";
//TODO: INSTANT C# TODO TASK: Insert the following converted event handler wireups at the end of the 'InitializeComponent' method for forms, 'Page_Init' for web pages, or into a constructor for other classes:
base.Load += frmRunningTotal_Load;

private string ConnectionString()
{
StrConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Santosh_Practice;Data Source=SANTOSH\\SQLEXPRESS"; //Myconnection is Key in App.Config
return StrConn;
}
private SqlConnection GetConnectObj()
{
return new SqlConnection(ConnectionString());
}
private DataTable FillDatatable(string Query)
{
DataTable objDT = new DataTable();
SqlDataAdapter objda = new SqlDataAdapter(Query, ConnectionString());
objda.Fill(objDT);
return objDT;
}
private void frmRunningTotal_Load(object sender, System.EventArgs e)
{
dtRunningTot = FillDatatable("Select salary from RunningTotal");
DataGridView1.DataSource = dtRunningTot;
for (int i = 0; i <= DataGridView1.Rows.Count - 2; i++)
{
for (int j = 0; j < DataGridView1.Columns.Count; j++)
{
if (i == 0)
{
DataGridView1.Rows[i].Cells["RunningTotal"].Value = Microsoft.VisualBasic.Conversion.Val(DataGridView1.Rows[i].Cells["Salary"].Value);
}
else
{
DataGridView1.Rows[i].Cells["RunningTotal"].Value = Microsoft.VisualBasic.Conversion.Val(DataGridView1.Rows[i - 1].Cells["RunningTotal"].Value) + Microsoft.VisualBasic.Conversion.Val(DataGridView1.Rows[i].Cells["Salary"].Value);
}
}
}
}
How was the Coding ??
Any Problem Plz feel free to Contact Santosh.mca08@gmail.com
i will reply u soon

Thanx and Regards
Santosh

Dynamic Search in Windows Application

Please follow the below code,how it is working to search Dynamically.
it will filter all the data corresponding to the textbox entering characters.
1. Drag a Text Box and a DataGridView.
VB Code
[Code]
Public Class frmDynamicSearch
Dim ds As DataSet
Dim _dt As DataTable
Public _strDynamicSearchColumn As String = "UserName"
Dim FindGridTable As DataTable
Dim dtSearchResult As DataTable
Public Property DynamicSearchColumn() As String
Get
Return _strDynamicSearchColumn
End Get
Set(ByVal value As String)
_strDynamicSearchColumn = value
End Set
End Property
Private Function DynamicSearch() As DataTable
Try
If txtUserName.Text.Trim.Equals(String.Empty) Then
Return FindGridTable
End If
dtSearchResult.Rows.Clear()
Dim dr() As DataRow = Nothing
Dim rowSearchResult As DataRow
For Each row As DataRow In FindGridTable.Rows
If Not IsDBNull(row(DynamicSearchColumn)) Then
If row(DynamicSearchColumn).ToString().Trim.ToUpper.StartsWith(txtUserName.Text.Trim.ToUpper) Then
rowSearchResult = dtSearchResult.NewRow()
rowSearchResult.ItemArray = row.ItemArray
dtSearchResult.Rows.Add(rowSearchResult)
End If
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Return dtSearchResult
End Function

Private Sub txtCode_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtUserName.TextChanged
Dim dt As DataTable = DynamicSearch()
If Not dt Is Nothing And dgvDisplay.RowCount > 0 Then
dgvDisplay.DataSource = dt
Else
MessageBox.Show("No Records Available", "Santosh Test", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub
Private Sub frmDynamicSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ds = clsDBManager.FillDataSet("Select * from tbUserMst")
If Not ds.Tables(0) Is Nothing AndAlso ds.Tables(0).Rows.Count > 0 Then
dgvDisplay.DataSource = ds.Tables(0)
_strDynamicSearchColumn = "UserName"
FindGridTable = ds.Tables(0).Copy
dtSearchResult = ds.Tables(0).Copy
Else
MessageBox.Show("No Records Available", "Santosh Test", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub
End Class
[/Code]

C# Code
[Code]
public class frmDynamicSearch
{
private DataSet ds;
private DataTable _dt;
public string _strDynamicSearchColumn = "UserName";
private DataTable FindGridTable;
private DataTable dtSearchResult;
//TODO: INSTANT C# TODO TASK: Insert the following converted event handler wireups at the end of the 'InitializeComponent' method for forms, 'Page_Init' for web pages, or into a constructor for other classes:
txtUserName.TextChanged += txtCode_TextChanged;
base.Load += frmDynamicSearch_Load;

public string DynamicSearchColumn
{
get
{
return _strDynamicSearchColumn;
}
set
{
_strDynamicSearchColumn = value;
}
}
private DataTable DynamicSearch()
{
try
{
if (txtUserName.Text.Trim().Equals(string.Empty))
{
return FindGridTable;
}
dtSearchResult.Rows.Clear();
DataRow[] dr = null;
DataRow rowSearchResult = null;
foreach (DataRow row in FindGridTable.Rows)
{
if (! (System.Convert.IsDBNull(row[DynamicSearchColumn])))
{
if (row[DynamicSearchColumn].ToString().Trim().ToUpper().StartsWith(txtUserName.Text.Trim().ToUpper()))
{
rowSearchResult = dtSearchResult.NewRow();
rowSearchResult.ItemArray = row.ItemArray;
dtSearchResult.Rows.Add(rowSearchResult);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return dtSearchResult;
}

private void txtCode_TextChanged(object sender, System.EventArgs e)
{
DataTable dt = DynamicSearch();
if (dt != null & dgvDisplay.RowCount > 0)
{
dgvDisplay.DataSource = dt;
}
else
{
MessageBox.Show("No Records Available", "Santosh Test", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void frmDynamicSearch_Load(object sender, System.EventArgs e)
{
ds = clsDBManager.FillDataSet("Select * from tbUserMst");
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
dgvDisplay.DataSource = ds.Tables[0];
_strDynamicSearchColumn = "UserName";
FindGridTable = ds.Tables[0].Copy();
dtSearchResult = ds.Tables[0].Copy();
}
else
{
MessageBox.Show("No Records Available", "Santosh Test", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
[/Code]

OutPut:
How was the Coding ???
Plz post a comment

Thanx and Regards
Santosh
http://santoshdotnetarena.blogspot.com/

Find Driver Details in VB.NET

Below Code will Help to Find Driver Details of a System

Imports System.IO
Imports System
Imports System.Math
Public Class frmFindDriverName
Private Sub frmFindDriverName_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
For Each objdriveinfo As DriveInfo In DriveInfo.GetDrives()
If objdriveinfo.Name = "A:\" Or objdriveinfo.Name = "G:\" Then
Else
ListBox1.Items.Add(objdriveinfo.Name & " " & Round((objdriveinfo.TotalFreeSpace / 1073741824), 2) & "GB" & " " & Round((objdriveinfo.TotalSize / 1073741824), 2) & "GB" & " " & objdriveinfo.DriveFormat & " " & objdriveinfo.VolumeLabel & "")
End If
'Label1.Text = Round(objdriveinfo.TotalFreeSpace, 2)
Next
End Sub
End Class

Output:

AUTO INCREMENT PRIMERY KEY IN RUN TIME

BELOW SP WILL HELP YOU TO AUTO INCREMENT PRIMERY KEY IN RUN TIME

ALTER PROCEDURE AUTO_INCREMENT
@NAME VARCHAR(12) = NULL
AS
BEGIN
DECLARE @ID INT
SELECT @ID = MAX(ID) FROM TEST --(TABLENAME)
IF @ID IS NULL
INSERT INTO TEST VALUES(1,@NAME)
ELSE
INSERT INTO TEST VALUES(@ID +1 ,@NAME)
END