SQL-Queries In A Loop And Add To A Datalist

Apr 13, 2007

Hello Guys,
I am really not very happy with all the possibilities you have in ASP.Net other than in Classic ASP. Back then I made a loop in which you access to a Database with your SQL-Statement and fill the Recordset into HTML-Tables.
 
Now I cannot really do that anymore (at least not in this existing project). My task is to display additional content.
We are using the Timetracker-SDK and are displaying the hours spent on any projects.
Now I shall add the number of bugs, suggestions and so on that are in the database for the specific project.
An ObjectDatasource is accessing to a DotProcedure (written correctly?) and displaying it in a DataList.
I really do not have any knowledge to modify it

So my question: Can I add a SqlDatasource and modify the "Select-" Statement in the runtime and add the Output to the Datalist?

Thanks in advance on any reply here!


Below the .ASPX-Part of the Site:

 <asp:ObjectDataSource ID="ProjectReportData" runat="server" TypeName="ASPNET.StarterKit.BusinessLogicLayer.Project"
SelectMethod="GetProjectByIds">
<SelectParameters>
<asp:SessionParameter Name="ProjectIds" SessionField="SelectedProjectIds" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:timetrackerConnectionString2 %>"
SelectCommand="select Count(*) as Anzahl from issuetracker_issues where projectid =9 and issuecategoryid=16 and issuestatusid =17">
</asp:SqlDataSource>
<table>
<tr>
<td colspan="2">
<asp:DataList ID="ProjectList" RepeatColumns="1" RepeatDirection="Vertical" runat="server"
DataSourceID="projectReportData" OnItemCreated="OnProjectListItemCreated">
<HeaderStyle CssClass="header-gray" />
<HeaderTemplate>
Project Report
</HeaderTemplate>
<ItemTemplate>
<table border="0" cellpadding="0" cellspacing="0" class="Content" width="100%">
<tr>
<td valign="top">
 </td>
</tr>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" class="Content" width="100%">
<tr>
<td width="180" class="report-main-header">
Project Name</td>
<td width="70" align="right" class="report-main-header">
Est. Hours</td>
<td width="100" align="right" class="report-main-header">
Actual Hours</td>
<td width="100" align="right" class="report-main-header">
Est. Completion</td>
<td width="100" align="right" class="report-main-header">
Issues</td>
<td width="100" align="right" class="report-main-header">
Solved</td>
<td width="100" align="right" class="report-main-header">
Reported</td>
<td width="100" align="right" class="report-main-header">
Not Qualified</td>
</tr>
<tr>
<td class="report-text">
<%# Eval("Name") %>
</td>
<td class="report-text" align="right">
<%# Eval("EstimateDuration") %>
</td>
<td class="report-text" align="right">
<%# Eval("ActualDuration") %>
</td>
<td class="report-text" align="right">
<%# Eval("CompletionDate", "{0:d}") %>
</td>
</tr>
</table> 
 

View 2 Replies


ADVERTISEMENT

UPDATE With A DataList

Jan 20, 2006

Hi,
I'm having some troubles getting my UPDATE to work with my DataList control.  I am trying to get Optimistic Concurrency working, but i'm getting an exception stating that the dictionary for oldValues is empty.  I understand this is referring to the original values it compares with before performing the update, however, my question is how do I fill this dictionary in with the original values?
I have included a code sample of the sort of thing i am trying to achieve, it is just a simplified example of how I am trying to UPDATE the underlying data source.  Am I on the right track?  What do I need to do in order to get this to work?
Thankstrenyboy
 
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = e.Item.ItemIndex;
DataList1.DataBind();
}
protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
SqlDataSource1.UpdateParameters["UnitPrice"].DefaultValue = ((TextBox)e.Item.FindControl("UnitPriceTextBox")).Text;
SqlDataSource1.UpdateParameters["UnitsInStock"].DefaultValue = ((TextBox)e.Item.FindControl("UnitsInStockTextBox")).Text;
SqlDataSource1.UpdateParameters["UnitsOnOrder"].DefaultValue = ((TextBox)e.Item.FindControl("UnitsOnOrderTextBox")).Text;
SqlDataSource1.Update();
DataList1.EditItemIndex = -1;
DataList1.DataBind();
}
protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = -1;
DataList1.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [ProductName], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ProductID] FROM [Products]" UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND [UnitPrice] = @original_UnitPrice AND [UnitsInStock] = @original_UnitsInStock AND [UnitsOnOrder] = @original_UnitsOnOrder" OldValuesParameterFormatString="original_{0}" ConflictDetection="CompareAllValues">
<UpdateParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="UnitPrice" Type="Decimal" />
<asp:Parameter Name="UnitsInStock" Type="Int16" />
<asp:Parameter Name="UnitsOnOrder" Type="Int16" />
<asp:Parameter Name="original_ProductID" Type="Int32" />
<asp:Parameter Name="original_ProductName" Type="String" />
<asp:Parameter Name="original_UnitPrice" Type="Decimal" />
<asp:Parameter Name="original_UnitsInStock" Type="Int16" />
<asp:Parameter Name="original_UnitsOnOrder" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>

