Programming for Everybody

Access database VBA programmer: Create insert update delete and search in another database with code



 Private Sub Command12_Click()

If (Not IsNull(Me.idtxt.Value)) Then

Dim dbs As DAO.Database

Set dbs = OpenDatabase("H:\rr.accdb")

Dim strsql As String

strsql = "Select firstname,lastname,marks,image1 From Table6 " _

& " Where id= " & Me.idtxt.Value & ""

Dim rst As DAO.Recordset

Set rst = dbs.OpenRecordset(strsql)

If rst.EOF Then

Me.Image1.Picture = ""

Me.ftxt.Value = ""

Me.ltxt.Value = ""

Me.mtxt.Value = ""

Else

Me.Image1.Picture = rst.Fields("image1")

Me.ftxt.Value = rst.Fields("firstname")

Me.ltxt.Value = rst.Fields("lastname")

Me.mtxt.Value = rst.Fields("marks")

End If

rst.Close

Else

MsgBox "Please Enter ID"

End If

End Sub


Private Sub Command13_Click()

Dim dbs As DAO.Database

Set dbs = OpenDatabase("H:\rr.accdb")

Dim strsql As String

strsql = "Update Table6 Set firstname='" & Me.ftxt.Value & "', " _

& "lastname = '" & Me.ltxt.Value & "',marks=" & Me.mtxt.Value & "," _

& "image1='" & Me.Image1.Picture & "' Where id= " & Me.idtxt.Value & ""

dbs.Execute (strsql)

MsgBox "Data Updated Successfully"

End Sub


Private Sub Command14_Click()

Dim dbs As DAO.Database

Set dbs = OpenDatabase("H:\rr.accdb")

Dim strsql As String

strsql = "Delete From Table6 Where id= " & Me.idtxt.Value & ""

dbs.Execute (strsql)

Me.Image1.Picture = ""

Me.ftxt.Value = ""

Me.ltxt.Value = ""

Me.mtxt.Value = ""

Me.idtxt.Value = ""

MsgBox "Data Deleted Successfully"

End Sub


Private Sub Command7_Click()

Dim img_of As Office.FileDialog

Dim img_var As Variant

Set img_of = Application.FileDialog(msoFileDialogFilePicker)

img_of.Title = "Please Select image"

img_of.Filters.Clear

img_of.Filters.Add "Images", "*.png; *.jpg"

If img_of.Show = True Then

For Each img_var In img_of.SelectedItems

Me.Image1.Picture = img_var

Next

Else

Me.Image1.Picture = ""

MsgBox "Please Select Image"

End If

End Sub


Private Sub Command8_Click()

Me.Image1.Picture = ""

Me.ftxt.Value = ""

Me.ltxt.Value = ""

Me.mtxt.Value = ""

Me.idtxt.Value = ""

End Sub


Private Sub Command9_Click()

Dim dbs As DAO.Database

Set dbs = OpenDatabase("H:\rr.accdb")

Dim strsql As String

strsql = "Insert Into Table6(firstname,lastname,marks,image1) " _

& " Values('" & Me.ftxt.Value & "','" & Me.ltxt.Value & "'," _

& "" & Me.mtxt.Value & ",'" & Me.Image1.Picture & "')"

dbs.Execute (strsql)

MsgBox "Data Inserted Successfully"

End Sub



ASP.NET C# insert update delete using SQL database and Load data in Gridview (WITH CODE)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;



using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Configuration;

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

