Re: asp.net and access database - ASP.NET Discussion Boards

What are common causes of the following exception?

SQLBindParameter not used for all parameters

I've read that if I did not use as many "?" in my insert statement as there are fields in the database, then that exception would be thrown. However, I've made sure that their numbers matched. I'd appreciate it if you would look into this problem. Thanks for your help.

-- modified at 23:37 Monday 18th June, 2007

Get it[^]

Regards,

Satips. Rose | [Rose]

Thanks for replying but none of what I've read helped since I'm using ODBC to connect to my database and it does not support named parameters. It however supports the use of ? marks. The people who had the same problem in the forum that you linked to, were using sql and their solution does not help me.

hi, i need to write a code to add record into the access database in 3 table. which are actor, director and movie. when the user click the submit button it will add the details into the respective table. i have done the movie table. Now i need to do the actor n director table. for my actor aspx there is a dropdown list for user to select if the user select a actor n click the add button the selected item will be display in the listbox, if the user select others then they need to enter the name in the text box and when the user click the add new the name in the textbox will be added into the listbox. And same for the director then the user click the submit button the record in the lsitbox will be added into the access database in the actor n movie table. below is the code that i have done so far. please advice. thanks in advance.

HTML source
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Add.aspx.vb" Inherits="WebApplicationDvd._Default" %>

<title>Untitled Page

<asp:label id="AddNewDvd" runat="server" designtimedragdrop="8" font-bold="True" ="" font-names="Times New Roman" font-size="14pt" width="240px">Add new DVD Record

<asp:label id="Moviename" runat="server" font-size="14pt" width="112px">DVD Title:	<asp:textbox id="txtmovieName" runat="server">
<asp:label id="Actor" runat="server" font-size="14pt" width="112px">Actor:	<asp:dropdownlist id="DropDownList1" runat="server" enabletheming="True" width="132px" datasourceid="SqlDataSource1" datatextfield="Actor_Name" datavaluefield="Actor_Name"> <asp:listitem> <asp:button id="btnAdd" runat="server" text="Add" width="72px"> <asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dvd_rentalConnectionString2 %>" ="" providername="<%$ ConnectionStrings:dvd_rentalConnectionString2.ProviderName %>" selectcommand="SELECT [Actor Name] AS Actor_Name FROM [Actor]"> <asp:textbox id="txtActorname" runat="server"> <asp:button id="btnAddNew" runat="server" text="Add New"> <asp:listbox id="lstbxActor" runat="server" height="94px" width="166px"> <asp:button id="btnDelete" runat="server" text="Delete">
<asp:label id="Director" runat="server" font-size="14pt" width="112px">Director:	<asp:dropdownlist id="DropDownList2" runat="server" width="138px" datasourceid="SqlDataSource2" datatextfield="Director_Name" datavaluefield="Director_Name"> <asp:button id="btnAddD" runat="server" text="Add" width="64px"> <asp:sqldatasource id="SqlDataSource2" runat="server" connectionstring="<%$ ConnectionStrings:dvd_rentalConnectionStringdirector %>" ="" providername="<%$ ConnectionStrings:dvd_rentalConnectionStringdirector.ProviderName %>" selectcommand="SELECT [Director Name] AS Director_Name FROM [Director]"> <asp:textbox id="txtDirector" runat="server"> <asp:button id="btnAddNewD" runat="server" text="Add New"> <asp:listbox id="ListBox2" runat="server" height="102px" width="168px"> <asp:button id="btnDeleteD" runat="server" text="Delete">
<asp:label id="language" runat="server" font-size="14pt" width="112px">Language:	<asp:textbox id="txtlanguage" runat="server">
<asp:label id="Numberofdisc" runat="server" font-size="14pt" width="145px">Number of Disc:	<asp:textbox id="txtNoofDisc" runat="server">
<asp:label id="Studio" runat="server" font-size="14pt" width="112px">Studio:	<asp:textbox id="txtStudio" runat="server">
<asp:label id="ReleaseDate" runat="server" font-size="14pt" width="112px">Release Date:	<asp:textbox id="txtReleaseDate" runat="server">
<asp:label id="Price" runat="server" font-size="14pt" width="112px">Price:	<asp:textbox id="txtPrice" runat="server">
<asp:label id="Quantity" runat="server" font-size="14pt" width="112px">Quantity:	<asp:textbox id="txtQuantity" runat="server">

<asp:button id="btnSubmit" runat="server" text="Submit">

<asp:calendar id="Calendar1" runat="server">

VB code

Partial Public Class _Default
Inherits System.Web.UI.Page
'Public atMydropdownlist As DropDownList

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub

Protected Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim Moviename
Dim Actorname
Dim Director
Dim Language
Dim Numberofdisc
Dim Studio
Dim ReleaseDate
Dim Price
Dim Quantity
Dim sql
'Dim asql

Dim DBconn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("dvd_rental.mdb"))

DBconn.Open()

Moviename = Request.Form("txtmovieName")
Actorname = Request.Form("txtActorname")
Director = Request.Form("txtDirector")
Language = Request.Form("txtLanguage")
Numberofdisc = Request.Form("txtNoofdisc")
Studio = Request.Form("txtStudio")
ReleaseDate = Request.Form("txtReleaseDate")
Price = Request.Form("txtPrice")
Quantity = Request.Form("txtQuantity")

sql = "INSERT INTO Movie(MovieName, MovieLanguage, Numberofdisc, Studio, ReleaseDate, Price, Quantity) "

sql = sql & "VALUES('" & Moviename & "'"
sql = sql & ",'" & Language & "'"
sql = sql & ",'" & Numberofdisc & "'"
sql = sql & ",'" & Studio & "'"
sql = sql & ",'" & ReleaseDate & "'"
sql = sql & ",'" & Price & "'"
sql = sql & ",'" & Quantity & "')"

Dim dbquery As New OleDb.OleDbCommand
dbquery.CommandType = CommandType.Text
dbquery.CommandText = sql
dbquery.Connection = DBconn
dbquery.ExecuteNonQuery()

DBconn.Close()
DBconn = Nothing

Response.Redirect("Webdvd.aspx")
End Sub

Protected Sub Calendar1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged

End Sub

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
'Dim DBconn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("dvd_rental.mdb"))

DropDownList1.DataValueField = "Others"

End Sub

Protected Sub txtActorname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtActorname.TextChanged

End Sub

Protected Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
If DropDownList1.SelectedValue <> "Others" Then
lstbxActor.Text = DropDownList1.SelectedValue

ElseIf DropDownList1.SelectedValue = "Others" Then
txtActorname.Focus()
End If
End Sub
End Class

ling_luv wrote:
Moviename = Request.Form("txtmovieName")

Why do you do your insert in the page load ? What if someone refreshes the page ? Why use the Form and not txtmovieName.Text ?