Hi,
I am having a problem updating one field in a table the update should be the product of two other fields from the same row.
There are atleast 3000+ records need to be updated here.
e.g.
update A
set A.b = A.c * A.D
here b c and d are from same row .. I was wondering if someone knows how to solve this problem.
Thanks in advance.
I have a stored procedure that I give some parameters and it joins a couple of tables and returns the results. I would like to know how to update the records in one of the original tables before returning results.
CREATE Procedure dbo.DS_GetSomething(@someValue char(5)AS
Select table1.* ,table2.* FROM table1 left inner join table1.itemid on table2.itemid where table2.someValue = @someValue
The table has a field of IMPRESSIONS that I would like to do something like:
;UPDATE table1 set impressions=impressions + 1 where recordid=??
I don't know how to access the recordids I just got from the original select statement.
Thanks for any help
Greg
Hello,
I have an external process that polls message rows from a table. apon taking them, it also needs to mark them as taken. there is a status column in the table. marking messages as taken will change the value of status.
How can i perform both these operations in one command? Select the top x rows where their status is equal 1, then update the status of those same rows to a value of 5 for example.
I could iterate through the result of hte intial select and change the status 1 by 1 using a cursor, but this seems like a slow option.
Any other ideas?
Thank you.
Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
View 4 Replies View RelatedUsing SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
Dear readers,
On my page people can opload foto's with a <asp:FileUpload ID="myfile" runat="server" BorderWidth="3px" BorderColor="Silver" BorderStyle="Inset" />
and stored the filePath in the database
Now i want, that when there is no picture selected, the path to Noimage.jpg picture who is in my image map will stored on my datadbase.
my script start like this If Not (myfile.HasFile) Then
////how can i select a standard image from
my imageMap and store path to database /////
Dim cnn As Data.SqlClient.SqlConnection
Dim cmd As Data.SqlClient.SqlCommand
Dim strSQL As String
Dim connString As String = (ConfigurationManager.ConnectionStrings("Personal").ConnectionString)
strSQL = "Insert Into tblMateriaal(ArtikelGroep,Artikelnaam,ArtikelType,ArtikelMaat,Aantal,Prijs,ContactPersoon,EmailAdress,Aanvul) Values(@ArtikelGroep,@ArtikelNaam,@ArtikelType,@ArtikelMaat,@Aantal,@Prijs,@ContactPersoon,@EmailAdress,@Aanvul)"
cnn = New Data.SqlClient.SqlConnection(connString)
cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)
Dim plaatje As New Data.SqlClient.SqlParameter("@plaatje", System.Data.SqlDbType.NVarChar)
plaatje.Value = " "
cmd.Parameters.Add(plaatje)
Dim ArtikelGroep As New Data.SqlClient.SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar)
ArtikelGroep.Value = ddl1.SelectedValue
cmd.Parameters.Add(ArtikelGroep)
Dim ArtikelNaam As New Data.SqlClient.SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar)
ArtikelNaam.Value = tb1.Text
cmd.Parameters.Add(ArtikelNaam)
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
End if Lots of thanks
HI Guys, I have a question.
I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).
Current Statement:
And tblRetailer_Contact.faxcontact LIKE *
I substituted:
And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’
This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?
DECLARE @FaxContact as varchar (50)
SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5)
SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5)
SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)
SELECT tblEData.Timestamp As [TimeStamp],
LTRIM(RTrim([ResultsCustName])) AS CustName,
LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status],
FaxContact AS FaxContact,
ResultsPKey As ResultsKey
INTO tmpE_Callbacks
FROM tblEData
LEFT JOIN tblContact
ON tblEData.RetailerPrefix = tblContact.Prefix
WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date
AND FaxContact Like '%@FaxContact%'
Thanx so much,
Trudye
I am new to writing sprocs so forgive me if this is trivial. I am selecting fields from a table and placing them into a temp table in row format. (Row 1 in temp table is the first row in a file that will be created using DTS package). My question is: How can a format a field that I have selected that only has, say 3 chars, into a value of 5.
Ex: field in DB = aaa
I need to format it as: 2 spaces + aaa
But the length of the value will be varying from record to record.
I need to get the Bacup of my SQl 2000 database
but i need only last 100 records of all tables in my database.
or
i want to create new database from existing , schama is same but i need to import only 1000 record from all tables
How would I reference the selected value of a dropdown parameter in SSRS using VS2005?
For instance, the city Miami is selected I want to find out if both dropdowns match eachother...
iif(dropdown1.selected.value = dropdown2.selected.value,false,true)
How can I get an Id of selected record? something like
Code Snippet
DECLARE @Id uniqueidentifier
SELECT @Id = ID FROM DataTable
BUT! I need also retrieve a data in the same query. Is that possible or there is a different way? Thanks.
I have to dts rows by timestamp. For example if my dts downloaded at 10 am then in the next run i want to grab rows updated in the AS400 after 10am. what is the best way to go in ssis?
thanks...
kushpaw
Hello,
I've gotton both the sprocs in these tutorials to work in the C# app:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/4503.aspx
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/2271.aspx
But when I try to call them in the Managment Studio I get the following error:
"No cube specified. A cube must be specified for this command to work."
What am I missing here?
Thanks in advance,
Adam
Hello all! How can I get data from SqlDataSource for row selected in GridView?
View 3 Replies View RelatedHi. With VWD i've produced the following code.<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @liedID)"><SelectParameters><asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue" Type="Int16" />But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?Thanks, Kin Wei.
View 2 Replies View RelatedHi I have a problem that seems to be stupid… but still I cant solve it so please if you can help ASAP I'll appreciated.
Its 2 tables "Courses" and "Facilitators"
The Course table contain: Course_Id and Course_Name
The Facilitators table contain: Facili_Id and Facili_Name
The two tables have (many to many) relation between them.
So I split them into a 3rd table called "Trans" which contain the PK from each Table
That will be : "Trans_Course_Id" and "Trans_Facili_Id"
I put some data on each table… at least 3 records and related some of them in the "Trans" table
Now im looking for an SQL command that brings me All the "Facili_ID" and "Facili_Name" for a specific course. And only those who is not already selected by the same course?
Like if I have the data in the "Courses" tableid: 1 and Name: VB
Id:2 and Name: C#
And in "Facilitators" table:
Id:1 and Name: Adam
Id:2 And Name: George
Id:3 and Name: Sam
Now in the relation table "Trans"
Course_Id:1 and Facili_Id:1
Course_Id:2 and Facili_Id:1
Course_Id:2 and Facili_Id:3
Now I want the SQL Commands that brings me the he "Facili_ID" and "Facili_Name"
For Course_id "For example" and should not be selected by the same course…
That would be:
Id:2 And Name: George
Id:3 and Name: Sam
And the same for eash time I pass the course_id for the command
Thank you.
I know how to get the events that start say on May, and I know how to get the events that end on May, however, How would I get the events that start on January and end in July. The month of May should display that event too.
so far, as an example, I have: SELECT
Events.startDate,
Events.endDate
FROM Events
WHERE
Events.Active = 1
AND
startDate BETWEEN convert(smalldatetime, '5/1/2006') AND convert(smalldatetime, '5/31/2006')
ORDER BY Events.startDate ASC; thank in advance.
Hi every body,
I am making a program which is currently dealing with thousands of records.
What I want is to have a button in the front END which make me able to fetch only first 100 record.If the desired record doesn't come with in first 100 record I press this button another time to fetch next 100 records i.e. from 101 to 199.
How can it be possible in SQL Server stored procedure.
Eagerly waiting from all of you GENIUS people.
Kailash
Hi !
Is there any function for counting each row selected.
For example if I do "select * from employers order by salary"
and get:
10 Matthew Norton 5000
15 Ben King 4000
13 Caroline Stewart 2500
97 Joe Langkow 1300
How can I rank this so that I can get a number like that Caroline has got the third best salary in the company ?
Ok..............so I'm totally new to this whole SQL thing and I need some help..........please!
I have a table rf_log with the following fields:
PACKSLIP varchar(20)
BINLABEL varchar(8)
EXTENDED varchar(50)
TERMID smallint
USERID varchar(8)
ACTION varchar(8)
QUANTITY int
Q_SCALER smallint
REFERENCE2 varchar(30)
REFERENCE3 varchar(30)
DATE_TIME varchar(23)
DATE_CREAT timestamp(8)
LOCATION varchar(20)
TOTLABEL varchar(20)
CLIENTNAME varchar(15)
PO_NUM varchar(25)
SERIAL varchar(25)
LICENSE_PLATE varchar(22)
What I need to do is to get a list of packslips where the packslip number will BEGIN with the user selected parameter.
For example if the user input is 'ORD000888' then I could possibly get a return of:
Ord000888-0
Ord000888-1
Ord000888-2...............and so on.
I have tried this but unsuccesfully:
SELECT PACKSLIP,BINLABEL,EXTENDED,TERMID,USERID,QUANTITY,Q_SCALER,TOTLABEL,REFERENCE2,REFERENCE3,DATE_TIME, DATE_CREAT,CLIENTNAME,PO_NUM,SERIAL,LOCATION,LICENSE_PLATE
FROM rf_log
WHERE PACKSLIP LIKE ?C
I have also tried:
DECLARE @Pickslip varchar(30)
SET @Pickslip = ?C
SELECT PACKSLIP
FROM rf_log
WHERE PACKSLIP LIKE @Pickslip
None of these worked. Any help would be greatly appreciated.
Hi all,
I have the situation below. I can't use dual. Thank you for your time.
Example Query:
select name
from employee
where id=1;
Result:
0 rows selected
Wanted Result:
1 row selected
I want to display the data in a datasheetview in ms access project.
The data is between particular date. What command should i used for this?
My form contailn feilds : UserId,FromDate,ToDate
Enter the userid & Particular date & after click on submit data for that period should display in datasheet view.
Please help me in this as soon as possible.
Hi,
Wondering if anyone can help with this..
I want to INSERT some data already SELECTED in a query and also some other variables.. Will the **dodgy code** make it easier to understand I wonder..
** DODGY CODE **
INSERT INTO tblTableOne(Column1, Column2, Column3, Column4)
VALUES( ( SELECT Column1, Column2 FROM tblTableTwo ), "Text for Column3", "And text for column4")
** END OF DODGY CODE **
I understand it may have something to do with TEMP TABLES perhaps??
Any help greatly appreciated..
KingRoon
Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.
The following test seems to contradict documented behavior....
1) Running SQL Server 2005 Express with Shared Memory (SM) and TCP enabled connect via ADO.
2) Connect with "localhost", it connects using SM.
3) Disable SM and restart the Server Service.
4) Connect with "localhost", it connects using TCP.
5) Enable SM and restart the Server Service.
6) Connect with "localhost", it still connects using TCP!!
To restore a SM connection, the only way that I have figure out is...
7) Disable TCP and restart the Server Service.
8) Connect with "localhost", it connects using SM.
And then restoring TCP does not change the connection
9) Enable TCP and restart the Server Service.
10) Connect with "localhost", it still connects using SM.
If you run steps 1 - 6 but skip #4, step #6 will connect with SM. So one concludes that the TCP connection is somehow "sticky".
Any ideas on how to force a shared memory connection in step #6?
Hi
In the page load of my webpage I call a databind for a gridview.
It generally calls this event handler :
protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
However sometimes (seemingly randomly) it doesn't.
Any ideas?
Thanks
p
protected void Page_Load(object sender, EventArgs e)
{
...
if (searchText != "")
SqlDataSource1.SelectParameters["search"].DefaultValue = searchText;
else{
SqlDataSource1.SelectParameters["search"].DefaultValue = "";
GridView1.DataBind();
}
How can i get ALL the selected items into the database? this loop only accepts one item. I have tried with a "clear" action, does not work.... protected void Button2_Click(object sender, EventArgs e) { if (Page.IsValid) { // Define data objects SqlConnection conn; SqlCommand comm; // Open the connection string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; // Initialize connection conn = new SqlConnection(connectionString); // Create command comm = new SqlCommand("INSERT INTO TestTabel (TestNavn) VALUES (@TestNavn)",conn); // Add command parameters foreach (ListItem item in TestListBox.Items) { if (item.Selected) { comm.Parameters.Add("@TestNavn", System.Data.SqlDbType.NVarChar); comm.Parameters["@TestNavn"].Value = Item.Text; } } // Enclose database code in Try-Catch-Finally try { // Open the connection conn.Open(); // Execute the command comm.ExecuteNonQuery(); // Reload page if the query executed successfully Response.Redirect("Default.aspx"); } catch(Exception Arg) { Response.Write(Arg.Message); // Display error message Label1.Text = "Error !"; } finally { // Close the connection conn.Close(); } } }
View 1 Replies View RelatedIn a few places in my application I want to execute a procedure for every selected record. The only way that I know how to do this is to use a cursor, as below. This code works perfectly, but everything I read says that one should use set operations rather than cursors wherever possible, as they are much more efficient. So what I really want to do is something like Exec procedure argument [,argument]... where argument in (Selet value from ....)or perhaps Exec procedure (select ...) [,argument]or SELECT (procedure....
all of which are invalid. Is there any syntax that avoids the cursor in: ---Copy all facts (and their details, if any) to the new INDI
Declare ccopyIndi cursor for --Step through facts
Select Factid from gdbfact
where factindiid = @IndiidFrom
Open ccopyIndi
FETCH Next from ccopyIndi into @Factid
while @@Fetch_status = 0
Begin
exec dbo.gdbcopyfact @NewIndiid, @Factid
Fetch next from ccopyIndi into @Factid
End
CLOSE ccopyIndi
DEALLOCATE ccopyIndi
(BTW, dbo.gdbcopyfact is NOT a simple INSERT statement)
Can this be done?
I have a procedure where I have Values and Selected Table items that have to be inserted into a nother table that require them to be stored in the same record to the data correctly placed?
Using Sql server 2005, SQLdatasource, I need to display total rows count. But the selected event is not fired? Am I mssing something or doing something wrong? Please comment. thanks
Code<asp:SqlDataSource ID="DataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:AuditToolConnection%>"
ProviderName="System.Data.SqlClient"SelectCommandType="StoredProcedure"
SelectCommand="usp_Dashboard_GetAll" >
--------------------------------
public in recordCount = 0;protected void DataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
recordCount = e.AffectedRows;
lblCount.Text += recordCount.ToString();
}
I get the rows(records) I want by removing the RequestDateTime column from the Select query SELECT distinct et.configname as 'Part Number', substring(email,(charindex('@',email)+1),len(email)) as 'email ending',u.company_name as 'Company',u.city as 'City',u.country as 'Country' --,RequestDateTime as 'Date Downloaded'from new_products.dbo.modelrequests etleft outer join cc_host.dbo.usr_list u on et.username = convert(varchar(50),u.[user_id])where interfacename like '%download%'and result=0and u.country = 'JP'and CONVERT(DATETIME, RequestDateTime, 102)>= convert(DATETIME,'12/5/2007', 102)GROUP BY email,et.configname,u.company_name,u.city,u.country,requestdatetime I want to show the RequestDateTime for the 1600 records that do get selected. How can I put RequestDateTime back in the resulting table output without it being part of the Distinct clause?
View 3 Replies View RelatedI have seen some information on this but have not understood fully the answers. I am fairly new to this and have spent the afternoon trying to work on this and although I have leant some really cool things I am no nearer. How do I get selected values from a LIstbox into a SQL statement using the IN command
First I loop through a List box control to get the multi selected values: Dim li As ListItemFor Each li In lbPClass.Items If li.Selected = True Then strPC += li.Value & ", " End IfNextLabel5.Text = strPC The result in the lable looks something like: 100, 101, 102 , I get rid of the trailing blank and commar with:Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) I have created a parameter called @PROPCLASS that will use the values from the selected values in the listbox in a SQL query using an IN statement: Cmd.Parameters.Add(New OleDbParameter("@PROPCLASS", Label6.Text)) strSQL = "SELECT * FROM web_transfer WHERE ( PROP_CLASS IN (@PROPCLASS)) AND (ACRES >= @lowSize)AND (ACRES < @HighSize) AND (YEAR = @lowYear)AND (SALE_PRICE > @lowPrice) AND (SALE_PRICE < @highPrice) "
It is really the first bit of the WHERE clause I am interested in. The sql statement works if I either type in the actual values IN( 100, 101,102) or if I select only one value in the list box. If I select two then the sql statement does not work.
My question is: How do I get the values selected in the List box into my SQL statement Thank you in advance, s
Hi i am trying to enter more than one value into my constructor field in my database, at the moment when the values are entered into the database through the front end, they appear in the database as seperate records however they have the same ID, the only thing that changes is are the data in the fields which i want in just one. This is the part of the stored procedure I am sure is causing this to happen;
IF @Access_Right_ID=8 or @Access_Right_ID=3 or @Access_Right_ID=9 BEGIN DECLARE @Delimiter char(1) SET @Delimiter = ',' DECLARE @CommaPos int SET @CommaPos = Charindex(@Delimiter,@Constructor_IDs) DECLARE @Remaining_Constructor_IDs VARCHAR(8000) SET @Remaining_Constructor_IDs = @Constructor_IDs DECLARE @Constructor_ID int WHILE (@CommaPos>=0) BEGIN
SET @CommaPos = Charindex(@Delimiter,@Remaining_Constructor_IDs) if @CommaPos >0 SET @Constructor_ID = ltrim(rtrim(Substring(@Remaining_Constructor_IDs,1,@CommaPos-1))) else SET @Constructor_ID = @Remaining_Constructor_IDs INSERT INTO tblUser_Constructor ( User_ID, Constructor_ID) VALUES (@User_ID, @Constructor_ID ) SET @Remaining_Constructor_IDs = SUBSTRING(@Remaining_Constructor_IDs,@CommaPos+1,LEN(@Remaining_Constructor_IDs)-@CommaPos) if @CommaPos=0 set @CommaPos = -1 END