Categories: c#, sql, database, executescalar

Problem with taking data from a table using Execute Scalar (C#)

1 answer

I have two tables, one called DogContract and one called WeeklyBooking. They both share two columns, called ContractLength and DogID.

I am currently programming a function for the user to use the GUI to input a new WeeklyBooking object into the table. I am attempting to retrieve the ContractLength value. I have a textbox where the user inputs the DogID and in theory my code is to take the ContractLength Value from DogContract and then assign it as the ContractLength for the weeklybooking object. I have a function in my WeeklyBookingDataAccess class to do this:

        public int getContractLength(string dogID, WeeklyBooking week)     {         SqlCommand getCLength = new SqlCommand("SELECT ContractLength FROM WeeklyBooking WHERE DogID =" + dogID);         try         {             db.Conn.Open();             week.contractLength = (int)db.Cmd.ExecuteScalar();         }         catch (Exception ex)         {             Console.WriteLine(ex.Message);         }         return week.contractLength;     } 

This method is called in the function I'm using to create the WeeklyBooking object. The problem is, when I check the table data the ContractLength Value is always set to zero. Has anybody any ideas as to fix this problem? -Thanks

All answers to this question, which has the identifier 60788621

The best answer:

You are doing something weird with Connection and SqlCommand. This should work:

public int getContractLength(string dogID, WeeklyBooking week) {     SqlCommand getCLength = new SqlCommand("SELECT ContractLength FROM WeeklyBooking WHERE DogID = @DogId", db.Conn);     try     {         getCLength.Parameters.Add("@DogId", SqlDbType.NVarChar, 64);         getCLength.Parameters["@DogId"].Value = dogID;         db.Conn.Open();         week.contractLength = (int)getCLength.ExecuteScalar();     }     catch (Exception ex)     {         Console.WriteLine(ex.Message);     }     return week.contractLength; } 

You need to associate your Connection and your SqlCommand. You can do that by passing the Connection to the constructor of the SqlCommand, or by asking the Connection to create the command.

Apart from that you should take a look at the using pattern and make sure your Connection and SqlCommand are created and destroyed in the right way. Note also that I'm using a named parameter, it's best practice, so you're not open to SQL-injection.

You can also use the simpler version, where you add the parameter with value:

public int getContractLength(string dogID, WeeklyBooking week) {     SqlCommand getCLength = new SqlCommand("SELECT ContractLength FROM WeeklyBooking WHERE DogID = @DogId", db.Conn);     try     {         getCLength.Parameters.AddWithValue("@DogId", dogID);         db.Conn.Open();         week.contractLength = (int)getCLength.ExecuteScalar();     }     catch (Exception ex)     {         Console.WriteLine(ex.Message);     }     return week.contractLength; } 

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type com.google.firebase.iid.zzav is referenced as an interface from com.google.firebase.messaging.zzd”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data