C#/MS SQL question...

Darragh29

Hi folks,

I'm using the code below to hopefully select a value from a column called stock_code from a table called vehicle_data, where



string sql3 = "select stock_code FROM vehicle_data WHERE vehicle_make = '@VW'";


sql3 = String.Format(sql3, stock_id);

Label2.Text = string.Format("Stock ID is: {0}", stock_id);


I'm not very clear on how to pass the value to a variable and print it back out to the screen, my database connecton is working fine and there are no compilation errors but when I expect to see a value for the stock code, it doesn't appear... I've just discovered that contrary to how this was done with C, I have to assign a value to a variable so to satisfy the compiler, I just used:

string stock_id = string.Empty;

Up at the top of the file when I declared the string stock...

I think the code in italics is the problem... Any suggestions???

Thanks in advance,

D.

pauldiv

What tool are you using?
Have you actually tested the query string against the vehicle_data table?
And why is the @ character in the query string? This special character is used in SQL Server Stored Procedures so using it forother purposes confuses things.

sql3 = String.Format(sql3, stock_id);

The above line looks strange as it appears you are assigning the sql3 string to itself. Why not do away with the string format method and make it simpler before running the program again.

It looks like there is nothing in the stock_id field because, as you said, is has been declared at the top of the program and initialised as being empty. If so there is nothing to print.

Also you have not shown us the code that talks to the server and processes the returned data.

Darragh29

SqlConnection conny = new SqlConnection("Data Source=mssql2005express.myhosting co.com\\steer;Initial Catalog=darragh;User ID=beans;Password=sprouts");
conny.Open();
SqlCommand cmdb = new SqlCommand(sql3, conny);
cmdb.CommandType = CommandType.Text;
cmdb.ExecuteNonQuery();
conny.Close();

I'm going to declare here, I'm obvioulsy new at this and am using MS Visual Web Developer 2005! I've changed the login and server info above for obvious reasons...

John_Mc

Darragh29 wrote: »

Hi folks,

I'm using the code below to hopefully select a value from a column called stock_code from a table called vehicle_data, where



string sql3 = "select stock_code FROM vehicle_data WHERE vehicle_make = '@VW'";


sql3 = String.Format(sql3, stock_id);

Label2.Text = string.Format("Stock ID is: {0}", stock_id);


I'm not very clear on how to pass the value to a variable and print it back out to the screen, my database connecton is working fine and there are no compilation errors but when I expect to see a value for the stock code, it doesn't appear... I've just discovered that contrary to how this was done with C, I have to assign a value to a variable so to satisfy the compiler, I just used:

string stock_id = string.Empty;

Up at the top of the file when I declared the string stock...

I think the code in italics is the problem... Any suggestions???

Thanks in advance,

D.

The text in bold will give you problems because the string your formatting into needs to contain at least {0} in it to tell the method where to place the text being added.

Changing sql3 to:

string sql3 = "select stock_code FROM vehicle_data WHERE vehicle_make = '@VW' AND StockID={0}";

And then using string.Format(sql3,stockID);

Will place the value of StockID where {0} is. If there's no value, then a null string is added.

If you wanted to add more strings in the format, you'd use {1},{2} and so on.

The connection you've pasted there is an ExecuteNonQuery meaning it's an Update, Insert or Delete and will not return anything.

Have you loaded the data from the DB into a data-structure? If it were loaded into a Datatable you could easily obtain the information and load StockID.

Darragh29

Thanks a mil John, I'll try that...

Darragh29

string mySelectQuery = "SELECT Vehicle_Model FROM Vehicle_Data WHERE Trans_ID='1'";


OleDbConnection myConnection = new OleDbConnection("Provider=SQLOLEDB;

Data Source=mssql2005express.my_makey_up_server.com\\steer;Initial Catalog=my_makey_up_Database;User ID=my_makey_up_User Password=my_makey_up_pssword");

OleDbCommand myCommand = new OleDbCommand(mySelectQuery,myConnection);
myConnection.Open();
OleDbDataReader myReader;
myReader = myCommand.ExecuteReader();
// Always call Read before accessing data.


while (myReader.Read())

{
Console.WriteLine(myReader.GetString(0));
}
// always call Close when done reading.
myReader.Close();
// Close the connection when done with it.
myConnection.Close();


Label2.Text = string.Format("Your Stock ID is: {0}", mySelectQuery);

When I try to run this query, all I get back on the screen is:

Your Stock ID is: "SELECT Vehicle_Model FROM Vehicle_Data WHERE Trans_ID='1'";

Could anyone tell me what is wrong with this code??? I have a very simple database, to get this working, I have a primary key set up as the first column called Trans_ID which increments 1 each time a new record is added. Just to get this working, I've tried the above approach, sending Trans_ID='1' with the query to get back data for Vehicle_Model where Trans_ID='1'.

I was originally using an ExecuteNonQuery to try to do this and it was pointed out that this was not possible. I did this before with PHP and it was a walk in the park!

Any help much appreciated!

Just to ad, I I know my connection is working fine because I use the same connection string to ad data to the database and no problem. This problem is specifically to do with a SELECT query and getting data back with the SQL query...

John_Mc

Darragh29 wrote: »

string mySelectQuery = "SELECT Vehicle_Model FROM Vehicle_Data WHERE Trans_ID='1'";


OleDbConnection myConnection = new OleDbConnection("Provider=SQLOLEDB;

Data Source=mssql2005express.my_makey_up_server.com\\steer;Initial Catalog=my_makey_up_Database;User ID=my_makey_up_User Password=my_makey_up_pssword");

OleDbCommand myCommand = new OleDbCommand(mySelectQuery,myConnection);
myConnection.Open();
OleDbDataReader myReader;
myReader = myCommand.ExecuteReader();
// Always call Read before accessing data.

while (myReader.Read())
{
System.Console.Write(myReader("Vehicle_Model").ToString());
System.Console.Write(" ");
}

Label2.Text = string.Format("Your Stock ID is: {0}", mySelectQuery);

When I try to run this query, all I get back on the screen is:

Your Stock ID is: "SELECT Vehicle_Model FROM Vehicle_Data WHERE Trans_ID='1'";

Could anyone tell me what is wrong with this code??? I have a very simple database, to get this working, I have a primary key set up as the first column called Trans_ID which increments 1 each time a new record is added. Just to get this working, I've tried the above approach, sending Trans_ID='1' with the query to get back data for Vehicle_Model where Trans_ID='1'.

I was originally using an ExecuteNonQuery to try to do this and it was pointed out that this was not possible. I did this before with PHP and it was a walk in the park!

Any help much appreciated!

Just to ad, I I know my connection is working fine because I use the same connection string to ad data to the database and no problem. This problem is specifically to do with a SELECT query and getting data back with the SQL query...

Assuming your SQL query will only return one row:

// Always call Read before accessing data.

int nStockID=0;

while (myReader.Read()) 
                                   {
nStockID=Convert.ToInt32(myReader("Vehicle_Model"))
                                   }

if(nStockID>0)
{
Label2.Text = string.Format("Your Stock ID is: {0}", nStockID.ToString());
}

Your code is correctly outputting the SQL query because you have provided it in the String.Format function.

The code snippet I wrote there was from memory so might need a bit of work. You should also add some logic to check if the DB Reader object returns a DBNull as my code will probably throw an exception if it does.

Lastly, the method of obtaining the the DB Reader value is the way it's done in VB. Not entirely sure about C# but if you continue to have problems with this, try using the [] instead of ().

Darragh29

There's pints for you next week John if I can get this to work before I head out tonight! Here goes!!!

Darragh29

I'm getting an error now for the following line of code

nStockID = Convert.ToInt32(myReader["Vehicle_Model"]);

saying:

"Input string was not in a correct format."

"Make sure your method arguments are in the right format"

The project will compile but when I click the button to run the event, I get the error above.

I honestly can't understand how something so simple can be so complicated with ASP.NET. This is the second or third time I've run into a complete brick wall with a task that was done in 2 seconds with PHP...

:mad::mad::mad:

pauldiv

Your call to the ConvertToIn32 method looks fine so It might be helplful to see the data type used for that field and a data sample.
Could post up the table field names and their data types?

This link at MS provides examples of the above method used to convert strings to integers:

http://msdn.microsoft.com/en-us/library/sf1aw27b.aspx

Examples are in VB and C#.

Evil Phil

You probably need to check for null values and if that passes use TryParse() to see if the value will convert to an int.

Darragh29

Have thrown in the towel with ASP.NET! Going back to PHP, might try to go back at ASP.NET in the future but too much of complicating the simple for the moment with ASP.NET...

Darragh29

pauldiv wrote: »

Your call to the ConvertToIn32 method looks fine so It might be helplful to see the data type used for that field and a data sample.
Could post up the table field names and their data types?

This link at MS provides examples of the above method used to convert strings to integers:

http://msdn.microsoft.com/en-us/library/sf1aw27b.aspx

Examples are in VB and C#.

Out of interest, what is the need for converting data types??? Database field is set up to take a nvarchar(50), I just want to read a word or set of characters from a field in a database, and I've to get into converting datatypes and all sorts of arsing around??? In PHP, I can just read out from a database and use a while loop to step through the data, no problem.

John_Mc

Darragh29 wrote: »

I'm getting an error now for the following line of code

nStockID = Convert.ToInt32(myReader["Vehicle_Model"]);

saying:

"Input string was not in a correct format."

"Make sure your method arguments are in the right format"

The project will compile but when I click the button to run the event, I get the error above.

I honestly can't understand how something so simple can be so complicated with ASP.NET. This is the second or third time I've run into a complete brick wall with a task that was done in 2 seconds with PHP...

:mad::mad::mad:

If the DB field contains a null value, then .net sees this as a DBNull value. This is not just an empty string, it's a datatype.

As I said in my last reply, you would have to check that the DB Value is one which is convertible to an integer value. A DBNull value is not and this is the error you are receiving.

Try wrapping the code which converts the value within the following If condition:

if(!Convert.IsDBNull(myReader["Vehicle_Model"]))
{
nStockID = Convert.ToInt32(myReader["Vehicle_Model"]);
}

I'll leave you implement the the else path here.

Darragh29 wrote: »

Out of interest, what is the need for converting data types??? Database field is set up to take a nvarchar(50), I just want to read a word or set of characters from a field in a database, and I've to get into converting datatypes and all sorts of arsing around??? In PHP, I can just read out from a database and use a while loop to step through the data, no problem.

If you have a value in a database and want to use it in an application, it's logical to represent that value as what it actually is. Stock ID is a number, so let's treat it as a number. Alternatively, you could treat it as a string and this would mean minor differences in your SQL query and your conditional statements.

The answer to your question is that you need to tell .Net what your data is so that it knows the rules, methods & contraints it can apply to it. This is a fundamental of any programming language that I have ever done, and I believe PHP is no different?

E.G To perform mathematical calculations, you need an integer, float/double, percent etc. In a string you can concatenate with, search, format and iterate through one character at a time. These are good examples of why you need to convert to data types.

It's a pretty straight forward concept and you should have no problem doing it this way. Stick with .Net a little longer, it'll click soon enough and you'll appreciate more and more over time

Evil Phil

John_Mc wrote: »

The answer to your question is that you need to tell .Net what your data is so that it knows the rules, methods & contraints it can apply to it. This is a fundamental of any programming language that I have ever done, and I believe PHP is no different?

I was under the impression that php stores everything as strings unless told to do otherwise, kinda like classic ASP, but I could be wrong.

John_Mc

Evil Phil wrote: »

I was under the impression that php stores everything as strings unless told to do otherwise, kinda like classic ASP, but I could be wrong.

Yeah probably so, but that was my point. Unless you explicitly tell PHP that a variable is an int or double, you can't perform any calculations with it.

I was just suggesting that the OP should be familiar with this concept already

Evil Phil

Yeah, it does seem like a strange reason not to use a language.

OP: Can you change the database field to an int?

Evil Phil

Darragh29 wrote: »

Out of interest, what is the need for converting data types??? Database field is set up to take a nvarchar(50), I just want to read a word or set of characters from a field in a database, and I've to get into converting datatypes and all sorts of arsing around??? In PHP, I can just read out from a database and use a while loop to step through the data, no problem.

Actually in this particular situation you don't need to convert. I probably should have read the entire thread before posting again :rolleyes:

Label2.Text = string.Format("Your Stock ID is: {0}", mySelectQuery("Vehicle_Model"));

If you did successfully manage to convert it to an Int you'd have had to convert it back to a string to display it in the label.

John_Mc

I did the same and assumed it was an int in the DB. Sorry!

Evil Phil's solution will work, just be sure to avoid any exceptions by checking that it's not a DBNull value before using the String.Format method!

Darragh29

John_Mc wrote: »

Yeah probably so, but that was my point. Unless you explicitly tell PHP that a variable is an int or double, you can't perform any calculations with it.

I was just suggesting that the OP should be familiar with this concept already

Yeah I can understand the idea of having to identify data as a particular type IF I want to perform mathemathical operations on numerical data types. What I want to do here is just deal with characters... I've trawled the internet for solutions for this and every single one seems to get straight into converting data from one type to another. I know I didn't have anywhere near the same difficulty with picking up PHP, I don't know what it is with .net, I'm really starting to dislike it...

Evil Phil

There is a bigger learning curve I suppose but its well worth the effort. Put it this way, if you spend a day working out how to do this datatype conversion, then you know datatype conversion for .net. It's a personal investment in your own knowledge.

John_Mc

Evil Phil wrote: »

There is a bigger learning curve I suppose but its well worth the effort. Put it this way, if you spend a day working out how to do this datatype conversion, then you know datatype conversion for .net. It's a personal investment in your own knowledge.

Totally agree! Once you get a grasp of the fundamentals you'll begin to like .net's out of the box web controls ajax.

One difference between the two platforms already identified in this thread is that PHP automatically converts a value to string for you, and .net does not.

You are getting problems with the convert because your input to the Convert.ToInt32() method contains alpha & non-numeric characters as well.

You'll rarely get this type of problem when converting to a string because it accepts most characters. One thing that does give an exception is DBNull values so as I pointed out previously, you need to check that it's not before parsing to a string. Instead of using Convert.ToInt32 just put a .ToString() after it.

Stick with it a little longer, as previously pointed out it is time well spent

Darragh29

Before I take my protein pills for another lash at ASP.NET and C#, can someone remind me again as to what the advantages are of picking this up??? I had to get a database solution put in place for a client today and I had it done in an hour with PHP. I think if I had to use ASP.NET I'd be here this time next year with it. I see some of the benefits of learning it, but is there some reason why the simple stuff is overly complicated, what's the story with it???

John_Mc

Darragh29 wrote: »

Before I take my protein pills for another lash at ASP.NET and C#, can someone remind me again as to what the advantages are of picking this up??? I had to get a database solution put in place for a client today and I had it done in an hour with PHP. I think if I had to use ASP.NET I'd be here this time next year with it. I see some of the benefits of learning it, but is there some reason why the simple stuff is overly complicated, what's the story with it???

Simply put, you did it in an hour because you know what your doing with PHP. You are only starting to learn ASP.net and can hardly expect to be at the same level of productivity as PHP.

It's highly likely that the same work could be done faster in .net than you did today because of the extensive amount of user controls and interfaces provided out of the box in .Net. An experienced .Net developer would know which of these controls meet the requirements and could use many of them without writing a any code.

My limited experience of PHP leads me to believe that PHP provides no controls whatsoever, or am I wrong about this?

Darragh29

John_Mc wrote: »

Simply put, you did it in an hour because you know what your doing with PHP. You are only starting to learn ASP.net and can hardly expect to be at the same level of productivity as PHP.

It's highly likely that the same work could be done faster in .net than you did today because of the extensive amount of user controls and interfaces provided out of the box in .Net. An experienced .Net developer would know which of these controls meet the requirements and could use many of them without writing a any code.

My limited experience of PHP leads me to believe that PHP provides no controls whatsoever, or am I wrong about this?

Yeah no contols with PHP but it seems to be easier to implement or something. I see the point with familiarity... I think asp.net is picker or something, php is a bit more rough around the edges...

John_Mc

Darragh29 wrote: »

Yeah no contols with PHP but it seems to be easier to implement or something. I see the point with familiarity... I think asp.net is picker or something, php is a bit more rough around the edges...

I can assure you that it's far simpler and faster to drag'n'drop a gridview control onto a page and configure it with a few clicks, than it is to code the equivalent in PHP

Darragh29

So, one-two weeks later and still trying to get my head around this!

//SELECT Stock_ID FROM Vehicle_Data
string selectSQL;
selectSQL = "SELECT Stock_ID FROM Vehicle_Data ";
selectSQL += "WHERE Vehicle_Make=" + dropdowna + "";

//Connection for SELECT FROM "Vehicle data"
SqlConnection connz = new SqlConnection("Data Source=mssql2005express.makey_up_host\\makey_up;Initial Catalog=makey_up;User ID=makey_up;Password=makey_up");
connz.Open();
SqlCommand cmdy = new SqlCommand(selectSQL, connz);
cmdy.CommandType = CommandType.Text;
cmdy.ExecuteReader();
connz.Close();

Label2.Text = string.Format("Your Stock ID is: {0}", selectSQL.Equals("Stock_ID"));


Now, dropdowna above is just a variable that returns some characters from a drop down list. My connection is working, the code is compiling but when I use the script, I get the following:

Your Stock ID is: False

Instead of the text I am expecting back like:

Your stock ID is: T768762

Or whatever characters are to be found at the Stock_ID field, which is set up on the database as a Varchar(50) field....

Also, someone advised me above to use:

Label2.Text = string.Format("Your Stock ID is: {0}", selectSQL("Vehicle_Model"));

And when I do this, I get a compile error:

CS0118: 'selectSQL' is a 'variable' but is used like a 'method'

When I change this to:

Label2.Text = string.Format("Your Stock ID is: {0}", selectSQL.Equals("Vehicle_Model"));

I can compile the code but I get the result back as:

Your Stock ID is: False

I'm not going to pretend I've enough of a grasp on this yet to be able to expect to deal with these kind of issues, but this is how I learn this stuff, by throwing myself into it and once I start seeing results, I usually pick this stuff up fairly quickly, or that has been the way I've learnt until now but unfortunately I've been a bit bamboozled with this...

John_Mc

Label2.Text = string.Format("Your Stock ID is: {0}", selectSQL.Equals("Vehicle_Model"));

SelectSQL is just a string containing your SQL query. What you're doing there is asking if SelectSQL=Vehicle model and injecting the answer into Label2.Text.

You can access the returned values using a Datareader object. Try declaring this:

SqlDataReader myDataReader;


And then setting it's value using:

myDataReader = cmdy.ExecuteReader();

Then to iterate through the results and access the values:

while (myDataReader.Read())
{
Label2.Text = string.Format("Your Stock ID is: {0}", myDataReader["Vehicle_Model"]);
}

This code is written off the top of my head so you may need to use Intellisense to correct case some things. Check out here for the full example I worked from
.

Another pointer for you is to store the Connection string in the Web.config file. This is best practice as it stores the connection string in a single centralised location so any changes to it are instantly reflected across your application.

Darragh29

Nice one John, I've the first bit of progress to report since I've started this little quest! No pun intended with the word bit! :D:D

When I use the following:

selectSQL += "WHERE Vehicle_Make=" + dropdowna + "";

I get no result...

However when I use:

selectSQL += "WHERE Vehicle_Make='VW'";

Where VW is the data contained within 2/2 the table records, I get back the data 456, which is just sample data I stuck into the Stock_ID field for the second row of the table.

What I'm wondering though, is why I'm not getting back the second piece of data for the Stock_Code data at row 1, which is 123... As you can see from the pic of the database table fields attached, the data I'm getting back is for row two, where the data in the Stock_Code in row WHERE Vehicle_Make='VW' is "456"...

Gently gently catchy monkey! :D:D

John_Mc

Darragh29 wrote: »

Nice one John, I've the first bit of progress to report since I've started this little quest! No pun intended with the word bit! :D:D

When I use the following:

selectSQL += "WHERE Vehicle_Make=" + dropdowna + "";

I get no result...

However when I use:

selectSQL += "WHERE Vehicle_Make='VW'";

Where VW is the data contained within 2/2 the table records, I get back the data 456, which is just sample data I stuck into the Stock_ID field for the second row of the table.

What I'm wondering though, is why I'm not getting back the second piece of data for the Stock_Code data at row 1, which is 123... As you can see from the pic of the database table fields attached, the data I'm getting back is for row two, where the data in the Stock_Code in row WHERE Vehicle_Make='VW' is "456"...

Gently gently catchy monkey! :D:D

For the SQL query, dropdowna is a dropdown list control so you need to specify the property of that control to use. For the Selected item use:

dropdowna.SelectedItem.Value.ToString();

DBReader is a forward moving iterator through the Results returned by your SQL Query.

In your scenario the DBReader is moving to the second (last) record in the results and as that's the last time it was assigned a value, that is what it's using.

To resolve this you can amend the SQL query so that it only returns the correct row. This would require you using the the Primary Key for the record in your SQL.

I would suggest binding dropdowna to the DB values and set the dropdowna Value property to the primary key, and the Text property to whatever it is now.

Then, in your SQL query, use dropdowna.SelectedItem.Value.ToString();

Hope this helps

Darragh29

John_Mc wrote: »

For the SQL query, dropdowna is a dropdown list control so you need to specify the property of that control to use. For the Selected item use:

dropdowna.SelectedItem.Value.ToString();

DBReader is a forward moving iterator through the Results returned by your SQL Query.

In your scenario the DBReader is moving to the second (last) record in the results and as that's the last time it was assigned a value, that is what it's using.

To resolve this you can amend the SQL query so that it only returns the correct row. This would require you using the the Primary Key for the record in your SQL.

I would suggest binding dropdowna to the DB values and set the dropdowna Value property to the primary key, and the Text property to whatever it is now.

Then, in your SQL query, use dropdowna.SelectedItem.Value.ToString();

Hope this helps

But here is my problem...

As part of the code (within the same function), for the dropdown list, I've made the following declarations:

string dropdowna = DropDownList1.SelectedValue;
string dropdownb = DropDownList2.SelectedValue;
string dropdownc = DropDownList3.SelectedValue;

I have a seperate connection that passes the values dropdowna, dropdownb and dropdownc into a different table that records queries that are made through the website, it basically posts data to another different table everytime the three pieces of info above are selected, dropdowna, dropdownb and dropdownc.....

The query that I am working on here, takes the three variables above, dropdowna, dropdownb and dropdownc, and goes off to a separate table and runs a WHERE query to find a row that contains a match with these three variables. Where a row is found, the plan is to return two other variables from this unique row to the screen, namely Stock_Code and Retail_Price...

So I have three local variables that are within scope, dropdowna, dropdownb and dropdownc...

Just to use the a possible PHP solution, can I not have something along the lines of...

SELECT FROM My_Equipment WHERE Vehicle_Make=$Vehicle_Make;

Where $Vehicle_Make is a variable from a drop down list???

Ive just boiled this down to the bare bones to get this to work, so Ive hardcoded the:

selectSQL += "WHERE Vehicle_Make='VW'";

To return just database values where vehicle_Make = VW, and this works for the moment, which lets me know that my SQL connection is sound as is my data reader connection.

What I want to do is have the user define Vehicle_Make through dropdownlista, the Vehicle_Model though dropdownlistb and the Engine_Type through dropdownlistc, and then use a WHERE Vehicle_Type=$dropdowna AND Vehicle_Model=$dropdownb AND Engine_Type=$dropdownc, statement, to get back two fields, called Stock_Code and Retail_Price...........

Apologies for the pseudocode, hope it clears up what I'm trying to do here...