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

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