Forum guidelines
  • Home
  • »
  • .NET General
  • »
  • insert, update & delete code in vb.net with sql server.
AuthorQuestion
insert, update & delete code in vb.net with sql server.
Posted on: 06 Feb 2013
 insert, update & delete  in vb.net with sql server with login page..

Hardik Patel
AuthorReply
Re: insert, update & delete code in vb.net with sql server.
Posted on: 06 Feb 2013  
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


If this post helps you mark it as answer
Thanks

Re: insert, update & delete code in vb.net with sql server.
Posted on: 06 Feb 2013  
vb-access.rar 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





If this post helps you mark it as answer
Thanks

SPONSORED BY

Offshore Software and Database Development
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.