|
Hi i have created one web application and in that i need to read the data from Mysql database and bind to gridview.
I have written Mysql query as follows
SELECT @row_number:=@row_number+1 AS row_number,Name FROM tablename, (SELECT @row_number:=0) AS r
After running in Mysql in database it will and work show the result.
But if take same query and execute through C# code below error is coming.
An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code
Additional information: Fatal error encountered during command execution.
and i will get Break or continue pop up. If i give continue it will show Parameter '@row_number' must be defined.
even i defined like int @row_number = 0; but same error.
I need to achieve serial number as shown below
ex in database
slno name
10102 nama1
2123 name2
5203 name3
result
1 nama1
2 name2
3 name3
how to achieve this. If anybody knows please reply me.
Thanks in advance.
|
|
|
|
|
This error occurs frequently and no insert query is running.
if i restart the sql server services it is working fine,but often getting this error message.
Recently i changed my management studio to 2012 as my server is 2008 R2 version.How to fix this issue permanently?
|
|
|
|
|
Hi,
Usually it's because there are corruption problems related to the storage system. Have you tried running DBCC CHECKDB against the database?
DBCC CHECKDB ('Your DB Name') WITH NO_INFOMSGS;
GO
This could give you more information.
Also, check the Auto Close property:
SELECT DATABASEPROPERTY ('Your DB Name', 'IsAutoClose');
GO
If it returns 1 (Auto Close is on), then that may be the reason of your problem...
Regards,
Andrius Leonavicius
|
|
|
|
|
Dear Sir,
It shows the following error
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created.
Either the reason is given in a previous error or one of the underlying volumes
does not support sparse files or alternate streams. Attempting to get exclusive
access to run checks offline.
my database shows is_auto_close_on=1.
What i should do to solve this?
|
|
|
|
|
I'd recommend to turn off the Auto Close:
ALTER DATABASE database_name
SET AUTO_CLOSE OFF;
GO
Speaking about errors, try to restart the server and run DBCC CHECKDB again. Also, take a look at Windows Event Logs for errors.
BTW, do you have enough disk space?
Regards,
Andrius Leonavicius
|
|
|
|
|
The Log For the Database is Not Available[^]
And please, do not post your question all over the place...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
hi , i have two table first table name is person and second table name is payment
what i'd like to do is that , i want to select those persons who haven't paid they'r bill for two month
select person.* from (person INNER JOIN
payment ON person.national_number = payment.national_number)
where (payment.payment_date > 'two month ago from now')
but this w'll fetch those persons who paid their bill for the two month ( i don't want them) . but i need those who didn't pay they'r bill for the two month
i don't know if you understand it or no .
|
|
|
|
|
Surely
where (payment.payment_date < 'two month ago from now')
?
|
|
|
|
|
Assuming that one person can have multiple payments, an INNER JOIN will return a record for every payment over two months old, regardless of whether the person has paid within the last two months.
What you need to do is find the person records which don't have a payment record within the last two months. To do that, you can use a Not Exists filter:
DECLARE @Today date = GetUtcDate();
DECLARE @CutoffDate date = DateAdd(month, -2, @Today);
SELECT
*
FROM
person
WHERE
Not Exists
(
SELECT 1
FROM payment
WHERE payment.national_number = person.national_number
And payment.payment_date > @CutoffDate
)
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Won't that have to look up each national_number in payment separately? It always looks like it does.  I don't use EXIST.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
No, the SQL optimizer is pretty good at generating a sensible query plan. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
So..
I would never show up in your list if I paid a dollar each month?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: Bastard Programmer from Hell
Oh so appropriate.
Should be renamed The Edge Case from Hell
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT person.*
FROM person
LEFT OUTER JOIN payment
ON person.national_number = payment.national_number
AND payment_date > 'two month ago from now'
WHERE payment_date IS NULL
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Hi,
I have employee_number field which has employee_numbers like this:
12
434
ABC345
QWR-567
I want to know how can I just select the numeric values to perform a MAX on it?
so ABC345 and QWR-567 will not be considered in the SELECT
Technology News @ www.JassimRahma.com
|
|
|
|
|
You could always try a regex to parse out only the numbers.
|
|
|
|
|
|
Some guidance on the database you are using would be useful.
Take a look at the ISNUMERIC keyword in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
WHERE id NOT LIKE '%[a-z]%' will eliminate any with alphabetic characters, that's a start, work from there.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I tried this:
SELECT employee_number FROM employees WHERE employee_number REGEXP ('[0-9]');
it's working excellent and I getting these numbers
7, 8, 9, 11, 1.222, 12, 13, 9, 15, 9, 9, 9
BUT....
when I run:
SELECT MAX(employee_number) FROM employees WHERE employee_number REGEXP ('[0-9]');
I get 9 where i should get 15.
why is that?
Technology News @ www.JassimRahma.com
|
|
|
|
|
Because they're strings; you'll need to convert to numeric fo rthe comparisons to work the way you want.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I have been searching all day to find a better answer to my question but those I found was useful
but do not critically analyse my question.
I am building a chat room using php, mysql, jquery and ajax.
The target group members are 3000 people who will frequently
chat every day, so I am expecting like one million messages a day.
The chat room is open to all the members, that means
every member has the same priviledge to send and view
sent messages.
In this case, every member has the permission to delete messages whether sent by him or different members,
however deleted messages should not affect other users. I wouldnt also keep messages for more than two days,
meaning every sent message should be deleted after two days.
Below are the sample tables that represents the logics above
users table
| u_id |
| name |
messages table
| msgID |
| msgBODY |
| msgTime |
| senderID--FK |
| deleted_by_sender|
recipient table
| recID |
| msgID--FK |
| recipientID ---FK |
| deleted_by_recipient|
Now, if I am to implement the schema above, it means that every single sent message has to do 3000 inserts into the
recipient table and one insert into the messages table. This also means that if there are 50 sent messages within 1 minute there would be more
inserts within the one minute. At the same time 3000 people are viewing the messages. Awwsh! more work load on the database within that minute.
hmm!
Please is there a way to handle this, scalability and performance wise?
Any idea is appreciated. Thanks.
|
|
|
|
|
Delete the recipient-table, and add a table like below;
DeletedMessagesPerUser
UserId
MessageId
Add a record to that table when the user "deletes" a message. When fetching all messages for a particular user, fetch all messages where the messageId isn't named in the DeletedMessagesPerUser table.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thats a good idea. Please if i want to track unread messages per a user, it means i would have to create another table for the unread messages,right? or there is a better way to deal with it
|
|
|
|
|
Member 10949986 wrote: it means i would have to create another table for the unread messages,right?
Yup - a table with both a reference to the user, and a reference to the messages. Any message-id that's not in the table for that user, would be considered 'unread'.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
