CODEWITHSUNDEEP
vb.net
user1271146
user1271146

Reputation: 45

Updating database from data in Gridview in VB

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....

    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()

I am trying this method.but it is not working

    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)

I changed like this:

    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()

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."

I changed the connection string like:

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;

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.....

Upvotes: 0

Views: 5461

Answers (2)

badprogrammer
badprogrammer

Reputation: 73

Try this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        DataSet1.Tables("employee").AcceptChanges()
        Dim i As Integer
        Dim cmdbuilder As New OdbcCommandBuilder(odbcadptr)
        i = odbcadptr.Update(odbcds, "customer")
        MsgBox("Updated Rows: " & i)
End Sub

Upvotes: 0

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

You write like follow

myConn.Open(DB_CONNECT_STRING) 
myDA.Update(ds.Tables(0))
myConn.Close()

EDIT:

Dont take the datasource of DataGridView1 into a dataset, instead take into a DataTable as follows

            Dim dtable As New DataTable()
            dtable = DataGridView1.DataSource

and update the table with DataAdapter as

myConn.Open(DB_CONNECT_STRING) 
myDA.Update(dtable)
myConn.Close()

Refer to this link How to write UpdateCommand to OleDBDataAdapter

Upvotes: 2

Related Questions