{

    public SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["webaspConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)

    {

        GridView1.DataBind();

    }


    protected void Button1_Click(object sender, EventArgs e)

    {

        SqlCommand cmd = new SqlCommand("Insert Into Table1(firstname,lastname,marks)Values(@firstname,@lastname,@marks)", conn);

        cmd.Parameters.AddWithValue("firstname", ftxt.Text.Trim());

        cmd.Parameters.AddWithValue("lastname", ltxt.Text.Trim());

        cmd.Parameters.AddWithValue("marks", mtxt.Text.Trim());

        conn.Open();

        cmd.ExecuteNonQuery();

        conn.Close();

        result_lbl.Visible = true;

        result_lbl.ForeColor = System.Drawing.Color.Green;

        result_lbl.Text = "Data Inserted Successfully";

        GridView1.DataBind();

    }


    protected void Button2_Click(object sender, EventArgs e)

    {

        ftxt.Text = "";

        ltxt.Text = "";

        mtxt.Text = "";

        id_lbl.Text = "";

        result_lbl.Text = "";

    }


    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)

    {

        id_lbl.Text = GridView1.SelectedRow.Cells[0].Text.ToString();

        ftxt.Text = GridView1.SelectedRow.Cells[1].Text.ToString();

        ltxt.Text = GridView1.SelectedRow.Cells[2].Text.ToString();

        mtxt.Text = GridView1.SelectedRow.Cells[3].Text.ToString();

    }


    protected void Button3_Click(object sender, EventArgs e)

    {

        SqlCommand cmd = new SqlCommand("Update Table1 Set firstname=@firstname,lastname=@lastname,marks=@marks Where id=@id", conn);

        cmd.Parameters.AddWithValue("firstname", ftxt.Text.Trim());

        cmd.Parameters.AddWithValue("lastname", ltxt.Text.Trim());

        cmd.Parameters.AddWithValue("marks", mtxt.Text.Trim());

        cmd.Parameters.AddWithValue("id", id_lbl.Text.Trim());

        conn.Open();

        cmd.ExecuteNonQuery();

        conn.Close();

        result_lbl.Visible = true;

        result_lbl.ForeColor = System.Drawing.Color.Green;

        result_lbl.Text = "Data Updated Successfully";

        GridView1.DataBind();

    }


    protected void Button4_Click(object sender, EventArgs e)

    {

        SqlCommand cmd = new SqlCommand("Delete From Table1 Where id=@id", conn);

                cmd.Parameters.AddWithValue("id", id_lbl.Text.Trim());

        conn.Open();

        cmd.ExecuteNonQuery();

        conn.Close();

        result_lbl.Visible = true;

        result_lbl.ForeColor = System.Drawing.Color.Red;

        result_lbl.Text = "Data Deleted Successfully";

        GridView1.DataBind();

        ftxt.Text = "";

        ltxt.Text = "";

        mtxt.Text = "";

        id_lbl.Text = "";

        

    }


VBA code for search ComboBox in userform Microsoft #Excel With Source Code



 Private Sub ComboBox1_Change()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

Dim i As Integer

For i = 1 To ws.UsedRange.Rows.Count

If Me.ComboBox1.Column(0) = ws.Cells(i, 2).Value Then

Me.id_txt.Text = ws.Cells(i, 1).Value

Me.math_txt.Text = ws.Cells(i, 3).Value

Me.geo_txt.Text = ws.Cells(i, 4).Value

Me.chem_txt.Text = ws.Cells(i, 5).Value

Me.his_txt.Text = ws.Cells(i, 6).Value

Me.com_txt.Text = ws.Cells(i, 7).Value

End If

Next i

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()

Dim lastrow, i As Integer

lastrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To lastrow

Dim vs As String

vs = Sheet1.Cells(i, "B")

Me.ComboBox1.AddItem vs

Next i

End Sub

Master Excel VBA: Insert, Update, Delete, and Search with Pictures + Free Source Code! 🚀

 Dim imagefile As String

Private Sub CommandButton1_Click()

imagefile = Application.GetOpenFilename(Title:="Select Image", _

FileFilter:="ImageFiles(*.png;*.Jpeg;*.JpG), *.png;*.Jpeg;*.JpG")

If imagefile = "False" Then

MsgBox "Please Select one Image"

Else

Me.Image1.Picture = LoadPicture(imagefile)

Me.Image1.PictureSizeMode = fmPictureSizeModeStretch


End If

End Sub


Private Sub CommandButton2_Click()

Dim sheet_v As Worksheet

Set sheet_v = ThisWorkbook.Sheets("Sheet1")

Dim last_row As Integer

last_row = sheet_v.Range("A" & Rows.Count).End(xlUp).Row

sheet_v.Range("A" & last_row + 1).Value = Me.code_student.Value

sheet_v.Range("b" & last_row + 1).Value = Me.name_txt.Value

sheet_v.Range("C" & last_row + 1).Value = Me.age_txt.Value

sheet_v.Range("D" & last_row + 1).Value = imagefile

MsgBox "The student inserted Successfully"

End Sub


Private Sub CommandButton3_Click()

Me.code_student.Value = ""

Me.name_txt.Value = ""

Me.age_txt.Value = ""

imagefile = ""

Me.Image1.Picture = LoadPicture(imagefile)

End Sub


Private Sub CommandButton4_Click()

If Me.search_txt.Text = "" Then

MsgBox "Please Enter code student"

Else

Dim sheet_v As Worksheet

Set sheet_v = ThisWorkbook.Sheets("Sheet1")

Dim last_row, i As Integer

last_row = sheet_v.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To last_row

If Me.search_txt.Text = sheet_v.Cells(i, 1) Then

Me.code_student.Text = sheet_v.Cells(i, 1)

Me.name_txt.Value = sheet_v.Cells(i, 2)

Me.age_txt.Value = sheet_v.Cells(i, 3)

imagefile = sheet_v.Cells(i, 4)

Me.Image1.Picture = LoadPicture(imagefile)

Me.Image1.PictureSizeMode = fmPictureSizeModeStretch

Exit Sub

Else

Me.code_student.Value = ""

Me.name_txt.Value = ""

Me.age_txt.Value = ""

imagefile = ""

Me.Image1.Picture = LoadPicture(imagefile)

End If

Next i

End If

End Sub


Private Sub CommandButton5_Click()


Dim sheet_v As Worksheet

Set sheet_v = ThisWorkbook.Sheets("Sheet1")

Dim last_row, i As Integer

last_row = sheet_v.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To last_row

If Me.search_txt.Text = sheet_v.Cells(i, 1) Then

 sheet_v.Cells(i, 1) = Me.code_student.Text

 sheet_v.Cells(i, 2) = Me.name_txt.Value

sheet_v.Cells(i, 3) = Me.age_txt.Value

 sheet_v.Cells(i, 4) = imagefile

Me.Image1.Picture = LoadPicture(imagefile)

Me.Image1.PictureSizeMode = fmPictureSizeModeStretch


End If

Next i

End Sub


Private Sub CommandButton6_Click()

Dim sheet_v As Worksheet

Set sheet_v = ThisWorkbook.Sheets("Sheet1")

Dim last_row, i As Integer

last_row = sheet_v.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To last_row

If Me.search_txt.Text = sheet_v.Cells(i, 1) Then

Rows(i).Delete

End If

Next i

Me.code_student.Value = ""

Me.name_txt.Value = ""

Me.age_txt.Value = ""

imagefile = ""

Me.Image1.Picture = LoadPicture(imagefile)

End Sub


Private Sub UserForm_Click()

End Sub




Microsoft Excel Search| search in all columns Excel Using ComboBox in VBA #Excel userforms

 Dim columnindex_v As Integer

Private Sub populate_combo1()

Dim i, lastcolumn As Integer

lastcolumn = Sheet5.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To lastcolumn

Me.ComboBox1.AddItem Sheet5.Cells(1, i).Value

Next i

End Sub


Private Sub ComboBox1_Change()

getcolumnindex

getcolumnname

populate_combo2

End Sub

Private Sub getcolumnindex()

columnindex_v = WorksheetFunction.Match(Me.ComboBox1.Text, Sheet5.Range("1:1"), 0)

Me.columnindex_txt.Caption = columnindex_v

End Sub

Private Sub getcolumnname()

Me.columnname_txt.Caption = Split(Sheet5.Columns(columnindex_v).EntireColumn.Address(0, 0), ":")(0)

End Sub

Private Sub search_listbox1()

With Me.ListBox1

.Clear

.AddItem "RN"

.List(.ListCount - 1, 1) = "ID"

.List(.ListCount - 1, 2) = "Firstname"

.List(.ListCount - 1, 3) = "Lastname"

.List(.ListCount - 1, 4) = "Sport"

.List(.ListCount - 1, 5) = "Points"

.ColumnCount = 6

Dim lastrow, i As Integer

lastrow = Sheet5.Cells(Rows.Count, Me.columnname_txt.Caption).End(xlUp).Row

For i = 2 To lastrow

If Me.ComboBox2.Text = Sheet5.Cells(i, columnindex_v) Then

.AddItem

.List(.ListCount - 1, 0) = .ListCount - 1

.List(.ListCount - 1, 1) = Sheet5.Cells.Range("A" & i)

.List(.ListCount - 1, 2) = Sheet5.Cells.Range("B" & i)

.List(.ListCount - 1, 3) = Sheet5.Cells.Range("C" & i)

.List(.ListCount - 1, 4) = Sheet5.Cells.Range("D" & i)

.List(.ListCount - 1, 5) = Sheet5.Cells.Range("E" & i)

End If

Next i

End With

End Sub

Private Sub ComboBox2_Change()

search_listbox1

End Sub


Private Sub UserForm_Initialize()

populate_combo1

End Sub

Private Sub populate_combo2()

Me.ComboBox2.Clear

Dim lastrow, i As Integer

lastrow = Sheet5.Cells(Rows.Count, Me.columnname_txt.Caption).End(xlUp).Row

For i = 2 To lastrow

Dim value_str As String

value_str = Sheet5.Cells(i, Me.columnname_txt.Caption)

If WorksheetFunction.CountIf(Sheet5.Range(Me.columnname_txt.Caption & 2, Me.columnname_txt.Caption & i), value_str) = 1 Then

Me.ComboBox2.AddItem value_str

End If

Next i

End Sub




visual Basic.net programmer Registration Form in VB.net



Imports System.IO

Imports System.Data.SqlClient

Public Class Form1

    Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged

        If CheckBox1.Checked = True Then

            password.UseSystemPasswordChar = False

        Else

            password.UseSystemPasswordChar = True

        End If

    End Sub


    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        OpenFileDialog1.ShowDialog()

        image_path.Text = Path.GetDirectoryName(OpenFileDialog1.FileName) & "\" & Path.GetFileName(OpenFileDialog1.FileName)

        PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)

    End Sub


    Private Sub lastname_KeyDown(sender As Object, e As KeyEventArgs) Handles lastname.KeyDown

        If e.KeyCode = Keys.Enter Then

            username.Text = firstname.Text + lastname.Text

        End If

    End Sub


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        If username.Text = "" Then

            MessageBox.Show("Please enter username")

            Return

        End If

        If m1.Checked = False And f1.Checked = False Then

            MessageBox.Show("Please select gender")

            Return

        End If

        Dim conn As New SqlConnection("Data source=.;initial catalog=register2;integrated security=true")

        If conn.State = ConnectionState.Closed Then

            conn.Open()

        End If

        Dim cmd As New SqlCommand("Select username from table1 where username=@username", conn)

        cmd.Parameters.AddWithValue("username", username.Text)

        Dim myreader As SqlDataReader = cmd.ExecuteReader

        If (myreader.Read()) Then

            MessageBox.Show("Username inserted before")

            conn.Close()

            Return

        Else

            conn.Close()


            Dim cmd2 As New SqlCommand("Insert into table1(firstname,lastname,username,password,phone,date_birth,gender,image1)Values(@firstname,@lastname,@username,@password,@phone,@date_birth,@gender,@image1)", conn)

            cmd2.Parameters.AddWithValue("firstname", firstname.Text)

            cmd2.Parameters.AddWithValue("lastname", lastname.Text)

            cmd2.Parameters.AddWithValue("username", username.Text)

            cmd2.Parameters.AddWithValue("password", password.Text)

            cmd2.Parameters.AddWithValue("phone", phone.Text)

            cmd2.Parameters.AddWithValue("date_birth", date_birth.Text)



            Dim gender_v As Boolean

            If m1.Checked = True Then

                gender_v = 1

            End If

            If f1.Checked = True Then

                gender_v = 0

            End If


            cmd2.Parameters.AddWithValue("gender", gender_v)

            cmd2.Parameters.AddWithValue("image1", image_path.Text)


            If conn.State = ConnectionState.Closed Then

                conn.Open()

            End If

            cmd2.ExecuteNonQuery()

            conn.Close()

            MessageBox.Show("Data inserted Successfully")

        End If

    End Sub

