Solved

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

Posted on 2006-07-03
3
195 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 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