</div>
<asp:DataList ID="DataList1" runat="server" DataKeyField="ProductID" DataSourceID="SqlDataSource1" RepeatDirection="Horizontal" RepeatLayout="Flow" OnEditCommand="DataList1_EditCommand" OnUpdateCommand="DataList1_UpdateCommand" OnCancelCommand="DataList1_CancelCommand">
<HeaderTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<th>Product Name</th>
<th>Unit Price</th>
<th>Units in Stock</th>
<th>Units on Order</th>
<th></th>
</tr>
</HeaderTemplate>

<ItemTemplate>
<tr>
<td><asp:Label ID="ProductNameLabel" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label></td>
<td><asp:Label ID="UnitPriceLabel" runat="server" Text='<%# Eval("UnitPrice") %>'></asp:Label></td>
<td><asp:Label ID="UnitsInStockLabel" runat="server" Text='<%# Eval("UnitsInStock") %>'></asp:Label></td>
<td><asp:Label ID="UnitsOnOrderLabel" runat="server" Text='<%# Eval("UnitsOnOrder") %>'></asp:Label></td>
<td><asp:LinkButton ID="LinkButtonEdit" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton></td>
</tr>
</ItemTemplate>

<EditItemTemplate>
<tr>
<td><asp:Label ID="ProductNameLabel" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label></td>
<td><asp:TextBox ID="UnitPriceTextBox" runat="server" Text='<%# Eval("UnitPrice") %>'></asp:TextBox></td>
<td><asp:TextBox ID="UnitsInStockTextBox" runat="server" Text='<%# Eval("UnitsInStock") %>'></asp:TextBox></td>
<td><asp:TextBox ID="UnitsOnOrderTextBox" runat="server" Text='<%# Eval("UnitsOnOrder") %>'></asp:TextBox></td>
<td><asp:LinkButton ID="LinkButtonUpdate" runat="server" CommandName="Update" Text="Update"></asp:LinkButton> <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Cancel" Text="Cancel"></asp:LinkButton></td>
</tr>
</EditItemTemplate>
</asp:DataList>
</form>
</body>
</html>
 

View 1 Replies View Related

Retrieve Id_product From A Datalist

Jan 31, 2008

 Hi all, I'm trig to create a simple cart in ASP.NET/C#. I've create a table named cart in which there are 2 field (user_id and id_product). In my page product I show all product and the cart icon (this is made by an asp.net image_button). When a person click on the cart there's and event in .cs which should insert the relative product in cart table. But how a can retrieve the product id?This is my .cs page: using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class provacarrello : System.Web.UI.Page{    private string connectionString = WebConfigurationManager.ConnectionStrings["MySQLconnection"].ConnectionString;    protected void Page_Load(object sender, EventArgs e)    {    }    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)    {        MembershipUser mu = Membership.GetUser(User.Identity.Name);        Guid myGuid = (Guid)mu.ProviderUserKey;        string insertSQL = "INSERT INTO cart (";        insertSQL += "userid, id_product)";        insertSQL += "VALUES(";        insertSQL += "@userid, @id_product)";        SqlConnection myConnection = new SqlConnection(connectionString);        SqlCommand cmd = new SqlCommand(insertSQL, myConnection);        //Add the parameters        cmd.Parameters.AddWithValue("@userid", myGuid);        cmd.Parameters.AddWithValue("@id_product", DataList1.???       <--- ???    }} Thank u in advance! 

