.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

sql select query not working

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :ASP.Net

hi guys,

i have been having trouble trying to find out what is wrong with my sql select query... i wanted to select a sessionID by comparing the tourID and dates. the dates datatype at the database is datetime and the value of itemSessionDate is in string. i tried to convert itemSessionDate to date and dateAndTime but it gave me mm/dd/yyyy while the date in my database is dd/mm/yyyy hh:mm:ss. so i thought i can try using the itemSessionDate string as it has the same value as the one return from the database. but it keeps on goin to the if not reader.hasRows and quit the statement

could somebody teach me how to solve this problem....

below are my code

Private Function getItemSessionID(ByVal tourID As String, ByVal itemSessionDate As String)
        Dim sessionID As String = ""

        Dim strConn As String = ConfigurationManager.ConnectionStrings("ProrsumDBConnectionString").ConnectionString
        'create sql connection
        Dim myConn As New SqlConnection(strConn)
        'Open connection; Note: You need to open a connection to database

            Dim cmd As String = "SELECT sessionID " & _
                                " FROM TourSession " & _
                                " WHERE tourID=" & Integer.Pars

View Complete Post

More Related Resource Links

SP Datasource select Command not working for Status Column



    (MOSS 2007-Webuser control Web part)

    I'm having a cutom list that i'm binding using SPDatasource to a gridview. I want to filter the list based on column values. I tried the following code

            SPList taskList = site.Lists["Accounts"];
            SPDataSource mySPDS = new SPDataSource();

         mySPDS.SelectCommand = "<View><Query><Where><Eq><FieldRef Name='Vendor_x0020_Name'/><Value Type='Text'>"+ddlVendorName.SelectedItem.Text +"</Value></Eq></Where></Query></View>";


mySPDS.SelectCommand = "<View><Query><Where><Eq><FieldRef Name='Status'/><Value Type='Choice'>" + ddlStatus.SelectedItem.Text + "</Value></Eq></Where></Query></Vi

On New Item form, select item on choice dropdown based on query result.


I posted this on Experts Exchange awhile back with no answer, so here's hoping someone here has an idea :)

So I've mostly figured this problem out, but I need some help in getting over the hump... note that I'm using JavaScript to do this (SPServices) on SharePoint 07'.

I'm on the New Items page of a list. Based on a user's selection in a "Teacher" dropdown, I'm doing the following:

1.) Get the value of the Teacher field and several other fields (School and Visit Date)
2.) Run a CAML query based on those fields to find the last list item entered for that Teacher

So here's where I'm stuck...

I have 10 choice fields, all with choices 0, 4, 7, and 10. Depending on what they were set to in the last entered item (which I queried), I want to set the selected item on each of those dropdowns to equal that. So "Score 1" was set to "4" on the la

select query


Hi All.

I have 2 table.


ID  | Person_Name

1   |  abc

2   |   def

3   |   ghq


ID  | Product  | ExportPersonID  | ImportedPersonID |  OwnerID

1    |   a         |   1                      |   2                           |  3

2    |  B          |    2                     |   3                           |  1

I want above two table output

ID    Product      |  ExpotedPerson|    ImportedPerson  |  Owner

1          a       &n

condition Select query

Hi All.Greetings.I have 1 table.Table 1ID | Name  | Sex       |  DateofBirth1   | abc    | Female  | 19802   |abc2   | Female  |20083   |abc3   |Male       |19854   |abc4   |Male      | 2009I want to see if age is greater then 3 year it will adult girl or adult boy. and If age is less then 3 year then it will be child girl or child boy.I have Output like thenName | Sex         |abc    | Adult girl |abc2  | child girl  |abc4  | child boy | etc.....How to write condition query to get my result.Thanks in Advance.

Pivot w/o aggregation...query not working

Hi all--Given this query, I need to make this query into a pivot table: select Personnel_id, First Name, Last Name, E-mail, Division, Region, Country, Manager, Question, Answer, Date Completed from #pivot where Question Answer 01. Received File No 02. Read File Yes 03. Signed File Yes ... Needs to look like this: Personnel_id First Name ... 01. Received File 02. Read File 03. Signed File ... Test01 Test No Yes Yes I have the following pivot query: SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM (SELECT [Personnel_id] ,[First Name] ,[Last Name] ,[Email] ,[Division] ,[Region] ,[Country] ,[Manager] ,[Question] ,[Answer] ,[Date Completed] FROM #pivot) topvt PIVOT (MAX([Personnel_id]) FOR Answer in ([1],[2], [3], [4], [5], [6], [7], [8] , [9], [10])) as pvt ORDER BY pvt.[Personnel_id] I get this error: Msg 207, Level 16, State 1, Line 83 Invalid column name 'Personnel_id'. Any ideas? Thanks!

