Datalist editing not working

cjcd · April 1st, 2004, 09:18 AM

I'm trying to use the edit function of the datalist, everything works fine but when I go to update it it gives me UPDATE SYNTAX ERROR and I've looked at it so much I don't see it.

<code>
Imports System
Imports System.Data
Imports System.Data.Oledb
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Configuration

Public Class Approve : Inherits Page

  Private strConn As String = ConfigurationSettings.AppSettings("myDSN")
  Public dtlComments As DataList

  Public Sub Page_Load(sender as Object, e as EventArgs)

    If Not IsPostBack Then
      BindTheData()
    End If

  End Sub

  Private Sub BindTheData()

    Dim objConn as new OledbConnection(strConn)
    Dim strSQL as String
    strSQL = "SELECT * FROM Comments WHERE Approved='0'"
    Dim sda as new OledbDataAdapter(strSQL, objConn)
    Dim ds as new DataSet()
    sda.Fill(ds,"Comments")
    dtlComments.DataSource = ds.Tables("Comments").DefaultView
    dtlComments.DataBind()

  End Sub

  Public Sub dtlComments_Edit(sender as Object, e as DataListCommandEventArgs)

    dtlComments.EditItemIndex = e.Item.ItemIndex
    BindTheData()

  End Sub

  Public Sub dtlComments_Cancel(sender as Object, e as DataListCommandEventArgs)

    dtlComments.EditItemIndex = -1
    BindTheData()

  End Sub

  Public Sub dtlComments_Update(sender As Object, e As DataListCommandEventArgs)

    Dim strName, strApprove, strDate, strCity As String
    Dim strCountry, strEmail, strURL, strComment, strCommentID As String

    strName = CType(e.Item.FindControl("txtName"), TextBox).Text
    strApprove = CType(e.Item.FindControl("txtApprove"), TextBox).Text
    strDate = CType(e.Item.FindControl("txtdate"), TextBox).Text
    strCity = CType(e.Item.FindControl("txtcity"), TextBox).Text
    strCountry = CType(e.Item.FindControl("txtcountry"),TextBox).Te xt
    strEmail = CType(e.Item.FindControl("txtemail"), TextBox).Text
    strURL = CType(e.Item.FindControl("txturl"),TextBox).Text
    strComment = CType(e.Item.FindControl("txtcomment"),TextBox).Te xt
    strCommentID = CType(e.Item.FindControl("lblCommentID"), Label).Text

    Dim strSQL As String
    strSQL = "Update Comments " & _
    "Set [Name] = @Name," & _
    "[eMail] = @Email," & _
    "[URL] = @URL, " & _
    "[City] = @City, " & _
    "[Country] = @Country, " & _
    "[Date] = @Date, " & _
    "[Comment] = @Comment, " & _
    "[Approved] = @Approved, " & _
    "WHERE CommentID = @CommentID"

    Dim objConn As New OledbConnection(strConn)
    Dim Cmd As New OledbCommand(strSQL, objConn)

    With Cmd.Parameters
        .Add(New OleDbParameter("@Name", strName))
        .Add(New OleDbParameter("@Email", strEmail))
        .Add(New OleDbParameter("@Url", strURL))
        .Add(New OleDbParameter("@City", strCity))
        .Add(New OleDbParameter("@Country", strCountry))
        .Add(New OleDbParameter("@Date", strDate))
        .Add(New OleDbParameter("@Comment", strComment))
        .Add(New OleDbParameter("@Approved", strApprove))
        .Add(New OledbParameter("@CommentID", strCommentID))
    End With

    objConn.Open()
    cmd.ExecuteNonQuery()
    objConn.Close()

    dtlComments.EditItemIndex = -1
    BindTheData()

  End Sub

  End Class
</code>

Thank you

stu9820 · April 1st, 2004, 09:55 AM

Is commentID supposed to be a string or a number?

cjcd · April 1st, 2004, 10:08 AM

CommentID is the key, so it's autonumber in Access.

cjcd · April 1st, 2004, 10:39 AM

should I convert the string to a number, and if so, how?

melvik · April 3rd, 2004, 07:28 AM

I hope this sample will help u to get ur answer.

