Solved

Stored Procedure and SQL DataAdapter.... PLEASE ! !! !   VERY URGENT  ! ! ! ! ! ! ! !!! !

Posted on 2006-07-03
3
191 Views
Last Modified: 2010-04-23
I 've this :

Da - SQLDATAADAPTER
Ds - DATASET

When _I Open the Connection and I do the select command, no problem doing this  :
     
            Da.Fill(Ds, "Autors")

       
What I need now is to "bind " a SQL Stored Procedure  ti the UpdateCommand of the DataAdapter ... HOW TO DO THIS ? ?


The Stored Procedure :

ALTER PROCEDURE AuthorUpdate
      (@au_id id,
      @au_lname varchar(40),
      @au_fname varchar(20),
      @phone char(12),
      @address varchar(40),
      @city varchar(20),
      @state char(2),
      @zip char(5),
      @contract bit)
AS

UPDATE authors
SET       au_lname = @au_lname,
      au_fname = @au_fname,
      phone       = @phone,
      address = @address,
      city       = @city,
      state       = @state,
      zip       = @zip,
      contract = @contract
WHERE au_id = @au_id



Tha Ds (Dataset) is bind to DataGrid and I've got this code on the form  (when a user ends edit on a cell on the grid, if there are changes I want to update the DATABASE) :

Private Sub DG1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG1.CellEndEdit
        Dim cm1 As New SqlCommand
        cm1.CommandType = CommandType.StoredProcedure
        cm1.CommandText = "AuthorUpdate"
        cm1.Connection = Cn


        cm1.Parameters.Add(New SqlParameter("@au_id", SqlDbType.VarChar, 11)).Value _
     = DG1.CurrentRow.Cells(0).Value.ToString
        cm1.Parameters("@au_id").Direction = ParameterDirection.Input

        cm1.Parameters.Add(New SqlParameter("@au_lname", SqlDbType.VarChar, 40))
        cm1.Parameters.Add(New SqlParameter("@au_fname", SqlDbType.VarChar, 20))
        cm1.Parameters.Add(New SqlParameter("@phone", SqlDbType.Char, 12))
        cm1.Parameters.Add(New SqlParameter("@address", SqlDbType.VarChar, 40))
        cm1.Parameters.Add(New SqlParameter("@city", SqlDbType.VarChar, 20))
        cm1.Parameters.Add(New SqlParameter("@state", SqlDbType.Char, 2))
        cm1.Parameters.Add(New SqlParameter("@zip", SqlDbType.Char, 5))
        cm1.Parameters.Add(New SqlParameter("@contract", SqlDbType.Bit))
        ' cm1.Parameters("@au_id").SourceVersion = DataRowVersion.Original   '  WHAT DOES IT MEAN THIS ?  ? ?

                                           Da.UpdateCommand = cm1

        If Ds.HasChanges Then
            Da.Update(Ds.Tables(0))
            Ds.Clear()
            Da.Fill(Ds)
        End If
    End Sub

 
0
Comment
Question by:franklinan
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
Comment Utility
Dim da As SqlClient.SqlDataAdapter
            da.UpdateCommand.CommandType = CommandType.StoredProcedure
            da.UpdateCommand.CommandText = "stored proc name here"
0
 
LVL 1

Author Comment

by:franklinan
Comment Utility
Sorry...still not works :


The error says : The procedure  'AuthorUpdate' waits a parameter :   'au_lname'

If I change this :

cm1.Parameters.Add(New SqlParameter("@au_lname", SqlDbType.VarChar, 40))

to this :
cm1.Parameters.Add(New SqlParameter("@au_lname", SqlDbType.VarChar, 40)).value = DG1.CurrentRow.Cells(1).Value.ToString


Then the error message tolds me that 'au_Fname' left....




What is wrong ?

I don't understand the sense of bind to DataAdapter if I have to put all the parameters = Textboxes / Gridcells.. ....

I think that if the bind would be right, I would just have to write : da.update(Ds.tables(0))


0
 
LVL 1

Author Comment

by:franklinan
Comment Utility
Finally I've done this, but the CellEndEdit does not works fine !  why  ??  ? ? At this line of code :


if Ds.HasChanges then


end if

The Dataset (Ds) not always is seeing the changes....Why ? ? ?  ?







Private Sub DG1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG1.CellEndEdit
        Dim cm1 As New SqlCommand
        cm1.CommandType = CommandType.StoredProcedure
        cm1.CommandText = "AuthorUpdate"
        cm1.Connection = Cn


        cm1.Parameters.Add(New SqlParameter("@au_id", SqlDbType.VarChar, 11)).Value = _
        DG1.CurrentRow.Cells(0).Value.ToString
        'cm1.Parameters("@au_id").SourceVersion = DataRowVersion.Original

        cm1.Parameters.Add(New SqlParameter("@au_lname", SqlDbType.VarChar, 40)).Value = _
        DG1.CurrentRow.Cells(1).Value.ToString
        cm1.Parameters.Add(New SqlParameter("@au_fname", SqlDbType.VarChar, 20)).Value = _
        DG1.CurrentRow.Cells(2).Value.ToString
        cm1.Parameters.Add(New SqlParameter("@phone", SqlDbType.Char, 12)).Value = _
        DG1.CurrentRow.Cells(3).Value.ToString
        cm1.Parameters.Add(New SqlParameter("@address", SqlDbType.VarChar, 40)).Value = _
        DG1.CurrentRow.Cells(4).Value.ToString
        cm1.Parameters.Add(New SqlParameter("@city", SqlDbType.VarChar, 20)).Value = _
        DG1.CurrentRow.Cells(5).Value.ToString
        cm1.Parameters.Add(New SqlParameter("@state", SqlDbType.Char, 2)).Value = _
        DG1.CurrentRow.Cells(6).Value.ToString
        cm1.Parameters.Add(New SqlParameter("@zip", SqlDbType.Char, 5)).Value = _
        DG1.CurrentRow.Cells(7).Value.ToString
        cm1.Parameters.Add(New SqlParameter("@contract", SqlDbType.Bit)).Value = _
        DG1.CurrentRow.Cells(8).Value


        Da.UpdateCommand = cm1
        Da.UpdateCommand.CommandType = CommandType.StoredProcedure
        Da.UpdateCommand.CommandText = "AuthorUpdate"


        If Ds.HasChanges Then
            'UpdatePub(Ds.GetChanges)
            Da.Update(Ds.Tables(0))
            Ds.Clear()
            Da.Fill(Ds, "Autors")
            DG1.DataSource = Ds.Tables(0)
        End If
    End Sub
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now