how can i change a column attribute using alter table in sql server 2005. i want to add an identity property to my column userid , which deosn't have an identity property defined when created.
i tried this.
alter table userlookup alter column userid int identity(1,1) not null
i got this error:
Incorrect syntax near the keyword 'identity'.
In design mode of table, when i try to add identity, it is disabled.
These are disabled.
Precision
scale
identity
identity seed
identity increment
is rowguid
why identity is distabled in a table?.
So how can i add identity to a exsiting table?.
I have a table that I need to add an identity field to. I created a field in the table as an INT and added values to all of the existing records. When I try to change it to an IDENTITY field I continually get an error saying 'Invalid cursor state' . The help function tells me that this is caused by not having enough space in the transaction log but I don't understand this b/c the trans log is configured to expand as needed. Anyone know how I can do this?
Thanks,
Tony
Hi Friends,
I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?
Thanks in Advance
Rao Aregaddan writes "Hi All,
Here is my Query.....
While executing the following code,it is showing some warnings.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
Drop procedure [DBO].[Usp_Extract_OLAP_Data_1]
GO
Create Procedure [DBO].[Usp_Extract_OLAP_Data_1] as
begin
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID('[DBO].[processed_olap_data_1]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[processed_olap_data_1]
select * into dbo.processed_olap_data_1 from pubs.dbo.processed_olap_data_1
EXEC ('ALTER TABLE dbo.processed_olap_data_1 ADD generatedid INT IDENTITY')
select * from processed_olap_data_1 order by generatedid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO
Out Put is as follows
----------------------
(141 row(s) affected)
(141 row(s) affected)
Warning: The table 'processed_olap_data_1' has been created but its maximum row size (3873071) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
(141 row(s) affected)
Please guide me on this issue that why iam getting this warning even i set the ansi null and ansi warnings off.
one more thing is that is there any other way to add an
identity column with out using alter statement.and is there any problem with front end guys if it shows like this warnings..."
Hi,In my stored procedure I'm doing a SELECT onINFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id onthis table, so I was wondering if it was possible to add it dynamicallyin my SELECT, so that I would assign a unique id to each recordreturned by my SELECT?Thanks for your support.
View 3 Replies View RelatedI've got a table with 36+ million rows. I've been asked to modify thetable and add in an identity column. The code I used caused SQL tolock up and it maxed out the log files. :)The code I used is:Begin TransactionAlter Table ODS_DAILY_SALES_POSADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)CommitIs there a way to break up the code? Maybe only do a few millionrecords at a time? Or is there a way to do this without lockinganything up?Thanks,Jennifer
View 2 Replies View RelatedI'd created a dataform from a table stored in a SQL database, after that I changed the primary key field to be an identity, refreshed the dataset but when I execute the program, it raises an exception when I try to save the record after adding a new one asking me for the value of this field. What can I do to be sure it will be created automatically?
View 4 Replies View RelatedI need to add a column that has the datatype of integer with a seed and identity increment to a table that already exists and has data in it - and can't all NULLs. I have heard that this not possible if the table already exists.
I have downloaded the demo's of SQL Programmer and a couple of Embarcadero programs to see if that would help, but have had no succes to date.
Thanks for any help,
Alan
I'm using VS2008 (as shipped with SQL CE 3.5 as shipped).
I want to write a database app and started with a quite simple example:
One table "Master" has 2 columns: int MasterID, int Value (where the MasterID is the primary key and is an identity column.
For the GUI I used the typed dataset components (based on the namespace: System.Data.SqlServerCe) which were automatically generated by VS2008. I just dropped the table from the datasources view to a form and got a DataGridview, a Navigator and some DB components, quite fine so far.
When i run the app i can add new rows and the identity column is filled with -1, -2 ... as expected. When I hit the save button, the rows are saved to DB, but the values of the identity columns are not updated (the still have -1, -2..)
When I use the exact same example with SQL Express evrything works fine: the identity Column's values are updated automatically and have +1, +2, ...
Sure there is a statement "SELECT @@IDENTITY"... but I'm just using automated code so far, and i expect that code to work (automatically). Next problem: if i want to insert that "Select @@identity" stuff (I don't want to do that, but perhaps I have to?) where can I do this, I did not found a OnInsertedRow-Event?
This problem is part of a bigger one: I want to use Master/Detail, but inserting the Details fails because the inserting of the master rows didn't return the correct Indentity values, so the detail rows cannot be inserted (the foreign keys -1, -2... will raise an error).
thanks in advance, Bernd.
I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.
thx
Kat
I have a table of raw data where each column can be null. The thought was to create an identity key (1,1) and set as primary for each row. (name/ address / zip/country/joindate/spending) with surrogate key: "pkid".However other queries will not use this primary key. So for instance they may count the # of folks at a zip, select all names, addresses etc. The queries may order by join date, or select all the people that joined on a specific date.No other code would logically use the primary key (surrogate primary id key), therefore would it still have any performance benefits? at this time the table would have no clustured or nonclustured indexes or keys. I'm curious if there are millions of records.
View 7 Replies View RelatedAfter SQL server service restart, a column which is set to auto increment jumped 1000. To fix the issue, I had to add T272 trace flag to SQL startup parameters. However, I did not see the column being reseeded after the service restart, it is still showing the 1000 jump. Am I doing something wrong?
Below the log showing the flag being added to the error log:
LogDateProcessorInfoErrorMSG
2015-06-15 22:29:53.850ServerRegistry startup parameters:
-d E:DATAmaster.mdf
-e E:logERRORLOG
-l E:DATAmastlog.ldf
-T 272
Hi
I created a new database called "TestReportServer" as mentioned in the installation instruction but I didn't
see (or could select) the option "Create the report server database in SharePoint integrated mode".
How can I select this option? Do I need to remove the reporing services and reinstall it again? Any suggestions?
After creating the database I get the error 'Some or all identity references could not be translated'.
The user I selected is a local administrator and has permission to all groups starting with wss.
I guess the database is not created as a sharepoint integration mode as I can start Server Management Studio
and see the database. Is that a correct assumption?
I hope somebody out there can help as I am strating to bang my head towards my desk right now :-)
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies View RelatedWhile I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies View RelatedHi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
View 1 Replies View Relatedwhen i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies View Relatedhey i have a very simple form here: www.syscpupower.com
I would like to have the information from the form saved to a sql db with a time stamp.
here is the code i have for the page below:"C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
"server">
"form1" runat="server">
"z-index: 108; left: 0px; position: absolute; top: 189px">
"z-index: 109; left: 204px; width: 411px; position: absolute; top: 18px;
height: 160px">
"3" rowspan="3" style="text-align: center"> Please accept to view your detailed trip information.
"Button1" runat="server" OnClick="Button1_Click" Style="z-index: 102;
left: 322px; position: absolute; top: 261px" Text="Accept" Width="60px">
"Button2" runat="server" OnClick="Button2_Click" Style="z-index: 103;
left: 392px; position: absolute; top: 261px" Text="Decline" Width="64px">
"Label1" runat="server" Style="z-index: 104; left: 254px; position: absolute;
top: 198px" Text="Please Enter Your First and Last Name!" Width="273px">
"TextBox1" runat="server" Style="z-index: 105; left: 278px; position: absolute;
top: 229px" Width="216px">
"RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1"
ErrorMessage="FirstName LastName" Font-Size="12px" Style="z-index: 106; left: 500px;
position: absolute; top: 229px">*
"ValidationSummary1" runat="server" Style="z-index: 110;
left: 263px; position: absolute; top: 300px" Width="284px">
Hello
A
B
C
D
1
230
40
190
190
2
470
194
276
466
3
376
328
48
514
4
558
340
218
732
5
447
483
-36
696
6
218
357
-139
557
7
395
414
-19
538
8
264
375
-111
427
9
214
285
-71
356
10
218
251
-33
323
In the above table I am looking to replicate column D in sql, columns A,B and C are already generated by the sql.
D is currently done in Excel using =D1 + C2 which is in spot D2.
Can this be done in sql? Does anyone have an example. I am looking to do it in a select query if possible.
Thanks
I have a details view with several parameters set up in my asp.net 2.0 code, I want to add a parameter before the sql parameter is executed. I need to use the find control of the details view because I am using items/edit item templates in my details view control. I tried this(see below) as well as the detailsview item command event args to no avail. It doesn't see the other parameters that have already been declared in my asp.net code. I don't want to have to declare all my varibles that are already in my asp.net code. I just want to add another parameter. Sub InsertNew(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) _ Handles dvEvents.ItemInserting Dim dvr As DetailsViewRow For Each dvr In dvEvents.Rows Dim CatIDup As Integer = CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue sdsevents.InsertParameters.Add("evCatID", CatIDup)sdsevents.Insert()
View 4 Replies View RelatedLooking for any help someone can offer on adding an image to SQL.Working on what will hopefully be quite a big site and the user will be asked for some details, one of which will be an image (jpg, gif or bmp).Rather than add the image to a folder on the server, a folder which could end up being quite large, I was hoping someone could give me some help, suggestions or even point me towards a tutorial on how to store the image in an SQL database.Any helps appreciated, thanks :)
View 2 Replies View RelatedHi Everyone,
I have a page with a textbox and a dropdown list. The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list.
I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list.
Here is the codebehind file:Imports System.Data.SqlClient
Imports System.Web.Configuration
Partial Class cms_Management_Company_NewCompany
Inherits System.Web.UI.Page
Dim companyName As String
Dim companyActive As Boolean
Dim companyArchived As Boolean
Dim companyDelegates As Integer
Dim userForeName As String
Dim userSurname As String
Dim userEmail As String
Dim userUsername As String
Dim userPassword As String
Dim userActive As Boolean
Dim userTypeID As Integer
Dim companyID As Integer
Dim i As Integer
Dim NoLoops As Integer
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSave.Click
Dim conString As String = WebConfigurationManager.ConnectionStrings("General").ConnectionString
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@CompanyName, @CompanyActive, @CompanyArchived, @CompanyDelegates)", con)
cmd.Parameters.AddWithValue("@CompanyName", companyName)
cmd.Parameters.Item("@CompanyName").Value = txtCompanyName.Text
cmd.Parameters.AddWithValue("@CompanyDelegates", companyDelegates)
cmd.Parameters.Item("@CompanyDelegates").Value = lstDel.SelectedValue
cmd.Parameters.AddWithValue("@CompanyActive", companyActive)
cmd.Parameters.Item("@CompanyActive").Value = True
cmd.Parameters.AddWithValue("@CompanyArchived", companyArchived)
cmd.Parameters.Item("@CompanyArchived").Value = False
Using con
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
Dim con2 As New SqlConnection(conString)
Dim cmd2 As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@UserForeName, @UserSurname, @UserEmail, @UserUsername, @UserPassword, @UserActive, @UserTypeID, @CompanyID)", con2)
cmd2.Parameters.AddWithValue("@UserForeName", userForeName)
cmd2.Parameters.Item("@UserForeName").Value = "First Name - Delegate 1"
cmd2.Parameters.AddWithValue("@UserSurname", userSurname)
cmd2.Parameters.Item("@UserSurname").Value = "Surname - Delegate 1"
cmd2.Parameters.AddWithValue("@UserEmail", userEmail)
cmd2.Parameters.Item("@UserEmail").Value = "Email Address - Delegate 1"
cmd2.Parameters.AddWithValue("@UserUsername", userUsername)
cmd2.Parameters.Item("@UserUsername").Value = "Username - Delegate 1"
cmd2.Parameters.AddWithValue("@UserPassword", userPassword)
cmd2.Parameters.Item("@UserPassword").Value = "Password - Delegate 1"
cmd2.Parameters.AddWithValue("@UserActive", userActive)
cmd2.Parameters.Item("@UserActive").Value = True
cmd2.Parameters.AddWithValue("@UserTypeID", userTypeID)
cmd2.Parameters.Item("@UserTypeID").Value = 2
cmd2.Parameters.AddWithValue("@UserTypeID", userTypeID)
cmd2.Parameters.Item("@UserTypeID").Value = 1
Using con2
con2.Open()
For i = 1 To NoLoops
cmd2.ExecuteNonQuery()
Next i
con2.Close()
End Using
Response.Redirect("~/cms/Management/Company/Company.aspx")
End Sub
End ClassThe other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)I hope this makes sense.Thank you very much guys.Scott.
I am trying to insert a value numeric + 1 in to db table but i get error when i do this
this is the code
Const SQL As String = "INSERT INTO [PageHits] ([DefaultPage]) VALUES (@defaultP)"Dim myCommand As New Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@DefaultP" + "1", DefaultP.Text.Trim())
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
The Error:
Must declare the variable '@defaultP'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@defaultP'.
i want to add vales in a query
this is my scenario (how can i add the price column values and display as one record) ex. 222 17
table = table1
tep1 price (column)
222 5
222 2
222 10
Hi all,
I have a database setup with a few rows and i would like to allow a user to create a new row of data. After some googling, I've deduced that I need to somehow use an onclick command to perform an insert command. here is my code as of right now:
protected void ItemAdd_Click(object sender, EventArgs e)
{int newnum = 4;int POnum = newnum ++;
string Item = textbox3.Text.ToString();
string Quantity = textbox4.ToString();string Part = textbox5.ToString();
string Description = textbox6.Text.ToString();string Price = textbox7.Text.ToString();string UOM = textbox8.Text.ToString();
SqlDataSource2.InsertParameters.Clear();
SqlDataSource2.InsertParameters.Add("@Item", Item);SqlDataSource2.InsertParameters.Add("@Quantity", Quantity);
SqlDataSource2.InsertParameters.Add("@Part", Part);SqlDataSource2.InsertParameters.Add("@Description", Description);
SqlDataSource2.InsertParameters.Add("@Price", Price);SqlDataSource2.InsertParameters.Add("@UOM", UOM);
SqlDataSource1.InsertCommand = "insert into ItemMaster "+"values (12, @Item, @Quantity, @Part, @Description, @Price, @UOM)";
SqlDataSource2.Insert();
}
here's the aspx:
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="createpo.aspx.cs" Inherits="Subpgs_Purchasing_createpo" Title="Create A PO" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table style="width: 600px; position: relative; top: 30px; height: 253px" cellpadding="10px">
<tr>
<td style="width: 180px; text-align: left;" rowspan="2">
Select Vendor<br />- or -<br /><asp:LinkButton ID="Linkbutton1" runat="server" OnClick="Linkbutton1_Click">Create Vendor</asp:LinkButton></td>
<td style="width: 100px" rowspan="2">
<asp:ListBox ID="ListBox2" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name"
DataValueField="ID" OnSelectedIndexChanged="ListBox2_SelectedIndexChanged"></asp:ListBox><asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Vendor_info]"></asp:SqlDataSource>
</td>
<td style="width: 180px; text-align: left;">
Job/Req.S.O. No.</td>
<td style="width: 100px">
<asp:TextBox ID="Job" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 180px; text-align: left;">
Terms</td>
<td style="width: 100px">
<asp:TextBox ID="Terms" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 180px; text-align: left;">
F.O.B.</td>
<td style="width: 100px">
<asp:TextBox ID="FOB" runat="server"></asp:TextBox></td>
<td style="width: 180px; text-align: left;">
Ship To:<br />
Address:<br />
City / State / Zip:</td>
<td style="width: 100px">
<asp:TextBox ID="Ship1" runat="server"></asp:TextBox>
<asp:TextBox ID="Ship2" runat="server"></asp:TextBox>
<asp:TextBox ID="Ship3" runat="server"></asp:TextBox></td>
</tr>
</table>
<br />
<br />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Purchase_Orders.mdf;Integrated Security=True;User Instance=True;Context Connection=False"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT [Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber] FROM [ItemMaster]" DeleteCommand="DELETE FROM [ItemMaster] WHERE [PONumber] = @PONumber" InsertCommand="INSERT INTO [ItemMaster] ([Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber]) VALUES (@Item, @Quantity, @Part, @Description, @Price, @UOM, @Ammount, @PONumber)" UpdateCommand="UPDATE [ItemMaster] SET [Item] = @Item, [Quantity] = @Quantity, [Part] = @Part, [Description] = @Description, [Price] = @Price, [UOM] = @UOM, [Ammount] = @Ammount WHERE [PONumber] = @PONumber">
<DeleteParameters>
<asp:Parameter Name="PONumber" Type="Decimal" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Item" Type="String" />
<asp:Parameter Name="Quantity" Type="Decimal" />
<asp:Parameter Name="Part" Type="String" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="Price" Type="Decimal" />
<asp:Parameter Name="UOM" Type="String" />
<asp:Parameter Name="Ammount" Type="Decimal" />
<asp:Parameter Name="PONumber" Type="Decimal" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="Item" Type="String" />
<asp:Parameter Name="Quantity" Type="Decimal" />
<asp:Parameter Name="Part" Type="String" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="Price" Type="Decimal" />
<asp:Parameter Name="UOM" Type="String" />
<asp:Parameter Name="Ammount" Type="Decimal" />
<asp:Parameter Name="PONumber" Type="Decimal" />
</InsertParameters>
</asp:SqlDataSource>
<br />
<table>
<tr>
<td>Item #</td>
<td><asp:TextBox ID="textbox3" runat=server></asp:TextBox></td>
<td>Quantity</td>
<td><asp:TextBox ID="textbox4" runat=server></asp:TextBox></td>
<td>Part Number</td><td><asp:TextBox ID="textbox5" runat=server></asp:TextBox></td>
</tr>
<tr>
<td>Description</td>
<td><asp:TextBox ID="textbox6" runat=server></asp:TextBox></td>
<td>Unit Price</td>
<td><asp:TextBox ID="textbox7" runat=server></asp:TextBox></td>
<td>Unit of Measure</td>
<td><asp:TextBox ID="textbox8" runat=server></asp:TextBox></td>
</tr>
<tr>
<td colspan="6" align="center"><asp:Button ID="ItemAdd" runat=server text="Add Item" OnClick="ItemAdd_Click" /></td>
</tr>
</table>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="PONumber"
DataSourceID="SqlDataSource2">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="Item" HeaderText="Item" SortExpression="Item" />
<asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity" />
<asp:BoundField DataField="Part" HeaderText="Part" SortExpression="Part" />
<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
<asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
<asp:BoundField DataField="UOM" HeaderText="UOM" SortExpression="UOM" />
<asp:BoundField DataField="Ammount" HeaderText="Ammount" SortExpression="Ammount" />
<asp:BoundField DataField="PONumber" HeaderText="PONumber" ReadOnly="True" SortExpression="PONumber"
Visible="False" />
</Columns>
</asp:GridView>
<br />
<br />
<br />
<asp:Button ID="Button1" runat="server" Text="Submit Data" OnClick="Button1_Click" /> <br />
<br />
<asp:Label ID="Label1" runat="server" Text="Please Verify Information below!" Visible="False"></asp:Label><br />
<br />
<table align="left">
<tr>
<td style="width: 200px; text-align: left;">
<asp:Label ID="Label4" runat="server" Text="Job/REQ.S.O. No." Visible="false"></asp:Label></td>
<td style="width: 200px; text-align: left;">
<asp:Label ID="Label5" runat="server"></asp:Label></td>
</tr>
<tr>
<td style="width: 200px; text-align: left;">
<asp:Label ID="Label7" runat="server" Text="Terms" Visible="false"></asp:Label></td>
<td style="width: 200px; text-align: left;">
<asp:Label ID="Label8" runat="server"></asp:Label></td>
</tr>
<tr>
<td style="width: 200px; text-align: left;">
<asp:Label ID="Label10" runat="server" Text="F.O.B." Visible="false"></asp:Label></td>
<td style="width: 200px; text-align: left;">
<asp:Label ID="Label11" runat="server"></asp:Label></td>
</tr>
</table>
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
Height="50px" Visible="False" Width="260px" BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellPadding="4" CellSpacing="2" DataKeyNames="ID" ForeColor="Black">
<FooterStyle BackColor="#CCCCCC" />
<EditRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="White" />
<PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
<Fields>
<asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Street" HeaderText="Street" SortExpression="Street" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
<asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
<asp:BoundField DataField="Telephone Number" HeaderText="Telephone Number" SortExpression="Telephone Number" />
<asp:BoundField DataField="Contact Information" HeaderText="Contact Information"
SortExpression="Contact Information" />
<asp:BoundField DataField="E-mail address" HeaderText="E-mail address" SortExpression="E-mail address" />
<asp:BoundField DataField="Fax Number" HeaderText="Fax Number" SortExpression="Fax Number" />
<asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" />
</Fields>
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
</asp:DetailsView>
<br />
<br />
<br />
<asp:Button ID="Button2" runat="server" Text="E-Mail PO" Visible="False" OnClick="Button2_Click" />
</asp:Content>
whenever I run this, I receive the following error and am not sure how to correct it:
System.Data.SqlClient.SqlException: Must declare the scalar variable "@Item".
If someone could please explain to me the process here of what is going ion it would be greatly appreciated. My understanding is the @item is just a variable if you will so i'm not sure why its stating i need to declare it.
Thank you all!
Hi Guys,
I know this is a little off the subject of ASP.net but I am using some code behind in C#. I am a real novice at this and so it may appear to be a simple question but Im trying to add a number to the database from a textbox but it is having issues as the Storenum is an int and it wants a string. Can anyone help? I know I need to write .tostring somewhere but can't work out where. My code is below.
Thanks in advance. =)
public partial class _addstore : System.Web.UI.Page
{protected string Location;
protected string Telephone;protected string Prefix;
protected int StoreNum;protected string myConnectionString;protected void Page_Load(object sender, EventArgs e)
{
Location = "default";Telephone = "default";
Prefix = "default";StoreNum = "0";myConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\HOF\App_Data\HOF.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;";
}protected void Button1_Click(object sender, EventArgs e)
{int sa;
SqlConnection myConnection = new SqlConnection(myConnectionString);
try
{
Location = TextBox2.Text;
Telephone = TextBox3.Text;
Prefix = TextBox4.Text;
StoreNum = TextBox5.Text;SqlCommand myCommand = new SqlCommand("INSERT INTO StoreInfo (StoreID, Location, Telephone, Prefix) Values (" + StoreNum + ", '" + Location + "', '" + Telephone + "', '" + Prefix + "')", myConnection);SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand);
myConnection.Open();
sa = myCommand.ExecuteNonQuery();
myConnection.Close();
}catch (Exception ex)
{
TextBox2.Text = ex.Message;
myConnection.Close();
}
}
}
tell me how to add foreign keys in sql 2005 express
View 3 Replies View Related