Hi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, possibly using ADO Objects, in quick time!
Hi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, perhaps using ADO objects in quick time!
I have an asp.net project that displays a timesheet based on a fortnightly system.
It has a Y and X axis, i.e. Sun-Mon, Sun-Mon across the top and Categories of hour types accross the Y axis, i.e. Holidays, Overtime, Maintenance.
I was using a datagrid to gather the rows, but I need to have the whole grid in edit mode which is something that requires more coding. So I have swapped to a datalist.
It will take me 240 fields to store all the data in their corresponding fields. So I basically need to know if there is an easier way to store two dimensional array type data into an SQL table using a minimum of fields.
I've been doing this in Access, but cannot find the answer to how to do it with SQL Server. From a web form, a user can select a number of different dates. The selected dates are held as text (not DateTime) in an ArrayList. Clicking the Submit button writes the contents of the form to a database table. This works for Access: insSQL &= "VALUES (@typEvent, @starts, @ends, @starts, @ends, @attend, @title, @room, @department, @contact, @address, @telephone, @email, @telefax, " For i = 0 to datesArray.Count - 1 insSql &= datesArray.Item(i) Next i insSQL &= "VALUES (@typEvent, @starts, @ends, @starts, @ends, @attend, @title, @room, @department, @contact, @address, @telephone, @email, @telefax, " For i = 0 to datesArray.Count - 1 insSql &= "#" & datesArray.Item(i) & "#, " Next i It doesn't work for SQL Server, and when trying to insert the value "01/29/2007" I get the error message: "The name '#1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted." I have also tried the line: For i = 0 to datesArray.Count - 1 insSql &= satesArray.Item(i) Next i and get: "Incorrect syntax near the keyword 'VALUES'." I'm not sure where to find the information to correct my error. Any help would be appreciated. Tinker
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
I have a user who uses the sql variant data type. Currently the database he uses is read only and he has noticed a lot of "garbage" in the column in the last few days. A dbcc shows no corruption and the user is following the rules of the sql variant data type as far as we know. One last thing, he never had this problem on AS 2000.
config: :confused: SQL 2000 SP3 on Windows Server 2003
I am trying to declare a global variable in a DTS package for passing the recordset to the next stage Active Script . After declaration of the Global Variable and selecting datatype of the variable as OTHER ( Variant ) , when I try to save the DTS Package changes , it throws a Type Mismatch Error .
Can anyone help on this? In SQL 2005, I use a variant with XML type; it seems that its size is limited here. If I assign a big data to it, it appears not assigned at all. Here is the example:
Code Snippet declare @xml xml --cross two table to get many records returned, if nothing is abnormal, just cross more tables here select * from master.sys.objects a, master.sys.objects b for xml RAW, ELEMENTS set @xml=( select * from master.sys.objects a, master.sys.objects b for xml RAW, ELEMENTS ) select @xml --no results here for var @xml
--use single table to get fewer records returned. select * from master.sys.objects for xml RAW, ELEMENTS set @xml=( select * from master.sys.objects for xml RAW, ELEMENTS ) select @xml --results as normal.
Note:here I don't want to test SQL server, I just use XML type variant as oupput for a sproc, and come across the issue here.
Hi, When i try to retirve the datatype of a variant column using SQLColAttribute, the datatype comes back as SQL_BINARY(-2) instead of a CHAR based type. To reproduce the problem, add extended property 'MS_Description' to either a column or a table. Assign a text value to the property.
Execute the following query and verify. SELECT value FROM sys.extended_properties WHERE name = 'MS_Description'
I'm using SQL Server 2005 sp 2 with Visual Studio 2005.
//get the datatype of the variant iReturn = SQLColAttribute( m_SQLStatement, (SQLUSMALLINT) 1, SQL_CA_SS_VARIANT_TYPE, NULL, NULL, &nDummyInt, &nType );
After the execution of SQLColAttribute, nType value is assigned a value of -2 (SQL_BINARY) instead of a CHAR based type. What am i doing wrong? Any help is appreciated.
I've some AT_DATE tables (eg: AT_20080401, AT_20080402, ...) in SQLServer DB, and these AT_XX table have same columns. but table count could be variant, so I have to query sysobjects to get all of these tables. like this: select name from sysobject where name like 'AT_%'
Now I try to create a view AT which is the union of all these AT_XX tables, such as:
Code Snippet
Create View AT as select * from AT_20080401 union select * from AT_20080402 union ...
but since I'm not sure how many tables there, it would be impossible to write SQL as above. though I could get this union result via stored-procedure, view couldn't be created on the resultset of a procedure. Create View AT as select * from AT_createView() <-- AT_createView must be a function, not procedure
I've checked msdn, there is Multi-statement table-valued function, but this function type seems to create one temporary table, I don't want to involve much of insert operation because there could be more than 1million records totally in these AT_XX tables.
So is there any way to achived my goal? any reference would be appreciated, thanks !
I am trying to migrate our Portals database from SQL2000 to SQL2005, but I received "SQL Type Variant Data" error during the data migration with some database. Can anyone help me with this?
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
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!
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....
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
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.
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
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.
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
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) )
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....
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
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
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.
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.