I want to auto increment and insert values in a cloumn "RefernceNo". Please HELP!!

Question

0.00/5 (No votes)

See more:

My HTML:-

XML

<table class="abtus">
              <tr>
                  <td class="style2">
                      <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
                  </td>
                  <td class="style1">
                      <asp:FileUpload ID="FileUpload1" runat="server" />
                  </td>
                  <td>
                      <asp:TextBox ID="TextBox2" runat="server" CssClass="txtbx"></asp:TextBox>
                  </td>
              </tr>
              <tr>
                  <td class="style2">
                      <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
                  </td>
                  <td class="style1">
                      <asp:FileUpload ID="FileUpload2" runat="server" />
                  </td>
                  <td>
                      <asp:TextBox ID="TextBox3" runat="server" CssClass="txtbx"></asp:TextBox>
                  </td>
              </tr>
              <tr>
                  <td class="style2">
                      <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>
                  </td>
                  <td class="style1">
                      <asp:FileUpload ID="FileUpload3" runat="server" />
                  </td>
                  <td>
                      <asp:TextBox ID="TextBox4" runat="server" CssClass="txtbx"></asp:TextBox>
                  </td>
              </tr>
              <tr>
                  <td class="style2">
                      <asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>
                  </td>
                  <td class="style1">
                      <asp:FileUpload ID="FileUpload4" runat="server" />
                  </td>
                  <td>
                      <asp:TextBox ID="TextBox5" runat="server" CssClass="txtbx"></asp:TextBox>
                  </td>
              </tr>
              <tr>
                  <td class="style2">
                      <asp:Label ID="Label5" runat="server" Text="Label"></asp:Label>
                  </td>
                  <td class="style1">
                      <asp:FileUpload ID="FileUpload5" runat="server" />
                  </td>
                  <td>
                      <asp:TextBox ID="TextBox6" runat="server" CssClass="txtbx"></asp:TextBox>
                  </td>
              </tr>
              <tr>
                  <td class="style2">
                      <asp:Label ID="Label6" runat="server" Text="Label"></asp:Label>
                  </td>
                  <td class="style1">
                      <asp:FileUpload ID="FileUpload6" runat="server" />
                  </td>
                  <td>
                      <asp:TextBox ID="TextBox7" runat="server" CssClass="txtbx"></asp:TextBox>
                  </td>
              </tr>
          </table>

C# Code:-

C#

protected void Button1_Click(object sender, EventArgs e)
    {
        
        if (FileUpload1.HasFile)
        {
            uploadimage(Label1,FileUpload1,TextBox2);
        }

        if (FileUpload2.HasFile)
        {
            uploadimage(Label2, FileUpload2,TextBox3);
        }

        if (FileUpload3.HasFile)
        {
            uploadimage(Label3, FileUpload3,TextBox4);
        }

        if (FileUpload4.HasFile)
        {
            uploadimage(Label4, FileUpload4,TextBox5);
        }

        if (FileUpload5.HasFile)
        {
            uploadimage(Label5, FileUpload5,TextBox6);
        }

        if (FileUpload6.HasFile)
        {
            uploadimage(Label6, FileUpload6,TextBox7);
        }

        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Pictures Uploaded Successfully')", true);
    }

    public void uploadimage(Label l1, FileUpload f1, TextBox t1)
    {
        string str = @"data source=DEEPAKSHARMA-PC\SQLEXPRESS;initial catalog=new; integrated security=true";
        SqlConnection con = new SqlConnection(str);
        con.Open();
        string s = Server.MapPath("Img/") + f1.FileName;
        f1.SaveAs(s);

Here I want to add a new record with column name as RefrenceNo:-

C#

    SqlDataAdapter da = new SqlDataAdapter("Select count(*)+1 from as d from date1", con);

    string query = "insert into date1 values('"+Label1.Text+"','" + TextBox1.Text + "','" + f1.FileName + "','" + t1.Text + "')";
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.ExecuteNonQuery();
    con.Close();

}

Posted 22-Dec-14 20:17pm

Deepak Kanswal Sharma

Updated 22-Dec-14 23:29pm

v3

Add a Solution

Comments

syed shanu 23-Dec-14 2:44am