View 5 Replies View Related

Selecting Data From Datalist

Jan 19, 2007



Hi all

I am having problem in selecting the data from my datalist. Here is teh sample code
<asp:datalist id="dlAttrName" runat="server" OnItemCommand="dlAttrName_SelectData" DataKeyField="attrID">
<ItemTemplate>
<asp:LinkButton ID="lbSelect" Runat="server" CommandName="Select" style="display:none">Select</asp:LinkButton>
<%# Container.DataItem("attrDef1")%>
</ItemTemplate>
</asp:DataList>
Private Sub dlAttrName_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs) Handles dlAttrName.ItemDataBound
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
'Add eventhandlers for highlighting
'a DataListItem when the mouse hovers over it.
e.Item.Attributes.Add("onmouseover","????")
e.Item.Attributes.Add("onmouseout", "????")
'Add eventhandler for simulating
'a click on the 'SelectButton'
e.Item.Attributes.Add("onclick", ?????????????????????, String.Empty))
??????????????????? this should have something like this
Me.Page.ClientScript.GetPostBackEventReference(e.Item.Controls(1)
But I am not able to get the ClientScript option, I don't know which namespace should I import?

I don't know whether I am right or not.
Its urgent please reply back soon,any help will be useful.
Thanks!!!!!!!

View 1 Replies View Related

Default Image URL In DataList Control!

Nov 29, 2007

Hey,
I have two questions:
1- In my page there should be a list of all system users, each of them may have an image or may not.
 So what I need to do is how to make the next SQL query return a default ImageURL - e.g. "noAvator.jpg" - in case of null image value?
SELECT     UserName , image, notesFROM         Members
2- When I try to write an image url in the SQL Server 2005 Management Studio - as "images/noAvator.jpg" or any other name - it always through me this error messagebox:
The changed value is not recignized as valid..NET Framework Data Type: Byte[]Error Message: You can not use the Result pane to set this Field data to value other than NULL.
Why is that? is there a problem with the format of typing the image url?
I appreciate your help!

View 4 Replies View Related

Query To Each Item Inside A Datalist

Dec 25, 2007

Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server">    Sub Page_Load(Sender As Object, E As EventArgs)        Dim DS As DataSet        Dim MyConnection As SqlConnection        Dim MyCommand As SqlDataAdapter        MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")        MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection)        'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True        DS = New DataSet()        MyCommand.Fill(DS, "aspnet_Users")        MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView        MyDataList.DataBind()                          End Sub</script><body>    <br />  <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server">      <ItemTemplate>        <table cellpadding="10" style="font: 10pt verdana" cellspacing="0">          <tr>            <td width="1" bgcolor="BD8672"/>            <td valign="top">                          </td>            <td valign="top">            <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div>               <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status:          </div>                            <p></div>              <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' >                                <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%>                                <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" >              </a>            &nbsp;&nbsp;</td>          </tr>        </table>      </ItemTemplate>  </ASP:DataList>  </body></asp:Content> Thanks a lot. 

View 5 Replies View Related

Data Not Displaying (in DataList) From SQL On GoDaddy

Apr 22, 2006

Everything works great on my development box.  I am using GoDaddy for production (ASP.Net v2, SQL 2000).
I am not receiving any errors, so I am stumped; no data from the database is displaying on the GoDaddy pages.
I updated the connection string in web.config to this:

< add name="snsb" connectionString="
Server=whsql-vXX.prod.mesaX.secureserver.net;
Database=DB_42706;
User ID=username;
Password=pw;
Trusted_Connection=False" providerName="System.Data.SqlClient" / >

But I am unsure if this is the issue??  Any insights?  This is the page I am working on: www.sugarandspicebakery.com/demo/bakery/default.aspx.  So, the page displays fine, but it should be showing data from the database.  This particular page uses a DataList with ItemTemplate.  There is definitely data in the database, and I have even ran the same exact query from the code using the Query Analayzer on GoDaddt and it returned results
I know there isn't much info to go by, but I am hoping someone has some insight since I have been trying to figure this out for days now!
Thank youJennifer

View 2 Replies View Related

