Remove From My Forums

INSERT Date in SQL Datebase using SQLDATASOURCE Control

Question

User448563479 posted

Hi,

I have built a data entry page. In which there re more than 20 Data which are being inserted via text box, dropdownlist etc.

Below is the aspx code snippet of SQLDATASOURCE Control:

 <asp:SqlDataSource ID="SalSQLDS" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [SalDatTbl] WHERE [VIN] = @VIN"
         InsertCommand="INSERT INTO [SalDatTbl] (  [VIN],            
    [DMSINVNUM],
	[DMSINVDT],     
		[TALINVNUM],       
	[TALINVDAT], 
	[CUSTNAME],     
	[MODEL],       
	[VARIANT],       
	 [EXS], 
	 [COLOR],       
	 [INSCOM],      
 	 [INSTYP],     
	 [INSAMPL],      
	 [INSAMAC],    
	  [EXWARRANTY],       
	 [EXWAM],      
	 [ECARE],     
	 [ECAREAM],      
	 [AXSBKAM],      
	 [AXEXAM],      
	  [TALBILAC],      
	   [RICD],      
	   [NDPW],      
	   [FDEALAM])
         
        VALUES(@VIN,            
    @DMSINVNUM,        
	@DMSINVDT,     
		@TALINVNUM,       
	@TALINVDAT,   
	@CUSTNAME,     
	@MODEL,       
	@VARIANT,       
	 @EXS,      
	 @COLOR,       
	 @INSCOM,       
 	 @INSTYP,      
	 @INSAMPL,      
	 @INSAMAC,      
	  @EXWARRANTY,       
	 @EXWAM,      
	 @ECARE,       
	 @ECAREAM,      
	 @AXSBKAM,      
	 @AXEXAM,      
	  @TALBILAC,      
	   @RICD,      
	   @NDPW,      
	   @FDEALAM)" SelectCommand="SELECT * FROM [SalDatTbl]"
         UpdateCommand="UPDATE [SalDatTbl] SET [VIN]=@VIN,
[DMSINVNUM]=@DMSINVNUM,      
    [DMSINVDAT]=@DMSINVDAT,  
[TALINVNUM]=@TALINVNUM,
[TALINVDAT]=@TALINVDAT,   
[CUSTNAME]=@CUSTNAME,     
[MODEL]=@MODEL,     
[VARIANT]=@VARIANT,     
 [EXS]=@EXS,
 [COLOR]=@COLOR,   
 [INSCOM]=@INSCOM,     
          [INSTYP]=@INSTYP,     
 [INSAMPL]=@INSAMPL,
 [INSAMAC]=@INSAMAC,     
  [EXWARRANTY]=@EXWARRANTY,     
 [EXWAM]=@EXWAM,     
 [ECARE]=@ECARE,     
 [ECAREAM]=@ECAREAM,  
 [AXSBKAM]=@AXSBKAM,     
 [AXEXAM]=@AXEXAM,     
  [TALBILAC]=@TALBILAC,     
   [RICD]=@RICD,     
   [NDPW]=@NDPW,     
   [FDEALAM]=@FDEALAM"> 
       
         <DeleteParameters>
            <asp:Parameter Name="VIN" Type="String" />
        </DeleteParameters>

        <InsertParameters>

           <asp:ControlParameter ControlID="DMSInvDtTxtBx" Name="DMSINVDAT" PropertyName="Text" Type="DateTime" />
            <asp:ControlParameter ControlID="TalInvNoTxtBx" Name="DMSINVNUM" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="TalInvDtTxtBx" Name="TALINVDAT" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="CustNameTxtBx" Name="CUSTNAME" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="ModelDropDDL" Name="MODEL" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="VINTxtBx" Name="VIN" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="VariantTxtBx" Name="VARIANT" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="ExSTxtBx" Name="EXS" PropertyName="Text" Type="String" />
                        <asp:ControlParameter ControlID="ColTxtBx" Name="COLOR" PropertyName="Text" Type="String" />
                        <asp:ControlParameter ControlID="InsCmpNameDDL" Name="INSCOM" PropertyName="Text" Type="String" />
                        <asp:ControlParameter ControlID="InsTypTxtBx" Name="INSTYP" PropertyName="Text" Type="String" />
                        <asp:ControlParameter ControlID="InsAmtPLTxtBx" Name="INSAMPL" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="InsAmtActTxtBx" Name="INSAMAC" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="ExWDDL" Name="EXWARRANTY" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="ExWCostTxtBx" Name="EXWAM" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="ECDDL" Name="ECARE" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="ECCostTxtBx" Name="ECAREAM" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="AxsBKAmtTxtBx" Name="AXSBKAM" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="AxsExTxtBx" Name="AXEXAM" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="TalBilTxtBx" Name="TALBILAC" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="RDiscTxtBx" Name="RICD" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="NDPWTxtBx" Name="NDPW" PropertyName="Text" Type="String" />
             <asp:ControlParameter ControlID="FinDealTxtBx" Name="FDEALAM" PropertyName="Text" Type="String" />

        </InsertParameters>

