|
Thanks Pete, Thanks for your idea. I was unnessacesary using cursur
THanks once again.
regards
imran khan
|
|
|
|
|
That's a bit arcane. I'd use
IF EXISTS
( SELECT * FROM branchwisecharge
WHERE branchid = @branch AND serviceid = @serv )
BEGIN
INSERT ...
END
ELSE
BEGIN
UPDATE ...
ENDNote that IF/ELSE in T-SQL is like if/else in C: the IF only governs the immediately following statement, not everything up to the ELSE. If you want to put multiple statements after an IF, you must use a BEGIN/END block (analogous to {} in C). Stylistically, I always use a block after an IF for the same reason as in C - it can be easy to get confused about whether a statement is controlled by the IF or not.
|
|
|
|
|
I have a table with a bitmask value column and would like to select from this table where records have any match with another bitmask value.
For instance 3 database records:
ID, bitmask_value
1, 1
2, 3
3, 16
To check if a single bit is selected, I can:
SELECT * FROM table WHERE (bitmask_value & 1) = 1
(checks if bit 1 is selected - returns ID 1 and ID 2)
But I would like to get all records where a criteria bitmask value I define is selected, such as, user selects 1 and 2 = 3.
I could do this:
SELECT * FROM table
WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2)
BUT if my criteria bitmask value includes several bits, can I query a different way to compare bitmask_value and criteria_bitmask, and get the proper results without checking each bit individually?
So given a criteria_bitmask value of 3... can I query using the value 3 somehow and get the following:
-any rows with 1 selected
-any rows with 2 selected
-any rows with 1 and 2 selected
-any rows with 1 and any others selected
-any rows with 2 and any others selected
-any rows with 1 and 2 and any others selected
I'm only filtering rows that DO NOT have a 1 or a 2 or both a 1 and a 2.
|
|
|
|
|
Sorry, some of your terminology is confusing me.
Leftyfarrell wrote: 1 and 2 = 3.
Actually 1 AND 2 = 0. Did you mean 1 OR 2?
Leftyfarrell wrote: SELECT * FROM table
WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2)
What about:
WHERE (bitmask_value & 3) <> 0
bitmask_value result
0 0
1 1
2 2
3 3
4 0
5 1
6 2
7 3
8 0
etc.
|
|
|
|
|
I did mean 1 OR 2 selected. By saying 1 and 2 = 3, I meant that the search criteria is defined for me by a user selecting options, and if they select options 1 and 2, I need to pull anyone that matches 1 OR 2. Their interface assumes an OR condition on the options they select.
I believe what you have provided is what I need. I just need to test a bit more.
Thank-you.
|
|
|
|
|
I currently store wav binary data in my database, and I am wondering how to play the files on my website. The binary files are placed into the database through another VB.NET application. What I want to do is have an ASP grab those files from the database and play them for the user. I have tried using ADODB.Stream, but it doesn't seem to be working. I also can spit out all the binary data as plain text, but that doesn't play either.
|
|
|
|
|
In the .aspx file to play the sound in:
<object classid="clsid:22D6F312-B0F6-11D0-94AB-0080C74C7E95">
<param name="MyFile" value="LoadMyFile.aspx?FileID=123" />
</object>
In the LoadMyFile.aspx.cs file (sorry, I don't have an example in vb but the syntax should be similar).
int FileID = Convert.ToInt32(Request.QueryString["FileID"]);
byte[] SoundData = LoadSoundDataFromDatabaseAsByteArray(FileID);
Response.Clear();
Response.ContentType = "audio/wav";
Response.OutputStream.Write(SoundData,0,SoundData.Length);
Response.End();
The html content of the LoadMyFile.aspx is replaced by the content provided in Response.OutputStream.Write... (which in this case is your sound data). Changing the Response.ContentType to "audio/wav" lets the browser to expect wav data and not html.
Hope that gets you in the right direction
|
|
|
|
|
Chris Buckett wrote: the syntax should be similar
I think you mean semantics. Syntax refers to the grammar, while semantics refers to the meaning.
|
|
|
|
|
I bow to your obvious greater linguistic skills then 
|
|
|
|
|
How can I build this query for date input also:
str1 = string.Format("INSERT INTO Table1(ERROR, STATION, TIME) VALUES ({0},{1}, CONVERT(DATETIME, '2009-09-09 00:00:00', 102))",x,y) ;
I want that the date will be variable
thankss
|
|
|
|
|
You are injecting values into the SQL statement. This will compromise security of your application. See SQL Injection Attacks and tips on how to prevent them[^]. If I recall this was mentioned to you before[^].
By solving the security problem, you also solve the problem of formatting dates. Part of the solution to the security problem is to use parameters. This then removes the need for you to worry about how to format dates in SQL because you can pass a DateTime object as a parameter.
string sql = "INSERT INTO Table1(Error, Station, Time) VALUES (@error, @station, @time)"
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Parameters.Add("@error", x);
cmd.Parameters.Add("@station", y);
cmd.Patarmters.Add("@time", someDateTimeObject);
|
|
|
|
|
thanks,
I'm doing my first step in SQL so I'm trying
to do the simple things first...
|
|
|
|
|
Hi,
I didn't know where I should post this, but because it is ADO.NET, it would be the best place (but I have posted in the C# forum also).
Anyway, I have a question, I have created a datasource to the northwind database and created a simple windows form. With the first name, last name textboxes etc. and to select the employees I use the listbox with the employees last names in it. I have added an the TextBox_Validate Event to each textbox with the code:
<br />
private void TextBox_Validated(object sender, EventArgs e)<br />
{<br />
employeesTableAdapter.Update(northwndDataSet1.Employees);<br />
<br />
employeesTableAdapter.Fill(northwndDataSet1.Employees);<br />
}
Yet, when I change a name, and then tab or click somewhere else, it doesn't update the database with the new name.
What am I doing wrong?
Thanks,
Any reply is appreciated.
|
|
|
|
|
Hi - I added a new table. Tested on two different databases. One of the database had db_owner role checked for this user and inserting into this table worked. The other database didn't have this option checked. By default this option is not checked for our database.
Is there a way I could set permission on table level, just for this table?
thanks
|
|
|
|
|
In my stored proc, changed a DML "truncate" to use "delete" - it is now working.
|
|
|
|
|
Hi,
I want to know,How to know Store Procedure Input and Output parameters.
What I want.
When I pass any Store Procedure Name.it's return that Store Procedure Input and Output parameters.
Help me out
it's very arrgent.
Thanks
|
|
|
|
|
|
Hi,
I am using DateTime data type in my SQL Server 2000 to store the date.
When I am retrieving the date from my DB, it will appended with TimeStamp
eg. 01/04/2007 12:00:00 . But I wish to display only the Date in the TextBox of my Web Page.
I am confused whether I can use Char or Varchar data type in my SQL Server DB to store Date Value or Any other way to convert the DateTime format of SQL Server into Date only format.
Kindly help me.
Regards,
Jay
|
|
|
|
|
Hi
Use This
select
Convert(varchar(12),DateTime,101) AS DateTime
from
Student
this will work for you
|
|
|
|
|
Jay_se wrote: When I am retrieving the date from my DB, it will appended with TimeStamp
eg. 01/04/2007 12:00:00 . But I wish to display only the Date in the TextBox of my Web Page.
I am confused whether I can use Char or Varchar data type in my SQL Server DB to store Date Value or Any other way to convert the DateTime format of SQL Server into Date only format.
It is considered bad practice to store dates as strings. SQL Server has a DATETIME column type that you can use that is culture indepenent.
It is better to render the date in a culture specific way in the application's presentation layer. When a DATETIME column is read into a .NET application you get a .NET DateTime object. You can use its ToString() method to define exactly how you want the date to be formatted. e.g.
myDate.ToString("dd/MM/yyyy");
A quick reference off the top of my head:
d: day
dd: day with leading zero
h: hour (12)
hh: hour with leading zero (12)
H: hour (24)
HH: hour with leading zero (24)
m: minute
mm: minute with leading zero
M: month
MM: month with leading zero
MMM: short month name
MMMM: long month name
yy: short year
yyyy: long year with century
|
|
|
|
|
Thanks Mr.Colin,
Its very much helpful for me to implement in efficient way.
Regards,
Jay
|
|
|
|
|
Hello,
I am trying to connect to a sql server 2005.
Under the surface server configuration - remote connections i have set it for named pipes and tcp/ip. Is there something else i should do with the configuration?
I have also turned of the firewall, in case that has caused a problem.
my connection string is below.
<br />
Try<br />
cnn.ConnectionString = "data source=1.1.1.1,1433\ssd01;Network Library=DBMSSOCN;database=serviceAcc; user id=; pwd=;persist security info=True; encrypt=yes"<br />
<br />
Catch ex As Exception<br />
Console.WriteLine(ex.Message)<br />
End Try<br />
I have changed this a few time and the error message:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"
Thanks in advance,
Steve
|
|
|
|
|
Hi,
It seems that you are using named instance. In such a case you should check what is the port of that named instance in order to point your connection string correctly.
For example, if your port number is 1550 you should specify the connection string as follows:
Network Library=dbmssocn;Data Source=127.0.0.1,1550;...
Hope it helps,
Uri
|
|
|
|
|
Using SQL Server (2005), and performing an INNER JOIN, let's say...
SELECT * FROM Employee E INNER JOIN Address A ON E.EmployeeId=A.EmployeeId WHERE E.Department='Sales'
It has occurred to me to wonder; mightn't it be more efficient to write that as...
SELECT * FROM Employee E INNER JOIN Address A ON E.Department='Sales' AND
E.EmployeeId=A.EmployeeId
But then I think; surely the engine is smart enough to optimize something so simple if it makes a difference.
I've tried executing a few of my joins both ways, and there's little difference between the two forms.
Can anyone here tell me whether or not such an optimization is performed? Or worthwhile?
|
|
|
|
|
I'd write it this way instead
select e.*
from employee e
where e.department = 'Sales'
and exists ( select a.EmployeeId
from Address a
where e.EmployeeId = a.EmployeeId
)
;
I've always found using the 'exists' clause with a second query to be more performant than trying to use a join (even though logically they are equivalent).
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
