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


ADVERTISEMENT

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

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

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

Rounding On Decimal Data Type (noob Question)

Feb 22, 2006

Hi all,

I'm trying to update a decimal field with a single decimal number (1.8) the problem i'm having, is that it's rounding the number up (2). I would've thought that a decimal datatype would keep the decimal places correct?

This is quite annoying.. I've been searching around the web for an answer.. To no avail (I'm probably asking the wrong question)

Can someone please point me in the right direction?

View 5 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

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

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 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

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

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

Create An Array In A Result Field

Sep 12, 2006

I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier.  Here is few result samples I receive from this code I am using now.  
ReqNo              ProcID
7102005          1409
7102005          1796
7139003          1411
7139003          6097
7261030          1409
7261030          1796
7268303          3998
7268303          4000
 
I would like to create a single row for each "ReqNo" and have a field that will an array of the  "ProcID" results I receive.  In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field?  I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table. 
 
So, ideally my result would be:
ReqNo             ProcSet
7102005          1409, 1796
7139003          1411, 6097
7261030          1409, 1796
7268303          3998, 4000
 
Is this possible?


declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
 
select
 @startdate = dateadd (dd, -7, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
 
select
@enddate = dateadd (dd, 1 , @startdate)
 
select distinct
pp_req_no as ReqNo,
pp_cproc_id_r as ProcID
 
from
risdb_rch08_stag..performed_procedure
(index pp_serv_time_r_ndx)
 
where
pp_service_time_r between @Startdate and @Enddate
and pp_status_v = 'CP'
and pp_rep_id > 0
 
order by
pp_req_no, pp_cproc_id_r

 

View 4 Replies View Related

Passing An Array And/or Variable Field Name To An SProc

Jul 20, 2005

I have 2 questions.I am trying to write a stored procedure to update a table. I am tryingto pass a variable that represents the name of the column/field andanother for the value that I am changing.For example:@FieldName VARCHAR(100)@FieldValue VARCHAR(100)ASUPDATE tblTHETABLESET @FieldName = @FieldValueFirst is it possible to use a variable as the column/field name? Ifso, how do I go about it?Also, it would be nice if I could have the @FieldName and @FieldValuevariables as arrays. Is that possible?Thank-you for any assistanceBill

View 2 Replies View Related

Storeing Picture In SqlServer ...

Dec 1, 2007

Can any say Is it possible to store a picture in sqlserver?? If it possible then Send me some code in C# to store picture file by browseing from my PC.Also which type of field i have to create to store Picture.
plz help me,..

View 1 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

Informix Date Type Field To SQL Server Datetime Field Error

Oct 17, 2007



I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part

I tried a couple of things:

Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.

Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.

What options do I have that will work?

View 1 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

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

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 View Related

What Should B The Field Type

Nov 7, 2004

I'm using Microsoft SQL Server 2000 on windows 2000 Professional OS.

I want to make a field having capacity of 100,000 characters. What datatype should be best for this requirement.

View 1 Replies View Related

About SQL Field Type

Jul 23, 2005

Hi FriendsI have question.I connected oracle and MS access tables(via Delphi7).I wanna get type of field in indicated table. For example if it isinteger or boolean e.t.cPlease tell me the SQL function or whatever that retrievs type of field.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Sum Of A Field That Has A Type Varchar

Mar 28, 2007

Hi guys
        I need immediate help with a query that I am trying to write. I want to sum the values in a query but the field has a type of varchar and it has decimal numbers too. So if I do the query something like that, that converts the field to int, I get the error message.
I tried converting it into real or float but I get error message on that too. I need help with adding the calculatedValues and getting there sum. I would appreciate any help with that.
 
Thanks
-Sarah
Select SUM(Convert(int, calculatedValue))
from monitor.dbo.monHistory
where LocalTimeWithoutDst > '8/26/06' and LocalTimeWithoutDst < '8/28/06'
 
This is the error message I recieve:
Conversion failed when converting the varchar value '274.2' to data type int.
 

View 6 Replies View Related

Updating A Field Of Type Bit

Aug 4, 2004

Hi,
I want to update a field in my table whose value is a 0, to a value 1. This field is of type bit and here is the SP that I wrote to achieve this. Somhow, its giving some error when I tried executing it in the Query Analyzer. What am I doing wrong here??


CREATE PROCEDURE PublishSchedule
(
@SiteCode smallint,
@YearMonth int
)
AS

DECLARE @Active bit
IF ( (SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode) > 0 )
BEGIN
UPDATE CabsSchedule
SET Active=1
WHERE SiteCode=@SiteCode AND YearMonth=@YearMonth
END
GO



Thanks

View 4 Replies View Related

Contains Keyword For Integer Field Type

Feb 17, 2007

I have a field ID of type integer, I want to put two numbers in that field: 3 and 7, so what I do is just store the number 37.Now, is there a command in SQL server which checks if the ID field contains a number I look for, say 7.Something like a CONTAINS keyword...If there is, could someone please tell me what it is AND tell me a bit more about it :memory usage, (dis)advantages etc.

View 1 Replies View Related

Save SQL XML Field Type To A Xml File

Jul 14, 2007

Hi, I have a table which holds raw xml in a field with type XML. How do I export that XML field to an XML file? Thanks Bones 

View 6 Replies View Related

SQL Datetime Field Type Command Help??

Jan 15, 2008

I am new to asp.net, C#, and sql server, coming from php and ODBC/Access background (trying to leave all that behind) Anyway, I have a textbox that will be used to INSERT a date into a SQL datetime field. The date coming from the textbox will be formatted like "MM/dd/yyyy". For my purposes the day, month, and year are all that I require, the time is irrelevant. Then from a different page in the site I will be using a variable containing the current date derived from "DateTime.Now.ToString("MM/dd/yyyy")" to select records with a matching date. Is there anyone that has an example showing both inserting into a datetime field and extracting records based off the date format that I have specified? Would it be better to use a different format? In the past I have stored dates as a julian in an int field type but I would rather learn to use the datetime field type in sql. Any help would be greatly appreciated.

View 1 Replies View Related

Duration Data Field Type

Mar 17, 2008

Hi, I want to store a time duration such as 1:30 (mm:ss), 1:00, or 1:23 in a SQL 2005 database.  What is the best data field type for this data?  DateTime or TimeStamp?  Thanks 

View 2 Replies View Related

Word Doc In Image Field Type

Feb 25, 2005

Hi,

I was wondering if someone can help me with is problem.

I have uploaded word docs to the db which is fine. The problem is viewing. I can view then as word documents but the boss does not want the files opened in word.

Is it possable to retrieve the file from from the db and put into say a textbox or lable.

I can see the letter P using this code

Dim Doc() As Byte = New Byte(Convert.ToInt32(0)) {}
Dim bytesReceived As Long = DBContent.GetBytes(0, 0, Doc, 0, Doc.Length)

Dim encoding As ASCIIEncoding = New ASCIIEncoding
lblTest.Text = encoding.GetString(Doc, 0, Convert.ToInt32(bytesReceived)).ToString

Can any help

Thanks

View 4 Replies View Related

About The Type Field : Datetime And Smalldatetime

Feb 4, 2006

hi all, the field type :datetime and smalldatetime,  i still can't understand.everytime when i inserted the data to the db, i also get the error message "System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."i must change the field type to string, so that i can insert data my codetxt_datetime.text = '5/2/2006'insert into datetime (datetime) values ('"& txt_datetime.text & "')"can anybody tell me the reason???thank you!!

View 4 Replies View Related

Table Field Data Type

Jul 9, 2004

I have a field with exam scores. 77, 89.5 ect. range :0-100

What datatype shall I use?

View 2 Replies View Related

SQL Server Field Type Designation

Dec 29, 2004

Is there a data type in SQL Server for entering hyperlinks to websites? MS Access has a hyperlink data type but I can't find a corresponding SQL Server data type which I can use.

View 4 Replies View Related







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