and after running each of them seams that the file have been saved; but I can not retrive the files. I have tried some solutions from msdn but inside the created file is empty. the point that i really look for it is to just working with memory not in the disk before saving. and then retriving each field that I want.
hi I have used the following code (mostly created by MSDN) to retrive a binary field from SQL database. it works but I have extra space between characters. for example if I save a text file with "Hello world" text, after retriving I have it like "H e l l o w o r l d". what is the problem?????? I am really looking forward your answers private void retrive() { public void a() { SqlConnection connection = new SqlConnection("Some Connection string"); SqlCommand command = new SqlCommand("Select * from temp", connection); // Writes the BLOB to a file FileStream stream; // Streams the BLOB to the FileStream object. BinaryWriter writer; // Size of the BLOB buffer. int bufferSize = 50; // The BLOB byte[] buffer to be filled by GetBytes. byte[] outByte = new byte[bufferSize]; // The bytes returned from GetBytes. long retval; // The starting position in the BLOB output. long startIndex = 0; // Open the connection and read data into the DataReader. connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess); while (reader.Read()) { // Create a file to hold the output. stream = new FileStream( "C:\file.txt", FileMode.OpenOrCreate, FileAccess.Write); writer = new BinaryWriter(stream); // Reset the starting byte for the new BLOB. startIndex = 0; // Read bytes into outByte[] and retain the number of bytes returned. retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize); // Continue while there are bytes beyond the size of the buffer. while (retval == bufferSize) { writer.Write(outByte); writer.Flush(); // Reposition start index to end of last buffer and fill buffer. startIndex += bufferSize; retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize); } // Write the remaining buffer. if (retval != 0) writer.Write(outByte, 0, (int)retval - 1); writer.Flush(); // Close the output file. writer.Close(); stream.Close(); } // Close the reader and the connection. reader.Close(); connection.Close(); } }
I want to have an SSIS package that processes a file in the normal insert, update style. But at the end I want to store the file as a binary field to another table for archive purposes. I am having trouble finding a good way to do this. Any samples, ideas, or articles would be appreciated.
This may be a stupid question but I can't find an easy answer for what Iwant to do. I need a blob data field that can store both binary and textdata like the sql_variant field. I am using vb.net to populate a sql table.I want to store both string and binary data.
Hello all, I am exporting data from AS/400 into an xml file and then importing the data from the xml file into an SQL database. I am getting the error: "The statement has been terminated. String or binary data would be truncated" when there are characters like "," or "&" in the xml. How can i solve this problem? Almost all my records have characters like that because they include comments and i can't disallow the users from entering these characters (especially ","). Thanks,Mike.
hai In my web application i want to bind data from sql 2005 to ultrawebgrid, when i use source code dim cmdselect as sqlcommand dim cmdstring as string cmdstring="select name, datefieldname from tablename" cmdselect=new sqlcommand(cmdstring,connectionstring) connectionstring.open() ultrawebgrid1.datasource=cmdselect.executenonquery() ultrawebgrid1.databind() connectionstring.close() ----- when i execute above coding i am geting date and time displayed there, but i want to display date alone --my datefield datatype is datetime - i am using sql 2005,(vs2005-vb/asp.net) please help me thanks in advance
Hey everyone I've got this question that has me stuck for the last few days but its an important part of my website.....What I am trying to do is basicly have a user be able to upload a file, have that uploaded file plus some other info automaticly display on other parts of my site, and have a different user eventually be able to download that file....I have thought about allowing the file upload as a BLOB but still cannot find a proper way to execute this using VB, plus I have heard that this way of doing it is not reccommeneded cause databases were not designed to store large files like this, lots of articles recommened having the file upload to a Folder on your server then get the binary data for the file that can be placed in a database to refrence that particular file.....Well this also proves to be a lot harder then said here is what I got so far (written in C#) protected void UploadBtn_Click(object sender, EventArgs e) { if (FileUpLoad1.HasFile) {
FileUpLoad1.SaveAs(@"C:Documents and SettingsAdamMy DocumentsVisual Studio 2005ProjectsWebsiteFiles" + FileUpLoad1.FileName); Label1.Text = "File Uploaded: " + FileUpLoad1.FileName; } else { Label1.Text = "No File Uploaded."; } } and here is the asp part of the code that goes with it<asp:FileUpLoad id="FileUpLoad1" runat="server" />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label> Now from what I know is I need to get the binary of the file which I have read you can do with the Page.Request.Files statement but again not sure how I would impliment this. Does anyone have any suggestions on which way I should take when dealing with this should I try and just use the BLOB method or use the binary refrence method? and if so how would I impliment this, heck even some good tutorials on the subject would be great... Thanks.....Adam
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
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?
I'm working on a report to show financial transactions from a table over a certain period. For most transactions there is a PDF document that is stored in a separate table in a binairy format. In my report I would like to include a link on every line with transaction information in the report that opens the PDF that is linked to that transaction. Just to be clear, I don't want to embed the PDF in the report but I want the users of the report to have the option to view the PDF that is related to that transaction in their standard pdf reader (adobe).
Code to do the following:
Once a user clicks on the link to view the PDF I need the code to get the binairy data of the PDF file from the table, convert it back to a PDF and open it in the default pdf reader (for example adobe reader). If it can't directly open the file then it's maybe possible to activate the 'open or download' pop up that you also get when you download something from a website.
Application is ocr'ing tiff image files and then storing the resultant text data in a text field in SQL 2005 database. This field is then used with the full text catalog.
All works fine,
However, am I using the correct field type to store the text files for efficiency and space saving?
If I use a binary field, does this reduce the size of the database by compressing the text data in the binary field? Also, is there a limit as to the size of text file that I could store in a binary field?
It would be good to get feedback on this before I go too far down the wrong road.
So, text field to store the text data or binary field to store the actual text file?
Good day., please help me,in a formview control, i set it in Insert Mode, so it should display info from table 1 but when i click on the insert button, it will insert it in table 2.btw, table 1 and table 2 are in the same database?? how about if they are not in the same database?how?please help me,Thanks.,SALAMAT PO.,
Code Block SELECT tce.TimeCardID, tce.TimeCardExpenseID, tc.DateCreated, e.LoginID, e.FirstName + ' ' + e.LastName AS FullName, tce.ExpenseAmount, tce.ExpenseDescription, op.ProjectName, op.ProjectDescription, ec.ExpenseCode FROM OPS_TimeCards tc JOIN OPS_Employees e ON e.EmployeeID = tc.EmployeeID JOIN OPS_TimeCardExpenses tce ON tc.TimeCardID = tce.TimeCardID Join OPS_Projects op ON op.ProjectID = tce.ProjectID Join OPS_ExpenseCodes ec ON ec.ExpenseCodeID = tce.ExpenseCodeID WHERE e.LoginID = 'jross' ORDER BY tc.DateCreated DESC
this query returns me the correct data....but i need to tweak the query so it does not duplicate rows....My tce.TimeCardID is a PK in its table and so is TimeCardExpenseID...but the problem is U can have many TimeCardExpenseID's for one timecard so my results look like
TimeCardID TimeCardExpenseID 1 2 1 3 1 4
I want my query to return the "TimeCardID" but i just want that one ID to represent all the TimeCardExpenseID's...but i can not get it to work and have no clue....
Hi, I have asked this question on 3 forums now and never get an answer, I don't know what is so hard about this question but I will try it here. I am using SHA512 in C# to convert a password and its salt to hashed. I need to store the password hash and the salt hash in the database in two fields. I was told to use binary field to store the hash data and that the output of SHA512 would ALWAYS be the same no matter how long the password is. I modified this hash example to use only SHA512 and to work with byte array instead of plain text. All I need to know now is what size I need to make my binary field to hold this password that is hashed. http://www.obviex.com/samples/Code.aspx?Source=HashCS&Title=Hashing%20Data&Lang=C%23 Say I have a password which is 30 characters max, and a salt which is 16 characters max. The password and the hash are stored in seperate fields in the same table. They are both hashed using SHA512 and are both being stored as byte arrays in C#, what size to I need to make the binary data type in order to hold the password, and to hold the salt. Thanks!
Can anyone point me in the right direction to find documentation for the problem below?I need to store and retrieve ten fields of 16-bits each for testing 16 true-false conditions (a total of 160 bits in each record) so I think I'd like to use ten 2-byte binary fields (160 "bit" fields would be quite unmanageble, if even possible [I think there is some kind of limit to the number of fields in a single record]). I'm not quickly finding in the SQL Server's online documentation how to test for, use and update binary fields. I'll keep looking, but can anyone point me in the right direction? I'm using VB, if that makes any difference.
I have a locally installed MS SQL server 2000. I am trying to retrieve the contents of the table onto a datagrid. But when I use the user name and password, It gives the following errorSystem.Data.SqlClient.SqlException: Login failed for user 'aroop'. Reason: Not associated with a trusted SQL Server connection. string connectionString = "server=(local); uid=aroop; pwd=abcdef; database=mydb"; string commandString = "select * from mytable"; SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, connectionString); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); dgEmployees.DataSource = dataSet; dgEmployees.DataBind();Can someone please let me know what to do about this?
i need to store a bmp or zipped file in a field of a ms sql db. i read the file using vb6 o.net, and in my mind i think sto store it in binary mode. the files could be more of 12 mega. whitch kind of field a could use?
Problem importing data from flat file into decimal(9,2) field. The data in the flat file is 000001453 and I am copying it to a decimal(10,2) field and instead of showing up in the 0000014.53 it comes across as 0001453.00. I tried defining the input columns a few different ways but none seemed to work. How do I do this with SSIS or do I need to write a SP and use convert? Thanks.
Need 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 ?
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()
Hi 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?
I'm trying to easily change a value in 80 ssis configuration files from "localhost" to "myservername". I downloaded WinGrep to do this, but it's balking on some of the files, saying that they are binary files.
Does anyone know how to make the *.dtsconfig files NOT binary in SSIS?
Any suggestions GREATLY appreciated.
I won't go into the reason I have my servername in 80 places...
I have a SSIS package that opens an xml file, puts the contents into a string, then runs a stored procedure that dumps it into an xml column in a table. One of the xml files is huge. Putting the data into a ssis string causes an error. The length of the string variable is 58,231,886. The file will only get bigger.
How else can I get this data into a SQL Server XML field.
hello! I am curious to know how I can find the length of a binary in Sql Server. The length doesn't work on binaries. ie select length(binary) from Binarytable
I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected. The folder remains empty after running this query.