|
Hi,
I am 2 fields in my Product table, namely RandAmount and ForeignAmount. When they do a search, there is are 2 radio buttons given the user the option to search for rand amount or foreign amount. And then there is a texbox for the value. Here is my where clause which does not work, it must just work on 1 of the 2 fields, if @CurrencyInd is 1 then it searches on rand amount, and when it is 2 then it searches on foreign amount:
WHERE
p.RandAmount = (CASE @CurrencyInd WHEN 1 THEN @CurrenyAmount ELSE p.RandAmount END)
AND p.ForeignAmount = (CASE @CurrencyInd WHEN 2 THEN @CurrenyAmount ELSE p.ForeignAmount END)
I have also tried the following, but also does not help:
WHERE
CASE @CurrencyInd
WHEN 1 THEN (p.RandAmount = @CurrenyAmount)
WHEN 2 THEN (p.ForeignAmount = @CurrenyAmount)
END
Please can someone help?
Thanks
Brendan
<div class="ForumMod">modified on Tuesday, August 12, 2008 5:39 AM</div>
|
|
|
|
|
|
|
hey text format is not supports thats y its shows like that
|
|
|
|
|
where (@currencyInd = 1 AND p.RandAmount = @CurrenyAmount) or
(@CurrencyInd = 2 and p.foreignamount = @CurrenyAmount)
|
|
|
|
|
Try
where (@currencyInd = 1 AND p.RandAmount = @CurrenyAmount) or
(@CurrencyInd = 2 and p.foreignamount = @CurrenyAmount)
|
|
|
|
|
Create table test(Days char(3),ids int,subject char(10))
Insert into test
Select
'Mon',1,'Eng'
Union
Select 'Mon',2,'Maths'
Union
Select 'Tue',1,'Eng'
Union
Select 'Tue',2,'Maths'
Union
Select 'Wed',1,'Eng'
Union
Select 'Wed',2,'Maths'
Select * from test
Select Distinct Days from Test
Drop table days
Create table days(Hours int,mon Char(20),Tue Char(20),Wed Char(20),Thu Char(20),Fri Char(20),Sat Char(20))
Insert Into Days(Hours)
Select 1 Union Select 2
Select * from test Order by IDS,Days
Select * from Days
Declare @IDS Char(3),@Days Char(3),@Subject Char(10),@SQL Varchar(8000)
Declare Cur Cursor For Select IDS,Rtrim(Days),Rtrim(Subject) from test Order by Days,IDS
Open Cur
Fetch Cur into @IDS,@Days,@Subject
While(@@Fetch_Status=0)
Begin
Set @SQL='Update Days Set '+@Days+'= '''+@Subject+''' Where Hours ='+@IDS+'
'
Print @SQL
Exec (@SQL)
Fetch Next from Cur Into @IDS,@Days,@Subject
End
Close Cur
Deallocate Cur
--Set @SQL='Update Days Set '+ @Days +' ='+ @Subject +' Where Hours ='+ @IDS +'
|
|
|
|
|
Have you tried to work through the problem or are you just asking someone to do it for you?
|
|
|
|
|
Create table test(Days char(3),ids int,subject char(10))
Insert into test
Select
'Mon',1,'Eng'
Union
Select 'Mon',2,'Maths'
Union
Select 'Tue',1,'Eng'
Union
Select 'Tue',2,'Maths'
Union
Select 'Wed',1,'Eng'
Union
Select 'Wed',2,'Maths'
Select * from test
Select Distinct Days from Test
Drop table days
Create table days(Hours int,mon Char(20),Tue Char(20),Wed Char(20),Thu Char(20),Fri Char(20),Sat Char(20))
Insert Into Days(Hours)
Select 1 Union Select 2
Select * from test Order by IDS,Days
Select * from Days
Declare @IDS Char(3),@Days Char(3),@Subject Char(10),@SQL Varchar(8000)
Declare Cur Cursor For Select IDS,Rtrim(Days),Rtrim(Subject) from test Order by Days,IDS
Open Cur
Fetch Cur into @IDS,@Days,@Subject
While(@@Fetch_Status=0)
Begin
Set @SQL='Update Days Set '+@Days+'= '''+@Subject+''' Where Hours ='+@IDS+'
'
Print @SQL
Exec (@SQL)
Fetch Next from Cur Into @IDS,@Days,@Subject
End
Close Cur
Deallocate Cur
--Set @SQL='Update Days Set '+ @Days +' ='+ @Subject +' Where Hours ='+ @IDS +'
|
|
|
|
|
hello,
i m trying to insert data into a database file while developing a desktop application in .net, but even when the ExecuteNonQuery() return 1 the data is visible while the application is running, but when i debug the application again the inserted data is lost........
please help me out with this. i m sending the code below...........
app.config file:
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database\LeaveMgmt.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" >
code snippet:
SqlConnection mycon = new SqlConnection(Properties.Settings.Default.LeaveMgmtConnectionString);
string insCmd = "Insert into lmsLeaveInfo values ('" + empid + "','" + DateTime.Now + "','" + dtpLeaveFrom.Value +"','" + dtpLeaveTo.Value + "','" + cmbReason.Text + "',0)";
if (checkFields())
{
try
{
mycon.Open();
SqlCommand cmd_Ins = new SqlCommand(insCmd, mycon);
cmd_Ins.ExecuteNonQuery();
mycon.Close();
clearFields();
}
}
|
|
|
|
|
Hi,
Quick guess would be that you have a pending transaction which needs to be committed.
Perhaps the easiest way to see this is to run the code to any point where you think the data would be persisted and then see from SQL Server Management Studio if you have locks on the data. If you have locks (specifically X-locks), the transaction is active.
Hope this helps,
Mika
|
|
|
|
|
Hi all .
I Wount To Connection To Sql Server 2005 By NetWork , But The Sql Server Give Me The Error (Can't Connection To Sql server Error 26 ) The Error provider:SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified .
And The
SQL Server Surface Area Configuration ------>
Surface Area Configuration for Services and Connections ---->
Remote Connections------------->
Local and remote connections -------> TCp/IP
But the Error is Stile .
Who I Can Connection To Sel Server 2005 by the NetWork ??
Thanks For Any Boudy Hellp Me ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Thaer
modified on Tuesday, August 12, 2008 6:07 AM
|
|
|
|
|
Hi,
I guess you are connecting to named instance. Check:
- connection string, especially check server name and instance name. (also keep in mind that in c# \ is an escape sequence unless you have @-character before string: "\\\\server\\instance" == @"\\server\instance")
- check that SQL Browser process is running on server
This error can also occur simply because your network doesn't allow communication to the server for example because of a firewall.
Hope this helps,
Mika
|
|
|
|
|
Hi,
I'm trying a stored procedure(in SQL server 2000) which retrieves a table of information that contains text fields. But I couldn't reach as I'm getting struck with an error being thrown as "Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query." Could some one assist me in sorting this out.
The error is in the following line
DECLARE @Olddescription nvarchar(4000)
SELECT @Olddescription = description from @ProblemsTable where rownum = 1
Notes:
@ProblemsTable is a local table being created with the following line of codes
Declare @ProblemsTable table
(
rownum int IDENTITY (1, 1) Primary key NOT NULL,
description text
)
insert into @ProblemsTable SELECT description FROM problems WHERE id = @IncidentID
Thanks in advance,
Kathir
|
|
|
|
|
Obvious isn't it?
SELECT @Olddescription = CONVERT(NVARCHAR(4000), description) from @ProblemsTable where rownum = 1
|
|
|
|
|
I was attending interview recently and was asked a question on SQL. Why should we avoid using cursors inside store procedure. I really didnt know until they asked that question. Can anyone please describe in few lines giving various reasons why we should avoid using cursors inside store procedure.
Thanks
Mark.
|
|
|
|
|
Poor performance. One of the main strengths of a database is performing set based queries/updates. A cursor makes it row based. There are times when it is unaoidable, but whenever possible avoid them
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob for the quick answer.
|
|
|
|
|
Perfect and precise answer![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
i am usind sql server 2000.
pl can anyone tell me how to upload image in the database and also retrive the same.
thanx..
|
|
|
|
|
Check this google result.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
|
hi all,
i use following procedure for backup of a database
Private Sub BackupDatabaseUsinSMO(ByVal DatabaseName As String, ByVal UserName As String, ByVal Password As String, ByVal ServerName As String, ByVal DestinationPath As String)<br />
<br />
Dim conn As ServerConnection = New ServerConnection(ServerName, UserName, Password)<br />
Dim myServer As Server = New Server(conn)<br />
<br />
Dim backup As Backup = New Backup<br />
backup.Action = BackupActionType.Database<br />
backup.Database = DatabaseName<br />
DestinationPath = System.IO.Path.Combine(DestinationPath, DatabaseName + ".bak")<br />
backup.Devices.Add(New BackupDeviceItem(DestinationPath, DeviceType.File))<br />
backup.Initialize = True<br />
backup.Checksum = True<br />
backup.ContinueAfterError = True<br />
backup.Incremental = False<br />
backup.LogTruncation = BackupTruncateLogType.Truncate<br />
backup.SqlBackup(myServer)<br />
<br />
<br />
End Sub
above code works well on XP OS but when i run this code on VISTA machine then it give error "Backup failed for Server 'Machine\Instance'." Inner Exception is {"An exception occurred while executing a Transact-SQL statement or batch."}
anybody have idea that how can i perform BackUp and Restore operation on VISTA .I use SQL Server 2005 Express edition
|
|
|
|
|
It makes no difference for SQL SMO whether it's XP or Vista. I'd hazard a guess that in the server with Vista, SQL Server does not have enough permission to write into the backup folder.
But as I said, this is just a guess.
|
|
|
|
|
str = Application.StartupPath & "\SQLEXPR32\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=" & InstanceName & " SECURITYMODE=SQL SAPWD=sapassword DISABLENETWORKPROTOCOLS=0 "<br />
Shell(str, AppWinStyle.Hide, True)<br />
System.Threading.Thread.Sleep(5000)<br />
Dim MainServer As Server = New Server(ComputerName & "\" & InstanceName)<br />
With MainServer.ConnectionContext<br />
.LoginSecure = True<br />
.ConnectionString = "Server=" & ComputerName & "\" & InstanceName & " ;Trusted_Connection=Yes"<br />
Try<br />
.Connect()<br />
Catch ex As Exception<br />
Application.Exit()<br />
End Try<br />
If MainServer.Databases.Contains(DatabaseName) Then<br />
Else<br />
Dim logstr As String<br />
Dim datastr As String<br />
datastr = Application.StartupPath & "\V.mdf"<br />
logstr = Application.StartupPath & "\V_log.ldf"<br />
'Attach the database<br />
Dim sc As StringCollection<br />
sc = New StringCollection<br />
sc.Add(datastr)<br />
sc.Add(logstr)<br />
Try<br />
MainServer.AttachDatabase(DatabaseName, sc)<br />
Catch ex As Exception<br />
MsgBox(ex.Message.ToString)<br />
End Try<br />
End If<br />
<br />
If MainServer.Logins.Contains(LoginName) Then<br />
<br />
Else<br />
Dim NewLogin As Login = New Login(MainServer, LoginName)<br />
NewLogin.LoginType = LoginType.SqlLogin<br />
NewLogin.DefaultDatabase = DatabaseName<br />
NewLogin.Create(LoginPassword)<br />
<br />
Dim db As New Database<br />
db = MainServer.Databases(DatabaseName)<br />
Dim DBUser As User = New User(db, UserName)<br />
DBUser.UserType = UserType.SqlLogin<br />
DBUser.Login = LoginName<br />
DBUser.Create()<br />
DBUser.AddToRole("db_Owner")<br />
<br />
End If<br />
.Disconnect()<br />
End With
above code works fine on both OS (XP and VISTA)
modified on Tuesday, August 12, 2008 3:23 AM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
