sqldatasourse and gridview

[johnny.g]

hii,,guys,,i m using asp.net2005 and sql server 2005...in my page i have used gridview and sql datasource,i m able to edit,update and delete the records,,the problem is tht for 1 column i would lik to use a dropdown list instead of textbox when i am in edit or update mode,,,heres is my code,,need help,,thnks in advance

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:sme_trackerConnectionString %>"
        SelectCommand="SELECT SME_Master.SME_Id, SME_Master.FirstName, SME_Master.LastName, SME_Master.Agency_id, SME_Master.Email, SME_Master.Address, SME_Master.Phone, SME_Master.Mobile, SME_Master.Fax, SME_Master.TimeZone_Id, SME_Master.Experience, SME_Master.City, SME_Master.State, SME_Master.Status, SME_Master.Level_Of_Exam, SME_Master.Other_Comments, SME_Master.Certificate, SME_Master.Expertise, Agency_Master.Agency_Name, SME_Master.Type_of_SME FROM SME_Master LEFT OUTER JOIN Agency_Master ON SME_Master.Agency_id = Agency_Master.Agency_Id"         
        OnSelected="SqlDataSource1_Selected" InsertCommand="INSERT INTO [SME_Master] ([SME_Id], [FirstName], [LastName], [Type_of_SME], [Agency_id], [Email], [Address], [Phone], [Mobile], [Fax], [TimeZone_Id], [Experience], [City], [State], [Status], [Level_Of_Exam], [Other_Comments], [Certificate], [Expertise]) VALUES (@SME_Id, @FirstName, @LastName, @Type_of_SME, @Agency_id, @Email, @Address, @Phone, @Mobile, @Fax, @TimeZone_Id, @Experience, @City, @State, @Status, @Level_Of_Exam, @Other_Comments, @Certificate, @Expertise)" OldValuesParameterFormatString="original_{0}" 
        UpdateCommand="UPDATE SME_Master SET FirstName = @FirstName, LastName = @LastName, Type_of_SME = @Type_of_SME, Email = @Email, Address = @Address, Phone = @Phone, Mobile = @Mobile, Fax = @Fax, Experience = @Experience, City = @City, State = @State, Agency_Master.Agency_Name = @Agency_Name FROM SME_Master INNER JOIN Agency_Master ON SME_Master.Agency_id = Agency_Master.Agency_Id WHERE (SME_Master.SME_Id = @original_SME_Id)" 
        DeleteCommand="DELETE FROM SME_Master where SME_Id=@original_SME_Id" 
        >

[serkan sendur]

If i were you, i would use the grid view for selecting and use formview for updating. Formview is more flexible. You can insert dropdownlist to its edititemtemplate, read the entry in my blog http://www.daniweb.com/blogs/entry2043.html

[johnny.g]

If i were you, i would use the grid view for selecting and use formview for updating. Formview is more flexible. You can insert dropdownlist to its edititemtemplate, read the entry in my blog http://www.daniweb.com/blogs/entry2043.html

hi,,,hey ur blog is really really nice,,,but now it wil b difficult for me to change all,,well i have managed to get the dropdown list instead of the textfield in edit mode,,,can u tell me how to update two table at the same time,coz the dropdown list column is frm another table and the other columns are from another table,,,,also how to make dropdown list to show the default value in edit mode,,,it was thr in ur blog bt i was not able to make it out clearly,,,heres is my code for the update command,,,,here agency_name is from another table and the rest of the columns are from diff table:::::here i m not able to update agency_name,,i hav done ths thr sqldatasource

UPDATE SME_Master SET FirstName = @FirstName, LastName = @LastName, Type_of_SME = @Type_of_SME, Email = @Email, Address = @Address, Phone = @Phone, Mobile = @Mobile, Fax = @Fax, Experience = @Experience, City = @City, State = @State FROM SME_Master INNER JOIN Agency_Master ON SME_Master.Agency_id = Agency_Master.Agency_Id WHERE (SME_Master.SME_Id = @original_SME_Id)