<UpdateParameters>
 <asp:Parameter  Name="DMSINVDAT" Type="String" />
            <asp:Parameter   Name="DMSINVNUM"    Type="String" />
            <asp:Parameter  Name="TALINVDAT"    Type="String" />
            <asp:Parameter  Name="CUSTNAME"    Type="String" />
             <asp:Parameter Name="MODEL"    Type="String" />
            <asp:Parameter Name="VIN"    Type="String" />
            <asp:Parameter Name="VARIANT"    Type="String" />
            <asp:Parameter  Name="EXS"    Type="String" />
            <asp:Parameter Name="COLOR"    Type="String" />
             <asp:Parameter   Name="INSCOM"    Type="String" />
             <asp:Parameter  Name="INSTYP"    Type="String" />
            <asp:Parameter   Name="INSAMPL"    Type="String" />
            <asp:Parameter   Name="INSAMAC"    Type="String" />
            <asp:Parameter   Name="EXWARRANTY"    Type="String" />
            <asp:Parameter   Name="EXWAM"    Type="String" />
             <asp:Parameter  Name="ECARE"    Type="String" />
             <asp:Parameter Name="ECAREAM"    Type="String" />
             <asp:Parameter Name="AXSBKAM"    Type="String" />
             <asp:Parameter Name="AXEXAM"    Type="String" />
             <asp:Parameter Name="TALBILAC"    Type="String" />
            <asp:Parameter  Name="RICD"    Type="String" />
             <asp:Parameter Name="NDPW"    Type="String" />
             <asp:Parameter Name="FDEALAM"    Type="String" />
        </UpdateParameters>


        
        </asp:SqlDataSource>

This is the Date Textbox:

  <asp:TextBox ID="DMSInvDtTxtBx" runat="server"  TextMode="Date"></asp:TextBox>

Below is the c# Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void SalDatSubBtn_Click(object sender, EventArgs e)
    {
        if (Page.IsValid == true)
        {
            DataView dv;

            dv = (DataView)SalSQLDS.Select(DataSourceSelectArguments.Empty);

            bool suflag = false;

            for (int i = 0; i < dv.Table.Rows.Count - 1; i++)
            {

                if (dv.Table.Rows[i]["VIN"].ToString().ToLower() == VINTxtBx.Text.ToLower() | dv.Table.Rows[i]["DMSINVNUM"].ToString().ToLower() == DMSInvNoTxtBx.Text.ToLower())
                {
                    suflag = true;
                    break;
                }


            }
            if (suflag == true)
            {
                Response.Redirect("https://www.yahoo.com");
            }
            else
            {

                SalSQLDS.Insert();
                Response.Redirect("https://www.google.com");

            }
        }
    }

}

Now the Error I am getting is that:

{"Must declare the scalar variable \"@DMSINVDT\"."}

Kindly help!

Monday, March 30, 2020 6:16 PM

Answers

User1535942433 posted

Hi niceapoorv,

Accroding to your description,I suggest you could check whether your code about parameters in Insertcommand, InsertParameters,UpdateParameters are same.

