insert, update & delete code in vb.net with sql server.

Question

insert, update & delete in vb.net with sql server with login page..

Satyapriya Nayak · Answer

insert, update & delete Public Class frmStudent Dim cnn As New OleDb.OleDbConnection Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click Me.Close() End Sub Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click Me.txtstdID.Text = "" Me.txtStdName.Text = "" Me.txtPhone.Text = "" Me.txtAddress.Text = "" Me.txtstdID.Tag = "" 'enable button edit Me.btnEdit.Enabled = True 'set button add to add label Me.btnAdd.Text = "Add" ' Me.txtstdID.Focus() End Sub Private Sub RefreshData() If Not cnn.State = ConnectionState.Open Then 'open connection cnn.Open() End If Dim da As New OleDb.OleDbDataAdapter("SELECT stdid as [ID], " & _ "stdname as [Name], Gender, Phone, Address " & _ " FROM student ORDER BY stdid", cnn) Dim dt As New DataTable 'fill data to datatable da.Fill(dt) 'offer data in data table into datagridview Me.dgvData.DataSource = dt 'close connection cnn.Close() End Sub Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Dim cmd As New OleDb.OleDbCommand If Not cnn.State = ConnectionState.Open Then 'open connection if it is not yet open cnn.Open() End If cmd.Connection = cnn 'check whether add new or update If Me.txtstdID.Tag & "" = "" Then 'add new 'add data to table cmd.CommandText = "INSERT INTO student(stdid, stdname, gender, phone, address) " & _ " VALUES(" & Me.txtstdID.Text & ",'" & Me.txtStdName.Text & "','" & _ Me.cboGender.Text & "','" & Me.txtPhone.Text & "','" & _ Me.txtAddress.Text & "')" cmd.ExecuteNonQuery() Else 'update data in table cmd.CommandText = "UPDATE student " & _ " SET stdid=" & Me.txtstdID.Text & _ ", stdname='" & Me.txtStdName.Text & "'" & _ ", gender='" & Me.cboGender.Text & "'" & _ ", phone='" & Me.txtPhone.Text & "'" & _ ", address='" & Me.txtAddress.Text & "'" & _ " WHERE stdid=" & Me.txtstdID.Tag cmd.ExecuteNonQuery() End If 'refresh data in list RefreshData() 'clear form Me.btnClear.PerformClick() 'close connection cnn.Close() End Sub Private Sub frmStudent_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load cnn = New OleDb.OleDbConnection cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & Application.StartupPath & "\data.mdb" ' 'get data into list Me.RefreshData() End Sub Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click 'check for the selected item in list If Me.dgvData.Rows.Count > 0 Then If Me.dgvData.SelectedRows.Count > 0 Then Dim intStdID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value 'get data from database followed by student id 'open connection If Not cnn.State = ConnectionState.Open Then cnn.Open() End If 'get data into datatable Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM student " & _ " WHERE stdid=" & intStdID, cnn) Dim dt As New DataTable da.Fill(dt) Me.txtstdID.Text = intStdID Me.txtStdName.Text = dt.Rows(0).Item("stdname") Me.cboGender.Text = dt.Rows(0).Item("gender") Me.txtPhone.Text = dt.Rows(0).Item("phone") Me.txtAddress.Text = dt.Rows(0).Item("address") ' 'hide the id to be edited in TAG of txtstdid in case id is changed Me.txtstdID.Tag = intStdID 'change button add to update Me.btnAdd.Text = "Update" 'disable button edit Me.btnEdit.Enabled = False 'close connection cnn.Close() End If End If End Sub Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click 'check for the selected item in list If Me.dgvData.Rows.Count > 0 Then If Me.dgvData.SelectedRows.Count > 0 Then Dim intStdID As Integer = Me.dgvData.SelectedRows(0).Cells("id").Value 'open connection If Not cnn.State = ConnectionState.Open Then cnn.Open() End If 'delete data Dim cmd As New OleDb.OleDbCommand cmd.Connection = cnn cmd.CommandText = "DELETE FROM student WHERE stdid=" & intStdID cmd.ExecuteNonQuery() 'refresh data Me.RefreshData() 'close connection cnn.Close() End If End If End Sub End Class

Satyapriya Nayak · Answer

Login code Imports System.Data Imports System.Data.SqlClient Public Class Form1 Dim ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings("dsn") Dim com As SqlCommand Dim str As String Dim obj As Object = Nothing Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click Dim con As New SqlConnection(ConnectionString) con.Open() str = "select count(*) from login where UserName=@UserName and Password =@Password" com = New SqlCommand(str, con) com.CommandType = CommandType.Text com.Parameters.AddWithValue("@UserName", TextBox_user_name.Text) com.Parameters.AddWithValue("@Password", TextBox_password.Text) obj = com.ExecuteScalar() If CInt(obj) 0 Then lb1.Text = "WELLCOME :: " + TextBox_user_name.Text Else lb1.Text = "invalid user name and password" End If con.Close() End Sub End Class