command.CommandText = “SELECT UserName from Users WHERE UserID = “ = userID
Executing this command returns one table with one column with one row. What is the syntax for getting that value into a variable? I can get the information into a dataSet but I can’t get it out. Should I be using a dataSet for this operation?
The rest of the code so far:
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.TableMappings.Add("Table", "Users");
dataSet = new DataSet();
dataAdapter.Fill(dataSet);
Hi, i m pretty new to this forum and c#.net i m doin a project in c#.net I have four values in my datarow array for example DataRow[] cmb; cmb=dsResult.Tables[0].Select("Controls Like 'cmb%'");// Here i m getting four Rows
for(i=0;i<cmb.Length;i++) { cmb[i]=Session["cmb'+i].ToString().Trim()//Here i m getting error;Cannot implicitly convert type 'string' to 'System.Data.DataRow' }
How to assign my session values to them. I want to assign my value stored in the session variable to that array.Is there any way i can do it.Can i convert datarow array to string array! Please can any one help me.
In my BLL I have a method that adds a new row to a table in the database... [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)] public bool AddContact(string firstname, string lastname, string middleinit, bool active, Guid uid, bool newsletter) { ContactsDAL.tblContactsDataTable contacts = new ContactsDAL.tblContactsDataTable(); ContactsDAL.tblContactsRow contact = contacts.NewtblContactsRow(); contact.FirstName = firstname; contact.LastName = lastname; contact.MiddleName = middleinit; contact.Active = active; contact.UserID = uid; contact.Newletter = newsletter; contacts.AddtblContactsRow(contact); int rowsAffected = Adapter.Update(contact); return rowsAffected == 1; } The primary key in this table is a BigInt set as an identity column....How do I capture the value of the primary key that gets created when the new row is added?
Hi,I have a client/server app. that uses a windows service for the server and asp.net web pages for the client side. My server class has 3 methods that Fill, Add a new record and Update a record. The Fill and Add routines work as expected but unfortunately the update request falls at the 1st hurdle.I pass two params to the remote(server) method for the update, one is the unique ID and the other is a string that is the name of the table in the database. See code below. I need the SelectedRow method to return a datarow that will then populate textbox's on another page. When the method is called I get an 'internal system error.....please turn on custom errors in the web.config file on the server for more info.(unfortunately my server is not s web server so I don't have a web.config file!!).Can anyone see anything obvious.Cheers. >>Calling routine:Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadSystem.Threading.Thread.CurrentThread.CurrentCultu re = New CultureInfo("en-GB")hsc = CType(Activator.GetObject(GetType(IHelpSC), _"tcp://192.168.2.3:1234/HelpSC"), IHelpSC)Dim drEdit As DataRowDim intRow As Integer = CInt(Request.QueryString("item"))strDiscipline = Request.QueryString("discipline")drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote methodstrRecord = drEdit.Item(0)txtLogged.Text = drEdit(1)txtEngineer.Text = drEdit.Item(3)End SubRemote Class Function:Public Function SelectedRow(ByVal id As Integer, ByVal discipline As String) As System.Data.DataRow Implements IHelpSC.SelectedRowstrDiscipline = Trim(discipline)Dim cmdSelect As SqlCommand = sqlcnn.CreateCommandDim drResult As DataRowDim strQuery As String = "SELECT * FROM " & strDiscipline & _" WHERE CallID=" & idcmdSelect.CommandType = CommandType.TextcmdSelect.CommandText = strQuerysqlda = New SqlDataAdaptersqlda.SelectCommand = cmdSelectds = New DataSetsqlda.Fill(ds, "Results")drResult = ds.Tables(0).Rows(0)Return drResultEnd Function
I have in SQL Table1 the following values in Field1:
ABC ABc AbC abc aBc abC Select Field1 From Table1 Where Field1 = "AbC"
I get all six records, not just the one record (AbC) that I want to retrieve. How do I tell the select statement to be case sensitive? my asp.net statement is given below DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'")[0]; i m using datarowso i need a helpi have quary but i don;t know how can use DataRow dr1 = ds.Tables["Login"].Select("UserId ='" + txtUserId.Text + "'And Passwords ='" + txtPassword.Text + "'where CONVERT(binary(5),Userid)=CONVERT(binary(5),'" + txtUserId.Text + "')'" )[0]; this give me error "Where condiction need operator"waiting for reply
Hi. I am new to ADO.NET and I can't seem to figure out how to populate a row that is a layer lower than the instantiated row. I am populating and XML file called Users.xml:
and, i am using the below code to access and populate the rows based on the users registration input:
Code:
Dim NewLogin As Data.DataRow = LoginDS.Tables(0).NewRow
I am able to access all the rows that are one layer into the xml file with the following code:
Code:
NewLogin("someNode") = _someNode.Text
but, how do i populate nodes that are "further down" such as "birthDay". Do i have to reinstantiate NewLogin as ...Tables(1).NewRow? I have tried various forms of this and it doesnt work. Suggestions appreciated... thanks.
I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.
a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key of a datarow you had just inserted into the database.
After a long set of trial and error i came up with the following:
- add the tablemappings of a table - call the dataadapte.fillschema method
then after inserting a new row into the database the primary key gets filled in automatically!
now thing is
i was hoping to duplicate this in sql server
but it doesn't seem to work at all
so after i insert a row into my datatable and update it the row is in the database but in vb the datarow primary key is not filled in! anyone have an idea?
prefereabely one that does not resort to stored procedures with return parameters etc
I will attempt to explain my situation more clearly.
I need to get data from a data source using a DataFlow Task (which pushes the DataSet into a Variable) and process the data row by row in a ForNext (ADO Enumerated) Container. I don't want to map the columns into variables because the number of columns and the data types vary considerably and I want to make my package as "generic" as possible.
Is there a way, in Script, to read the current row of the ForEach Container from into an ADO DataRow so that thie names and values of each column can be accessed?
I know how to read the entire DataSet object from a Variable into an ADO DataSet and iterate through the rows in the normal way but this doesn't suit my purpose. It would be really useful if there was a way to do somehing similar with the current DataRow in the ForEach Container.
To explain what I am doing, the idea is to use the Column Names and Values for each row to construct an xml fragement, store it in a string Variable and (in the next step) use the Web Services Task to call a Web Method with the xml fragment (from the Variable) as one of the inputs.
A less attarctive alernative would be to use a Scipt outside a ForEach Container and loop through the rows of teh DataTable as descibed above and perhaps call the Web Service Task from teh Script. The proble is that I don't know how to do this either and it woudl be much "neater" anyway to use the ForEach Container.
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring")) Dim cmdSQL As New SqlCommand(strSQL, myConnection)
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
) ) ) ) ) AS timeType, Sum([2007_hours].Hours) AS SumOfHours from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql): select ID, FName, LName if(SUBSTRING(FirstName, 1, 4)= 'Mike') Begin Replace(FirstNam,'Mike','MikeTest') if(SUBSTRING(LastName, 1, 4)= 'Kong') Begin Replace(LastNam,'Kong,'KongTest') if(SUBSTRING(Address, 1, 4)= '1245') Begin ......... End End
end
Case Statement might be the solution but i could not do it.
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _ & "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0")) buildDD(sql, ddlPerNames)
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT 2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end 3 FROM abc 4 Anyone can help? Thanks a lot.
Hi, i am trying to write a mulitple sql statement. basically i have 5 fields to search:
User ID FirstName LastName Department Site i would like to search the records of the database with any of the fields above, so a user can specify a last name of "smith" and a department of "finance" which would return all the smiths in the finance department. or if a user enters "john" all the johns from any department or site would appear. How would the sql statement go like for this? and could i bind a tableadapter to a datagrid to view the results? Any help would be appreciated. Thank you.
Hello, I have at sp that return a value:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_getLastActivityDate] ( @userid nvarchar(256)
) AS DECLARE @ret nvarchar(256) SET @ret = (SELECT CAST(LastActivityDate AS nvarchar) FROM aspnet_Users WHERE UserName = @userid) RETURN @ret The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @ret? // Tomas
I'm a 3GL guy not familiar with SQL that much. What I try to add a record to shopping cart and assign a line number for each record. If the same item already exist, instead of adding a new line I'd like to add to existing line. I use to following code and I got two lines created even I was only adding the very first first. The following are the code I wrote, would anyone please give some hints. Thx.
ALTER PROCEDURE dbo.AddItemToCart ( @Cart nvarchar(50), @ItmID int, @Qty int, @ItmTyp char ) As
Declare @AllItmCount Int SELECT @AllItmCount = Count(ItmID) FROM ShoppingCart WHERE Cart = @Cart
IF @AllItmCount = 0 /*new shoppong cart*/ INSERT INTO ShoppingCart ( Cart, Qty, ItmID, ItmTyp, Line ) VALUES ( @Cart, @Qty, @ItmID, @ItmTyp, 1)
ELSE /*some item entered */
DECLARE @CountItems int
SELECT @CountItems = Count(ItmID) FROM ShoppingCart WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
IF @CountItems > 0 /* same item entered before then add together */
UPDATE ShoppingCart SET Qty = (@Qty + ShoppingCart.Qty) WHERE ItmID = @ItmID AND Cart = @Cart AND ItmTyp='R'
ELSE /* Find the last line number */
Declare @LastLine Int SELECT @LastLine = Max(Line) FROM ShoppingCart WHERE Cart = @Cart
Hi all, I'm trying to build a simple forum. I want to display a list containing the forum names and last post date and last post author. I can view the date, but can't figure out a way to see the author. Here's the query i use: SELECT ForumID, ForumTitle, ForumDesc, ForumTopics, ForumReplies, (SELECT MAX(AddedDate) FROM (SELECT ForumID, AddedDate, Author FROM dbo.Forums_Topic UNION ALL SELECT ForumId, AddedDate, Author FROM dbo.Forum_Reply) AS dates WHERE (ForumID = dbo.Forums.ForumID)) AS ForumLastPostDateFROM dbo.Forums Any suggestion on how to show the author of the last message as well? Thanks in advance.
I am trying to create a view, but I am having trouble with the syntax (go figure!). It seems that as I reference more and more tables, my result set becomes smaller. For example, I want to return the state for all my customers then everything is fine, but when I want the state plus the site type (home, office, etc.) then I return fewer results. What is would be the correct syntax to use to return all the rows even if some of the columns have NULL?
SELECT TimeZone.LongZone, SiteMain.SiteNumber, SiteType.SiteTypeDescription FROM SiteType INNER JOIN TimeZone ON SiteType.id = TimeZone.id INNER JOIN SiteMain ON TimeZone.id = SiteMain.TimeZone AND SiteType.id = SiteMain.SiteType
I have some problem for construction Sql statement to execute in asp pages. In the table the data is stored in such a way that Worknote Id,IncidentId, SeqNo , Desc1,Desc2. 1111 2004 1 - Test1 Test2 2222 3000 1 - Test1 Test2 2222 3000 2 - Test3 Test3
I want to take the maximum of IncidentId (3000) and concatinate the description (Test1 TEst2 Test3 Test4)
I dono how to construct SQl to fetch records . I tried some way , it gives only the last row.
SELECT iWorkNoteID, SeqNum, vchWorkNote1, vchWorkNote2 from incidentworknote where iWorkNoteID = (Select max(iWorknoteID) from incidentworknote where iIncidentID = " & iIncidentId & ")"
I have a sql database which is not a backed up copy.....just a copy of the dbf file. I have tried everything in the enterprise manager to get the file re-introduced to no avail. I was told that you could execute the DB in the query analyser with the following
execute sp_attach_db @filename
but it does not like the syntax....can anyone please help.....
is it posible to run one sql query to check 20 tables to find a value? Let say i have 20 tables e.g. table1, table2, table3 e.t.c all have a column call import_instance_ID what sql statement do i run to check all tables where import_instance_ID = 2 ?? can someone help me with the syntax pls.
Excuse my ignorance. I have been reading and studying this stuff but still some questions.
Table Datatest Table B ID Q1 Q2 Output ID Field1 Field2 1 Y N 2 Y Y 3 N N
Everytime someone adds a record to Table Datatest I want that ID automatically added to Table B. What is the best way to do that? Is this were I use a trigger and an update or is there an easier way? Also case expressions again. How do I add the results from a case to the same table using a trigger?
Like: 100 for ID 1 223 for ID 2
SELECT ID, CASE WHEN Q1 = 'Yes' THEN 100 ELSE 0 end + CASE WHEN Q2 = 'Yes' THEN 123 ELSE end as Output, FROM Datatest
If I want to see a description of a specific tiem I do this:
select description from items,customer where customer.id = 1 and customer.item1 = items.id
That works fine but how do I dom if I want to have the dexcription on all three items ?: I tried select description from items,customer where customer.id = 1 and customer.item1 = items.id or customer.item2 = items.id or customer.item3 = items.id
Hello members, I have a trouble in retrieve correct query by SQL syntax. Ok I have use
SELECT * FROM tableOne WHERE UserID = 'mugun' AND First_Name LIKE '%tom%' OR Last_Name LIKE '%tom%' ORDER by First_Name ASC
Ok the probem is lets say, we have name TomCruise in our DB in First_NAme and Last_Name column. It return a correct value which is TomCruise. But if we change the syntax like this:
SELECT * FROM tableOne WHERE UserID = 'mugun' AND First_Name LIKE '%tomcruise%' OR Last_Name LIKE '%tomcruise%' ORDER by First_Name ASC
There is no result for the above statement. Why? Please help me . Thanx in advanced.
Here is the scenario: Table A has customer info (name and address)
Table B is a phone log table...a new row is created for each new call. In the table is a Date (when the customer was called) and a column named 'stage' that will be filled out either with the word 'follow up', 'dead', or null.
So the relationship from Table A to B is 1 to many, respectively.
I want to ask the two tables this question: Give me name and address (from Table A) and LATEST date customer was called, but ONLY if the 'stage' column says 'follow up'.
Here is the problem...Dr. Jones has three rows in Table B, his latest call was today, but stage is set to 'dead'. His second line in the table was called yesterday, with the designation of 'follow up'.
When I run my query, I pull out the row from yesterday, since it meets the criteria of 'follow up'....but this is wrong, as I only want the latest designation of 'follow up' IF its the lastest date for this customer.
Here is my syntax: SELECT DISTINCT MAX(tableB.date) AS latestdate, tableA.CONTACT, tableA.ADDRESS1, FROM tableB INNER JOINTableA ON TableB.ACCOUNTNO = TableA.ACCOUNTNO GROUP BY tableA.CONTACT, CONTACT1.ADDRESS1 HAVING (tableb.stage = 'follow-up')
Table A has three columns (aaa1, aaa2, aaa3) Table B has three columns (bbb1, bbb2, bbb3)
Common field is aaa1 = bbb1
I write a query that retrieves 3 rows out of table a (select aaa2, aaa3 from table a where (aaa2 = green) and (aaa3 = yellow)) ---------------------------------------------------------- When I now create a left outer join onto Table b, and add one more additional criteria to the where exisiting ones(aaa2=green,aaa3=yellow) which is bbb2='white', I expect to at least see my original three rows of data (since of my left outer join), but now only receive 2 rows of data, since the third row does not have bbb2 as white.
Im confused...I was expecting to see three rows of data, with the bbb2 column being null for the third row....but instead I only get two rows of data...how can I write my query that will give me what I expected ?
