• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

how do i update a sql database from a datagrid.

Hi all

i have a form with a datagrid populated with a dataset, a sqdataAdapter  and a datatable. the form works great and the grid and all the other controlls are bound and working fine. my problem is that the are two views of the data. one view is the detailed view wich is on a tab control with textboxes and dropdaown list's, the other view i sthat of the datgrid. the problem lies in updating changes i make to the data i can only save or update the database with the 1st view with text boxes which i can only update one row at a time. for speed i would like to be able to make changes in the datagrid on multiple rows and save those changes to the datbase. i have tried some textbook examples but nothing eems to work does anyone have any code examples of how this can be achieved.

thanx

mackie1908
0
mackie1908
Asked:
mackie1908
1 Solution
 
arif_eqbalCommented:
If I understand you right, the scenario is like this
You have a datagrid showing a number of rows, then you have TextBoxes/Combo Boxes which show one row at a time. The user edits the items in these TextBoxes and then all such changes are reflected in the Grid.
Then you finally save it back, all rows in a batch, to the database, is it ??
0
 
SandeepRRCommented:
What i understand in ur problem is that
U are able to update the database via textbox.
Now what u want is user can edit the data in grid and u want to update the database with the values in the datagrid

for that, the solutin is......
add a update database button to ur Datagrid view TAB
and write the folowing code for updating database with datagrid values on that button click
------------------------------------------------------------------------------------
       Dim cn As New SqlConnection
        cn.ConnectionString = "connection string"
        cn.Open()
        Dim cmdUpdate As New SqlCommand
        cmdUpdate.Connection = cn
        cmdUpdate.CommandText = 'Update Command

for example----"Update Employees Set Name=@Name, Basic=@basic, Deptno=@Deptno where Empno = @empno"
--------and add sql paarmeter for the update query------
  Dim pUname As New SqlParameter
        pUname.ParameterName = "@Name"
        pUname.SourceColumn = "Name"
        pUname.SourceVersion = DataRowVersion.Current
        pUname.SqlDbType = SqlDbType.VarChar
        cmdUpdate.Parameters.Add(pUname)

        Dim pUBASIC As New SqlParameter
        pUBASIC.ParameterName = "@BASIC"
        pUBASIC.SourceColumn = "Basic"
        pUBASIC.SourceVersion = DataRowVersion.Current
        pUBASIC.SqlDbType = SqlDbType.Decimal
        cmdUpdate.Parameters.Add(pUBASIC)

        Dim pUDeptno As New SqlParameter
        pUDeptno.ParameterName = "@Deptno"
        pUDeptno.SourceColumn = "Deptno"
        pUDeptno.SourceVersion = DataRowVersion.Current
        pUDeptno.SqlDbType = SqlDbType.Int
        cmdUpdate.Parameters.Add(pUDeptno)

        Dim pUEmpno As New SqlParameter
        pUEmpno.ParameterName = "@Empno"
        pUEmpno.SourceColumn = "Empno"
        pUEmpno.SourceVersion = DataRowVersion.Original-----------as i am not allowing user to update the Emp no.
        pUEmpno.SqlDbType = SqlDbType.Int
        cmdUpdate.Parameters.Add(pUEmpno)
        Dim da As New SqlDataAdapter

        da.UpdateCommand = cmdUpdate


        'Insert Command

        Dim cmdInsert As New SqlCommand
        cmdInsert.Connection = cn
        cmdInsert.CommandText = "Insert Command like the update command"
        da.InsertCommand = cmdInsert

        'delete Command

        Dim cmddelete As New SqlCommand
        cmddelete.Connection = cn
        cmddelete.CommandText = "delete Command like the update command"

        da.DeleteCommand = cmddelete
        da.Update(ds, "Employees")
---------------------------------------------------------------------------------------------
0
 
SandeepRRCommented:
just forget to tell that in my previous sol. i am populating the datagrid with table having 4 columns
-Name
-Basic
-Deptno
-Empno


Regards,
SandeepRR
0
 
mackie1908Author Commented:
Hi SandeepRR

thanks for your reply. i have already tried something like this but all i get is "object reference not set to an instance of an object." below is my source code and a copy of my stored proc can you see what i have done wrong it is quite a big table to update so there are a lot of parameters being set. the place where it is giving me the above error is "da.UpdateCommand = cmdUpdate". please any advice as to what i am doing wrong would be much appreciated.

