Posted by: Sourav | June 17, 2015

Update in VB.NET,SQL Server


My Update Tab Page has one label having text property Search 

one textbox named textbox9 which has the code

Private Sub TextBox9_TextChanged(sender As Object, e As EventArgs) Handles TextBox9.TextChanged
If TextBox9.Text <> “” Then

If sqlcon.hasconnection = True Then
sqlcon.runquery(“select * from test where Name like ‘” & TextBox9.Text & “%'”)
If sqlcon.sqldataset.Tables.Count > 0 Then
DataGridView4.DataSource = sqlcon.sqldataset.Tables(0)
Else
DataGridView4.DataSource = Nothing
DataGridView4.Rows.Clear()
DataGridView4.Columns.Clear()

End If
End If
Else

If sqlcon.hasconnection = True Then
sqlcon.runquery(“select * from test order by ID”)
If sqlcon.sqldataset.Tables.Count > 0 Then
DataGridView4.DataSource = sqlcon.sqldataset.Tables(0)

End If
End If

End If
End Sub

I have a datagridview named datagridview4 below,whenever an user types the first or more character of a name the sql like operator will work and the

datagridview will populate itself having records containing the names possible matching,for example if

n pressed record of navin will appear in datagridview4

Now for update if I click on any cell of a row(record) in datagridview4,there are textboxes which will be populated with the record being clicked on

textbox10.text(for ID)

textbox11.text(for Name)

textbox12.text(for Age)

textbox13.text(for Username)

textbox14.text(for Password)

and there is checkbox2 having text property active

To make that happen my datagridview4 has code like this

Private Sub DataGridView4_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView4.CellClick
If e.RowIndex >= 0 Then
Dim row As DataGridViewRow
row = Me.DataGridView4.Rows(e.RowIndex)
TextBox10.Text = (row.Cells(“ID”).Value.ToString)
TextBox11.Text = (row.Cells(“Name”).Value.ToString)
TextBox12.Text = (row.Cells(“Age”).Value.ToString)
TextBox13.Text = (row.Cells(“Username”).Value.ToString)
TextBox14.Text = (row.Cells(“Password”).Value.ToString)
If (row.Cells(“Active”).Value.ToString) = True Then
CheckBox2.Checked = True
Else
CheckBox2.Checked = False

End If
End If
End Sub

Private Sub DataGridView4_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView4.CellContentClick
If e.RowIndex >= 0 Then
Dim row As DataGridViewRow
row = Me.DataGridView4.Rows(e.RowIndex)
TextBox10.Text = (row.Cells(“ID”).Value.ToString)
TextBox11.Text = (row.Cells(“Name”).Value.ToString)
TextBox12.Text = (row.Cells(“Age”).Value.ToString)
TextBox13.Text = (row.Cells(“Username”).Value.ToString)
TextBox14.Text = (row.Cells(“Password”).Value.ToString)
If (row.Cells(“Active”).Value.ToString) = True Then
CheckBox2.Checked = True
Else
CheckBox2.Checked = False

End If
End If
End Sub

Now I also want when the user press enter or tab while he/she on datagridview4,the row being selected populates the 

textboxes such as

textbox10.text(for ID)

textbox11.text(for Name)

textbox12.text(for Age)

textbox13.text(for Username)

textbox14.text(for Password)

and there is checkbox2 having text property active

So to do that

Private Sub DataGridView4_KeyPress(sender As Object, e As KeyPressEventArgs) Handles DataGridView4.KeyPress
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
Dim currCell As DataGridViewCell = DataGridView4.CurrentCell

Dim row As New DataGridViewRow
If Not Me.DataGridView4.Rows(currCell.RowIndex) Is Nothing Then
row = Me.DataGridView4.Rows(currCell.RowIndex)
TextBox10.Text = (row.Cells(“ID”).Value.ToString)
TextBox11.Text = (row.Cells(“Name”).Value.ToString)
TextBox12.Text = (row.Cells(“Age”).Value.ToString)
TextBox13.Text = (row.Cells(“Username”).Value.ToString)
TextBox14.Text = (row.Cells(“Password”).Value.ToString)
If (row.Cells(“Active”).Value.ToString) = True Then
CheckBox2.Checked = True
Else
CheckBox2.Checked = False

End If

End If
e.Handled = True
End If
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Tab) Then
Dim currCell As DataGridViewCell = DataGridView4.CurrentCell

