SQL Update Statement in VB.net

[eileenc87]

Dear All,

When i execute my queries for Edit and Save for edited subject, there is no updated in my sql database. Appreciate if anyone could lend me a hand for this issue.

My queries are as below.

Private ConnString As String = "server=localhost; Integrated Security=SSPI;Persist Security Info=False;database=carerpt"

Public Sub UpdateMyData(ByVal myConnString As String, ByVal Meeting_Subject As String, ByVal Meeting_Date As String, ByVal StartTime_Planned As String, ByVal EndTime_Planned As String, ByVal StartTime_Actual As String, ByVal EndTime_Actual As String, ByVal ChairedBy As String, ByVal Department As String, ByVal Logged_By As String)

Dim myInsertQuery As String = "Update Meeting Set Meeting_Date = "", StartTime_Planned = "", EndTime_Planned = "", StartTime_Actual = "", EndTime_Actual = "", ChairedBy = "", Department = "" , Logged_By = "" Where ( Meeting_Subject = '" + Meeting_Subject + "' )"

Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myInsertQuery, myConnection)
'Dim retvalue As Integer
myConnection.Open()
'retvalue = myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim fieldName1 As String = txtMeetingSubject.Text
Dim fieldName2 As String = txtMeetingDate.Text
Dim fieldName3 As String = txtStartTime_Planned.Text
Dim fieldName4 As String = txtEndTime_Planned.Text
Dim fieldName5 As String = txtStartTime_Actual.Text
Dim fieldName6 As String = txtEndTime_Actual.Text
Dim fieldName7 As String = txtChairedBy.Text
Dim fieldName8 As String = txtDepartment.Text
Dim fieldName9 As String = txtLoggedBy.Text
UpdateMyData(ConnString, fieldName1, fieldName2, fieldName3, fieldName4, fieldName5, fieldName6, fieldName7, fieldName8, fieldName9)

End Sub

[Jx_Man]

Dim myInsertQuery As String = "Update Meeting Set Meeting_Date = "", StartTime_Planned = "", EndTime_Planned = "", StartTime_Actual = "", EndTime_Actual = "", ChairedBy = "", Department = "" , Logged_By = "" Where ( Meeting_Subject = '" + Meeting_Subject + "' )"

your sql statment is wrong. your query is missing single quote and ampersand sign, and you didn't assign any fieldname with any parameter.
I confused to recognize what is your fieldname and what is your paramater name.
Your code should like this :

Dim myInsertQuery As String = "Update Meeting Set Meeting_Date = '" & Meeting_Date  & "', StartTime_Planned = '" & StartTime_Planned &"', EndTime_Planned = '" & EndTime_Planned &"', StartTime_Actual = '" & StartTime_Actual &"', EndTime_Actual = '" & EndTime_Actual &"', ChairedBy = '" & ChairedBy  &"', Department = '" & Department  &"' , Logged_By = '" & Logged_By &"' Where ( Meeting_Subject = '" & Meeting_Subject & "' )"

if you want to get value direct from text boxes then your code should be like this following example:

"UPDATE Users SET Id_User='" & Trim(txtUserId.Text) & "', Id_Role= '" & Trim(txtIdRole.Text) & "' , Password='" & Trim(txtPasw.Text) & "' WHERE Id_User='" & Trim(txtUserId.Text) & "'"

You should to execute your query :

myCommand.ExecuteNonQuery()

[debasisdas]

try this sample, may help.

myConnection.Open()
cmd = New SqlCommand(Sql, myConnection)
cmd.ExecuteNonQuery()
cmd.Dispose()
cnn.Close()
myConnection.Close()

[eileenc87]

Thanks guy!!
I've got what I want.

But one question here, after I updated my SQL database, how do I refresh my datagridview with the updated data?

[debasisdas]

You need to requery / refresh the control from actual data in the database.

[eileenc87]

Any example for this?? I dont get the real figure for this...

[Jx_Man]

just write the code to display data into datagrid and call it after update data..

[Jx_Man]

Try this following code to refresh datagrid :

Private Sub Refresh_DataGrid()
        Dim conn As SqlConnection
        Dim cmdTest As New SqlCommand
        Dim daTest As New SqlDataAdapter
        Dim dsTest As New DataSet
        Dim dtTest As New DataTable

        conn = GetConnect()
        Try
            cmdTest = conn.CreateCommand
            cmdTest.CommandText = "SELECT * FROM Meeting"
            daTest.SelectCommand = cmdTest
            daTest.Fill(dsTest, "Meeting")
            DataGridView1.DataSource = dsTest
            DataGridView1.DataMember = "Meeting"
            DataGridView1.ReadOnly = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
        End Try
    End Sub

You can call this procedure after update :

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim fieldName1 As String = txtMeetingSubject.Text
...
...
UpdateMyData(ConnString, fieldName1, fieldName2, fieldName3, fieldName4, fieldName5, fieldName6, fieldName7, fieldName8, fieldName9)

' Call Refresh_DataGrid Procedure
Refresh_DataGrid
End Sub

Also you can use this procedure to refresh datagrid after add or delete data..