here is the code and below the Stored proc.

 Private Sub SaveBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveBtn.Click
        Dim cn As New SqlConnection

        Dim Param As New System.Data.SqlClient.SqlParameter
        Dim trans As SqlTransaction

        MakeConnection(cn)
        trans = cn.BeginTransaction

        Dim cmdUpdate As New SqlCommand
        cmdUpdate = cn.CreateCommand
        cmdUpdate.CommandText = CommandType.StoredProcedure
        cmdUpdate.CommandText = "UpdateLocations"

        Param = cmdUpdate.Parameters.Add("@loc", SqlDbType.VarChar, 20)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "Location"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@wz", SqlDbType.VarChar, 10)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "workzone"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@pz", SqlDbType.VarChar, 10)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "putawayzone"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@trkLic", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "tracklicence"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@pickpath", SqlDbType.Int)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "pickpath"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@putpath", SqlDbType.Int)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "putawaypath"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@actfit1", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "actualfit1"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@actfit2", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "actualfit2"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@current1", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "current1"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@current2", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "current2"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@currentuom", SqlDbType.Char, 10)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "currentuom"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@capfit1", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "capacityfit1"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@capfit2", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "capacityfit2"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@capuom", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "capacityuom"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@actuom", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "actualuom"
        Param.SourceVersion = DataRowVersion.Current


        Param = cmdUpdate.Parameters.Add("@avail1", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "avail1"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@avail2", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "avail2"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@avail3", SqlDbType.BigInt)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "avail3"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@caprules", SqlDbType.VarChar, 50)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "caprules"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@conttofit", SqlDbType.VarChar, 10)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "containertofit"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@locstat", SqlDbType.Char, 20)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "locstatus"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@wh", SqlDbType.VarChar, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "InWh"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@trackattr", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "trackattr"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@fixpick", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "fixedpick"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@storpick", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "storagepick"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@aux", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "aux"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@compcap", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "computecap"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@multilic", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "multilic"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@mixsku", SqlDbType.Char, 5)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "mixedsku"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@locnotes", SqlDbType.Char, 300)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "locnotes"
        Param.SourceVersion = DataRowVersion.Current

        Param = cmdUpdate.Parameters.Add("@key", SqlDbType.Int)
        Param.Direction = ParameterDirection.Input
        Param.SourceColumn = "Lockey"
        Param.SourceVersion = DataRowVersion.Original

        da.UpdateCommand = cmdUpdate

        da.UpdateCommand.Transaction = trans

        Try
            Dim rowsUpdated As Int16 = da.Update(ds, "LocDetails")
            trans.Commit()
            MessageBox.Show(rowsUpdated.ToString() + " Rows Updated.")

        Catch ex As Exception
            MessageBox.Show("Unable to Update DataBase!" + ex.Message)
            trans.Rollback()
        End Try

        LocationList.DataSource = ds.Tables("LocDetails")

        cn.Close()
        cn = Nothing


    End Sub

and here is my stored proc

CREATE PROCEDURE UpdateLocations
@loc varchar(20),
@wz varchar(10),
@pz varchar(10),
@trkLic char(5),
@pickpath int,
@putpath int,
@actfit1 bigint,
@actfit2 bigint,
@current1 bigint,
@current2 bigint,
@currentuom char(10),
@capfit1 bigint,
@capfit2 bigint,
@capuom char(5),
@actuom char(5),
@avail1 bigint,
@avail2 bigint,
@avail3 bigint,
@caprules varchar(50),
@conttofit varchar(10),
@locstat char(20),
@wh varchar (5),
@trackattr char(5),
@fixpick char(5),
@storpick char(5),
@aux char(5),
@compcap char(5),
@multilic char(5),
@mixsku char(5),
@locnotes char(300),
@key int
as
Update Locations
SET      location = @loc,
      workzone = @wz,
      putawayzone = @pz,
      tracklicence = @trkLic,
      pickpath = @pickpath,
      putawaypath = @putpath,
      actualfit1 = @actfit1,
      actualfit2 = @actfit2,
      current1 = @current1,
      current2 = @current2,
      currentuom = @currentuom,
      capacityfit1 = @capfit1,
      capacityfit2 = @capfit2,
      capacityuom = @capuom,
      actualuom = @actuom,
      avail1 = @avail1,
      avail2 = @avail2,
      avail3 = @avail3,
      caprules = @caprules,
      containertofit = @conttofit,
      locstatus = @locstat,
      InWh = @wh,
      trackattr = @trackattr,
      fixedpick = @fixpick,
      storagepick = @storpick,
      aux = @aux,
      computecap = @compcap,
      multilic = @multilic,
      mixedsku = @mixsku,
      locnotes = @locnotes,
      lastupdate = (getdate())

WHERE  Lockey = @key
GO
0
 
cubixSoftwareCommented:
Hi

You have not defined the data adapter (da) and that is why you get the error...

Dim da as new SQLDataAdapter

HTH :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now