inserting data to SQL via Storeprocedure(Please help) - ASP.NET Discussion Boards

Hi,
I try to import Excel files to sqlserver problem is that the number and the titles of columns are dynamic.How can i do that?by code.
Thank you

Do you want a new table each time? You could use a select into SQL statement.

What are you going to do with the data? A bunch of random SQL tables with random columns doesn't sound very useful.

thank you T M Gray,
I have this problem for almost a month.
I work on an intranet site management reviews of students.
I want to help the manager module to enter student markes .
the problem is that the subjects of a module are dynamic ,I tried a gridview in edit mode but I failed to create the columns dynamically and insert all data at once in the DB.
Now I have an excel file generated by a dynamic sql query containing students of a module but I do not know how import it to the database because the column names and number change .
the table:
reveiw :
id_student________id_subjects______marks
table subject:

id_subject_______name_subject______id_module

the excel file columns:

id_student_____name_student_____name_subject1____name_subject2....

in the name_subjet1 i have de marks of subject1.
I do not know what to do ,is there another solution?
Thanks.

It looks to me like you need to redo the query that generates the excel file.

The relationships between student, subject, and module aren't completely clear to me.

Hi,
this is the query that generates excel:

DECLARE @MATIERE nvarchar(max)   <br />
SELECT @MATIERE =   <br />
  STUFF(   <br />
  (   <br />
  select distinct ',[' + LIBELLE_MATIERE + ']'  <br />
  from MATIERE where CODE_MODULE =11   <br />
  for xml path('')   <br />
 ),   <br />
 1,1,'')   <br />
  <br />
DECLARE @SQL nvarchar(max)   <br />
SELECT @SQL = N'   <br />
select  <br />
 *   <br />
from (   <br />
  <br />
  SELECT     ETUDIANT.NUM_INSCRIPTION, ETUDIANT.NOM_PRENOM_ETUDIANT_ARABE,EXAMEN.CODE,    <br />
 EXAMEN.NOTE,    <br />
                     MATIERE.LIBELLE_MATIERE   <br />
FROM         ETUDIANT INNER JOIN  <br />
                      EXAMEN ON ETUDIANT.NUM_INSCRIPTION = EXAMEN.NUM_INSCRIPTION INNER JOIN  <br />
                      CAPITALISE_MODULE ON    <br />
                      ETUDIANT.NUM_INSCRIPTION = CAPITALISE_MODULE.NUM_INSCRIPTION INNER JOIN  <br />
                      MATIERE ON EXAMEN.CODE_MATIERE = MATIERE.CODE_MATIERE INNER JOIN  <br />
                      MODULE ON CAPITALISE_MODULE.CODE_MODULE = MODULE.CODE_MODULE    <br />
                      AND MATIERE.CODE_MODULE = MODULE.CODE_MODULE   <br />
WHERE      (CAPITALISE_MODULE.CODE_MODULE = 11) AND    <br />
                      (CAPITALISE_MODULE.CODE_TYPE_RELATION_MODULE = 1)   <br />
) Data   <br />
PIVOT (   <br />
 SUM(NOTE)   <br />
 FOR LIBELLE_MATIERE   <br />
 IN (   <br />
  ' + @MATIERE + '  <br />
 )   <br />
) PivotTable   <br />
'   <br />
  <br />
exec sp_executesql @SQL  <br />

there is a relationship"review" between the student and subject
and a relationship between subject and module(module consists of several materials)
Thanks.

Hy,
I have a reuirement in which i have to send a mail.The mail would have two parts.Confirm and Deny.
I have to pass 2 links to confirm and deny which the user can not see. so that when he click on confirm,a link should open directly.
And the link should not be shown to the user.

Thanks nad Regards,
raghvendra Panda

I don't believe you will be able to hide the link. If you are sending a HTML formatted email, the user could always do a "view source" and see the link.

Explain in more detail what you are trying to achieve and maybe we can give you a different approach.

For example, you can have a link that passes a parameter to a webpage and that parameter represents either confirm or deny. The value of the parameter would be some hash algorithm of the email and a datetimestamp, so that the link would expire after a number of days.

A table on the server would be something like:

EmailTo, LinkCreationDateTime, HashValueConfirm, HashValueDeny, ResponseDateTime, ResponseValue (Deny or Confirm)

Here is how it would work:
When you send the email, create a record in this table with the EmailTo, the current timestamp, a has value which represents Confirm and a Hashvalue which represents Deny.

When the user receives the email, he can click on either the Confirm or Deny link, which will pass the appropriate hash value.

When the link is processed at the server, it will first check to see if there is a value in the ResponseValue for this user, if there is already a value, then ignore this new link response. If there is no value, check the value of the hash against the HashValueConfirm and HashValueDeny and set the appropriate ResponseValue.

Hope this gives you something to think about.

Cool | :cool:

Hello every1.

I am trying to improve my skill in ASP.net with C# code. I use MVC in this sample. I create a database and trying to insert data by using Storedprocedure. I created an SP in SQL and wana use it. the problem is I don't know how to use it. I write some code but I really don't know what is the next step. Could you please help me??

it is the first class:item

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace MvcApplication1.Models.Repository.DTO
{
public class Item
{
//definition of class's fileds
public int _id;
public int _code;
public string _description;
public string _creator;
public DateTime _created;

//definition of properties
public int Id
{
set
{
_id = value;
if (value < 0)
throw new ArgumentException(string.Format("Id should be greater than 0 !"));
}
get { return _id; }
}
public int Code
{
set { _code = value; }
get { return _code; }
}
public string Description
{
set { _description = value; }
get { return _description; }
}
public string Creator
{
set { _creator = value; }
get { return _creator; }
}
public DateTime Created
{
set { _created = value; }
get { return _created; }
}

//definiton of constructor
public Item(int id, int code, string description, string creator, DateTime created)
{
this._id = id;
this._code = code;
this._description = description;
this._creator = creator;
this._created = created;
}
}
}

the next class is as below

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using MvcApplication1.Models.Repository.DTO;

namespace MvcApplication1.Repository
{
public class ItemRepository
{
//definition of fields
private const string datasource =
"Data Source= Dell\\SQLEXPRESS;Initial Catalog=DB_Furniture.mdf;Integrated Security=True;User Instance=True";

private SqlConnection _connection;
private SqlDataAdapter _dataAdapter;
private DataSet _dataset;
private SqlCommand _sqlcommand;

//definition of constructor
public ItemRepository()
{

// instantiate the connection
_connection = new SqlConnection(datasource);

// instantiate a new DataSet
_dataset = new DataSet();
_dataAdapter = new SqlDataAdapter(_sqlcommand);

//definition of SQl SP parameters
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.Parameters.Add("@Code", SqlDbType.NVarChar);
_sqlcommand.Parameters.Add("@Description", SqlDbType.NVarChar);
_sqlcommand.Parameters.Add("@Created", SqlDbType.NVarChar);
_sqlcommand.Parameters.Add("@Creator", SqlDbType.NVarChar);

what is next?????????????????????????????????????????????????????????????
how shold i call SP ?????

}
}
}

First: Might want to tidy that up a bit, you're not quite using code blocks right.

Second: You're almost there, the first class doesn't come into it. You've defined the connection object, the command object so just set the command's ".CommandText" to be your stored procedure name, ".Connection" to be your connection and then open the connection and execture the command using .ExecuteNonQuery, .ExecuteReader, .ExecuteScalar, etc. depending on what return, if any, you want.

thx for reply,
Could you please advice me how can I make it more tidy.

the next question is how assign the value of textboxes to them. I mean how pass the parameter to SP?

Could you please give me an example.
thx in advance

Well putting only the code relevant into "pre" tags would be a help.

Anyway, you've added the parameters a bit wrong, you've supplied the name and the type not the value which would be your textboxes.

