There are two tables
Table_1:
ProductID (Identity Increment) ProductDescription (nvarchar)
Table_2:
ProductID (int)
I have a sql statement and it's like this. This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:
DECLARE @getTheNewProductID int
INSERT INTO Table_1 (ProductDescription) VALUES ('SomeValue') ; SET @getTheNewProductID = SELECT SCOPE_IDENTITY()
INSERT INTO Table_2 (ProductID) VALUES (@getTheNewProductID)
What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2
The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.
How do you do this?
I did try using @@identity which may have stuffed things up??
Hi,I have a shopping cart and before each product is added to the cart it checks to see if the stock control is turned on if so then checks the quantity available.My problem is that when i run the query select [dbStatus] from InventoryControl where [ID]=1 to view the status it is returning the incorrect bit value for dbStatus.If "dbStatus" is 0 in the database it returns 0 which is correct, but if the "dbStatus" is 1 in the db it returns -1!! If i run the query in query analyser the correct values are returned. Any ideas???Thanks in Advance
I have an asp.net 1.1 website that uses sql server 2000 and vb.I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run itin the query analyzer the correct value is returned. Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB
' Obtain Order ID from QueryString Dim OrderID As Integer = CInt(Request.Params("ID"))
' Get the customer ID too Dim myNewCustomerId As Integer = 0 myNewCustomerId = orderHistory.GetOrderCustomer(OrderID)
Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer
' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection)
' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC Dim parameterOrderID As New SqlParameter("@order_id", SqlDbType.Int, 4) parameterOrderID.Value = orderID myCommand.Parameters.Add(parameterOrderID)
Dim parameterOrderCustID As New SqlParameter("@customer_id", SqlDbType.Int, 4) parameterOrderCustID.Value = ParameterDirection.Output myCommand.Parameters.Add(parameterOrderCustID)
'Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close()
' Return the customer_id (obtained as out paramter of SPROC)
If parameterOrderCustID.Value <> 0 Then Return CInt(parameterOrderCustID.Value) Else Return 0 End If
End Function
the stored procdure is CREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID] ( @order_id int, @customer_id int OUTPUT ) AS
/* Return the customer_id from the Orders. */ SELECT @customer_id = customer_id
FROM nlb_Orders
WHERE order_id = @order_id GO
I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2. Any ideas would be appreciated ThanksPete
So; if you read my earlier thread here (http://www.dbforums.com/showthread.php?t=1214353), you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:
SELECT [tblTickets].[id] AS ticketId, [tblEvents].[id] AS eventId, [tblStands].[id] AS standId, [tblAdmissionDates].[id] AS admitDateId, [tblEvents].[event_name], [tblStands].[stand_name], [tblTicketConcessions].[concession_name], [tblMemberships].[membership_name], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblBookingMinQuantities]. AS minBookingQuantity, [tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity, MIN([tblQuotas].[quota]) AS Quota, [B]SUM([tblBasket].[ticket_quantity]) AS History, [tblTickets].[price], [tblTickets].[availability]
FROM [tblTickets] LEFT JOIN [tblEvents]ON [tblEvents].[id] = [tblTickets].[event_id] LEFT JOIN [tblStands]ON [tblStands].[id] = [tblTickets].[stand_id] LEFT JOIN [tblBookingDates]ON [tblBookingDates].[id] = [tblTickets]. LEFT JOIN [tblTicketConcessions]ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id] LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id] LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantitiesON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id] LEFT JOIN [tblAdmissionDates]ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id] LEFT JOIN [tblMemberships]ON [tblMemberships].[id] = [tblTickets].[membership_id] LEFT JOIN [tblQuotas]ON ([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND ([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND ([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND ([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND ([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND ([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL) [B]LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id] LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]
WHERE 1=1 AND ([tblTickets].[ticket_open] = 1) AND (([tblEvents].[id] = @eventId OR @eventId = 0)AND ([tblEvents].[event_open] = 1)) AND (([tblStands].[id] = @standId OR @standId = 0)AND ([tblStands].[stand_open] = 1)) AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)AND ([tblAdmissionDates].[date_open] = 1)) AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0) AND ((getdate() BETWEEN [tblBookingDates]. AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL)) AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL) [B]AND ([tblOrders].[user_id] = @userId OR @userId=0)
GROUP BY [tblTickets].[id], [tblEvents].[id], [tblStands].[id], [tblAdmissionDates].[id], [tblEvents].[event_name], [tblStands].[stand_name], [tblTicketConcessions].[concession_name], [tblMemberships].[membership_name], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblBookingMinQuantities].[booking_quantity], [tblBookingMaxQuantities].[booking_quantity], [tblTickets].[price], [tblTickets].[availability] GO
Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.
Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".
When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?
And how do i get it to return all tickets regardless of whether you've bought them previously or not?
Hi everyone, If I have a table with some indexes on the foriegn keys and these indexes are heavily fragmented (80%+), is it normal for queries to return incorrect results?
For example if I had a table called Customer( CustID, Name) and Orders (OrderID, CustID, Product, Date). Lets say I have a non clustered index on CustID in Orders table, and the clustered indexes are Customer.CustID and Orders.OrderID
If the non clusterd index on Orders.CustID becomes heavily fragmented and I am querying the Orders table with TSQL "SELECT * FROM Orders where CustID = @CustID" I sometimes get missing data or incorrect results. In one case all orders for a particular year were missing, but if I queried using OderID they were returned. Rebuilding the index fixed the problem.
I know the index should be rebuilt or reorganized depending on the fragmentation but if one happened to become this fragmented should it start returning incorrect data?
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)
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.
Stored Procedure:CREATE PROCEDURE [dbo].[GetBanner] @BannerPage nvarchar(50), @MagazineID intASBEGIN SET NOCOUNT ON; DECLARE @BannerID int SELECT TOP 1 @BannerID = BannerID FROM Banners WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID AND StartDate <= GetDate() AND EndDate >=GetDate() ORDER BY Views SELECT BannerID, BannerFileName, AltText, URL, Views FROM Banners WHERE BannerID = @BannerID UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerIDEND OnSelected method: protected void dsBanner_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; Response.Write(RecordCount); } This ALWAYS gives me 0 - despite the fact that the correct banner is pulled from the database, and updated etc. Also, when I execute the procedure (for valid parameter values) in SSMS, I get "1 row(s) Affected". Can anyone tell me what I need to change to get the proper value? Thanks
I have the following sql:SELECT courseModuleCode FROM courses WHERE courseCode like '%' + @prefixText + '%'UNION SELECT courseName FROM courses WHERE courseName like '%' + @prefixText + '%' UNIONSELECT TeacherName FROM courses WHERE TeacherName like '%' + @prefixText + '%' it returns one column, i want to name tha column as "words"how do i do it?thanks
I have a table that COULD be linked to 3 other tables, but if one of the link fields is zero I get nothing back at all. Here is my query: SELECTEvents.*, Agencies.A_NAME AS A_NAME, (Families.F_FNAME + ' ' + Families.F_LNAME) AS F_NAME, (Candidates.C_FNAME + ' ' + Candidates.C_LNAME) AS C_NAME FROM Events INNER JOIN Agencies ON Events.E_AGENCY_ID = Agencies.A_ID INNER JOIN Families ON Events.E_FAMILY_ID = Families.F_ID INNER JOIN Candidates ON Events.E_CANDIDATE_ID = Candidates.C_ID WHEREE_ID = 89
Even if all the inner join id's are zero, I would at least like to get back the event information. Could anyone help me out here, or do I have to do 4 queries?
The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?
---------------------------------------------------------------------------------------------------- CREATE PROCEDURE spUnitsbyUnitID @unitid int AS
INNER JOIN tbl_radios as B ON A.unitid = B.unitid INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid INNER JOIN tbl_contacts as D on A.unitid = D.unitid INNER JOIN tbl_cameras as E on A.unitid = E.unitid
WHERE A.UnitID = @unitID GO -----------------------------------------------------------------------------------------------------
can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
update purchaseorders set orderstatusID = 4 where purchaseorderid in ( select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null )
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
I am getting the below error while trying to run sql query
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.
I am running a query that works just fine however, I would like it to exclude value that are equal to zero.
Basically my query looks at the commission that clients pay over a number of periods.
So it goes like this
T.Client_Code as Client ,SUM(CASE t.Transaction_Date WHEN DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)THEN (ABS (t.transaction_commission) /((fx.Exchange_Bid + fx.Exchange_Ask)/2 )) ELSE 0 END)as Commission_Day
FROM TABLE T JOINING FX TABLE
WHERE fx.Currency = 'USD'
And T.Salesman_Name in ('X''Y'Z)
Group BY T.Client_Code
It works perfectly fine however, we dont transact with our clients everyday so therefore this list will return all of our clients in the database and many will have generate zero commission. I want to keep the query along those lines I just need to insert something that says "ONLY SHOW WHEN Commission is not ZERO.
i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role
My code is:
Code: select top 100 username, (Select b.name from application b inner join userroleapplication c on b.applicationID = c.applicationID ) Application, (select name from role d, userroleapplication gr where gr.roleID = d.roleID and gr.userID = a.userID) role from users a where username in ( 'username' )
i know where is the problem but i cant figure how to fix this
I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query
The table with data is like this,one file maybe belong to many group.
Filename FilegroupID
file1 1 file2 2 file1 3 file1 4 file3 1 file2 5
I need write a query to get the result like this
file1 1,3,4 file2 2,5 file3 1
I try to write a query with subquery but I get error message
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
I use SQL Server 2005.but I need the subquery return data like "1,3,4".
I have a simple update query that looks like this:
Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID
I am receiving 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."Yes, it is all contained in the same table.
Okay, I want to fill a table in one database with some info from an identical table from another database. When I run this query, I get 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. The statement has been terminated."
But the point of the subquery was to in fact return more than one value.
Anyone know how I can null-out the returned values of the PrjMgr field? I have bolded the field in question. Here is the code:
select AgencyId ,ControlNumber ,ISNULL(LLA_AUDITS.PrjMgr,Projects.PrjMgr) as PrjMgr,ISNULL(CASE LEN(datepart(mm, PeriodFinish)) WHEN 1 THEN '0' + CAST(datepart(mm, PeriodFinish) as varchar(2)) ELSE CAST(datepart(mm, PeriodFinish) as varchar(2)) END ,'')As month ,ISNULL(CAST(datepart(dd, PeriodFinish) as varchar), '') As day ,ISNULL(CAST(right(datepart(yy, PeriodFinish),2) as varchar),'') As year ,ISNULL(CAST(left(datepart(yy, PeriodFinish),2) as varchar),'') As century ,custName from LLA_Audits Left Outer Join Projects on controlnumber = prjcode Left Outer Join Customers on Agencyid = CustCode where AgencyId is not null and not(LLA_AUDITS.PrjMgr is null and Projects.PrjMgr is null) and left(controlnumber,2)<>'72' and left(controlnumber,2) <>'13' and (CPAFirmId IN ('0','')or CPAFirmId is null) order by ControlNumber
The code below returns 0 rows. The statement is intended as generic statement that would return all records between a particular start and end date and on each date only between a specific start and end time. It works perfectly if the end date is greater than the start date. Unfortunately, if the start and end dates are equal (i.e, return all records on that one date and only between the specified start and end times) then no records are returned. BTW, it is also looking for matching datetimes in two tables and there really is matching data in the two tables for that particular date and times. Can anybody help resolve this?
SELECT DISTINCT t1.* FROM [DbName].[SchemaName].[TableName1] AS t1,
[DbName].[SchemaName].[TableName2] AS t2
WHERE t1.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME)
AND t2.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME) AND
CONVERT(DATETIME,CONVERT(varchar, t1.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
CONVERT(DATETIME,CONVERT(varchar, t2.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
SET @parentPage = (SELECT [frntPage] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID] AND [OLissue].[frntPage] IS NOT NULL AND [Outlook].[parent] IS NOT NULL))
IF @parentPage IS NOT NULL
IF exists (SELECT [Outlook].[ID], [Outlook].[title], [Outlook].[name], [Outlook].[issueID] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID])) SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT DISTINCT NULL ID, 'No parent page yet' title FROM [Outlook]
And I kept getting this error when testing in SQL Management Studio.
Msg 512, Level 16, State 1, Procedure parentPage, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have a simple report with a group header, details and footer. How do I display a message to inform users when no data is returned when they enter a parameter which returns no records - at the moment my report returns a blank screen, it would be more useful if I could output the following
I have a stored procedure that is going to be called from my ssis job. I create a package and it as an 'Execute SQL Task' within it. My stored procedure will return a 1 or 0. If 1 is returned then the package needs to continue processing. If 0 is returned then the job stops running.
How can I get the value that is being returned from the stored procedure and either continue or stop the job depending on what is returned?
I'm having problem Inserting data from 1table into another table. I'm getting the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I get the reason why I'm having this error message but I don't know how to fix code... Help please? Thanks
Here's an example of what I'm trying to do...
table1 has: ID | Name | countryID 1 | Joe | 101 2 | Ben | 102 3 | Tom | 102
Now I wrote this code to insert some of the data from table 1 into table2...
insert into Table2(CompName, ID) values('Information',(select ID from table1 where countryid=102))
I have a qry/dataset in a report. If the qry returns no data the report will simply show a the headers/footers and no data in the detail section. However, I need to display all zeros if the detail section ie '0', if no data is returned. How can I do that? I have SSRS 2005.
I have a SP that needs to return a record set based upon the values of calling another SP. The 2nd SP uses the output parameter and works fine. However, the resulting Insert doesnt see the value. I cannot for the life of me work out why not!
My other question is how do I stop the imbedded procedures from outputing a result? When the procedure is run in analyser it produces three sets of data. The first two being the outputs for the two SPs.
Any help would be much appreciated!
Code as follows:
CREATE PROCEDURE [dbo].[trl_GetOpenDurations] AS
--- CALLS the sp trl_CallOpenDuration with the following params ---1 min minute value - int ---2 max minute value - int ---3 return value - int - interval in minutes
set nocount on
declare @min int --- less than 1 min declare @hour int --- more than 1 min, less than 1 hour
create table #t ([Type] VARCHAR(10) ,[Value] int)
EXECUTE trl_callOpenDuration 0,1,@min insert into #t ([Type],[Value]) values ('<1Min', @min)
EXECUTE trl_callOpenDuration 1,60,@hour insert into #t ([Type],[Value]) values ('<1Hour',@hour) ---get data from table Select * from #t GO
I am not sure how to fix this?? Imports System.Data Imports System.Web.UI.WebControls.FormView Imports System.Data.SqlClient Partial Class Assignment Inherits System.Web.UI.Page Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click Dim empID As Integer Dim nbrID As Integer Dim equid As Integer Dim UpdateQuery Dim empequipid As SqlCommand Dim mySQLConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("DBConnectionString").ToString Dim myConnection As New SqlConnection(mySQLConnection) myConnection.Open() empID = (Session("UserEmployeeID")) equid = (Session("UserEquipID")) If CType(empID, Integer) = Nothing Then empID = (DropDownList1.SelectedValue) 'Response.Write("Insert: " & empID) 'Response.End() Me.SqlDataSource1.InsertCommand = "INSERT INTO [EMPLOYEES_EQUIP] ([EquipID], [EmployeeID]) VALUES " & equid & " " & empID & "" Me.SqlDataSource1.InsertCommand = SqlDataSourceCommandType.Text Me.SqlDataSource1.Insert() Else empequipid = New SqlCommand("Select EmpEquipID From EMPLOYEES_EQUIP Where EmployeeID = " & empID & " AND EquipID = " & equid & " ", myConnection) empequipid.CommandType = CommandType.Text nbrID = empequipid.ExecuteScalar 'Response.Write("Select EmpEquipID: " & nbrID) 'Response.End() empID = (DropDownList1.SelectedValue) 'Response.Write("<br>Update EmployeeID: " & empID) 'Response.End() Me.SqlDataSource1.UpdateCommand = "UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID & " WHERE EmpEquipID = " & nbrID & " " Me.SqlDataSource1.UpdateCommand = SqlDataSourceCommandType.Text Me.SqlDataSource1.Update() <---Right here is displaying "Incorrect syntax near '0'."
'Added to output the Update Query UpdateQuery = "UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID & " WHERE EmpEquipID = " & nbrID & " " 'Response.Write(UpdateQuery) 'Response.End() End If 'Updating the session to new EmployeeID Session("UserEployeeID") = (DropDownList1.SelectedValue) 'Response.Write("<br>UserEmployeeID Session: " & Session("UserEmployeeID")) 'Response.End() '-- Turn On Employee Information Table EmpInfo.Visible = True '-- Hides the Employee Name's Drop Down List table. maintable.Visible = False End Sub End Class
Hi,I am trying to test a login form and I get this error message and can't find out why. Istarted out with the Login control, but since I have to try it on the ISP's server, I can't use the SQL Server Managment Studio's integrated authentication. So, I converted the login control to a template and assigned a handler for the login button:protected void LoginButton_Click(object sender, EventArgs e){String usrname = lpLogin.UserName.ToString(); //lpLogin is the <ASP:Login ...>String conString = "Data Source=mylocalserver\SQLEXPRESS;Initial Catalog=LPRU;Integrated Security=True";String selQuery = "SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname + "';";SqlConnection con = new SqlConnection(conString);SqlCommand cmd = new SqlCommand(selQuery, con);con.Open();SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // <--- it says "syntax error near ';' " on this line, I tried it without CommandBehaviorwhile (rdr.Read()){Label1.Text= rdr.GetString(0) + rdr.GetString(1); // for testing purposes, trying to print out first name and last name}rdr.Close();con.Close();}Is there a way of using SQLServer 2000, used by my ISP, and take advantage of .net 2.0's login control, roles, membership, ...? By just using a connection string?
