I have this select statement which joins two tables and produces one column as its result. What I'm figuring out is how to add this data into another table as a new column. The output of the select statement and the table I'm going to transfer the data to has the same number of rows. Should I put it first in a temporary table? This column shouldn't allow nulls also.
View 6 RepliesI have a select statement and want to be able to insert some spaces in the returned data:
[Code]....
For the parts with ' - ', I want to remove the dash and simply put spaces now. But when I remove the dash and put 4 spaces, I only get one space. How do I put in those 4 spaces?
have a very important issue,i have three Stored Procedures Sp1,Sp2 and Sp3 .the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:INSERT EXEC statement cannot be nested I tried to change the place of execute Sp2 and it display me another error:Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
View 4 RepliesI have a statement like this;
insert destinationtable(col1,col2,col3)
select col,col2,col3
from sourcetable t1
where not exists(select * from destinationtable t2 where t1.col1 = t2.col1)
I would like to insert into destinationtable which is MS SQL and the sourcetable is Oracle. Is there a way to do this?
How would you handle an INSERT that gets some values from another table using SELECT, where you wanted some values to come from the source table record and other values to come from a different parameter source like a session value? Is this possible?
View 3 RepliesI am wondering there is no one asking this question on the internet even I have used google. (May be I am using a strange way on this case)
Here is the insert statement with select statement
Insert into a_table (ref, user, pw) values ((Select max(ref)+1 from reference_table), txtUser.text, txtPw.text);
Ignore the sql injection first, this statement is incorrect. Any idea? thx.
sql_select = "SELECT auid FROM tbl_auAccount WHERE username='" & IUN & "'"
'Add Error Trapping Later
con = Server.CreateObject("ADODB.Connection")
con.Open(data_source)
'Execute the SQL Command
con.Execute(sql_select)
'Close the connection
con.Close()
con = Nothing
Now this works and I was able to put the value into a dropdown list box, but I need the value returned placed into a label such as:
lblShowNewUserID.Text = ? (? being the value returned from the Query). I thought this would be simple, but it is not (at least for a newbie like me). NOTE: (I will have instructions before this to be sure that only one value will be returned and taht the value will not be null).
i'm trying to to create a simple string array with data retrieved from a select statement(SELECT firstname FROM customers , for ex.)
View 6 RepliesI'm trying to pull a list of clients that have both of two codes in the code table. Here is what I have tried and it does not return any rows. I know that there are clients that have both codes.
[Code]....
I have a Winform that has fields need to be filled by a user. All the fields doesn't belong to one table, the data will go to Customer table and CustomerPhone table, so i decided to do multiple inserts. I will insert appropriate data to CustomerPhone first then Insert the rest data to Customer table.
View 3 Repliesi am new to asp.net programming i am trying to insert data into access db from asp.net web form but it is showing error as
Syntax error in INSERT INTO statement.
my code is :
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
I have to pull up a list of people by their branch number which could vary, and give a detailed report on there performance using a select queries. I have the select queries but how can i create a script to look similar to this one. Ive got the branch drop downlist already created just need to know how i can go about pulling all the users in this group and doing this select to all of them. someone told me I might need to use cursors to loop thru a select with every person in the list but im not sure how to go about that.
select fldusername from tbluser where fldio='o' and
flduserbranch=@branchid
is going to give me the names of the people i need to query. but how can i run a query on each of them seperate?
Here is the deal. I need to create a Stored Procedure that has 2 SELECT statements. Now in ASP.NET, I know that a DataSet can have multiple tables and the DataAdapter can do just that. I am stuck with a scenario where I need to get a value from the first SELECT statement which will later be used in the second SELECT statement. I am creating a SP that passes one parameter (@AnswerID). Which should give me 2 resultsets. See below SELECT statements.
[Code]....
So how do I get the QuestionID from the first SELECT statement?
Basically I am creating a Questionnaire that has Question Dependacies. So from the above SQL statements I would like to display the Question Title with it's relevant answer options but depending on the previous Answer selected hence the @AnswerID parameter.
The reason why I want to use 2 SELECT statements is because I don't want to have 2 roundtrips to th server. So in my code the DataAdapter should return 2 resultsets and fill the DataSet.
If there is any another solution that can prevent 2 roundtrips to the server, I would most definitely like to know how to do it.
Here's my SQL Statement
[Code]....
Unfortunately, I get the following error message:Message="The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."
I have a select statement:
cmd = new SqlCommand("select lab_key from tblorderwhere", connection);
I would like to add a where statement that pass variable to the select statement. How I can do this?
Where lab_key =ChargeFine( that is my variable)
I am using join to get values from three tables. I want the first column to be Sl.No which autoincrements by 1.What should i write in the select for the first column.
View 4 RepliesIs it possible to sum to clolumns in one select statement?
[Code]....
[Code]....
ID sUM(aMOUNTpAID) sUM(aMOUNTdUE)
1234 506.64 4340.00
The actual rows look like this:
1234 10.00 5.00
1234 200.00 100.00
1234 130.00 130.00
1234 166.64 1000.00
totals 506.64 1235.00
i have the table like
TId DG rating DGMake DGCapacity
1 1 4 DG1 1X1
1 2 5 DG2 2X2
1 3 3 DG3 3X3
i need the output like this.
TId DG1 DG2 DG3 DG1Rating DG2Rating DG3Rating DG1Make DG2Make DG3Make DG1Capacity DG2Capaciy DG3Capacity
1 1 2 3 4 5 3 DG1 DG2 DG3 1X1 2X2 3X3
this might be an easy one, but I just can't get it.I am creating a page which will query a table with many columns and most items are not unique. I need to be able to get a list of records that match as many of the (up to 4) search criteria as possible.Example:am user searching for the following items, I enter at least one and up to 4 of the items below in a text box:Name, age, gender, weight (user may or may not fill in all of them).If he just enters "F" for gender, then he will get a list of thousands of females with their name, age, gender and weight.However if he enters "F" for gender and "300" for weight, he will get a much smaller list of returned records.I need to be able to create a sql statement that can perform that search with that functionality.
View 6 Replieshow can i use Distinct in my sql statement like that
SELECT
Distinct * from my_table ?? ? ? ?? ? ? ?
here is a sql select statement,
Select
ColA = ID
,ColB = Name
,ColC = invtotamt + (SELECT SUM(TableA.camount) FROM TableA WHERE TableA.csucceed = 1 AND TableA.cID = InventoryTotal.invID GROUP BY TableA.cID)
,ColD = ((SELECT SUM(TableA.camount) FROM TableA WHERE TableA.csucceed = 1 AND TableA.cID = InventoryTotal.invID GROUP BY TableA.cID)
,ColE = (SELECT SUM(TableB.camount) FROM TableB WHERE TableB.csucceed = 1 AND TableB.cID = InventoryTotal.invID GROUP BY TableB.cID)
FROM TABLE
it look complicated, and long statement, actually it's easy, it is like: (it's a sample, not a really one)
Select
ColA = ID
,ColB = Name
,ColC = ColD + ColE
,ColD = ColE + ColC
,ColE = ColC + ColD/100
FROM TABLE
it looks much easier with ALIAS. But, it seems i cannot directly use alias in select stetement like that.
if i cannot use alise, i have to copy paste lots of same column definition. how can i use the alias in select statement?? this bothers me a lots of times!
I used an function in select statement. the select return 3 rows but the function return same value in this 3 rows.
[Code]....
I have the following code to For each Item in the file uploader, Insert Into database.... but It doesnt work in the For Each Statement but if i take it out of the For Each statement it works.
For Each item As AttachmentItem In Attachments1.Items
Dim objConn2 As New SqlConnection("Data Source=XXX")
objConn2.Open()
Dim objCmd2 As New SqlCommand("INSERT INTO clientport (name, event, date1, username, password, path)" & "VALUES (@name, @event, @date1, @username, @password, @path)", objConn2)
objCmd2.Parameters.AddWithValue("@name", clientname.Text)
objCmd2.Parameters.AddWithValue("@event", [event].Text)
objCmd2.Parameters.AddWithValue("@date1", [date].Text)
objCmd2.Parameters.AddWithValue("@username", username.Text)
objCmd2.Parameters.AddWithValue("@password", password.Text)
objCmd2.Parameters.AddWithValue("@path", item.FileName)
objCmd2.ExecuteNonQuery()
objConn2.Close()
creating a dynamic select statement, so far my SP look like below
[code]....
SELECT u.Email FROM UserProfiles p, aspnet_Membership u WHERE p.RulesCheckBox = 'True' AND u.UserId = p.UserId This gives me a list of registered member email addresses that have selected the RulesCheckBox in their profile, so I can email them with an email script. There is a relationship between the Membership table and the UserProfile table, so it finds the email addresses from the Membership table where that user has the RulesCheckBox checked in their profile (in UserProfiles table). I'm wanting to add some additional names to this list, from a different table. The purpose is so I can manually add email addresses to the additional table, so the additional email addresses will also receive the same email, even though they aren't registered members of the site. So how can I adjust that SELECT statement so this one is combined with it somehow?:
SELECT Email FROM ExtraEmails WHERE Rules = 'true'