Code:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">
  void Page_Load( Object s, EventArgs e )
  {
    if ( !IsPostBack )
      BindData();
  }

  void BindData()
  {
    SqlConnection myConnection;
    SqlCommand myCommand;
    myConnection = new SqlConnection( "Server=Localhost;uid=sa;Database=Pubs" );
    myCommand = new SqlCommand( "Select au_id, au_lname, au_fname, phone from Authors order by au_lname", myConnection );
    myConnection.Open();
    myDataList.DataSource = myCommand.ExecuteReader();
    myDataList.DataBind();
    myConnection.Close();
  }

  void editAuthor( Object s, DataListCommandEventArgs e )
  {
    myDataList.EditItemIndex = e.Item.ItemIndex;
    BindData();
  }

  void cancelEdit( Object s, DataListCommandEventArgs e )
  {
    myDataList.EditItemIndex = -1;
    BindData();
  }

  void deleteAuthor( Object s, DataListCommandEventArgs e )
  {
    SqlConnection myConnection;
    SqlCommand myCommand;
    String sqlString;
    myConnection = new SqlConnection( "Server=Localhost;uid=sa;Database=Pubs" );
    sqlString = "Delete Authors Where au_id=@authorID";
    myCommand = new SqlCommand( sqlString, myConnection );
    myCommand.Parameters.Add( new SqlParameter( "@authorID", SqlDbType.VarChar, 11 ) );
    myCommand.Parameters[ "@authorID" ].Value = myDataList.DataKeys[ e.Item.ItemIndex ];
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myDataList.DataBind();
    myConnection.Close();
    myDataList.EditItemIndex = -1;
    BindData();
  }

  void updateAuthor( Object s, DataListCommandEventArgs e )
  {
    SqlConnection myConnection;
    SqlCommand myCommand;
    String sqlString;
    myConnection = new SqlConnection( "Server=Localhost;uid=sa;Database=Pubs" );
    sqlString = "Update Authors Set au_lname=@lastname, au_fname=@firstname, phone=@phone Where au_id=@authorID";
    myCommand = new SqlCommand( sqlString, myConnection );
    myCommand.Parameters.Add( new SqlParameter( "@lastname", SqlDbType.VarChar, 40 ) );
    myCommand.Parameters[ "@lastname" ].Value = ( (TextBox) e.Item.FindControl( "lastname" ) ).Text;
    myCommand.Parameters.Add( new SqlParameter( "@firstname", SqlDbType.VarChar, 20 ) );
    myCommand.Parameters[ "@firstname" ].Value = ( (TextBox) e.Item.FindControl( "firstname" ) ).Text;
    myCommand.Parameters.Add( new SqlParameter( "@phone", SqlDbType.Char, 12 ) );
    myCommand.Parameters[ "@phone" ].Value = ( (TextBox) e.Item.FindControl( "phone" ) ).Text;
    myCommand.Parameters.Add( new SqlParameter( "@authorID", SqlDbType.VarChar, 11 ) );
    myCommand.Parameters[ "@authorID" ].Value = myDataList.DataKeys[ e.Item.ItemIndex ];
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myDataList.DataBind();
    myConnection.Close();
    myDataList.EditItemIndex = -1;
    BindData();
  }
</Script>

<html>
<head><title>Edit Authors</title></head>
<body>

<form Runat="Server">

<asp:DataList id="myDataList" cellpadding=10 cellspacing=0 gridlines="both"
  RepeatColumns="3" RepeatDirection="Horizontal" DataKeyField="au_id"
  OnEditCommand="editAuthor" OnDeleteCommand="deleteAuthor"
  OnUpdateCommand="updateAuthor" OnCancelCommand="cancelEdit" Runat="Server">

  <ItemTemplate>
    <asp:LinkButton Text="Edit" CommandName="edit" Runat="Server"/>
    <%# DataBinder.Eval( Container.DataItem, "au_lname" )%>
  </ItemTemplate>

  <EditItemTemplate>
     <b>Last Name:</b>
     <br><asp:TextBox id="lastname"
       text='<%# DataBinder.Eval( Container.DataItem, "au_lname" ) %>'
       Runat="Server"/>
     <p>
     <b>First Name:</b>
     <br><asp:TextBox id="firstname"
       text='<%# DataBinder.Eval( Container.DataItem, "au_fname" ) %>'
       Runat="Server"/>
     <p>
     <b>Phone:</b>
     <br><asp:TextBox id="phone"
       text='<%# DataBinder.Eval( Container.DataItem, "phone" ) %>'
       Runat="Server"/>
     <p>
     <asp:Button Text="Update" CommandName="update" Runat="Server"/>
     <asp:Button Text="Delete" CommandName="delete" Runat="Server"/>
     <asp:Button Text="Cancel" CommandName="cancel" Runat="Server"/>
  </EditItemTemplate>
</asp:DataList>
</form>
</body>
</html>

Always:),
Hovik Melkomian.