?
Solved

Update SQL From DataGrid.

Posted on 2006-11-21
12
Medium Priority
?
404 Views
Last Modified: 2012-05-05
Windows Application:

When I have a user edit a field in my datagrid, how can I have it update SQL?  So if they edit lets say a BOOLEAN, and after the form is closed, the update to the grid, literally updates SQL?

I have never worked with anything other than generic SELECT and filling of the datagrid.  Never using the grid to modify the actual data in SQL.

0
Comment
Question by:chrisryhal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
12 Comments
 
LVL 6

Expert Comment

by:sara110
ID: 17986777
you could find some example in following websites , you need to hide your primary key in your datagrid and use it when you want to update and on double click event or other event or click ... you could update
http://www.vb-helper.com/howto_net_datagrid.html
0
 
LVL 6

Expert Comment

by:sara110
ID: 17986817
0
 
LVL 2

Author Comment

by:chrisryhal
ID: 17986848
Sara110:  I am in VB, sorry, I don't know how to compare the code unfortuantly.  I am reviewing the examples you provided righ tnow.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Accepted Solution

by:
sara110 earned 2000 total points
ID: 17986908
vb.net example
http://www.codeproject.com/vb/net/grid101.asp

  Private Sub datagrid1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dg1.DoubleClick
'dgindex is assume your primarykey string or integer : took primary key for updating that data
dgindex = datagrid1.Item(datagrid1.CurrentRowIndex, "0")
'you could access to each cell of your datagrid by changing "0" and using 1 ... instead
end sub

0
 
LVL 6

Expert Comment

by:sara110
ID: 17986915
the first example in vbhelper is very simple one
0
 
LVL 2

Author Comment

by:chrisryhal
ID: 17986921
Based on your first example, I have successfully populated the datagrid.  However, on closing of hte form as your example states, I get the following error if I lose focus on the row I am working with.:

          An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
          Additional information: System error.


If I still have focus on the row I am changing, I dont get an error, but it doesn't update SQL.
   


\\\\\\\\\\\\\\\\\\\MY CODE MODIFIED FROM YOUR EXAMPLE\\\\\\\\\\\\\\\\

 Private Const SELECT_STRING As String = _
        "SELECT Top 50 * FROM TaskRost Order By [Database Number]"
    Private Const CONNECT_STRING As String = _
        "Data Source=;" & _
  "Initial Catalog=;" & _
  "User ID=;" & _
  "Password="

    ' The DataSet that holds the data.
    Private m_DataSet As DataSet

    ' Load the data.
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim data_adapter As SqlDataAdapter

        ' Create the SqlDataAdapter.
        data_adapter = New SqlDataAdapter(SELECT_STRING, CONNECT_STRING)

        ' Map Table to Contacts.
        data_adapter.TableMappings.Add("Table", "TaskRost")

        ' Fill the DataSet.
        m_DataSet = New DataSet
        data_adapter.Fill(m_DataSet)

        ' Bind the DataGrid control to the Contacts DataTable.
        ' Changed by Jim Rogers (jrogers@antaresnet.com)
        dgContacts.SetDataBinding(m_DataSet, "TaskRost")
        'dgContacts.DataSource = m_DataSet.Tables("Contacts")
    End Sub

    ' Save any changes to the data.
    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If m_DataSet.HasChanges() Then
            Dim data_adapter As SqlDataAdapter
            Dim command_builder As SqlCommandBuilder

            ' Create the DataAdapter.
            data_adapter = New SqlDataAdapter(SELECT_STRING, CONNECT_STRING)

            ' Map Table to Contacts.
            data_adapter.TableMappings.Add("Table", "TaskRost")

            ' Make the CommandBuilder generate the
            ' insert, update, and delete commands.
            command_builder = New SqlCommandBuilder(data_adapter)

            ' Uncomment this code to see the INSERT,
            ' UPDATE, and DELETE commands.
            Debug.WriteLine("*** INSERT ***")
            Debug.WriteLine(command_builder.GetInsertCommand.CommandText)
            Debug.WriteLine("*** UPDATE ***")
            Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
            Debug.WriteLine("*** DELETE ***")
            Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)

            ' Save the changes.
            data_adapter.Update(m_DataSet)
        End If
    End Sub
0
 
LVL 6

Expert Comment

by:sara110
ID: 17986969
you could inplace update data by writing code for changeEvent , or Click , end edit  or ... ,
my above code is for double click and  let your user to double click and have another select from your database using datatable to load information in textboxes and then easily update your data

for updating database when you have your primarykey and your updated data from your grid
by this method
dgindex = datagrid1.Item(datagrid1.CurrentRowIndex, "0")
then easily you could call your update function which could be like this


Public Function Updatefunction(strNewdata as string,PK as integer) As Boolean
      dim cnn As New SqlConnection
       cnn.ConnectionString = "server=ipaddressofyourServer;initial catalog=yourdatabaseName;User ID=sa;password="
        Dim cmdUpdate As New SqlCommand
        Try
           
            With cmdUpdate
                .Connection = cnn
                .Parameters.Add("@strNewdata", SqlDbType.VarChar, 200)
                .Parameters(0).Value = strnewdata


                        .CommandText = "update yourtablename set yourColumnName= @strNewData  WHERE ID  = '" & PK  & "'"
                End Select

            End With
            cnn.Open()
            cmdUpdate.ExecuteNonQuery()
            cnn.Close()
            Return True
        Catch ex As Exception
            cnn.Close()
            Return False
        End Try
    End Function

0
 
LVL 2

Author Comment

by:chrisryhal
ID: 17987038
Quick question:

What is the correction of this line of code for SQL?

      Dim DBSchema As New OleDb.OleDbSchemaGuid

I am not seeing a SchemaGuid for SQL in the CLASS explorer.
0
 
LVL 6

Expert Comment

by:sara110
ID: 17987083
I haven't used this class yet  but this is microsoft reference:
http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid.aspx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question