ASP.NET

ASP.NET RSS

Problem with insert statement

Please support our ASP.NET advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Feb 2009

Posts: 29

Reputation: mem81 is an unknown quantity at this point

Solved Threads: 0

mem81

Offline

Light Poster

Problem with insert statement

Feb 24th, 2009

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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ('@CedantID', '@CompanyType', '@CompanyName', '@TypeOfFile', '@FullFileName','@ReportingPeriod')
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

Last edited by peter_budo; Feb 25th, 2009 at 8:38 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Feb 2009

Posts: 18

Reputation: Bandar.M.A is an unknown quantity at this point

Solved Threads: 1

Bandar.M.A Bandar.M.A is offline

Offline

Newbie Poster

Re: Problem with insert statement

Feb 24th, 2009

Hi ;

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

•

Join Date: Feb 2009

Posts: 29

Reputation: mem81 is an unknown quantity at this point

Solved Threads: 0

mem81

Offline

Light Poster

Re: Problem with insert statement

Feb 24th, 2009

•

Originally Posted by 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:

 Help with Code Tags 
 asp.net Syntax (Toggle Plain Text) 
<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>
 
<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

•

Join Date: Apr 2008

Posts: 45

Reputation: bcasp is an unknown quantity at this point

Solved Threads: 10

bcasp

Offline

Light Poster

Re: Problem with insert statement

Feb 24th, 2009

Get rid of the surrounding ' for the values in your insert statement.

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ('@CedantID', '@CompanyType', '@CompanyName', '@TypeOfFile', '@FullFileName','@ReportingPeriod')
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ('@CedantID', '@CompanyType', '@CompanyName', '@TypeOfFile', '@FullFileName','@ReportingPeriod')

should probably be

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES (@CedantID, @CompanyType, @CompanyName, @TypeOfFile, @FullFileName,@ReportingPeriod)
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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ([@CedantID], [@CompanyType], [@CompanyName], [@TypeOfFile], [@FullFileName],[@ReportingPeriod])
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName,Reportingperiod) VALUES ([@CedantID], [@CompanyType], [@CompanyName], [@TypeOfFile], [@FullFileName],[@ReportingPeriod])

Last edited by peter_budo; Feb 25th, 2009 at 8:39 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Feb 2009

Posts: 18

Reputation: Bandar.M.A is an unknown quantity at this point

Solved Threads: 1

Bandar.M.A Bandar.M.A is offline

Offline

Newbie Poster

Re: Problem with insert statement

Feb 24th, 2009

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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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)
 
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!!!

Last edited by peter_budo; Feb 25th, 2009 at 8:40 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Feb 2009

Posts: 29

Reputation: mem81 is an unknown quantity at this point

Solved Threads: 0

mem81

Offline

Light Poster

Re: Problem with insert statement

Feb 24th, 2009

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 :

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
 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
 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:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName, ReportingPeriod, DateReceived) VALUES ([@CedantID], [@CompanyType], [ @CompanyName], [@TypeOfFile], [@FullFileName], [@ReportingPeriod], [@ReciptDate])
INSERT INTO DataStatus(CedantID, CompanyType, CompanyName, TypeOfFile, FileName, ReportingPeriod, DateReceived) VALUES ([@CedantID], [@CompanyType], [ @CompanyName], [@TypeOfFile], [@FullFileName], [@ReportingPeriod], [@ReciptDate])