title: Updating database from data in Gridview in VB
body: <p>This is the code for displaying data from database to gridview....If i am doing any changes in GridView and it has to update in database...I dont know how to do it...Can anyone help me....</p>
  
  <pre><code>    Dim DBCONSRT, QRYSTR As String
  
      Dim strSQL As String
  
      Dim DBCON, myConn, myCommand, rs As Object
  
      Dim NoOfRecords As Long
      rs = CreateObject("ADODB.Recordset")
      Const DB_CONNECT_STRING = "Provider=MSDASQL.1;Persist Security Info=False;User ID=cpa5k;Data Source=NP1;DSN=NP1;UID=cpa5k;PASSWORD=pass;SDSN=Default;HST=ibslnpb1.sysplex.homedepot.com;PRT=4101;Initial Catalog=QA1MM;"
      myConn = CreateObject("ADODB.Connection")
      myCommand = CreateObject("ADODB.Command")
      myConn.Open(DB_CONNECT_STRING)
      myCommand.ActiveConnection = myConn
      strSQL = "select * from QA1MM.STRSK_OH FETCH FIRST 10 ROWS ONLY with ur;"
      rs.Open(strSQL, myConn)
      Dim myDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
      Dim myDS As DataSet = New DataSet
  
      myDA.Fill(myDS, rs, "MyTable")
      DataGridView1.DataSource = myDS.Tables(0)
      DataGridView1.Refresh()
      myConn.Close()
  </code></pre>
  
  <p>I am trying this method.but it is not working</p>
  
  <pre><code>    Dim myDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
      Dim myDS As DataSet = New DataSet
      myDA.Fill(myDS, rs, "MyTable")
      ds = DataGridView1.DataSource
      myDA.Update(ds)
  </code></pre>
  
  <p>I changed like this:</p>
  
  <pre><code>    Dim DBCONSRT, QRYSTR As String
      Dim strSQL As String
      Dim DBCON, myConn, myCommand, rs As Object
      Dim ds As DataSet = New DataSet
      rs = CreateObject("ADODB.Recordset")
      Const DB_CONNECT_STRING = "Provider=MSDASQL.1;Persist Security Info=False;User ID=cpa5k;Data Source=NP1;DSN=NP1;UID=cpa5k;PASSWORD=mexico13;SDSN=Default;HST=ibslnpb1.sysplex.homedepot.com;PRT=4101;Initial Catalog=QA1MM;"
      myConn = CreateObject("ADODB.Connection")
      myCommand = CreateObject("ADODB.Command")
      myConn.Open(DB_CONNECT_STRING)
      myCommand.ActiveConnection = myConn
  
      Dim myDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
      Dim myDS As DataSet = New DataSet
      Dim dtable As DataTable = New DataTable()
      myDA.UpdateCommand = New SqlClient.SqlCommand("UPDATE QA1MM.STRSK_OH set OH_QTY = 10  WHERE SKU_NBR = 108011", myConn)
      '   myDA.UpdateCommand.Parameters.Add("@OH_QTY", OleDb.OleDbType.VarChar, 15, "OH_QTY")
      '   myDA.UpdateCommand.Parameters.Add("@SKU_NBR", OleDb.OleDbType.VarChar, 15, "SKU_NBR")
      '   myDA.UpdateCommand.Parameters(0).SourceVersion = DataRowVersion.Current
      '   myDA.UpdateCommand.Parameters(1).SourceVersion = DataRowVersion.Current
      dtable = DataGridView1.DataSource
      myDA.Update(dtable)
      myConn.Close()
  </code></pre>
  
  <p>But it is giving error like "Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface." </p>
  
  <p>I changed the connection string like:</p>
  
  <pre><code>Provider=MSDASQL.1;Persist Security Info=False;Data 
  Source=NP1;DSN=NP1;SDSN=Default;HST=ibslnpb1.sysplex.homedepot.com;PRT=4101;Integrated 
  Security = True;Initial Catalog=QA1MM;
  </code></pre>
  
  <p>Its not working.The error is "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".....Sorry i dont know how to change.....</p>