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
ADVERTISEMENT
SqlServer 2005 String Or Binary Data Would Be Truncated When Data Is OK
Feb 21, 2006When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.
The scenario is as follows:
Create table:
create table test3 (
name varchar (18) ,
tbname varchar (18)
)
Create and populate table:
create table maxtable (
tablename varchar (18) not null,
[...]
)
Try to insert into test3:
insert into test3 (name, tbname)
select i.name, o.name
from dbo.sysindexes i, sysobjects o, maxtable m
where i.indid > 0 and i.indid < 255
and i.id = o.id and i.indid = 1
and o.name = lower(m.tablename)
And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.
How To Compare Binary And Int Colns
Jul 23, 2005I need to compare and update a Binary(8) coln with values from anInt(4) coln. Could someone please explain how to do this..??thanksSunit
View 5 Replies View RelatedT-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 RelatedBinary Tree Problem In SQLServer
Feb 8, 2007Hi Members
I m new in this forum, I have have Binary Tree database in SQL Server with
table fields(Mcode,Mname,LeftMCode,RightMCode). i want to display downline and pairs of each member.
can any one help me out?
Regards
Amirbkk
Compare String Value In Sqlserver 2005
Apr 24, 2008CREATE FUNCTION [dbo].[Compare]
(
@usrVal varchar(100),
@dbVal varchar(100)
)
RETURNS BIT
AS
BEGIN
DECLARE @flag BIT
DECLARE @userStrLen INT
DECLARE @dbStrlen INT
DECLARE @counter INT
--Default assignments
SET @counter=1
Set @userStrLen=len(@usrVal)
Set @dbStrlen=len(@dbVal)
IF (@userStrLen != @dbStrlen)
BEGIN
return 0
END
ELSE
BEGIN
WHILE @counter <= @userStrLen
BEGIN
IF (substring(@usrVal,@counter,1) != substring(@dbVal,@counter,1))
RETURN 0
SET @counter = @counter + 1
END
return 1
END
RETURN @flag
--SELECT dbo.Compare('sqlserver user defined functions','sqlserver is the best backend')
END
Problem Unicode Data 0x2300 In SQLServer 2000 SQLServer 2005 Express
Sep 20, 2006Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
Ntext Over 4000 Chars Causes 'Data In Row (n) Was Not Update... String Or Binary Data Would Be Truncated...'
Oct 18, 2006When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
View 7 Replies View RelatedString Or Binary Data Would Be Truncated. I Get This Error When Entering Data Using Sql Server Management Studio Express.
May 21, 2008String or binary data would be truncated. I get this error when entering data using sql server management studio express.
I am not running a sql to insert or update the table. This is through the EDI.
The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.
Any ideas?
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>
Binary 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>?
Binary 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 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 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
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.
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!
CHECKSUM() Of Binary Data
Mar 19, 2004Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
Karam
Insert Binary Data
Jan 4, 2008Hi my first post so be gentle :D
I have a table which has a column of type binary(200)
I want to put some test data into this table just using sql server managment studio express
how do i do this i keep getting message which says
"the changed value in this cell was not recognized as valid .NET framework data type: Byte[]"
Thanks,
Adam
String Or Binary Data Would Be Truncated
Aug 9, 2006Hi Folks,
After I inserted a row in my Database (row 27) I started getting this error when I try to insert, update or delete the record in the database.
I've searched about the error on google and it says that I should have a field that crossed the limit of characters.
I have only the autoincrement field, two varchar fields and a text field, neither one of the varchar fields crossed the limit, they arent even close. I found in google that one solution would be turn the field that is having problems in a text field, but the only field that actually can be causing the problem already is of the text type.
The exact error I get on VS 2005 when trying to change something in the row 27 is:
"No row was updated.
The data in row 27 was not committed.Error Source: .Net SqlClient Data Provider.Error Message: String or binary data would be truncated.The statement has been terminated.
Corret the errors and retry or press ESC to cancel the change(s)."
I need some help guyz, see ya, hugs.
String Or Binary Data Would Be Truncated.
Jan 21, 2007Hi Guys, I'm trying to save the data into 2 table when i click the button. But it pops out this error: String or binary data would be truncated. The statement has been
terminated. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: String or binary data would be
truncated. The statement has been terminated.Source Error:
Line 116:Line 117: Sqlinsert.Connection.Open()Line 118: Sqlinsert.ExecuteNonQuery()Line 119:Line 120: Sqlinsert.Connection.Close() I Don't know what it means so i paste my codes regarding the button & the redline Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
'add the data into shopper
Dim strsqlcmd As String
Dim strName, straddress As String
Dim strContact, strEmail, strPw As String
Dim strIC As String
strName = txtName.Text
straddress = txtAdd.Text
strEmail = txtEmail.Text
strContact = txtboxContact.Text
strIC = txtIC.Text
strsqlcmd = "Insert Into Shopper (Name, Address, Contact, Email, IC) Values (@Name, @Address, @Contact, @Email, @IC)"
SqlCommand1.CommandText = strsqlcmd
With SqlCommand1.Parameters
.Add("@Name", strName)
.Add("@Address", straddress)
.Add("@Contact", strContact)
.Add("@Email", strEmail)
.Add("@IC", strIC)
End With
SqlCnt.Open()
SqlCommand1.ExecuteNonQuery()
SqlCnt.Close()
'add the data into the presc
strsqlcmd = "Insert Into Prescription (Name, Address, Contact, Email, IC) Values (@Name, @Address, @Contact, @Email, @IC)"
Sqlinsert.CommandText = strsqlcmd
With Sqlinsert.Parameters
.Add("@Name", strName)
.Add("@Address", straddress)
.Add("@Contact", strContact)
.Add("@Email", strEmail)
.Add("@IC", strIC)
End With
Sqlinsert.Connection.Open()
Sqlinsert.ExecuteNonQuery()
Sqlinsert.Connection.Close()
' go the add item
Response.Redirect("NewPrescItem.aspx") why my sqlinsert.excutenonquery will have this error? and what is this error really means? Thanks in advance
Problem In Inserting Binary Data In Sql;
Feb 24, 2007Hi,
I am facing problem inserting binary data into sql data type varbinary.
I want to save an object data type in var binary data type into sql.It gives a followin error.
The name 'DocumentData' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
I do not use ASP Upload file.
Any type of help .
Thanks in advance
Friend
String Or Binary Data Would Be Truncated
Sep 12, 2007I am currently developing a simple program that will upload ms-word documents to a database so users can view them within the department.
The program worked prefectly while using sql 2005 with the these fields:
FormId intFileName nvarchar(50)FileBytes varbinary(Max)
This was done in unit testing, now in system testing there is a difference.
A slight downgrade in the database and server software!
We are using sql 2000 on a 2000 server and the FileBytes datatype had to change to the following:
FormId intFileName nvarchar 50 FileBytes varbinary 8000
There is no 'Max' option in sql 2000 for the datatype varbinary.
So when the insert is perform, we get this error message:
String or binary data would be truncated.The statement has been terminated.
I am guessing it has something to do with FileBytes, because this is the only thing that changed.
I just don't know how to solve the problem.P.S.The application is in ASP.NET 2.0
Thanks,
xyz789
String Or Binary Data Would Be Truncated.
Oct 8, 2007I keep getting the error "String or binary data would be truncated." when I try to insert data into SQL Sever 2005 from an ASP.net page. Having searched thoughout the web, I know this is generally caused because one of the values being inserted is bigger than the size of the field it's going into. But, I have tested this out in many different ways, and this is not what's causing the problem.
I have tried doing the insert as a SQL statement and using the "ExecuteNonQuery" command. I have also tried running this through a stored procedure. Neither works. In both situations, I have captured the TSQL statements via SQL Profiler and run them in a query window. In both cases, the statements work just fine in a query window.
When I try to do the insert via a stored procedure, all of the statements in the stored procedure show up in SQL Profiler, including the final SELECT statement I have the indicates a successful result. But, the data does not end up in the table and the ASP.net page returns an error.
Also, I have run both the Stored Proceure and the SQL insert statement making all of the fields blank as a way of ensuring that no value can be longer than a field's size. But, in both cases I still get the same error.
Here's the line I use when doing a regular insert:
Dim Command4 = New Data.SqlClient.SqlCommand("INSERT INTO ResourceCenter(UserEmail, UserPassword, Region, FirstName, LastName, Company, JobTitle, Address1, Address2, City, StateProvince, ZipPostalCode, Country, BusinessPhone, WebSiteURL, HowDidYouFind, Industry, WhatTypeOfSolution, CompanySize, NumLogins, LastLogin) VALUES ('" & UserEmail & "', '" & UserPassword & "', '" & Region & "', '" & FirstName & "', '" & LastName & "', '" & Company & "', '" & JobTitle & "', '" & Address1 & "', '" & Address2 & "', '" & City & "', '" & StateProvince & "', '" & ZipPostalCode & "', '" & Country & "', '" & BusinessPhone & "', '" & WebSiteURL & "', '" & HowDidYouFind & "', '" & Industry & "', '', '', 1, " & Now & ")", conn2)
Dim NumRowsUpdated2 = Command4.ExecuteNonQuery()
Here's my code when I call a stored procedure:
Dim dsSignup As New Data.DataSet()Dim Command4 As New Data.SqlClient.SqlDataAdapter("ap_ResourceCenterModify", conn2)Command4.SelectCommand.CommandType = Data.CommandType.StoredProcedureCommand4.SelectCommand.Parameters.Add("@UserID", Data.SqlDbType.Int, 0).Value = 0Command4.SelectCommand.Parameters.Add("@UserEmail", Data.SqlDbType.VarChar, 100).Value = UserEmailCommand4.SelectCommand.Parameters.Add("@UserPassword", Data.SqlDbType.VarChar, 20).Value = UserPasswordCommand4.SelectCommand.Parameters.Add("@Region", Data.SqlDbType.VarChar, 50).Value = RegionCommand4.SelectCommand.Parameters.Add("@FirstName", Data.SqlDbType.VarChar, 100).Value = FirstNameCommand4.SelectCommand.Parameters.Add("@LastName", Data.SqlDbType.VarChar, 100).Value = LastNameCommand4.SelectCommand.Parameters.Add("@Company", Data.SqlDbType.VarChar, 100).Value = CompanyCommand4.SelectCommand.Parameters.Add("@JobTitle", Data.SqlDbType.VarChar, 100).Value = JobTitleCommand4.SelectCommand.Parameters.Add("@Address1", Data.SqlDbType.VarChar, 100).Value = Address1Command4.SelectCommand.Parameters.Add("@Address2", Data.SqlDbType.VarChar, 100).Value = Address2Command4.SelectCommand.Parameters.Add("@City", Data.SqlDbType.VarChar, 100).Value = CityCommand4.SelectCommand.Parameters.Add("@StateProvince", Data.SqlDbType.VarChar, 50).Value = StateProvinceCommand4.SelectCommand.Parameters.Add("@ZipPostalCode", Data.SqlDbType.VarChar, 50).Value = ZipPostalCodeCommand4.SelectCommand.Parameters.Add("@Country", Data.SqlDbType.VarChar, 200).Value = CountryCommand4.SelectCommand.Parameters.Add("@BusinessPhone", Data.SqlDbType.VarChar, 100).Value = BusinessPhoneCommand4.SelectCommand.Parameters.Add("@WebSiteURL", Data.SqlDbType.VarChar, 200).Value = WebSiteURLCommand4.SelectCommand.Parameters.Add("@HowDidYouFind", Data.SqlDbType.VarChar, 100).Value = HowDidYouFindCommand4.SelectCommand.Parameters.Add("@Industry", Data.SqlDbType.VarChar, 100).Value = IndustryCommand4.SelectCommand.Parameters.Add("@WhatTypeOfSolution", Data.SqlDbType.VarChar, 250).Value = WhatTypeOfSolutionCommand4.SelectCommand.Parameters.Add("@CompanySize", Data.SqlDbType.VarChar, 100).Value = CompanySizeCommand4.Fill(dsSignup)Any ideas of what else I can try? Thanks in advance.
Excel Export Binary Data?
Oct 18, 2007hi
i used to export my tables to excel file
but now i have tables which have binary (image) data
what happens to them?
is there any way to backup these data?
String Or Binary Data Would Be Truncated.
Jan 19, 2008 Hi,I am getting the following error:System.Data.SqlClient.SqlException: String or binary data would be
truncated.The statement has been terminated.Can someone tell me what it means?Thanks,Jon
String Or Binary Data Would Be Truncated
Jan 7, 2004I am trying to pass a multi lined text field into my SQL Server database. I tried varchar and set the length to 1000 but I still can only pass upto 50 charcters. I also have tried to use other data types like nvarchar, text, and ntext but the text and ntext won't allow me to chenge the length of the field and n varchar will only allow up to 50 charcters no matter how big I make the length. Any help you can give me would be great.
David
String Or Binary Data Would Be Truncated
Jun 21, 2004I have a stored proc that inserts binary data into an image field.
I am getting a "String or binary data would be truncated" error when trying to insert large data, but this is *no where* near the maximum size of the image datatype.
Should I be declaring the size of the image parameter in my SQLCommand or something?
TIA,
Mark
Code example:
The sproc:
ALTER PROCEDURE sproc_Content_Insert
(
@ContentName varchar(250),
@ContentDesc varchar(500),
@ContentType varchar(25),
@Content image,
@LocationID int
)
AS
INSERT INTO tbl_Content (ContentName, ContentDesc, ContentType, Content, LocationID)
VALUES (@ContentName, @ContentDesc, @ContentType, @Content, @LocationID)
The method:
Public Sub SaveBinaryContent(ByRef ContentName As String, ByRef ContentDesc As String, ByRef ContentType As String, ByRef Content As String, ByRef LocationID As Integer)
'convert the content to a binary stream.
Dim ms As New System.IO.MemoryStream
Dim bf As New BinaryFormatter
bf.Serialize(ms, Content)
ms.Position = 0
Dim oConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlCommand("sproc_Content_Insert", oConn)
With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@ContentName", ContentName)
.Parameters.Add("@ContentDesc", ContentDesc)
.Parameters.Add("@ContentType", ContentType)
.Parameters.Add("@Content", ms.ToArray)
.Parameters.Add("@LocationID", LocationID)
End With
oConn.Open()
cmd.ExecuteNonQuery()
oConn.Close()
oConn.Dispose()
cmd.Dispose()
bf = Nothing
ms = Nothing
End Sub
Reading / Writing Binary Data To Db
Sep 9, 2004I'm trying to read a byte array of an image datatype from sql server, and then to put this in another field in the database. I get a byte array, but somehow the image doesn't get into the db well with the sql parameters. Does anyone have an idea how to tackle this problem?
Thanks a lot, Hugo
String Or Binary Data Would Be Truncated. :((
May 2, 2005Hi all,
when ever i try uploadin the file to the database, i am facing this weird probs :(
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.Source Error:
Line 124:
Line 125:// Update data source
Line 126:dbAdapt.Update(dbSet,"tblFile1");
Line 127:
Line 128:// Get newFileID
i donno what this means :(
well the code is like this
private void Button1_Click(object sender, System.EventArgs e)
{
HttpPostedFile myFile = filMyFile.PostedFile;
// Get size of uploaded file
nFileLen = myFile.ContentLength;
nFiletype = myFile.ContentType;
// Allocate a buffer for reading of the file
myData = new byte[nFileLen];
// Read uploaded file from the Stream
myFile.InputStream.Read(myData, 0, nFileLen);
strFilename = Path.GetFileName(myFile.FileName);
if( myData.Length != 0)
{
WriteToDB(strFilename,nFiletype,ref myData);
}
}
private int WriteToDB(string strName, string strType, ref byte[] Buffer)
{
int nFileID = 0;
// Create connection
SqlConnection dbConn = new SqlConnection("Data Source=D3BKIRAN; Database=ooc;UID=sa;Password=*****");
// Create Adapter
SqlDataAdapter dbAdapt = new SqlDataAdapter("SELECT * FROM tblFile1", dbConn);
// Create and initialize CommandBuilder
SqlCommandBuilder dbCB = new SqlCommandBuilder(dbAdapt);
// Open Connection
dbConn.Open();
// New DataSet
DataSet dbSet = new DataSet();
// Populate DataSet with data
dbAdapt.Fill(dbSet, "tblFile1");
// Get reference to our table
DataTable dbTable = dbSet.Tables["tblFile1"];
// Create new row
DataRow dbRow = dbTable.NewRow();
// Store data in the row
dbRow["FileName"] = strName;
dbRow["FileSize"] = Buffer.Length;
dbRow["ContentType"] = strType;
dbRow["FileData"] = Buffer;
// Add row back to table
dbTable.Rows.Add(dbRow);
// Update data source
dbAdapt.Update(dbSet,"tblFile1");
// Get newFileID
if( !dbRow.IsNull("FileID") )
nFileID = (int)dbRow["FileID"];
// Close connection
dbConn.Close();
// Return FileID
return nFileID;
}
plz any one give me an soln for the same
thanks in advance
OPENXML And Binary/Image Data
Aug 17, 2005I seem to be getting very annoying behaviour from SQL Server and was wondering what the correct method should be...I want to store some binary data in the database and I want to use a stored procedure. I also want to pass some XML to the stored procedure where this XML contains the binary data. I will then use OPENXML to grab the binary data and stick in the table. In order to ensure the safe transmission of the binary through XML I Base64 encode it. Ok so far. The problem is that although OPENXML is supposed to assume that anything marked as Image/Binary will be Base64 encoded it then proceeds to Base64 encode it again and put that result into the database! So what am I missing, am I supposed use a CDATA section or something rather than Base64 encode my XML?
View 2 Replies View RelatedHmm - Unique Id Is In Binary Data - How Do I Query Against It?
May 12, 2006Hi - I've got a table in SQL server that has its unique ID field as binary data. I have a gridview displaying data from this table and I have set the datakey of this gridview as that binary data field.I have a 'Select' ciolumn in my gridview that, when selected, will display more details from the selected record. The problem is, how do I pass the selected id back to sql server bearing in mind that it's binary data? Here's what I'm doing at the moment:I have a function in my data accsess class that queries the database:Public Function getDetailsById(ByVal Id As System.Byte) As DataSet Dim con As New SqlConnection(conStr) Dim cmd As New SqlCommand("SELECT * FROM tableName WHERE Id=@Id", con) cmd.Parameters.Add("@Id", SqlDbType.Binary) cmd.Parameters("@Id").Value = Id Dim ds As New DataSet Dim adp As New SqlDataAdapter(cmd) adp.Fill(ds) Return ds End FunctionAnd then I call this function from my page with the gridview using: Dim da As New myDataAccess Dim ds As New DataSet ds = da.getDetailsById(GridView1.SelectedDataKey.Value)But I get the error:Conversion from type 'Byte()' to type 'Byte' is not valid.Any ideas where my data/code is going wrong? I'm not sure how to pass this sort of data around?Thanks
View 4 Replies View Related