More details,you could refer to below codes:

   <asp:SqlDataSource ID="SalSQLDS" runat="server"
                ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542%>" DeleteCommand="DELETE FROM [SalDatTbl] WHERE [VIN] = @VIN"
                InsertCommand="INSERT INTO [SalDatTbl] (  [VIN],            
    [DMSINVNUM],
	[DMSINVDT],     
		[TALINVNUM],       
	[CUSTNAME],     
	[MODEL])
         
        VALUES(@VIN,            
    @DMSINVNUM,        
	@DMSINVDT,     
		@TALINVNUM,         
	@CUSTNAME,     
	@MODEL)"
                SelectCommand="SELECT * FROM [SalDatTbl]"
                UpdateCommand="UPDATE [SalDatTbl] SET [VIN]=@VIN,
[DMSINVNUM]=@DMSINVNUM,      
    [DMSINVDT]=@DMSINVDT,  
[TALINVNUM]=@TALINVNUM,  
[CUSTNAME]=@CUSTNAME">

                <DeleteParameters>
                    <asp:Parameter Name="VIN" Type="String" />
                </DeleteParameters>
                
                <InsertParameters>

                    <asp:ControlParameter ControlID="DMSInvDtTxtBx" Name="DMSINVDT" PropertyName="Text" Type="DateTime" />
                    <asp:ControlParameter ControlID="TalInvNoTxtBx" Name="DMSINVNUM" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="TalInvDtTxtBx" Name="TALINVNUM" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="CustNameTxtBx" Name="CUSTNAME" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="ModelDropDDL" Name="MODEL" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="VINTxtBx" Name="VIN" PropertyName="Text" Type="String" />

                </InsertParameters>

                <UpdateParameters>
                    <asp:Parameter Name="DMSINVDT" Type="String" />
                    <asp:Parameter Name="DMSINVNUM" Type="String" />
                    <asp:Parameter Name="TALINVNUM" Type="String" />
                    <asp:Parameter Name="CUSTNAME" Type="String" />
                    <asp:Parameter Name="MODEL" Type="String" />
                    <asp:Parameter Name="VIN" Type="String" />
                </UpdateParameters>
            </asp:SqlDataSource>
        </div>

        <asp:TextBox ID="DMSInvDtTxtBx" runat="server" TextMode="Date"></asp:TextBox>
        <asp:TextBox ID="TalInvNoTxtBx" runat="server" TextMode="SingleLine"></asp:TextBox>
        <asp:TextBox ID="TalInvDtTxtBx" runat="server" TextMode="SingleLine"></asp:TextBox>
        <asp:TextBox ID="CustNameTxtBx" runat="server" TextMode="SingleLine"></asp:TextBox>
        <asp:TextBox ID="ModelDropDDL" runat="server" TextMode="SingleLine"></asp:TextBox>
        <asp:TextBox ID="VINTxtBx" runat="server" TextMode="SingleLine"></asp:TextBox>
        <asp:Button ID="SalDatSubBtn" runat="server" Text="Button" OnClick="SalDatSubBtn_Click" />

Code-Behind:

 protected void SalDatSubBtn_Click(object sender, EventArgs e)
        {
            if (Page.IsValid == true)
            {
                DataView dv;

                dv = (DataView)SalSQLDS.Select(DataSourceSelectArguments.Empty);

                bool suflag = false;

                for (int i = 0; i < dv.Table.Rows.Count - 1; i++)
                {

                    if (dv.Table.Rows[i]["VIN"].ToString().ToLower() == VINTxtBx.Text.ToLower())
                    {
                        suflag = true;
                        break;
                    }


                }
                if (suflag == true)
                {
                    Response.Redirect("https://www.yahoo.com");
                }
                else
                {
                    SalSQLDS.Insert();
                    Response.Redirect("https://www.google.com");

                }
            }
        }

Best regards,

Yijing Sun

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Tuesday, March 31, 2020 8:21 AM

Deutsch	English	Español	Français
Italiano	日本語	한국어	Português
Pусский	简体中文	繁體中文

Answered by:

INSERT Date in SQL Datebase using SQLDATASOURCE Control

Question

Answers