Can any say Is it possible to store a picture in sqlserver?? If it possible then Send me some code in C# to store picture file by browseing from my PC.Also which type of field i have to create to store Picture.
Hi everyone, I have an odd problem. I have a generic upload/download ASP.net page that allows the upload and download of and type of file. I have so far tested the following file types: XLS, MDB, JPG, DLL, EXE, PDF, TXT, SWF, and GIF All of these upload and download fine, EXCEPT PDF's. I have tried 4 different PDF's and all open prior to upload/download, but after uploading and downloading, I get the following (from Adobe Reader) error upon trying to open: "There was an error opening this document. The file is damaged and could not be repaired" Here's my current code: 9 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs)10 11 If e.CommandName = "open" Then12 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)13 Dim sSQL As New StringBuilder14 Dim sqlCmd As SqlCommand15 Dim sqlReader As SqlDataReader16 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte17 18 sSQL.Append(" SELECT * ")19 sSQL.Append(" FROM [survey_document] ")20 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ")21 22 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)23 24 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument)25 26 sqlConn.Open()27 sqlReader = sqlCmd.ExecuteReader28 29 While sqlReader.Read30 31 Response.ContentType = sqlReader("sd_mime_type").ToString()32 Response.BinaryWrite(sqlReader("sd_document"))33 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString())34 End While35 36 sqlReader.Close()37 sqlConn.Close()38 39 End If40 41 End Sub42 43 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click44 45 'Make sure a file has been successfully uploaded46 If UploadedFile.HasFile Then47 48 'Connect to the database and insert a new record into Products49 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString)50 Dim sSQL As New StringBuilder51 Dim sqlCmd As SqlCommand52 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte53 54 'Read the files binary data into a Byte array55 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length)56 57 sSQL.Append(" INSERT INTO [survey_document] ")58 sSQL.Append(" ( sd_document ")59 sSQL.Append(" , sd_title ")60 sSQL.Append(" , sd_file_name ")61 sSQL.Append(" , sd_upload_date ")62 sSQL.Append(" , sd_mime_type ")63 sSQL.Append(" ) VALUES ( @sd_document ")64 sSQL.Append(" , @sd_title ")65 sSQL.Append(" , @sd_file_name ")66 sSQL.Append(" , @sd_upload_date ")67 sSQL.Append(" , @sd_mime_type ")68 sSQL.Append(" ) ")69 70 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn)71 72 sqlCmd.Parameters.AddWithValue("@sd_title", FileTitle.Text.Trim())73 sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType)74 sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName))75 sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now)76 sqlCmd.Parameters.AddWithValue("@sd_document", byteArray)77 78 sqlConn.Open()79 sqlCmd.ExecuteNonQuery()80 sqlConn.Close()81 82 Else83 84 'Either the file upload failed or no file was selected85 86 End If87 88 End SubI have also tried the following code as a result of searching far and wide, trying other peoples methods: 1 Protected Sub ItemCommand_Click(ByVal sender As System.Object, ByVal e As RepeaterCommandEventArgs) 2 3 If e.CommandName = "open" Then 4 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString) 5 Dim sSQL As New StringBuilder 6 Dim sqlCmd As SqlCommand 7 Dim sqlReader As SqlDataReader 8 Dim byteArray(UploadedFile.PostedFile.InputStream.Length) As Byte 9 10 sSQL.Append(" SELECT * ") 11 sSQL.Append(" FROM [survey_document] ") 12 sSQL.Append(" WHERE [sd_document_code] = @sd_document_code ") 13 14 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn) 15 16 sqlCmd.Parameters.AddWithValue("@sd_document_code", e.CommandArgument) 17 18 sqlConn.Open() 19 sqlReader = sqlCmd.ExecuteReader 20 21 While sqlReader.Read 22 23 Dim buffer() As Byte = sqlReader("sd_document") 24 Dim blen As Integer = CType(sqlReader("sd_document"), Byte()).Length 25 26 Response.ContentType = sqlReader("sd_mime_type").ToString() 27 Response.OutputStream.Write(buffer, 0, blen,) 28 Response.AddHeader("Content-Disposition", "attachment;filename=" & sqlReader("sd_file_name").ToString()) 29 End While 30 31 sqlReader.Close() 32 sqlConn.Close() 33 34 End If 35 36 End Sub 37 38 Protected Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click 39 40 'Make sure a file has been successfully uploaded 41 If UploadedFile.HasFile Then 42 43 'Connect to the database and insert a new record into Products 44 Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("data_partsbranding").ConnectionString) 45 Dim sSQL As New StringBuilder 46 Dim sqlCmd As SqlCommand 47 Dim byteArray(UploadedFile.PostedFile.InputStream.Length - 1) As Byte 48 49 'Read the files binary data into a Byte array 50 UploadedFile.PostedFile.InputStream.Read(byteArray, 0, byteArray.Length) 51 52 sSQL.Append(" INSERT INTO [survey_document] ") 53 sSQL.Append(" ( sd_document ") 54 sSQL.Append(" , sd_title ") 55 sSQL.Append(" , sd_file_name ") 56 sSQL.Append(" , sd_upload_date ") 57 sSQL.Append(" , sd_mime_type ") 58 sSQL.Append(" ) VALUES ( @sd_document ") 59 sSQL.Append(" , @sd_title ") 60 sSQL.Append(" , @sd_file_name ") 61 sSQL.Append(" , @sd_upload_date ") 62 sSQL.Append(" , @sd_mime_type ") 63 sSQL.Append(" ) ") 64 65 sqlCmd = New SqlCommand(sSQL.ToString, sqlConn) 66 67 'sqlCmd.Parameters.AddWithValue("@sd_title", PictureTitle.Text.Trim()) 68 'sqlCmd.Parameters.AddWithValue("@sd_mime_type", UploadedFile.PostedFile.ContentType) 69 'sqlCmd.Parameters.AddWithValue("@sd_file_name", System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName)) 70 'sqlCmd.Parameters.AddWithValue("@sd_upload_date", Now) 71 'sqlCmd.Parameters.AddWithValue("@sd_document", byteArray) 72 73 sqlCmd.Parameters.Add(New SqlParameter("@sd_mime_type", SqlDbType.VarChar)) 74 sqlCmd.Parameters.Add(New SqlParameter("@sd_title", SqlDbType.VarChar)) 75 sqlCmd.Parameters.Add(New SqlParameter("@sd_upload_date", SqlDbType.DateTime)) 76 77 sqlCmd.Parameters.Add(New SqlParameter("@sd_file_name", SqlDbType.VarChar)) 78 79 sqlCmd.Parameters.Add(New SqlParameter("@sd_document", SqlDbType.Image)) 80 81 Dim bArray(UploadedFile.PostedFile.ContentLength - 1) As Byte 82 83 84 85 86 87 88 UploadedFile.PostedFile.InputStream.Read(bArray, 0, UploadedFile.PostedFile.ContentLength) 89 90 sqlCmd.Parameters("@sd_mime_type").Value = UploadedFile.PostedFile.ContentType 91 sqlCmd.Parameters("@sd_title").Value = PictureTitle.Text.Trim() 92 sqlCmd.Parameters("@sd_upload_date").Value = Now 93 94 sqlCmd.Parameters("@sd_file_name").Value = System.IO.Path.GetFileName(UploadedFile.PostedFile.FileName).ToLower 95 96 sqlCmd.Parameters("@sd_document").Value = bArray 97 98 99 sqlConn.Open() 100 sqlCmd.ExecuteNonQuery() 101 sqlConn.Close() 102 103 Else 104 105 'Either the file upload failed or no file was selected 106 107 End If 108 109 End Sub 110
This method atleast gives a different error: "Adobe Reader could not open '132-171510.pdf' because it is either not a supported file type or the file has been damaged (for examplc, it was sent as an email attachment and wasn't correctly decoded)." Please help, it would be appreciated. Thanks
I am using the article, " Storing Binary Files Directly in the Database Using ASP.NET 2.0 By Scott Mitchell "
http://aspnet.4guysfromrolla.com/articles/120606-1.aspx I have tried to modify the code and my datatable to replicate the function, but I am getting an error, "MIMEType" is not a SQL Parameter
An SqlParameter with ParameterName 'Type' is not contained by this SqlParameterCollection. 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.IndexOutOfRangeException: An SqlParameter with ParameterName 'Type' is not contained by this SqlParameterCollection.Source Error:
Line 53: Line 54: 'Specify the values for the MIMEType and ImageData parameters Line 55: e.Command.Parameters("Type").Value = MIMEType Line 56: Line 57: 'Load FileUpload's InputStream into Byte arraySource File: E:homeDefaultetances.ushtdocshomeadminaddNews.aspx Line: 55 Code: Protected Sub AddNewsBut_Click(ByVal sender As Object, ByVal e As System.EventArgs) If Page.IsValid Then InsertDs.Insert() Response.Redirect("~/news/default.aspx") End If
End Sub
Protected Sub InsertDs_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) 'Reference the FileUpload control
'Make sure a file has been successfully uploaded If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then Label1.Text = "If there is no photo of this event, please use a stock photo if you have none available. Else, please upload a photo of the event." e.Cancel = True Exit Sub End If
'Make sure we are dealing with a JPG or GIF file Dim extension As String = Path.GetExtension(UploadedFile.PostedFile.FileName).ToLower() Dim MIMEType As String = Nothing
Select Case extension Case ".gif" MIMEType = "image/gif" Case ".jpg", ".jpeg", ".jpe" MIMEType = "image/jpeg" Case ".png" MIMEType = "image/png"
Case Else 'Invalid file type uploaded Label1.Text = "Please use the correct file type: .jpg, .jpe, .gif, or .png." e.Cancel = True Exit Sub End Select
'Specify the values for the MIMEType and ImageData parameters e.Command.Parameters("Type").Value = MIMEType
'Load FileUpload's InputStream into Byte array Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length) e.Command.Parameters("Photo").Value = imageBytes
Dear readers, On my page people can opload foto's with a <asp:FileUpload ID="myfile" runat="server" BorderWidth="3px" BorderColor="Silver" BorderStyle="Inset" /> and stored the filePath in the database Now i want, that when there is no picture selected, the path to Noimage.jpg picture who is in my image map will stored on my datadbase. my script start like this If Not (myfile.HasFile) Then
////how can i select a standard image from my imageMap and store path to database /////
Dim cnn As Data.SqlClient.SqlConnection
Dim cmd As Data.SqlClient.SqlCommand
Dim strSQL As String
Dim connString As String = (ConfigurationManager.ConnectionStrings("Personal").ConnectionString) strSQL = "Insert Into tblMateriaal(ArtikelGroep,Artikelnaam,ArtikelType,ArtikelMaat,Aantal,Prijs,ContactPersoon,EmailAdress,Aanvul) Values(@ArtikelGroep,@ArtikelNaam,@ArtikelType,@ArtikelMaat,@Aantal,@Prijs,@ContactPersoon,@EmailAdress,@Aanvul)" cnn = New Data.SqlClient.SqlConnection(connString)
cmd = New Data.SqlClient.SqlCommand(strSQL, cnn)
Dim plaatje As New Data.SqlClient.SqlParameter("@plaatje", System.Data.SqlDbType.NVarChar) plaatje.Value = " " cmd.Parameters.Add(plaatje)
Dim ArtikelGroep As New Data.SqlClient.SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar) ArtikelGroep.Value = ddl1.SelectedValue cmd.Parameters.Add(ArtikelGroep)
Dim ArtikelNaam As New Data.SqlClient.SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar) ArtikelNaam.Value = tb1.Text cmd.Parameters.Add(ArtikelNaam)
Hi, My question is how can i get a page show all of my pictures i got in folder c:..Images? by using Database. Becouse I want some of the pictures to be shown in one page for theirselves. Database looks like: ID | Pname | Pact | 1 | picture1 | Yes | 2 | picture2 | No | etc. Now i want picture 1 and 2 to be shown in my Allpictures.aspx I tried by using <asp:repeater...>... But i got a problem when i wanted the <img src=<%#EVAL(Pname)/>... becouse i cant do it that way... Thanks, Even Knutli
I'm looking for information that could tell me how to saving picture in a SQL database. The site is build on asp.net 2.0 and c#, and I'm using Visual Web Developer 2005 Express Edition. I would be thankful for some help.
I need some advice about the best way to setup log shipping in conjunction with full backups and other system maint. such as DBCC's. I'd like to know what others are doing.
What is your schedule, 24/7, other? When do you take full backups of the databases? Do you restore these backups to the destination server then startup log shipping again?
I considered doing log ship from 7:00 - 19:00. Then do a full backup at 19:30 that is restored to the destination server. I understand that your schedule may vary due to app. requirements.
I found plenty of info. about the mechanics of setting up log ship but nothing from a broader perspective. If you know of an article that I can reference please pass that on as well.
I'v been looking for a sample that illustrate how to send an Image(jpeg/gif/bmp) using SSB. The initiating client should pick an image stored locally on a folder in that machine and send it over to the target broker instance using TCP. The target should verify the contents of the message and then insert the image in a table, which i assume should have a column of type varbinary(max).
Hello, I'd like to use a background picture in my report and I have some problems with its resolution. When it is 72 dpi the quality of printed background picture is very bad and when the resolution is 300 dpi a picture does not hold in one sheet of report. Help me please.
I am trying to save an uploaded picture filepath to a sql database record. The record that the filepath will be saved to will be the record determined by the logged in userid. I get an error stating Object not set to an instance of an object. This is my code for when the upload button is hitProtected Sub uploadbutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uploadbutton.Click If UploadTest.HasFile = False Then ' No file uploaded! UploadDetails.Text = "Please first select a file to upload..." Else ' Display the uploaded file's detailsUploadDetails.Text = String.Format( _ "Uploaded file: {0}<br />" & _"File size (in bytes): {1:N0}<br />" & _"Content-type: {2}", _ UploadTest.FileName, _ UploadTest.FileBytes.Length, _ UploadTest.PostedFile.ContentType) ' Save the fileDim filePath As String = Server.MapPath("~/ProfilePictures/" & UploadTest.FileName) UploadTest.SaveAs(filePath)Dim datasources1 As New SqlDataSourcedatasources1.ConnectionString = ConfigurationManager.ConnectionStrings("aspnetdb_connection").ToString() datasources1.UpdateCommandType = SqlDataSourceCommandType.Text Dim myUserID As String = Membership.GetUser().ProviderUserKey.ToStringdatasources1.UpdateParameters("userid").DefaultValue = myUserID datasources1.UpdateCommand = "UPDATE into profile_details (pic) VALUES (@filepath) WHERE ([UserId] = @UserId)" End If End Sub
How do I store an image/photo/picture in a database?
I have a database called database1 with a table called table1 like:
Table1: Personid Name1 Name2 Address
and I want to stora a picture of the person in the database (SQL Sever 7) c:picture.jpg. How could I do that? I would like to use a stored procedure to do it. Something with BLOB?bulk insert? how does it work?
17:04:32 Process Exit Code: (-1) 17:04:40 Setup failed to configure the server. Refer to the server error logs and C:WINDOWSsqlstp.log for more information. 17:04:40 Action CleanUpInstall: 17:04:40 C:WINDOWSTEMPSqlSetupBinscm.exe -Silent 1 -Action 4 -Service SQLSERVERAGENT 17:04:40 Process Exit Code: (1060) the currect service are not found as installed service.
I am new to SQL Server and I have got this problem and I am wondering if anyone could provide me with a solution, please.
I created a Product database table using Visual Basic 2005 Express and SQL Server 2005 Express. I have just added a new column [Picture] with IMAGE datatype to the database table, which of course, should store an image or picture of a product. I am writing to kindly ask you guys for help.
i) How do I store a picture or image for each record of this column [Picture] of database table? I have tried and all that I can see is < binary data > in the picture column, and when I start the VB 2005 Express form is a blank picture box!
ii) How do I get this image to display on Visual Basic 2005 Express form, so that when a product is selected the product image is displayed too? All text data appears but not the picture!
Okay. Simple request. I have a local report in my VS2005 project. I want to embed my company logo in the header. How do I do this?? When I put an image item on the page it wants me to put something in the value for this? I've tried sending through a dataset but SSRS can't load data set information into the header! Why is this so complicated? I don't want to embed a dynamic database image, just a static image that will be embedded in my program. Any help on this would be appreciated.
Sir, Is there any way to insert picture to image datatype in sql server 2000 without using front end. If so please let me know. Thanks in Advance,Arun.
I would like to create a table which can store a VLOB or BLOB.. which are pictures and images.. How do I insert them into the database and how do I select them into the data base for playing in a media player
I really do not have a background on this.. much.. I just know how to connect to a database and insert texts.. and updating and stuffs...
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System; using System.Collections.Generic; using System.Text; using System.Net; using System.IO;
namespace Predict.Services { public static class Foo { public static string GetIntradayQuote(string symbol) { string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close(); response.Close();
return returnQuote; } } }
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers. OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error". OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954. http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.