Updating a bit field with a VB checkbox and a Stored Procedure

Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged

If Me.CheckBox1.Checked Then

UpdateChecked(0, True)

Else

UpdateChecked(0, False)

End If

End Sub

Private Sub UpdateChecked(ByVal ID As Integer, ByVal Checked As Boolean)

Dim MyCommand As New Data.SqlClient.SqlCommand()

Dim MyParameter As New Data.SqlClient.SqlParameter

MyCommand.Connection = MyConnection

MyParameter.Direction = ParameterDirection.Input

MyParameter.Value = ID

MyCommand.Parameters.Add(MyParameter)

MyCommand.CommandType = CommandType.StoredProcedure

If Checked Then

MyCommand.CommandText = "Checked"

Else

MyCommand.CommandText = "UnChecked"

End If

Try

If MyConnection.State <> ConnectionState.Open Then

MyConnection.Open()

End If

MyCommand.ExecuteNonQuery()

MyConnection.Close()

Catch ex As Exception

'error handling

End Try

End Sub

DMan1 at 2007-9-4 >

I can explain how to do it without using datasets, using typed dataset and table adapters are quite easy actually, it creates everythin automatically for you, but it would take a litle time to explain..

First of all you don't need two stored procedures to do what you want to do, You want to update a bit field to 1 or 0 so, you can sen it as a parameter as well...

Here is the stored procedure:

Create proc [dbo].[SetCheckStatu]

@id int

@Checked bit

as

update Check

set Checked = @Checked

where ID=@ID

So.. You have the stored procedure.. Now lets run this procedure from your code

Open your windows forms code page and write the folloewing code into your checkBoxes CheckedChanged event

Dim conStr as String = "Server = YOURSERVER; Database= YUORDATABASE; UID=YourUserName; PWD=YourPassword"

Dim conn as new SQLCLient.SQLConnection (conStr)

Dim cmd as new SQLClient.SQLCommand

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "SetCheckStatu"

cmd.Parameters.Add("@Checked",Me.CheckBox.Checked) ''if the combobox is selected than it will return 1 else it will return 0

cmd.Parameters.Add("@Id", ID) ' your ID variable

conn.open

cmd.executeNoneQuery

conn.close

Sooo everytime you check or uncheck your checkbox, this commad will run, and send the statu as a parameter..

I hope it helps,

Success

AyhanYerli at 2007-9-4 >