Im having a problem with a database getting full and would like to export a column with binary data. The binary data belongs to different types of files (pictures, txt-files etc) attached to tickets in an error reporting system. We would like to create a directory named as the issue ID and dump the file(s) corresponding to the issue there. For example C:23-1005Errormessage.txt
The database is called MSDE (some kind of SQL light.. ?). Is there a simple way of doing this?
I'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?
Hi All, I recently created my entire site before even starting to look at hosting providers. It's a very complex ASP.NET e-Commerce site built on SQL Server. I've (nearly) got it to a point where I want it to go live. I found a host that caters for all my needs and is a good price. What I need to do now is migrate all my data, as well as all my tables and stored procedures, into the database with my host. Is there an easy wy to go about this? - Mina
When 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...
When 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.
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.
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)
Hello, 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>?
We 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:
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."
How 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.
Hi 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,,,
Hello 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
Here 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
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
--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
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.
I 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!
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.
Hi 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.
Hi 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
' 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
Hi, 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 .
I 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
I 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.
hi 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?
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
I 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.
I 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()
I'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?
Hi 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
I 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?
Hi - 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