Selecting From Two Tables With Different Data - Presenting In One Datalist

Oct 6, 2006

Hello!I have two tables:Pressreleases:| Pressrelease_ID | PressDate | FilePath | Title |PressLinks:| PressLink_ID | PressLinkDate | PressLinkUrl | PressText |I would like to select the the TOP 3 with the most recent dates from either Pressreleases or PressLinks, and present them in a DataList. How do I do that? Selecting from one table is no problem:SELECT TOP 3 Pressreleases.PressDate, Pressreleases.Filepath, PressReleases.Title FROM Pressreleases ORDER BY PressDate DESCHow do I select from both tables and rename the columns to Date, Path, Text so that I can use it in a Datalist?Thank you in advance!

View 4 Replies View Related

How To Form A Single SQL Statement In A Datalist For A Challenging And Convoluted Problem ?

Aug 26, 2007

Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem.   I'm relatively new to SQL, so this may not be so tough for some of you.  Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him.  The list is presented to him via a datalist.  Each item of the datalist contains a table of 2 columns and 1 row as pictured below.  The first box contains the user photo and user name of the person who SENT him the message (not the logged in user).  The second box contains the subject title of the message.
      FROM        |    SUBJECT   |  User Photo     |                    ||                       |    Subject     ||  User Name     |                    |
Here is the list of the relevant 4 tables of my database and the relevant fields.....
aspnet_Users tableUserId (used to link to Member table)UserName 
Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table)
Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive)
Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max))
So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user.  The senders will be determined by the Member.fromMember fields.  Intuitive enough so far, I guess.   This is the SQL statement I have so far.....
SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember
Note that I'm grabbing the logged in user info from Profile.UserName.  So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user.  HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)?  Do you see the core of my dilemna here?  I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER).  Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.
And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ??  I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe.   The SQL statement so far asks to retrieve information based on the logged in user.  But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now?  And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement.  This is indeed very very complicated for me lol.
This problem has been giving me migraines this whole weekend.  Has anyone been through such a problem before?  Any help would be greatly appreciated - thanks in advance.

View 22 Replies View Related

How To DataList To Read Stored Procedure Data To A Href Link

Sep 26, 2007

 I am coding a DataList control for 2 column data from a stored procedure.  The 1st column isprogramName and the 2nd is programLink.  I used Edit Template -> Edit DataBonding to add<h ref="http.....>, but can not get it right.  Please help.  Also, what is the Parameter Sourcefor using Logon User ID as a stored procedure paramter when configuring a SqlDataSource? None, cookie, control,..?  TIA,Jeffrey
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1" Style="z-index: 16;left: 29px; position: absolute; top: 82px"><ItemTemplate>programName:<asp:Label ID="programNameLabel" runat="server" Text='<%# Eval("programName") %>'></asp:Label><br />programlink:<asp:Label ID="programlinkLabel" runat="server" Text='<%# Eval("programlink") %>'></asp:Label><br /><br /></ItemTemplate></asp:DataList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:iTemplatesConnectionString %>"SelectCommand="MyPrograms" SelectCommandType="StoredProcedure"><SelectParameters><asp:Parameter Name="meid" Type="Int32" DefaultValue="3532" /></SelectParameters></asp:SqlDataSource>

View 4 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

ForEach Loop Or For Loop??

Feb 23, 2006

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View 1 Replies View Related

Update From Datalist Does Not Update.

Apr 21, 2008

I try to get the display a datalist with nickname. And make changes to the nickname and then press update button (UpdateClick) to update nickname changes
Problem:

The value of the datalist did change from the datalist but just does not update the SQL 2005 Express databaseSub UpdateClick(ByVal sender As Object, ByVal e As System.EventArgs)
Dim asdf As TextBox = CType(ProfileDataList.Items(0).FindControl("txtnickname"), TextBox)
Dim asdfnickname As Stringasdfnickname = CStr(asdf.Text)
Response.Write(asdfnickname)
Dim asdfSession As String = "1"Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("Local_LAConnectionString1"))Dim myCommand As SqlCommand = New SqlCommand("ProfileUpdate", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Connection = myConnectionmyCommand.Parameters.AddWithValue("@nickname", asdfnickname)
myCommand.Parameters("@nickname").Direction = ParameterDirection.InputDim parameterCusID As SqlParameter = _New SqlParameter("@CustomerID", SqlDbType.NVarChar, 2)
parameterCusID.Value = asdfSession
myCommand.Parameters.Add(parameterCusID)Try
myConnection.Open()
myCommand.ExecuteNonQuery()Catch ex As Exception
 
Finally
myConnection.Close()
End Try
 
End Sub
<asp:TextBox ID="txtnickname" Text='<%#DataBinder.Eval(Container.DataItem, "nickName")%>' runat="server" />
<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="UpdateClick" />
ProfileUpdate (Storeprocedure)USE [Local_LA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[ProfileUpdate]
@CustomerID nvarchar(2),
@nickname nvarchar(75)
 
AS
UPDATE CustomersSET nickName = @nickname
 
 WHERE
CustomerID = @CustomerID
 

View 7 Replies View Related

Parameterized Queries Running Slower Than Non-parameterized Queries

Jul 20, 2005

HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)

View 1 Replies View Related

How Do You LOOP In T-SQL?

Aug 20, 2007

I used to loop through recordsets with ease in old classic .asp pages.
Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below.
I would love to learn how to do it to better develop my skills.
 SELECT * FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]

IF [Product Interest - Actuators] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Actuator] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Chillers] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]
 
 

View 24 Replies View Related

For Loop

Jan 12, 2008

Dear All.
 Have a nice day.
I have db table, I need to update all fields of table.
Please can you write code," for loop " how can update all fields of my table by loop.
 Thanks.
Zahyea.

View 3 Replies View Related

While Loop

Mar 3, 2008

Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN            DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int            SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId                                   If @ProdSize = 'XL'            BEGIN                UPDATE                    _ProductBatches                SET                    XL = '0'                WHERE                    ProductId = @ProdId            END            DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here.  Thank you, Alec 

View 7 Replies View Related

More Of This While Loop

Mar 4, 2008

Hello everyone...... I'm trying to do the following but am having issues:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN        execute code with item......... erase itemEND      In the while loop I want to execute code from each item in my _ShoppingCart and then erase them until there are no more items. However the above code gives me the error: "Subquery returned more than 1 value. This is not permitted........" It works fine when there is only one item. Does any one know what format to use when dealing with more that one entry?  Thank you, Alec 

View 2 Replies View Related

For Next Loop

Sep 21, 2000

hi,
I am trying to find a way of using a loop that won't be an endless loop because I have to insert parts of a string until the string reaches the end. I am unable to make the loop get to a point where the statement is false.

Is there anyway I can find out the length of the string so that I can tell the statement to loop while the statement is true?

Help!!!!!!!!!!!!1

View 1 Replies View Related

For Loop

Jan 26, 2004

HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;


The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.

can I write like this?


for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@AcctNum);
set @Cntr=@Cntr+1
}


I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared
in the beginnning.



tks

View 14 Replies View Related

Loop Help

Apr 11, 2008

I get the following results on a view.

Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 2 | A1102
99-999 | 4 | AB839

What I would like is the following.

Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 1 | A1102
06-324 | 1 | A1102
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839

View 2 Replies View Related

Loop Won't End.

Apr 18, 2008

I have a loop is running with no end point. What I'm trying to do is get the Grand total of each row where BudgetNodeID = 120. Your help is much appreciated.

AV

Set NoCount on
Declare @Amt as bigint
Declare @Cont as bigint
Declare @Mark as Bigint
Declare @Total as bigint
Declare @BudgetNodeID as Bigint
Declare @GTotal as bigint
Set @BudgetNodeID ='120'
Set @Amt = 0
set @Cont = 0
set @Mark = 0
set @GTotal = 0

While exists (Select * from xBudgetNodeCosts where BudgetNodeID =@BudgetNodeID) Begin
select @Amt = IsNull(xBudgetNodeCosts.Qty,0) * IsNull(xBudgetNodeCosts.CostRate,0)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')

