|
You've declared @BDATE twice in your SP - once as a parameter and secondly as a variable. This code won't complile - remove your variable declaration.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
You should really pass @ID in. Change it to:
dbo.uspGetBirthdayFriends
@ID INT,
@BDATE SMALLDATETIME
AS
begin
select * from Friends where UserID=@ID and FBirthday =@BDATE
end
RETURN From what I saw of your earlier post, you also seem to have a problem with the way you are handling your parameter. When you add in the SqlParameter, you need to put the parameter in with an @ in front of the name, e.g. command.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
|
|
|
|
|
Thank you very much. Both of you. I finaly solved the problem.
rzvme
|
|
|
|
|
pmarfleet wrote: You don't need to do any string concaternation here.
Yes, that is dangerous.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I wrote the following code. application runs , no error but it does not display any data. What will be the problem with it? some one please help me.although code i tokk is from book, but still there is problem...
using System;
using System.Data.SqlClient;
namespace ConsoleApplication2
{
public class ExecuteReadExample
{
public static void Main(string[] args)
{
// string source = "server = \\10.8.7.67" + "integrated security=SSPI;" + "database=Northwind";
//string select = " SELECT ContactName, CompanyName FROM Customers";
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; User Id=admin; Password="
SqlConnection conn = new SqlConnection(source);
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
Console.WriteLine("Contact : {0,-20} company : {1}" , reader[0] , reader[1]);
}
}
}
}
Safdar Khan
|
|
|
|
|
Check your query. Does it return any data if you run it through Access?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks a lot. OK,I do that but still it didn't work.
Safdar Khan
|
|
|
|
|
Look at Mike's reply[^]. What he says is correct. You are using the SQL Server classes when you should be using the OleDb classes.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
That's an OLE DB connection string to use the Jet database engine in-process. It won't work for SQL Server.
If you want to connect to SQL Server, use a SQL Server connection string. If you want to use Jet with that connection string, use OleDbConnection.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
<br />
SELECT ID, time, type, ((first + last) * 0.5) AS avg10, (CASE WHEN (a.ID+1) <br />
% 5 = 0 THEN<br />
(SELECT ((c.first + c.last) * 0.5)<br />
FROM a, tmp_ak000002_50 c<br />
WHERE c.ID = (a.ID + 1) / 5 - 1) WHEN a.ID =<br />
(SELECT MAX(a.ID)<br />
FROM a) AND (a.ID + 1) % 5 != 0) THEN<br />
(SELECT ((c.first + c.last) * 0.5)<br />
FROM a, tmp_ak000002_50 c<br />
WHERE c.ID = (a.ID + 1 + 4) / 5 - 1) ELSE NULL END) AS avg50<br />
INTO NewTable<br />
FROM tmp_ak000002_10 a<br />
-- modified at 8:34 Wednesday 28th November, 2007
|
|
|
|
|
SELECT ID, time, type, ((first + last) * 0.5) AS avg10,
( CASE WHEN (a.ID+1) % 5 = 0 THEN
(
SELECT ((c.first + c.last) * 0.5) FROM a, tmp_ak000002_50 c WHERE c.ID = (a.ID + 1) / 5 - 1)
WHEN a.ID = (SELECT MAX(a.ID) FROM a) AND (a.ID + 1) % 5 != 0 THEN
(
SELECT ((c.first + c.last) * 0.5) FROM a, tmp_ak000002_50 c WHERE c.ID = (a.ID + 1 + 4) / 5 - 1)
ELSE NULL END) AS avg50
INTO NewTable
FROM tmp_ak000002_10 a
oops...I didn't see the [solved] in the title....
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
i cant solve this problem 
adding with this cmd ;
cmdInsert.Parameters.AddWithValue("@RECORD_DATE", DateTime.Today.ToString("dd/MM/yyyy"));
Gettin' this error.
Arithmetic overflow error converting expression to data type datetime.
|
|
|
|
|
What's wrong with:
<br />
cmdInsert.Parameters.AddWithValue("@RECORD_DATE", DateTime.Today);
What data type is record_date?
|
|
|
|
|
oops i found problem.. Problem is my sql command . i was changing datetime.today to string and getting error. thanx for help Paddy Boys .
|
|
|
|
|
there is one small thing
cmd.CommandText = "SELECT KODU,CDISMI,CDTURU,CDADET,ACIKLAMA FROM TBL_PS_2" +
" WHERE RECORD_DATE BETWEEN '" + txtDate1.Text + "' AND '" + txtDate2.Text + "' ";
but getting error about datatype char conversion to datetime bla bla.
|
|
|
|
|
Be careful to send the DateTime-value to the Sql-Server in the right format. If your database-language is set to English(US), maybe you should try to convert the DataTime to this format with a CultureInfo-object.
<br />
CultureInfo ci = new CultureInfo("en-US");<br />
DateTime begin = DateTime.Parse(txtDate1.Text, ci);<br />
DateTime end = DateTime.Parse(txtDate2.Text, ci);<br />
Your SQL-command should then look like this:
cmd.CommandText = "SELECT KODU,CDISMI,CDTURU,CDADET,ACIKLAMA FROM TBL_PS_2" +
" WHERE RECORD_DATE BETWEEN '" + begin.ToString() + "' AND '" + end.ToString() + "' ";
Maybe this solves your problems.
|
|
|
|
|
cmd.CommandText = "SELECT KODU,CDISMI,CDTURU,CDADET,ACIKLAMA FROM TBL_PS_2" +<br />
" WHERE RECORD_DATE BETWEEN '" + txtDate1.Text + "' AND '" + txtDate2.Text + "' ";<br />
Just for your info, that would be subject to dangerous sql injection attacks.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hello Forum!
Here is my problem: I have a table called TblArticle (artnr, comment) and a table called TblArticlemoves (autoid, artnr, quantity, date). Now I need a report that looks like the following example:
artnr ....|01.11.07 | 02.11.07 | 03.11.07 | 04.11.07 | 05.11.07 | ...
A ........| 20 ........| 20 .........| 35.......... | 45 ........| .........15 | ...
B........ | 5 ..........| 15......... |.25 ..........| 5 ......... | ..........20 | ...
.
.
.
The main thing is I need to display each day of a month, but I 'm not having an entry for each day of a month in my table TblArticlemoves. Therefore i need to calculate the stock quantity for each day of the month. How can i do this?
regards adyck
|
|
|
|
|
you can loop through all the days of months and store count of moves in another table.
|
|
|
|
|
u can create a proc with following steps which will return a table..
1) Get no. of days in month. say @no and @p = 1
2) while (@p < @no)
3) Get date and write query GetDate[^]
4> create table with date and count and return it.
Lemme know if it is useful...
|
|
|
|
|
Hello Prateek G,
excuse me, I'm a beginner but
1)what do you mean with @p?
3)can You give me some more hints
4)do You mean selecting the count from TblArticlemoves into a Date table?
Thank You!
Regards adyck
|
|
|
|
|
Doing this with straightforward SQL might get messy and complex. If you're up to it, create a temp table, fill in the days of the month and the stock value and PIVOT it to form the report.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi SG,
I've managed to create a temp table like this:
Dim dt As DataTable = New DataTable("TblDate")
dt.Columns.Add("artnr", Type.GetType("System.String"))
Do While startDate < endDate
dt.Columns.Add((startDate), Type.GetType("System.String"))
startDate = startDate.AddDays(1)
Loop
Now, how do fill in the stock values into the TblDate?
Regards adyck
|
|
|
|
|
I meant a Temp table in SQL Server. Instead of creating a tables with dates as columns, you can use the PIVOT command to prepare your report. PIVOT [^]is easy to master. Just look it up.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
hi all
i have a SQL Query that is like this
<br />
select p1.Property_ID,p1.Lis_key,p1.Func_key,p1.Attrib_code<br />
from sde.property p1<br />
where p1.func_key = 'PV000000'<br />
and exists (select 1<br />
from sde.property p2<br />
where p2.lis_key = p1.lis_key<br />
and substring(p2.func_key,1,5)='GEOSS'<br />
and substring(p1.Attrib_code,3,2)=22<br />
having count(p2.func_key) = 2)<br />
order by Actual_extent Asc<br />
<br />
And this Query works fine, and i want to update the records found by this query above,i tried this
<br />
update sde.property<br />
set Attrib_code = substring(Attrib_code,1,2)+ '10'+ substring(Attrib_code,5,8)<br />
where Exists (select p1.Property_ID,p1.Lis_key,p1.Func_key,p1.Attrib_code<br />
from sde.property p1<br />
where p1.func_key = 'PV000000'<br />
and exists (select 1<br />
from sde.property p2<br />
where p2.lis_key = p1.lis_key<br />
and substring(p2.func_key,1,5)='GEOSS'<br />
and substring(p1.Attrib_code,3,2)=22<br />
having count(p2.func_key) = 2 and <br />
P1.aCTIVE =1))<br />
<br />
The above query will try to execute the whole day, please help me combine the Query to create an update from these update that will update the records found in the first query and please can you explain, so that i can see my mistakes.
<br />
<br />
update sde.property<br />
set Attrib_code = substring(Attrib_code,1,2)+ '10'+ substring(Attrib_code,5,8)<br />
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
