Datagrid, not updating DB

[Compkitty]

Hi, I have a valid update Stored procedure that works, however, I have built a datagrid w/ 7 out of the 8 columns one of which is unique... which I add in but is not visible. I run the OnUpdatecommand, and it doesn't work... Below is my code... for the Stored Procedure and Vb.net

THANKS

Stored Procedure

CREATE PROCEDURE Sp_UpdPTO

(
@FirstName varchar (50),
@Lastname varchar(50),
@DateL datetime,
@DateR datetime,
@TimeIn datetime,
@TimeOut datetime, 
@Memo varchar (80),
@LoginId varchar(50)
)


 AS

UPDATE tblDTPTO 
SET DateLeaving = @DateL , DateReturning=@DateR, TimeIn = @TimeIn, TimeOut = @TimeOut, Memo=@Memo, DTFname =@FirstName, DTLName = @LastName
WHERE DTLoginID = @LoginID
GO

VB.net code

 Public Sub Dg_Update(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
        Dim LoginId As String = e.Item.Cells(1).Text
        Dim First As String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
        Dim Last As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
        Dim Dleave As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
        Dim DReturn As String = CType(e.Item.Cells(5).Controls(0), TextBox).Text
        Dim TimeIn As String = CType(e.Item.Cells(6).Controls(0), TextBox).Text
        Dim TimeOut As String = CType(e.Item.Cells(7).Controls(0), TextBox).Text
        Dim Memo As String = CType(e.Item.Cells(8).Controls(0), TextBox).Text

        Dim UpdCmd As SqlCommand = New SqlCommand("Sp_UpdPTO", SqlConn)
        UpdCmd.CommandType = CommandType.StoredProcedure

        Dim FName As SqlParameter = New SqlParameter("@FirstName", SqlDbType.VarChar, 50)
        FName.Direction = ParameterDirection.Input
        UpdCmd.Parameters("@FirstName").Value = First
        UpdCmd.Parameters.Add(FName)

        Dim LName As SqlParameter = New SqlParameter("@LastName", SqlDbType.VarChar, 50)
        LName.Direction = ParameterDirection.Input
        UpdCmd.Parameters("@LastName").Value = Last
        UpdCmd.Parameters.Add(LName)

        Dim DLParam As SqlParameter = New SqlParameter("@DateL", SqlDbType.DateTime)
        DLParam.Direction = ParameterDirection.Input
        UpdCmd.Parameters("@DateL").Value = Dleave
        UpdCmd.Parameters.Add(DLParam)

        Dim DRParam As SqlParameter = New SqlParameter("@DateR", SqlDbType.DateTime)
        DRParam.Direction = ParameterDirection.Input
        UpdCmd.Parameters("@DateR").Value = DReturn
        UpdCmd.Parameters.Add(DRParam)

        Dim TIParam As SqlParameter = New SqlParameter("@TimeIn", SqlDbType.DateTime)
        TIParam.Direction = ParameterDirection.Input
        UpdCmd.Parameters("@TimeIn").Value = TimeIn
        UpdCmd.Parameters.Add(TIParam)

        Dim TOParam As SqlParameter = New SqlParameter("@TimeOut", SqlDbType.DateTime)
        TOParam.Direction = ParameterDirection.Input
        UpdCmd.Parameters("@TimeOut").Value = TimeOut
        UpdCmd.Parameters.Add(TOParam)

        Dim MemParam As SqlParameter = New SqlParameter("@Memo", SqlDbType.VarChar, 80)
        MemParam.Direction = ParameterDirection.Input
        UpdCmd.Parameters("@Memo").Value = Memo
        UpdCmd.Parameters.Add("MemParam")

        Dim LoginParam As SqlParameter = New SqlParameter("@LoginId", SqlDbType.VarChar, 50)
        LoginParam.Direction = ParameterDirection.Input
        UpdCmd.Parameters("@LoginId").Value = LoginId
        UpdCmd.Parameters.Add(LoginParam)

        SqlConn.Open()
        UpdCmd.ExecuteNonQuery()
        SqlConn.Close()

        DG.EditItemIndex = -1
        bindData()

    End Sub

Any help is appreciated

 

[huel]

When you execute the stored procedure, are any error messages returned; or is it the case that when you execute the stored procedure, absolutely nothing happens?

I can make one quick guess by looking at the posted code:

Dim MemParam As SqlParameter = New SqlParameter("@Memo", SqlDbType.VarChar, 80)
MemParam.Direction = ParameterDirection.Input
UpdCmd.Parameters("@Memo").Value = Memo
UpdCmd.Parameters.Add("MemParam")

All you may have to do is remove the parentheses around MemParam at the line

UpdCmd.Parameters.Add("MemParam")

It's probably viewing "MemParam" as a value rather than an SqlParameter variable.

Jay

 

[Compkitty]

Sorry, actually I got that... the Stored procedure works. what I am looking to do now is create the Datagrid w/ button controls (not part of the Datagrid) but I am having issues seting it up.... w/ the stored procedure I have a index number which is the PK, welll as long as I input that it updates.. so I've decided to do insert/update/delete... can you guide me on creating the button click events??? right now I have the first column as a select command column but not sure how to pass the selecteditem.. or setup the edit...

THANKS

 

[huel]

To grab the selected item out of a datagrid you could use

dim item as Object = Dg.Item(Dg.CurrentCell)

When you say that you want to set up an edit, does that mean you want the user to be able to edit data directly in the datagrid?

Are you going to have separate button click events for your insert, update, and delete operations? Do you also have stored procedures to perform your insert and delete operations?

Jay

 

[jshurst]

Are you sure that you are that you are using the right cell numbers. I am not sure because I can't see you grid but remember that they have a base 0 starting point. Is your invisible identifier really at the .cells(1) place. If not then try

Dim LoginId As String = e.Item.Cells(0).Text

You will need to do this for the rest as well.

 

[Compkitty]

I would like to have separate buttons outside of Datagrid. I do also use separate Stored Procedures for the insert/delete/update... I am pretty sure that I am using the right cell numbers... I start w/ 1 as the select column, then the rest are for the update.. the 0 is my hidden PK field... could this be the issue? I have my PK field as not visible, but still updating to the DB...

 

[Compkitty]

I also have another question: when I do the ctype(e.items.cells(?).control(0), textbox).text.... if I don't have the columns setup in the grid as textboxes is this the issue... when I hit delete the fields turn into textboxes....

Thanks for all ur help

 

[Compkitty]

These are the ones that I am having issues w/ now..

    Dim Dleave As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
        Dim DReturn As String = CType(e.Item.Cells(5).Controls(0), TextBox).Text
        Dim TimeIn As String = CType(e.Item.Cells(6).Controls(0), TextBox).Text
        Dim TimeOut As String = CType(e.Item.Cells(7).Controls(0), TextBox).Text
        Dim Memo As String = CType(e.Item.Cells(8).Controls(0), TextBox).Text

I think the issue is w/ the datatypes... they are datetime in the sqltable, and in the datagrid I format them to be shorter... when I run the stored procedure thur server explorer, I don't have an issue though.

THANKS again