|
hello again
ok
with empty DB
1 msgbox
Original image count = 0
2 msgbox
delete failed
3 msgbox
Delete not done, rows in BD =0
and without closing application I add one image with original method and it shows msgbox that 1 image is saved, and then without closing application i click button to use your method still it shows that
first msgbox= original image count = 0
and in second = Delete not done row in datatable=0,
and when i click button second time without closing application
so first msgbox is original image count =0
second msgbox is delete failed
therd msgbox is row i datatable=0
so that means image has been saved in database and deleted when i click button to use your method and when i click button second so result was different
and then add one image and rerun the application so it show one image in database and i can see the image in picturebox as well now i click button for your method
so msgbox 1 Original Image count =1
msgbox2 = delete not done, row in datatable = 1
now i have click button second time without closing application so
msgbox 1 Original Image count =1
msgbox 2 Delete Failed
msgbox 3 delete not done, row in datatable = 1
so this is some thing else
i think we are going to the right direction
thanks
waiting for your kind rep.
|
|
|
|
|
This is confusing... I thought that now the delete operation would have deleted 1 row.
I think I must get some sleep but in the mean time if you could test this variation with one picture in database (this should delete ALL pictures):
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)
...
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End If
I'll get back to this sometime tomorrow afternoon so don't hurry with this. If I'm reading the clock correctly it's past 10pm at your timezone so you could use some rest too.
Mika
|
|
|
|
|
Hello again
yes you are right it's 10.30 pm now so better to rest and get back to work tomorrow but thank you very much for your help
take care
|
|
|
|
|
hello again
yes i have tried new quary but still it's not working
waiting for your kind rep.
|
|
|
|
|
Hi again,
What was the output from these (rowcount)?
MsgBox("Original image count in dataset: " & CStr(imageCount))
...
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End If
|
|
|
|
|
hello sir
how are you
in messagebox
1st
original image count = 2
2nd msgbox
delete not done, rows in datatable = 2
and when i click ok it shows SqlException was unhandled
"Incorrect syntax near '*'
wanting for your kind rep.
|
|
|
|
|
Where is that SqlException coming from? After the "Delete not done..." message box there should be no code to execute (only End If).
Another thing, the dataset is defined in the project, am I correct? Could you post the part that selects the images or do you create the SELECT statement?
|
|
|
|
|
hello again
the code which exacute is as bellow
Dim imageCount As Integer = dsImage.Images.Rows.Count
MsgBox("Original image count in dataset: " & CStr(imageCount))
conImage = GetDBConnection()
commImage = New Data.SqlClient.SqlCommand("DELETE * FROM Images)", conImage)
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), _
MsgBoxStyle.Information)
End If
and the code where error shows
If commImage.ExecuteNonQuery() <> 1 Then (this is the line)
MsgBox("Delete failed", MsgBoxStyle.Information)
End If
this is the part to get image
Private Sub GetImage()
Dim fs As FileStream = New FileStream(mImageFilePath.ToString(), FileMode.Open)
Dim img As Byte() = New Byte(fs.Length) {}
fs.Read(img, 0, fs.Length)
fs.Close()
mImageFile = Image.FromFile(mImageFilePath.ToString())
Dim imgHeight As Integer = picImage.Height 'mImageFile.Height
Dim imgWidth As Integer = picImage.Width 'mImageFile.Width
Dim imgLength As Integer = mImageFile.PropertyItems.Length
Dim imgType As String = Path.GetExtension(mImageFilePath)
mImageFile = Nothing
' image content
Dim pic As SqlParameter = New SqlParameter("@pic", SqlDbType.Image)
pic.Value = img
commImage.Parameters.Add(pic)
' title
Dim title As SqlParameter = New SqlParameter("@title", System.Data.SqlDbType.VarChar, 50)
title.Value = txtTitle.Text.ToString()
commImage.Parameters.Add(title)
' type
Dim itype As SqlParameter = New SqlParameter("@itype", System.Data.SqlDbType.Char, 4)
itype.Value = imgType.ToString()
commImage.Parameters.Add(itype)
' height
Dim iheight As SqlParameter = New SqlParameter("@iheight", System.Data.SqlDbType.Int)
iheight.Value = imgHeight
commImage.Parameters.Add(iheight)
' width
Dim iwidth As SqlParameter = New SqlParameter("@iwidth", System.Data.SqlDbType.Int)
iwidth.Value = imgWidth
commImage.Parameters.Add(iwidth)
End Sub
this is select statment
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
btnSave.Enabled = False
'get sql connection
Dim ImageAdd As Integer
Try
conImage = GetDBConnection()
Dim sSQL As String = "INSERT INTO Images (Pic,Title, IType, Height, Width) VALUES(" & _
"@pic, @title, @itype, @iheight, @iwidth)"
commImage = New Data.SqlClient.SqlCommand(sSQL, conImage)
Call GetImage()
ImageAdd = commImage.ExecuteNonQuery()
'Me.daImage.Update(Me.dsImage, "Images")
dsImage.AcceptChanges()
Me.ImagesTableAdapter.ClearBeforeFill = True
Me.ImagesTableAdapter.Fill(Me.dsImage.Images)
MessageBox.Show(ImageAdd.ToString & " Image successfuly saved in database", _
"Image Load")
Catch ex As Exception
MsgBox(ex.Message)
End Try
commImage.Dispose()
commImage = Nothing
conImage.Close()
conImage.Dispose()
Call LoadImages()
End Sub
and yes I drag and drop data adaptor and then generate dataset
|
|
|
|
|
Ok,
First change the following
commImage = New Data.SqlClient.SqlCommand("DELETE * FROM Images)", conImage)
to
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)
If you could then rerun the test where you had two images, what is the output now?
The deletion wasn't done, because of the syntax error in SQL statement, so we don't know if the deletion works or not
|
|
|
|
|
hello
with 2 images in database
msgbox1 original row count = 2
msgobx2 delete not done , rows in datatable = 2
and then i click second msgbox it shows sql exception
on this under line code it shows this error Incorrect syntax near ')'.
If commImage.ExecuteNonQuery() <> 1 Then
MsgBox("Delete failed", MsgBoxStyle.Information)
End If
|
|
|
|
|
Yeah, sorry.
Instead of:
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)
write
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images", conImage)
There was an extra parenthesis at the end of the statement...
Then let's try again
|
|
|
|
|
hello
ok no problem
MsbBox1 = original image count = 1
msgbox2 = delete not done,rows in datatable = 1
msgbox3 = delete not done, rows in datatable = 1
and exit sub
and then i click again
so
msgbox1= original image count = 1
msgbox2 = delete not done,rows in datatable = 1
msgbox3 = delete failed
msgbox4 = delete not done, rows in datatable = 1
so this is the only difference
waiting for your kind rep.
thanks
|
|
|
|
|
I am missing it... For some reason seems like Fill is fetching different data...
What is the connection used for filling dsImage? Is it the same that is used in deleting
Another thing. I don't think we can get much further by posting through CodeProject. Is it possible for you to send me the whole project via e-mail?
|
|
|
|
|
hello again
yes you are right and i dont mind to email this project and with empty database but size of zip folder is 42 MB so how can i e mail you ?
waiting for your rep.
|
|
|
|
|
Wow, so big?
If there's data in the database, could it be emptied. Pictures are not necessary. Also bin and obj folders are not necessary since they are created by compiler. Would that change the size say less than 5 MB.
I think emailing can be done by pressing Email-link in a post instead of Reply. I'll send you a test message..
|
|
|
|
|
hello
I have sent you mail as well
so if remove bin then this will cut down up to 26mb but .mdf and log file for database both are 13 mb each but rest files are ok to attach with e mail
|
|
|
|
|
if you put the log and the mdf into zip folder is it still 26mb. They should compress quite well.
The test message didn't come so I think we cannot use that. Also I don't know if there's any way to use attachments in cp email...
Try to send me a little message to address mika dot wendelius at bdb dot fi
|
|
|
|
|
Hi Mika
It's done now and it's working fine
so thanks for your help to solve this issue and teaching me a lot
|
|
|
|
|
You're welcome
|
|
|
|
|
Hello everyone,
I'm using bcp.exe to export some data from an SQL Server 2005 database to a txt file that can be used in some other old unix system.
Thing is, once the export is done, exported rows look like this:
0730280001868 38 20040005232004-03-24 00:00:00.0002004-04-19 00:00:00.000
Dates are in a yyyy-MM-dd hh:mm:ss.sss format. However, I need datetime values to get exported in a dd/MM/year format.
According to documentation in MSDN, format in bcp import/export operations is done through a format file (an xml file in this case), so any change in the format should be done by modifying this file. Currently, my format file looks like this:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="11" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="POL_NumPoliza" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="CIA_Id" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="SIN_Alias" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="SIN_FechaSiniestro" xsi:type="SQLVARYCHAR" LENGTH="8"/>
<COLUMN SOURCE="5" NAME="SIN_FechaParte" xsi:type="SQLVARYCHAR" LENGTH="8"/>
</ROW>
</BCPFORMAT>
Considering I've changed almost everything that can be changed in that file [datatypes, collations, lengths etc], my question is, does anyone have an idea on how to force bcp to write datetime values in a particular format (just like using FORMAT !) when writing the text file?
Thanks in advance !
Kazz
"Users are there to click on things, not think. Let the archs do the damn thinking."
|
|
|
|
|
Hi,
you didn't include the bcp command to the post, but I believe that you are exporting a table directly (like: bcp tablename... ).
Instead of that, try exporting based on a query where you convert the datetime colun to varchar in desired format and use that query in bcp. For example:
bcp "SELECT CONVERT(varchar(50), DatetimeColumnName, 103), ..." queryout, ...
Mika
|
|
|
|
|
I'm creating a query to transform one DB to another, but it's getting uncontrollably big! I know, I know I should use SSIS packages, but they just don't do it for me. Most of the time it's faster to just write the code than model it in SSIS. Truth be told, I REALLY miss SQL200's DTS packages! Maybe I'm just to stubborn to accept change.
But anyway...
Is there no way that I can make my query simpler by moving some code out and simply referencing it in my main query? (And I don't think SPROCS is what I want here)
In Oracle I used to be able to write statements which execute text files... does SQL2005 have such functionality?
modified on Thursday, August 28, 2008 5:25 AM
|
|
|
|
|
If you want to break logic into several pieces you can use for example sqlcmd command line utility to run a file (a bit like in Oracle SQL*Plus). However you cannot break a single statement, but I guess that is not your intention.
Mika
|
|
|
|
|
You might want to go after the SSIS package approach. It might take longer to create, but it is a lot easier to maintain with larger projects. You might need to ask yourself will you be saving the time in the future by using SSIS?
|
|
|
|
|
i have a string data in a column in an msqsql database table and the values are separated by commas (,)eg 01,02,M,1,045,12,1948 i want to remove the commas in that column so that the data looks like 0102M1045148.
can any one help me .
thanks members
phokojoe
|
|
|
|