Convert A Time Field In The Select Statement Of The Query
Hi,
I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.
Can anyone help me please?
Thx
View Complete Forum Thread with Replies
Related Forum Messages:
Select Statement With Run Time Field Selection
I have this SELECT statement. SELECT [issueID], [name] FROM [MyIssue] What I wanted to do is in addition to the above statement, I want to add two run time fields like this: 99 [issueID],'All Issues' [name] So let's say the above select statements generates this list: Summer 2007 Issue Winter 2007 Issue The two addition fields will make the result list like this: 01 Summer 2007 Issue 02 Winter 2007 Issue 99 All Issues How do I accomplish this? Any help is much appreciated.
View Replies !
Query To Sum The Same Field Twice In The Select Statement
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
View Replies !
How Do I Convert As/400 Time Field Being Seen As Dt_I8 To String
I am struggling getting a time field from as/400 into SQL 200 using SSIS. The time field for some reason is being seen as dt_I8, And every type of conversion I try to do with it, it gives me an error about truncation. Example of my time field 12.35.00 How do you convert time field from as/400 which sql is seeing as DT_I8 to a string so that I can combine it with a date field to put it into a database timestamp field. Stacy
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
Convert Fields In Select Statement
The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
View Replies !
How To Convert SELECT Into UPDATE SQL Statement
Hello! I am trying to write an update t-SQL statement from the following select statement: SELECT EduNextContacts.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduNextContacts.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2) I tried many versions of writing my update statement with no luck. My most recent version is as follows: UPDATE EduNextContacts SET EduNextContacts.overLMLimit = 'Y' FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code WHERE EduNextContacts.ssn = (SELECT DISTINCT EduContactsAuditChanges.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduContactsAuditChanges.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)) And gives the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Can anyone help shed some light on how to make an update statement from my SELECT query above? Thanks in advance, Harry
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
SELECT Field &> 1 Time
hello ! for MS SQL 2000 how can I get something like SELECT name from users WHERE Count(name) > 1 i want to return only the rows where name appears more than 1 time thank you
View Replies !
The Select Statement Is In A Field
Ok, I inherited this database and there is a field that stopres a selectstatement. Is there anyway possible to execute the value of the fieldwithin a select statement?For example:the table:Name "george"lookupForName "Select orders from Ordertable"So maybe something like select name, execute(lookupforname) as ordersSorry, I didn't design this, just inherited :)george
View Replies !
Need Help With SQL Statement (combined SELECT And WHERE At Same Time)
I am building a file repository page. For data source I use SQLDataSource and Repeater to display the categories. I am trying to achieve the result of displaying the following data: List of all "Main Categories" and all "Sub Categories" under each main category name. Also, "Total Number of files" and "Category Description" under each "Main Category". Sample: Human Resources All documents related to new hire process. New Hire Request forms, New Hire forms, Termination Forms Total Files: 25 Programs & Utilities Windows utilities and other tools to keep you safe on the net. Antivirus, Spyware Removal Tools, Other programs Total Files: 37 My SQL2005 DB has 2 tables. 1) FileCategories. Fields(CategoryID, ParentCategoryID, CategoryName, CategoryDescription) 2) Files. (CategoryID, ParentCAtegoryID, FileTitle) My "Main Categories" are the ones that has a NULL in ParentCategoryID field. Because they are the Parents. My each "Sub Category" has it's own ID as well as parentCategoryID. This insures that each Sub Category corresponding to it's parent only. Sample: Human Resources. CategoryID=4, ParentCategoryID=NULL New Hire Request forms. CategoryID=13, Parent CategoryID=4 New Hire Forms. CategoryID=17, Parent CategoryID=4 Termination Forms. CategoryID=22, Parent CategoryID=4 Programs & Utilities. CategoryID=6, ParentCategoryID=NULL Antivirus. CategoryID=8, Parent CategoryID=6 SpyWare Removal Tools. CategoryID=26, Parent CategoryID=6 Other Programs. CategoryID=31, Parent CategoryID=6 When I am creating a sql Select statement first I need to choose All "CategoryIDs" where "Parent category" is NULL. Then All "CategoryIDs" where "Parent categories" are not NULL and corresponding to Main Categories. Is it something accomplishable with SQLdataSource or I have to use ObjectDataSource or something else. I'll be on the site all day today (10/29/07) and tomorrow (10/30/07) checking for replies. Thanks in advance and let me know if you have any questions.
View Replies !
Query DB2 From SSRS And Convert Integer To Time
Hi, I am querying DB2 from SSRS. I get an interger back that represents a time like this: HHMMSS (The data type in DB2 is an integer.) I would like to make this representation of the time display a little more friendly. Does anyone have a good idea on how I can change the query or SSRS format to display semi-colons to break up the hours, minutes and seconds? Also, the other issue is that since I don't get leading zeros back....they probably need to be added to this value if the value is not a full 6 characters.
View Replies !
Select Statement With A New Identity Field
Hello, Is it possible to generate a identityfield dynamically upon select, like this: SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC I want the output to be: 1 - Name12 - Name23 - Name3 The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes. Like: SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC I want the output to be: 1 - Name32 - Name23 - Name1 Patrick
View Replies !
Select Statement Eliminate Field Name
Hi I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help. Thanks Regards -Leong
View Replies !
First Time SELECT CASE Statement User
Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears. Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname. I have been advised to use CASE statements, yet I do not know how to use them. Below have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then Dim tkt As FormsAuthenticationTicket Dim cookiestr As String Dim ck As HttpCookie tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _ DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data") cookiestr = FormsAuthentication.Encrypt(tkt) ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr) If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration ck.Path = FormsAuthentication.FormsCookiePath() Response.Cookies.Add(ck) Dim strRedirect As String strRedirect = Request("ReturnURL") If strRedirect <> "" Then Response.Redirect(strRedirect, True) Else strRedirect = "webform1.aspx" Response.Redirect(strRedirect, True) End If Else Response.Redirect("webform3.aspx", True) End If End Sub
View Replies !
How Do I Convert A Unix Date/Time Field To A Date When The The SQL DB Stores That Data As Char 11?
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days). I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss. The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help? Please advise. Thanks. Best. K7
View Replies !
How Do I Call A Select Statement Properly When The Field Is A Yes/no?
Hello, i am pretty new to asp. I am trying to do a select statement for sending an email to everyone who is not an admin. the code is below, i know it must be fairly simple, yet i do not know how to do it. With the code below, I select everyone. I want to know how to do it properly, similar to the second which does not work. Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers", conn) DOES NOT WORK: Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers WHERE Admin = 'N'", conn) Thanks in advance.
View Replies !
SELECT Statement - How To Not Get Column Field Names?
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
View Replies !
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone
Hi all, I have created a report in SSRS 2005 which is being viewed by users from different Time Zones. I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone. For example if the date is August 07, 2007 10:00 AM UTC, then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST. Similarly for other Time Zones it should display the time accordingly. Is this possible in SSRS 2005? Any pointers will be usefull... Thanks in advance sudheer racha.
View Replies !
Need To Set A Field In A Select Statement Equal To Yes Or No If Record Exists In Separate Table
Hey gang, I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query') I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling. Here's my current Query this works fine: Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#') What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note. I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No) Any advice would be greatly appreciated.
View Replies !
Help! - Query On Time Field In SQL Server - How?
Hi,I have an orders database and I need to be able to write a querythat groups sales by:1. Date2. Time of day (both am and pm)I currently have a date/time field named "Submitted" that storesjust the date.However, how do I store the time?I know I can store the date/time in the "Submitted" field Icreated. However, because of the time being stored in that field, Ican't execute an easy query such as:select * from database where submitted = '2/10/05'(reason being, the time portion makes each "Submitted" fieldrecord unique, so just the date won't be recognized)I realize there probably is a much better way to do this in theSQL query language that I am not aware of.Two questions:1. Based on what I'm trying to do -- query based on date and groupby number of sales for each hour of the day (am and pm) -- what is thebest approach to store the time?2. What does the query look like that querys by date and groupsorders by time of day (am and pm)?Thanks for your help.J
View Replies !
In Code Behind, What Is Proper Select Statement Syntax To Retrieve The @BName Field From A Table?
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName) I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER " error is:Line 1: Incorrect syntax near 'V'. 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.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'. Source Error: Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View Replies !
Need Query To Select VarChar Rows That Can Convert To Integer
I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton
View Replies !
Need Help Running Query On Data/time Field
I have a date/time field with a sql format of "datetime" The actualdate/time data format is MM/DD/YYYY^hh:mm:ss:pm or "1/25/200712:00:16 AM" Both the date and time are combined on the same fieldwith a space seperating the two.I need to run a query on this date/time field using the criteria oftoday's date so if I want to search all records with todays date, Ineed to query on this field. I don't need the time, but just today'sdate (MM/DD/YYYY) portion of the field.Thanks,Brian
View Replies !
Query With Time Field In Sql Server Express 2005
Dear All, I have a table in access fldDate fldTime fldEnrolled 12/1/2006 1:39:30 PM Biswa 2/27/2008 10:08:40 PM Tom Now I have converted it in sql server 2005 table calld tblTime. I am using VB.Net 2005 for that My Sql command is for accesing the .mdb i.e. access database pstrFIELDS = "* " pstrFROM = "FROM tblTime " pstrWHERE = "WHERE [fldDateCreated] = #" & DateCreated_In & "# " _ & " AND [fldTimeCreated] = #" & TimeCreated_In & "#" Its running well. But Whenever I am going to retrieve it from SQL Server database its not retriveni any data at all. For Sql My code is pstrWHERE = " WHERE [fldDateCreated] = '" & DateCreated_In & "' " _ & " AND [fldTimeCreated] = '" & TimeCreated_In & "'" SQL Table ======= fldDate fldTime fldEnrolled 12/1/2006 12:00:00 AM 1:39:30 PM 12/30/1899 Biswa 2/27/2008 12:00:00 AM 10:08:40 PM 12/30/1899 Tom ---- Can any body tell me the solution to resolve the prob. Regards, Biswajit
View Replies !
DISTINCT SELECT Query With More Than One Field
Hello, I want to do a DISTINCT SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out. the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key. Thanks in advance
View Replies !
Query/select The First 10 Characters In A Field
I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B. So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi' I know I've done this before but can't find a good reference. It's something like this when calling a query within a WHILE loop: SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8) Am I close? Any help would be appreciated.
View Replies !
Select Statement Query
In my select statement, I return a column for 'datediff' using a CASEquery. I call this column 'Elapsed_days'is there anyway I can use this result later on in the same select? IEI want to refer to 'elasped days' in another CASE query rather thanhave to re-write something which incorporates the original one.Simpler the better - I'm new!Make any sense?Hope so
View Replies !
Select Statement Query
Hi, I have a table called notes, with thousands of rows of "notes" entered by customer services agents. Each row has an account number, date and username columns. An account can have many notes on thetable. How can I select the last two notes on the table left for each account? Trying to use select top 2, but of course it's only giving me the top 2 notes for ALL accounts. Please help. Thanks very much.
View Replies !
Simple Select Query Takes A Very Long Time
I have a table tblCustTrans which contains custid int transid int startdate datetime value int the custid, transid and startid are composite primary key. the table contains more than 10 million records. Now i want to fetch record for select * from tblcusttrans where startdate > = 10/10/2006 10:00:000 and startdate <= 10/10/2006 11:00:000 This statement is taking more than 2 hours to fetch the data. is there a way to fetch the record with less time Regards
View Replies !
Help With A Query- Select A Top Field And Join It With Another Table
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
View Replies !
How To Format A Date Field In Select Query
Is it possible to format the date field create_date (mm/dd/yyyy or mm/dd/yy) I use the following query in stored proc. will be called in the asp.net page for population the datagrid. select id, name, create_date from actionstable; Please help, Thank you.
View Replies !
Can Anyone Tell Me How To Use 'Case' Statement In Select Query In SQL ?
I need to pull one field from one table and one field from another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement??? is this correct ?? its showing me errors Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ??? (case when 'DataSource =1' then 'ABC' then 'BCD' endcase) Where ..... Pls correct me Thanks
View Replies !
Logic Statement Using Select Query
I'd like to make a logic statement, that would take as arguments result of the sql select query. In more details: I would like to create a local Bool variable that would be false if some value is NULL in the table (or select query).Query example:select taskID from Users where Login=@usernameWhich classes/methods should i use to solve this problem? I use SqlDataSource to get access to database and i think i should use something like SqlDataSource.UpdateCommand and SqlDataSource.UpdateParameters but dont know how to build from this a logic statement.Thanks in advance
View Replies !
Select Statement Help:How Do I Query Two Tables
How do I Query two tables and minus the result to be displayed in a gridview. I will appreciate all the help that I can get in this regard. Find below my two select statement 1st Select StatementDim SelectString As String = "SELECT DISTINCT [Course_Code], [Course_Description], [Credit_Hr], [Course_Type], [Course_Method] FROM [MSISCourses] WHERE (([Course_Type] = Core) OR ([Course_Type] = Information Integration Project) "If radBtnView.Checked = True ThenSelectString = SelectString & " OR ([Course_Type] = 'Knowledge')"End IfIf chkGView.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Data Management')"End IfIf chkGView2.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'General')"End IfIf chkGView1.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Electronic Commerce')"End IfIf chkGView3.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Network Administration and Security')"End IfIf chkGView4.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Healthcare Information Systems')"End IfSqlDataSource3.SelectCommand = SelectString 2nd Select Statement"SELECT DISTINCT [Co_Code], [Co_Description], [Cr_Hr], [Co_Type], [Co_Method] FROM [StudentCourses] WHERE ([Co_Code] = StdIDLabel)" my gridview<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="Course_Code" DataSourceID="SqlDataSource3" GridLines="Horizontal"><Columns><asp:BoundField DataField="Course_Code" HeaderText="Course_Code" ReadOnly="True" SortExpression="Course_Code" /> <asp:BoundField DataField="Course_Description" HeaderText="Course_Description" SortExpression="Course_Description" /> <asp:BoundField DataField="Credit_Hr" HeaderText="Credit_Hr" SortExpression="Credit_Hr" /> <asp:BoundField DataField="Course_Type" HeaderText="Course_Type" SortExpression="Course_Type" /> <asp:BoundField DataField="Course_Method" HeaderText="Course_Method" SortExpression="Course_Method" /> </Columns></asp:GridView>
View Replies !
How To Use Select Statement In Insert Query
hi my self avii want to copy data from one table to other table,by giving certaincondition and i want o use insert statement .in this i want to pass somevalue directly and some value from select statement , if i try i ll geterror i.e all column of destination table (i.e in which i want to insertdata) should match with all columns in values column some thing likethis.plz give me some helpful suggetion on this
View Replies !
How Do I Use Select Statement In Update Query
hi myself avii am developing one appliacaion in which i am using vb 6 as front end,adodb as database library and sql sever 7 as backend.i want to update one table for which i required data from other table. andiretrive data from second table by giving some condition. when i get data,then to update first table i need to use do while loop. instead of that iwant to use select statement directly in update query.plz give me some help.following is the my queries and its out putStrSql = ""StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &txtTransactionID.text & ""rsMName.Open StrSql, Conn, adOpenKeysetDo While Not rsMName.EOFStrSql = ""StrSql = "Update StockTable Set Outward=Outward - " &rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'RsAdd.Open StrSql, Conn, adOpenStaticrsMName.MoveNextLooprsMName.Closeout put***main querySelect * From SalesVchMaterialDesc where TransactionID=848do while not loopUpdate StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0loop
View Replies !
Using A CASE Statement Within A Select Query
Hi folks, Hope you are all well. I am using a CASE statement within a SELECT query to sum up values for different customers. SELECT CR_CUST.Customer_Code, 'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END, 'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END FROM CR_CUST INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code WHERE (CR_PROD.Part_Class_Code = 1 OR CR_PROD.Part_Class_Code = 7 OR CR_PROD.Part_Class_Code = 8) GROUP BY CR_CUST.Customer_Code, CR_PROD.Part_Class_Code The above query produces the following results... Customer_Code General_01 General_07 General_08 ------------- ---------------- ---------------- ---------------- 02210 10074.30 .00 .00 02347 7606.49 .00 .00 02210 .00 12618.42 .00 02347 .00 13131.63 .00 02210 .00 .00 4505.44 02347 .00 .00 5018.03 My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so... Customer_Code General_01 General_07 General_08 ------------- ---------------- ---------------- ---------------- 02210 10074.30 12618.42 4505.44 02347 7606.49 13131.63 5018.03 I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table. SELECT Customer_Code, SUM(General_01), SUM(General_07), SUM(General_08) FROM #MyTempTable GROUP BY Customer_Code Thanks in advance, Kev
View Replies !
Can't Query More Than 15 Fields In A Select Statement
An ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this: set rs=server.createobject("ADODB.Recordset") rs.open "select * from mytbl",application("mycon"),1,3 And the error is something like this: "you can't query more than the maximum field "
View Replies !
Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string. I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2))) option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value. With refresh_shipping_sched .ActiveConnection = CurrentProject.Connection .CommandText = "spRefresh_shipping_sched" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue) .Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option) .Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "") .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "") Set rs_refresh_shipping_sched = .Execute End Please help
View Replies !
Select Statement Subcribed To Query Notification
I have noticed something strange on the select statement that you can use on query notification. I have a table that contains a compute column let's call it "Calculate". If I subscribe to query notification: Select ID from table the subscription fails. The column Calculate doesn't use the column ID. Why?? for me it shouldn't be the case Thankx MArina B.
View Replies !
|
TRACKER
