dataadapter update method

//Update the records using update method of dataadapter object

 

   SqlDataAdapter da = new SqlDataAdapter();

            SqlCommand cmd = new SqlCommand();

         

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString() );

            cmd.Connection = con;

            SqlCommand upCmd = new SqlCommand(“update authors set au_lname=@au_lname where au_id=@au_id”, con);

            upCmd.Parameters.Add(“@au_lname”, SqlDbType.VarChar, 40, “au_lname”);

 

            upCmd.Parameters.Add(“@au_id”, SqlDbType.VarChar , 11, “au_id”);

 

            da.UpdateCommand  = upCmd; 

 

 

 

            da.Update(ds);

 

Here parameter values “au_lname” and  “au_id”  are column names in dataset.

Sql server date time functionality

Select * from users where convert(char,createddate,101)=’07/29/2008′

select cast(getdate() as char)

Resetting the table identity values

To reset the identity value in database table

 

Syntax: DBCC checkident (tablename, reseed, 0)

 

DBCC checkident can reseed the value of the table. For e.g.: our table has 25 records with 25 as last identity. If we want next record to have identity has 35 we need to run following T-SQL

 

DBCC checkidetnt (tablename, reseed, 34)

 

Disadvantage: It will violate the uniqueness.

Copying one table values into another table.

INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
–Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable

IF it is in different database.
INSERT INTO SmithNephewStoreFront.dbo.category ( [Name],parentcategoryID)
SELECT  category,subcatid
FROM endo.dbo.category
WHERE catid = 2

resetting the identity column in the database table .

 

DBCC CHECKIDENT (category, RESEED, 0)

Assign a value to a textbox which is in password mode

Assign a value to a textbox which is in password mode
[ Assign password value to a text box]
 txtPassword.Attributes.Add(”value”, password);

Avoid duplicate insertion while page refresh

 

 Whenever you insert one record and after that if u click on refresh, again the same record insert once again. To avoid that we can use the following code in page load. And whereever u want simply u can use

if (!isrefresh){ }

 

 

 

 

if (Session["postid"] != null && ViewState["postid"] != null)

{

if (ViewState["postid"].ToString() != Session["postid"

].ToString()) 

 

 

 

IsPageRefresh =

 

 

true;

}

Session["postid"] = System.Guid.NewGuid().ToString();

ViewState["postid"] = Session["postid"];

select dropdown list value using javascript

                  var i;
                   var colorValue;
                  var color control
                  
                   colorControl = document.getElementById(”drp”) ;
                 
                for(i=colorControl.options.length-1;i>=0;i–)
                {
                if(colorControl.options[i].selected)
                 {
                  colorValue = colorControl.options[i].value;
                 
                  }
                }

gridview events

 Maintain validations in Gridview

      <Columns>

<asp:TemplateField HeaderText=”Name” > 

<EditItemTemplate> 

    <asp:TextBox  ID=”txtName” runat=”server” Text=’<%# Bind(”Name”) %‘></asp:TextBox>    

<asp:RequiredFieldValidator ID=”RequiredFieldValidator1″ runat=”server”   ControlToValidate=”txtName”  ErrorMessage=”enter name.”                                                                                                                SetFocusOnError=”True” >*   </asp:RequiredFieldValidator>   </EditItemTemplate>      <ItemTemplate>       <asp:Label ID=”Label1″ runat=”server” Text=’<%# Bind(”Name”) %>‘></asp:Label>      </ItemTemplate> </asp:TemplateField>  <asp:BoundField DataField=”no” HeaderText=”no” />

   </columns>

                                             To retrieve that grid view values 

                                           TextBox txtAccountName = (TextBox)gridview.Rows[e.RowIndex].FindControl(“txtName”);             TextBox no = (TextBox)grideview.Rows[e.RowIndex].Cells[4].Controls[0];   

                           To call a java script function to display the confirm messages.

 <Columns>     <asp:TemplateField >     <ItemTemplate>     <asp:LinkButton  ID=”lbDelete” Text=”Delete” runat=”server”           OnClientClick=”return confirm(’Are you sure you want to delete record ?’);”                                        CommandName=”Delete” CssClass=”labelText”   CausesValidation=”false”  />                        </ItemTemplate>  </asp:TemplateField>

</Columns>

  To retrieve a value from grid view by using data keys. int ID = Convert.ToInt32(Gridview.DataKeys[e.RowIndex].Value); 

Grid view row editing.

 Gridview.EditIndex = e.NewEditIndex; 

Grid view page index changing. 

 gridview.PageIndex = e.NewPageIndex;    

If I click edit button in grid view after that I click on submit or any other button.It performs some operations. I don’t want do operations when I click on edit button. if (GVAccounts.EditIndex == -1){} 

Consuming web service from VBScript file

Hi All,

For our current project, the requirement was to call a web service (Web service internally will do some task) on a particular time interval say hourly, daily, monthly etc…

The easy solution we adopted is consuming this web service in VBScript file and schedule this file in the MS-Scheduler. The VBScript looks like

 Set oServerXML = CreateObject(”Msxml2.ServerXMLHTTP”) oServerXML.Open GET”,”http://chm18/purgeservice/purgeservice.asmx/DeleteArchiveFolder1″, FalseoServerXML.setRequestHeader “Content-Type”,”application/x-www-form-urlencoded” oServerXML.sendSet oServerXML = nothing  

Just copy the above code in a VBScript file and schedule this file in a MS-Scheduler. While executing this file if u not yet get the result means then try to execute the web service from .net application using the HTTPWebRequest method. if this is executing without errors means then the VBScript file executes correctly. Note: While executing the web service using HTTPWebRequest method if u got the “The remote server returned an error: (500) Internal Server Error” means then go and add the following code snippet in the web.config file of the web service                   

     <webServices>
           <protocols>
                                         <add name=”HttpGet”/>
                                          <add name=”HttpPost”/>
         </protocols>
</webServices>

 That’s all the web service will works fine…. 

Regards

DotnetRaja