|
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
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
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
|
|
|
|
|
select replace(Columnname,',','') from TableName
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
thanks Blue_Boy, i am going to try it, but by the look of the syntax, it will work.
thanks once more
phokojoe
|
|
|
|
|
I have tested that query and it has worked for me, and sure will work for you too.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
i have just tested it, but the problem is, i have so many entries in that column. when i write a column name, it does not replace the commas, instead it returns the column name not even the entries in that column. eg.
like i said, the values in that column are of this shape:
01,02,M,045,1948,2,21 and the column name (field) is say column1 i will write:
select replace('column1',',','') it only returns column1 not 0102M0451948221.
please help me on this. 
phokojoe
|
|
|
|
|
phokojoe wrote: select replace('column1',',','')
is not correct bcause you have write columnename inside single qutes
Try again code down below
<br />
Select replace(columname,',','') from tablename
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
yah yah yah, without you i could have not survived this stress.
i have done it! it works.
thanks once more.
phokojoe
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi friends, I would like to query with a string variable:
For example if using
select * from Members
I get
ID(bigint) Name(varchar(max)) Owner(varchar(max))
1 N1 a000001
2 N2 a000002
3 N3 a000003
4 N4 a000003,a000004
5 N5 a000001,a000002
Then if using
select * from Members where Owner like '%a000001%'
I would get
ID(bigint) Name(varchar(max)) Owner(varchar(max))
1 N1 a000001
5 N5 a000001,a000002
Then I use a string variable test to query as follows:
System.String test="a000001";
select * from Members where Owner like '%+test+%'
I get nothing.
So could you please help me that:
how can I use the test to query with the same result as
select * from Members where Owner like '%a000001%'
when test="a000001"?
Thanks for any feedback.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
