Index Was Outside The Bounds Of The Array
Index was outside the bounds of the array
When working with a SQL Server 2005 database and ASP.NET,
I get this error when trying to pull a small amount of data from the database,
Any help would be great, thanks a lot
I work for a large company, so I have to watch the code I release.
View Complete Forum Thread with Replies
Related Forum Messages:
Index Was Outside The Bounds Of The Array.
On the data tab in reporting services I have just named the stored procedure I wish to run. This Stored procedure expects a datetime parameter. When I select run in the data tab and enter the date (I have tried various different formats) the error "Index was outside the bounds of the array. (system data)". Does anyone know how this can be solved? it seems a simple issue but is turning into a pain to sort out.
View Replies !
Index Was Outside The Bounds Of The Array - When Table Columns Deleted
Hi If I delete 5 or more columns from a table I get the error "Index was outside the bounds of the array", OK is the only response. I then have to close the report and then open it again. Easy fix is don't delete more than 5 columns, but my template is setup with 15 columns as it's far quicker to delete them as it is to add them 1 by 1 each time. I've finally got around to looking for a hotfix or something similar but can find no mention of this exact error relating to table column deleting. Anyone else experience this or know if there is a hotfix for it? Thanks
View Replies !
Help, Index Out Of Bounds Error
Hi, I really don't know why I'm getting this error! Index was outside the bounds of the array. I am reading a comma-delimited flat file, line by line. This is part of my script component: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim rowValues As String() rowValues = Row.Line.Split(columnDelimiter) 'parse rows by comma Row.col1 = rowValues(0).Replace("""", String.Empty) Row.col2 = rowValues(1).Replace("""", String.Empty) I've done this quite a few times before in other packages. WHY is it telling me that rowValues(1) is out of bounds?? I've commented out EVERYTHING in my script, except for these 2 rows. If I comment out Row.col2, I don't get the out of bounds error. If I include Row.col2, I get the out of bounds error, so it doesn't like rowValues(1). The file has 13 columns in it. A row looks like this: "ATMC","004930202",529.000000,"L",11.380000,6020.020000,7210.270000,6072.920000,"BSZTX","MTPO",,"Equities","My Fund Appreciation","06/30/2006" Thanks
View Replies !
Index Array Out Of Bound
Dear All, i'm getting an error "index array out of bound" while saving the record. sql server 2005 with service pack1 system is working fine. but at client place it is sql server 2005 with SP2. there we are getting error. any help.... windows 2003 sp1 at both the places. only the difference is in sql server. how can i handle this
View Replies !
Database Growing By Leaps And Bounds
I have a database that is growing by 40 to 50 megs a day. I understand that the '_W' objects are statisical information for query performance and not indexes, but does anybody know how much disk space is actually used by these objects. I do have the 'auto create statistics' and 'auto update statistics' set on.
View Replies !
How Would I Send A String Array As A Integer Array?
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type. What would I need to do to convert it to an Integer array? @OfficerIDs as varchar(200) Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs) Thanks
View Replies !
Array Of Array - IRR Function
Hi, I am using the IRR function in a report. I have created the following code so it creates an array: Public GroupIRRArray(-1) As Double Public Function addToIRRArray(ByVal BMV As Decimal, ByVal BAB As Decimal, ByVal EMV As Decimal, ByVal EAB As Decimal, ByVal CFB As Decimal) Dim g As Integer g = uBound(GroupIRRArray) + 1 ReDim Preserve GroupIRRArray(g) if g=0 then GroupIRRArray(g) = (CFB+EAB-BAB+BMV)*-1 else if g=1 then GroupIRRArray(g) = (BAB-CFB-EAB+EMV) else GroupIRRArray(g-1)= GroupIRRArray(g-1)-(BMV) GroupIRRArray(g) = (BAB-CFB-EAB+EMV) end if End Function It works fine but now I want to create multiple groups within my report. How can I change the code so it loops on another parameter? What I had in mind was to create an initial array with the parameter value that I want to use for grouping and a dynamic array based on the name of each group. So I would end up with one array containing the group name plus x number of arrays with the raw data. Alternatively, is there a way to use the IRR function without creating a custom code? Like a conversion parameter that would make my floating field a one dimensional array? Thanks, Jam
View Replies !
Clustered Index On Client_ID+ORderNO+OrdersubNo, If I Create 3 Noncluster Index On Said Column Will It Imporve Performance
Dear All. We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions. table is >> Order_Dtl Columns are:- Client_ID varchar 10 Order_ID varchar 50 Order_Sub_ID decimal ..... ... .. . Pk is (ClientID+OrderId+OrderSubID) Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order. The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination. Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status) My Question are:- Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.? Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.? Q3. I want to check what indexes has been used? on what search? Q4. How can i check what table was populated when, or last date of update (DML)? My Limitation is i Dont Create a Partioned table. I dont have permission to do it. In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003. I am thank u to all who read or reply. Arshad Manager Database Esoulconsultancy.com (Teradata Master) 10g OCP
View Replies !
Index Was Out Of Range. Must Be Non-negative And Less Than The Size Of The Collection. Parameter Name: Index
Keep getting this error when positioning to the last page of a report. Using Server 2003...SqlRpt Svcs 2000 sp2 Detail error msg: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index Anyone have any suggestions? Any way to find out what collection is blowing?...or where parameter name: index comes from?
View Replies !
Index Table1 And Select For 647.600 Records.. It Is So Slow.. But I Have No Index :)???
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
View Replies !
The Index Entry For Row ID Was Not Found In Index ID 3, Of Table 357576312
Hi, I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error: The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'. All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . . Any ideas as to what is causing this error?
View Replies !
Advantages Of Using Nonclustered Index After Using Clustered Index On One Table
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ?? Thanks
View Replies !
Index Internals - Last Time Index Was Rebuilt?
I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt? Thanks much.
View Replies !
Index/performance Index For SELECT.... IN Statement
Hi All, I 'm working to improve some sql performance. One of the major syntax inside the SELECT statment is .. WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F') (It's to compare the field content with some user input parameter inside a parameter table... ) I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records. Do any one know if still any chance to improvide the performance like this? Much Thanks, Andy
View Replies !
Clustered Index Vs. Full Text Index
Quick question about the primary purpose of Full Text Index vs. Clustered Index. The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance. Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself? I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database. Is this correct? I am kind of confused on why you would use full text index as opposed to clustered index. Thank you Goldmember
View Replies !
Array
I messed up my question so badly a few posts ago, that I'm going to lay out the problem carefully as follows: On a spreadsheet: --there are a few names down column A, one name per row (e.g., r3c1Mary, r4c1Scott, r5c1Jane, r6c1Ann, r7c1Cathy, r8c1Jim) --there are a bunch of columnar animals heading up row 1 (e.g., r1c2cat, r1c4dog, r1c6horse, r1c8bird, r1c10snake, r1c12elephant, r1c14goat, r1c16giraffe, r1c18ox, r1c20rat, r1c22monkey, r1c24pig) --(might be addressed in a later post to this or another thread): on row 2, there is a Budget field and a Cost field for each animal (e.g., r2c2Bud, r2c3Cost, r2c4Bud, r2c5Cost, r2c6Bud, r2c7Cost, r2c8Bud, r2c9Cost, r2c10Bud, r2c11Cost, r2c12Bud, r2c13Cost, r2c14Bud, r2c15Cost, r2c16Bud, r2c17Cost, r2c18Bud, r2c19Cost, r2c20Bud, r2c21Cost, r2c22Bud, r2c23Cost, r2c24Bud, r2c25Cost) But for now, just the people names down the left side, and the animals across the top would suffice to pose my question. The question is this: the number of animal types gets added to, or subtracted from, across the top of the page every month. On July, there might be 12 animal kinds listed; but on August, there might be 19--or there might be 11. (The number of people involved change as well.) As a user, I don't know how many and of what kind of animals (aka fields) will be on the page on any given month. The underlying query to the table doesn't know whether a Giraffe is going to be a category, among others at the top of the sheet, from one month to the next. If a garden variety query was written, it would hard code: SELECT r1c2cat, r1c4dog, r1c6horse, r1c8bird, r1c10snake, r1c12elephant, r1c14goat, r1c16giraffe, r1c18ox, r1c20rat, r1c22monkey, r1c24pig. But that rots. The query would have to be re-written for every new list of applicable animals each month. Or even more ugly, I'd have a predetermined number of variables to hold each type of animal; some would go unused while a number exceeding the variables available wouldn't make it into the query, or whatever. I could on the other hand slowly loop through a SELECT sAnimalType for each sPerson. Slowly. That's where my question comes in: Can a query be written that allocates a SELECT Array(sAnimalType)? (And if so, I hope I can extract the sequential info accordingly.) Simplified, I currently have an Access table with two columns (four if we go whole hog): Person, Animal (, Budget, Cost). (But I'm perfectly happy to leave Budget and Cost for a separate posting. Maybe that's a sub query or something.) Thanks for any assistance that you can provide.
View Replies !
Parameterized Or Array With This SQL?
I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes. Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click Dim ACSelections As Boolean = False Dim ACItem As ListItem For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSelections = True End If Next If ACSelections = True Then Dim ACSqlString As String ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE " For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4) ACSqlString &= "ORDER By Customers.CustomerName" sql_CustomerSearchGrid.SelectCommand = ACSqlString End IfEnd Sub
View Replies !
DataRow Array
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.
View Replies !
SqlCommand Array Help
I want to do something like the following but I get an error: Object reference not set to an instance of an object. SqlConnection sqlConnection = new SqlConnection("server=xxxxx"); SqlCommand [] cmd = new SqlCommand[3]; Object returnValue; cmd[0].CommandText = "DO QUERY"; cmd[1].CommandText = "DO QUERY"; cmd[2].CommandText = "DO QUERY"; cmd[3].CommandText = "DO QUERY"; } sqlConnection.Open();int i = 0;while(i<4){ cmd[i].CommandType = CommandType.Text; cmd[i].Connection = sqlConnection; cmd[i].ExecuteNonQuery(); returnValue[i] = cmd[i].ExecuteScalar();i++} sqlConnection.Close();How should I do the followingThanks
View Replies !
Array In WHERE Clause
Hello, how can I use an array in a WHERE clause? If I had an array saystring[] NamesArray = new string[] {"Tom", "***", "Harry"} How would I do this:string myquery = "SELET name, city, country FROM myTable WHERE name in NamesArray" Thanks in advance,Louis
View Replies !
Array Problem
Hi All, I have this code below:Dim a As Integer = 0 While a <= myArray.Length() Conn.Open() Dim UpdateCmd As New SqlClient.SqlCommand("Update email_addr SET category = 'Deleted' where HP = @hp", Conn) UpdateCmd.Parameters.Add("@hp", SqlDbType.VarChar, 50).Value = myArray(a) UpdateCmd.ExecuteNonQuery() a = a + 1 Conn.Close() End While BUt, When I run it, I receive error "Prepared statement '(@hp varchar(50))Update email_addr SET category = 'Deleted' wher' expects parameter @hp, which was not supplied. " DOes anyone have the solution??? Thanks....
View Replies !
SQL Query In A VB.net Array
I want to do a query on an SQL Server 2005 db and have the results returned into aarray or collection in vb.net... how do I do this? I know the basicconnection and stuff.. just not how to get the result to an array thanks!
View Replies !
Get Array Value From A Loop?
Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...?? ===================================== DECLARE @ABBR AS NVARCHAR(50)SET @ABBR = 'CHP,CNH,COW' DECLARE @SEP AS NVARCHAR(5)SET @SEP = ',' WHILE patindex('%,' + @ABBR + ',%', @ABBR ) > 0 BEGIN -- do the rest END
View Replies !
Result Set Into Array
Hi All, How can I read a query result set (which are of type VARCHAR) into an array? i.e the result set comprises of just one column and 5 rows and i want to save these into an array to easily extract each row whenever i want to. Hope I have conveyed my idea clearly. Gayathri
View Replies !
How To Identify An Array
I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.
View Replies !
Array In SQL Server
How would one implement an array in SQL Server ? Specically, i'm parsing a field into the array, and doing operations on these elements. Thanks.
View Replies !
Array In A STOC??
Hi all, My Issue is the next thing: I have a table filled with contracts I have Stoc that checks if contract has expired If I start the Stoc I must give it the contractnumber with it like: SP_expired 8 This works fine but now I want that the Stoc checks all the contracts by just starting the Stoc. I don't really know how to do this, I have thinking about using an array. But don't have an idea where to start.... Thnx in advance
View Replies !
Array Of Recordsets
Hi, I have a form with several tabs, on each tab has a similar structure : on each tab description of a table in database (different tables on each tab). I was wondering : is there a possibility to create an array of recordsets? (in this case i could attach an index of recordset to index of tab )
View Replies !
Do Loop Or Array In SQL
How does one perform a do loop in SQL. Is there any documentation on it somewhere I can read? Also, how might SQL handle arrays? Any help would be greatly appreciated it Thank you all for you help. Rey
View Replies !
Inline Sql With An Array
I have data which looks like below actid labname 100 CKS 200 CKS;HDP;LAS I need the data to be actid labname 200 CKS 200 HDP 200 LAS The ; is the seperator For a reporting product I created a sp which created a temp table and then using my function below built. problem is the product won't allow me to create a temp table. With what I have below anyone have any creative ideas I could use. In-line sql, subquery views? select enc_id,labcnt,order_name,date_due reate FUNCTION fn_GET_ARRAY_VALUE( @DELIMITER VARCHAR(100), @STRING VARCHAR(1000), @ARRAY_POSITION INT) RETURNS VARCHAR(8000) AS BEGIN DECLARE @CURRENT_POSITION INT DECLARE @VALUE VARCHAR(8000) SET @CURRENT_POSITION = 0 WHILE @CURRENT_POSITION<@ARRAY_POSITION AND CHARINDEX(@DELIMITER,@STRING,0)>0 BEGIN SET @STRING = SUBSTRING(@STRING, CHARINDEX(@DELIMITER, @STRING, 0) +LEN(@DELIMITER), LEN(@STRING) -CHARINDEX(@DELIMITER, @STRING, 0) + LEN(@DELIMITER) ) SET @CURRENT_POSITION = @CURRENT_POSITION + 1 END IF CHARINDEX(@DELIMITER,@STRING,0)=0 SET @VALUE = @STRING ELSE SET @VALUE = SUBSTRING(@STRING, 0, CHARINDEX(@DELIMITER, @STRING, 0) ) RETURN(LTRIM(RTRIM(@VALUE))) END
View Replies !
Array Parameter
Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?
View Replies !
2 Dimensional Array
Hi. Does anyone know how to pass a 2 dimensional array into a stores procedure? I need to loop through it and insert it into a table. Thanks!
View Replies !
Create An Array With T-SQL
I'm using SQL Server 2005 and have have a table similar to the following: Name Week Role Bob 1 Primary Bob 2 Backup Bob 3 Primary Joe 1 Backup Joe 2 Backup Mike 1 Primary Mike 2 Backup Is there a t-SQL query that will combine the role and week into an array and return a table like: Name Role Bob Primary: 1, 3 Backup: 2 Joe Backup: 1, 2 Mike Primary: 1 Backup: 2
View Replies !
Array In TSQL
Hi all, I have data like this : POHDR;JAKARTA;St.1DTL;1. ;00248337;8996006855701 ;083;041;002; 7,812.50; 312,500.00; 284,091.00; 8,875.00 first row is the PO header and the second is the detail. This data was collected using Export Import Wizard from flatfile and put it in one column. I want to split the header and the detail into different table and map each column separated by ; to a proper field. For this work I should check every column separated by ; with CHARINDEX function but I have to write the TSQL as much as the count of ;. It would be better if we can use array to simplify the code. Can we use array in Transact SQL ? since I have no clue referring to SQLBOL. Thanks in advance. Best regards, Hery
View Replies !
Split Array
hi, how can i split text separated by semicolumn in different cells: text1;text2;text3; into 1 - text1 2 - text2 3 - text3 thank you
View Replies !
Array As Parameter
hi guys im having some problems with arrays as parameter what i want to do is pass a array of comma separated values i.e 12,24,36 and use the in(@parameter) but it doesnt work i've found some solutions, like ALTER PROCEDURE [dbo].[GetLawyers] ( @LawyerTypeIDs VARCHAR(100) AS DECLARE @SQL VARCHAR(2000) SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS] WHERE [PARCELAS] IN (' + @LawyerTypeIDs + ')' EXECUTE (@SQL) but in my case doesnt work. here is a part of my code SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cod_banco ORDER BY coeficiente asc) AS RecID FROM tb_valor_bancos where (1 = CASE WHEN @TABELA IS NULL THEN 1 ELSE CASE WHEN TABELA = @TABELA THEN 1 ELSE 0 END END ) AND (1 = CASE WHEN @PARCELAS IS NULL THEN 1 ELSE CASE WHEN PARCELAS in(@PARCELAS) THEN 1 ELSE 0 END END ) ... i know there is a way to pass the array into a table but it doesnt work too thanks
View Replies !
Dynamic Array
Please excuse me if this is the wrong forum. I hope your combined genius will be able to point me in the right direction. I have an ASP page that accepts any SQL query. I want to create a table based on the result set so the number of columns can grow/shrink. But I don't know how. Can anyone help me? My ASP Code shows how I create a table with only 8 fields. <% Dim objConn dim SQLstmt Dim objRS Dim SQLqry Dim UID Dim RecordArray Dim I UID = right(Request.ServerVariables("AUTH_USER"),8) SQLqry = Request.Form("qry") Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString="Trusted_Connection=yes;driver=SQL Server;server=Server;database=db;" objConn.Open If SQLqry = "" then Response.Write "Please Enter a SQL Query" Else Set objRS = objConn.Execute(SQLqry) Response.Write "<H5 align = ""center"">" & sqlqry & "</H5>" Response.Write "<Table align = ""center"" border = ""1"">" For I = 0 To objRS.Fields.Count - 1 Response.Write "<TD><B>" & objRS(I).Name & "</B></TD>" Next Do while not objRS.EOF RecordArray = objRS.GetRows(30) For I = 0 To UBound(RecordArray,2) Response.Write "<TR>" Response.Write "<TD>" & RecordArray(0,I) & "</TD>" Response.Write "<TD>" & RecordArray(1,I) & "</TD>" Response.Write "<TD>" & RecordArray(2,I) & "</TD>" Response.Write "<TD>" & RecordArray(3,I) & "</TD>" Response.Write "<TD>" & RecordArray(4,I) & "</TD>" Response.Write "<TD>" & RecordArray(5,I) & "</TD>" Response.Write "<TD>" & RecordArray(6,I) & "</TD>" Response.Write "<TD>" & RecordArray(7,I) & "</TD>" Response.Write "</TR>" Next Loop Response.Write "</TABLE>" objRS.close objConn.close set objRS=nothing set objConn=nothing End if %>
View Replies !
Index Scan Vs Index Seek
I have a really strange problem. I execute this query: declare @cid int set @cid = 2003227 select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @cid select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @cid or @cid = 0 3 Million rows in sales, 120000 in product. The first does and index seek, the second an index scan. The execution plan reports that the scan takes 99.87% of the cost, and the seek takes 0.13% This problem obviously gets worse the bigger the dataset / query /etc. The reason I query this, is because it never used to take this long to do index scans. Is there something i can change, something i can fix? Any help would be appreciated. Josh
View Replies !
Need Help Building/reading C# SQL Array
I'm stuck and uinder a bit of a time crunch. I have 5 fields I want to get out of a sql database using a function that I'm writing. I figure it sounds like an array. basically I want to make an array, and fill it up with the results of a sql select, then read the array. This is what I have so far..... String TempHRAcctCode, TempJobDescription, TempHourlyRate, TempEmplID; Array TempArray; TempJobDescription = DDDept.SelectedItem.Text; (to get KeY Value) SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MYCONN"].ToString()); connection.Open(); SqlCommand command = new SqlCommand("Select HRAcctCode, HourlyRate , EmplID, ... FROM TimeMyProfile WHERE JobDescription = " + TempJobDescription + " ", connection); SqlDataReader TempDataReader = command.ExecuteReader; while TempDataReader.Read ( ... OK I GIVE UP! Thanks in advance
View Replies !
Get &> Array &> Split &> Listbox
Hi everyone, having some problems Basically, using ASP.NET 2.0 and here is my problem, Get data from table Put into array Split where there is a + remove +'s assign to listbox to give a list of everything in that table The + split the courses, so in my Order table I have A + B + C etc and I want all of the different options in a list box (note different records have different entries it isn't always a b c) I am testing my code, here is what I got Public Sub TitleChange(ByVal Sender As Object, ByVal E As EventArgs) Try Dim DBConn As SqlConnection Dim DSPageData As DataSet = New DataSet() Dim VarTxtAOE As String DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=XXX") 'Dim DBDataAdapter As SqlDataAdapter DBDataAdapter = New SqlDataAdapter("Select AOE FROM TBL_Role WHERE Title = @ddlTitle", DBConn) DBDataAdapter.SelectCommand.Parameters.Add("@ddlTitle", SqlDbType.NVarChar) DBDataAdapter.SelectCommand.Parameters("@ddlTitle").Value = TitleDropDown.SelectedValue DBDataAdapter.Fill(DSPageData, "Courses") 'Need to find out what this rows business is about whats the number about? and am I doing it correct? 'txtAOE.Text = DSPageData.Tables("Courses").Rows(0).Item("AOE") 'txtAOE.Items.Add(New ListItem(DSPageData.Tables(0).Rows(0).Item("AOE"))) VarTxtAOE = DSPageData.Tables("Courses").Rows(0).Item("AOE") Dim VarArray() As String = VarTxtAOE.Split("+") Response.Write(VarArray()) txtAOE.DataSource = VarArray() txtAOE.DataBind() 'Response.Write(test) 'ListBox1.DataSource = test 'Response.Write(VarArray) Catch TheException As Exception lblerror.Text = "Error occurred: " & TheException.ToString() End Try End Sub My response.Write works correctly, but my list box doesn't, also I don't want to say which bit of the array like I have done using 1, I just want to display the whole array in my list box. I am not worrying about removing the +'s at the minute, just splitting my data and putting each section into a listbox Maybe I am going about this the wrong way, but I have been trying a lot of different things and its hard to find any help Thanks Chris
View Replies !
Write SQL Rows To An Array
I have an sql table called survey with rows: SurveyID, callref, question1, question2, question3, question4 etc. I want to get the information from the database with a select command like "SELECT question1, question2, question3, question4, question5, question6 WHERE SurveyID = 1" and write it to and array like this: aProfits As ArrayList = New ArrayList() aProfits.Add("question1value")aProfits.Add("question2value")aProfits.Add("question3value")aProfits.Add("question4value")aProfits.Add("question5value") Whats the best way of doing this? Andrew
View Replies !
|
TRACKER
