Solved

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

Posted on 2006-07-03
3
192 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
ID: 17031138
Dim da As SqlClient.SqlDataAdapter
            da.UpdateCommand.CommandType = CommandType.StoredProcedure
            da.UpdateCommand.CommandText = "stored proc name here"
0
 
LVL 1

Author Comment

by:franklinan
ID: 17031778
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
ID: 17032054
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
fso.FolderExists("\\server\HiddenFolder$") 4 68
send messages to whatsapp programatically 2 49
Gridview selected row 9 47
Help with error when uploading excel file 3 29
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

803 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