Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-03
3
Medium Priority
?
212 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 1500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

688 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