Select Top X not working

hello,ive a problem with ms access query; im trying to solect top X records and it fails returning > X each time. without the TOP keyword it would filter a lot more records, with TOP keyword it alwasy select more than required; im selecting 4 fields and performing value check on 3 of them, ive noticed that if i limit to different values of TOP it works in some cases while doesnt in others; it seems that it select TOP X records with specific value in the last field grouping the results until it select X records; here is an exaple:id     vi_se  ri_no  coeffaf418    16    27    0.592592592592593de788    18    31    0.580645161290323al452    22    38    0.578947368421053du771    14    25    0.56ns011    15    27    0.555555555555556iv729    15    27    0.555555555555556ge103    15    27    0.555555555555556in this case the effective records are 7 however if you group by coeff they are 5 as requested by the TOP keyword, this is the exact query result, there is no grouping performed on coeff field, in addition in this case the last three valu

Caml query not working

Hi, I'm trying to query a SPListItem from a sharepoint list but it doesn't seem to work:   SPDocumentLibrary myDocuments = (SPDocumentLibrary)site.Lists["Documents"]; SPQuery query = new SPQuery() { Query = " <Where>" + " <Eq>" + " <FieldRef Name='DocumentPath' />" + " <Value Type='Text'>" + source + "</Value>" + " </Eq>" + " </Where>" }; var result = myDocuments.GetItems(query); I get no result, but there's an item with the specified "source" that does exist.


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

Why is the Content by Query Web Part CommonViewFields attribute not working as expected?

I am using the Content by Query Web Part to show data from a Custom List.  I have already added to the XSL "ItemStyle" sheet so that I can pick the correct Item Style.  That has worked fine - I have been able to show whatever fields I would like and apply any CSS Styles I would like to them.  The issue I am having is in formatting the field correctly.  I have two different fields in my current example that need to be formatted: [1] I want to show an Image, but right now it shows the URL  [2] I want to show Rich Text, but right now it shows all the HTML Code as Plain Text.  I have looked at many articles online, and they all tell me to change the following in the WEBPART file from: <property name="CommonViewFields" type="string" /> to: <property name="CommonViewFields" type="string">Thumbnail, Image;Teaser, RichHTML;</property> What I am doing wrong?  The Web Part still runs, but it does not format the fields any differently.  Thanks for the help!

ODBC Select if Statement in MAS90 query

Hi, I am connecting to a MAS90 database via DSN and am having trouble with the query string for ODBC.  I can collect most all fields if I SELECT them directly, however, I want to create a result column based on which cost is higher. As far as I know, this select statement in to and ODBC connection needs to comply with MSSQL I have tried SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile And SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile Neither are working?  What am I missing here? Can I do this sort of if then statement in MSSQL?   Thanks for your help 

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks

Working on a SELECT Output

Hi Please can anyone help me. I've just joined two table together. Next I would like to output the SUM of one of the columns. I am almost a complete beginner so please could anyone give me some pointers on how to do this. Thank you Matthew

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Select and update in same query

Hi all, I need to update the selected rows from a select statement and update it using an update query. For that the query i am using is UPDATE SERVERACCOUNTSTRAN..TRAN_JOURNALDET SET CONTRATRANSACTIONHEADCODE=T.CODE FROM ##TRAN TR INNER JOIN SERVERSSRETAIL..TRANSACTIONHEAD T ON T.SHORTNAME=TR.CREDT_ACCT WHERE T.COMPANYCODE=1 AND CONTRATRANSACTIONHEADCODE=0 Now the problem is all the values of contratransactionheadcode field is updated to 18. But the real values are different for each row. only the first row value is 18 which is updated to every other row. Thanks in advance.Sujith Kumar www.sujithkumar.tk

Error in select query

Hi, I am tring to use the following command but getting the following error message --Query Select * from CTOR_DBPF\Amord_wrk\dbo.rate2010 Server:   CTOR_DBPF Database: Amord_wrk Table:    rate2010 --Error message Line 1: Incorrect syntax near '\'.

CAML query on external list is not working

Hi, I have an external list, display data from SQLServer 2008. Table on SQL have an UniqueIdentifire (GUID) type column as a Primary key. It also has one identifier column, Its value is increased by 1, as the item is created. In share point server side code, I want to fetch single record against the primary key GUID value. I write down the CAML query on GUID column and run without any error. But all the external list data is returned instead of Single list item. I also try to filter on Auto Incremented column, but same problem is occurred.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend