By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

insert date into SQL server at Visual basic

P: 6
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
  2.  
  3. Dim strcon As String = "Data Source=BISHAN\MICROSOFTSQL;Integrated Security=True;Pooling=False;Initial Catalog='AbelProject'"
  4. Dim con As SqlConnection = New SqlConnection(strcon)
  5.  
  6.  Dim cmd As New SqlCommand
  7. Dim cmd2 As New SqlCommand
  8. Dim cmd3 As New SqlCommand
  9.  
  10.  con.Open()
  11.  
  12. cmd3.Connection = con
  13. Label5.Text = cmd3.ExecuteScalar()
  14.  
  15.  
  16. If Not Page.IsPostBack Then
  17. FillCarmodelList()
  18. FillbranchList()
  19. FillHourlist()
  20. FillminuteList()
  21. FilldayList()
  22. Fillmonthlist()
  23.  
  24.  
  25. End If
  26.  
  27. Dim i As Integer
  28.  
  29. cmd.CommandText = "select count(*) from Rent_Record "
  30.  
  31. cmd.Connection = con
  32.  
  33.  
  34. i = cmd.ExecuteScalar()
  35.  
  36. Label7.Text = i + 1
  37. 'If i <= 0 Then
  38.  
  39. 'cmd.CommandText() = "Insert into Rent_Record values"
  40. Dim insertsql As String
  41.  
  42. insertsql = "INTO Rent_Record ('Booking_ID')"
  43.  
  44. insertsql &= "values('" + Label7.Text + "')"
  45.  
  46. 'ElseIf i > 0 Then
  47.  
  48. 'Dim insertsql As String
  49. 'insertsql = "INTO Rent_Record ("
  50. 'insertsql &= "Booking_ID)"
  51. 'insertsql &= "values('" + Label7.Text + "')"
  52.  
  53. 'insertsql &= "values('"
  54. 'insertsql &= "i + 1 '")
  55.  
  56. 'End If
  57. con.Close()
  58. con.Dispose()
  59. End Sub
  60.  
  61. Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
  62.  
  63. Dim strcon As String = "Data Source=BISHAN\MICROSOFTSQL;Integrated Security=True;Pooling=False;Initial Catalog='AbelProject'"
  64. Dim con As SqlConnection = New SqlConnection(strcon)
  65.  
  66. Dim startdate As DateTime
  67. Dim enddate As DateTime
  68. 'Create SQL command object
  69. Dim cmd As New SqlCommand
  70. con = New SqlConnection(strcon)
  71. 'connect command to connection
  72. cmd.Connection = con
  73.  
  74. startdate.Hour.Equals(MonthList1.SelectedItem.Text)
  75. 'startdate.Hour.Equals(MonthList1.SelectedItem.Text)
  76. 'startdate.Day.Equals(DayList1.SelectedItem.Text)
  77. 'startdate.Minute.Equals(MinuteList1.SelectedItem.Text)
  78.  
  79. enddate.Hour.Equals(MonthList2.SelectedItem.Text)
  80. enddate.Day.Equals(Daylist2.SelectedItem.Text)
  81. enddate.Minute.Equals(MinuteList2.SelectedItem.Text)
  82.  
  83.  
  84. con.Open()
  85.  
  86.  
  87. ' cmd.CommandText() = "Insert into Customer values ('" + _
  88. 'Label7.Text(+"','" + _
  89. 'startdate.Date + "','" + _
  90. 'enddate.Date + "','" + _
  91. 'CarModelList.SelectedItem.Text + "','" + _
  92. 'BranchList.SelectedItem.Text + "','" + _
  93. 'Session("customerID") + "')")
  94.  
  95. cmd.CommandText() = "update rent_record set startDate = '" + startdate.Date + _
  96. "', enddate = '" + enddate.Date + _
  97. "', modelname = '" + CarModelList.SelectedItem.Text + _
  98. "',Branch_ID = '" + BranchList.SelectedItem.Text + _
  99. "',Customer_ID= '" + Session("customerID") + _
  100. "'" + _
  101. " Where Booking_ID = '" + Label7.Text + "'"
  102.  
  103.  
  104. cmd.ExecuteNonQuery()
  105.  
  106. con.Close()
  107. con.Dispose()
  108. Response.Redirect("BookingConfirm.aspx")
  109. End Sub
Feb 4 '09 #1
Share this Question
Share on Google+
4 Replies


Frinavale
Expert Mod 5K+
P: 9,731
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?
Feb 4 '09 #2

P: 6
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.
Feb 5 '09 #3

Frinavale
Expert Mod 5K+
P: 9,731
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.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
  2.  
  3.     Dim strcon As String = "Data Source=BISHAN\MICROSOFTSQL;Integrated Security=True;Pooling=False;Initial Catalog='AbelProject'"
  4.     Dim con As SqlConnection = New SqlConnection(strcon)
  5.  
  6.     Dim cmd As New SqlCommand
  7.     Dim cmd2 As New SqlCommand
  8.     Dim cmd3 As New SqlCommand
  9.  
  10.     con.Open()
  11. 'I really don't understand what you are doing here
  12. 'You haven't specified any SQL query/command and yet
  13. 'you're calling the ExecuteScalar method to return you
  14. 'the first cell in the result set returned by the query ...
  15. 'What are you doing here?????
  16.     cmd3.Connection = con
  17.     Label5.Text = cmd3.ExecuteScalar()
  18.  
  19.  
  20.     If Not Page.IsPostBack Then
  21.         FillCarmodelList()
  22.         FillbranchList()
  23.         FillHourlist()
  24.         FillminuteList()
  25.         FilldayList()
  26.         Fillmonthlist()
  27.      End If
  28.  
  29.  
  30. 'Now this makes sense: you're supplying the command 
  31. 'with an SQL query to execute.  You're retrieving the
  32. 'first cell that results from the query ....
  33. 'but you're storing that value incremented by one
  34. 'in a label instead of using it in any programming logic.
  35. 'I think you are doing this in the wrong place...
  36. 'you should probably be doing this were you need to use the
  37. 'value returned....Please explain.
  38.     Dim i As Integer
  39.     cmd.CommandText = "select count(*) from Rent_Record "
  40.     cmd.Connection = con
  41.      i = cmd.ExecuteScalar()
  42.  
  43.     Label7.Text = i + 1
  44.  
  45.  
  46.  
  47.                     'If i <= 0 Then
  48.                     'cmd.CommandText() = "Insert into Rent_Record values"
  49.  
  50. 'Ok now here comes the most confusing part.
  51. 'You're declaring a String that contains an SQL query 
  52. '(built on the value you stored in the label...instead of just using i+1...)
  53. 'But you never actually Use this query anywhere....
  54. 'You're just declaring it and aren't executing it.
  55. 'Even if you did execute this, and it worked properly....
  56. 'Why are you doing this in the Page Load event? It's going
  57. 'to be executed every time the page posts back.
  58.     Dim insertsql As String
  59.      insertsql = "INTO Rent_Record ('Booking_ID')"
  60.      insertsql &= "values('" + Label7.Text + "')"
  61.  
  62.                     'ElseIf i > 0 Then
  63.                     'Dim insertsql As String
  64.                     'insertsql = "INTO Rent_Record ("
  65.                     'insertsql &= "Booking_ID)"
  66.                     'insertsql &= "values('" + Label7.Text + "')"
  67.                     'insertsql &= "values('"
  68.                     'insertsql &= "i + 1 '")
  69.                      'End If
  70.     con.Close()
  71.     con.Dispose()
  72. End Sub
  73.  
  74. Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
  75.  
  76.     Dim strcon As String = "Data Source=BISHAN\MICROSOFTSQL;Integrated Security=True;Pooling=False;Initial Catalog='AbelProject'"
  77.     Dim con As SqlConnection = New SqlConnection(strcon)
  78.  
  79.     Dim startdate As DateTime
  80.     Dim enddate As DateTime
  81.  
  82.     'Create SQL command object
  83.     Dim cmd As New SqlCommand
  84.     con = New SqlConnection(strcon)
  85.  
  86.     'connect command to connection
  87.     cmd.Connection = con
  88.  
  89. 'Are you crashing here??
  90. 'Is this where you're having problems?
  91. 'Have you tried stepping through this and checking if this is set correctly?
  92.  
  93.     startdate.Hour.Equals(MonthList1.SelectedItem.Text)
  94.     'startdate.Hour.Equals(MonthList1.SelectedItem.Text)
  95.     'startdate.Day.Equals(DayList1.SelectedItem.Text)
  96.     'startdate.Minute.Equals(MinuteList1.SelectedItem.Text)
  97.  
  98.     enddate.Hour.Equals(MonthList2.SelectedItem.Text)
  99.     enddate.Day.Equals(Daylist2.SelectedItem.Text)
  100.     enddate.Minute.Equals(MinuteList2.SelectedItem.Text)
  101.  
  102.  
  103.     con.Open()
  104.  
  105.  
  106.             ' cmd.CommandText() = "Insert into Customer values ('" + _
  107.             'Label7.Text(+"','" + _
  108.             'startdate.Date + "','" + _
  109.             'enddate.Date + "','" + _
  110.             'CarModelList.SelectedItem.Text + "','" + _
  111.             'BranchList.SelectedItem.Text + "','" + _
  112.             'Session("customerID") + "')")
  113.  
  114. 'I think this is where you want to create a new entry in the Rent_Record table.
  115. 'First, grab the next available ID for the record and then insert a new record....
  116. 'instead of updating some record that you created during the page post back.
  117.     cmd.CommandText() = "update rent_record set startDate = '" + startdate.Date + _
  118.     "', enddate = '" + enddate.Date + _
  119.     "', modelname = '" + CarModelList.SelectedItem.Text + _
  120.     "',Branch_ID = '" + BranchList.SelectedItem.Text + _
  121.     "',Customer_ID= '" + Session("customerID") + _
  122.     "'" + _
  123.     " Where Booking_ID = '" + Label7.Text + "'"
  124.  
  125.  
  126.     cmd.ExecuteNonQuery()
  127.  
  128.     con.Close()
  129.     con.Dispose()
  130.     Response.Redirect("BookingConfirm.aspx")
  131. End Sub
Feb 5 '09 #4

P: 6
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
Feb 5 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.