insert date into SQL server at Visual basic

[AXRabbit]

Hi, i am now doing a Car rental system web project. Well the interface is very similiar to http://www.avis.com.sg/, which i have extra modelname dropdownlist.

Here is my code.Label 7 is just a Label to indicate Booking_ID number. MY Table consist of Booking_ID,StartDate,EndDate,Modelname,branch_ID,c ustomer_ID respectively.

I just couldnt insert the data into my SQL database even though i have insert the datas in all my of the dropdownlist and click submit. The Data is still not inserted with no error encountered. I suspect is the date problem.
Please assit.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
 
Dim strcon As String = "Data Source=BISHAN\MICROSOFTSQL;Integrated Security=True;Pooling=False;Initial Catalog='AbelProject'"
Dim con As SqlConnection = New SqlConnection(strcon)
 
 Dim cmd As New SqlCommand
Dim cmd2 As New SqlCommand
Dim cmd3 As New SqlCommand
 
 con.Open()
 
cmd3.Connection = con
Label5.Text = cmd3.ExecuteScalar()
 
 
If Not Page.IsPostBack Then
FillCarmodelList()
FillbranchList()
FillHourlist()
FillminuteList()
FilldayList()
Fillmonthlist()
 
 
End If
 
Dim i As Integer
 
cmd.CommandText = "select count(*) from Rent_Record "
 
cmd.Connection = con
 
 
i = cmd.ExecuteScalar()
 
Label7.Text = i + 1
'If i <= 0 Then
 
'cmd.CommandText() = "Insert into Rent_Record values"
Dim insertsql As String
 
insertsql = "INTO Rent_Record ('Booking_ID')"
 
insertsql &= "values('" + Label7.Text + "')"
 
'ElseIf i > 0 Then
 
'Dim insertsql As String
'insertsql = "INTO Rent_Record ("
'insertsql &= "Booking_ID)"
'insertsql &= "values('" + Label7.Text + "')"
 
'insertsql &= "values('"
'insertsql &= "i + 1 '")
 
'End If
con.Close()
con.Dispose()
End Sub
 
Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
 
Dim strcon As String = "Data Source=BISHAN\MICROSOFTSQL;Integrated Security=True;Pooling=False;Initial Catalog='AbelProject'"
Dim con As SqlConnection = New SqlConnection(strcon)
 
Dim startdate As DateTime
Dim enddate As DateTime
'Create SQL command object
Dim cmd As New SqlCommand
con = New SqlConnection(strcon)
'connect command to connection
cmd.Connection = con
 
startdate.Hour.Equals(MonthList1.SelectedItem.Text)
'startdate.Hour.Equals(MonthList1.SelectedItem.Text)
'startdate.Day.Equals(DayList1.SelectedItem.Text)
'startdate.Minute.Equals(MinuteList1.SelectedItem.Text)
 
enddate.Hour.Equals(MonthList2.SelectedItem.Text)
enddate.Day.Equals(Daylist2.SelectedItem.Text)
enddate.Minute.Equals(MinuteList2.SelectedItem.Text)
 
 
con.Open()
 
 
' cmd.CommandText() = "Insert into Customer values ('" + _
'Label7.Text(+"','" + _
'startdate.Date + "','" + _
'enddate.Date + "','" + _
'CarModelList.SelectedItem.Text + "','" + _
'BranchList.SelectedItem.Text + "','" + _
'Session("customerID") + "')")
 
cmd.CommandText() = "update rent_record set startDate = '" + startdate.Date + _
"', enddate = '" + enddate.Date + _
"', modelname = '" + CarModelList.SelectedItem.Text + _
"',Branch_ID = '" + BranchList.SelectedItem.Text + _
"',Customer_ID= '" + Session("customerID") + _
"'" + _
" Where Booking_ID = '" + Label7.Text + "'"
 
 
cmd.ExecuteNonQuery()
 
con.Close()
con.Dispose()
Response.Redirect("BookingConfirm.aspx")
End Sub

[Frinavale]

Instead of doing the following:
insertsql = "INTO Rent_Record ('Booking_ID')"
insertsql &= "values('" + Label7.Text + "')"

Why don't you just use "i+1"?


You aren't actually executing the SQL Insert command anywhere in code...could this be your problem?

[AXRabbit]

i suspect either of the 2 reasons or both.

1) theres problem inserting with Booking_ID without inserting other fields under the REnt_REcord Table

2_ The insert of codes from 3 dropdownlist to variable StartDate and EndDate Variables at Line 74- 77 consist of error.

Please Assist.
Thanks Frinavale, i will check it out.

[Frinavale]

I have added a bunch of comments to your code.
You're code is confusing to me and you are going to have to explain things to me before I can continue to help you.

One thing I'd recommend is that you try your SQL queries in something like Query Analyzer to make sure that they work before attempting to use them in code.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
 
    Dim strcon As String = "Data Source=BISHAN\MICROSOFTSQL;Integrated Security=True;Pooling=False;Initial Catalog='AbelProject'"
    Dim con As SqlConnection = New SqlConnection(strcon)
 
    Dim cmd As New SqlCommand
    Dim cmd2 As New SqlCommand
    Dim cmd3 As New SqlCommand
 
    con.Open()
'I really don't understand what you are doing here
'You haven't specified any SQL query/command and yet
'you're calling the ExecuteScalar method to return you
'the first cell in the result set returned by the query ...
'What are you doing here?????
    cmd3.Connection = con
    Label5.Text = cmd3.ExecuteScalar()
 
 
    If Not Page.IsPostBack Then
        FillCarmodelList()
        FillbranchList()
        FillHourlist()
        FillminuteList()
        FilldayList()
        Fillmonthlist()
     End If
 
 
'Now this makes sense: you're supplying the command 
'with an SQL query to execute.  You're retrieving the
'first cell that results from the query ....
'but you're storing that value incremented by one
'in a label instead of using it in any programming logic.
'I think you are doing this in the wrong place...
'you should probably be doing this were you need to use the
'value returned....Please explain.
    Dim i As Integer
    cmd.CommandText = "select count(*) from Rent_Record "
    cmd.Connection = con
     i = cmd.ExecuteScalar()
 
    Label7.Text = i + 1
 
 
 
                    'If i <= 0 Then
                    'cmd.CommandText() = "Insert into Rent_Record values"
 
'Ok now here comes the most confusing part.
'You're declaring a String that contains an SQL query 
'(built on the value you stored in the label...instead of just using i+1...)
'But you never actually Use this query anywhere....
'You're just declaring it and aren't executing it.
'Even if you did execute this, and it worked properly....
'Why are you doing this in the Page Load event? It's going
'to be executed every time the page posts back.
    Dim insertsql As String
     insertsql = "INTO Rent_Record ('Booking_ID')"
     insertsql &= "values('" + Label7.Text + "')"
 
                    'ElseIf i > 0 Then
                    'Dim insertsql As String
                    'insertsql = "INTO Rent_Record ("
                    'insertsql &= "Booking_ID)"
                    'insertsql &= "values('" + Label7.Text + "')"
                    'insertsql &= "values('"
                    'insertsql &= "i + 1 '")
                     'End If
    con.Close()
    con.Dispose()
End Sub
 
Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
 
    Dim strcon As String = "Data Source=BISHAN\MICROSOFTSQL;Integrated Security=True;Pooling=False;Initial Catalog='AbelProject'"
    Dim con As SqlConnection = New SqlConnection(strcon)
 
    Dim startdate As DateTime
    Dim enddate As DateTime
 
    'Create SQL command object
    Dim cmd As New SqlCommand
    con = New SqlConnection(strcon)
 
    'connect command to connection
    cmd.Connection = con
 
'Are you crashing here??
'Is this where you're having problems?
'Have you tried stepping through this and checking if this is set correctly?
 
    startdate.Hour.Equals(MonthList1.SelectedItem.Text)
    'startdate.Hour.Equals(MonthList1.SelectedItem.Text)
    'startdate.Day.Equals(DayList1.SelectedItem.Text)
    'startdate.Minute.Equals(MinuteList1.SelectedItem.Text)
 
    enddate.Hour.Equals(MonthList2.SelectedItem.Text)
    enddate.Day.Equals(Daylist2.SelectedItem.Text)
    enddate.Minute.Equals(MinuteList2.SelectedItem.Text)
 
 
    con.Open()
 
 
            ' cmd.CommandText() = "Insert into Customer values ('" + _
            'Label7.Text(+"','" + _
            'startdate.Date + "','" + _
            'enddate.Date + "','" + _
            'CarModelList.SelectedItem.Text + "','" + _
            'BranchList.SelectedItem.Text + "','" + _
            'Session("customerID") + "')")
 
'I think this is where you want to create a new entry in the Rent_Record table.
'First, grab the next available ID for the record and then insert a new record....
'instead of updating some record that you created during the page post back.
    cmd.CommandText() = "update rent_record set startDate = '" + startdate.Date + _
    "', enddate = '" + enddate.Date + _
    "', modelname = '" + CarModelList.SelectedItem.Text + _
    "',Branch_ID = '" + BranchList.SelectedItem.Text + _
    "',Customer_ID= '" + Session("customerID") + _
    "'" + _
    " Where Booking_ID = '" + Label7.Text + "'"
 
 
    cmd.ExecuteNonQuery()
 
    con.Close()
    con.Dispose()
    Response.Redirect("BookingConfirm.aspx")
End Sub

[AXRabbit]

hey thanks~ i working out the SQL part.

Ok my final question. As u can see i got 2 set of 3Dropdownlists.
StartDate: Minute,Hour, Month
Enddate Minute Hour,Month

How do i get the data from drop downlist and insert to data. my Line 93-100 seems stupid.

Thank u so much

How to get Date