I need help with the code below. It's supposed to do a basic database insert, but I keep getting a "Specified cast is not valid. " on the line below in red that says "intLastCustID = CInt(objDR.GetString(0))". Any help would be greatly appreciated.
<%@ Page Language="
vb" Debug="true" %><%@ Import namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb"%>
<html>
<head>
<script runat="server">
Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) ' subroutine to run on page load event
' Declaration section
Dim conClsf As OleDbConnection
Dim rdrContracts As OleDbDataReader
conClsf = New OleDbConnection("Provider=MSDAORA.1;" & _
"User ID=cm485a01;" & _
"PassWord=perfect26;" & _
"Data Source=nova;")
' Open DB connection
conClsf.Open()
' Create new OleDbCommand object with SQL to execute
Dim cmdContractID = New OleDbCommand("SELECT contractID FROM contract", conClsf)
' Create a DataReader and execute the command
rdrContracts = cmdContractID.ExecuteReader
contractList.DataSource = rdrContracts
contractList.DataTextField = "contractID"
contractList.DataValueField = "contractID"
contractList.DataBind()
cmdContractID = New OleDbCommand("SELECT homeID FROM home", conClsf)
rdrContracts = cmdContractID.ExecuteReader
homeList.DataSource = rdrContracts
homeList.DataTextField = "homeID"
homeList.DataValueField = "homeID"
homeList.DataBind()
cmdContractID = New OleDbCommand("SELECT agent_ID FROM agent", conClsf)
rdrContracts = cmdContractID.ExecuteReader
agentList.DataSource = rdrContracts
agentList.DataTextField = "agent_ID"
agentList.DataValueField = "agent_ID"
agentList.DataBind()
cmdContractID = New OleDbCommand("SELECT custID FROM customer", conClsf)
rdrContracts = cmdContractID.ExecuteReader
custList.DataSource = rdrContracts
custList.DataTextField = "custID"
custList.DataValueField = "custID"
custList.DataBind()
' Close all objects
rdrContracts.Close()
cmdContractID.Dispose()
conClsf.Close()
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
' Declaration section
Dim objDBConn As OleDbConnection
Dim objCmd As OleDbCommand
Dim objCmd2 As OleDbCommand
Dim objDR As OleDbDataReader
Dim strSQL As String
Dim intLastCustID As Integer
Dim intNextCustID As Integer
' Retrieve parameters from HTML form
Dim strPrice = Request("price")
Dim strContactList = Request("contractList")
Dim strHomeList = Request("homeList")
Dim strDay = Request("day")
Dim strMonth = Request("month")
Dim strYear = Request("year")
Dim strAgentList = Request("agentList")
Dim strCustList = Request("custList")
Response.Write("Price= " & strPrice & "<br>")
Response.Write("Contact ID= " & strContactList & "<br>")
Response.Write("Home ID= " & strHomeList & "<br>")
Response.Write("Day= " & strDay & "<br>")
Response.Write("Month= " & strMonth & "<br>")
Response.Write("Year= " & strYear & "<br>")
Response.Write("Agent ID= " & strAgentList & "<br>")
Response.Write("Cust ID= " & strCustList & "<br>")
' Open DB connection
objDBConn = New OleDbConnection("Provider=MSDAORA.1;" & _
"User ID=cm485a01;" & _
"PassWord=perfect26;" & _
"Data Source=nova;")
objDBConn.Open()
' Retrieve highest Cust ID so far and determine next Cust ID
strSQL = "SELECT MAX(saleID) FROM sale"
objCmd = New OleDbCommand(strSQL, objDBConn)
objDR = objCmd.ExecuteReader
objDR.Read() ' obtain row from results of query)
intLastCustID = CInt(objDR.GetString(0))
'Response.Write("Last Sale ID= " & CStr(intLastCustID) & "<br>")
'intNextCustID = intLastCustID + 1
'Response.Write("Next Sale ID=" & CStr(intNextCustID) & "<br>")
'objDR.Close()
'' Set up INSERT statement SQL
'strSQL = "INSERT INTO sale(saleid,amount,contractid,homeid,saledate,agen t_id,cust_id)VALUES()" & "('" & intNextCustID & "','" & strPrice & "','" & strContactList & "','" & strHomeList & "','" & strDay & "-" & strMonth & "-" & strYear & "','" & strAgentList & "','" & strCustList & "')"
'Response.Write("strSQL= " & strSQL & "<br>")
'' Execute the SQL to INSERT new customer
'objCmd2 = New OleDbCommand(strSQL, objDBConn)
'objCmd2.ExecuteNonQuery()
'' Post success message
'strSuccess.Text = "<h2>Customer " & strPrice & " " & strCustList & " successfully added to database!</h2>"
'strSuccess.Visible = "True"
' Close all objects
objCmd.Dispose()
'objCmd2.Dispose()
objDBConn.Close()
End Sub
</script>
<title>Oracle Real Estate Sales</title>
</head>
<body>
<h1>Oracle Real Estate Sales</h1>
<asp:label id="strSuccess" runat="server" />
<form action="insert_customer_oracle.aspx" method="post" runat="server">
<p>
Price:
<asp:TextBox ID="price" runat="server" Width="122px"></asp:TextBox></p>
<p>
Contract ID:
<asp:DropDownList ID="contractList" runat="server">
</asp:DropDownList> </p>
<p>
Home ID:
<asp:DropDownList ID="homeList" runat="server">
</asp:DropDownList> </p>
<p>
Date:
<asp:TextBox ID="day" runat="server" Width="28px"></asp:TextBox>
<asp:TextBox ID="month" runat="server" Width="37px"></asp:TextBox>
<asp:TextBox ID="year" runat="server" Width="45px"></asp:TextBox></p>
<p>
Agent:
<asp:DropDownList ID="agentList" runat="server">
</asp:DropDownList> </p>
<p>
Customer:
<asp:DropDownList ID="custList" runat="server">
</asp:DropDownList> </p>
<p>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
</p>
</form>
</body>
</html>
Specified cast is not valid during database insert
