Ad:

ASP.NET Discussion Thread
Marked Solved
Views: 973

Feb 24th, 2009

Problem with insert statement

Expand Post »

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:

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.

Similar Threads

Insert statement problem in sql server 2005 in MS SQL
SQL Insert statement syntax in Visual Basic 4 / 5 / 6
Insert Dataset into SQL Database. in VB.NET
insert statement problem in C#
problem with insert statement in ASP.NET

mem81

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

29 posts
since Feb 2009

Permalink

Feb 24th, 2009

Re: Problem with insert statement

Hi ;

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

Bandar.M.A

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

18 posts
since Feb 2009

Permalink

Feb 24th, 2009

Re: Problem with insert statement

Click to Expand / Collapse Quote 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:

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

mem81

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

29 posts
since Feb 2009

Permalink

Feb 24th, 2009

Re: Problem with insert statement

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

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

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

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.

bcasp

Reputation Points: 23

Solved Threads: 10

Light Poster

Offline

45 posts
since Apr 2008

Permalink

Feb 24th, 2009

Re: Problem with insert statement

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

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.

Bandar.M.A

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

18 posts
since Feb 2009

Permalink

Feb 24th, 2009

Re: Problem with insert statement

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 :

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:

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

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

mem81

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

29 posts
since Feb 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP.NET Forum Timeline: Error while binding to datagrid.

Next Thread in ASP.NET Forum Timeline: Binding an enum with descriptions to a DataGrid

DaniWeb Message

Cancel Changes

User Name
Password