This Question is pertaining to AND and OR operators.
If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?
Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.
let's say I have a table named "myTable" in a SQL database:
UniqueID
FirstName
FamilyName
1
Elizabeth
Moore
2
Chris
Lee
2
Robert
McDonald's
I want to create a SQL query should contain a parameter for example:
SELECT * FROM myTable WHERE UniqueID = @TextBox OR FirstName = @TextBox OR FamilyName = @TextBox,
and when I type in my TextBox the ' * ' character, it should retrieve the whole table...
hope anybody understood, will be happy to explain more.
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
View 1 Replies View Related
Basically what i have is a ddl that gets filled with column data "BidCity" in the C# code behind. with this code.
All im interested in is how to add a value("with SQL") that doesnt exist in the database and have it populated into the ddl with all the other info in the "BidCity" column. read more below.....
Is this even possible with SQL?
private void DoCitiesInit()
{
//CONNECTION STRING
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
DataSet dsCities = new DataSet();
SqlDataAdapter daCities = new SqlDataAdapter("SELECT DISTINCT Bids.BidCity, Bids.BidStatusID FROM Bids WHERE Bids.BidState = '" + ddlStates.SelectedValue + "' AND Bids.BidStatusID = 1 ORDER BY Bids.BidCity ", conn);
daCities.Fill(dsCities, "Bids");
ddlCities.DataTextField = "BidCity";
ddlCities.DataValueField = "BidCity";
ddlCities.DataSource = dsCities;
ddlCities.DataBind();
}
I would like to add a text field value "(All Cities)" to my ddl with the other data that gets populated into the ddl, and have that selection have a value(999 or whatever) without having to put this in the database. My reason behind this madness is that if i put this data into a row in the same table it will get populated into the gridview with the rest of my code.
Basically i am working with a database i myself did not design and i cannot change the database as it is being used for a windows application and i am using this table to populate for a website for end users. if i dont make sense please let me know. Im used to it.
Thanks
I need to have tables of world countries and cities to use in a user registration form, I searched the Internet for scripts,xml,excel files for countries & cities lists but I couldn't find. Can anybody tell me where to get them from?
View 6 Replies View Related
Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location
location shows the dif cities and the sql query will show cities will a count of people per city
how do you do this?
Hello,
I'm new to the SQL world and want to know if this is the site for a newbie or would you recommend another site more my speed. Thanks for the input.
Clint
US Navy - fueled, armed, and go for launch.
I have the country iso table does anyone have the data for tying contenents to the countries?
You can do anything at www.zombo.com
Hi,
I have created some statistics for my site based on the ip of the visitors. For that reason I have created two tables
1. One table with a column which hold the ip number of the visitor
Table: AccessLog
Column: IP
2. One table with the ip range and the countries
Table: Countries
Columns: ip_to, ip_from, country_name
I would like to create a query which will bring the country name of the visitor IP which IP will be like "ip_to <= ip <= ip_from"
Thanks
Denis
www.tabletennis.gr
Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris
I dont know the exact position of '/',',','--' or any special character.I have to fetch first name of the country.
create table abc
(country nvarchar(200))
go
INSERT INTO abc VALUES ('Tennessee/Virginia,USA')
GO
INSERT INTO abc VALUES ('Kansas,USA')
[code]...
Hi!How I can know the "Country version" of SQL Server using VB.NET(perhaps with the SQLDMO)?I mean: if the SQL Server is in "English version" or in "Italianversion" or ...Thank you so much!!Bye
View 1 Replies View RelatedHiI am using Visual Web Developer 2005 Express and SQL Server Express 2005.From Northwind I can display data to Gridview.Could someone point me in the right direction on two points.How do I start a new database (.mdf) - I cannot find this option.I can change Northwind around - but I want to start my own database.
Once I have that sorted - how can I "insert" data into database using a dropdown box?A "dropdown" box such as you would find online.
Thanks in advance.Stephen
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
I am having a users table which contains "Mobile" column as well. I want a query to set the country code value by default into the column name so that the column should be updated with the mobile number along with the default country code.
View 10 Replies View RelatedI need help to resolve the following problem.
I have a form that is supposed to insert data into my data base. But I get an error message when I click submit.
Below is the Code and after that is the error message that I get. Thank you in advance.
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
End SubProtected Sub insertSubmitBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
SqlDataSource1.Insert()End Sub
</script><html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<script type="text/javascript">
function pageLoad() {
}
</script>
<style type="text/css">
.style1
{width: 34%;
}
.style2
{width: 144px;
}</style>
</head><body>
<br />
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<br /><asp:SqlDataSource ID="SqlDataSource1" runat="server"
InsertCommand="INSERT INTO property(provinceName, cityName, ownerName, ownerPhone, numOfRooms, rent, ownerEmail, listPeriod, addInfo)
VALUES(@provinceName, @cityName, @ownerName, @ownerPhone, @numOfRooms, @rent, @ownerEmail, @listPeriod, @addInfo)">
<InsertParameters><asp:ControlParameter ControlID="provinceddl2" Name="provinceName"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="cityddl2" Name="cityName"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="nameTxtBox" Name="ownerName"
PropertyName="Text" /><asp:ControlParameter ControlID="phoneTxtBox" Name="ownerPhone"
PropertyName="Text" /><asp:ControlParameter ControlID="roomsTxtBox" Name="numOfRooms"
PropertyName="Text" />
<asp:ControlParameter ControlID="rentTxtBox" Name="rent" PropertyName="Text" /><asp:ControlParameter ControlID="EmailTxtBox" Name="ownerEmail"
PropertyName="Text" /><asp:ControlParameter ControlID="listPeriodddl" Name="listPeriod"
PropertyName="SelectedValue" /><asp:ControlParameter ControlID="addInfoTxtBox" Name="addInfo"
PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>
<br />
<br />
<br />
<br />
<table cellspacing="1" class="style1">
<tr>
<td class="style2">
Contact Name:</td>
<td>
<asp:TextBox ID="nameTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Contact phone:</td>
<td>
<asp:TextBox ID="phoneTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Province:</td>
<td>
<asp:DropDownList ID="provinceddl2" runat="server" Width="205px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style2">
City:</td>
<td>
<asp:DropDownList ID="cityddl2" runat="server" Width="205px">
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style2">
Email:</td>
<td>
<asp:TextBox ID="EmailTxtBox" runat="server" Width="200px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Number of Rooms:</td>
<td>
<asp:TextBox ID="roomsTxtBox" runat="server" Width="80px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Monthly Rent:</td>
<td>
<asp:TextBox ID="rentTxtBox" runat="server" Width="80px"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style2">
Period to list:</td>
<td><asp:DropDownList ID="listPeriodddl" runat="server"
DataSourceID="listPeriodSqlDataSource" DataTextField="listDays"
DataValueField="listDays" Width="85px">
</asp:DropDownList><asp:SqlDataSource ID="listPeriodSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:PlaceConnectionString1 %>"
SelectCommand="SELECT [listDays] FROM [listPeriod]"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td class="style2">
Additional Infomation:</td>
<td><asp:TextBox ID="addInfoTxtBox" runat="server" Rows="5" TextMode="MultiLine"
Width="200px"></asp:TextBox>
</td>
</tr>
</table>
<br />
; <asp:Button ID="insertSubmitBtn" runat="server" Text="Submit" Width="140px"
onclick="insertSubmitBtn_Click" />
<br/>
<cc1:CascadingDropDown
ID="Provincecdd2"
runat="server"
TargetControlID="Provinceddl2"
Category="Province"
PromptText="Select a province"
ServicePath="Location.asmx"
ServiceMethod="GetProvince" />
<br /><cc1:CascadingDropDown
ID="Citycdd2"
runat="server"
TargetControlID="Cityddl2"
ParentControlID="Provinceddl2"
Category="City"
PromptText="Select a city"
ServicePath="Location.asmx"
ServiceMethod="GetCity" />
</div></form>
</body>
</html>
Server Error .
Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentException: Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[ArgumentException: Invalid postback or callback argument. Event validation is enabled using <pages enableEventValidation="true"/> in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.]
System.Web.UI.ClientScriptManager.ValidateEvent(String uniqueId, String argument) +2132728
System.Web.UI.Control.ValidateEvent(String uniqueID, String eventArgument) +108
System.Web.UI.WebControls.DropDownList.LoadPostData(String postDataKey, NameValueCollection postCollection) +55
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.LoadPostData(String postDataKey, NameValueCollection postCollection) +11
System.Web.UI.Page.ProcessPostData(NameValueCollection postData, Boolean fBeforeLoad) +353
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1194
Hotkwesi
Hello,
I'm trying to make a query to insert data form xls file to my database and it's working
Here's my code
INSERT INTO MY_TABLE
SELECT SAP_NBR, PERIOD, ANSWER, OSAT, SCORE
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:
ipTMP_Aubrygsi_osat.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
WHERE SAP_NBR is not null
AND PERIOD is not null
AND ANSWER is not null
AND OSAT is not null
AND SCORE is not null
And I would like to verify if there no sap_nrb and period already the same in the database
so I add :
AND SAP_NBR not in MY_TABLE.SAP_NBR
AND PERIOD not in MY_TABLE.PERIOD
but it doesn't work
what's the correct syntax to make it work ???
Thanks a lot & merry Xmas... :)
Good day., please help me,in a formview control, i set it in Insert Mode, so it should display info from table 1 but when i click on the insert button, it will insert it in table 2.btw, table 1 and table 2 are in the same database?? how about if they are not in the same database?how?please help me,Thanks.,SALAMAT PO.,
View 3 Replies View RelatedHey All,
I am developing a data acquistion system which monitors the amount of energy that a user consumes in different parts of a house and displays the information in real time on their computer screen. I am collecting the data through tranducers attached to the circuit breakers in the breaker box and sending the data to analog-to-digital converter channels in a MCU. I am retrieving the data from the serial port and storing it to a text file. Each line of data in the text file represents three fields which are separated by commas. I will be reading data from multiple data collection boxes so the first field is the unit number, the second fied represents the analog-to-digital converter channel number from each unit, and the third field is the data that is collected from the ATD channel. I am trying to use SSE to retrieve the data from the text file, and parse each line of data into individual columns in a databse. Then I want to be able to extract the data associated with a particular ATD channel number from the databse and display it in the appropriate text field on a windows form.
I've got the MCU programmed. I have no problem collecting the data from the serial port, and I can do the visual basic programming okay. I have absolutely no clue how to read the data into the database, continuosly read new values into the databse, and then access the stored data to update the text fields on the form. Please help if you can, I've been working on this specific problem for a couple of weeks and I'm not making any progress. Thanks.
hi! can anybody please help me...what would be my query string if i want to combine 3 column into one column?
example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.
I have currently created a design which uses three main tables for storing information related to financial actions. The two tables I wish to combine are described below. There is a third table after the OrderTransactions table which contains information about each step of a transaction.
This means that anytime I have to write a query to get information down at the transaction activity level (very frequently), I will have to always perform two joins. Would it be acceptable in this scenario to combine the Orders and OrderTransactions tables, and place a ParentOrderID field in there? A transaction would either have no parent, or would have to belong to a parent that does not have a parent.
This means that the information in the Orders table will be duplicated for each transaction. The data in the Orders table is more or less static after its initial insert. The data there is never updated, no matter which approach is used.
Either approach will work, I'm just looking to see what some of the people more knowledgeable than me think of the situation.
Orders:
Contains the core order information pertaining to all transactions
CREATE TABLE [Orders] (
[OrderID] [int] NOT NULL ,
[MerchantID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentMethodID] [int] NOT NULL ,
[IsTestOrder] [bit] NOT NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
)
Transactions:
Each order may have one or more transactions. All of the information in the Orders table is pertinent to a given transaction.
CREATE TABLE [OrderTransactions] (
[OrderID] [int] NOT NULL ,
[TransactionID] [int] NOT NULL ,
[TransactionTypeID] [int] NOT NULL ,
[CustomerIPAddress] [bigint] NOT NULL ,
[Description] [nvarchar] (250) NOT NULL ,
CONSTRAINT [PK_OrderTransactions] PRIMARY KEY CLUSTERED
(
[OrderID],
[SequenceID]
) ON [PRIMARY] ,
CONSTRAINT [FK_OrderTransactions_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [Orders] (
[OrderID]
)
)
create view vwchannel
as
select distinct s2.soptype,
s2.sopnumbe , --internet orders/information center orders
s2.custnmbr , --amazon.com orders
s3,itemnmbr, sum(s3.quantity) from salestab s2
left outer join (select distinct soptype,sopnumbe,itemnmbr,quantity from salesdisttab) s3
on s2.soptype = s3.soptype and s2.sopnumbe = s3.sopnumbe
where (s2.CUSTNMBR LIKE 'amazon%')
AND ((s2.SOPNUMBE LIKE 'net%') OR (s2.SOPNUMBE LIKE 'inv%'))
AND s2.soptype = 3
group by s3.itemnmbr,s2.sopnumbe,s2.custnmbr,s2.soptype
go
i m getting 70 rows in output which is correct but..
i have to combine sopnumbe and custnmbr into one column name as channel
how can i do that?
i tried like:
case when (s2.SOPNUMBE LIKE 'net%' OR s2.SOPNUMBE LIKE 'inv%') then s2.sopnumbe
when s2.CUSTNMBR LIKE 'amazon%' then s2.custnmbr
end as channel
but i m getting 0rows affected in ouput.. instead of 70rows...
can anyone help me?
thanks.
My company needs to move a 30 Gig SQL Server across the country as soonas possble on July 1. Turns out moving the full db across the networktakes a few hours.I'd like to move a full copy of db a week ahead of time, and then justmove either a differential backup(s) or transaction log with with theweek's new data on July 1.Can anyone suggest the best strategy for doing this? Currently we'redoing a differential backup each hour on the db, and dumping the txnlog each night.Many thanks,Burt
View 1 Replies View RelatedQuick questiom, I need to Import a table with City and State Information
What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?
Or can I just store them in a table with one column?
CityName, State
Thanks for your help/
Hey All,
I am developing a data acquistion system which monitors the amount of energy that a user consumes in different parts of a house and displays the information in real time on their computer screen. I am collecting the data through tranducers attached to the circuit breakers in the breaker box and sending the data to analog-to-digital converter channels in a MCU. I am retrieving the data from the serial port and storing it to a text file. Each line of data in the text file represents three fields which are separated by commas. I will be reading data from multiple data collection boxes so the first field is the unit number, the second fied represents the analog-to-digital converter channel number from each unit, and the third field is the data that is collected from the ATD channel. I am trying to use SSE to retrieve the data from the text file, and parse each line of data into individual columns in a databse. Then I want to be able to extract the data associated with a particular ATD channel number from the databse and display it in the appropriate text field on a windows form.
I've got the MCU programmed. I have no problem collecting the data from the serial port, and I can do the visual basic programming okay. I have absolutely no clue how to read the data into the database, continuosly read new values into the databse, and then access the stored data to update the text fields on the form. Please help if you can, I've been working on this specific problem for a couple of weeks and I'm not making any progress. Thanks.
Very basic question here but I need to add a form to a page so it adds content, its as simple as that. I've done this kind of using a <asp.Formview> tag and it works, but becuase I think I've used a form view I have to click add to insert data first. Should I be using the <asp:formview> tag or is it a problem with the templates?
<asp:FormView ID="FormView1" runat="server" DataKeyNames="ContentID" DataSourceID="ObjectDataSource1"> <InsertItemTemplate> Resource :<asp:DropDownList ID="ResourceIDTextBox" runat="server" Text='<%# Bind("ResourceID") %>' DataSourceID="ObjectDataSource2" DataTextField="Resource" DataValueField="ResourceID"> </asp:DropDownList><br /> Headline: <asp:TextBox ID="HeadlineTextBox" runat="server" Text='<%# Bind("Headline") %>' Width="451px"></asp:TextBox><br /> Date: <asp:TextBox ID="DateTextBox" runat="server" Text='<%# Bind("Date") %>'> </asp:TextBox><br /> Body: <FTB:FreeTextBox id="FreeTextBox1" runat="Server" Text='<%# Bind("Body") %>' Width="542px" /> <br /> ImageURL: <asp:TextBox ID="ImageURLTextBox" runat="server" Text='<%# Bind("ImageURL") %>'> </asp:TextBox><br /> OpeningPara: <asp:TextBox ID="OpeningParaTextBox" runat="server" Text='<%# Bind("OpeningPara") %>'> </asp:TextBox><br /> Ret: <asp:TextBox ID="RetTextBox" runat="server" Text='<%# Bind("Ret") %>'> </asp:TextBox><br /> Man: <asp:TextBox ID="ManTextBox" runat="server" Text='<%# Bind("Man") %>'> </asp:TextBox><br /> Pro: <asp:TextBox ID="ProTextBox" runat="server" Text='<%# Bind("Pro") %>'> </asp:TextBox><br /> Com: <asp:TextBox ID="ComTextBox" runat="server" Text='<%# Bind("Com") %>'> </asp:TextBox><br /> IndustryID: <asp:TextBox ID="IndustryIDTextBox" runat="server" Text='<%# Bind("IndustryID") %>'> </asp:TextBox><br /> ContentTitle: <asp:TextBox ID="ContentTitleTextBox" runat="server" Text='<%# Bind("ContentTitle") %>'> </asp:TextBox><br /> Status: <asp:TextBox ID="StatusTextBox" runat="server" Text='<%# Bind("Status") %>'> </asp:TextBox><br /> Pub: <asp:TextBox ID="PubTextBox" runat="server" Text='<%# Bind("Pub") %>'> </asp:TextBox><br /> Fin: <asp:TextBox ID="FinTextBox" runat="server" Text='<%# Bind("Fin") %>'> </asp:TextBox><br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert"> </asp:LinkButton> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"> </asp:LinkButton> </InsertItemTemplate> <ItemTemplate> <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="Click here to Add New Content"> </asp:LinkButton> </ItemTemplate> </asp:FormView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="ContentADDMethod" TypeName="DataSet2TableAdapters.ContentADDAdapter" UpdateMethod="Update"> <DeleteParameters> <asp:Parameter Name="Original_ContentID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ResourceID" Type="Int32" /> <asp:Parameter Name="Headline" Type="String" /> <asp:Parameter Name="Date" Type="String" /> <asp:Parameter Name="Body" Type="String" /> <asp:Parameter Name="ImageURL" Type="String" /> <asp:Parameter Name="OpeningPara" Type="String" /> <asp:Parameter Name="Ret" Type="String" /> <asp:Parameter Name="Man" Type="String" /> <asp:Parameter Name="Pro" Type="String" /> <asp:Parameter Name="Com" Type="String" /> <asp:Parameter Name="IndustryID" Type="Int32" /> <asp:Parameter Name="ContentTitle" Type="String" /> <asp:Parameter Name="Status" Type="String" /> <asp:Parameter Name="Pub" Type="String" /> <asp:Parameter Name="Fin" Type="String" /> <asp:Parameter Name="Original_ContentID" Type="Int32" /> <asp:Parameter Name="ContentID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="ResourceID" Type="Int32" /> <asp:Parameter Name="Headline" Type="String" /> <asp:Parameter Name="Date" Type="String" /> <asp:Parameter Name="Body" Type="String" /> <asp:Parameter Name="ImageURL" Type="String" /> <asp:Parameter Name="OpeningPara" Type="String" /> <asp:Parameter Name="Ret" Type="String" /> <asp:Parameter Name="Man" Type="String" /> <asp:Parameter Name="Pro" Type="String" /> <asp:Parameter Name="Com" Type="String" /> <asp:Parameter Name="IndustryID" Type="Int32" /> <asp:Parameter Name="ContentTitle" Type="String" /> <asp:Parameter Name="Status" Type="String" /> <asp:Parameter Name="Pub" Type="String" /> <asp:Parameter Name="Fin" Type="String" /> </InsertParameters> </asp:ObjectDataSource> <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="DataSet2TableAdapters.ResourcesTableAdapter"> <InsertParameters> <asp:Parameter Name="Resource" Type="String" /> </InsertParameters> </asp:ObjectDataSource>
Hi,I'm new at asp .net and have a problem. I have a page where the logged in user can add another user to his list of friends. To do this, I have a page where all the users are listed by using a datalist, and I have a form with an invisible field which passes the value of <%#DataBinder.Eval(Container.DataItem, "UserName")%> in post when the user clicks on "add as a friend" so that in the next page I can get that value and add it as well as the username of the logged in user to the table friendships. This is not working, can anyone help please? The code of the page with the datalist is:<script language="VB" runat="server"> Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) ' Session("username") = User.Identity.Name Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() End Sub Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select u.*,f.buddyId as userNameIfFriend from aspnet_Users u left outer join aspnet_friendship f ON u.UserId=f.buddyId AND f.userId=@Param1 where IsAnonymous='False' and u.UserId<>@Param1", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True MyCommand.SelectCommand.Parameters.AddWithValue("@Param1", CurrentUser) 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: <form id="teste" name="teste" method="post" action="add_buddy.aspx"> <input name="UserId" type="hidden" id="UserId" value="<%#DataBinder.Eval(Container.DataItem, "UserName")%>" /> </form> <%#IIf(Container.DataItem("userNameIfFriend") Is DBNull.Value, "<a href='add_buddy.aspx'>Add as buddy</a> ", "Already buddy")%> </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList></body></asp:Content>The code of the page where the user is redirected to add a friend and where I want to make the insert is: <script runat="server"> Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() End Sub Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs) Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() ' Declaring variables Dim UserId As String ' A Function to check if some field entered by user is empty ' Receiving values from Form UserId = (Request.Form("UserId")) 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("insert @Param2, @Param1 into aspnet_friendship.userId, aspnet_friendship.buddyId", MyConnection) MyCommand.SelectCommand.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand.SelectCommand.Parameters.AddWithValue("@Param2", UserId) ' Creating Connection Object and opening the database Dim DS As DataSet DS = New DataSet() MyCommand.Fill(DS, "aspnet_friendship") ' Done. Close the connection End Sub </script> I am not sure if sending the information in a form in post to another page and executing the query there is the best option or if I can do it all within
the if statement. I appreciate any help, since I am having difficulties in solving this.Thank you.
I have been successful with DTS packages and various SQL statements. However, I have a new challenge. I have a table in an SQL Server database. One of the columns is employee number and a column for department number(which is not populated) In a remote AS400 file I have the employees number and department number. I want to create a package to connect to remote table and update SQL Server table with department number where the two tables match on the employee number.
View 3 Replies View Relatedusing a query or vba ,transfer data from one form to another,
table1 customers-form1 customer address,
table2 orders-form2 order address,
how do i transfer data from customer address to order address
yours M [/center]
I am researching ideas for the best way to have users enter data into SQL.
I am familiar with VB and Access Forms and HTML coding but what I am looking for is a way to have a standalone app or website where users can enter information but I want the form to have branches that change depending on user input (such as selecting a meeting type then having specific questions open up related to it).
Any ideas on the best way to do this?
If a different forum would be better for this request please advise and I will move appropriately.
Respectfully,
Lewis
I'm running a query to show the transfer fees spent by a club with data from two tables :
Code:
SELECT SUM([Transfer Fee]) From [Summer_2001_2011] WHERE [New Club] LIKE 'Manchester City'
Union All
SELECT SUM([Transfer Fee]) From [Winter_2001_2011] WHERE [New Club] LIKE 'Manchester City'
Shows the sum from each table :
Code:
545.01
110.98
What do I need to add to the query to show the total from both tables?
In our project users log in and are assigned a GUID. The GUID is stored as a session variable that is used for filtering what a user sees on a page/report etc.
We have a report in which there are 2 parameters (Drop Downs).
Drop Down 1 lists the Entities a user can see (this is filtered by the GUID that is passed to the backend) and this works fine.
Drop Down 2 lists the products a user can see within Entity (this is filtered by the same GUID and also the selected value from DDL1.)
Here€™s the dilemma, how to we pass 2 variables into DDL2, when one of the variables comes from DDL1, and the other is passed by the URL?
I have built a sample table, query, and results for this question. I am using SQL server.
declare @TableX Table
(
Date Date not null,
ID int not null,
Tick varchar(6) not null
)
INSERT @tableX
[Code] ....
Expected results:
DATE(No column name)count
2013-09-02LON1
2013-09-03LON1
2013-09-04LON1
2013-09-05LON1
2013-09-06LON1
2013-09-02USA2
2013-09-03USA2
2013-09-04USA2
2013-09-05USA2
2013-09-06USA2
I want to select unique country - date pairs. It is not even necessary to have the count of each one, just the list of unique country/dates.
My query here uses 'group by' to accomplish this task, but there may be a way to do this with a self join. I believe using a self join would make the query faster.
1) Is this possible to do with a self join?