End Class

 

C# tutorial: search, update and delete buttons with(all data types) With SQL Server with Source Code

 





using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace Retrieve_data_from_Sql_server_database

{

    public partial class Form2 : Form

    {

        public Form2()

        {

            InitializeComponent();

        }


        private void Form2_Load(object sender, EventArgs e)

        {

            dateTimePicker1.Format = DateTimePickerFormat.Custom;

            dateTimePicker1.CustomFormat = " ";

        }


        private void button1_Click(object sender, EventArgs e)

        {

            SqlConnection conn1 = new SqlConnection("Data Source=.;Initial Catalog=db1;Integrated Security=true");

            conn1.Open();

            SqlCommand cmd1 = new SqlCommand("Select firstname,lastname,phone,gender,date_birth,image1 From Table1 Where id=@idpar", conn1);

            cmd1.Parameters.AddWithValue("idpar", idtxt.Text.Trim());

            SqlDataReader reader1;

            reader1 = cmd1.ExecuteReader();

            if (reader1.Read())

            {

                ftxt.Text = reader1["firstname"].ToString();

                ltxt.Text = reader1["lastname"].ToString();

                phtxt.Text = reader1["phone"].ToString();

                dateTimePicker1.Format = DateTimePickerFormat.Custom;

                dateTimePicker1.CustomFormat = "MM-dd-yyyy";

                dateTimePicker1.Text = reader1["date_birth"].ToString();

                pictureBox1.Image = Image.FromFile(reader1["image1"].ToString());

                Boolean gender_v = (bool)reader1["gender"];

                if (gender_v==true)

                {

                    m1.Checked = true;

                }

                else

                {

                    f1.Checked = true;

                }

            }

            else

            {

                ftxt.Text = "";

                ltxt.Text = "";

                phtxt.Text = "";

                dateTimePicker1.Format = DateTimePickerFormat.Custom;

                dateTimePicker1.CustomFormat = " ";

                pictureBox1.Image = null;

                m1.Checked = false;

                f1.Checked = false;

                MessageBox.Show("No data found");

            }

            conn1.Close();

        }


        private void browse_btn_Click(object sender, EventArgs e)

        {

            openFileDialog1.Filter = "Image|*.jpg;png";

            if (openFileDialog1.ShowDialog() == DialogResult.OK)

                pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);

            else

                pictureBox1.Image = null;

        }


        private void update_btn_Click(object sender, EventArgs e)

        {

            SqlConnection conn2 = new SqlConnection("Data Source=.;Initial Catalog=db1;Integrated Security=true");

            SqlCommand cmd2 = new SqlCommand("Update Table1 Set firstname=@fname,lastname=@lname,phone=@phone,gender=@gender,image1=@image,date_birth=@dateb Where id=@id", conn2);

            cmd2.Parameters.AddWithValue("fname", ftxt.Text.Trim());

            cmd2.Parameters.AddWithValue("lname", ltxt.Text.Trim());

            cmd2.Parameters.AddWithValue("phone", phtxt.Text.Trim());

            int gg;

            if (m1.Checked==true)

            {

                gg = 1;

            }

            else

            {

                gg = 0;

            }

            cmd2.Parameters.AddWithValue("gender", gg);

            cmd2.Parameters.AddWithValue("image", openFileDialog1.FileName);

            cmd2.Parameters.AddWithValue("dateb", dateTimePicker1.Value);

            cmd2.Parameters.AddWithValue("id", idtxt.Text.Trim());

            conn2.Open();

            cmd2.ExecuteNonQuery();

            conn2.Close();

            MessageBox.Show("Data updated successfully");

        }


        private void button2_Click(object sender, EventArgs e)

        {

            if (MessageBox.Show("Are you want to delete this row?", "Delete row", MessageBoxButtons.YesNo, MessageBoxIcon.Question)== DialogResult.Yes)

            {

                SqlConnection conn3 = new SqlConnection("Data Source=.;Initial Catalog=db1;Integrated Security=True");

                SqlCommand cmd3 = new SqlCommand("Delete From Table1 Where id=@id", conn3);

                cmd3.Parameters.AddWithValue("id", idtxt.Text.Trim());

                conn3.Open();

                cmd3.ExecuteNonQuery();

                conn3.Close();

                idtxt.Text = "";

                ftxt.Text = "";

                ltxt.Text = "";

                phtxt.Text = "";

                dateTimePicker1.Format = DateTimePickerFormat.Custom;

                dateTimePicker1.CustomFormat = " ";

                pictureBox1.Image = null;

                m1.Checked = false;

                f1.Checked = false;

                MessageBox.Show("ID deleted Successfully");

            }

        }

    }

}