thnks in advance

[johnny.g]

welll now thrs a small change,,,,forget updating two tables,,,,sorry for tht,,what i m thinkin of is tht on updating the agency name(which is in the Agency_Master table),,,the particular agency_id (which is in SME_Master table)should change,,,in short i am updating the Agency_id,,,,,,,coz it is far more sensible to change the id on selecting the agency name,,,but the other prob still remains the same,,in edit mode the dropdown list should display the default value which is different for diff rows...how can i do tht..pls reply asap..thnks in advance

[serkan sendur]

First you need to set the gridview's autogeneratecolumns property to false then create template fileds under the columns section of the gridview :
<Columns>
<asp:TemplateField>
<ItemTemplate>
your normal controls go here
</ItemTemplate>
<EditItemTemplate>
your dropdown goes here
</EditItemTemplate>
</asp:TemplateField>

[johnny.g]

First you need to set the gridview's autogeneratecolumns property to false then create template fileds under the columns section of the gridview :
<Columns>
<asp:TemplateField>
<ItemTemplate>
your normal controls go here
</ItemTemplate>
<EditItemTemplate>
your dropdown goes here
</EditItemTemplate>
</asp:TemplateField>

whts next,,,,,boss can u giv ne idea how to display a confirmation box for delete,,coz now if i press delete frm gridview it deletes the record directly,,i just want to confirm the user whether he wants to delete or not......

[ravichandra]

Try this:

 protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Page.RegisterClientScriptBlock("KEY", "<script language='javascript'>" + "confirm('Do you want to delete?')" + "</script>");
    }

[johnny.g]

Try this:

 protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Page.RegisterClientScriptBlock("KEY", "<script language='javascript'>" + "confirm('Do you want to delete?')" + "</script>");
    } 

end quote.

thnks ravi,,its done,,thnks

[johnny.g]

First you need to set the gridview's autogeneratecolumns property to false then create template fileds under the columns section of the gridview :
<Columns>
<asp:TemplateField>
<ItemTemplate>
your normal controls go here
</ItemTemplate>
<EditItemTemplate>
your dropdown goes here
</EditItemTemplate>
</asp:TemplateField>

boss,,,u have helped me a lot,,coz of u and also othr ppl i am very near to complete my project,,neways 1 mor doubt....i have a gridview aand sql datasource,,,in edit mode i have put a dropdown list for agency_name column,,,wht i want is whn in edit mode,,the dropdown list should display its old value for that particular row,,now wht is happnin is tht if i click on edit of any row,the dropdown list shows 1st value,,hopw u got wht i m tryin 2 say,,
need ur help,,thnks in advance

[serkan sendur]

ok jonny i will help you but i am at office now, i must get home to answer your question. To provide you a step by step process i need to create a project for testing, i can not do that at the moment. Wait until i get home.

[johnny.g]

ok jonny i will help you but i am at office now, i must get home to answer your question. To provide you a step by step process i need to create a project for testing, i can not do that at the moment. Wait until i get home.

thnks buddy,,tak ur tim,,,,i wil b waiting for ur answer,,,thnks

[johnny.g]

ok jonny i will help you but i am at office now, i must get home to answer your question. To provide you a step by step process i need to create a project for testing, i can not do that at the moment. Wait until i get home.

well boss,,i got an answer for tht,,this code is workin fine,,here it is..but still i m waitin for ur answer

<asp:TemplateField HeaderText="Agency Name">

<ItemTemplate>

<%#Eval("agency_name")%> 
 

</ItemTemplate>

<EditItemTemplate>

<asp:DropDownList ID="agency_nameList" selectedvalue='<%#Eval("agency_name")%>' DataValueField="agency_name" DataSourceID="SqlDataSource1" runat="server" >

 

</asp:DropDownList>

</EditItemTemplate>

</asp:TemplateField>

thnks

[serkan sendur]

ok jonny you seem to find the solution, i would do probably the same.

[johnny.g]

ok jonny you seem to find the solution, i would do probably the same.

