SQL Search ASP.NET

Question

I am building a mock car rental site. I have to check to see if a certain car is reserved or not. I have a table in my SQL database for each car. Each car's table contains a datetime column. Each day a car is reserved an entry is made in said table. Given the users request I know which table to search. My problem is writing a sqlcommand that searches the columns for a specific date time. If it finds nothing then good let us make a reservation, otherwise I cannot make the reservation. to add something to a table this works for me:

cmd = new SqlCommand("INSERT INTO reservationTable (resNumber,pickUp, dropOff) Values(@resNumber,@pickUp,@dropOff)", con);
cmd.Parameters.Add("@resNumber", SqlDbType.Int).Value = i;
cmd.Parameters.Add("@pickUp", SqlDbType.DateTime).Value = reservation.pickUp;
cmd.Parameters.Add("@dropOff", SqlDbType.DateTime).Value = reservation.ret;
cmd.ExecuteNonQuery();

Anyone suggest a search command like this for my intents and purposes? Say for example I wanted to search reservationTable for a specific resNumber.

Well, first I would probably recommend not creating a table for every car. What happens when you get a new car for the service? What happens when one is sold? Also, would be helpful if you provided some data structures, and possibly some sample data. — Bert
– Bert, Commented Nov 29, 2011 at 2:23
@YoungGuy why not have one table like a log. Then select on where dropOff > GETDATE() and CarID = xxx — Saidur Rahman
– Saidur Rahman, Commented Nov 29, 2011 at 2:31
Yeah, I agree with @BertEvans. I think you're having issues due to a poor database schema design. Also, more code samples will definitely help. — JesseBuesking
– JesseBuesking, Commented Nov 29, 2011 at 2:35
Well I am not too sure on how to design the table most efficiently. I can add or create sample data as I choose, I just have to have 4 classes of vehicle..Compact,Standard, SUV, and MiniVan. All cars are available to start. — YoungGuy
– YoungGuy, Commented Nov 29, 2011 at 2:51
Fully agree. One table per car? You just told everyone on the planet you better work atmcdonalds serve bburgers- this is database rape. Get a book about how sql databases work. — TomTom
– TomTom, Commented Nov 29, 2011 at 5:28

xQbert · Accepted Answer · 2011-11-29 02:37:05Z

1

your where clause will be something like

WHERE Dropoff >= @requestedPickup and Pickup <= @requestedDropoff

This seems strange but test it; should identify any existing records which overlap the requested reservation; thus negating the requested reservation. if no records are returned, then the requested reservation would be valid.

answered Nov 29, 2011 at 2:37

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xQbert Over a year ago

I agree with some of the above comments however, a separate table for each car doesn't scale well;

ozhug · Accepted Answer · 2011-11-29 02:33:35Z

1

cmd = new SqlCommand(
    "Select resNumber,pickUp, dropOff 
     from reservationtable 
     where resnumber=@resNumber"), con); 

cmd.Parameters.Add("@resNumber", SqlDbType.Int).Value = i; 
using (SqlDataReader dr = cmd.ExecuteReader())
{ 
    while (dr.read())
    {
     // do stuff with results.
    }
}

answered Nov 29, 2011 at 2:33

ozhug

1,09314 silver badges23 bronze badges

Collectives™ on Stack Overflow

SQL Search ASP.NET

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related