select @Cont = @Amt * (xBudgetNodeCosts.Contingency/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID ) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
select @Mark = @Cont * (xBudgetNodeCosts.Markup/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
-- compute the sell

select @Total = @Amt + @Cont + @Mark

-- add to grand total
Select @GTotal = Sum(@Total+ @GTotal)

select @GTOtal
end

View 5 Replies View Related

Loop

Jun 17, 2008

Hi

I want to loop this command with a pause. It must run every 30 min to refresh my query. Can anyone assist me.



select * from BULKPICKXLOC



Thanks

View 4 Replies View Related

For To Next Or Do While Loop In SP

Apr 11, 2006

Exist a funtion that I can use in a SP that do something like the for to next or Do while Loop do?

View 5 Replies View Related

While Loop

Jan 30, 2007

I need to keep the first 4 values above 80 or the first 2 values above 90. If there are not enough, I need to keep as many values as possible. Should this be done with a while loop, if so, how would it be done.

View 19 Replies View Related

Loop

Mar 13, 2007

hello,
i have this SP to tally up my inventory tables.. im finding a way to loop through my table tblitemdetail to get necessary parameter to be insert into my other SP (SP_StkAdj_tbl_alignmt) that should accept this params (from the itemdetail) :-
@ItemID ='',
@ClientID='',
@CustomLotNo ='',
@UDF1=NULL,
@UDF2=NULL,
@UDF3 =NULL,
@UDF4 =NULL,
@UDF5=NULL,
@UDF6 =NULL,
@UDF7 =NULL,
@UDF8 =NULL,
@UDF9 =NULL,
@UDF10 =NULL,
@StockID ='0950-4388',
@RecvOwn ='OWN',
@ConsignorID ='JAB1MY'
EG:i will GROUP BY my itemdetail so it will give me the x records of data with :-

SELECT ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3, ownerstatus
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3,ownerstatus
ORDER BY ItemID

with the result then, i need to insert the param into the SP:SP_StkAdj_tbl_alignmt so that it perform the calculation.

so i guess this will need some looping from the result set i get from the group by and some Sp calling from main Sp

~~~Focus on problem, not solution~~~

View 20 Replies View Related

While Loop

Apr 18, 2007

hi all, ive no idea what's wrong with my while loop nested in IF .. it only work correctly when i remove the while from IF :-


IF @Picktype='FI'
BEGIN
-- Insert data into @Stage to play around
DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))

WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
INSERT@Stage (ItemStorageID, Qty, RecvDate, BB)
SELECT TOP 1t1.ItemStorageID,
t1.Qty,
t1.RecvDate,
t1.BB
FROM#DataList AS t1
LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
WHEREs.ItemStorageID IS NULL
ORDER BYt1.RecvDate,
t1.Qty DESC

IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
SELECTrecID,
ItemStorageID,
Qty,
RecvDate,
BB
FROM@Stage
ELSE
select * from #DataList
END
correct result after i remove the while from inside IF

DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))


WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
INSERT@Stage (ItemStorageID, Qty, RecvDate, BB)
SELECT TOP 1t1.ItemStorageID,
t1.Qty,
t1.RecvDate,
t1.BB
FROM#DataList AS t1
LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
WHEREs.ItemStorageID IS NULL
ORDER BYt1.RecvDate,
t1.Qty DESC

IF @pickType='FI'
BEGIn
IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
SELECTrecID,
ItemStorageID,
Qty,
RecvDate,
BB
FROM@Stage
end


~~~Focus on problem, not solution~~~

View 9 Replies View Related

Should I Use A Loop?

Sep 18, 2007

I have the following stored procedure which enters items bought by the usr in the database, inserting the user's ID, the item and the price. now every user has a unique id and every user can only buy three items; thus only three inputs must be inserted in the table, how can i do that? This is the current SP i have...

ALTER PROCEDURE [dbo].[spA_ALW_InsertIntoMLAGoods]

@MLAFormIDFINT,
@DescriptionNVARCHAR(50),
@AmountINT

AS

BEGIN
BEGIN TRY
--UPDATE MLAGoods
--SET
--MLAFormIDF=@MLAFormIDF,
--Description=@Description,
--Amount=@Amount
--WHERE MLAFormIDF = @MLAFormIDF

INSERT INTO MLAGoods
(
MLAFormIDF,
Description,
Amount
)
VALUES
(
@MLAFormIDF,
@Description,
@Amount
)
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [ERRORMSG], ERROR_SEVERITY() AS [ERRORSEV]
RETURN @@ERROR
END CATCH

