Posted by: Sourav | June 1, 2015

Insert record in SQL server using VB.Net


Insert new records from vb.net to sql server

Rename the next page of tab control as insert

Click on insert

Add a groupbox to insert

Click on the groupbox control

Rename it to new member

Now inside that groupbox

Add label Id and it’s corresponding textbox

Add label Name and it’s corresponding textbox

Add label Id and it’s corresponding textbox

Add label Age and it’s corresponding textbox

Add label Username and it’s corresponding textbox

Add label Password and it’s corresponding textbox

Add a checkbox and make it’s text to active

Add a button and change it’s text to insert

Now change the maxlength property of your textbox controls as per your database structure

Now star sql server management studio

Connect and go to Tools-Options-Designers and then

And uncheck prevent saving changes that require table re-creation

And the make the database look like this

ID int not null

Name nchar(10) null

Age int null

Username nvarchar(50) null

Password nchar(10) null

Active bit null

To make the id autonumber

Select the column go to it’s properties

Identity specification yes

Isidentity yes

Identity Increment 1

Identity Seed 1

Now make the sqlcon.vb look like this

Imports System.Data.Sql

Imports System.Data.SqlClient

Public Class Sqlcon

   Public sqlcon As New SqlConnection With {.ConnectionString = “Server=HOMEPC\SQLEXPRESS;Database=test;User=sa;Pwd=123456;”}

   Public sqlcommand As SqlCommand

   Public sqldataadapter As SqlDataAdapter

   Public sqldataset As DataSet

   Public Function hasconnection() As Boolean

       Try

           sqlcon.Open()

           sqlcon.Close()

           Return True

       Catch ex As Exception

           MsgBox(ex.Message)

           Return False

       End Try

   End Function

   Public Function findlastid() As Integer

       Dim query As String = “SELECT MAX(ID) FROM test”

       Dim sqlcommand As New SqlCommand(query, sqlcon)

       sqlcommand.CommandType = CommandType.Text

       Dim id As Integer = 0

       Try

           sqlcon.Open()

           id = Convert.ToInt32(sqlcommand.ExecuteScalar())

           Return id

       Catch ex As Exception

           MsgBox(ex.Message)

           Return 0

       Finally

           sqlcon.Close()

       End Try

   End Function

   Public Sub runquery(query As String)

       Try

           sqlcon.Open()

           sqlcommand = New SqlCommand(query, sqlcon)

           ‘Dim r As SqlDataReader = sqlcommand.ExecuteReader

           ‘While r.Read

           ‘MsgBox(r.GetName(0) & “:” & r(0))

           ‘End While

           sqldataadapter = New SqlDataAdapter(sqlcommand)

           sqldataset = New DataSet

           sqldataadapter.Fill(sqldataset)

           sqlcon.Close()

       Catch ex As Exception

           MsgBox(ex.Message)

           If sqlcon.State = ConnectionState.Open Then

               sqlcon.Close()

           End If

       End Try

   End Sub

   Public Sub addmember(name As String, age As Integer, username As String, password As String, active As Integer)

       Try

           Dim querystring As String = “insert into test (Name,Age,Username,Password,Active) values (” & “‘” & “name” & “‘,'” & “age” & “‘,'” & “username” & “‘,'” & “password” & “‘,'” & “active” & “‘ )”

           MsgBox(querystring)

           sqlcon.Open()

           sqlcommand = New SqlCommand(querystring, sqlcon)

           Dim insertstring As String

           insertstring = “insert into test (Name,Age,Username,Password,Active) values(‘” & name & “‘, ‘” & age & “‘, ‘” & username & ” ‘, ‘” & password & ” ‘, ‘” & active & ” ‘)”

           ‘ sqlcommand.CommandText = “insert into test (Name,Age,Username,Password,Active) values(‘” & name & “‘, ‘” & age & “‘, ‘” & username & ” ‘, ‘” & password & ” ‘, ‘” & active & ” ‘)”

           sqlcommand = New SqlCommand(insertstring, sqlcon)

           sqlcommand.ExecuteNonQuery()

           sqlcon.Close()

       Catch ex As Exception

           MsgBox(ex.Message)

           If sqlcon.State = ConnectionState.Open Then

               sqlcon.Close()

           End If

       End Try

   End Sub

End Class

The form load event should call

Call populatenextid()

To make the id field prepopulate with the next id

Add

Private Sub populatenextid()

       ‘Dim id As Integer = sqlcon.findlastid

       TextBox2.Text = Convert.ToString(sqlcon.findlastid + 1)

   End Sub

To the form

The insert button should like this

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

       sqlcon.runquery(“select * from test where Username = ‘” & TextBox5.Text & “‘”)

       If sqlcon.sqldataset.Tables(0).Rows.Count > 0 Then

           MsgBox(“user already exists”)

           Exit Sub

       Else

           adduser()

           TextBox3.Text = “”

           TextBox4.Text = “”

           TextBox5.Text = “”

           TextBox6.Text = “”

           CheckBox1.Checked = False

           Call populatenextid()

       End If

   End Sub

The sub adduser should look like this

Private Sub adduser()

       If Len(TextBox5.Text) >= 5 And Len(TextBox6.Text) >= 5 Then

           Dim active As Integer

           If CheckBox1.Checked = True Then

               active = 1

           Else

               active = 0

           End If

           sqlcon.addmember(TextBox3.Text, Convert.ToInt32(TextBox4.Text), TextBox5.Text, TextBox6.Text, active)

       Else

           MsgBox(“username or password is too short”)

          Exit Sub

       End If

   End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: