How Would I Send A String Array As A Integer Array?
Jun 25, 2007
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 5 Replies
ADVERTISEMENT
Jun 3, 2008
I'm trying to build an SQL string that should look like this when executed:
UPDATE [Table] SET Active = 'False' WHERE ID IN (3, 4, 5, 6, 7, etc.)
I'm using the convention (in code behind):SqlCommand cmd = new SqlCommand("UPDATE [Table] SET Active = 'False' WHERE ID IN (@TheIDs)", connection);cmd.Parameters.Add("TheIDs", SqlDbType.Text).Value = theIDsAsAnArrayList;
But logically enough I cannot insert them as a text string as they have to be integers seperated by commas.
Question: How can I convert an Array of integers into ... well, a string without the quotes, if you know what I mean?
As it cannot end up like this: UPDATE [Table] SET Active = 'False' WHERE ID IN ("3, 4, 5, 6, 7, etc.")
Note the quotes around the integers.
Any hints on doing this with security in mind are welcome. I know I can concatenate the whole lot as strings, but this is unsecure, so I'm not going for that approach.
View 4 Replies
View Related
Aug 7, 2004
Hi everyone....
I need a column in my table that its DataType should be as integer arrays how can I implemant it in my sqlserver table.
Any help appreciated.
View 5 Replies
View Related
May 4, 2006
hi there,
i need a procedure that works with C# e.g.:
using (SqlCommand cmd = GetCommand("Procedure_Name"))
{
//i=an array of integer values
cmd.Parameters.Add("@array", SqlDbType.????!?!???).Value = i;
cmd.ExecuteScalar();
}
i need to write a stored procedure that takes as input an array of integers (amongst other values)
this procedure must loop through every integer in the array and INSERT a new record into a table.
i have never used T-SQL before.
Many thanks
View 3 Replies
View Related
Jan 25, 2007
All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
View 10 Replies
View Related
Apr 28, 2008
Hi there,
My requirement is to send more than one GUID to the stored procedure.
How can I do that? If you can give me an example that will be great.
Kind regards,
Ricky
View 2 Replies
View Related
Sep 30, 2015
I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:
#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>
[Code] ....
In my code, I would like to Insert the array and then count the number of rows:
SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;
Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.
Hint : If I monitor the database using SQL Profiler. It looks like this:
Why this statement doesn't work correctly?
View 7 Replies
View Related
Jan 25, 2008
Hello All,
I have about 10 different insert into statements which I would like to include into a loop of some sort. Here is what I am trying to accomplish.
SET @aryTableNames = 'thisTable','thatTable','anotherTable'
DO
SELECT * INTO [Server].[dbo].[@aryTableNames]
FROM OPENQUERY(mysqlDB, 'SELECT * FROM `@aryTableNames`')
LOOP
View 5 Replies
View Related
Jul 28, 2015
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
View 8 Replies
View Related
Jun 14, 2006
Hello, I have a question on sql stored procedures.I have such a procedure, which returnes me rows with ID-s.Then in my asp.net page I make from that Id-s a string likeSELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? Here is it:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path
Thank youArtashes
View 2 Replies
View Related
Jun 13, 2006
I keep running into a problem with the debugger freezing up. I'm trying to pull results from a query into an array (not more than 10 rows)
cmd.ExecuteReader();
while (rdr.Read() == true) {
letter = rdr[0].ToString();
i += 1;
}
if I comment out the "letter = rdr[0].ToString();" portion of code, the project runs fine. With that code there, I can not step into the proc from the SQL test script. No errors are raised. VS just freezes.
View 1 Replies
View Related
Jul 17, 2007
how can i send array from asp.net to sql?
View 3 Replies
View Related
Jun 19, 2006
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 1 Replies
View Related
Jan 31, 2007
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 1 Replies
View Related
Apr 4, 2007
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 1 Replies
View Related
Aug 28, 2007
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 3 Replies
View Related
Jan 16, 2008
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 6 Replies
View Related
Jun 2, 2008
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 7 Replies
View Related
Aug 22, 2004
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 1 Replies
View Related
Feb 19, 2006
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 9 Replies
View Related
Sep 20, 2006
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 3 Replies
View Related
Feb 14, 2005
Hi,
Is it possible to have a column as array in SQL Server 2000- similar to Oracle's varray. If yes, how?
rgds,
Kishore
View 14 Replies
View Related
Jan 27, 2004
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 4 Replies
View Related
Feb 3, 2004
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 8 Replies
View Related
Jan 3, 2007
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 3 Replies
View Related
Jan 28, 2008
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 4 Replies
View Related
Feb 21, 2008
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 4 Replies
View Related
Nov 20, 2006
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 1 Replies
View Related
Sep 19, 2007
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 4 Replies
View Related
Jan 24, 2008
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 7 Replies
View Related
Oct 10, 2007
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 1 Replies
View Related
Jul 20, 2007
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 1 Replies
View Related
Oct 18, 2006
Hi, I want to populate the array with a single column values from database(sqlserver 2000)Ex. name ageaaaa 23bbbb 43cccc 18 Now i want to populate the array with name field values. Please anyone guide me how to do this.Thanks in advanceBala
View 3 Replies
View Related
