|
Mohammad Daba`an wrote:
its a wrongggggggggggggggg answer
That was not very polite.
Mohammad Daba`an wrote:
i found another soultion which is left join or full join with omition of the alter
Huh?! Your original request was "i need to change the following code to another one which does the same function without using join, left outer joi, or any join statements"
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hmmm... strange select.
First of an outer join without a join... hmmm..
What the result is I can not predicct... What is the result you are looking for...?
/M
|
|
|
|
|
i have the following code:
while(true)
{
Thread.Sleep(delay);
SqlConnection connection = new SqlConnection;
connection.Open();
'some code here'
connection.Close();
}
i'm wondering how many sqlconnection will be made? after a while is it possible to get an "System.NullRefferenceException: Object not set to an instance of an object"(probably because the new connections are instantiated, but never disposed)?
thanks.
MetalSandman.
|
|
|
|
|
|
i have a project written in C# using and ODBC database that use SQL pervasive
SQL Driver ,
Problem:
when i run the windows application that lunch the report in my Computer every thing works fine
but when i run the application and try to run report from another computer that have the same ODBC access the Application Threw an Exception Message Said 'ReportDocumetn Threw Exception'
//---Notes
the Report is built by VS.NET Crystal Report IDE : its a Class
DataAdapter filling Data in the Other Machine : C# Code have No Error
Go East Go West C# is the Best
agmb
|
|
|
|
|
Can you force the SELECT command to select only a single row?
Im using the following:
SELECT * from SomeTable(UPDLOCK) where....
I don´t wanna lock all the tables that the SELECT returns, I wanna lock ONLY one. Is that possable?
|
|
|
|
|
|
hello
just write the query..
select top 1 from tablename where ..........
it will fetch only the first record that matches the condition...
hope will work for u.
bye
pankaj
|
|
|
|
|
execute @OLEResult = sp_OAMethod @FS, 'name',@FileName out
i'm not getting the filename using the above line in my SQL SP
Regards
Wilbur J. Pereira
"If they love me let them, for the heck of it.If they don't, who da heck cares about it"
|
|
|
|
|
Hello all,
I"m trying to write the core portion of a app that will sum the total amount (Amount)of money collected from start to end of a shift(Shift_Number), then return that value to (-currently-) the Console. I can get the the app to run, take the inputs of the machine name (Location_ID) and the last shift (Shift_Number), but it failes after it takes the last input,I get "Specified cast is not valid". I've enclosed the code, the database is sql, i'm sure you could change it to northwind and find a table with some numbers to sum and check against. written in C#, and using the console app template...
any help will be most apreciated!
<code>static void Main(string[] args)
{
try
{
SqlConnection LoadLastConnection = new SqlConnection();
LoadLastConnection.ConnectionString = "User ID=blah;Password=Blah;Initial Catalog=sellmdat2kSQL;Data Source=jpltpharos";
LoadLastConnection.Open();
Console.Write("Please enter a Machine_Number(4 characters): ");
string Machine_Number = Console.ReadLine();
Console.Write("Please enter the Last_Shift_Number(4 characters): ");
string Last_Shift_Number = Console.ReadLine();
SqlCommand LoadLastInvoiceCmd = new SqlCommand();
LoadLastInvoiceCmd.Connection = LoadLastConnection;
LoadLastInvoiceCmd.CommandText = "SELECT sum (Amount) From Transaction_table WHERE Location_ID = @machineName and Shift_Number = @ShiftNumber" ;
LoadLastInvoiceCmd.Parameters.Add("@machineName", Machine_Number);
LoadLastInvoiceCmd.Parameters.Add("@ShiftNumber", Last_Shift_Number);
SqlDataReader dataReader = LoadLastInvoiceCmd.ExecuteReader();
while (dataReader.Read())
{
int last_invoice = dataReader.GetInt32(0);
string Amount = dataReader.GetString(1);
Console.WriteLine("Current Invoice Period is {0}\nAnd the Cash Value is {1}\nTotal Dollars", last_invoice, Amount);
}
dataReader.Close();
LoadLastConnection.Close();
}
catch(Exception e)
{
Console.WriteLine("An error occurred accessing the database:" + e.Message);
}
}</code>
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
I'd say one of these is the problem:
int last_invoice = dataReader.GetInt32(0); string Amount = dataReader.GetString(1);
It would help more than a little if you first took the time to see what line is blowing up, and checked the data in the dataReader to see what it's trying to convert. Best guess, one of these is coming back with a NULL.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
ok,
so I've changed it a small bit, checked it, done the stack trace, and it is still failing,
but the catch -- e.Message is giving the following: incorrect syntax near 'transaction_ table'
and the catch -- e.StackTrace is giving the following:
at system.data.sqlclient.sqlcommand.executereader(commandbehavior cmdbehavior, runbehavior runbehavior, boolean returnstream)
at system.data.sqlclient.sqlcommand.executereader()
at reportorders.report.main(string[] args) in c:\documents and settings.....(it list the filename)report.cs:line28
Press any key to continue
line 34 is not the FROM Transaction_Table statement line...but rather the dataReader execution statement.
I've double checked query in query analizer and it works 
what am I missing here? can I not do an aggregate function with the dataReader? 
I will enclose the code again, in its modified version... (again, i'm using the C# console app template in vs.net 2003)
Also, you can comment / uncomment out the "Sequence_Number" from the query and from the
int Sequence_Number = dataReader.GetInt32(0); parts, as that is the primary key
Thanks, again...
using System;
using System.Data.SqlClient;
namespace ReportOrders
{
class Report
{
[STAThread]
static void Main(string[] args)
{
try
{
SqlConnection dataConnection = new SqlConnection();
dataConnection.ConnectionString = "user ID=blah;pwd=BLAH;Initial Catalog=sellmdat2kSQL;Data Source=localhost";
dataConnection.Open();
Console.WriteLine("Please enter a Location ID (4 characters): ");
string Location_ID = Console.ReadLine();
Console.WriteLine("Please enter a Shift Number (4 characters): ");
string Shift_Number = Console.ReadLine();
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = "SELECT sum (Amount)as Amount";
dataCommand.CommandText += "FROM transaction_table WHERE Location_id ='" + Location_ID + "' and Shift_Number='" + Shift_Number + "'";
Console.WriteLine("About to execute: " + dataCommand.CommandText);
SqlDataReader dataReader = dataCommand.ExecuteReader();
while (dataReader.Read())
{
int Sequence_Number = dataReader.GetInt32(0);
if (dataReader.IsDBNull(2))
{
Console.WriteLine("Shift Number {0} not yet closed\n\n", Shift_Number);
}
else
{
string Amount = dataReader.GetString(0);
Console.WriteLine("Value {0}\nAdded Since Last Shift ", Amount );
}
}
dataReader.Close();
dataConnection.Close();
}
catch(Exception e)
{
Console.WriteLine("An error occurred accessing the database: " + e.StackTrace);
}
}
}
}
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
wetrivrrat wrote:
dataCommand.CommandText = "SELECT sum (Amount)as Amount"; //dataCommand.CommandText = "SELECT Sequence_Number, sum (Amount)as Amount"; dataCommand.CommandText += "FROM transaction_table WHERE Location_id ='" + Location_ID + "' and Shift_Number='" + Shift_Number + "'"; Console.WriteLine("About to execute: " + dataCommand.CommandText);
If you look closely, your strings don't end with, or begin with a space. So you're trying to execute this:
SELECT sum (Amount)as AmountFROM transaction_table WHERE Location_id ='etc
when you want this:
SELECT sum (Amount)as Amount FROM transaction_table WHERE Location_id ='etc
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
ok, so that was the easy part,
now, i'm back to the earlier error of Specified cast is not found, and as you suppected the string Amount = dataReader.GetString(0); is the bad guy, but I know the value returned is not null, as i've ran the query before to test it, is my problem due to the fact that i'm creating a new field and it can't ref the new field? I know it is pulling the data down though because if i run it with the primary key i get a return that the return data is outside the boudaries of the array, which was expected due to the way sql returns the data... so ........
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
I've even changed my query to select sum (amount) as Amount from transaction_table where sequence_table IN (select sequence_table from transaction_table where .....); and i still get the same error, so it seems like the problem is in the creation of that new field, any thoughts?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
wetrivrrat wrote:
as you suppected the string Amount = dataReader.GetString(0);
Have you tried object o = dataReader.GetString(0) to see if you get a value at all ?
wetrivrrat wrote:
is my problem due to the fact that i'm creating a new field and it can't ref the new field?
Shouldn't be, no. I don't really use datareaders, though, so I can't be sure.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Christian Graus wrote:
Shouldn't be, no. I don't really use datareaders, though, so I can't be sure.
what methods do you prefer?
I plugged in your code and still get the same error.....
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
wetrivrrat wrote:
what methods do you prefer?
The project I work on has a program which generates wrapper classes around our stored procedures, which return datasets. It's not a matter of my choice, that's just what I'm used to working with, by default.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Well, if you get any new ideas please let me know, cause this was supposed to be delivered by weeks end...
any thing will be helpful!
thanks for everything though!
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
what about doing it in ASP.net? or vb, As i'm not restricted to C# onthis!!!
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
Did you step through and see if there was an object in the data reader at all, and if it was null ?
VB won't help, it's calling the same framework. Surely you can't change your whole project to ASP.NET ?
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
I will admit no I did not step thru, cause well idon't know how...
and yes, I could convert to asp, as that single query is the core of the project, unless i can get it to run and return the value of the 'sum (amount)' then I could write it in any language i wanted, just so much as it runs!!, the prefered method by the client was web based anyhow, I was just working in C# since it was easier to get things started, as i have tried to stay away from the web based stuff.... so, that all said, ":scratch, scratch" if you don't mind explaining how to step thru or helping me connect to sql(server) thru asp your assistance would be GREATELY apreciated.
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
wetrivrrat wrote:
I will admit no I did not step thru, cause well idon't know how...
Then I'm surprised you got this far. Highlight the line you want to stop on, and hit F9. A black dot will appear next to it. Hit F5, make sure first you're doing a debug build. Then the code will stop execution on this line, and in the bottom left is a window where you can type variable names and see their values. F10 steps through the code.
Converting to ASP.NET is easy if your middle tier is seperate from your GUI code. But it won't solve this problem.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Sorry, never called it stepping thru, just debugging it....self taught(if you want to call it that) and so far (knock on wood) this is the first real problem I've run into, that I couldn't fix by either re-writing or re-evaluating what I've written. sorry to be of a frustration.
I did step-thru or debug it and found that on the 'object o = dataReader.GetString(0);' you had me test, I am getting the following: o <undefined value=""> System.Object
and for the 'string Amount = dataReader.GetString(0);' I am returning a null, but unless i'm not pulling the correct field the data shouldn't be a null, rather a value of 331.000. Unless its not pulling the data from the variables i set with the +location_id+ which if that were the case, it seems that I would have a failure elsewhere....in the query, but it IS pushing the console.writeline values to the query, so.......
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
Can you select the parameter by name instead of index ? That is dataReader.GetString("Amount") ?
Doesn't look like it. What if you replace this:
"SELECT sum (Amount) From Transaction_table WHERE Location_ID = @machineName and Shift_Number = @ShiftNumber"
with this:
string.Format("SELECT sum(Amount) as TotalAmount from Transaction_table where Location_ID = {0} and Shift_Number = {1}", machine_name, shift_number)
so that you don't have to mess with parameters at all, just pass in the SQL. ( and so your result has a name )
Christian Graus - Microsoft MVP - C++
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