k no worries,,thnks buddy,,,well u remember i needed a confirmation box for deleting rows thr my gridview,,well i am using the code given below....the prob is even if i choose to cancel then also the record gets deleted,,,,ne idea how to get it rite,,,

protected void GridViewtab_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Page.RegisterClientScriptBlock("KEY", "<script language='javascript'>" + "confirm('Do you want to delete this record?')" + "</script>");

    }

[serkan sendur]

protected void gridview1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if(e.Row.RowType == DataControlRowType.DataRow)
{
LinkButton db = (LinkButton)e.Row.Cells[0].Controls[0];
db.OnClientClick = "return confirm('Are you certain you want to delete the item?');"
}
}

[johnny.g]

protected void gridview1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if(e.Row.RowType == DataControlRowType.DataRow)
{
LinkButton db = (LinkButton)e.Row.Cells[0].Controls[0];
db.OnClientClick = "return confirm('Are you certain you want to delete the item?');"
}
}

sir,,ur code gives an error sayin tht::

'System.Web.UI.WebControls.GridViewDeleteEventArgs' does not contain a definition for 'Row'

[serkan sendur]

Make sure you use the correct event, if you use the RowDataBound event it doesnt generate GridViewDeleteEventArgs, read my code carefully, it doesnt mention that, it uses GridViewRowEventArgs

[johnny.g]

Make sure you use the correct event, if you use the RowDataBound event it doesnt generate GridViewDeleteEventArgs, read my code carefully, it doesnt mention that, it uses GridViewRowEventArgs

well sir now i am using the correct event,but now the records are gettin deleted without any confirmation box,,,i really don kno what is goin on here,,,reply

[serkan sendur]

I tried it, it is working. Your problem may be about the index of the control or cell in the gridview. Is your delete button in the first cell and is that the first control in that first cell? Do you use custom delete button instead of the commandbutton? These may be the problems

[johnny.g]

I tried it, it is working. Your problem may be about the index of the control or cell in the gridview. Is your delete button in the first cell and is that the first control in that first cell? Do you use custom delete button instead of the commandbutton? These may be the problems

yes sir,u wer rite,,the prob was of index of the control,,now its working fine,,thnks buddy

[serkan sendur]

Why dont you add to my reputation then :)

[johnny.g]

Why dont you add to my reputation then :)

sir u hav helped me so much,,,pls help me onhow to add to ur reputation..

[serkan sendur]

There is a link just after the title of the post saying Add to serkansendur's reputation, just click on it :)

[johnny.g]

There is a link just after the title of the post saying Add to serkansendur's reputation, just click on it :)

thnks bro,,i have marked it to ur reputation,,,,well now i strt with 1 mor prob>>here it goes
i hve an insert page in which i have used sqldatasource and formview,,thrs an dropdownlist for agency_id,,the prob is tht whn i select the value from dropdownlist and click on insert,,all gets inserted in the table except the agency_id,,and also the default value it shows is the first agency_id from the table,,so how can i solve ths prob....thnks in advance

[serkan sendur]

Can you extend the problem a little? i didnt understand.

[johnny.g]

Can you extend the problem a little? i didnt understand.

ok,,well i m using asp.net 2005 and sqlserver 2005,,i have a page called as sme_insert.aspx thr which user can enter new records for smeid,name,no agencyid ,,etc..
i have used sqldatasource and formview for tht,,,well for agency_id i have replaced the textfield with a dropdown list and it displays the agency_id values from the table sme_master..till now everything is working fine,,whn i go for inserting a new record and select a value from the dropdownlist,the value doesnot gets inserted in the tableso everything is working fine except the dropdownlist,,,another prob is tht the dropdown list always displays the first record of agency_id from the table,,instead of tht i would lik to display some other text such as SELECT........hope u got my prob

[johnny.g]

Can you extend the problem a little? i didnt understand.

hey bro,,in the edit link in gridview,,all fields gets changed to textfield so tht v can update in,,,welll thr r few columns whom i dont want to update it,,,any idea how to do tht,,,,