Hey folks,
I am trying to updat a table by inserting values from another table into the parent table. The SQL being used is:
"insert into "LogSchema"."_rt1182" select * from "LogSchema"."_rt557" where recordID not in (select * from "LogSchema"."_rt1182") ORDER BY TIMESTAMP"
I have used the above statement and was successful, but with these two particluar tables I am getting an error message" Wrong number of values to INSERT"
Please lemme know, what this means and how to correct this problem!
TIA
hey guys I got an error when trying to insert an SQL statement. basically it when I try to insert into a unique ID column I think error is: Compiler Error Message: BC30516: Overload resolution failed because no accessible 'New' accepts this number of arguments.Source Error:
Line 51: objCmd = New SQLCommand(strSQL1, SqlConn) Line 52: Line 53: objCmd.Parameters.Add(New SqlParameter("@CustomerID")) Line 54: objCmd.Parameters("@CustomerID").Value = UserID Line 55: objCmd.Parameters.Add(New SqlParameter("@CompanyID", _Source File: C:Prototypeofficial1html icketsystem.aspx Line: 53
Dim strConnection as string = ConfigurationManager.ConnectionStrings("GeolinkSupportdb").connectionstring Dim sqlConn = New SqlConnection(strConnection) dim mu = Membership.GetUser() dim UserID = mu.ProviderUserKey.ToString()
Sub Page_Load(sender as Object, e as EventArgs)
If Not IsPostBack() then '1. Create a connection Dim sqlConn = New SqlConnection(strConnection) '2. Create the command object, passing in the SQL string Const strSQL as String = "SELECT IssueID, IssueName FROM Support_Issue;" sqlConn.Open() Dim myCommand as New SqlCommand(strSQL, sqlConn) '3. Create the DataReader
Dim objDR as SqlDataReader objDR = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'Databind the DataReader to the listbox Web control TicketIssue.DataSource = objDR TicketIssue.DataTextField = "IssueName" TicketIssue.DataValueField = "IssueID" TicketIssue.DataBind()
'Add a new listitem to the beginning of the listitemcollection TicketIssue.Items.Insert(0, new ListItem("-- Choose an Issue --")) objDR.close() sqlConn.Close()
'assign fullname from profile and company ID to the text fields. FullName.Text = profile.firstName & " " & profile.lastName Company.Text = profile.CompanyID end if End Sub Sub Submit(sender as Object, e as EventArgs) Dim rightNow as DateTime = DateTime.Now Dim strSQL1 = "insert into Support_Ticket (CustomerID, CompanyID, Subject, Problem, Open_Date, IsClosed) Values (@CustomerID, @CompanyID, @Subject, @Problem, @OpenDate, @IsClosed);" Dim objCmd As SqlCommand objCmd = New SQLCommand(strSQL1, SqlConn)
objCmd.Parameters.Add(New SqlParameter("@CustomerID", _ SqlDbType.uniqueidentifier)) objCmd.Parameters("@CustomerID").Value = UserID objCmd.Parameters.Add(New SqlParameter("@CompanyID", _ SqlDbType.uniqueidentifier)) objCmd.Parameters("@CompanyID").Value = profile.CompanyID objCmd.Parameters.Add(New SqlParameter("@Subject", _ SqlDbType.varchar, 50)) objCmd.Parameters("@Subject").Value = Subject.Text objCmd.Parameters.Add(New SqlParameter("@Problem", _ SqlDbType.ntext)) objCmd.Parameters("@Problem").Value = TicketInfo.Value objCmd.Parameters.Add(New SqlParameter("@Open_Date", _ SqlDbType.DateTime)) objCmd.Parameters("@Open_Date").Value = rightNow.ToString("dd/MM/yyyy , HH:mm:ss") objCmd.Connection.Open() objCmd.Parameters.Add(New SqlParameter("@IsClosed", _ SqlDbType.bit)) objCmd.Parameters("@IsClosed").Value = 0 ' Test whether the new row can be added and display the ' appropriate message box to the user. Try Catch ex As SqlException objCmd.ExecuteNonQuery() Message.InnerHtml = "<b>Record Added</b> to Database<br>"
If ex.Number = 2627 Then 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" Message.Style("color") = "red"
End If End Try objCmd.Connection.Close() end sub
the database looks like this TicketID uniqueidentifier CustomerID uniqueidentifier Subject varchar(50) Problem ntext Open_Date datetime IsClosed bit Closed_Date datetime DeptID uniqueidentifier CompanyID varchar(50)
Msg 515, Level 16, State 2, Procedure aspnet_Roles_CreateRole, Line 37 Cannot insert the value NULL into column 'RoleId', table 'PORTAL.MDF.dbo.aspnet_Roles'; column does not allow nulls. INSERT fails. The statement has been terminated. this MS sql server 2005 using the aspnetdb.mdf. The RoleID is an primary key and supposed to be autopopulating itself everytime there is an new insertion correct? so what is the problem?
i get error 605 on several occassions... namely when i am doing a bcp into the database OR when a user is trying to update a record. it seems very sparodic otherwise, but it always happens during the bcp insert. if anyone has any ideas or suggestions on how to correct this issue, it would be greatly appreciated. need additional info?
We are running a application that uses SQL. No users use this database but the application. We have been getting an error stating that there was an error inserting into one of my tables. We have increased the LE Threshold to 10000 and have also increased my locks to 200000 but it still doesn't get rid of the problem. Any ideas as to why. I can't imagine that this application will hold that many locks but then again I'm not the developer. Any suggestions will help.
Hi all, I made a C# function to insert new row to MS SQL Server 2005. However, I kept getting this error when I executed it:
Code:
Line 1: Incorrect syntax near '0'. Unclosed quotation mark before the character string '','{ 0 }','Sun, Mar 25, 2007 04:33:00 PM')'.
here is the sql insert statement
Code:
insert into Temip_tmp_3(Managed_object, Alarm_type, Perceived_Severity, Probable_Cause, Domain, Additional_Text, Specific_Problems, Original_Event_Time ) values ('','EquipmentAlarm','Major','EquipmentMalfunction','Domain tsel_ns:.dom.radio_ericsson','"EXTERNAL ALARM RECEIVER FAULT *** ALARM 121 A2/IO_DEV BTAK1 042/0700H U 070325 1633 EXTERNAL ALARM RECEIVER FAULT AP APNAME NODE NODENAME 1 AP_BTAK1_C A AP_BTAK1_A APNODE FCODE B FAULT CODE 23 **SpecificProblem:121 , AdditionalInfo:@@"','{ 0 }','Sun, Mar 25, 2007 04:33:00 PM')
I believe there is not a single quotation mark unclosed in my query. And, what's so funny is when I tried to execute the query on SQL Server Enterprise Manager, it worked. I don't know, maybe I missed something. Can you guys please help me figure this out?
i have created a multiple database for other reasons i have to change all into one data base for that i have done graphically by using generate scripts by using this all data base tables, & store procedures all are created . by using webform i just inserting data to database. but here i am getting an error to me that the error has "Cannot insert the values NULL Into column Tblename database.dbo.columnname does not allow nulls.insert fails the statement has been terminated."here the primary key has not working in runtime.
my requirment is insert TableName and JourneyDate and FlightNumber alongwith otherdata at RunTime but i get error, I tried it several times. Table Structure is: Create Table HA142 ( JourneyDate DateTime primary key, FlightNo char(5)not null FOREIGN kEY REFERENCES FLIGHTS(FlightNo), FirstClassSeatAvalable int, BusinessClassSeatAvalable int, EconomyClassSeatAvalable int, FsWaitingAvalable int, BsWaitingAvalable int, EcWaitingAvalable int ) string flightno = drpFlightNo.SelectedItem.Text; string JourneyDate = Session["JourneyDate"].ToString(); string newStrign = ",18,42,280,3,7,35)"; SqlConnection myConn = new SqlConnection("workstation id=JASIM;packet size=4096;user id=ASPNET;data source=JASIM;persist security info=False;initial catalog=Test"); SqlCommand populateFlightTable = new SqlCommand("INSERT INTO "+flightno+" VALUES("+JourneyDate+","+flightno+newStrign,myConn); myConn.Open(); populateFlightTable.ExecuteNonQuery(); myConn.Close(); whenever compiler reached to populateFlightTable.ExecuteNonQuery(); I received error. i tried it to rectify several times but no result.plz hemp me...
I am trying to insert 1000000.00 into my sql table from a webpage. I as long as the amount is 999.99 or less it works fine, once higher then that amount it gives me an error. Below is the code I am using to do the insert, it gets the error on the insert and the update both: I am getting the error on the price inserting the FormatCurrency(txtprice.Text) SelectStatement = "Insert crewchief (crewchief, price, car_num) Select '" & txtcrewchief.Text & "', " & FormatCurrency(txtprice.Text) & ", '" & txtcarnum.Text & "'" Adapter.SelectCommand = New SqlClient.SqlCommand(SelectStatement, myConnection) MyCommandBuilder = New SqlClient.SqlCommandBuilder(Adapter) Adapter.Fill(MatcherDS, "temp") Any ideas on why?
Greetings, When using Inserting event of SqlDataSource ASP.NET gives me an error when I reference InsertParameter by Name An SqlParameter with ParameterName 'CreatedByEmployeeId' is not contained by this SqlParameterCollection. However, when I reference parameter by index everything works. Is this a bug or I'm doing something wrong? Here's the code: <asp:SqlDataSource ID="dsRole" runat="server" ConnectionString="<%$ ConnectionStrings:SecurityConnectionString %>" DeleteCommand="spDeleteRole" InsertCommand="spAddRole" SelectCommand="spGetRole" UpdateCommand="spUpdateRole" DeleteCommandType="StoredProcedure" InsertCommandType="StoredProcedure" SelectCommandType="StoredProcedure" UpdateCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="RoleId" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="RoleId" Type="Int32" /> <asp:Parameter Name="RoleName" Type="String" /> <asp:Parameter Name="RoleDescription" Type="String" /> <asp:Parameter Name="UpdatedByEmployeeId" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="RoleName" Type="String" /> <asp:Parameter Name="RoleDescription" Type="String" /> <asp:Parameter Name="CreatedByEmployeeId" Type="Int32" /> </InsertParameters> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="RoleId" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> When using parameter name, I get an error: Protected Sub dsRole_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles dsRole.Inserting e.Command.Parameters("CreatedByEmployeeId").Value = Internal.Security.GetEmployeeIdFromCookie(Page.Request.Cookies) End Sub When using index instead of name, there's no problem: Protected Sub dsRole_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles dsRole.Inserting e.Command.Parameters(2).Value = Internal.Security.GetEmployeeIdFromCookie(Page.Request.Cookies) End Sub
hi, I am using asp.net web admin tool to create users and the other personal details of the user are stored in a table called "Users". So in my Users table I have a field called "Id" which is of datatype UniqueIdentifier. The UserId generated in the aspnet_Membership table is also stored in my "Users" table's Id field. The problem is that an error is thrown when executing the statement System.Web.Security.Roles.AddUserToRole(name, Role). The parameters name and role are getting values correctly. But as soon as this is executed a sql exception is thrown saying that "The conflict of interleave for the equal operator cannot be solved to." Am not able to solve this.Pls help me out. Thanks in Advance.
I created my own table on the ASPNETDB.mdf file. When i try to insert data on it, i get an exception: System.Data.SqlClient.SqlException was unhandled by user code Message="String or binary data would be truncated. The statement has been terminated." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=8152 Procedure="" Server="\\.\pipe\33189AFE-4730-4B\tsql\query".... My C# code to insert:SqlConnection conexao = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString); string query = "Insert Into NovasMaquinas " +"(NomeDaMaquina, FicheiroExecutavel, FicheiroXML, AdminQueSubmeteu, Autor, VmID)" + "Values (@NomeDaMaquina, @FicheiroExecutavel, @FicheiroXML, @AdminQueSubmeteu, @Autor, @VmID)";SqlCommand cmd = new SqlCommand(query, conexao); cmd.Parameters.AddWithValue("@NomeDaMaquina", textboxNomeVM.Text);cmd.Parameters.AddWithValue("@FicheiroExecutavel", path + fileUploadEXE.PostedFile.FileName); cmd.Parameters.AddWithValue("@FicheiroXML", path + fileUploadEXE.PostedFile.FileName);cmd.Parameters.AddWithValue("@AdminQueSubmeteu", User.Identity.Name); cmd.Parameters.AddWithValue("@Autor", textboxAutorVM.Text);cmd.Parameters.AddWithValue("@VmID", Guid.NewGuid().ToString()); conexao.Open(); //cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); //THAT IS THE LINE WHERE THE EXCEPTION IS THROWN conexao.Close();
The error occurred in D:HostingkpstoolinsertPage.cfm: line 132 130 : datasource= "kpstool_accesscf_jobs"> 131 : INSERT INTO Jobs (Position, Needs, Necessary) 132 : VALUES ('#form.Position#','#form.Needs#','#form.Necessary#') 133 : </cfquery> 134 : <cfoutput>
This is the code for the form that this code is referring to: <form action="insertpage.cfm" method="post" name="Form" id="Form"> Position: <input type="text" name="Position" size="25" maxlength="25"> <br> <input type="hidden" name="Position_required" value="You must enter position"> <br> Needs: <input type="text" name="Needs" size="25" maxlength="25"> <br> Necessary:
Hello. I'm working with SQL Server 2005 Standard edition. I have a Java program that loads PDF files into the database. I have a table called T08_entity which, among others, has two IMAGE columns. The first Image column is for the original PDF file. The second one is for the PDF file with modified permissions (printing, saving, etc). This is made using the i-text library. The programs looks for the content of a disk folder, reads the contents of the folder, and inserts, one by one, the pdf files (besides other fields, like the name of the file, and ID, etc... but these are varchar or int fields. No problem with these. When the folder has only small files (smaller that 7-8 mb), it loads them without any problem into the database. But when the folder has bigger files (>10mb, more or less...) I get an OUT OF MEMORY error. I'm using the latest sqljdbc.jar driver (v1.2.2727). My server computer has only 1GB of RAM... but I've read that this latest driver can load big amounts of binary data using the connection property "responseBuffering=adaptive". Here is a sample of my code (at least the most relevant lines):
This is my connection code:
public String getConnectionUrl(){ return "jdbc:sqlserver://"+serverName+":"+portNumber+";databaseName=" +databaseName+";responseBuffering=adaptive;selectMethod=cursor"; }
public java.sql.Connection getConnection(){ try{
...
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password); if (con.getAutoCommit()) { con.setAutoCommit(false); } ... } catch(Exception e){ System.out.println("etc, etc..."); } return con; }
The following is a loop where each loop represents a file in the folder:
...And this is the insertDirectory procedure which inserts every file: the pdffile and pdffilenoperm are the IMAGE columns. The rest are varchar or int columns:
public void insertDirectorio(File archivo) {...if (archivo.isFile()){ pstmt =con.prepareStatement("INSERT INTO temp_carga "+ "(directory, name, dir_sup, filetype, pfilesize,pdffile,pdffilenoperm)"+ " values (?,?,?,?,?,?,?)"); }... long tamano = archivo.length();
//INSERTS ORIGINAL FILE................ int fileLength = Integer.MIN_VALUE; is = new FileInputStream(pdffile); fileLength= (int) pdffile.length(); pstmt.setBinaryStream(6, is, fileLength);
//INSERTS FILE WITHOUT PERMISSIONS (THIS PART OF THE CODE IS LONG AND IRRELEVANT, IT JUST USES THE ITEXT LIBRARY TO MODIFY THE PDF FILE. AT THE END, I HAVE THE FILE IN AN OUTPUT STREAM, AS SHOWN HERE:) ByteArrayInputStream inputnoimp = new ByteArrayInputStream(outnoimp.toByteArray()); pstmt.setBinaryStream(7,inputnoimp,(int)outnoimp.size()); } catch(Exception e) { err = e.toString(); } } pstmt.executeUpdate(); con.commit(); pstmt.close(); this.closeConnection(); }catch(java.sql.SQLException e) { err = e.toString(); } }
Well, as I said, when I run the program, when it reads smaller files, there's no problem. But when it gets a big file, I get the OUT OF MEMORY error. I have another application that reads pdf files ONE AT A TIME, using a code very much like this one, and it reads big files (>30mb) with no problems. The problems is with this one. Any help will be appreciated. If you have any question to clarify the problem, just tell me. Thanks in advance. Eric.
Hello All,i am trying to insert some values into a table in sql database. i keep getting error saying incorrect syntax near 'S'. i fired up my debugger and found that one of the row contains name like Georgia's way. i am getting error at the "S" in georgia's way. how can i fix that. here is my code for inserting the values in to the table SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr1 in objDataSet.Tables[0].Rows) { String sqlinsert = String.Format("insert into Det values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8},'{9}','{10}','{11}','{12}')" , dr1[0].ToString() , dr1[1].ToString() , dr1[2].ToString() , dr1[3].ToString() -- this is the column where georgia's way is , dr1[4].ToString() , dr1[5].ToString() , dr1[6].ToString() , Convert.ToDecimal(dr1[7].ToString()) , Convert.ToDecimal(dr1[8].ToString()) , dr1[9].ToString() , dr1[10].ToString() , dr1[11].ToString() , dr1[12].ToString());
new SqlCommand(sqlinsert, mysqlcon).ExecuteNonQuery(); LabelImport.Text = " Row Inserted"; } mysqlcon.Close(); can some please help me out.Thanks a lot
i use this statement to insert into sql tablecmdInsert = New SqlCommand("insert into empbill ([deptcode],[personNo,[entrydate]] ) values(" & Val(eno.Text) & "," & Val(TextBox5.Text) & ", " & dd.Text & "')", db) cmdInsert.ExecuteNonQuery() dd.Text value is 22/5/2008 it come from datetime calender the entrydate field datatype is datetime i found data always 1/1/1900 00:00:00 how i enter my date
Hi, Im struggling with this insert statement, I want to use with a AJAX validation Post Form page. Its quite straght forward, if a search query returns null the insert these values. The search query does work, what I mean by that is that txt field values seem to pass for search but not insert. Any help out there cheers Paul if (RowCount == 0) {String strSQL = "INSERT INTO Mail_List (FirstName, Email) VALUES( @FirstName, @Email )";
try {mySqlConn = new SqlConnection(strSqlConn); mySqlConn.Open();SqlCommand cmd = new SqlCommand(); cmd = new SqlCommand(strSQL, mySqlConn);cmd.Parameters.AddWithValue("@FirstName", Request.Form["FirstName"]);cmd.Parameters.AddWithValue("@Email", Request.Form["Email"]); cmd.ExecuteNonQuery(); lblStatus.Text = "Registration Successful"; }
I would like to know what options I have with regards to trapping a duplicate record before it tries to post to a SQL database. I have set the column to unique in SQL. But when I try to use ASP and post a duplicate record I get a system error. I would like to just create a referential error to notify the user that they cannot post a duplicate record please try again. Any help would be greatly appreciated.
I've just noticed some strange behavior that seems like a bug to me. It's much easier to follow an example of it that to outright explain it, so here goes.
I have a table defined with a NOT NULL constraint on a column and a default clause: -- DROP TABLE TestTable CREATE TABLE TestTable ( TestField0 varchar(10), TestField1 varchar(10) NOT NULL DEFAULT ('a') )
I have a view defined on the table, in this example case, the view just mirrors the table one to one: -- DROP VIEW TestView CREATE VIEW TestView as SELECT TestField0, TestField1 FROM TestTable
So far so good, if I run this statement, it works as I would expect and inserts the value and the default goes into the other field: INSERT INTO TestView (TestField0) SELECT 'test'
Now... If I add an INSTEAD OF trigger to the view, and have it perform the insert for me, I get an error with the same insert stmt: -- DROP TRIGGER TestTrigger CREATE TRIGGER TestTrigger ON TestView INSTEAD OF INSERT AS BEGIN INSERT INTO TestTable (TestField0, TestField1) SELECT TestField0, COALESCE(TestField1, 'X') FROM inserted END
Notice the trigger will ensure that a null value cannot be inserted into TestField1. If I run this insert stmt though I get an error: INSERT INTO TestView (TestField0) SELECT 'test'
Server: Msg 233, Level 16, State 2, Line 1 The column 'TestField1' in table 'TestView' cannot be null.
I have written following SQL query, this creates temporary table, inserts rows into it. I need to create VIEW "vw_NumberOfAttachments" in the database. I initially created table using "CREATE TABLE" but then i got error as VIEW can not be filled by temporary table. Hence I am using DECLARE TABLE -------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE sp_GetViewNumberOfAttachments -- Add the parameters for the stored procedure here
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @emailMessageID bigint DECLARE @metaDataStorageID bigint DECLARE @numberOfAttachments int
DECLARE ATTACHMENT_CURSOR CURSOR FOR SELECT emailMessageID, metaDataStorageID FROM ppaEmailMessage WHERE hasAttachments='true'
OPEN ATTACHMENT_CURSOR FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID
WHILE @@FETCH_STATUS = 0 BEGIN -- here the table name need to get dynamically the name of the attachment table -- for a moment it is written as ppaMsOfficeDoc, but that should change dynamically set @numberOfAttachments = (SELECT count(*) FROM ppaMsOfficeDoc WHERE metaDataStorageID = @metaDataStorageID)
INSERT INTO @AttachmentDetails(emailMessageID, metaDataStorageID, numberOfAttachments) VALUES (@emailMessageID, @metaDataStorageID, @numberOfAttachments)
FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID END
CLOSE ATTACHMENT_CURSOR DEALLOCATE ATTACHMENT_CURSOR
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vw_NumberOfAttachments') DROP VIEW vw_NumberOfAttachments GO CREATE VIEW vw_NumberOfAttachments AS SELECT @AttachmentDetails.emailMessageID, @AttachmentDetails.metaDataStorageID, @AttachmentDetails.numberOfAttachments FROM @AttachmentDetails GO END GO
----------------------
I am getting following errors: ----------- Msg 102, Level 15, State 1, Procedure sp_GetViewNumberOfAttachments, Line 57 Incorrect syntax near 'vw_NumberOfAttachments'. Msg 137, Level 15, State 2, Procedure vw_NumberOfAttachments, Line 3 Must declare the scalar variable "@AttachmentDetails". Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'END'. ----------- Can anyone please suggest whats wrong in there? Many thanks
Hi guys, I have created 3 tables namely Static, Dynamic and Demo... Static has colums FormID(uniqueidentifier, PrimaryKey) and FormName(Nvarchar(50)). Dynamic has colums formID(uniqueidentifier,PrimaryKey) and FormName(Nvarchar(50)). Demo has 4 colums namely ValueID(uniqueidentifier, Primary Key), formID(uniqueidentifier, foreign key), Name(nvarchar(50)), Value(nvarchar(50).
Now the formID coloum in Demo table i have set as foreign key to both the dynamic table as well as static table on the formID colum in both table.
Now first i insert a row in the dynamic table then take the uniqueidentifier which is generated automatically and try to insert in the Demo table in formID colum as that colum is FK to dynamic and static but when i try to insert a row it show that its violating the FOREIGN KEY CONSTRAINT
the exact error is
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Demo_Dynamic1". The conflict occurred in database "huzefaJTest", table "dbo.Dynamic", column 'formid'.
My time dimension has date mm/dd/yy 00:00:00 where as the source has mm/dd/yy and some time not 00:00:00 I am sure the iserts in fact table are failing. I do not want the time part to come anywhere in data mart. what should i do in SSIS.
string fileName = "d:\shiporder.xml"; DataSet dataSet = new DataSet(); dataSet.ReadXML(fileName); //connection string string cmd = "INSERT INTO Orders (OrderID, OrderPerson) VALUES (dataSet.Tables[0].Row[0][0].ToString(),dataSet.Tables[0].Row[0][1].ToString); SqlConnection con = new SqlConnection(conection string); SqlCommand mycmd = new SqlCommand(cmd,con); con.Open(); mycmd.ExecuteNonQuery(); con.Close(); it gives me this error dataSet.Tables its not permitted in this context. valid expression are constants, constant expression, . Columns name are not permitted
I have a database with columnsuserOwnListsuserID uniqueidentifieruserName nvarchar100userList nvrachar100createdDateI have created successfully a gridview controller to edit these values in database. The Gridview data is populated by SqlDataSource.I have also created a EmptyDataTemplate and created a form into it. There is only one textBox and submit button to create the First entry to userOwnLists -table.Now I collect the value from EmptyDataTemplate textbox with id userList1 and create a codebehind logic for the submitbutton.protected void Button2_Click(object sender, EventArgs e) { TextBox listName = (TextBox)this.FindControl("listName1", GridView1.Controls);SqlDataSource1.InsertParameters["userId"].DefaultValue = Membership.GetUser().ProviderUserKey; SqlDataSource1.InsertParameters["userName"].DefaultValue = Membership.GetUser().UserName.ToString(); SqlDataSource1.InsertParameters["listName"].DefaultValue = listName.Text; SqlDataSource1.InsertParameters["createdDate"].DefaultValue = DateTime.Now.ToString(); SqlDataSource1.Insert(); }The problem is now that I get error: Exception Details: System.Data.SqlClient.SqlException: Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.OK. So I Googled a bit and found this:http://scottonwriting.net/sowblog/posts/4690.aspxMy Question is: How do I convert userId so I can insert it to database successfully?This does not work:String userId = Membership.GetUser().ProviderUserKey.ToString(); SqlDataSource1.InsertParameters["userId"].DefaultValue = Convert.ToString(userId);
i currently have more tha 3 million of records in my table in sql 7. i am getting timeout error in my web application when i try to insert a record in that table
what could be rhe reason for this? how to avoid this type of problem?
Hi all,I am getting this error when insert values from one table to another inthe first table the values are varchar (10). In the second they aredatetime. The format of the data is mm/dd/yyyy to be easily convertedto dates. The conversion in this case is implicit as indicated in SQLServer documentation. Here is my query:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionUNIONSELECT program_id + 100000, program_name, start_date, end_date,campaign_id AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.programsUNIONSELECT execution_id + 200000, execution_name, start_date,end_date, program_id + 100000 AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.executionsUNIONSELECT wave_id + 300000, wave_name, start_date, end_date,mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL ASListIDFROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executionsWHERE mysqlfactiva.dbo.waves.execution_id =mysqlfactiva.dbo.executions.execution_idI am referencing programs table two times. If I just select this all Iget all data I need. When doing insert I get a message:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. The statement has been terminated.If I execute just first part of the query before first union, I insertdata fine:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionAs soon as I use union I get the above error. This is very strangesince even when I execute the query using first union where the datescome from the same table 'programs' I get the error. Why I can insertfrom programs first time and can's second time?Any help will be appreciated.Thanks,Stan
Inside a single transaction I'm inserting/updating multiple records into multiple tables, in this order: table 1 record 1 table 2 record 1 table 3 record 1 table 1 record 2 table 2 record 1 table 1 record 3 table 2 record 3 table 3 record 3
Now I'm getting an unspecified error on a certain table:
Indicates a data modification, such as an insert, an update, or a deletion. Ensures that multiple updates cannot be made to the same resource at the same time. (I assume that multiple updates within the SAME transaction can be made, only multiple updates from different transaction cannot be made, right?) I cannot find any reference to this error message and don't know what the numbers mean. Maybe it relates to data that can be found in the sys.lock_information table like explained here, http://technet.microsoft.com/en-us/library/ms172932.aspx, but I'm not sure.
Furthermore, the sys.lock_information table is empty. I haven't been able to reproduce the problem myself. I only received an error log and the database to investigate it.
So, does anybody have an idea what this error message means and what I can do to troubleshoot this?
Hi, Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error : Must declare the scalar variable "@BookMarkArrayA". when it reaches the .insert command, I've tried using a local variable temp in place of the array element and .ToString , but still get the same error This is the code : Public Sub NewCustomer() Dim temp As String = " " Dim ID As Integer = 1 'Restore the array from the view state BookMarkArrayA = Me.ViewState("BookMarkArrayA")
temp = BookMarkArrayA(6) Dim Customer As SqlDataSource = New SqlDataSource()
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
( NAME_COL varchar(64), ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest