Saving Time in 12 hour format

Hi,
By default in SQL Server, when I store a time in an Datetime field, it
stores in 24 hour format. but I want to store in 12 hours format with AM/PM
also being stored.
Can anyone help me in this.
Thanks & Regards
Sudhakara.T.P.Its is not important how the data is stored in the database. all that
metters is how u retreive the data.
if u want to retrive with AM and PM format: u can use
CONVERT(varchar(20), getdate(), 100)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:

> Hi,
> By default in SQL Server, when I store a time in an Datetime field, it
> stores in 24 hour format. but I want to store in 12 hours format with AM/P
M
> also being stored.
> Can anyone help me in this.
> Thanks & Regards
> Sudhakara.T.P.|||Hi,
I am very much aware of the fact of retreiving the records in the format
that I want, but I am very much interested in storing the time data in 12
hour format itself as this is one of my prime requirement.
"Chandra" wrote:
> Its is not important how the data is stored in the database. all that
> metters is how u retreive the data.
> if u want to retrive with AM and PM format: u can use
> CONVERT(varchar(20), getdate(), 100)
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Sudhakara.T.P." wrote:
>|||SQL Server does not store datetime values in any user specified format,
but in an internal format.
So showing the AM/PM is basically a formatting issue.
You can use the CONVERT function to do the same.
Have a look at
http://www.karaszi.com/sqlserver/info_datetime.asp
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sudhakara.T.P." <SudhakaraTP@.discussions.microsoft.com> wrote in message
news:BB0D056C-CA8C-4D59-9B97-EDB5B3CBE88E@.microsoft.com...
> Hi,
> By default in SQL Server, when I store a time in an Datetime field, it
> stores in 24 hour format. but I want to store in 12 hours format with
> AM/PM
> also being stored.
> Can anyone help me in this.
> Thanks & Regards
> Sudhakara.T.P.|||then create a varchar field for this instead of datetime field. hope this
can solve the purpose.
may i know the prime reason behind storing as that format. probably i can
give u a suggestion
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:
> Hi,
> I am very much aware of the fact of retreiving the records in the format
> that I want, but I am very much interested in storing the time data in 12
> hour format itself as this is one of my prime requirement.
> "Chandra" wrote:
>|||Hi Chandra,
thanks for the reply.
Well for one of the project that we are developing for the client, it is his
prime requirement to see the datetime record saved in the database in 12 hou
r
format as the db administrator is the client himself and he needs to open th
e
database and check the records frequently for some of his purposes[which he
has not revealed the reason].
I am freelance programmer and this is his first requirement/condition in the
project.
Regards
Sudhakara.T.P.|||then for this purpose, i prefer using a view and those who want to check can
use a view instead of referrig directly to a table
pleas let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sudhakara.T.P." wrote:

> Hi Chandra,
> thanks for the reply.
> Well for one of the project that we are developing for the client, it is h
is
> prime requirement to see the datetime record saved in the database in 12 h
our
> format as the db administrator is the client himself and he needs to open
the
> database and check the records frequently for some of his purposes[which h
e
> has not revealed the reason].
> I am freelance programmer and this is his first requirement/condition in t
he
> project.
> Regards
> Sudhakara.T.P.|||>> By default in SQL Server, when I store a time in an Datetime field [sic], it s
tores in 24 hour format. but I want to store in 12 hours format with AM/PM a
lso being stored. <<
Take your hands off the keyboard. Step away from the database. You
are dangerously ignorant.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. One of the *many* differences in
a field and column is that a column has an abstract datatype and
meaning in and of itself. A field gets its meaning from the program
that reads it. This kind of question is the result of not knowing
these differences.
How a column is displayed in done in the host program and has nothing
WHATSOEVER to do with the internal format in the RDBMS. In the case of
T-SQL, it is a "floating point number" that counts clock ticks. To
display it as a string, you use CONVERT() and a format parameter.
Now, if you are a good SQL programmer who respects ISO standards you
use ISO-8601 formats only to avoid data exchange problems, ambigous
dates and other problems so you would never have AM/PM times in your
front end.

Saving Store procedure on table

Hello there
I have some store procedure that return query as result.
Is there a way to store the result in table?"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:em9Zi27CGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have some store procedure that return query as result.
> Is there a way to store the result in table?
Maybe this will give you the information you need:
http://www.sommarskog.se/share_data.html|||INSERT INTO YourTable (Col1, Col2...) EXEC YourSp
Andrew J. Kelly SQL MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:em9Zi27CGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I have some store procedure that return query as result.
> Is there a way to store the result in table?
>
>|||You can create the table upfront & use INSERT .. EXEC statement.
INSERT tbl ( ... ) EXEC usp @.param1, @.param2, ...
Alternatively, you can create a loopback and use OPENQUERY like:
EXEC sp_serveroption 'data access', true
GO
SELECT * INTO tbl
FROM OPENQUERY ( Server, 'SET FMTONLY OFF; EXEC.. ' )
Anith

Saving ParameterValue Object To SQL Table

Greetings,
I am trying to store/retrieve given RS report's ParameterValue object to a
SQL table (ntext?)â?¦Iâ've tried MemoryStream w/ BinaryFormatter and am getting
â'Not marked as serializableâ' errors. Any suggestions on a viable way to do
this would be appreciated.
Thank you for your time!
JohnI solved my problem using the XmlSerializer object as followingsâ?¦
Public Shared Function Deserialize(ByVal bytes() As Byte) As Object
Dim xs As New XmlSerializer(GetType(ParameterValue()))
Dim ms As New MemoryStream(bytes)
Dim obj As Object
Try
obj = xs.Deserialize(ms)
Catch ex As Exception
Throw ex
End Try
Return obj
End Function
Public Shared Function Serialize(ByVal Obj() As Object) As Byte()
Dim xs As New XmlSerializer(GetType(ParameterValue()))
Dim Ms As New MemoryStream
Try
xs.Serialize(Ms, Obj)
Catch ex As Exception
Throw ex
End Try
Return Ms.ToArray
End Function
Dim Rp() as ParameterValue
â'Store parameter object
DataRow(â'MyBinaryFieldâ') = Serialize(Rp())
â'Get Stored Parameter object
Rp = Deserialize(DataRow(â'MyBinaryFieldâ'))
"JB" wrote:
> Greetings,
> I am trying to store/retrieve given RS report's ParameterValue object to a
> SQL table (ntext?)â?¦Iâ've tried MemoryStream w/ BinaryFormatter and am getting
> â'Not marked as serializableâ' errors. Any suggestions on a viable way to do
> this would be appreciated.
> Thank you for your time!
> John
>sql

Saving Images in SQL Servers Image Datatype

I would like to store images in and retrieve images from SQL Server. And the images are to be stored and retrieved from ASP. How do I do it?Hi..

Check out in this web site

http://www.sqlteam.com/item.asp?ItemID=986

Regards,
Selva Balaji B.sql

saving image in SQL server or in harddrive

I am trying to store about millions of pictures in Web server with lots of traffic. some poeple told me. SQL server cannot handle it and may freeze quickly. can anybody tell me which way is better and more efficent? store image in SQL server image field or store in server's harddrive?Hi,

SQL Server 2000 is actually quite efficient about storing binary data. Microsoft did a lot of work to make it that way. Earlier versions weren't as efficient, and the myth continues that it isn't a good thing to do.

That said, you'll almost certainly have an easier time developing and maintaining the entire system by using the database to store file locations. That way you can use SQL Server to search and sort images, but use the file system to actually store them. Besides, adding and retrieving images from SQL Server is a bit of a pain, but you only have to code it once.

Don|||::That said, you'll almost certainly have an easier time developing and maintaining the entire
::system by using the database to store file locations.

I want to counter this. Storing file locations in the database has a plenthora of negative consequences:

* Editing: your editros needs access to the file location. Oh, no problem from the same website. What if you want to add a rich client application for image editing?
* Scaling: add more web servers, and - well - your problems are starting. How do you synchronize the file stores?
* Backup: two backups, instead of one.
* Integrity. How do you make sure you dont have images left over in case of a program fault while uploading? Tip: it is possible, using Seviced Components and using such a serviced component for file manipulation, together with the quarantees provided by a Compensating Ressource Manager I am just dure 99% of the developers have no clue what a CRM is.

When the images belong to data in a database, they should be stored in a database - everything else is a hack. And hacks come back nad make you pay for using them, normally pretty fast.|||for the net working, I decide to use three computer in the beginning, Web server, sql server, and storage server for each computer.

sinerio 1
If I only store file locations in SQL server and store file in file system, that will reduce lots of workload in SQL server because web server only get the path from SQL server, then it will retrieve the file from the file server. is this more efficent than sinerio 2 because it transfer the work load to web server from SQL server?

sinerio 2
However, if I put database in storage server, and I put file in image field in SQL server , the web server has to go through sql server then go to storage server. the SQL server has to process the file and send it to web server. will this have more workload on SQL server than sinerio 1?|||Hi,

I'm not sure I understand scenario 2. Do you mean putting the SQL Server database on the storage server?

Your assesment of scenario 1 is correct, in terms of less workload for the SQL Server. I would guess that scenario 1 would be, because the presumably larger image data would go directly from the storage server to the Web server. Scenario 2 would have it go from storage to database server to Web server.

Thomas will no doubt have an opinion about this as well. ;-)

Don|||Yes! donkiely, I mean putting the SQL Server database on the storage server.

one more question:
If I write a VB application installed in my computer (client), and the server setup is using scenario 2, how do I write this VB program to pass the fire wall and get the file from harddrive of storage server to my local computer?|||sorry! the previous post is WRONG. it should be "sinerio 1" NOT sinerio 2.

how do I get file from storage server to VB application in sinerio 1.|||::Thomas will no doubt have an opinion about this as well.

Avsolutly. Your assessment is correct: the workload for the SQL server is higher when the images come from the SQL Server.

BUT:
* Is this relevant? You should hardly ever get images from the SQL Server - ASP.NET would actually take them from output cache most of the time :-) Caching is anyway the best way to handle this (images are static).
* I this turns into a problem, you could come up with a federated database, still keeping it all in the db, and making the storage server a sql server, too. This can be a pricing problem, though.

The real question is whether this additional load makes a difference. SQL Server is pretty fast in handling binary data today.

And I can not repeat oit over and over again: caching can and should kill this overhead nearly to nothing.|||

Hello, this is my first post...EVER. :)

I've read many of the arguments that you should or should not store a digital image in a binary or image field in SQL server. Whether the debate continues or not, I believe someone (actually many new developers) are looking to you who are MCP's and MCD's and all the others to actually SHOW them HOW to do this. Only through the experience will they understand and be able to evaluate performance within their environments.

So, this is something I haven't done since my old VB 6.0 days where I had to use the GETCHUNK method to chop up a binary file and store it in a database. So, I'm curious what the NEW method(s) would be. I am in the precarious predicament of working at an international bank that actually WANTS to store images in a database.

I thank you and await your replies.

Bill (non-MCP)

|||

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadDim connAs New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) conn.Open()Dim cmdAs New SqlCommand("SELECT MimeType,Filename,Data FROM Attachments WHERE ID=@.ID", conn) cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = Request.QueryString("ID")Dim drAs SqlDataReader dr = cmd.ExecuteReader dr.Read() Response.Clear() Response.AddHeader("Content-type", dr("MimeType")) Response.AddHeader("Content-Disposition","inline; filename=""" & dr("Filename") &"""")Dim buffer()As Byte = dr("Data")Dim blenAs Integer =CType(dr("Data"),Byte()).Length Response.OutputStream.Write(buffer, 0, blen) Response.End()End Sub

|||

Create a page called ViewPicture.aspx.

Insert the above code.

Anytime you need an image, call ViewPicture.aspx?ID=123 where 123 is the image number.

Set the page with the appropriate caching options, and/or add SqlDependancies if your images can change.

The above assumes you have a table named "Attachments" with columns ID, MimeType, Filename, and Data.

ID would be an int, Mimetype would be a varchar or nvarchar of appropriate length (I use 50, but I use a very limited list of MimeTypes), Filename would be a nvarchar(253), and Data would be type image.

|||

Thank you very much. I appreciate the help. I'm pretty confident that I can translate your code to C# since I've been programming in VB for about 15 years.

Would you mind indulging me and tell me if the MimeType is required? If so, why?

Thanks again.

Bill

|||

This is great for viewing the stored images. But, would you just use a fileupload control to store the image to the table?

|||

Yes, infact I've posted a few times code on how to use the file upload control in order to store images in the database. I'm sure a quick search on this forum will turn up at least one of them.

As for MimeType, it depends on what you are storing. If it will always be say a jpeg then you already should know the mimetype, but if you want to be able to store say jpeg, bmp, tiff, png, etc then you need to know what type of file it is, and I've just chosen to store that information in the MimeType field since ultimately that is what will need to be sent to the client, and it allows me to store any type of file that the client's system is aware of (Excel spreadsheets, PDF's, Zip files, etc).

If you don't supply the MimeType, then the client's browser will most likely treat whatever you are sending as a blob and will force you to store it to disk rather than possibly opening it in an appropriate application (Winzip for zip files, excel for excel files, or displaying the picture in the browser for jpeg's, etc).

sql

Saving Carriage Return in a nvarchar field (SQL Server 2005)

Hi,

As you can see, I'm totally new at Sql Server.

I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".

What can I do to preserve the cr inside each field ?

Thanks in advance.

Hi George,

To resolve this issue, you need to ensure that insert/update operations are presenting character data which includes CR/LF's in a manner that is recognisable to mssql:

create table #crTest
(
charCol nvarchar(100) not null
);

declare @.s nvarchar(100);

set @.s = 'this is a test:' + nchar(13) + 'new line';
insert into #crTest values(@.s);

select * from #crTest;

In the above example, the CRLF is represented as the ASCII code 13 - sql is able to recognise and preserve formatting during subsequent interrogations.

To correct existing data, you could use the REPLACE function as such:

update MyCharTable

set problematicColumn = replace(problematicColumn, '?', nchar(13));

However this assumes you do not have a valid "?" somewhere in the "problematicColumn" column.

Cheers,

Rob

|||

Thanks for your reply Robert,

The problem is that I could have some valids "?", so I would have to make mssql understand that the chr(13) is indeed a chr(13) and not to convert it to "?"

According to your answer, if I'm storing the data from vb.net to mssql, is it possible to do this ?:

SqlStr as string = "Insert into table (field1nvarchar) Values ('" & Var1.replace(chr(13),nchar(13)) & "')"

or how should I do it ?

Thanks in advance.

George

|||

Hi George,

Sorry for the delay in responding - I've been off.

If you're using vb, they where you would normally insert your VBCRLF (or is it Environment.Newline in vb too now?) you would insert a char(13):

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

AdoCommand.Execute("Insert into MyTable (column1Nvarchar) values (" & sVal + ")")

The point to keep in mind is that REPLACE is a SQL function and if you're able to control how CR's are represted to sql at the time of insert/update, then you do not need it - simply use CHAR(13) to tell mssql to store a CR.

Cheers,

Rob

|||

I'm having this same problem too. However; there is no NCHAR(13) in VB.NET that I know of. It is in MSSQL though. Should I do something like use chr(13) instead of vbCrLf and then REPLACE all chr(13)'s with a NCHAR(13) on the SQL side in my stored procedure that saves my data? By doing this, will these be automatically converted back to vbCrLf's on the VB.NET side so that my data will show properly in my multiline textbox?

Thanks

|||

Hi George,

Yes, you'll have to replace the vbCrLf with NCHAR(13) in the query/parameter string. It's not obvious, but if you look closely in my previous post, the NCHAR(13) is now encolsed in a string:

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

So in your proc, I assume you've a nchar/char parameter that accepts the string param from VB - in this case, sVal. If the @. param of the sp is a nchar, then in the above scenario, the actual CRLF will be included in the @. param - you won't need to do anything further to recognise and store the CR/LF on the mssql side.

Cheers,
Rob

Saving Carriage Return in a nvarchar field (SQL Server 2005)

Hi,

As you can see, I'm totally new at Sql Server.

I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".

What can I do to preserve the cr inside each field ?

Thanks in advance.

Hi George,

To resolve this issue, you need to ensure that insert/update operations are presenting character data which includes CR/LF's in a manner that is recognisable to mssql:

create table #crTest
(
charCol nvarchar(100) not null
);

declare @.s nvarchar(100);

set @.s = 'this is a test:' + nchar(13) + 'new line';
insert into #crTest values(@.s);

select * from #crTest;

In the above example, the CRLF is represented as the ASCII code 13 - sql is able to recognise and preserve formatting during subsequent interrogations.

To correct existing data, you could use the REPLACE function as such:

update MyCharTable

set problematicColumn = replace(problematicColumn, '?', nchar(13));

However this assumes you do not have a valid "?" somewhere in the "problematicColumn" column.

Cheers,

Rob

|||

Thanks for your reply Robert,

The problem is that I could have some valids "?", so I would have to make mssql understand that the chr(13) is indeed a chr(13) and not to convert it to "?"

According to your answer, if I'm storing the data from vb.net to mssql, is it possible to do this ?:

SqlStr as string = "Insert into table (field1nvarchar) Values ('" & Var1.replace(chr(13),nchar(13)) & "')"

or how should I do it ?

Thanks in advance.

George

|||

Hi George,

Sorry for the delay in responding - I've been off.

If you're using vb, they where you would normally insert your VBCRLF (or is it Environment.Newline in vb too now?) you would insert a char(13):

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

AdoCommand.Execute("Insert into MyTable (column1Nvarchar) values (" & sVal + ")")

The point to keep in mind is that REPLACE is a SQL function and if you're able to control how CR's are represted to sql at the time of insert/update, then you do not need it - simply use CHAR(13) to tell mssql to store a CR.

Cheers,

Rob

|||

I'm having this same problem too. However; there is no NCHAR(13) in VB.NET that I know of. It is in MSSQL though. Should I do something like use chr(13) instead of vbCrLf and then REPLACE all chr(13)'s with a NCHAR(13) on the SQL side in my stored procedure that saves my data? By doing this, will these be automatically converted back to vbCrLf's on the VB.NET side so that my data will show properly in my multiline textbox?

Thanks

|||

Hi George,

Yes, you'll have to replace the vbCrLf with NCHAR(13) in the query/parameter string. It's not obvious, but if you look closely in my previous post, the NCHAR(13) is now encolsed in a string:

sVal as string = " 'this is line 1' " & VBCRLF & " 'this is line 2' "

Should be:

sVal as string = " 'this is line 1' + NCHAR(13) + 'this is line 2' "

So in your proc, I assume you've a nchar/char parameter that accepts the string param from VB - in this case, sVal. If the @. param of the sp is a nchar, then in the above scenario, the actual CRLF will be included in the @. param - you won't need to do anything further to recognise and store the CR/LF on the mssql side.

Cheers,
Rob

save dr property to variable

how do you store a datareader propety to a variable? Below is my current code. I have already declared my connectionString and sqlComm objects, as well as the userName and such. I need to store the value from the dr to the variables, UserName, UserPass, and serverName. Thanks

Try sqlCon.Open() dr = sqlComm.ExecuteReaderWhile dr.Read userName = dr("uName").ToString LogInfo("userName = " & userName) userPass = dr("uPass").ToString LogInfo("userPass = " & userPass) serverName = dr("sName").ToString LogInfo("serverName = " & serverName)End While dr.Close()Catch obugAs Exception LogEvent("Credentials Error: " & obug.Message)Finally sqlCon.Close()End Try

You would set them exactly as you have done above (i.e. whilst inside the "dr.Read" while loop). Are you saying this doesn't work? If so, have you stepped through your code to see what is happening?

|||

Hi,

How many rows do you have in your table. If there is only one row and you want to store them into variables, its fine. However what if you have more than one rows? With each loop of your dr, your previous value in the variable will get lost.

Instead use a Generic List to store your values.

Checkthis sample.

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Got it to work. Since I had declared the variables as string, and did not fill them with anything, I was getting an error while trying to use the variables in some code after this dr object. Setting the variables to empty string resolved the errors:

Dim var AS String = ""

thanks for the responses.

Save data flow task result into specific table in database

Hello

Kindly i need support in this issue, i create task flow import from flat file and store in database but i need to save all result for task into specific table

Like Record count transferred

Destination table name

Time ..........etc

thanks

Sounds like you are looking for auditing information. Watch the Kimball Webcast on the main page of this forum. Plenty of examples in there.

In summary, you'll populate variables in your data flow, and then after the data flow completes, you'll use an Execute SQL task in the control flow to perform an insert statement into your "specific table" using those variables previously populated.|||where in the main page ?|||

Hosam Abd EL-Wahab wrote:

where in the main page ?

The Ralph Kimball Group Webcast

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1

Save BLOB to SQL2K TEXT column in VB.NET

In VB6 I store PDF files as BLOBS in a TEXT column of a SQL table
The VB6 code works and I'm trying to do the same in VB.NET
without success. Any help?
Dim vText As Variant, vLinesFromFile As Variant
Dim nFile as Integer
Dim sSQL as String
sSQL = "SELECT * FROM CIP_WF_DOCUMENTS WHERE [FILENAME] = ''"
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
nFile = FreeFile
Open "c:\TEST.PDF" For Binary As #nFile
vText = StrConv(InputB(LOF(nFile), nFile), vbUnicode)
rs.AddNew
rs("CIP_WF_Status_ID").Value = gAccount.nCIP_WF_Status_ID
If nCIP_Documents_ID <> 0 Then ' Rev 11/03/2004
rs("CIP_Documents_ID").Value = nCIP_Documents_ID
End If
rs("Status").Value = nSubstitutePages ' Rev 04/20/2005 was 0
rs("Date").Value = gdNowDate
rs("FILENAME").Value = sName
rs("BODY").Value = vText
rs("DocOrder").Value = nDocOrder' Rev 04/20/2005
rs.Update
=VB. NET=====================================
============================
Private Function sqlSave(ByVal fileName As String) As Integer
Dim IsConnecting As Boolean = True
Dim nReturn As Integer = 0
Dim con As New SqlConnection(gsConnectionString)
Dim sSql As String = "INSERT INTO CIP_WF_Scanned(Filename, [Body])
VALUES(@.fileName, @.Body)"
Dim pdfImage() As Byte = GetPDFImage(fileName)
Dim addCIP_WF_Scanned As New SqlClient.SqlCommand(sSql, con)
addCIP_WF_Scanned.Parameters.Add("@.fileName", SqlDbType.NVarChar,
50).Value = fileName.ToString
addCIP_WF_Scanned.Parameters.Add("@.Body", SqlDbType.Text,
pdfImage.Length).Value = Convert.ToString(pdfImage)
Try
con.Open()
addCIP_WF_Scanned.ExecuteNonQuery()
con.Close()
sqlSave = 1
Catch exc As Exception
sqlSave = 0
SendSQLErrorEmail("sqlSave", sSql, exc)
End Try
con = Nothing
End Function
Public Shared Function GetPDFImage(ByVal filePath As String) As Byte()
Dim fs As FileStream = New FileStream(filePath, FileMode.Open,
FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)
Dim pdfImage() As Byte = br.ReadBytes(fs.Length)
br.Close()
fs.Close()
Return pdfImage
End Function
=TABLE==================================
============================
Column_name Type
CIP_WF_Scanned_ID int
Filename varchar
Body textHi
Have you seen
http://support.microsoft.com/defaul...b;en-us;317034?
John
"David A. Beck" <davidb@.beckb.com> wrote in message
news:%23UgkZnDaFHA.2984@.TK2MSFTNGP15.phx.gbl...
> In VB6 I store PDF files as BLOBS in a TEXT column of a SQL table
> The VB6 code works and I'm trying to do the same in VB.NET
> without success. Any help?
> Dim vText As Variant, vLinesFromFile As Variant
> Dim nFile as Integer
> Dim sSQL as String
> sSQL = "SELECT * FROM CIP_WF_DOCUMENTS WHERE [FILENAME] = ''"
> rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
> nFile = FreeFile
> Open "c:\TEST.PDF" For Binary As #nFile
> vText = StrConv(InputB(LOF(nFile), nFile), vbUnicode)
> rs.AddNew
> rs("CIP_WF_Status_ID").Value = gAccount.nCIP_WF_Status_ID
> If nCIP_Documents_ID <> 0 Then ' Rev 11/03/2004
> rs("CIP_Documents_ID").Value = nCIP_Documents_ID
> End If
> rs("Status").Value = nSubstitutePages ' Rev 04/20/2005 was 0
> rs("Date").Value = gdNowDate
> rs("FILENAME").Value = sName
> rs("BODY").Value = vText
> rs("DocOrder").Value = nDocOrder' Rev 04/20/2005
> rs.Update
> =VB. NET=====================================
============================
> Private Function sqlSave(ByVal fileName As String) As Integer
> Dim IsConnecting As Boolean = True
> Dim nReturn As Integer = 0
> Dim con As New SqlConnection(gsConnectionString)
> Dim sSql As String = "INSERT INTO CIP_WF_Scanned(Filename, [Body])
> VALUES(@.fileName, @.Body)"
> Dim pdfImage() As Byte = GetPDFImage(fileName)
> Dim addCIP_WF_Scanned As New SqlClient.SqlCommand(sSql, con)
> addCIP_WF_Scanned.Parameters.Add("@.fileName", SqlDbType.NVarChar,
> 50).Value = fileName.ToString
> addCIP_WF_Scanned.Parameters.Add("@.Body", SqlDbType.Text,
> pdfImage.Length).Value = Convert.ToString(pdfImage)
> Try
> con.Open()
> addCIP_WF_Scanned.ExecuteNonQuery()
> con.Close()
> sqlSave = 1
> Catch exc As Exception
> sqlSave = 0
> SendSQLErrorEmail("sqlSave", sSql, exc)
> End Try
> con = Nothing
> End Function
> Public Shared Function GetPDFImage(ByVal filePath As String) As Byte()
> Dim fs As FileStream = New FileStream(filePath, FileMode.Open,
> FileAccess.Read)
> Dim br As BinaryReader = New BinaryReader(fs)
> Dim pdfImage() As Byte = br.ReadBytes(fs.Length)
> br.Close()
> fs.Close()
> Return pdfImage
> End Function
> =TABLE==================================
============================
> Column_name Type
> CIP_WF_Scanned_ID int
> Filename varchar
> Body text
>|||The URL is invalid
*** Sent via Developersdex http://www.examnotes.net ***|||Hi David,
Please try this URL and let me know whether you could open the link
HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking
in ADO.NET and Visual Basic .NET
http://support.microsoft.com/kb/317034
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.