Array Data Type In SQL Server 2005

Aug 9, 2006

Hi,

I was migrating from Oracle to SQL Server 2005 using SSMA (SQL Server Migration Assistant) but i`ve found an issue, i can´t find how to fix it. In my stored procedure in PL/SQL exists this lines:

TYPE T_ARRAY_COL IS VARRAY (1000 ) OF VARCHAR2 (50);
A_COLUMNS T_ARRAY_COL := T_ARRAY_COL();

Somebody know how can i simulate this data type ARRAY. I was reading http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/ but some things are not clear for me.... please help me, give me one hand.

Thank you

David



View 1 Replies


ADVERTISEMENT

Array Data Type In SQL Server Stored Procedures?

Aug 17, 2000

Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem?
Thanks,
Dan Collins

View 6 Replies View Related

Parse Array Data Type To Rows

Jun 7, 2007

Hi,



We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.



Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.



Any Ideas?



Thanks



Emilio

View 6 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Memo Data Type Import Error While Importing Data From Access File Into SQl Server 2005

Sep 10, 2007

I have one column in SQL Server 2005 of data type VARCHAR(4000).

I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.

now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.

Could you please let me know what is the reason?

I know that memo data type does not supported into the SQl Server 2005.

I am with SQL Server 2005 Standard Edition with SP2.

Please help me to understans this issue correctly?

View 4 Replies View Related

Sql Server 2005 Data Type

Apr 18, 2008



i have created a table called a
in that there are fields
like
col.name datatype
name varchar(100)
syste bigint

after creating it
i need to get the column name and data field thru table name in single query ( sql server 2005)

i need to get the colname and its datafields
as output in single query
my output will be
name varchar(100)

View 1 Replies View Related

Data Type Nvarchar(MAX) In SQL Server 2005

Sep 8, 2006

nvarchar(max) I have a nvarchar(MAX) “Description” field in my SQL Server 2005 database. Somewhere I think I read that nvarchar(MAX) is the size of 4000. My question is in my Insert Stored Proc set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[syl_CodeScheduleTypeInsert]               @ScheduleName                        nvarchar(50),                 @ScheduleDescription                nvarchar(????????)ASBEGIN            -- SET NOCOUNT ON added to prevent extra result sets from            -- interfering with SELECT statements.            SET NOCOUNT ON;            BEGIN TRY                        INSERT I What number should replace the  ???????? with? nvarchar(4000) is that correct? I tried nvarchar(MAX) and nvarchar but both versions only input one character into my database table. Please help a newbie out, cheers!

View 2 Replies View Related

A Puzzle Of Data Type In SQL Server 2005

Oct 13, 2007

Recently I began to use SQL 2005. But yesterday I found a puzzle about data type.
I use this script to create a table:

CREATE TABLE [dbo].[InvoiceMaster](
......
[InvoiceMemo] [varchar](max) NULL,
......
)

But when I run this cmd : exec sp_columns InvoiceMaster. I find the type_name of this column changed to "text".
Can anybody tell me why?

Thanks in advance!

View 2 Replies View Related

SQL Server 2005 Datetime Data Type

Jul 23, 2005

Does anyone know if SQL Server 2005 will support date values before1/1/1753 in the datetime data type?

View 1 Replies View Related

Question In SQL Server 2005 Data Type

Nov 4, 2007



Hi..

Are there auto number "data type" in SQL server 2005?
if so, how can I add it?
if not, can you tell me what is the alternative?

Thanks..

View 3 Replies View Related

What Is DT_UI8 Equivalent Data Type In SQL Server 2005 For Integration Server

Aug 13, 2007

Hello,
I'm trying to build a integration service package importing data from XML files and directs this data to different MS SQL server 2005 Database tables.
Can someone suggest me what is equivalent(mapping) data type of DT_UI8 in Sql server 2005 for Integration Services.

Or how to consume DT_UI8 fields in SQL server 2005.

View 2 Replies View Related

Type Mismatch Rs In Array

May 6, 2006

I have a problem using 3 tables. The first recordset is my own invention (that means a very simple one). It reads product features from a table:

Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT * from prodfeatures"
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

'then I read the recordset into arrays like this:

While Not Rsx.eof
i=i+1
mte(i)=Rsx("id")
' every id has a corresponding featurename
mfnavn(mte(i))=Rsx("featurename")
Rsx.movenext
Wend

'this part works very well

Now comes the difficult part that the SQLTeam figured out (this combines an order base with an itemorder base):

Set Rs = Server.CreateObject("ADODB.RecordSet")
sSQL="select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & dDate & " AND Orders.orderid <> 1 group by oitems.catalogid,oitems.features"
Rs.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

'Then I write the content of the products with product features - the complexity of the sql is due to a use of many other operations:

While Not Rs.eof
response.write Rs("catalogid") & "features:" & Rs("features") & "Name:" & mfnavn(rs("features"))
Rs.movenext
Wend

The query runs, and starts well, writing 3 lines:

239 features:221 Name:Standard bil max 5m lang og 1,9 m høy
240 features:270 Name:Liggestol (gratis)
240 features:271 Name:C1 Enkeltseng i delt lugar u. bildekk. Kun vask


but it is then aborted with this message:

Microsoft VBScript runtime error '800a000d' Type mismatch: 'Rs(...)'
I know that the problematic part is the array
mfnavn(rs("features"))
...anyone with experience in this?

View 6 Replies View Related

Iseries To Sql Server 2005 Package Returns Invalid Data Type

May 3, 2006

I have installed the trial enterprise edition of sql server 2005. I am trying to recreate a dts packages. I am using an iseries OLE DB provider. In the data flow the connection to a physical file that has string data gets translated into binary data. Has any one gotten a download to work with the correct data?

View 1 Replies View Related

Array / Table As Return Type

Aug 16, 2006

Hello All,I have a scenario in which my stored procedure has to return fewvariables with their value and also the collection. Now in SQL their isno such as array, so the best is to return the table in place of.I am execting the stored procedure by having sql command in place.and created the various parameters(variables) those i need the valuesof and secondly wondering how should i be creating the parameter as atable returntype.Any help on this would be a million worth useful.I can excerpt the code if required.RegardsSandesh Kadam

View 1 Replies View Related

Proc Argument Type Array

Jul 20, 2005

I have ...CREATE PROC InvoiceDeleteExample2(@InvoiceList1 VARCHAR(255),@InvoiceList2 VARCHAR(255) = '',...@InvoiceListN VARCHAR(255) = '')AS....GOI would like....CREATE PROC InvoiceDeleteExample2(SELECT * FROM table;)AS....GOI hope help... thx

View 1 Replies View Related

SQL Server 2005 Partitions And SAN Array

Jul 3, 2007


I am looking for information/suggestions in regards to portioning data across a large SAN. The database is several TB in size, and we are looking to partition the data on a date so that the current data is always the fastest available and history can be set as read only but still be available.

If the server has 8 drive letters how can I ensure that the 4 data drives are stripped across the most physical drives for the most through-put??

Are there any specific questions I need to ask the server technicians?

Any pitfalls I should be aware off??

Thanks!!!

View 1 Replies View Related

How Insert An Array Into Sql Server 2005 Using VB.2005

Mar 4, 2008

I have the text file parsed into an array.  How to insert that into  SQLServer Database? (VB 2005)
 Thanks,
Siby

View 3 Replies View Related

Am Using Sql Server 2005, Column Data Type Is Varchar, How To Write A Query To Sort

Aug 2, 2006

this data. need help
Sort following numbers by asc and desc order

Before query sort
-1.1
-8.8
-15.5
0.0
+0.5
+0.2

Sort asc
+0.5
+0.2
0.0
-1.1
-8.8

Sort Desc
-8.8
-1.1
0.0
+0.2
+0.5












View 5 Replies View Related

Using User Defined Data Type (UDT) In A Sql Server 2005 Database + Window Application.

Feb 18, 2006

Hi,

I am using VS2005 C# + sql server 2005 Express edition.

I need to using a database that uses my own defined data types to define its tables columns.

I already have designed a Database projact and create the new UDT as follows:



Create a new Database project in the Visual C# language nodes.


Add a reference to the SQL Server 2005 database that will contain the UDT.


Add a User-Defined Type class.


Write code to implement the UDT.


Select Deploy from the Build menu.

Now I need to ask some quistions:

1- When I try to add a new query to a table that contains my new data type in its columns,if I try to exexute the query the next message appears:

'Execution of user code in the .Net framework is disabled. Enable "clr enabled" configuration option'.

How can I doing that??

2- I need to use that database - which has the new data type - in a traditional ' Visual C# Windows Application' instead of 'Database', but:

when I try to add a new Data Source that contains the tables that have the new data types in its definitions, the next message appears:

'<AyaDatabase.dbo.MyNewUDTTable>

User-defined types(UDTs)are not supported in the Dataset Designer.'

So, how can I resolve that problem??

please help me.

Thanks in advance for any help.

Aya.



View 4 Replies View Related

What Type Of Field Should I Use To Store An Array Or Datatable?

Feb 25, 2005

Hi - I'm using Session variables to store information (sort of webshop).

The sessions are a mix of the usual straightforward strings, wich are no problem - but I also have a DataTable which I store in a session variable, and I also have an array, which I store in a session variable eg. session("day")(x) where x is the item in the array.

I want to give my users the ability to store the items they've selected in my database, but have no idea of what type of field I should use for the datatable and array session variable. Should I use a TEXT field, or is there another more appropriate one'?

I'm using SQL Server 2000.

Thanks for any help,

Mark

View 3 Replies View Related

Another Noob Question... What Field Type Is Best For Storeing An Array?

Feb 1, 2008

Is there a special field type I should be using, instead of a NVARCHAR for storing comma separated words?

I could, construct an array from the field value... but not sure if this would be the "right" way to do it?

View 4 Replies View Related

Dose Sqlserver 2000 Support Array Type?

Mar 31, 2006

Any one know about it?

View 9 Replies View Related

Using An Optimised Method To Insert A Variant Type Array To DB

Feb 13, 2008

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!

View 1 Replies View Related

Passing A List/array To An SQL Server Stored Procedure 2005

Aug 16, 2007

Hi, I m using sql 2005 as a back end in my application...
I am useing Store procedure..for my data in grid..
 
ALTER PROCEDURE [dbo].[ProductZoneSearct]
(
@Productid char(8),@Proname char(8),@radius int,@mode varchar(5) = 'M',@Zone nvarchar(1000),)
ASSET NOCOUNT ON;Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))
Insert #Product Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment ,Miles, UserID, Daily, PhoneFrom [tblproduct] Where city IN (@Zone)
Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment ,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone From #Product As L Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID  Order By DateAvailable
 
if i pass value in "where city in (@Zone)" and @Zone ='CA','AD','MH'  then it can not get any result..but if write where city in ('CA','AD','MH') then it give me perfact result..
I tried to below syntax also but in no any user Where city IN ('+@Zone+')
In short if i pass value through varibale (@Zone) then i cant get result...but if i put  direct value in query then only getting result..can anybody tell me what is problem ?
Please Hel[p me !!!
Thank you !!!

View 5 Replies View Related

Index Creation Causes Error The Conversion Of A Char Data Type To A Datetime Data Type Resulted...

Jul 23, 2005

Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

System.Data.SqlClient.SqlException: The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Dec 14, 2005

After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.

System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.

Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.

At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.

anyone having this problem before?? hopefully you guys can help.

Thanks,

View 4 Replies View Related

Help With Error 'value Of Type Byte Canot Be Converted To 1 Dimensional Array Of Byte'

Mar 26, 2008

hi
i am getting an error with my code, it says 'value of type byte canot be converted to 1 dimensional array of byte' do you know why and how i can correct this error, the follwoing is my code.
can anyone help me correct the error and let me know ow to solve it
thanks for any help givenPublic Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequestDim myConnection As New Data.SqlClient.SqlConnection("ConnectionString")
myConnection.Open()
 Dim sql As String = "Select Image_Content from ImageGallery where Img_Id=@ImageId"Dim cmd As New Data.SqlClient.SqlCommand(sql, myConnection)cmd.Parameters.Add("@imgID", Data.SqlDbType.Int).Value = context.Request.QueryString("id")
cmd.Prepare()Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
dr.Read()
context.Response.ContentType = dr("imgType").ToString()context.Response.BinaryWrite(CByte(dr("imgData"))) ----- this is the line with the error
End Sub

View 1 Replies View Related

Converting A Data Type Double To A Type Float Using A Data Adapter &&amp; SSCE 3.5

Feb 13, 2008

Hi,

I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:


this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);

this.mycourtsDataSet1.AcceptChanges();

this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);


This did not happen with VS2005/CE3.01.

I have tried changing all references to decimal (or money in CE3.5) without luck.

I'm beginning to think that string may be the way to go!!!!!!!

Can someone shed some light on my problem.

Thanks,

Later:

It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.

View 4 Replies View Related

COnverting Numeric Data Type (Oracle) To Date Data Type Using SSIS

Mar 7, 2007

We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

On Error, If I fail the component, then the error is :

There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Regards

RH

View 3 Replies View Related

Storeing PDF's In SQL 2005 Using Image Data Type, Not Working...

Jul 20, 2007

Hi everyone, I have an odd problem.  I have a generic upload/download ASP.net page that allows the upload and download of and type of file.  I have so far tested the following file types:
XLS, MDB, JPG, DLL, EXE, PDF, TXT, SWF, and GIF
All of these upload and download fine, EXCEPT PDF's.  I have tried 4 different PDF's and all open prior to upload/download, but after uploading and downloading, I get the following (from Adobe Reader) error upon trying to open:
"There was an error opening this document.  The file is damaged and could not be repaired"
 Here's my current code:
9      Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs)10   11       If e.CommandName = "open" Then12         Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)13         Dim sSQL As New StringBuilder14         Dim sqlCmd As SqlCommand15         Dim sqlReader As SqlDataReader16         Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte17   18         sSQL.Append(" SELECT      * ")19         sSQL.Append(" FROM        [survey_document] ")20         sSQL.Append(" WHERE       [sd_document_code] = @sd_document_code ")21   22         sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)23   24         sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument)25   26         sqlConn.Open()27         sqlReader = sqlCmd.ExecuteReader28   29         While sqlReader.Read30   31           Response.ContentType = sqlReader("sd_mime_type").ToString()32           Response.BinaryWrite(sqlReader("sd_document"))33           Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString())34         End While35   36         sqlReader.Close()37         sqlConn.Close()38   39       End If40   41     End Sub42   43     Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click44   45       'Make sure a file has been successfully uploaded46       If UploadedFile.HasFile Then47   48         'Connect to the database and insert a new record into Products49         Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)50         Dim sSQL As New StringBuilder51         Dim sqlCmd As SqlCommand52         Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte53   54         'Read the files binary data into a Byte array55         UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length)56   57         sSQL.Append(" INSERT INTO [survey_document] ")58         sSQL.Append(" (           sd_document ")59         sSQL.Append(" ,           sd_title ")60         sSQL.Append(" ,           sd_file_name ")61         sSQL.Append(" ,           sd_upload_date ")62         sSQL.Append(" ,           sd_mime_type ")63         sSQL.Append(" ) VALUES (  @sd_document ")64         sSQL.Append(" ,           @sd_title ")65         sSQL.Append(" ,           @sd_file_name ")66         sSQL.Append(" ,           @sd_upload_date ")67         sSQL.Append(" ,           @sd_mime_type ")68         sSQL.Append(" ) ")69   70         sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)71   72         sqlCmd.Parameters.AddWithValue("@sd_title", FileTitle.Text.Trim())73         sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType)74         sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName))75         sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now)76         sqlCmd.Parameters.AddWithValue("@sd_document", byteArray)77   78         sqlConn.Open()79         sqlCmd.ExecuteNonQuery()80         sqlConn.Close()81   82       Else83   84         'Either the file upload failed or no file was selected85   86       End If87   88     End SubI have also tried the following code as a result of searching far and wide, trying other peoples methods:
 1 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs)
2
3 If e.CommandName = "open" Then
4 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)
5 Dim sSQL As New StringBuilder
6 Dim sqlCmd As SqlCommand
7 Dim sqlReader As SqlDataReader
8 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte
9
10 sSQL.Append(" SELECT * ")
11 sSQL.Append(" FROM [survey_document] ")
12 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ")
13
14 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)
15
16 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument)
17
18 sqlConn.Open()
19 sqlReader = sqlCmd.ExecuteReader
20
21 While sqlReader.Read
22
23 Dim buffer() As Byte = sqlReader("sd_document")
24 Dim blen As Integer = CType(sqlReader("sd_document"), Byte()).Length
25
26 Response.ContentType = sqlReader("sd_mime_type").ToString()
27 Response.OutputStream.Write(buffer, 0, blen,)
28 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString())
29 End While
30
31 sqlReader.Close()
32 sqlConn.Close()
33
34 End If
35
36 End Sub
37
38 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
39
40 'Make sure a file has been successfully uploaded
41 If UploadedFile.HasFile Then
42
43 'Connect to the database and insert a new record into Products
44 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)
45 Dim sSQL As New StringBuilder
46 Dim sqlCmd As SqlCommand
47 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte
48
49 'Read the files binary data into a Byte array
50 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length)
51
52 sSQL.Append(" INSERT INTO [survey_document] ")
53 sSQL.Append(" ( sd_document ")
54 sSQL.Append(" , sd_title ")
55 sSQL.Append(" , sd_file_name ")
56 sSQL.Append(" , sd_upload_date ")
57 sSQL.Append(" , sd_mime_type ")
58 sSQL.Append(" ) VALUES ( @sd_document ")
59 sSQL.Append(" , @sd_title ")
60 sSQL.Append(" , @sd_file_name ")
61 sSQL.Append(" , @sd_upload_date ")
62 sSQL.Append(" , @sd_mime_type ")
63 sSQL.Append(" ) ")
64
65 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)
66
67 'sqlCmd.Parameters.AddWithValue("@sd_title", PictureTitle.Text.Trim())
68 'sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType)
69 'sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName))
70 'sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now)
71 'sqlCmd.Parameters.AddWithValue("@sd_document", byteArray)
72
73 sqlCmd.Parameters.Add(New SqlParameter("@sd_mime_type", SqlDbType.VarChar))
74 sqlCmd.Parameters.Add(New SqlParameter("@sd_title", SqlDbType.VarChar))
75 sqlCmd.Parameters.Add(New SqlParameter("@sd_upload_date", SqlDbType.DateTime))
76
77 sqlCmd.Parameters.Add(New SqlParameter("@sd_file_name", SqlDbType.VarChar))
78
79 sqlCmd.Parameters.Add(New SqlParameter("@sd_document", SqlDbType.Image))
80
81 Dim bArray(UploadedFile.PostedFile.ContentLength - 1) As Byte
82
83
84
85
86
87
88 UploadedFile.PostedFile.InputStream.Read(bArray, 0, UploadedFile.PostedFile.ContentLength)
89
90 sqlCmd.Parameters("@sd_mime_type").Value = UploadedFile.PostedFile.ContentType
91 sqlCmd.Parameters("@sd_title").Value = PictureTitle.Text.Trim()
92 sqlCmd.Parameters("@sd_upload_date").Value = Now
93
94 sqlCmd.Parameters("@sd_file_name").Value = System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName).ToLower
95
96 sqlCmd.Parameters("@sd_document").Value = bArray
97
98
99 sqlConn.Open()
100 sqlCmd.ExecuteNonQuery()
101 sqlConn.Close()
102
103 Else
104
105 'Either the file upload failed or no file was selected
106
107 End If
108
109 End Sub
110

 This method atleast gives a different error:
 "Adobe Reader could not open '132-171510.pdf' because it is either not a supported file type or the file has been damaged (for examplc, it was sent as an email attachment and wasn't correctly decoded)."
 Please help, it would be appreciated.  Thanks
 

View 5 Replies View Related

RS2005: Export To Excel Error: Destination Array Was Not Long Enough. Check DestIndex And Length, And The Array's Lower Bounds.

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

User Defined Data Type Issues After Upgrade To 2005

Mar 13, 2008

Hi,

After upgrading to MSSQL 2005 we have stored procedures which get this error:

Msg 2715, Level 16, State 7, Procedure prc_sel_accruals, Line 37
Column, parameter, or variable #15: Cannot find data type umoney.

The user data type is defined in the database. I tried recompiling the SP, which suucceeds but then fails on execution.

The SP works under SQL2000. Is there anything we need to do after the conversion?

View 6 Replies View Related

What Is The 'Data Type' For A Field Containing Both Text And Number, In VS Studio 2005, Databases?

Dec 1, 2007

For a field in a database to accept both text and number what 'Data Type' should I be using, i.e. 23t5

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved