Determining If A SqlCeDataReader Has Returned Data From A SELECT Statement
Sep 26, 2006
Hi all,
is there no a way to determine if a SqlCeDataReader has managed to return data (rows) from an executed SQL SELECT statement? I ask this because I have the following problem where I first need to determine if data (rows) are returned, if so, cycle through them and get the data out. But if I do the test to determine if data is returned (like I have in my code below) then the Reader.Read() is classed as reading a row, so when I do my 'while (Reader.Read())' this will then only work with data in the 2nd row.
if (Reader.Read()){ while (Reader.Read()) { //Get data from rows }}
I recall having this issue before as there doesn't seem to be a standard way of testing for data returned from a SELECT statement. I hope you can shed some light on this for me.
Thanks
View 8 Replies
ADVERTISEMENT
Aug 22, 2006
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.
Here is my select statement:
SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate -
(SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name,
department.department_name, location.location_name
FROM employee INNER JOIN
request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
department ON employee.emp_department = department.department_id INNER JOIN
location ON department.department_location = location.location_id
GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial,
employee.emp_lname, department.department_name, location.location_name
ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information:
SELECT (SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!
TIA
View 3 Replies
View Related
Mar 30, 2004
hi all
Any day, another question. I love you guys.
I want to select a subset of data from a dataset returned by either another subquery or a union.
e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.
select * from
(
select col1, col2, col3 from table1
union
select col1, col2, col3 from table 2
)
where col1 = 'blah'
in essence oracle treats the data returned by the subquery as a table that it would select from.
how would i do the same in mssql?
thank you
James :)
View 5 Replies
View Related
Jul 27, 2007
Hi,
I have a C# server application which clients can send arbitrary SQL statements to. These can be absolutely anything - creating tables/views, selecting from tables/views, inserts, updates, deletes, you name it.
There are two return parameters from the server method which executes the SQL - a results set containing the data, and a count of the rows that were updated - (either one or the other should be populated depending on the type of command sent to it). To deal with this, what I planned on doing was (pseudocode follows..):
try
{
reader = OpenReaderCursor(statement)
}
catch
{
updatedCount = ExecuteNonQueryCommand(statement)
}
Unfortunately this doesn't really work, as OpenReaderCursor is able to execute Non Queries (eg. UPDATE/INSERT/DELETEs etc) but doesn't give me a row count, and trying the other way round, ExecuteNonQueryCommand is happy to execute SELECT statements, but I can't then of course return a results set as I don't have access to it.
My question then, if you will excuse the waffle above, is "Is there a simple way of determining if a string containing an SQL statement is a query?" - or will I have to come up with some way of dealing with this in my application code?
Please don't slate the design (ideally I would have two methods on the server, one for queries which returns results and the other for nonqueries which returns an updatecount) but there's nothing I can do, this is how it must be done (the interface was defined long long ago)
Thanks for your time, all ideas appreciated
Jim
View 1 Replies
View Related
Jun 20, 2006
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
View 2 Replies
View Related
Nov 6, 2007
SqlCeDataReader.GetSchemaTable Method
Returns a DataTable that describes the column metadata of the SqlCeDataReader
Problem
In its MSDN documentation, the Schema Table it returns dun contain "IsIdentity" column in comparison with SqlDataReader.GetSchemaTable Method.
But when I call SqlCeDataReader.GetSchemaTable in my program, it can return a schema table which contain "IsIdentity" column. However, its value is always False whatever the Data Table column is set to be Identity column. So I still can't know whether there is an identity or not.
How to solve it?
Or there are any methods which help to decide it is an Identity column?
View 3 Replies
View Related
Sep 4, 2007
Hello,
I have a SELECT statement which returns a result, depending on the selection the users make (application written in Visual Basic 2005). I want to export the result of the SELECT statement to a Excel file (.csv, or .txt). How should I proceed?
View 1 Replies
View Related
Mar 28, 2012
I'm using sql 2008 and triying to build a dynamic sql script to split the records 50/50.I know using newid() with order by clause selects randomly but how should I build the select statement to split the data 50/50 so i don't need to run the script manually everytime ?
View 10 Replies
View Related
Apr 17, 2007
Hi,
Quick question: how do I determine programmatically if a data driven subscription is currently running?
More info:
I€™m writing a web application which allows the user to kick off an existing data driven subscription (reporting services 2000), which runs from a table with parameters, paths, etc which the user has populated.
The subscription can take several minutes to run, during which time I need to prevent other users from attempting to run the subscription or alter data on the table driving the subscription.
All I€™ve found in the docs so far is
1. The ActiveState on the subscription.
This seems to have more to do with weather or not it can run than if it is running.
2. The status of the subscription.
This seems to only return €œdone: {0} of {1} with {2} errors€? Parsing this is likely to be too flaky to be acceptable.
I really need to move on this as soon as possible, any help is appreciated.
Thanks
View 3 Replies
View Related
Oct 3, 2007
I am struggling with syntax for an update statement.
Goal: If a RelationID has invoice history then HasHistory=1
UPDATE [Tbl_Relations]
SET [HasHistory]= 1
FROM [Tbl_Relations] r INNER JOIN
(SELECT RelationID
FROM vw_Invoice_history
GROUP BY RelationID
) inv ON r.RelationID= inv.RelationID
Result: Subquery returned more than 1 value
What am I doing wrong?
Thanks in advance,
Frans
View 9 Replies
View Related
Jan 11, 2008
We have some SQL CLR code that was written to go get information from a web service...all works great and the CLR code does what it is supposed to. The problem is, it returns its success or failure status as a string text value (equivalent of a print statement I imagine) instead of as a result set or output parameter that i can check. In the proc I wrote to call the CLR stored proc I do some cleanup that i would like to rollback if the call to the web service that the CLR proc makes fails. Problem is, how can i get there. I have tried every way that I know and I can't seem to get that text into an object (temp table, variable whatever) that i can evaluate. Anyone have any suggestions?
View 2 Replies
View Related
Feb 5, 2015
I have this update statement I am trying to use, to update a table. My problem is if there is no data in the select statement, it still adds number to the columns. How can I have this update statement work to put blank value in if there are no counts?
UPDATE T_AXA_BreakDown_Claims
SET [Claim Count Conm] = t2.[Claim Count Conm]
FROM T_AXA_BreakDown_Claims t1
INNER JOIN
(select
[code]....
View 2 Replies
View Related
Dec 13, 2007
How do you use data from a select statement as inputs for a store procedure?
e.g.
Code Block
Select FirstName, LastName from Student where Grade = 'A'
And I want to use all the FirstName and LastName as inputs for this store procedure
Code Block
Exec StudentOfTheMonth @FirstName = FirstName, @LastName = LastName
Thanks
View 21 Replies
View Related
Aug 25, 2006
I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.
Query is sort of
Select field1,fiel2... from table 1 where id = @id
@id is each ID from lookup
Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.
Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.
View 8 Replies
View Related
Sep 21, 2007
I have a problem....SOMEONE PLEASE HELP!
Here is the setup.
Text Box: User enters in customer transaction number
Button: User clicks button to display information about the customer
Now the database has a lot of unique customer numbers. What I am trying to do is take what the user enters so it can search the database and pull out that customers information. I am having a hard time getting that information from the textbox. Any suggestions! Here is what I have so far.
Private Sub btnViewFlow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewFlow.Click
Try
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.DataSet11 = New links.DataSet1
CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=<14852>;packet size=4096;user id=<userID>;password=<Strong Password>;data source=ZRTPD0WB;p" & _
"ersist security info=False;initial catalog=DTR"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "DTR_Document_Summary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DocumentId", "DocumentId"), New System.Data.Common.DataColumnMapping("PartnerId", "PartnerId"), New System.Data.Common.DataColumnMapping("PartnerName", "PartnerName"), New System.Data.Common.DataColumnMapping("Direction", "Direction"), New System.Data.Common.DataColumnMapping("TranSet", "TranSet")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT DocumentId, PartnerId, PartnerName, Direction, TranSet FROM DTR_Document_S" & _
"ummary "
'WHERE (DTR_Document_Summary.PartnerId = 'txtPartnerId.text.toString')"
'THE STATEMENT ABOVE DOESNT WORK
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
'
'DataSet11
'
Me.DataSet11.DataSetName = "DataSet1"
Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
'Open the connection
SqlConnection1.Open()
TextBox1.Text = "Connection Open"
'Populate DataSet11
SqlDataAdapter1.Fill(DataSet11)
TextBox1.Text = "DataSet11 has been filled!"
'Display Data
DataGrid1.DataBind()
TextBox1.Text = "Here is your requested information"
Catch ex As Exception
TextBox1.Text = ex.Message
End Try
'Close the connection
SqlConnection1.Close()
End Sub
View 6 Replies
View Related
Sep 3, 2007
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 3 Replies
View Related
Apr 28, 2004
I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.
ddave
SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
END
View 7 Replies
View Related
Apr 25, 2006
Gurus.I do not know if it is possible, but here is what I want to do.I want to allow user to page the SQL result, so he could decides toreturn from row 10 to row 20, or row 100 to 200, without returns thewhole resultset. Every time he sends another request, I do not mind tohit the database again, (I do not want to cache the result in themiddle tier server, scalability issue), and I know that I could achievethis with CURSOR, but unfortunately the FOR XML is not allowed in aCURSOR statement .(I know that I could achieve what I want to do by writing custom codein the middle tier, but I just want to see if there is a way to do thison the database side.)Any comments & suggestion is greatly appreciated.Thanks in advance.(I am using SQL2005)John
View 2 Replies
View Related
Apr 27, 2008
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
View 2 Replies
View Related
Mar 28, 2007
I would like to use the value returned from my SqlDataSource SELECT method, in the INSERT method for the same SqlDataSource.
Any ideas how this is done?
View 1 Replies
View Related
Dec 24, 2007
select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1
I need to write that kind of sql to return me single value a and multiple values b on each of lines, like
a b
----------------------------
tom small,big,hugh
But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!
View 4 Replies
View Related
Jan 25, 2006
Hi,
I have a problem that I would like help with.
I have to write an SQL Server 2000 stored procedure that returns rows from a table (a SELECT with an ORDER BY). A front end system calls the stored procedure and displays the returned rows. If there is more than one screen's worth of data (ie more than 20 rows returned from the table) then there is a requirement that the stored procedure only returns the rows for the screen ie, for screen 1 I need to return rows 1-20, for screen2 I need to return rows 21-40, screen 3 = rows 41-60.
The screen number will be passed into the stored procedure so I can work out what rows to return, the only problem is how can I tell SQL Server to only select the required rows (ie, say rows 21 to 40 from the returned rows). I can't see any arguments that allow selective rows to be returned (apart from TOP).
Any ideas??
Regards,
Chris
View 3 Replies
View Related
Jul 16, 2007
What is the syntax for saving a single value returned by a Select statement in a variable? I have tried the following, but its telling me I "Must declare the variable '@TempCount'." which is already declared!
SET @SQL = 'Select @TempCount = count(*) FROM ' + @TblName + ' WHERE ' + .......
exec(@SQL)
View 1 Replies
View Related
Jun 14, 2006
Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like
SELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? (without sending it to page)
Here is it:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path
fileCategories table: (for information)Here is the screenshot of the table (10 kb )http://eai.w2o.ru/screen1.gif
Thank you
Artashes
View 12 Replies
View Related
Jul 31, 2007
How is the best way to check for No rows returned from a Select statement in a Stored Proc? I have see this:
Code Snippet
Declare @res int
set @res = NULL
Select @res = dbKeyColumn
From aTable
If @res is null
then no rows returned.
Surely there is a better way??
Thanks.
View 3 Replies
View Related
Oct 19, 2006
I would like to make a listbox only appear if there are results returned by the SQL select statement. I want this to be assessed on a click event of a button before the listbox is rendered.I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?
View 3 Replies
View Related
Jan 8, 2004
I am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.
I was using in Access:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID"
I have changed this for SQL Server to:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID"
But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.
Is there a particular format for the date I am comparing with?
Am I missing something else in my query?
The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.
Many thanks for any help or comments you can provide.
View 2 Replies
View Related
Jul 30, 2013
I want to show the number of rows returned by a select query in the result.
e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy
If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result
OutPut
0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..
How can i get this number in the result.
View 3 Replies
View Related
Aug 17, 2005
Hi there,
I¡¦ve got a table with the following as well as other info:
User ID
DirectoryTypeID (int)
Region ID (int)
I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query:
subRegionAreaID directoryTypeID
--------------- ---------------
3 1
3 2
3 3
3 9
If need these results to be:
subRegionAreaID directoryTypeID
--------------- ---------------
3 1, 2, 3, 9
Is this possible?
Many Thanks!! ļ
Grazi
View 1 Replies
View Related
Sep 13, 2006
I am working on a company sign out sheet for our Intranet. The application accesses a sql database. I tried using a datareader as well as a dataset to access a small piece of information regarding the group that each employee belongs to in the company. My last attempt was to assign the value to the text property of a text box and evaluate the text from the text box with the Select Case but that did not work either.The application is supposed to generate an email to our receptionist as well as to the group technical assistant responsible for each group according to the value that is passed into the Select Case statement. With the datareader as well as the dataset and even now with creating a text box and accessing the text property, I was able to assign the group value to a variable (I can see the value in the subject of the email), but when the variable is supposed to be evaluated by the select case statement it skips right through all of the cases to case else and uses only the receptionist's address in the email as if it doesn't even see the value of the variable. I have searched for a possible answer to the problem I am but so far have had no luck. Any ideas? I included the part of the code that I am having trouble with: '********************************************************************************************' New DataSet is created to determine the group the employee belongs to so that the appropriate' group technical assistant is emailed when employee signs out'******************************************************************************************** ' declare variablesDim sqlGroup As String ' string variable to store sql statement for datasetDim BLGroup As String ' string variable to store the bl group of the employeeDim emailAddress As String ' string variable to store resulting email address Dim groupDS As DataSet ' dataset variableDim groupAdapter As SqlDataAdapter ' sql data adapter variable' sql statement to access group value from the databasesqlGroup = "SELECT BLGroup FROM BLGroupsView WHERE (RTRIM(fname) + ' ' + LTRIM(lname)='" & employee & "')" ' repopen the connection (leftover from working with the datareader)Connection.Open()' create new SqlDataAdapter applying sql statement and connectiongroupAdapter = New SqlDataAdapter(sqlGroup, Connection)' create new dataset groupDS = New DataSet()' populate the datasetgroupAdapter.Fill(groupDS, "BLGroupsView") ' get the value stored in the BLGroup column (only one row is returned at a time) BLGroup = groupDS!BLGroup' Create GroupTextBox TextBox control.Dim GroupTextBox As New TextBox()' Set options for the UserTextBox TextBox control.GroupTextBox.ID = "GroupTextBox"GroupTextBox.Visible = "False"GroupTextBox.Text = BLGroup' just trying anything with this next line, even when i didn't set this the select case statement did not seem to see the value returned by the variableGroupTextBox.runat = "server" ' use a select case statement to evaluate the value of the group (didn't work when I passed in the BLGroup variable eitherSelect Case GroupTextBox.TextCase "bh"emailAddress = "receptionist@myaddress.com; bhassistant@myaddress.com"Case "env"emailAddress = "receptionist@myaddress.com; envassistant@myaddress.com"Case "sw"emailAddress = "receptionist@myaddress.com; swassistant@myaddress.com"Case "fac"emailAddress = "receptionist@myaddress.com; facassistant@myaddress.com"Case "www"emailAddress = "receptionist@myaddress.com; wwwassistant@myaddress.com"Case ElseemailAddress = "receptionist@myaddress.com"End Select '*************************************************************************************************' set new message objectDim myMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage()'**********************************************************' set message properties'********************************************************** myMessage.From = "me@myaddress.com"myMessage.To = emailAddressmyMessage.Subject = employee & " Signing Out at " & TimeOut.Text & " " & BLGroupmyMessage.Body = employee & " Signing Out at " & TimeOut.Text & " for " & Destination.Text & " will potentially return at " & EstTimeIn.Text'***********************************************************' set smtp server namesmtpMail.SmtpServer = "mailserver1"' send email using smtpsmtpMail.Send(myMessage) It sends the message and returns the BLGroup value everytime but only sends to the receptionist. Thanks for your time!
View 1 Replies
View Related
Dec 13, 2007
Hi all,
I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.
How can i do that.
I am using asp.net vb.net.
View 1 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