I'm not going to give direct code snippets as you should be able to work from the words I'm giving you, otherwise the next time you have to do this again, it won't have registered and you wont understand what you did previously.

hopefully I recken i write right code

public ItemRepository()
{

// instantiate the connection
_connection = new SqlConnection(datasource);

}

//definition of insert method
public void Insert(string code, string description, DateTime created, string creator)
{
//definition of SQl SP parameters
_sqlcommand = new SqlCommand("Ins_item",_connection);
_sqlcommand.CommandType = CommandType.StoredProcedure;
_sqlcommand.Parameters.AddWithValue("@Code", code);
_sqlcommand.Parameters.AddWithValue("@Description", description);
_sqlcommand.Parameters.AddWithValue("@Created", created);
_sqlcommand.Parameters.AddWithValue("@Creator", creator);
_datareader = _sqlcommand.ExecuteReader();

}
}

I did the above code in MODEL folder of MVC project. 
would you mind telling me what I have to write in the controler folder

Your code goes in the pre tags, not your message e.g.

public ItemRepository()
{

// instantiate the connection
_connection = new SqlConnection(datasource);

}

Anyway, you're closer there but your insert method's a little off. It's a void, meaning it's not returning anything, therefore you don't need _datareader, your command should be running .ExecuteNOnQuery rather than ExecuteReader.

When you say controler folder, could you elaborate more please?

Hi, I really appreciate for you help.
I fixed up the code as you said.

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace MvcApplication1.Models.Repository.DTO
{
    public class Item
    {
        //definition of class's fileds
        public int _id;
        public int _code;
        public string _description;
        public string _creator;
        public DateTime _created;


        //definition of properties
        public int Id
        {
            set 
            {
                _id = value;
                if (value &amp;amp;lt; 0)
                    throw new ArgumentException(string.Format("Id should be greater than 0 !"));
            }
            get { return _id; }
        }
        public int Code
        {
            set { _code = value; }
            get { return _code; }
        }
        public string Description
        {
            set { _description = value; }
            get { return _description; }
        }
        public string Creator
        {
            set { _creator = value; }
            get { return _creator; }
        }
        public DateTime Created
        {
            set { _created = value; }
            get { return _created; }
        }

        //definiton of constructor
        public Item(int id, int code, string description, string creator, DateTime created)
        {
            this._id = id;
            this._code = code;
            this._description = description;
            this._creator = creator;
            this._created = created;
        }
    }
}

then in Model part in another class I write:

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using MvcApplication1.Models.Repository.DTO;

namespace MvcApplication1.Repository
{
    public class ItemRepository
    {
        //definition of fields
        private const string datasource =
            "Data Source= Dell\\SQLEXPRESS;Initial Catalog=DB_Furniture.mdf;Integrated Security=True;User Instance=True";

        private SqlConnection _connection;
        private SqlCommand _sqlcommand;
        private SqlDataReader _datareader;
        //definition of constructor
        public ItemRepository()
        {           
            // instantiate the connection
            _connection = new SqlConnection(datasource);     
        }
        //definition of insert method
        public void Insert(string code, string description, DateTime created, string creator)
        {
            //definition of SQl SP parameters
            _sqlcommand = new SqlCommand("Ins_item",_connection);
            _sqlcommand.CommandType = CommandType.StoredProcedure;
            _sqlcommand.Parameters.AddWithValue("@Code", code);
            _sqlcommand.Parameters.AddWithValue("@Description", description);
            _sqlcommand.Parameters.AddWithValue("@Created", created);
            _sqlcommand.Parameters.AddWithValue("@Creator", creator);
            _datareader = _sqlcommand.ExecuteNOnQuery();

        }
    }
}

the problem is here. I don't know what should I write in Controller part.I craete a controller class.

ing System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using MvcApplication1.Models.Repository.DTO;
using MvcApplication1.Models.Repository;
using MvcApplication1.Repository;
namespace MvcApplication1.Controllers
{
    public class FurnitureController : Controller
    {
        //
        // GET: /Furniture/

        public ActionResult Index()
        {
            return View();
        }

        //Get: /Furniture/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: /Furniture/Create
        //  [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Craete(Item item)
        {
            try
            {
                ItemRepository ItemRep = new ItemRepository();
??????????????????????????????????????????????????????????????????????????????????
?
 ??
            }
            catch
            {
            }
        }

    } 
}

PLease heap me. its a week. I went through many samples and article on the internet.

er... look at your code, it's all ok, you just need to call it in your controller class. You've already instantiated an object for your data access class so just...call the function off of it.

To put it another way, how do you use methods and properties off of a class? I really can't make it any simpler without writing the entire thing for you.

dude, the problem is I don't know how to call it. do u have any sample, please?

Seriously, you're going to kick yourself for this when you realize what I meant. Ok, you've got it here:

Member 4674360 wrote:
public ActionResult Craete(Item item)
{
try
{
ItemRepository ItemRep = new ItemRepository();
??????????????????????????????????????????????????????????????????????????????????
?
??
}
catch
{
}
}

In your actual code, you've created a method called "insert" off of ItemRepository .

Ergo you just need to call it.

As an example:

Class DataAccessorX
{
    //blah blah blah constructors, etc.
    public void DeployFrickingLazers(int A, int B, int C)
    {
      ...
    }
}

Class MazingerZ
{
     //blah blah blah constructors, etc.
     public void WeNeedKillingPowerNow()
     {
        DataAccessorX oldSkoolAnime = new DataAccessorX();
        oldSkoolAnime.DeployFrickingLazers(ArmOnePilotID, ArmTwoPilotID, ArmThreePilotID);
     }
}

Hi All,

I have a details view which contains a datagrid. They are both populating fine but now I want to edit the rows in the datagrid.

I'm setting up the datagrid like so:

protected override void OnInit(EventArgs e)
        {
            InitializeComponent();
            base.OnInit(e);
        }

        private void InitializeComponent()
        {
            DataGrid productCulturesDataGrid = ((DataGrid)ProductDetailsView.FindControl("ProductsCultureDataGrid"));

            productCulturesDataGrid.EditCommand += new DataGridCommandEventHandler(this.ProductsCultureDataGrid_EditCommand);
            this.Load += new System.EventHandler(this.Page_Load);
        }

And then in the page load method i'm setting the datasource like so:

//GetListOfProductCultures
                        List<ProductsCultureInfo> productCultures = (List<ProductsCultureInfo>)ListOfProductCultures();

 DataGrid productCulturesDataGrid = ((DataGrid)ProductDetailsView.FindControl("ProductsCultureDataGrid"));
                        productCulturesDataGrid.DataSource = productCultures;
                        productCulturesDataGrid.DataBind();

The EditCommand method looks like this but is never triggered:

private void ProductsCultureDataGrid_EditCommand(object sender, DataGridCommandEventArgs e)
{
    DataGrid ProductsCultureDataGrod = (DataGrid)ProductDetailsView.FindControl("ProductsCultureDataGrid");
    ProductsCultureDataGrod.EditItemIndex = e.Item.ItemIndex;
}

Any ideas what i'm doing wrong? Also not sure if this has anything to do with it but this is a dotnetnuke module.

Thanks
Phil

P.S. here is the ascx page encase that helps

