Have something to say? Contribute New Article Reply to this Article
Problem with insert statement
Hello,
I am trying to get the currently selected values from dropdown lists and textboxes from a web page and write them to an access database.
I am using MS Web Developer 2005 Express.
To do that I have created data Source , for the InsertQuery Property of the DataSource I have the following:
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ('@CedantID', '@CompanyType', '@CompanyName', '@TypeOfFile', '@FullFileName','@ReportingPeriod')
Then I have Created button and on it`s onclick event I have nameOfDataSet(qryaddRecods).Insert()
When I click on the button , it does write a row in the table .
The problem is instead of getting the selected values , it returns parameters as described in the insert query.
Instead of selected reporting period (2009-02-28) I get @ReportingPeriod, instead of CedantID (1111) I get @ CedantID in the table.
Could you please tell me if you have an idea where my problem is .
Thanks in advance
Hello,
I am trying to get the currently selected values from dropdown lists and textboxes from a web page and write them to an access database. I am using MS Web Developer 2005 Express. To do that I have created data Source , for the InsertQuery Property of the DataSource I have the following:
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ('@CedantID', '@CompanyType', '@CompanyName', '@TypeOfFile', '@FullFileName','@ReportingPeriod')
Then I have Created button and on it`s onclick event I have
nameOfDataSet(qryaddRecods).Insert()
When I click on the button , it does write a row in the table . The problem is instead of getting the selected values , it returns parameters as described in the insert query. Instead of selected reporting period (2009-02-28) I get @ReportingPeriod, instead of CedantID (1111) I get @ CedantID in the table.
Could you please tell me if you have an idea where my problem is .
Thanks in advance
Hi thanks for reply,
Here is the code behind the page:
<System.Serializable()> Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Public Property SelectedDate() As Date
Get
Return Date.Parse(Me.TextBox1.Text)
End Get
Set(ByVal value As Date)
Me.TextBox1.Text = value.ToShortDateString()
End Set
End Property
Protected Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender
End Sub
Protected Sub Calendar1_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.Disposed
End Sub
Protected Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged
TextBox1.Text = Calendar1.SelectedDate.ToShortDateString
Me.Calendar1.Visible = False
End Sub
Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
If Me.Calendar1.Visible = False Then
Me.Calendar1.Visible = False
Else
Me.Calendar1.Visible = True
End If
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If Calendar1.Visible = False Then
Calendar1.Visible = True
End If
End Sub
Protected Sub DDTypeOfFile_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDTypeOfFile.SelectedIndexChanged
End Sub
Protected Sub ReciptDate_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles ReciptDate.Load
ReciptDate.Text = Date.Now.ToString("d")
End Sub
Protected Sub ReciptDate_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles ReciptDate.PreRender
End Sub
Protected Sub DDCompanyType_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDCompanyType.DataBound
End Sub
Protected Sub DDCompanyType_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDCompanyType.Load
End Sub
Protected Sub DDCompanyType_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDCompanyType.SelectedIndexChanged
End Sub
Protected Sub DDCedantID_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDCedantID.Load
If Not Page.IsPostBack Then
Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\DataStatusRecord1.mdb")
Dim strSQL As String = "SELECT distinct CedantID FROM Recording"
Dim da As New System.Data.OleDb.OleDbDataAdapter(strSQL, conn)
Dim ds As New Data.DataSet
da.Fill(ds, "Recording")
With Me.DDCedantID
.DataSource = ds.Tables("Recording")
.DataTextField = "CedantID"
.DataValueField = "CedantID"
.DataBind()
End With
End If
End Sub
Protected Sub DDCedantID_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDCedantID.SelectedIndexChanged
End Sub
Protected Sub btnAddRecords_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddRecords.Click
'Add the new product!
qryAddRecords.Insert()
End Sub
Protected Sub DDTest_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDTest.SelectedIndexChanged
End Sub
Protected Sub FileName_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles FileName.Init
End Sub
Protected Sub FileName_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles FileName.PreRender
FileName.Text = String.Format("{0:d}-{1}-{2}", Me.Calendar1.SelectedDate, Me.CompShortName.SelectedValue, Me.DDTypeOfFile.SelectedValue)
End Sub
Protected Sub FileName_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles FileName.TextChanged
End Sub
Protected Sub CompShortName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CompShortName.SelectedIndexChanged
End Sub
End Class
and I am using query where I use parameters , that I have put in the insert property of the datasource called qryAddRecords.
Here is the source code as well:
<%@ Page Language="VB" CodeFile="Default.aspx.vb" AutoEventWireup="true" Inherits="_Default" %>
<html>
<head runat="server">
<title>CompanySelection</title>
</head>
<body>
<form id="form1" runat="server">
<asp:panel ID=Controls1 style="Z-INDEX: 181; LEFT: 0px; VISIBILITY:visible; POSITION: absolute; TOP: 0px"
runat="server" BackColor="Transparent" Height="500px" Width="1000px" Design_Time_Lock="True" CssClass="textcenter" HorizontalAlign="Center">
<div style="left:0px;Width:988px;BORDER-RIGHT-STYLE: outset; BORDER-LEFT-STYLE: outset;
POSITION: absolute; TOP: 0px; HEIGHT: 498px; BACKGROUND-COLOR:Ivory; TEXT-ALIGN: center; BORDER-BOTTOM-STYLE: outset" >
<asp:Label ID="Label1" runat="server" Text="Select Cedant ID:" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:20px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent"></asp:Label>
<asp:DropDownList ID="DDCedantID" runat="server" Style="Z-INDEX:100; LEFT:250px;Position:absolute;TOP:20px" Height="20px" Width="125px">
</asp:DropDownList>
<asp:DropDownList ID="DDCompanyType" runat="server" DataSourceID="dsCompanyType"
DataTextField="CompanyType" DataValueField="CompanyType" Style="Z-INDEX:100; LEFT:250px;Position:absolute;TOP:50px" Height="20px" Width="125px" AppendDataBoundItems="True">
<asp:ListItem Selected="True"></asp:ListItem>
</asp:DropDownList>
<asp:AccessDataSource ID="dsCompanyType" runat="server" DataFile="C:\DB\DataStatusRecord1.mdb"
SelectCommand="SELECT DISTINCT [CompanyType] FROM [Recording]
where CedantID= @CedantID
">
<SelectParameters>
<asp:ControlParameter ControlID="DDCedantID" Name="CedantID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:AccessDataSource>
<asp:Label ID="Label2" runat="server" Text="Pick Company Type:" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:50px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent"></asp:Label>
<asp:DropDownList ID="DDCompName" runat="server" DataSourceID="dsCompName" DataTextField="CompanyName"
DataValueField="CompanyName" Style="Z-INDEX:100; LEFT:250px;Position:absolute;TOP:110px" Height="20px" Width="125px">
</asp:DropDownList >
<asp:AccessDataSource ID="dsCompName" runat="server" DataFile="C:\DB\DataStatusRecord1.mdb"
SelectCommand="SELECT DISTINCT [CompanyName] FROM [Recording]">
</asp:AccessDataSource>
<asp:Label ID="Label3" runat="server" Text="Select Reporting Period:" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:140px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server" Style="Z-INDEX:100; LEFT:250px; POSITION: absolute; TOP:140px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="124px" Height="20px" BackColor=Info BorderStyle="Solid" BorderWidth="1px" BorderColor="Black"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="..." Style="Z-INDEX:100; LEFT:390px; POSITION: absolute; TOP:140px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="24px" Height="20px" BackColor=ButtonShadow BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent" ForeColor="#C0C0FF">
</asp:Button>
<asp:Label ID="Label4" runat="server" Text="Select Type Of File:" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:80px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent">
</asp:Label>
<asp:DropDownList ID="DDTypeOfFile" runat="server" DataSourceID="dsFileType" DataTextField="TypeOfFile"
DataValueField="TypeOfFile" Style="Z-INDEX:100; LEFT:250px;Position:absolute;TOP:80px" Height="20px" Width="125px">
</asp:DropDownList >
<asp:AccessDataSource ID="dsFileType" runat="server" DataFile="C:\DB\DataStatusRecord1.mdb"
SelectCommand="SELECT DISTINCT [TypeOfFile] FROM [Recording]">
</asp:AccessDataSource>
<asp:Calendar ID="Calendar1" runat="server" Visible="False" Style="Z-INDEX:100; LEFT:415px;Position:absolute;TOP:140px" Height="50px" Width="195px">
</asp:Calendar>
<asp:Label ID="Label5" runat="server" Text="Select Company Name:" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:110px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent">
</asp:Label>
<asp:Label ID="Label6" runat="server" Text="Receipt Date:" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:170px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent">
</asp:Label>
<asp:TextBox ID="ReciptDate" runat="server" Style="Z-INDEX:100; LEFT:250px; POSITION: absolute; TOP:170px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="124px" Height="20px" BackColor=Info BorderStyle="Solid" BorderWidth="1px" BorderColor="Black">
</asp:TextBox>
<asp:Label ID="RowFileName" runat="server" Text="Row File Name:" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:200px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent"></asp:Label>
<asp:DropDownList ID="DDRawFileName" runat="server"
Style="Z-INDEX:100; LEFT:250px;Position:absolute;TOP:200px" Height="20px" Width="125px" DataSourceID="dsRawFileName" DataTextField="BasicFileName" DataValueField="BasicFileName">
</asp:DropDownList >
<asp:AccessDataSource ID="dsRawFileName" runat="server" DataFile="C:\DB\DataStatusRecord1.mdb"
SelectCommand="SELECT DISTINCT [BasicFileName] FROM [Recording]">
</asp:AccessDataSource>
<asp:Label ID="Label7" runat="server" Text="CompanyShortName:" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:230px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent">
</asp:Label>
<asp:DropDownList ID="CompShortName" runat="server"
Style="Z-INDEX:100; LEFT:250px;Position:absolute;TOP:230px" Height="20px" Width="125px" DataSourceID="dsCompShortName" DataTextField="BasicCompanyName" DataValueField="BasicCompanyName">
</asp:DropDownList >
<asp:AccessDataSource ID="dsCompShortName" runat="server" DataFile="C:\DB\DataStatusRecord1.mdb"
SelectCommand="SELECT DISTINCT [BasicCompanyName] FROM [Recording]">
</asp:AccessDataSource>
<asp:Label ID="Label8" runat="server" Text="File Name is :" Style="Z-INDEX:100; LEFT:5px; POSITION: absolute; TOP:260px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="162px" Height="18px" BackColor=Control BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent">
</asp:Label>
<asp:TextBox ID="FileName" runat="server" Style="Z-INDEX:100; LEFT:250px; POSITION: absolute; TOP:260px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="412px" Height="20px" BackColor=Info BorderStyle="Solid" BorderWidth="1px" BorderColor="Black"></asp:TextBox>
<asp:Button ID="btnAddRecords" runat="server" Text= "Submit" Style="Z-INDEX:100; LEFT:420px; POSITION: absolute; TOP:290px" CssClass="textFont14WhiteBold + padding5Top" Design_Time_Lock="True" Width="72px" Height="20px" BackColor=ButtonShadow BorderStyle="Solid" BorderWidth="1px" BorderColor="Transparent" ForeColor="#C0C0FF">
</asp:Button>
<asp:AccessDataSource ID="qryAddRecords" runat="server" DataFile="C:\DB\DataStatusRecord1.mdb" InsertCommand="INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName, ReportingPeriod, DateReceived) VALUES ('@CedantID', '@CompanyType', '@CompanyName', '@TypeOfFile', '@FullFileName', '@ReportingPeriod', '@ReciptDate')">
<InsertParameters>
<asp:ControlParameter ControlID="DDCedantID" Name="@CedantID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DDCompanyType" Name="@CompanyType" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DDCompName" Name="@CompanyName" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DDTypeOfFile" Name="@TypeOfFile" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="FileName" Name="@FullFileName" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox1" Name="@ReportingPeriod" PropertyName="Text" />
<asp:ControlParameter ControlID="ReciptDate" Name="@ReciptDate" PropertyName="Text" />
</InsertParameters>
</asp:AccessDataSource>
Thanks very much for looking
Regards
Mem81
Get rid of the surrounding ' for the values in your insert statement.
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ('@CedantID', '@CompanyType', '@CompanyName', '@TypeOfFile', '@FullFileName','@ReportingPeriod')
should probably be
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES (@CedantID, @CompanyType, @CompanyName, @TypeOfFile, @FullFileName,@ReportingPeriod)
With Access, I've also had to do something like
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ([@CedantID], [@CompanyType], [@CompanyName], [@TypeOfFile], [@FullFileName],[@ReportingPeriod])whay you don't use the simple way?
define function to execute
sub execute(p,p,p,p,p,p,.....) ' as many as you want!
'then
Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\DataStatusRecord1.mdb")
Dim strSQL As String = "insert into (TABLENAME) (C,C,....) values (p,p,p......p) "
dim cmd as Data.OleDb.OleDbCommand
cmd = new Data.OleDb.OleDbCommand(strSQL, conn)
Dim da As New System.Data.OleDb.OleDbDataAdapter(cmd)
Dim ds As New Data.DataSet
cmd.ExecuteNonQuery()
'da.fill(ds)something like this and don't forget to open and close connection
i hope this well help you
good luck!!!
Great thank you both,
bcasp I had to remove the single quotations around the parameters values and add square brackets as you suggested.
so [@ParameterName] instead of '@ParameterName'
Bandar M.A thank you very much to you toofor your exact reply . It hitted the point . Thanks.
Here is the final code :
Protected Sub btnAddRecords_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddRecords.Click
'on click of a button insert records
qryAddRecords.Insert()
End Sub
Private Sub execute(ByVal CedantID, ByVal CompanyType, ByVal CompanyName, ByVal TypeOfFile, ByVal FullFileName, ByVal ReportingPeriod, ByVal ReciptDate)
'handles getting the currently selected data in controls
Dim conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\DataStatusRecord1.mdb")
Dim strSQL As String = "insert into (DataStatus) (CedantID,CompanyType,CompanyName,TypeOfFile,FileName,ReportingPeriod,DateReceived) values (@CedantID,@CompanyType,@CompanyName,@TypeOfFile,@FullFileName,@ReportingPeriod,@ReciptDate) "
Dim cmd As Data.OleDb.OleDbCommand
conn.Open()
cmd = New Data.OleDb.OleDbCommand(strSQL, conn)
Dim da As New System.Data.OleDb.OleDbDataAdapter(cmd)
Dim qryAddRecords As New Data.DataSet
da.Fill(qryAddRecords)
cmd.ExecuteNonQuery()
conn.Close()
End Sub
and the query I have on the insertquery propery of the data source qryAddRecords is:
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName, ReportingPeriod, DateReceived) VALUES ([@CedantID], [@CompanyType], [ @CompanyName], [@TypeOfFile], [@FullFileName], [@ReportingPeriod], [@ReciptDate])
Although when I try to preview the query execution it throws an error it works perfectly.
Thanks again.
I will mark the thread as Solved
This question has already been solved
You
© 2012 DaniWeb® LLC
