Problem with insert statement

[mem81]

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

[Bandar.M.A]

Hi ;

please sent your code so i can know where is the error
'btw'
are you using sp?

[mem81]

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" >
 
        &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
       <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>
        
         &nbsp; &nbsp; &nbsp;&nbsp;<asp:DropDownList ID="DDCedantID" runat="server"  Style="Z-INDEX:100; LEFT:250px;Position:absolute;TOP:20px" Height="20px" Width="125px">
        </asp:DropDownList>
        &nbsp;&nbsp;&nbsp;&nbsp;<br />
        <br />
        &nbsp;&nbsp;
        <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>
        &nbsp;
        
        <br />
        <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>
      
        
        <br />
         
        <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>
        &nbsp;<br />
        <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>
        &nbsp; &nbsp;
        <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>
        &nbsp; &nbsp;
        <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

[bcasp]

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])

[Bandar.M.A]

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!!!

[mem81]

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