END

View 4 Replies View Related

Sql Loop

Sep 25, 2007

Hi i have a sql loop query which i have working in asp fine, i have altered it to try and get it working as a stored procedure.

but i am not sure what the syntax is.

can someone help please.

many thanks



DECLARE
CURSOR GetWebOrder_cur IS
SELECT O_R_ID, O_Name, O_Add_1, O_DB_Code, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_Instore, O_STC_Code, O_ID
FROM [newserver].dbo.X_TBL_ORDER
WHERE NewOrder = 0

BEGIN
FOR GetWebOrder_rec IN GetWebOrder_cur LOOP

-- SET ALL FIELDS
set R_ID34 = GetWebOrder_cur("O_R_ID")
set R_Name = GetWebOrder_cur("O_Name")
set R_Contact = GetWebOrder_cur("O_Add_1")
set R_Code = GetWebOrder_cur("O_DB_Code")
set R_Add_1 = GetWebOrder_cur("O_Add_2")
set R_Add_2 = GetWebOrder_cur("O_Add_3")
set R_Add_3 = GetWebOrder_cur("O_Add_4")
set R_Add_4 = GetWebOrder_cur("O_Add_5")
set R_Add_5 = GetWebOrder_cur("O_Add_6")
set R_Add_6 = GetWebOrder_cur("O_Add_6")
set R_PostCode = GetWebOrder_cur("O_PostCode")
set R_CostCode = GetWebOrder_cur("O_CCode")
set R_Delivery = GetWebOrder_cur("O_Service")
set R_Instore = GetWebOrder_cur("O_Instore")
set R_STCODES = GetWebOrder_cur("O_STC_Code")
set WebOrderID = GetWebOrder_cur("O_ID")

-- GET MAX ID
SELECT Max(O_ID) AS MAXOID FROM dbo.X_TBL_ORDER

-- INSERT ORDER INTO F4 ORDERS
INSERT INTO dbo.X_TBL_ORDER
(O_Date, O_R_ID, O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_O_By, O_Instore, O_STC_Code)
VALUES
(getdate(), R_ID34, R_Name, R_Code, R_Contact, R_Add_1, R_Add_2, R_Add_3, R_Add_4, R_Add_5, R_PostCode, R_CostCode, R_Delivery, R_Contact, R_Instore, R_STCODES)

-- UPDATE VIRTUAL SERVER SET NewOrder = 1
UPDATE [newserver].dbo.X_TBL_ORDER
SET NewOrder = 1
WHERE O_ID = WebOrderID

-- SET CURSOR FOR ORDERLINES
CURSOR orderlines_cur IS
SELECT *
FROM [newserver].dbo.X_TBL_ORDER_LINE
WHERE OL_O_ID = @WebOrderID
-- OPEN LOOP THROUGH ORDERLINES
FOR orderlines_rec in orderlines_cur LOOP

-- SET ORDERLINE FIELDS
set B_St_Code = orderlines_cur("OL_St_Code")
set B_Description = orderlines_cur("OL_Desc")
set B_Qty = orderlines_cur("OL_Qty")
set B_dbcode = orderlines_cur("OL_DB_Code")

-- INSERT INTO F4 ORDERLINES
INSERT INTO dbo.X_TBL_ORDER_LINE
(OL_O_ID, OL_St_Code, OL_Desc, OL_Qty, OL_Allocated, OL_Despatch, OL_DB_Code)
VALUES
(B_preorderID, B_St_Code, B_Description, B_Qty, B_Qty, B_Qty, B_dbcode)
-- CLOSE LOOP THROUGH ORDERLINES
END LOOP;

END LOOP;
END;

View 9 Replies View Related

Loop

Nov 21, 2007

Hi,

I have this scenario: I have a table one database in sql called facttable_Temp with columns CustomerName, ItemKey.The fields are varchar.


I have another table called Accounts in a different database. Accounts contains fields such as CustomerName,Account. The fields are varchar.

What I need to do is to check if the values for CustomerName from table FactTable_Temp exists in the field CustomerName in the table Accounts.
If it exists then I need to insert the entire row for that CustomerName including ItemKey into a 3rd table called FactTable.

What is the best way of accomplishing this in SSIS?

Thanks


Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved