|
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++
|
|
|
|
|
Christian,
We had it all backwards.....
I was using the Getint32 class and while that is fine I am only trying to retrieve one result and since I was creating it as a 'virtual' column it wouldn't pull becasue I apparently was trying to read it in the .net framework data type, but it was returning .net data provider data type. SO this basically means that no matter how i queried it it was never going to convert to the correct data type unless i told it to. plus, when we tried to change the ordnal type from numeric (0,1,2,etc) to alpha ("Amount") it would not read since it only reads ordnals as numeric values. intersting huh? and if it ever helps you out, the datareader provides the fastest level of data retrieval from a DBMS if all you are doing is querying it for reading purposes, so if at work you guys never have to update or insert it may be helpfull to use it.....
who knows, maybe this will all help you or someone else out there one day.....
THANKS FOR YOUR HELP
talk to you later.....but hopefully not! 
have a nice day!
Oh, by the way here's the code that paid the bills! ::::
Console.WriteLine("Current Shift Value on machine [" + Location_ID + "] is $" + dataReader["Value"].ToString());
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
Hi Folks,
I'm trying to access a CSV file as an ODBC to import some data into an Oracle Database. I almost have it.
I have the connect string for Microsoft Text Driver. I have sorted out how to access the CSV file as a table. What I can't figure out how to do is get the Microsoft Text Driver to stop recognizing every field as a VARCHAR(255). This takes a horrible amoung of space and causes compatibility problems with the Oracle table I'm using.
The Microsoft Text Driver will recognize a header on a CSV file with the field names, but I can't figure out how to specify data types with the correct syntax.
Help!!!!!
Matthew Evans
Canada
|
|
|
|
|
With ado (not .net) if i have a RecordSet (basicly an in memory table) can i connect to it as if it where a database and run a select statment against it to return a new recordset? And if it posible would you be so kind as to include a code sniplet.
|
|
|
|
|
I have an application using SQL Server database. After a few insert new data from application into database. That database is seem to be stucked. I can query any function from it any more. I must wait for a while, stop and then restart SQL Server, close (if it's opening) and then open Query Analyzer and change the database processing to the database that my application works with in order to make it works again . And i must exactly follow all the steps above to make it works again.
If i try inserting data from Query Analyzer, there's no thing wrong appears. but right after that if i try to delete that database, i will receive a message that i can't delete that database because it's in use (although i closed my app and query analyzer already). I (again) must stop, and restart SQL Server to delete.
What's my problem? The error is caused by my app or my SQL Server?
Please help me. Thanks!
|
|
|
|
|
I'd check for open transactions..that aren't being committed/rolled back etc..
|
|
|
|
|
I am trying to connect to a database on an AS400 via ODBC and getting some issues with the connection string. I have searched and not found anything that will help.
My connection string is
Private Const RSEL_CONNECTION = "Driver={Client Access ODBC Driver (32- bit)};System=XXX; DBQ=LTL400TNT3;Uid=XXX;Pwd=XXX;"
My calling code is
Dim conn As OdbcConnection
conn = New OdbcConnection(RSEL_Connection)
My error
Message "Format of the initialization string does not conform to specification starting at index 0." String
Any help would be much appreciated. Also, i need to do this via ODBC or OLEDB. I know this is slower, but I cannot use the beta managed database connectors available.
Thanks,
Scott
|
|
|
|
|
Hai,
I want to get the last 10 records from a table containing 100 records.What will be the siplest SQL query ,I can use for retreiving last 10 records,without giving any conditions.I am sure somebody can show my way.
Thank you,
Rahul.
|
|
|
|
|
This is the best I can come up with:
SELECT TOP 10 FROM MyTable ORDER BY ClusteredIndexColumns DESC
Remember that the SELECT statement returns a set, and in a set there is no concept of order, so, in order to maintain this artificial order, to get the first 10 rows chance the DESC to ASC
The ClusteredIndexColumns are the columns that make up your clustered index, this is often the primary key.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hai Colin,
Great.That seems to be the best and simplest way.Thank you very much for your valuable information and for the fantastic help.
Thank you,
Rahul
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