Dim row As New DataGridViewRow
If Not Me.DataGridView4.Rows(currCell.RowIndex) Is Nothing Then
row = Me.DataGridView4.Rows(currCell.RowIndex)
TextBox10.Text = (row.Cells(“ID”).Value.ToString)
TextBox11.Text = (row.Cells(“Name”).Value.ToString)
TextBox12.Text = (row.Cells(“Age”).Value.ToString)
TextBox13.Text = (row.Cells(“Username”).Value.ToString)
TextBox14.Text = (row.Cells(“Password”).Value.ToString)
If (row.Cells(“Active”).Value.ToString) = True Then
CheckBox2.Checked = True
Else
CheckBox2.Checked = False

End If

End If
e.Handled = True
End If

End Sub

Now I have data from datagridgridview to my corresponding textboxes,I have button

which has a text property update

so code for update button 

Private Sub Button4_Click_1(sender As Object, e As EventArgs) Handles Button4.Click

If TextBox13.Text <> “” And TextBox11.Text <> “” And TextBox14.Text <> “” Then

sqlcon.runquery(“select * from test where Username = ‘” & TextBox13.Text & “‘ and ID <> ‘” & TextBox10.Text & “‘”)
If sqlcon.sqldataset.Tables(0).Rows.Count > 0 Then

MsgBox(“UserName taken,please select a different username”)

Exit Sub
End If

sqlcon.runquery(“select * from test where Name = ‘” & TextBox11.Text & “‘ and ID <> ‘” & TextBox10.Text & “‘”)

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

MsgBox(“Name taken,please select a different name”)
Exit Sub
End If

Else
MsgBox(“Name Username or Password can not be blank”)
Exit Sub

End If

If Len(TextBox13.Text) >= 3 And Len(TextBox14.Text) >= 3 Then

Dim active As Integer
If CheckBox2.Checked = True Then
active = 1
Else
active = 0
End If
sqlcon.dataupdate(“update test set Name = ‘” & TextBox11.Text & “‘,Age='” & TextBox12.Text & “‘, Username='” & TextBox13.Text & “‘,Password='” & TextBox14.Text & “‘ ,Active='” & active & “‘ where ID='” & TextBox10.Text & “‘”)
populategrid2()
populategrid4()
Else
MsgBox(“Username or password too short”)
Exit Sub

End If

End Sub

*******”select * from test where Username = ‘” & TextBox13.Text & “‘ and ID <> ‘” & TextBox10.Text & “‘” the logic behind this statement is

I like to maintain unique username for everyone,to do that we have to find if this username exists in any other row except for the one

which is being updated,otherwise it will always come true and you will see username exists every time,so we need to search if this username exists 

in any other rows except for the one which is being updated,that’s why the  and ID <> ‘” & TextBox10.Text & “‘” part.******

The dataupdate subroutine is defined in the Sqlcon class

Public Sub dataupdate(query As String)
Try
sqlcon.Open()
sqlcommand = New SqlCommand(query, sqlcon)
Dim change As Integer = sqlcommand.ExecuteNonQuery
sqlcon.Close()
If change = 0 Then
MsgBox(“you haven’t found what you where looking for”)
Else
MsgBox(change & ” rows affected”)

End If

Catch ex As Exception
MsgBox(ex.Message)
If sqlcon.State = ConnectionState.Open Then
sqlcon.Close()
End If
End Try
End Sub

The populategrid2() and populategrid4() subroutines are defined in the form class

Public Sub populategrid2()
If sqlcon.hasconnection = True Then
sqlcon.populategridview(“select * from test order by ID”)
If sqlcon.sqldataset.Tables.Count > 0 Then
‘DataGridView1.DataSource = sqlcon.sqldataset.Tables(0)
DataGridView2.DataSource = sqlcon.sqldataset.Tables(0)
End If
End If

End Sub

Private Sub populategrid4()
If sqlcon.hasconnection = True Then
sqlcon.populategridview(“select * from test order by ID”)
If sqlcon.sqldataset.Tables.Count > 0 Then
‘DataGridView1.DataSource = sqlcon.sqldataset.Tables(0)
DataGridView4.DataSource = sqlcon.sqldataset.Tables(0)
End If
End If
End Sub

Sourav Bhattacharya
VB.Net Faculty

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: