How To Compare Binary And Int Colns
Jul 23, 2005I need to compare and update a Binary(8) coln with values from an
Int(4) coln. Could someone please explain how to do this..??
thanks
Sunit
ADVERTISEMENT
Sqlserver And Binary Data How To Compare
Apr 16, 2008
Hi Guys,
is there a way to compare if some binary data in a sql database are the same or different?
Cheers
Concatenate All Binary Columns Into Single Binary Column?
May 22, 2014Server is SQL 2000
I have a table with 10 rows with a varbinary column
I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)
example as follows
TableA
Column1 Column2 Column3
aaa 001 <some binary value>
aaa 002 <some binary value>
aaa 003 <some binary value>
aaa 004 <some binary value>
aaa 005 <some binary value>
desired results in TableB
Column1 Column2
aaa <concatenated value of above binary columns>
T-SQL (SS2K8) :: Store Binary Data Rather Than Int Or Binary?
May 7, 2015I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?
View 9 Replies View RelatedWhen Have 4 Tables How To Compare Table With 4nd Table? ( Need To Join And Compare With Datatime)
Jul 22, 2007Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
Binary
Dec 31, 2007i am using asp.net 2.0 with c#.
i have database - mssql server.
in that i have tuser table in that table i have password feild which datatype is Binary.
when i am entered password in asp.net application that time runtime error is convert the string to binary.
my code is---string myByte = Stingtest;
System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();Byte[] bytes = encoding.GetBytes(myByte);myByte = "";
myByte = encoding.GetString(bytes);
objClsDllSql.sqlCon = objReadIniFile.funReadIniSql();
objClsDllSql.sqlCon.Open();objVarIni.sqlDA = new SqlDataAdapter("select * from tUser ", objClsDllSql.sqlCon);
objVarIni.sqlDA.Fill(objVarIni.sqlDS, "tUser");
objVarIni.sqlDT = objVarIni.sqlDS.Tables["tUser"];
strSqlCmdText = "insert into tUser values( " + Convert.ToInt32(txtUserCode.Text) + " , "+
" " + " ' " + txtUserID.Text + " ' " + ", "+" " + " ' " +txtFirstName.Text + " ' " + ", "+
" " + " '" + txtLastName.Text + "'" + ", "+" " + myByte + ", "+
" " + "'" + txtUserType.Text + "'" + ", "+ " " + "'" + dtpWarningDate.SelectedDate + "'" + " , "+
" " + "'" + dtpExpireDate.SelectedDate + "'" + ", "+" " + "'" + drpdwnStatus.SelectedItem.Text + "'" + ", "+
" " + "'" + Session["LoginName"].ToString() + "'" + ", "+" " + "'" + dtpCreateDate.SelectedDate.ToString() + "'" + ", "+
" " + "'" + txtUpDatedBy.Text + "'" + ", "+
" " + "'" + dtpUpdateDate.SelectedDate.ToString() + "'" + ")";
pls help me .
its really urgent.
gayatri
Binary Serialization
Mar 9, 2007Hello,I am trying to serialize (binary) a class and save it in a database. I followed a few examples I found in internet and this is what I came up with: 1 ' Rows
2 <Serializable()> _
3 Public Class Rows
4 Implements ISerializable
5
6 Private _Rows As New Generic.List(Of Row)
7 Public Property Rows() As Generic.List(Of Row)
8 Get
9 Return _Rows
10 End Get
11 Set(ByVal value As Generic.List(Of Row))
12 _Rows = value
13 End Set
14 End Property ' Rows
15
16 ' New
17 Public Sub New()
18 End Sub ' New
19
20 ' New
21 Public Sub New(ByVal siRows As SerializationInfo, ByVal scRows As StreamingContext)
22 End Sub ' New
23
24 ' GetObjectData
25 Public Sub GetObjectData(ByVal siRows As SerializationInfo, ByVal scRows As StreamingContext) Implements ISerializable.GetObjectData
26
27 siRows.AddValue("Rows", Me.Rows)
28
29 End Sub ' GetObjectData
30
31 Public Sub Serialize(ByVal filename As String)
32
33 Dim sRows As Stream = Stream.Null
34 Try
35 sRows = File.Open(filename, FileMode.Create, FileAccess.ReadWrite)
36 Dim bfRows As New BinaryFormatter
37 bfRows.Serialize(sRows, Me)
38 Finally
39 sRows.Close()
40 End Try
41
42 End Sub ' Serialize
43
44 Public Shared Function Deserialize(ByVal filename As String) As Rows
45
46 Dim sRows As Stream = Stream.Null
47 Try
48 sRows = File.Open(filename, FileMode.Open, FileAccess.Read)
49 Dim bfRows As New BinaryFormatter
50 Return CType(bfRows.Deserialize(sRows), Rows)
51 Finally
52 sRows.Close()
53 End Try
54
55 End Function ' Deserialize
56
57 End Class ' Rows
After serializing the class I need to save it in an a SQL 2005 database.But all the examples I followed use the filename ... Anyway, do I need to do something to save this into an SQL 2005 database or can I use it as follows? And how can I use this?This is the first time I do something like this so I am a little bit confused.Thanks,Miguel
Binary Problem
Aug 1, 2007Hy friends!I'm new in WebApplications that have SQL SERVER DB.My problem is that...In the DataBase I have a table with varbinary column and in the program I want save in this column a value of binary[] variable! How I can make it?Another question.... If I want select (by a query in the application) the value of the binary column, where I store it? because if I want select an integer value I take this in a int variable, and if I want select an string value from a DataBase I take this in a string variable..but How I can make it with binary column?Thanks in advance!!
View 1 Replies View RelatedBinary Data
Dec 28, 2007Hello,
I have a table which uses binary data to store passwords. How do I view the contents of the "binary data" column, ie. the passwords? It just shows it as <binary data>?
SQL Binary To C# Byte[]
Apr 6, 2004I have a binary column with length 20 in SQL server table. I store dynamically C# byte[] value range from 0 to 19. So for example, If I store length of 16 and when I try to retrive it back into byte [] in C# it returns whole length 20. When I see in debug it has value from 0 to 15 which I want to use but from 16 to 19 is zero. How can I get just length value which I stored.
I use DataRow to get whole row and from the row object I extract byte [] based on column name.
Thank you in advance....
Binary File
Aug 5, 2004Can someone firecct me to instructions on how to download a Binary file in a asp.net application.
I have a sql 2000 db that has a field that contains files and my users need access to them via my web app.
Bit To Binary Problem
Nov 15, 2004I have a field in my database defined as a bit. I need to bind this field to a checkbox I have stored in a datgrid on my webform. I'm having problmes finding the right call in the html
<asp:CheckBox id="chkComplexity" runat="server" Width="125px" Text="Complexity">
WHAT TO PUT HERE !?!?!
</asp:CheckBox>
Any help would be greatly appreciated
Binary Vs. TimeStamp
Jan 12, 2002Can I use "timestamp" datatype in SQL Server as the equivalent datatype for "binary" in ACCESS 2000?
View 1 Replies View RelatedBinary Data
Feb 8, 2001We have a dll that sends a hexadecimal data (const. length) to MS SQL Server database. It's declared as String in VB, the db column data type is binary.
Here is the SQL String that has been executed successfully in Query Analyzer:
"declare @MyHAX varchar(32)
select @MyHAX='0x3236374535454337363145313430463742394545 413443473230343544320000'
insert MyTABLE (MyCOLUMN)
values (convert (binary(32),@MyHAX))"
When I am trying to do the same thing in the insert stored procedure, I get an error message: "Disallowed implicit conversion from data type varchar to data type binary, table 'MyDB.dbo.MyTABLE', column 'MyCOLUMN'. Use the convert function to run this query."
Does anyone know how can I insert my binary data?
Binary Data Into SQL 6.5
Oct 21, 1998How does one go about getting a graphic image into SQL Server 6.5. For example, let`s say I have a company logo that I want to include in a company profile table to be used on some reports. The graphic is now a .BMP or .GIF or .JPG file.
I just do not have a clue how this works.
Bob
Binary Checksum
Feb 6, 2004Hi,
Can anyone provide me with the syntax for comparing rows of two tables using binary checksum? The tables A and B have 8 & 9 columns respectively. The PK in both cases is Col1 & Col2. I want checksum on Columns 1 to 8.
Thanks
Binary Data
Apr 1, 2008Hi there,
Am working on an archiving system that stores files/images in a column of type Binary. we want to change the front end of this archiving system it was done using asp.net we dont have access to the source code
what is the way to retrieve the files from the binary columns?
how they store or retrieve the files?
Thanx best site for sql,,,
Binary Datatype
Jun 14, 2006Quick question here. I am working with a database which uses Binary(8) as an id(ie, contact_id is binary(8), and connects 2 tables). I can already extract the binary data (in the form 00-00-00-00-00-00-ab-cd). I know that there exists a contact_id 0x0000000000001989 (hex) however, when I attempt this query...
"SELECT * FROM Support_Incident where Contact_Id = '0x0000000000001989'". I get no results. What have I done wrong?
Binary Value Manipulation
May 1, 2007I would like to drop the leading 0x on a binary value so I can do abitwise operation.Here is simplified code:select right(0x88186000,8)I expected to get back 88186000, this was not the case. The commandreturned some wierd characters.Am I missing something?
View 3 Replies View RelatedQ. About Binary Formats.
Jul 20, 2005Is it true that binary and varbinary has a max-kapacity at 8000 bytes? Imjyst unsure how to read the dokumentation and want to be completely sure.And image is only one to use for data aobve that size?Thx in regards for clearing things out for meReagardsAnders
View 1 Replies View RelatedBitArray To Binary
Jul 20, 2005I want to store a binary string in SqlServer from VB .NET through astored procedure. I have been storing the binary data in a BitArraywhich I am not set on by any means. When I pass the BitArray into astored procedure, which is looking for a variable of type binary, itthrows the following: "Object must implement IConvertible." BitArrayobviously does not implement IConvertable.If anyone has a good way of passing binary data into a storedprocedure through a parameter array I'd appreciate it.
View 1 Replies View RelatedBinary Primary Key
Sep 14, 2007I have a 256 bit hash value to identify chunks of data. Would it be a good idea to create a 32-byte binary field as the primary key or encode the bytes to a string?
View 1 Replies View RelatedConverting Binary To String
Jun 27, 2006is there a way to query a varbinary column and print the readable char in query analyzer?
thanks in advance!
Ryan
How To Read Binary File
Jun 6, 2007Need help reading a binary file see below for details...
I have uploaded a csv file into a sql table.
Now i want to extract the data and insert the data in the csv file into another sql table.
What commands can i use in sql to extract/ read the data ?
Binary Data Type
Jul 24, 2007Hello Dears,
I was Maked Pictures table in sql server 2005 with Two Col pic_Id (int) and picture(binary)
i need now ro insert image into this table by Asp.net under VB.net Code i have in my form FileUpload and button control
and this is my CodeProtected Sub btnLoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoad.Click
Dim content() As Byte = ReadBitmap2ByteArray(FileUpload1.PostedFile.ContentType)
StoreBlob2DataBase(content)
End SubFunction ReadBitmap2ByteArray(ByVal FileName As String) As Byte()
Dim image As Drawing.Bitmap = New Drawing.Bitmap(FileName)Dim stream As IO.MemoryStream = New IO.MemoryStream()
image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)Return stream.ToArray
End Function
Sub StoreBlob2DataBase(ByVal content As Byte())
con.Open()Dim cm As New SqlCommand("Insert into Pictures(Pic_Id,picture) values(@id,@pic)", con)
cm.Parameters.AddWithValue("@ID", 1)Dim p1 As New SqlParameter
p1.ParameterName = "@pic"
p1.SqlDbType = Data.SqlDbType.Binary
p1.Value = content
p1.Size = content.Length
cm.Parameters.Add(p1)
cm.ExecuteNonQuery()
End Sub
when i make run to my website it give me an Exeption in this statement
Dim image As Drawing.Bitmap = New Drawing.Bitmap(FileName)
it tell me that Parameter is not valid
i need help about this please
with my Best regard
khalil
Binary Data And Sql Server
Sep 20, 2007Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated.
Thanks Jerry
oSQLConn.Open()Dim myreader As SqlDataReader
myreader = myCommand.ExecuteReader
Response.Expires = 0
Response.Buffer = True
Response.Clear()
Do While (myreader.Read())
Response.ContentType = ("application/pdf")Response.BinaryWrite(myreader.Item("img_content"))
Loop
Binary Files In DB : Set Name On Retrieve
Nov 2, 2007Hello, I have a table that stores binary files. When I serve them up to the user, I call the following page (serveDocument.aspx?DocumentID=xxx) which holds only the following code and pass the document ID: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim DocumentID As Integer = Convert.ToInt32(Request.QueryString("DocumentID")) 'Connect to the database and bring back the image contents & MIME type for the specified picture
Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("kelly_lmConnectionString1").ConnectionString) Const SQL As String = "SELECT [Content_Type], [Document_Data] FROM [lm_Service_Detail_Documents] WHERE [Document_id] = @Document_id"
Dim myCommand As New SqlCommand(SQL, myConnection) myCommand.Parameters.AddWithValue("@Document_id", DocumentID) myConnection.Open() Dim myReader As SqlDataReader = myCommand.ExecuteReader If myReader.Read Then
Response.ContentType = myReader("Content_Type").ToString() Response.BinaryWrite(myReader("Document_Data")) End If
myReader.Close()
myConnection.Close()
End Using End Sub It works perfectly. But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'. Is there any way to inject the filename that I want to save the document as? Thank you.
Write Binary From Sql To File In Vb.net
Mar 17, 2008Hi,
I need help please.
I have a image field in SQL called AttachData.
I need to retrieve the information and save it on my computer.
I tried the following but it fails on the line 7.
How can I write the data from sql to my computer?
Here is my table in sql:
CREATE TABLE [dbo].[MailAttachment](
[MsgID] [int] NOT NULL,
[AttachName] [nvarchar](255) COLLATE Hebrew_CI_AS NULL,
[AttachType] [nvarchar](255) COLLATE Hebrew_CI_AS NULL,
[AttachSize] [int] NOT NULL DEFAULT (0),
[AttachCompSize] [int] NOT NULL DEFAULT (0),[AttachData] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]1 Dim s As New _
2 System.IO.FileStream("c:emails" _
3 & "" & AttachName.ToString, System.IO.FileMode.CreateNew,System.IO.FileAccess.Write)
4
6
7 ' s.Write(CType(AttachData, Byte()), 0, CInt(AttachSize))
8
10
11 s.Close()
Retrieiving Binary Data
Mar 29, 2008
Hello All, I am using a stored procedure. In that stored procedure I am passing the name of a table as an argument. and after executing that procedure, it gives me the 'Insert' query of each and every row. So if I have 10 records, then the procedure will give me 10 'Insert' queries with its table values. There is a image datatype in last column of my table. and i am not able to fetch that binary data of that table. Can anybody tell me what is the problem with my stored proce. given below :: ALTER PROCEDURE [dbo].[SP_Generate_Insert_Statements_Testing]
@strTableName varchar(128), -- used to specify the table to generate data for
@RequiredGo bit = 0 -- used to allow GO statements to separate the insert statements
AS
--Variable declarations
DECLARE @InsertStmt varchar(max)
DECLARE @Fields varchar(8000)
DECLARE @SelList varchar(max)
DECLARE @ColName varchar(128)DECLARE @IsChar tinyint
DECLARE @tableData varchar(8000)
DECLARE @strImageSQL varchar(200)
DECLARE @strTextSQL varchar(200)
DECLARE @ImageData varbinary(8000)
DECLARE @TextData varchar(max)
DECLARE @bitIdentity BIT
SET NOCOUNT OFF
SELECT @bitIdentity = OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_Name =@strTableName
PRINT '---- ** Start of Inserts ** ----'
PRINT ''
IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName +'] ON '
END
--initialize variables
SELECT @InsertStmt = 'INSERT INTO [' + @strTableName + '] (',
@Fields = '',
@SelList = 'SELECT '
--create a cursor that loops through the fields in the table
--and retrieves the column names and determines the delimiter type that the field needs
DECLARE CR_Table CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME,
'IsChar' = CASEWHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timestamp') THEN 0
WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uniqueidentifier', 'nchar') THEN 1WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
WHEN DATA_TYPE in ('text', 'ntext') THEN 3WHEN DATA_TYPE in ('sql_variant') THEN 4
WHEN DATA_TYPE in ('image') THEN 5ELSE 6
END
FROM INFORMATION_SCHEMA.COLUMNS c WITH (NOLOCK)
INNER JOIN syscolumns sc WITH (NOLOCK)
ON c.COLUMN_NAME = sc.name
INNER JOIN sysobjects so WITH (NOLOCK)ON sc.id = so.id
AND so.name = c.TABLE_NAMEWHERE table_name = @strTableName
AND DATA_TYPE <> 'timestamp'AND sc.IsComputed = 0
ORDER BY ORDINAL_POSITIONFOR READ ONLYOPEN CR_Table
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
WHILE (@@fetch_status <> -1)
BEGIN
IF @@fetch_status <> -1
BEGINIF @SelList = 'SELECT'
BEGIN
SELECT @Fields = @Fields + '[' + @ColName + ']' + ', 'SELECT @SelList = CASE @IsChar
WHEN 1 THEN @SelList + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL'') ' + ' COLLATE database_default + '
WHEN 2 THEN @SelList + 'ISNULL('''''''' + CONVERT(VARCHAR(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + '
WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + '
--WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR(MAX),['+ @ColName + ']) '''
WHEN 4 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + '
WHEN 5 THEN @SelList + '''MyImageData'''''
ELSE @SelList + ' ISNULL(CONVERT(VARCHAR(2000),['+ @ColName + '],0),''NULL'')' + ' COLLATE database_default + ' END
END
ELSE
BEGIN
SELECT @Fields = @Fields + '[' + @ColName + ']' + ', 'SELECT @SelList = CASE @IsChar
WHEN 1 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL'') ' + ' COLLATE database_default + '
WHEN 2 THEN @SelList + ''',''' + ' + ' + 'ISNULL('''''''' + CONVERT(VARCHAR(20),['+ @ColName + '])+ '''''''',''NULL'') ' + ' COLLATE database_default + '
WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + '
--WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR(MAX),['+ @ColName + ']) '''
WHEN 4 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(CONVERT(VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_default + '
WHEN 5 THEN @SelList + ''',''' + '''MyImageData'''''
ELSE @SelList + ''',''' + ' + ' + ' ISNULL(CONVERT(VARCHAR(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_default + ' END
ENDIF @IsChar = 5
SET @strImageSQL = 'SELECT ' + @ColName + ' FROM ' + @strTableName
END
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
CLOSE CR_TableDEALLOCATE CR_Table
SELECT @Fields = SUBSTRING(@Fields, 1,(len(@Fields)-1))
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @strTableNameSELECT @InsertStmt = @InsertStmt + @Fields + ')'
SET NOCOUNT ON
--now we need to create and load the temp table that will hold the data
--that we are going to generate into an insert statement
CREATE TABLE #TheData (TableData varchar(MAX))
INSERT INTO #TheData (TableData) EXEC (@SelList)
IF @strImageSQL <> ''
BEGIN
CREATE TABLE #ImageData (TableData image)
INSERT INTO #ImageData (TableData) EXEC (@strImageSQL)
END
--Cursor through the data to generate the INSERT statement / VALUESDECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @tableDataIF @strImageSQL <> ''
BEGIN
DECLARE CR_ImageData CURSOR FAST_FORWARD FOR SELECT TableData FROM #ImageData FORREAD ONLY
OPEN CR_ImageDataFETCH NEXT FROM CR_ImageData INTO @ImageData
END
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)BEGIN
IF @strImageSQL <> ''
BEGINPRINT @InsertStmt
PRINT 'VALUES '
PRINT '('
PRINT SUBSTRING(@tableData,1,CHARINDEX('MyImageData',@tableData)-2)PRINT @ImageData
PRINT SUBSTRING(@tableData,CHARINDEX('MyImageData',@tableData) + 12,LEN(@tableData))
PRINT ')' + CHAR(13)
END
ELSE
PRINT @InsertStmt + ' VALUES (' + @tableData + ')' + CHAR(13)
IF @RequiredGo = 1
PRINT 'GO'
ENDFETCH NEXT FROM CR_Data INTO @tableData
IF @ImageData <> '' FETCH NEXT FROM CR_ImageData INTO @ImageData
-- IF @TextData <> ''
-- FETCH NEXT FROM CR_TextData INTO @TextData
END
CLOSE CR_Data
DEALLOCATE CR_DataIF @ImageData <> ''
BEGINCLOSE CR_ImageData
DEALLOCATE CR_ImageDataEND
IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName + '] OFF '
END
PRINT '---- ** End of Inserts ** ----'
RETURN (0)
=========== End of Procedure =======================
Any help will be appreciated.
Retrieving Binary Image From SQL
Jul 21, 2005i am trying to retrieve an image blob stored in SQL to a web matrix form (using VB.NET). I am totaly new to this and i got the code below from a site but it doesn't see to work.....
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.IO" %>
<%@ import Namespace="System.Drawing.Image" %>
<%@ import Namespace="System.Drawing.Bitmap" %>
<%@ import Namespace="System.Drawing.Graphics" %>
<%@ import Namespace="System.Web.UI.WebControls" %>
<%@ import Namespace="System.Data.Odbc" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Private Function GetImageFromDB(ByVal ImageID As Integer) As System.Drawing.Image
' Get the Connection string
Dim strConnection As String
strConnection = "server='(local)'; trusted_connection=true; database='mydatabasename'"
Dim conn As New SqlConnection(strConnection)
Dim sqlCommand As String
Dim strImage As String
Dim image As System.Drawing.Image
Try
sqlCommand = "SELECT ImageField FROM ImageTable WHERE MyImageId = " + ImageID.ToString
Dim cmd As New SqlCommand(sqlCommand)
cmd.Connection = conn
conn.Open()
Dim dr As SqlDataReader
dr = cmd.ExecuteReader()
While (dr.Read())
Dim byt As Byte()
byt = dr.Item(strImage)
Dim bmp As New System.Drawing.Bitmap(New System.IO.MemoryStream(byt))
image = bmp
End While
Catch ex As Exception
' Log Error
Response.Write(ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
Return image
End Function
Private Sub Page_Load (ByVal sender As System.Object, ByVal e As System.EventArgs)
If Not IsPostBack Then
GetImageFromDB(2111)
End If
End Sub
</script>
<html>
<head>
</head>
<body>
<form enctype="multipart/form-data" runat="server">
<asp:Image id="BrandSignImg" runat="server"></asp:Image>
</form></body></html>
Binary Data Type
Jan 23, 2006I am trying to store a byte array in a database. I want to use binary to store the data but I am confused about the type. The byte array I am trying to store is a password hash from SHA512.
I hash a plain text value then store the result in a byte array. I then want to store the byte array in the database as binary but I am confused when its asking for the size of the binary field. In nvarchar a size of 2 would mean 2 characters.
How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently?
The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated.
I need to make sure the size of my binary field will hold the largest possible password hash, but I don't want it too large so its never completely used.
How is this data stored in the binary field, and what size binary field should I choose to make sure there are no problems with the password hash being truncated, yet making sure I'm not just wasting by creating a field thats too large.
Thanks!
Upload File To A DB In Binary
Apr 20, 2006Hi have done the following but get the errors at the end, any ideas
Created a stored procedureALTER PROCEDURE ulfile@upload varchar(200)ASINSERT INTO results(@upload)VALUES (@Upload)RETURN after sitting for two hours I realised I had a comma after (200) then the following in the .cs file using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class Default3 : System.Web.UI.Page{//PostedFile Property of Type HTTPPostedFileint intDocLen = txtFileContents.PostedFile.ContentLength;byte[] Docbuffer = new byte[intDoclen];protected void Page_Load(object sender, EventArgs e){ Stream objStream;//InputStream://Gets a Stream object which points to an uploaded Document; //to prepare for reading the contents of the file.objStream = txtFileContents.PostedFile.InputStream;objStream.Read(Docbuffer, 0, intDocLen);//results is the connection objectcmdUploadDoc = new SqlCommand("ulfile", results);cmdUploadDoc.CommandType = CommandType.StoredProcedure;//Add the Params which in the Stored Proc cmdUploadDoc.Parameters.Add("@upload ", SqlDbType.VarChar, 200);//Set Params ValuescmdUploadDoc.Parameters[0].Value = txtTitle.Text;//Set the "@Doc" Param to be Docbuffer byet ArraycmdUploadDoc.Parameters[1].Value = Docbuffer;cmdUploadDoc.Parameters[2].Value = strDocType; }private void btnSubmit_Click(object sender, System.EventArgs e){string strDocExt;//strDocType to store Document type which will be Stored in the Databasestring strDocType;//Will be used to determine Document lengthint intDocLen;//Stream object used for reading the contents of the Uploading DocumnetStream objStream;SqlConnection results;SqlCommand cmdUploadDoc;if(IsValid){if(txtFileContents.PostedFile != null){//Determine File TypestrDocExt = CString.Right(txtFileContents.PostedFile.FileName,4).ToLower();switch(strDocExt){case ".doc":strDocType = "doc";break;case ".ppt":strDocType = "ppt";break;case ".htm":strDocType = "htm";break;case ".html":strDocType = "htm";break;case ".jpg":strDocType = "jpg";break;case ".gif":strDocType = "gif";break;case ".im":strDocType = "im";break;default:strDocType = "txt";break;}//Grab the Content of the Uploaded DocumentintDocLen = txtFileContents.PostedFile.ContentLength;//buffer to hold Document Contentsbyte[] Docbuffer = new byte[intDocLen];//InputStream://Gets a Stream object which points to an uploaded Document; //to prepare for reading the contents of the file.objStream = txtFileContents.PostedFile.InputStream;//Store the Content of the Documnet in a buffer//This buffer will be stored in the DatabaseobjStream.Read(Docbuffer ,0,intDocLen);//Add Uploaded Documnet to Database as Binary//You have to change the connection stringBooksConn = new SqlConnection("Server=66.179.84.110;UID=******;PWD=******;Database=******");//Setting the SqlCommandcmdUploadDoc = new SqlCommand("ulfile",results);cmdUploadDoc.CommandType = CommandType.StoredProcedure;cmdUploadDoc.Parameters.Add("@upload ",SqlDbType.VarChar,200);cmdUploadDoc.Parameters[0].Value = txtTitle.Text;cmdUploadDoc.Parameters[1].Value = Docbuffer ;cmdUploadDoc.Parameters[2].Value = strDocType;Results.Open();cmdUploadDoc.ExecuteNonQuery();Results.Close();}//End of if(txtFileContents.PostedFile != null)}//End Of if(IsValid)}//End of Method btnSubmit_Click} And the form code <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head id="Head1" runat="server"><title>Untitled Page</title></head><body><form id="frmUpload" method="post" enctype="multipart/form-data" runat="server"><span>Title</span><asp:textbox id="txtTitle" runat="server" EnableViewState="False"></asp:textbox><asp:requiredfieldvalidator id="valrTitle" runat="server" ErrorMessage="* Required" ControlToValidate="txtTitle">* Required</asp:requiredfieldvalidator><span>Document to Upload</span><input id="txtFileContents" type="file" runat="server" name="txtFileContents"></></><asp:button id="btnSubmit" Text="Submit" Runat="server" OnClick="btnSubmit_Click"></asp:button> </form></body></html> however when I try and view in browser I get the following Line 21:
Line 22:
Line 23: Stream objStream;
Line 24: //InputStream:
Line 25: //Gets a Stream object which points to an uploaded Document; I have trawlled the net, MSDN and anywhere else I can find to see what it is and nothing. eventually changed Stream.objstream to System.IO.Stream objStream; And now getting the following error Line 29:
Line 30: //results is the connection object
Line 31: cmdUploadDoc = new SqlCommand("ulfile",results);
Line 32: cmdUploadDoc.CommandType = CommandType.StoredProcedure;
Line 33: //Add the Params which in the Stored Proc Can anyone help?
Whoops, Binary To Smalldatetime ??
Apr 19, 2002I converted a field from smalldatetime to binary. Is it possible to convert back to smalldatetime?
Thanks