Solved

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

Posted on 2006-07-03
3
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

632 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