Your query seems wrong do you mean you want to insert records to date1 table and RefrenceNo should be incremented value.
here in your code what this mean "Select count(*)+1 from as d from ddate"
but you insert query is on date1 table.and what will be your "RefrenceNo"

Deepak Kanswal Sharma 23-Dec-14 5:59am

Yeah I notice there's the wrong table name. I edited it as date1.
Now I want when I click the submit button, the 'RefrenceNo' column gets the incremented value and rest of the fields get updated from textboxes.

This is my table:-

RefrenceNo------ddate-------------image------detail
1------------2014-11-09-----1698309388_n.jpg--NULL
2------------2014-11-09-----6403019191_n.jpg--NULL
3------------2014-11-15-----11111banner1.jpg--This is
4------------2014-11-15-----banner111112.jpg--xasxa

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2014-12-22T20:51:00

Solution 1

Bad idea to query and increment a number yourself since there is no guarantee of uniqueness, use the SQL server IDENTITY that's what it is there for : http://www.w3schools.com/sql/sql_autoincrement.asp[^]

Posted 22-Dec-14 20:51pm

Mehdi Gholam

Comments

George Jonsson 23-Dec-14 3:07am

Good answer. +5.
I tried to increment a column myself once upon a long time ago and learned the hard way why it was a terrible idea.
(In my defense I didn't have enough knowledge at the time to convince the database guy that he should change the table)

Mehdi Gholam 23-Dec-14 3:09am

We live and learn :) Cheers!

Deepak Kanswal Sharma 23-Dec-14 5:16am

C'mon guys!! Every problem has a solution!! May be not today, I'll get it tomorrow. Thanks for your comments!! :)

Deepak Kanswal Sharma 23-Dec-14 5:26am

Mehdi Sir,
I already saw this link on w3schools, But I'm not making a new table anywhere. Just I'm updating the table. And I want every time I insert the new row the 'RefrenceNo' Column Increase by +1.

This is my table:-

RefrenceNo ddate image detail
1 2014-11-09 1698309388_n.jpg NULL
2 2014-11-09 6403019191_n.jpg NULL
3 2014-11-15 11111banner1.jpg This is
4 2014-11-15 banner111112.jpg xasxa
5 2014-12-16 7152111110_n.jpg Science
6 2014-12-16 images111111.jpg Girl

Mehdi Gholam 23-Dec-14 5:41am

When you use identity on your primary key the this will automatically happen and you don't need do to anything even insert a number.

Deepak Kanswal Sharma 23-Dec-14 6:11am

Ok I tried that also set RefrenceNo as primary key.
And when I submit the button. I get the error:-
"Column name or number of supplied values does not match table definition."

Sid_Joshi · Answer 2 · 2014-12-23T00:34:00

Solution 2

create table date1(RefrenceNo int identity(1,1),ddate datetime,image image,detail varchar(20))
use this query for creation of table

and use following query for inserting data to table
bt dont insert data in RefrenceNo value for this column will be automatically generated
insert into date1 values('" + TextBox1.Text + "','" + f1.FileName + "','" + t1.Text + "')";

Posted 23-Dec-14 0:34am

Sid_Joshi

Comments

Deepak Kanswal Sharma 23-Dec-14 10:14am

I'm not making any new table. Just want to update the existing one. And add the values to column RefrenceNo.
If I can't add it in insert into command, can I process it by getting value into any int and then adding it?

Sid_Joshi 24-Dec-14 0:40am

if you wants to update a existing row then why you wants auto increment number?

Deepak Kanswal Sharma 25-Dec-14 1:22am

Not existing row dear. I want to make a new row into existing table. And want to increase the column value "RefrenceNo" by +1.

Sid_Joshi 25-Dec-14 2:58am

then use identity(1,1)
sql server will automatically increments value by 1 during inseration of new row

Deepak Kanswal Sharma 25-Dec-14 22:14pm

How can I apply that. Please explain. Thanks

Sid_Joshi 26-Dec-14 1:06am

open ojbject explorer->right click on table->Design->select RefrenceNo column->expand identity specification node from column properties tab change is identity to Yes

Deepak Kanswal Sharma 28-Dec-14 10:17am

That's Done MAN!! Thanks!! Thanks A Ton!! ;)

Deepak Kanswal Sharma 28-Dec-14 10:18am

Thanks all for Your Comments!! It's really helpful!