CODEWITHSUNDEEP
c#asp.netsessionparameters
Dieter
Dieter

Reputation: 441

Problems converting session values where control in sql server db

Here is my problem, at my login i fill 2 sessions, 1 with the company name and 1 with the username. Using these 2 sessions i'd like to find the corresponding ID in the database. But as you can guess the data types of the sessions and IDs aren't the same so i'm asuming some converting is needed. So basicly what i'm trying to do is save the IDs corresponding with the text in the sessions into a table.

Here is the code i'm using so far but this gives an error when i try to save the order.

System.Data.SqlClient.SqlCommand myCommand1 = new System.Data.SqlClient.SqlCommand("SELECT tUserId FROM tblUsers WHERE tUserName=@Username", sqlConn);
                myCommand1.Parameters.AddWithValue("@Username", (string)Session["username"]);
                myCommand1.ExecuteNonQuery();
                int user = Convert.ToInt32(myCommand1.ToString());

                System.Data.SqlClient.SqlCommand myCommand2 = new System.Data.SqlClient.SqlCommand("SELECT tCompId FROM tblCompany WHERE tCompName=@company", sqlConn);
                myCommand2.Parameters.AddWithValue("@company", (string)Session["compNameLogin"]);
                myCommand2.ExecuteNonQuery();
                int comp = Convert.ToInt32(myCommand2.ToString());

                System.Data.SqlClient.SqlCommand myCommand3 = new System.Data.SqlClient.SqlCommand("INSERT INTO tblOrder(tBestCompId, tBestUserId ) VALUES('" + comp + "','" + user + "') ", sqlConn);
                myCommand3.ExecuteNonQuery();

System.FormatException: Input string was not in a correct format. at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.Convert.ToInt32(String value)

This is the error i encountered.

If anyone could me get in the right direction or give me hints on what i'm doing wrong, or other ways to do this. Please be my guest to post your suggestions.

Thank you in advance.

Upvotes: 0

Views: 638

Answers (1)

manji
manji

Reputation: 47978

What you want to do is retrieving a single data from the DB. Use ExecuteScalar :

System.Data.SqlClient.SqlCommand myCommand1 = 
         new System.Data.SqlClient.SqlCommand(
              "SELECT tUserId FROM tblUsers WHERE tUserName=@Username", sqlConn);
myCommand1.Parameters.AddWithValue("@Username", (string)Session["username"]);
int user = (int)myCommand1.ExecuteScalar();

ExecuteNonQuery will return the number of rows affected. Converting myCommand2.ToString() to intis definetly not going to work as myCommand2 does not store your result (and we don't know what the result of applying ToString() to it is, which in general is a brief desciption of an object)

2nd solution:

You can remplace your code with the following that requires only one request:

string insertQuery = 
@"INSERT INTO tblOrder(tBestCompId, tBestUserId )
 SELECT U.tUserId, T.tblCompany
   FROM (SELECT tUserId FROM tblUsers WHERE tUserName=@Username) U,
        (SELECT tCompId FROM tblCompany WHERE tCompName=@company) T";


System.Data.SqlClient.SqlCommand myCommand
     = new System.Data.SqlClient.SqlCommand(insertQuery, sqlConn);
myCommand.Parameters.AddWithValue("@Username", (string)Session["username"]);
myCommand.Parameters.AddWithValue("@company", (string)Session["compNameLogin"]);
int affectedRows = myCommand1.ExecuteNonQuery();

// affectedRows will contain the number of inserted rows (which is 1)

Upvotes: 2

Related Questions