due to SQL Injection Attack I use parametric sql request like this SqlConnection cnx = new SqlConnection(strCnx)) SqlParameter prm; cnx.Open(); string strQry = "SELECT Count(*) FROM Users WHERE UserName=@username "; int intRecs; SqlCommand cmd = new SqlCommand(strQry, cnx); cmd.CommandType= CommandType.Text; prm = new SqlParameter("@username",SqlDbType.VarChar,50); prm.Direction=ParameterDirection.Input; prm.Value = txtUser.Text; cmd.Parameters.Add(prm);but how do I retrieve values ndlr, I have several rowsnormally its like SqlDataAdapter ... DataSet... DataTable... foreach (Datarow datarow in DataTable.Rows) { ...... }so how do I retrieve values in parametric request ?????
select * from table 1 where column1 = @var1 and column2 = @var2 union all select * from table 2 where column1 = @var1 and column2 = @var2 union all ..........
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost Servername: DED1774 (the machine name) Servername: localhost/reportserver Servername: DED1774/reportserver Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
Okay, I'm a novice! I installed Back Office with NT opperating system on my home computer. I want to learn SQL 7.0. This is incredible, but I can't launch the program. I've tried everything under Start, Programs, SQL, but nothing listed seems to get me into SQL 7.0. What am I missing? Help is most appreciated!!
public int RunSQLQuery(string sqlStatement) { if (_sqlConnection.State != ConnectionState.Open) _sqlConnection.Open();
_sqlCommand = new SqlCommand(sqlStatement,_sqlConnection); return _sqlCommand.ExecuteNonQuery(); }
Now i am stuck on how i will get the ID from the sp (that is @ID) and return it to my C# code as i need it to update the same row further on :)all i am getting till now is the number of rows 'changed' i.e. 1! any thoughts? thanks
Hi,I have quite a complicated request to do in sql.I've got on table with 3 fields (id, field1, field2) and I have toprocess a request on each of the records, one by one, and thendetermine if the status is OK for each recordsFor example, I would check if the sum of field1 and field2 is greaterthan 10.What is the best way to do this ? It has to be done in a storedprocedure. I can't return the status for each one of the records, soshould I store it in a temporary table and do a select on it from mytier application (vb.net) ?ThxSam
Hi,I'm going to explain as clearly as possible:I have two tables:Relationships(relation_id, table1, table2)Relationfields(relation_id, field1, field2)In Relationships, relation_id is the primary keyIn Relationfields, relation_id is the foreign keyI have a front-end interface that allows the user to add records toRelationships and Relationfields as followed:The user selects a table1 and table2 values from listboxes. These arereal table names from sys.objects, so then the user can select fieldsof these tables on which he wants to create a JOIN.Anyway, I can easily insert the table1 and table2 into Relationships(relation_id is an auto-increment). Then I need to get the relation_idof this new Relationship (easy since I know which values I've insertedand table1-table2 associations are unique.Now the PROBLEM :I need to insert into Relationfields all the fields selectioned by theuser for each of the two tables . But the user might have selectedseveral fields from table1 and table2, so I need to pass A LISTPARAMETER to my Stored Procedure as I don't know how many values offield1 and field2 there is going to be.I hope this is clear enough. Is it possible to achieve what I want ?Should I pass an entire concatenated string with values separated bycomma or whatever and then decrypt it in the stored procedure ?Thx
I've got a table that houses the data for several routes, (routeID, pointID, Longitude, Latitude and Elevation). a set of Points make up a route. I'd like to programmatically access specific points and I'm trying to figure out how to request...say the third point in my dataset. I'm new to SQL, but I was able to figure out that I can find the row number by using the SQL syntax:SELECT ROW_NUMBER() OVER(ORDER by PointID) as 'Num', Latitude, Longitude, Elevation FROM [PointTable] WHERE (RouteID = 5) But I cannot (or do not know how to) add a clause that saysAND (Num = 3) So can someone show me how to request a specific row?
I have two tables:-------- Bids-------PKBidsFKAuctionsFKUsersBidAmount-------------------FollowedLots-------------------PKFollowedLotsFKAuctionsFKUsers Fields beginning with PK are Primary keys and those beginning with FK are Foreign Keys. For each FollowedLot of a specific User, I would like the MAX BidAmount of the FKAuctions and also the MAX BidAmount of the FKAuctions of that specific User. I hope you can understand my question.Thank you!
Hie, I have a big request involving 24 tables working with Oracle. I`m porting my application onto SQL Server 6.5. The problem is the maximum number of tables: 16. I did create a view with 9 tables and recontruct my query with the 15 remaining tables and the view. I tried and I got an error message: "Msg 4408, Level 19, State 1 The query and the views in it exceed the limit of 16 tables."
Here is the query: SELECT ENT.ENT_DOM FROM CTI, ENT, IEX, V_VAL, V_DEV DEV_A, VTM VTM1, VTM VTM2, VTM VTM3, VTM VTM4, VTM VTM5, V_DEV DEV_B, NIE, POS, TIE, TLP, TPL
There is only 16 tables... Can anyone explain me why it does not work. Thanks a lot.
Hi, (sorry for my english...)I have a probelm to build my Pivot table.Indeed, The goal is just to see an example of Table Pivot with our Database...For that, I use this link : http://www.csharphelp.com/archives4/archive623.htmlI try to adapt the code but some problems...I use visual developper, and i have installed OWC11 & .NET framework 2.0.using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Office.Interop.Owc11;public partial class _Default : System.Web.UI.Page{private void Page_Load(object sender, System.EventArgs e){//determine whether or not the browser supports OWCif(Request.Browser.ActiveXControls){Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'classid='clsid:0002E55A-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");//cause the object to load data on the clientResponse.Write(@"<script>document.all.pTable.ConnectionString ='" + ConfigurationManager.ConnectionStrings["TimeTrackingDB"] + "'</script>");Response.Write("<script>document.all.pTable.ResourceID = 'RESOURCE'</script>");PivotTableClass PTClass = new PivotTableClass();PTClass.ConnectionString = ConfigurationManager.ConnectionStrings["TimeTrackingDB"].ToString();PTClass.DataMember = "";PivotView pview = PTClass.ActiveView;Response.Write(pview.FieldSets.Count);}}}When i'm lauching there is an error : HRESULT E_FAIL http://fists148.free.fr/Error.JPGIf i put the last lines in commentary, public partial class _Default : System.Web.UI.Page{private void Page_Load(object sender, System.EventArgs e){//determine whether or not the browser supports OWCif(Request.Browser.ActiveXControls){Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'classid='clsid:0002E55A-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");//cause the object to load data on the clientResponse.Write(@"<script>document.all.pTable.ConnectionString ='" + ConfigurationManager.ConnectionStrings["TimeTrackingDB"] + "'</script>");Response.Write("<script>document.all.pTable.ResourceID = 'RESOURCE'</script>");}}}The component is loading but it seems to be an issue with the connection database...In my web.config, I Have this : <connectionStrings><add name="TimeTrackingDB" connectionString="Data Source=PORT06139DEV_2000;Initial Catalog=TimeTracking;Persist Security Info=True;User ID=user_TTT;Password=user_TTT;pooling=false"/></connectionStrings>Error connection (http://fists148.free.fr/OWCscreen.JPG)If you have any idea, i take it...I'm deseperate :eek: Thanks By advance
Im now working on a assignment where i have hierarchial tree structure arrangement for representing an health care. This is supported by SQL DB 2005.One of the node in the tree is dissolved and all it functionality have to ported to the other tree node which share the similar structure. Problem in porting is complex due to the following things
1. There are capabilities and permission associated with the each node.
2.each node has a unique identifier across the three (note: though this seems to be of no problem it might become issue when search occurs for eg. a->b->c search now becomes a->b->x->n ->?).
3.Already there are messages left in the DB. Please can anyone help me how can i initiate this migration. Im very new to the design and im just started "ABCD" of it.
I am not sure if this belongs under this forum or 'New to SQL' so please feel free to move it if necessary.
Following on from an issue i have had with sysdepends and syscomments tables filling up, I have been trying to research what these tables are and how they get filled up (i.e. what is the trigger to insert a row into these tables).
Unfortunately on all my web searches and book searches i have not found anything much more than 'They are system tables'. From what i have gleaned, it looks like every time a stored procedure or database object is changed, then this will create a new entry into the sysdepends table which highlights what the new object is linked to.
Is this understanding correct? And does anyone have any other sources of information which might give me a better idea of what and how these tables are about.
Hi,I've got two primary keys in a table:Constraint(QueryId, ConstraintName)In a stored procedure I select {QueryId, ConstraintName} couples thatmatch some criteria, and what I want to do is specifying in my a SELECTstatement that I want all of the {QueryId, ConstraintName} that are notin my stored procedure result. With only one field, it would be easy :Select * from Constraint where QueryId not in (Select QueryId fromOtherTable)My explanations are not great but I think it's enough to understandwhat I want.Select * from Constraint where QueryId and ConstraintName not in(select QueryId ,ConstraintName from OtherTable)--> of course not correct, but then how can I do that ?Thx
Hi,I have a DB where I would like to maintain a fixed size and control itby myslef.I do not have the options: "Auto-Grow" and "Auto-Shrink" enabled.[color=blue]>From time to time, without a notice, or any logging, the database files[/color]gets shrinker and this causes a database full error.As I wrote, I would like to maintain the size of the database by myselfand not automatioc by the DB server.Please help me find out what can cause this problem and how to solvethis issue.Thnaks,- Ze'ev
I Stipulate that I am the most ignorant creature in the universe, incapable of following exactly written directions available all over the internet to install Microsoft Visual Web Developer 2008 Express Edition and SQL Server Express, in a way that will enable me to make a connection to SQL Server Express.
Having Stipulated the above, is there someone out there that can explain to me, in a dumb-proof way, how to do it in a way that is understandable to the most ignorant creature in the universe?
I am running Windows XP SP2 with all the lattest updates, frameworks, etc., and using IIS.
The dynamic SQL at the buttom causes a error: Msg 402, Level 16, State 1, Line 9 The data types ntext and nvarchar are incompatible in the equal to operator.
I think it is due to the one of the where conditions [URL] = @old_URL ,both are set to NULL(@URL=NULL, @old_URL=NULL). How do I make changes to the SQL statement to compare the conditions where NULL comparsion is inevitable. exec sp_executesql N'UPDATE [Announcement] SET [Atype] = @Atype, [Title] = @Title, [Content] = @Content, [URL] = @URL WHERE [AnnouncementID] = @old_AnnouncementID AND [Atype] = @old_Atype AND [Title] = @old_Title AND [Content] = @old_Content AND [URL] = @old_URL',N'@Atype nvarchar(3),@Title nvarchar(3),@Content nvarchar(41),@URL nvarchar(4000),@old_AnnouncementID decimal(1,0),@old_Atype nvarchar(3),@old_Title nvarchar(3),@old_Content nvarchar(41),@old_URL nvarchar(4000)',@Atype=N'CMC',@Title=N'aaa',@Content=N'asdfg',@URL=NULL,@old_AnnouncementID=6,@old_Atype=N'CMC',@old_Title=N'aaa',@old_Content=N'asdfg',@old_URL=NULL Any help would be apprecaied, Ricky.
Hi everybody. Got a nice little problem here. I have a accessdatabase containing 100 000 rows. Im fetching these rows to a dataset and then inserting them, row by row, to a MSSQL dB. The dB and IIS is running on the same server. I also have full control over this webserver, so I pushed the Server.ScriptTimeout value up to 3600 sec (both in IIS and in the c# code) but when executing this query (witch aprox. take me 8 minutes) I recieve a Error Code: 408. The operation timed out. The remote server did not respond within the set time allowed error. Someone got a clue for me? :) -Thomas
Is it possible for SQLServer to make a http web request in a stored procedure? I know that you can configure it to send email, but I'd rather do that on the webserver side, and that needs to be triggered by a request of some sort. I have a sp that runs nightly, and I'd like it to send out a http request when its done so that I can do further processing/send email/what have you. I haven't been able to find anything on this subject in any of the books I have, and my google-fu isn't strong enough. Thanks for any hints.
Hello! I am learning the RS from SQL 2000 and am having an issue. Does anyone have a "Working" example of how to render a report and render images embedded/external/database to the web? I have the report generated, but the pictures just don't want to render (through the web). I have contacted my hosting service (who are all supposedly Microsoft Certified Technicians) and they have no clue why it won't work. I have gone through every online resource that I have been able to find (including the Microsoft thing about the RenderStream) and it still doesn't work. If any of you have a working example of this, it would be very helpful!! As of right now, I am using a database image, and this is the ASP.Net code that I have been using:
Dim rs As New newafp.RSService.ReportingService rs.Credentials = New System.Net.NetworkCredential("domainusername", "password", "") Dim items As newafp.RSService.CatalogItem() = rs.ListChildren("/", True) Dim results As [Byte](), image As Byte() Dim streamids As String(), streamid As String results = rs.Render("/NewUserLetter" & Location, "HTML4.0", Nothing, "<DeviceInfo><HTMLFragment>True</HTMLFragment><StreamRoot>/newafp/</StreamRoot></DeviceInfo>", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids) For Each streamid In streamids image = rs.RenderStream("folder/NewUserLetter", "HTML4.0", streamid, _ Nothing, Nothing, Nothing, Nothing, Nothing)
Dim stream As System.IO.FileStream = _ System.IO.File.OpenWrite(Server.MapPath("") & "" & streamid & ".jpg")
stream.Write(image, 0, CInt(image.Length)) stream.Close() Next
I'm not really sure how to explain this, so please bear with me. I have a SQL statement, such as: SELECT TOP (10) FROM chartTracks This works with SQL Server Express 2005, but when I moved my site over to work with a MSSQL Server 2000, the statement had to be changed in order for it to work: SELECT TOP 10 FROM chartTracks I was just wondering if there was a technical term for this, and if possible, the locations of anymore sourecs of information regarding the above? I'm just writing a report and would like to include this, if possible. Thanks in advance!
What I want is to update the field 'SaleMonthName' based on the 'SaleMonthNum' field using the CASE expression. I want something like this (pseudo-code):
update Mytable set SaleMonthName Case: IF SaleMonthNum =1, then := 'January' IF SaleMonthNum =2, then := 'February' ... IF SaleMonthNum =12, then := 'December'
Can you tell me why the following queries would give me slightly different results?
SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE CallDate Between '02/19/2007' and '02/20/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) UNION SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE CallDate Between '02/19/2007' and '02/20/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7))
SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE CallDate Between '02/19/2007' and '02/20/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) GROUP BY dateadd(day, datediff(day, 0, CallDate), 0) UNION SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE CallDate Between '02/19/2007' and '02/20/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) GROUP BY dateadd(day, datediff(day, 0, CallDate), 0)
I'm running a (phpBB) forum which uses a MYSQL data base - I can access the database via a webpage through my ISP and it lets me run short scripts (through a web interface). I need to do the following (shown in psuedo code below). I would be eternally grateful if someone could translate it into pure SQL commands for me to paste into my SQL quiery input field on the web page I'm using to access my database. (I'm a complete SQL novice)
===================== /* I need to be sure these commands will do nothing if 'jeff' does not exist in the user table */
/* extract the id value for the user called 'jeff' from the users table */ user = GET id FROM users WHERE username = "jeff"
/* remove all entries from the forums_watch and topics_watch tables that contain Jeff's userID */ REMOVE ALL ENTRIES WHERE userID = user
notify = 0;
/* insert many entries into the topics_watch table, one for each topic that exists in the topic table */ arrayOfTopicIDs = GET ALL id FROM topics FOR EACH current_Topic IN arrayOfTopicIDs { INSERT INTO topics_watch (topic_id, user_id, notify_status) values (current_Topic, user, notify) }
/* insert many entries for jeff into the forums_watch table, one entry for each forum that exists in the forum table */ arrayOfForumIDs = GET ALL id FROM forums FOR each current_Forum in arrayOfTopicIDs { INSERT INTO forums_watch (topic_id, user_id, notify_status) values (current_Forum, user, notify) }
=====================
I'm going to implement this with php and MYSQL as a mod for my forum application (phpBB) - but I could really do with a way of doing this immediately with pure SQL commands as a quick and dirty one shot paste into the input field of the MYSQL database web interface until I get the php mod going.
Can this be done using only SQL commands without tedious repetition?
I have two server defined (S1 and S2) S1 has a link defined to S2. IfI open Query Analyzer and run a query referencing S2 server. Mystatement returns data (lets say I am using MSSQL standardauthenitcation). Now I have a ASP.NET application that connects to S1server using ODBC. The exact same statement returns an error.Driver][SQL Server]The operation could not be performed because theOLE DB provider 'SQLOLEDB' was unable to begin a distributedtransaction.ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DBprovider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DBerror trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].Russ....
I have a table (Tickdata; Contract,Price,Timecreated) which storesTimecreated as datetime. I need to request the maximum price where theTimecreated is greater than some value, in this case 9/13/2004 3:09:29 PM.How exactly do I get data from a specified time?Thanks
Is there a way to increase the Toop Tip Length? Currently, it is limiting to 512 (approx) Characters. I need to show more number of characters for certain items. If its not possible, is there an alternative of showing a window on mouse hover and show the data just like a tool tip.
My delete record from database table below is not working, do anyone here know why delete is not working.
Code Snippet
Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click
Dim conn As New SqlClient.SqlConnection(My.Settings.HACS) Dim strSQL As String Dim cmd As New SqlCommand strSQL = ("DELETE FROM CUSTOMER WHERE CUSTOMER_ID =" & Me.IDTextBox.Text)
If conn.State <> ConnectionState.Open Then conn.Open()
If MessageBox.Show("Are you sure you want to delete the current record?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question, _ MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.OK Then