<asp:DetailsView ID="ProductDetailsView" runat="server" Height="50px" Width="125px" EnableModelValidation="True" AutoGenerateRows="False">
    <Fields>
        <asp:BoundField DataField="ProductID" HeaderText="Product ID" Visible="False" />
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:Label ID="NameLabel" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Description">
            <ItemTemplate>
                <asp:Label ID="DescriptionLabel" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Thumbnail">
            <ItemTemplate>
                <asp:TextBox ID="ThumbnailTextBox" runat="server" Text='<%# Bind("ThumbURL") %>' />
                <asp:Image ID="ThumbnailImage" runat="server" ImageUrl='<%# Bind("ThumbURL") %>' />
                <asp:LinkButton ID="cmdUpdateThumbnail" runat="server" BorderStyle="None" Text="Test URL" OnClick="cmdUpdateThumbnail_Click" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Ext Product ID">
            <ItemTemplate>
                <asp:TextBox ID="ExtProductIDTextBox" runat="server" Text='<%# Bind("ExtProductID") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Builder Type">
            <ItemTemplate>
                <asp:DropDownList ID="BuilderTypesDropDownList" runat="server">
                    <asp:ListItem>Calendars</asp:ListItem>
                    <asp:ListItem>Cards and Stationery</asp:ListItem>
                    <asp:ListItem>Gifts</asp:ListItem>
                    <asp:ListItem>Photobooks</asp:ListItem>
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Active">
            <ItemTemplate>
                <asp:CheckBox ID="ActiveCheckBox" runat="server" Checked='<%# Bind("Active")%>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Culture Variations">
            <ItemTemplate>
                <asp:DataGrid ID="ProductsCultureDataGrid" runat="server" AutoGenerateColumns="false">
                    <Columns>
                        <asp:BoundColumn DataField="ItemID" HeaderText="ProductCultureID" Visible="False">
                        </asp:BoundColumn>
                        <asp:BoundColumn DataField="CultureCode" HeaderText="Culture Code"></asp:BoundColumn>
                        <asp:BoundColumn DataField="Name" HeaderText="Name"></asp:BoundColumn>
                        <asp:BoundColumn DataField="Description" HeaderText="Description"></asp:BoundColumn>
                        <asp:BoundColumn DataField="Currency" HeaderText="Currency"></asp:BoundColumn>
                        <asp:BoundColumn DataField="CurrencySymbol" HeaderText="Currency Symbol"></asp:BoundColumn>
                        <asp:BoundColumn DataField="Price" HeaderText="Price"></asp:BoundColumn>
                        <asp:TemplateColumn HeaderText="Symbol At Front?">
                            <ItemTemplate>
                                <asp:CheckBox ID="SymbolAtFront" runat="server" Checked='<%# Bind("SymbolAtFront") %>' />
                            </ItemTemplate>
                        </asp:TemplateColumn>
                        <asp:EditCommandColumn EditText="Edit" ButtonType="PushButton"></asp:EditCommandColumn>
                    </Columns>
                </asp:DataGrid>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button ID="AddNewCultureButton" runat="server" Text="Add Culture Variation" OnClick="AddNewCultureButton_Click" />
            </ItemTemplate>
        </asp:TemplateField>
    </Fields>
</asp:DetailsView>

Still trying to solve this, anyone got any ideas at all?

Hi All,

I have a scenario. I have two javascript files attached in my aspx page. I have a function which is present in both the files. I want to call the method from first file only.

Is there any way of doing this other then changing the order of the attached files

Regards

Overload the methods with different number of parameters.

It's not good to have two methods of same name and same signature. Either overload them or change the name.

Change the name of the other function name to different.

Castle Rider

My: Website | Yahoo Group | Blog Spot

You can use namespaces in JavaScript, although it is unconventional and a better approach would be, as the others have suggested, rename the method(s)

I know the language. I've read a book. - _Madmatt

When i am setting the Readonly property of a textbox to true and then, changing the textbox's value,
The changed value doesn't get populated to the server on postback.

Is there any alternative for this.

I achieved this by setting the txtName.Attributes.Add("readonly", "readonly");

Need your comment if there is any better method.

Anurag Gandhi.
http://www.gandhisoft.com
Life is a computer program and every one is the programmer of his own life.

I'm not sure what you're trying to achieve here?

Scenario 1: You want to be able to alter the text so that it gets posted back to the server but in which case...why are you trying to make it read only? Read only means just that, you're not meant to change it.

Scenario 2: You want the text to be unchangeable. Use the .enabled flag to false on the control.