|
Try the following solutions:
1) use the abbreviations 'd' OR 'dd' inplace of day in your syntax.
2) Check out your table. Are you storing your data alongiwth the time in the database. If yes then this query will never return any result
Thanks,
Vikash Kumar Singh
vikash
|
|
|
|
|
i have th followwing qury and i need to show only one record for the customer that is last transiction that he done.
i have three tables and i write the query
SELECT cust_order.order_id AS order_id, customers.fname AS fname, customers.lname AS lname, cust_order.order_date AS order_date, cust_order.order_status AS order_status
FROM cust_order, customers WHERE cust_order.customer_id=customers.customer_id cust_order.order_id In (SELECT DISTINCT order_id
FROM cust_debit WHERE amount>=2000) AND cust_order.customer_id=customers.customer_id and cust_order.customer_id in ( SELECT DISTINCT customer_id FROM cust_order
WHERE order_id IN ( SELECT DISTINCT order_id
FROM cust_debit WHERE amount>=2000);
in which i get the distinct customers from the
SELECT DISTINCT customer_id FROM cust_order
WHERE order_id IN ( SELECT DISTINCT order_id
FROM cust_debit WHERE amount>=2000
when i run the query individually i get distinct(one record for the customer) but when i run the above whole query i got all order which the customer place.
plz its urgant i need ur help
|
|
|
|
|
Please stop using text speak. Read the forum guidelines. Also, we don't care if it is urgent or not.
_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!
|
|
|
|
|
Try this[^]. Is this homework?
|
|
|
|
|
But is it the same guy using two different accounts hoping to get two different answers?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
I know this was urgent for you, but I didn't see you offer to pay for a solution, so I wasn't sure just how urgent it really was. Nevertheless, I did take a few minutes and try to read your select statement. I'm really blind about these things so I took the liberty of reformatting it a bit so I could see what was going on:
SELECT cust_order.order_id AS order_id
, customers.fname AS fname
, customers.lname AS lname
, cust_order.order_date AS order_date
, cust_order.order_status AS order_status
FROM cust_order, customers
WHERE cust_order.customer_id=customers.customer_id
? cust_order.order_id IN (SELECT DISTINCT order_id
FROM cust_debit
WHERE amount>=2000)
AND cust_order.customer_id=customers.customer_id
AND cust_order.customer_id IN (SELECT DISTINCT customer_id
FROM cust_order
WHERE order_id IN (SELECT DISTINCT order_id
FROM cust_debit
WHERE amount>=2000)?;
Apart from putting in spaces, carriage returns, and changing case on a couple of reserved words, I've also placed two '?' marks where I don't understand the query. The first one SEEMS to have a missing operator. The next one SEEMS to have a missing ')'. You said you are running this query and getting results, so I guess I don't understand the dialect you are using. Would it be possible for you to re-write the intention of the query in a dialect I could understand, and re-post it? If so, I might be able to look at it a couple of minutes and POSSIBLY offer some suggestions.
David
|
|
|
|
|
Hello, i have some visual foxpro tables and i try to insert data into them using ole db. I have the following command:
INSERT INTO comanda_a (comanda, data, agent, sofer, client, magazin, discount)
VALUES ('2', '07/20/2007', 104, 1, '10016705', 2, 30)
the comanda_a table looks like this:
---------------------------
comanda | OleDbType.Char
data | OleDbType.DBDate
agent | OleDbType.Integer
sofer | OleDbType.Integer
client | OleDbType.Char
magazin | OleDbType.Integer
discount| OleDbType.Double
---------------------------
After the insert i get a data type mismatch error, i dont know which column.
I never used ole db before, (but i know the sql commands,) if u know some links or tutorials please post them also.
In the meantime i have found a way to insert rows using parameters(in c#).
any help would be greatly appreciated.
-- modified at 7:59 Friday 20th July, 2007
|
|
|
|
|
Using parameters would be the recommendation in any case, since this is a generally more robust solution and avoids the likelihood of sql injection attacks by malicious users. It is most likely the date column that is complaining, or the double column (need a .0?). Parameters solve this by providing a more explicit opportunity for the data conversion, and they encapsulate the variables in the query, minimizing chances for SQL injection from the UI.
|
|
|
|
|
i have string of ids which i am passing as an string like 11,33,454,34,...
now i want to separate this string n do
IF EXISTS (SELECT DISTINCT chRoleId
FROM Users WHERE chRoleId=@RoleIDs)
like this stuff.
that is i need to split the comma separated string . can anyone help me to do it in the stored procedure.
thanks in advance. 
|
|
|
|
|
Well, you can use the CHARINDEX, SUBSTRING, LENGTH and LEFT commands to achieve this effect. In pseudo code, you need to do the following:
Create in-memory table
While length of your string > 0
Get the first part of the string (up to the ,) and insert this into your in-memory table.
Remove the first part of the string from the string (including the comma)
End while
select from your table where the users are in the in-memory table.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Declare @Pos1 Int
Declare @pos2 Int
Declare @idss int
Set @Pos1=1
Set @Pos2=1
While @Pos1
|
|
|
|
|
hai all ,
in database i used only one database for empid (int),username(varchar),password(varchar),status(int),usertype(varchar)
what ever i will assign that values are not inserted into database
iam getting value in text but iam not getting in query.
please see my code
<pre>
Label1 .Text =""; //for displaying in design phase
Label2 .Text ="String.Empty;";
Label3 .Text ="String.Empty";
Label4 .Text ="";
Label5 .Text ="String.Empty";
TextBox1 .Text ="empid"; //for entering
TextBox2 .Text ="username";
TextBox3 .Text ="password";
DropDownList1.DataValueField =""; //status 1 and 2
DropDownList2 .DataValueField ="usertype"; //for normal user and admin
SqlConnection con=new SqlConnection ("uid=sa;password=sa123;database=usermanagement;server=c113");
SqlCommand cmd=new SqlCommand ("insert into tbl_user_management (empid,username,password,status,usertype) values('"+ TextBox1.Text +"','"+ TextBox2.Text +"','"+ TextBox3.Text +"','"+ DropDownList1 .DataValueField +"','"+ DropDownList2 .DataValueField +"')",con);
//SqlDataReader dr;
con.Open ();
//status = Convert.ToInt32(dr["status"]);
cmd.ExecuteNonQuery();
con.Close ();
srinivas
|
|
|
|
|
You really have to use better names for things. Lable5 and TextBox3 really don't help anyone understand your code.
Next up: SQL Injection Attacks - You're code is vulnerable. You should learn about SQL Injection Attacks and Tips on How to Prevent Them[^]
Have you tried running the SQL Statment directly in SQL Server? What was the result?
Do you get any error messages? What were they?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Also, why are you puttin String.Empty inside quotes. This will display the string value "String.Empty", rather than the empty string ""...
|
|
|
|
|
Hi All,
I am trying to optimize a SP which involves cursor for the following DB structure and scenario.
DB Structure:
-------------
--Read it as TableName : (columnname(s))
TableBuyer : (BuyerName,BuyerType)
-- BuyerName is UniqueID
-- BuyerType can hold the value 1 or 2
TableCar : (BuyerName,Warranty,ChassisNumber)
-- BuyerName UniqueID
-- Warranty takes "DEF" as default value
TableCarDetails : (ChassisNumber, BuyerName)
Scenario:
---------
- If BuyerType is 2 then
1) Update warranty column with the value which is equal to corresponding
BuyerName(UniqueID) suffixed with / (forward slash)
2) A Corresponding entry has to be made (Insert Row) in TableCarDetails
with that chassis number and BuyerName
Existing Flow
-------------
The sp involves a temp table which stores the BuyerName where the BuyerType is 2
Based on the BuyerName in temp table all the updations are done thro cursor.
Can anyone throw somelight on how efficient I can tune this sp??
|
|
|
|
|
SwatCats wrote: Can anyone throw somelight on how efficient I can tune this sp??
Figure out a way of doing the same without using cursors. Relational databases are very good at doing set based operations (i.e. operations on large numbers of rows at the same time). The are not very good at working on one row at a time.
UPDATE statments can operate on many rows at once and I would imaging that the updates you want can be completed on large numbers of rows in one operation.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
hi brothers
iam working with Mysql DB on the web .. and i make timer to connection every minute to DB and check the table for last record has been added and show it in msgbox.
after one minute When it reconnection again .. i want it make sure the current result is not Unequalized previous result
iam tried but icant do it .. any one can help me plz ... thanx
---------------------------------------------------------------------------
Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Dim mysqlstr As String
Dim mycomstr As String
Dim numm As Integer
mysqlstr = String.Format("server=localhost;user id=root;password=pass; database=mostashark; pooling=false;charset=utf8")
myconn = New MySqlConnection(mysqlstr)
mycomstr = "select T_Id,T_Exp_id,T_Text from twsyat order by T_Id DESC limit 1"
mycom = New MySqlCommand(mycomstr, myconn)
myconn.Open()
myreader = mycom.ExecuteReader()
Dim msg As New frmMessage
Do While (myreader.Read())
myreader.Read()
If myreader.Item("T_Id") > numm Then
dim MBoxText as string = myreader.Item("T_Text")
msgbox(MBoxText)
numm = myreader.Item("T_Id")
End If
Loop
End Sub
|
|
|
|
|
Do you have any kind of timestamp field in your table?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
no brother my table with this column : msg_id msg_user msg_txt
But i can add timestamp field if u have any solution with it
thanx
|
|
|
|
|
Dr.PHP wrote: But i can add timestamp field if u have any solution with it
Just do either one of these to add the timestamp field...
If you don't care about the timestamp being possibly null:
alter table {YourTableName} add ( msg_timestamp datetime )
or, if you do not want it to be null:
alter table {YourTableName} add ( msg_timestamp datetime NOT NULL )
{YourTableName} needs to be substituted with the actual name of the table.
Hope this helps,
Paul
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
i have one varchar variable @v which has value like '@p5','@p6' etc.
i have @p5, @p6 variables in my stored procedure.
when @v's value is '@p5', then value of @P5 will be used.
and so on.
how can i do that
|
|
|
|
|
if @v = '@p5'
Begin
--Use @p5 variable here
End
|
|
|
|
|
but i have @p1 to @p30 variables.
and i have to use this in loop
|
|
|
|
|
This sounds like you have a real design problem. I would suggest that you need to take a long hard look at your design here.
Plus, why are your variables called @p1 to @p30? What do they do? How can you tell what the purpose of these variables are? Out of the following, which do you think will help you to maintain your code in 6 months time:
declare @p1 nvarchar(20) or
@declare @first_name nvarchar(20)
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
my table have columns p1,p2,..p30,
in stored procedure using cursor this values are fetched in @p1, @p2..,@p30
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
