Insert Values Error
Oct 19, 2004
Hey Guys:
I am trying to create a form, and then insert the values entered by a user into a sql database, I have enclosed the page code below. Everything works except the data is not being inserted into the database, and i keep getting the default message in my error message section. I took this right from the quick start tutorial and started working with it, and keep getting an error.
I believe the error is located in the INSERT statement
<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
Dim MyConnection As SqlConnection
Sub Page_Load(Sender As Object, E As EventArgs)
MyConnection = New SqlConnection("server=localhost;database=planetauction;uid=planetauction;pwd=bean13")
If Not (IsPostBack)
BindGrid()
Page.DataBind()
End If
End Sub
Sub AddAuthor_Click(Sender As Object, E As EventArgs)
Page.Validate()
If Not Page.IsValid
Return
End If
Dim DS As DataSet
Dim MyCommand As SqlCommand
If txtLastName.Value = ""
Message.InnerHtml = "ERROR: Null values not allowed for Author ID, " & _
"Name or Phone"
Message.Style("color") = "red"
BindGrid()
Return
End If
Dim InsertCmd As String = "insert into users (txtLastName) values (@lastname)"
MyCommand = New SqlCommand(InsertCmd, MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@lastname", SqlDbType.NVarChar, 50))
MyCommand.Parameters("@lastname").Value = txtLastName.Value
MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the " & _
"same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
"the fields are correctly filled out"
End If
Message.Style("color") = "red"
End Try
MyCommand.Connection.Close()
BindGrid()
End Sub
Sub BindGrid()
Dim MyCommand As SqlDataAdapter = new SqlDataAdapter( _
"select * from users", MyConnection)
Dim DS As DataSet = new DataSet()
MyCommand.Fill(DS, "Users")
MyDataGrid.DataSource=DS.Tables("Users").DefaultView
MyDataGrid.DataBind()
End Sub
</script>
<body style="font: 10pt verdana">
<form runat="server" ID="Form1">
<h3><font face="Verdana">Inserting a Row of Data</font></h3>
<table width="95%">
<tr>
<td valign="top">
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>
</td>
<td valign="top">
<table style="font: 8pt verdana">
<tr>
<td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New Author:</td>
</tr>
<tr>
<td nowrap>Last Name: </td>
<td>
<input type="text" id="txtLastName" runat="server" NAME="txtLastName"><br>
</td>
</tr>
<tr>
<td></td>
<td style="padding-top:15">
<input type="submit" OnServerClick="AddAuthor_Click" value="Add Author" runat="server" ID="Submit1" NAME="Submit1">
</td>
</tr>
<tr>
<td colspan="2" style="padding-top:15" align="center">
<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>
View 3 Replies
ADVERTISEMENT
Mar 24, 2008
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
View 4 Replies
View Related
Oct 12, 2007
In SQL 2005 I created table as
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[logMsg](
[logMsgID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[msg] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL,
[AppId] [int] NULL,
CONSTRAINT [PK_logMsg] PRIMARY KEY CLUSTERED
(
[logMsgID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
and trying to insert values with
INSERT INTO [ProxyDB].[dbo].[logMsg]
([msg]
,[AppId])
VALUES
('Text Test',1)
Getting error message:
Msg 213, Level 16, State 1, Procedure TrgInslogMsg, Line 14
Insert Error: Column name or number of supplied values does not match table definition.
Urgent help is required
View 1 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Jan 4, 1999
My process return a date in char format like "3/12/1998",
How could I put this data ( in char ) directly to date ISQ Server field ?
Thank's
View 1 Replies
View Related
Sep 1, 2007
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
View 3 Replies
View Related
Apr 8, 2008
Hi,
I want to insert data into table without duplicate values ..
how to do?
View 5 Replies
View Related
Apr 29, 2007
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
View 1 Replies
View Related
Apr 8, 2008
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul
View 1 Replies
View Related
Oct 25, 2007
hi everyone
how do i insert multiple rows in a database ?
i came up with something like this after googling but it does not work
INSERT INTO tblSold (LID, BuyerID,Date)select ('759','2106','2441') UNION ALLselect ('0','0','0') UNION ALLselect ('10/25/2007','10/25/2007','10/25/2007')
View 8 Replies
View Related
Jan 12, 2008
Hi,I have a button that that executes insertion of data into a database. For the data it inserts into one column, the data is located in another database table.As well as this being inserted, I would like more data to be inserted in the same column, which comes from textboxes which are located on the same page as the button that executes the insertion..For example: someone types in information into textboxes, then presses the button. The code behind then inserts the data from the textboxes plus the data from the other table (for which the coding is already done).Here is my current code: public bool [snip](int ProductId) {[snip] command.CommandText = "INSERT INTO Messages (sendername,recievername,message,senddate,subject) VALUES (@sendername,@recievername,@message,@date,@subject)"; command.Parameters.AddWithValue("@sendername", System.Web.HttpContext.Current.User.Identity.Name); DataView dv = SqlDataSource2.Select(DataSourceSelectArguments.Empty) as DataView; string receivername = " " + dv[0]["Usersname"].ToString() + " "; command.Parameters.AddWithValue("@recievername", receivername); DataView dv2 = SqlDataSource3.Select(DataSourceSelectArguments.Empty) as DataView; string message = dv2[0]["paymentinstructions"].ToString(); command.Parameters.AddWithValue("@message", message); command.Parameters.AddWithValue("@subject", TextBox1.Text); command.Parameters.AddWithValue("@date", DateTime.Now); command.ExecuteNonQuery(); con.Close(); command.Dispose(); return true; } So along with "paymentinstructions" being inserted into 'message', I would also like the values from TextBox4, TextBox5 and TextBox6 to be inserted... It would also be good if text could be inserted infront of the values of these textboxes - e.g. Price: "TextBox4's value"Does anyone have any ideas? Reply if it needs further explanation.Thanks,Jon
View 14 Replies
View Related
Feb 12, 2008
I have a table wih multiple records and some has common values.
For example, new child row need to inherit some of parent's data.
Here is how I want to do:
Select [parent data1], [parent data2] from [TABEL] where id = parent
Insert INTO [TABLE] ([child id], [child data1], [child data2] ) VALUES [child id] [parent id] [parent id]...........
I was thinking about using Selected and Inserting events but not sure how.
Please help?
Thanks,
View 1 Replies
View Related
May 9, 2008
I have a form with a few panels on it, and the visibility of the panels is determined by a radiobutton selection. Once a user selects a radio button item, the appropriate panels visibility changes to true. On two of the panels, there is a drop down list with a list of clients, and the dropdownlists are named Client1 and Client2 (Client 1 is included in panel 1 and client 2 is in panel 2)
How do I insert the values for client1 or client2 (depending upon which is visible) into the same sql column via a stored procedure? The database field is called client. How do I get the value from my vb.net form into the stored procedure?
View 2 Replies
View Related
Jan 28, 2005
Hi all
Iam having some problems with null values
try
{
cmd.Parameters["@fmv"].Value = Convert.ToDecimal(TextBox13.Text);
}
catch
{
cmd.Parameters["@fmv"].Value =System.Data.SqlTypes.SqlDecimal.Null;
}
for Empty Textbox values
i am getting the following error,
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
My Database is allowed to accept null values.
System.Data.SqlType.SqlDatetime.Null works but SqlDecimal.Null does not work.
Could any one help me with this?
Thanks
Raj
View 2 Replies
View Related
Jul 10, 2001
I have two tables.
S
SNO CHAR(5)
SNAME CHAR(20)
S1
SNo CHAR(5)
SNAME CHAR(20)
The structure is same but S(SNAME) does not contain data. If I use the following command of SQL then It appends the rows in table. I want that the values should be inserted from TOP to BOTTOM.
INSERT INTO S (SNAME)
Select sname from s1
Could anybody solve this probelm?
Thanks
View 2 Replies
View Related
Nov 20, 2000
I have a situation where I need to create an insert trigger on table a which will create a corresponding record in table b. However before I insert the record i must obtain the max value for the record in table b and increment it by one. I have all this working. My question is if I just put a begin and commit with this statement is there a chance that when 2 users insert at the same time the max value may be incorrect say for instance
CREATE TRIGGER tr_cms_prov_ins ON provider
FOR INSERT
as
declare @ndentPrid char(3),
@nxtgenPrid char(7),
@fname varchar(40),
@lname varchar(40)
begin tran
select @ndentPrid = max(provider_id) from providerdnt
if @ndentPrid is null
set @ndentPrid = 1
else set @ndentPrid = @ndentPrid + 1
insert into dental..provider (provider_id, first_name, last_name, collections_go_to)
select @ndentPrid, first_name, last_name','YYYYYYYYYYYYYYYNNNNN' from inserted
commit tran
Will this do it or do I need to enforce some type of locking to handle the max value. There are no inserts into
table b directly only by the trigger insert on table a
View 1 Replies
View Related
Jan 5, 2000
i have about 2,000 record and i need to insert them in my table.
how do i insert these informations using this syntax??
au_id au_name au_fname
1003 vivian latin
1005 cecy mani
1004 bili david
insert into autors (au_id, au_name,au_fname)
Values ('1101', 'Rabit','jesicca')
thanks for your time and help.
View 1 Replies
View Related
Oct 3, 2005
hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain it
View 3 Replies
View Related
Oct 18, 2006
Hello
for MS SQL 2000
I am having MyTable with 7 columns : A,B,C,D,E,F,G
i want to
INSERT INTO myTable
SELECT A,B,C,D FROM FirstTable WHERE FirstTable.ID > 100
and the columns E,F,G :
E = 1
F = SELECT Max(ID) AS F FROM SecondTable
G = 0
how can i do it ?
i would like to create a stored procedure
thank you
View 2 Replies
View Related
Apr 14, 2014
this is my data
AM1WSJZ1241
AM1WLSU7162
AM1SXBI5100
AM1TWXX0477
AM1MSMQ6167
AM1WRQP1810
AM1HNME1411
i want a query to insert a data in table
View 1 Replies
View Related
Aug 12, 2014
I need to insert a record in a table which has about 90 columns.
My first column is a CustCode that would be supplied by parameter all other columns should be set to 0
Is there a quick way of doing this?
View 5 Replies
View Related
Sep 24, 2014
I have a table as dbo.Bewegungen and it will be updated every day with new dates
I inserted once the values of this table to another table with group by. I want to know how should i insert the new values every day to the new table? i don't want to insert the previous values again.
it has 5 columns as primary key and one of them is Date.
View 2 Replies
View Related
Oct 3, 2005
hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain it
bye
View 1 Replies
View Related
Dec 28, 2005
Hi,
Wondering if anyone can help with this..
I want to INSERT some data already SELECTED in a query and also some other variables.. Will the **dodgy code** make it easier to understand I wonder..
** DODGY CODE **
INSERT INTO tblTableOne(Column1, Column2, Column3, Column4)
VALUES( ( SELECT Column1, Column2 FROM tblTableTwo ), "Text for Column3", "And text for column4")
** END OF DODGY CODE **
I understand it may have something to do with TEMP TABLES perhaps??
Any help greatly appreciated..
KingRoon
Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.
View 4 Replies
View Related
Sep 6, 2006
create proc dbo.usp_CatchAddressException @tableName varchar(500) as
-- alter proc dbo.usp_CatchAddressException @tableName varchar(500) as
begin
set nocount on
declare @sql varchar(700)
-- truncate table tempException
set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp)
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate()
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in
(select nm_street from HAVA_DCDE.dbo.state_streets ss)'
-- and a.id_town = + @tableName
-- print(@sql)
exec(@sql)
set nocount off
end
---------------------------------------------------
when i run this SP 2 times, it will insert the values 2 times.
what should i do if i don't want to insert the same values 2 times?
I'm confussed, Should i use update? any input will be appreciated.
View 20 Replies
View Related
Oct 24, 2006
Simple question, I hope. How can I retrieve the AutoNumber generated by
INSERTing a row using SqlCommand? This would be the unique ID of the
record and I need to perform other operations with it.
Any help would be great. Thanks in advance
View 5 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Jul 24, 2006
i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date. i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this? what i currently have doesn't blow up, but neither does it actually insert anything. here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>" InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)"> <InsertParameters> <asp:Parameter Name="Major" Type="Int32" /> <asp:Parameter Name="Minor" Type="Int32" /> <asp:Parameter Name="Sub" Type="Int32" /> <asp:Parameter Name="Rev" Type="Int32" /> <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" /> </InsertParameters></asp:SqlDataSource>
View 7 Replies
View Related
Oct 20, 2006
Im trying to setup a SQL server 2000 database to use membership & roles. Running aspnet_regsql.exe gives me the following errorsSetup failed.Exception:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 515 and the SqlException message is: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.Warning: Null value is eliminated by an aggregate or other SET operation.The statement has been terminated.----------------------------------------Details of failure----------------------------------------SQL Server: Database: [AddressVerification]SQL file loaded:InstallCommon.sqlCommands failed:CREATE TABLE #aspnet_Permissions( Owner sysname, Object sysname, Grantee sysname, Grantor sysname, ProtectType char(10), [Action] varchar(20), [Column] sysname)INSERT INTO #aspnet_PermissionsEXEC sp_helprotectIF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Setup_RestorePermissions') AND (type = 'P')))DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissionsSQL Exception:System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.Warning: Null value is eliminated by an aggregate or other SET operation.The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)Ive run this many times on other servers and have never come across this problem before. Has anyone else ? Does anyone know the cause and if there is a fix ??** I am logged in with full admin privaliges tiaMark.
View 3 Replies
View Related
Nov 16, 2006
I have a this SP that inserts values into a table with results from a query, but at the same time I want to insert some default values.But thats NOT working the way I hoped for, actually sqlserver 2005 dont let me create this SP at all."Incorrect syntax near the keyword 'DEFAULT'."Can someone please tell me how I can achieve this? create procedure %PROC% ( @Ordre_ID int
)
asbeginIF NOT EXISTS(SELECT Ordre_ID FROM tbl_Ordre WHERE Ordre_ID = @Ordre_ID AND Ordrestatus IN ('2', '3', '4'))BEGIN return 0;ENDIF EXISTS(SELECT Ordre_ID FROM tbl_Faktura WHERE Ordre_ID = @Ordre_ID)BEGIN return 0; ENDBEGIN TRANSACTIONINSERT INTO tbl_Faktura( Ordre_ID ,PostNummer ,KID ,Fakturastatus ,Kontonummer ,Forfallsdato ,Belop ,BekreftetBetaltDato ,Faktura_GUID ,Adresse ,PostBoks ,Fornavn ,Etternavn ) (SELECT O.Ordre_ID ,K.PostNummer ,DEFAULT
,DEFAULT
,SI.Kontonummer
,(getdate()+14) ,v_OTS.TOTALBELOP ,DEFAULT
,DEFAULT
,K.Adresse
,K.PostBoks
,K.Fornavn
,K.Etternavn
FROM
tbl_Ordre AS O INNER JOIN
tbl_Kunde AS K ON
O.Kunde_ID = K.Kunde_ID
INNER JOIN
v_OrdreTotalSum AS v_OTS ON
O.Ordre_ID = v_OTS.Ordre_ID
,tbl_StatiskeInnstillinger AS SI WHERE
O.Ordre_ID = @Ordre_ID
)
UPDATE
tbl_Ordre
SET
Ordrestatus = '6'
WHERE Ordre_ID = @Ordre_ID COMMIT end
go
View 3 Replies
View Related
Dec 4, 2006
Hello,I have 3 tableTable 1 : list of "whatever" programTable 2: list of tasks for each programTable 3: list of user for each taskWhen I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.Is there a way to do that in sql?I do not really want to go through each single task, then copy it with the new program, then get the @@identity of the inserted task and then assign the same user to the newly inserted task.Thanks
View 3 Replies
View Related
Sep 18, 2007
Ok what i am looking to do i cannot figure out.
What i want to do is have a simple script that when a user logs onto the website (via windows auth) to get there username somehow llike with
Request.ServerVariables("LOGON_USER")
that should display there username either "domain/name" or "username"
and then insert that into the UserLogs Table under my database with the date with the GETDATE() command..
But when i do this i cannot get the page to auto submit the values. Actually i cannot get anything to write to the DB unless i am doing it under the query builder.
Here is my Query that i was using.
INSERT INTO UserLogs([User], Date) VALUES (@UserName, GETDATE())
then in the Code of the page i have
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="SubmitForm.aspx.vb" Inherits="Template_SubmitForm" %><!DOCTYPE html PUBLIC "-//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" >
<head runat="server"><title>Untitled Page</title>
</head>
<body><% Dim name
name = Request.ServerVariables("LOGON_USER")%>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:REPCOMConnectionString %>"
InsertCommand="INSERT INTO UserLogs([User], Date) VALUES (@name, GETDATE())"
SelectCommand="SELECT [User], Date FROM UserLogs" CancelSelectOnNullParameter="False">
<InsertParameters>
<asp:SessionParameter DefaultValue="test1234" Name="name" SessionField="name" />
</InsertParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
So i know i am doing something wrong but what?
Thank You,
Corey
View 1 Replies
View Related
Sep 20, 2007
I need to insert some values into a table and after that catch the ID inserted.
I set some input parameters in stored procedure and only one to get the @id defined as output
SqlParameter paramIdPedido = new SqlParameter("@APP_IDPEDIDO", SqlDbType.Int, 4);paramIdPedido.Direction = ParameterDirection.Output;cmd.Parameters.Add(paramIdPedido);
Do I have to run cmd.ExecuteNonQuery(); to record first what I need and afterrun ExecuteReader: something like SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()... to get the ID)
From stored procedure:
INSERT table(fields) VALUES(vars)SELECT TOP 1 @id = id FROM table ORDER BY id DESC
I must do all these steps or maybe is there anything less complex to do?
Thanks!
View 1 Replies
View Related
