|
Hi,
I'm using SQL Server 2005.
I have the following SQL which is supposed to pad an integer value with leading zeros. The problem is that it prints 0 instead of 001
DECLARE @UserID INT
SET @UserID = 1
DECLARE @UtopiaOrderNumber NVARCHAR
SET @UtopiaOrderNumber = (SELECT RIGHT(REPLICATE('0', 3) + CONVERT(NVARCHAR(3), @UserID), 3))
PRINT @UtopiaOrderNumber -- prints 0 instead of 001
The following SQL does select the correct value:
DECLARE @UserID INT
SET @UserID = 1
SELECT RIGHT(REPLICATE('0', 3) + CONVERT(NVARCHAR(3), @UserID), 3) -- selects 001 as expected
Can anyone let me know what I'm doing wrong?
Thanks,
dlarkin77
|
|
|
|
|
You used a default for NVARCHAR the command should be
DECLARE @UtopiaOrderNumber NVARCHAR(3)
That will give you the output you expect.
|
|
|
|
|
That sorted it out alright. Thanks very much.
|
|
|
|
|
Hi all
I'm experiencing troubles with the code from this site.
Which parameter is exspected instead of:"master..sysdatabases" in the following sql-command:Dim mySQL As String = _
"IF EXISTS (" & _
"SELECT * " & _
"FROM master..sysdatabases " & _
"WHERE Name = 'MyDatabase')" & vbCrLf & _
"DROP DATABASE MyDatabase " & vbCrLf & _
"CREATE DATABASE MyDatabase "
Thanks,
Lucchingolo
|
|
|
|
|
What database engine are you using?
|
|
|
|
|
I'm using Microsoft SQL Server 2008
|
|
|
|
|
Okay, by right clicking on a database in SSMS you can select "Script Database as" then select "DROP to" you can then find that you are looking for sys.databases
As this looks like .NET code you may not have access to SSMS so here is an T-SQL example:
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
DROP DATABASE [MyDatabase]
|
|
|
|
|
Sorry, still get an error.
Maybe i misundertood you.
Fact is that i still get an error.
What i did is using your sql-command as string for my SQL-command.
What I mean is this:
In the Form1_Load-Event of my Application(project).
MyConnection = "Server=localhost;" & "DataBase=;" & "Integrated Security=SSPI"
'Your line of code
Dim mySQL As String = "IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')DROP DATABASE [MyDatabase]"
Try
Dim conn As New SqlConnection(MyConnection)
Dim myCmd As New SqlCommand(mySQL, conn)
conn.Open()
myCmd.ExecuteNonQuery()
conn.Close()
.
.
.
Last but not least, don't forget that the point is not only to "drop" a Database but i'm trying so hard to create a SQL-Database. 
thank you,
Lucchingolo
|
|
|
|
|
You will need a semicolon before the drop. If you are not dropping a database I would suggest not even coding the if - drop.
I have not programmed this at the .NET end. You may need to go to the VS forum.
|
|
|
|
|
Thank your, I will give it a try in the Visual Basic forum.
I could not find a VS-forum. Guess you meant the VB-forum.
|
|
|
|
|
Hi,
I am using LINQ to SQL and MVVM pattern in my application where i am retrieving
my data by the following query:
internal ObservableCollection<INVCategory> GetCategoryList()
{
DataLoadOptions dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<INVCategory>(t => t.INVSubCategories);
this.Context.LoadOptions = dataLoadOptions;
var categories = from category in this.Context.INVCategories
orderby category.CatgeoryId descending
select category;
return new ObservableCollection<INVCategory>(categories.ToList());
}
And my XAML code for parent(Category) combo is:
<ComboBox Grid.Column="1" Grid.Row="0" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Margin="0,2,10,2" TabIndex="0" x:Name="categoryComboBox" ItemsSource="{Binding CategoryList}" IsEditable="True" DisplayMemberPath="CategoryName" SelectedValuePath="CatgeoryId" SelectedItem="{Binding CategoryList, Mode=TwoWay}" SelectedValue="{Binding Path=CurrentEntity.CategoryId, Mode=TwoWay}">
</ComboBox>
for child(Subcategory) combo i am using:
<ComboBox Grid.Column="1" Grid.Row="1" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Margin="0,2,10,2" TabIndex="1" ItemsSource="{Binding SelectedItem, ElementName=categoryComboBox, Mode=OneWay}" DisplayMemberPath="SubCategoryName" SelectedValuePath="SubCategoryId" SelectedItem="{Binding INVSubCategories, Mode=TwoWay}" >
</ComboBox>
But my child combo items is not populated during form loading as well as parent combo's
selection changed though my parent combo items are populated.
i can't figure out why my child combo is not functioning based on parent combo's
selected item ,please help me.
|
|
|
|
|
I think you are more likely to get an answer posting this on the WPF forum instead of the SQL forum.
But I experienced basically the same thing and finally wrote my own code the old-fashioned way to populate the child table. Though, looking at your XAML you've pretty much setup a logical binding between the two.
Good luck.
|
|
|
|
|
I was sitting out on my porch having lunch when I realized what your problem may be.
I remember there was a property (don't know it right now .. you'll have to search for the obvious in your XAML) which was basically binding to an event to tell the control when to rebind it's data. You need to find that property and bind it to the combobox SelectionChanged event.
|
|
|
|
|
hi iam using sql server 2000
i have table images
brabchid nvarchar(50)
imagenew image
imageold image
at the first time iam storing image in imagenew field then by using update querry i want to copy imagenew field data into imageold field and in imagenew field iam storing new image but it is showing same images can you correct my querry
update images set imageold=imagenew ,imagenew=@imagenew where branchid=@branchid
|
|
|
|
|
hi , sir iam waiting for your reply
|
|
|
|
|
Hi,
Could anybody tell me about Interbase database 6.0 driver's compatibility with different OS as (All Windows/Unix/Linux) based on practical experience,not based on information available on internet as it is not always correct.
Thanks & Regards
Navneet
|
|
|
|
|
I can only confirm that I currently run it on XP (SP3) but it has been on my system since the release of XP. So XP, XP (SP1), XP (SP2) are also all OK.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi,
I stored/retrieved images upto size 64 KB (65535 byte) using CByteArray.But it is not able to save whole image of size 2 MB, partially it saves upto 64 KB of total size 2 MB.
How to use CLongBinary to store image of size > 64 KB (upto 2 MB) with Interbase Database 6.0 driver?
Please provide me example specific to this database driver.
Database table structure is:
Create table "TEST"
(
"Name" varchar(10),
"BlobData" BLOB
);
I tried to check BLOB limit of database table as well.I run following queries on database:
Case 1:
Create table "TEST"
(
"Name" varchar(10),
"BlobData" BLOB(65535)
);
Result: Successfully created.
Case 2:
Create table "TEST"
(
"Name" varchar(10),
"BlobData" BLOB(65536)
);
Result: Dynamic SQL Error Error code -842 shown as: unsigned short integer expected.
So please help me to resove this problem.
Thanks & Regards
Navneet
|
|
|
|
|
Hello
I am trying to get from a mysql tables, all records from one table that do not have a corresponding ID in another, I am using the following query but I'm missing the plot somewhere.
SELECT tbl_band_songs.id3_artist, tbl_band_songs.Artist, tbl_band_songs.id, tbl_band_songs.id3_title
FROM tbl_band_songs, tbl_song_lyrics
WHERE tbl_band_songs.id3_artist = 239 AND tbl_band_songs.id <> tbl_song_lyrics.Sng_ID
Basically I am trying to show the user songs that have no lyrics already enetered so they then have the option to enter them.
Thank you in advance for any help.
regards
Ray
|
|
|
|
|
Never touched mysql in my life, but I think you're looking for NOT IN and a subquery.
SELECT tbl_band_songs.id3_artist, tbl_band_songs.Artist, tbl_band_songs.id, tbl_band_songs.id3_title
FROM tbl_band_songs, tbl_song_lyrics
WHERE tbl_band_songs.id3_artist = 239
AND tbl_band_songs.id NOT IN (SELECT tbl_song_lyrics.Sng_ID FROM tbl_song_lyrics)
|
|
|
|
|
You should do a left outer join from tbl_band_songs to tbl_band_lyrics and then select where tbl_song_lyrics.Sng_ID is null.
If you search on Google you will find plenty of examples that explain what an outer join is and how to code it to get the results you want.
|
|
|
|
|
|
My preference for these kinds of queries is to use NOT EXISTS in the where clause.
select your_field_list
from table_one t1
where not exists ( select t2.id
from table_two t2
where t2.id = t1.id
)
;
I've found this to usually be the most performant, though YMMV.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I had an old boss who swore by this route, in favour of my NOT IN solution above. He said it was much more efficient.
He was right, and I forgot about that whole conversation till I saw your answer.
|
|
|
|
|
I have a table which is some thing like below..
Date ID
2009-07-01 1
2009-07-01 2
2009-07-01 3
2009-08-01 4
2009-08-01 5
2009-08-01 6
2009-09-01 7
2009-09-01 8
2009-10-01 9
2009-10-01 10
2009-11-01 11
....
Now I need to write a query which will show a output like below.
Date Start End
2009-07 1 3
2009-08 4 6
2009-09 7 8
...
How can I do this..
Any help would be highly appreciated
Thanking In Advance
Johnny
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
