see i am using this mode of SQLDATASOURCE configuration
154

and now on the basis of this sql statement i want to generate UPDATE, INSERT, DELETE queries, especially UPDATE
and i tried my best to do it but can't.

this thing is quite possible in the mode given below in image
154

but the you can select firleds from one table only and i want to join two tables and generating UPDATE, insert queries on base of that

help please , i wasted alot of time on it,
here is my code : ASPX

<%@ Page Language="C#" MasterPageFile="~/MasterPageAdministration.master" AutoEventWireup="true" CodeFile="testing.aspx.cs" Inherits="testing" Title="Untitled Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <div class="superAdminContent">
     <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
         DataKeyNames="userid,pinfoid" DataSourceID="SqlDataSource1">
         <Columns>
             <asp:BoundField DataField="userid" HeaderText="userid" InsertVisible="False" 
                 ReadOnly="True" SortExpression="userid" />
             <asp:BoundField DataField="fullname" HeaderText="fullname" 
                 SortExpression="fullname" />
             <asp:BoundField DataField="email" HeaderText="email" SortExpression="email" />
             <asp:BoundField DataField="password" HeaderText="password" 
                 SortExpression="password" />
             <asp:BoundField DataField="question" HeaderText="question" 
                 SortExpression="question" />
             <asp:BoundField DataField="answers" HeaderText="answers" 
                 SortExpression="answers" />
             <asp:BoundField DataField="doc" HeaderText="doc" SortExpression="doc" />
             <asp:BoundField DataField="roleid" HeaderText="roleid" 
                 SortExpression="roleid" />
             <asp:BoundField DataField="pinfoid" HeaderText="pinfoid" InsertVisible="False" 
                 ReadOnly="True" SortExpression="pinfoid" />
             <asp:BoundField DataField="bankname" HeaderText="bankname" 
                 SortExpression="bankname" />
             <asp:BoundField DataField="bankdraftno" HeaderText="bankdraftno" 
                 SortExpression="bankdraftno" />
             <asp:BoundField DataField="amount" HeaderText="amount" 
                 SortExpression="amount" />
             <asp:BoundField DataField="dod" HeaderText="dod" SortExpression="dod" />
             <asp:BoundField DataField="doc1" HeaderText="doc1" SortExpression="doc1" />
             <asp:BoundField DataField="firstname" HeaderText="firstname" 
                 SortExpression="firstname" />
             <asp:BoundField DataField="lastname" HeaderText="lastname" 
                 SortExpression="lastname" />
             <asp:BoundField DataField="fathername" HeaderText="fathername" 
                 SortExpression="fathername" />
             <asp:BoundField DataField="photo" HeaderText="photo" SortExpression="photo" />
             <asp:BoundField DataField="country" HeaderText="country" 
                 SortExpression="country" />
             <asp:BoundField DataField="city" HeaderText="city" SortExpression="city" />
             <asp:BoundField DataField="phoneno" HeaderText="phoneno" 
                 SortExpression="phoneno" />
             <asp:BoundField DataField="mobno" HeaderText="mobno" SortExpression="mobno" />
             <asp:BoundField DataField="domicile" HeaderText="domicile" 
                 SortExpression="domicile" />
             <asp:BoundField DataField="sect" HeaderText="sect" SortExpression="sect" />
             <asp:BoundField DataField="cnicnum" HeaderText="cnicnum" 
                 SortExpression="cnicnum" />
             <asp:BoundField DataField="nationality" HeaderText="nationality" 
                 SortExpression="nationality" />
             <asp:BoundField DataField="mailingadd" HeaderText="mailingadd" 
                 SortExpression="mailingadd" />
             <asp:BoundField DataField="permanentadd" HeaderText="permanentadd" 
                 SortExpression="permanentadd" />
             <asp:BoundField DataField="companyname" HeaderText="companyname" 
                 SortExpression="companyname" />
             <asp:BoundField DataField="companyurl" HeaderText="companyurl" 
                 SortExpression="companyurl" />
             <asp:BoundField DataField="companyemailadd" HeaderText="companyemailadd" 
                 SortExpression="companyemailadd" />
             <asp:BoundField DataField="companyadd" HeaderText="companyadd" 
                 SortExpression="companyadd" />
             <asp:BoundField DataField="faxnum" HeaderText="faxnum" 
                 SortExpression="faxnum" />
             <asp:BoundField DataField="companydescription" HeaderText="companydescription" 
                 SortExpression="companydescription" />
             <asp:BoundField DataField="contactperson" HeaderText="contactperson" 
                 SortExpression="contactperson" />
             <asp:CheckBoxField DataField="agreedtodec" HeaderText="agreedtodec" 
                 SortExpression="agreedtodec" />
             <asp:BoundField DataField="userid1" HeaderText="userid1" 
                 SortExpression="userid1" />
         </Columns>
     </asp:GridView>
     <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
         ConnectionString="<%$ ConnectionStrings:Waleed_orsfinalConnectionString16 %>" 
         DeleteCommand="DELETE FROM [tblUser] WHERE [userid] = @userid" 
         InsertCommand="INSERT INTO [tblUser] ([fullname], [email], [password], [question], [answers], [doc], [roleid]) VALUES (@fullname, @email, @password, @question, @answers, @doc, @roleid)" 
         SelectCommand="select * from tblUser inner join tblPersonalInfo On tblUser.userid= tblpersonalInfo.userid AND roleid=2 "
         UpdateCommand="UPDATE [tblUser] SET [tblUser].[fullname] = @fullname, [tblUser].[email] = @email,[tblUser]. [password] = @password, [tblUser].[question] = @question, [tblUser].[answers] = @answers, [tblUser].[doc] = @doc, [tblUser].[roleid] = @roleid, [tblpersonalInfo].[bankname]=@bankname, [tblpersonalInfo].[bankdraftno]=@bankdraftno,
                        [tblpersonalInfo].[amount]=@amount,[tblpersonalInfo].[doc]=@doc1 ,[tblpersonalInfo].[firstname]=@firstname,
                        [tblpersonalInfo].[lastname]=@lastname,[tblpersonalInfo].[fathername]=@fathername, 
                        [tblpersonalInfo].[photo]=@photo,[tblpersonalInfo].[country]=@country, [tblpersonalInfo].[city]=@city,
                        [tblpersonalInfo].[phoneno]=@phoneno, [tblpersonalInfo].[mobileno]=@mobileno, [tblpersonalInfo].[domicile]=@domicile, [tblpersonalInfo].[sect]=@sect, [tblpersonalInfo].[cnicno]=@cnicno, 
                        [tblpersonalInfo].[nationality]=@nationality, [tblpersonalInfo].[mailingadd]=@mailingadd,
                        [tblpersonalInfo].[permanentadd]=@permanentadd, [tblpersonalInfo].[companyname]=@companyname,[tblpersonalInfo].[companyurl]=@companyurl, [tblpersonalInfo].[companyemailadd]=@companyemailadd, [tblpersonalInfo].[faxno]=@faxno, [tblpersonalInfo].[companydescription]=@companydescription, 
                        [tblpersonalInfo].[contactperson]=@contactperson,[tblpersonalInfo].[agreedtodec]=@agreetodecission 
                         inner join On [tblUser].[userid] = [tblpersonalInfo].[userid]
                         WHERE [tblUser].[userid] = @userid">

         <DeleteParameters>
             <asp:Parameter Name="userid" Type="Int32" />
         </DeleteParameters>
         <UpdateParameters>
             <asp:Parameter Name="fullname" Type="String" />
             <asp:Parameter Name="email" Type="String" />
             <asp:Parameter Name="password" Type="String" />
             <asp:Parameter Name="question" Type="String" />
             <asp:Parameter Name="answers" Type="String" />
             <asp:Parameter DbType="Date" Name="doc" />
             <asp:Parameter Name="roleid" Type="Int32" />
             <asp:Parameter Name="userid" Type="Int32" />
             <asp:Parameter Name="bankname" Type="String" />
             <asp:Parameter Name="bankdraftno" Type="String" />
             <asp:Parameter Name="amount" Type="Int32" />
             <asp:Parameter DbType="Date" Name="doc1" />
             <asp:Parameter Name="firstname" Type="String" />
             <asp:Parameter Name="lastname" Type="String" />
             <asp:Parameter Name="fathername" Type="String" />
             <asp:Parameter Name="photo" Type="String" />
             <asp:Parameter Name="country" Type="String" />
             <asp:Parameter Name="city" Type="String" />
             <asp:Parameter Name="phoneno" Type="String" />
             <asp:Parameter Name="mobileno" Type="String" />
             <asp:Parameter Name="domicile" Type="String" />
             <asp:Parameter Name="sect" Type="String" />
             <asp:Parameter Name="cnicno" Type="String" />
             <asp:Parameter Name="nationality" Type="String" />
             <asp:Parameter Name="mailingadd" Type="String" />
             <asp:Parameter Name="permamnentadd" Type="String" />
             <asp:Parameter Name="companyname" Type="String" />
             <asp:Parameter Name="companyurl" Type="String" />
             <asp:Parameter Name="companyemailadd" Type="String" />
             <asp:Parameter Name="companyadd" Type="String" />
             <asp:Parameter Name="faxno" Type="Int64" />
             <asp:Parameter Name="companydescription" Type="String" />
             <asp:Parameter Name="contactperson" Type="String" />
             <asp:Parameter Name="agreetodecission" Type="Boolean" />
             <asp:Parameter Name="userid1" Type="Int32" />
         </UpdateParameters>
         <InsertParameters>
             <asp:Parameter Name="fullname" Type="String" />
             <asp:Parameter Name="email" Type="String" />
             <asp:Parameter Name="password" Type="String" />
             <asp:Parameter Name="question" Type="String" />
             <asp:Parameter Name="answers" Type="String" />
             <asp:Parameter DbType="Date" Name="doc" />
             <asp:Parameter Name="roleid" Type="Int32" />
         </InsertParameters>
     </asp:SqlDataSource>
 </div>
</asp:Content>

Recommended Answers

All 4 Replies

You do not have to use the graphical wizards. You can go back to the aspx page and just update the select, update, delete, and insert commands manually. Just manually modify line 83 in the code you posted for the update command.

ok i did but now it says that update syntax is not correct,
please provide a correct one

That's a very long update command you have there. You most likely have a mistake in your syntax. What i do is create my SQL statements using SQL management studio, then copy them over once i have them tested and working. I declare the same variables in SQL studio so that they work when i copy them over.

Its a lot of work, but for complex SQL statements, this method allows for quicker troubleshooting.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.