Prepared Statement Doesn't Insert String
Jul 30, 2007
Hi,
I am facing a problem while using pepared statement for inserting data in MS-Access db.
Code Snippet
private const String CONN_STR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\TestDb.mdb";
conDatabase.Open(CONN_STR, "", "", -1);
String sQuery = "INSERT INTO " + sTableName +
" ([FileName],[IsCopied],[Size]) VALUES (@FileName,@IsCopied,@Size)";
cmdPrepStmnt.ActiveConnection = conDatabase;
cmdPrepStmnt.CommandText = sQuery;
cmdPrepStmnt.CommandType = CommandTypeEnum.adCmdText;
cmdPrepStmnt.Prepared = true;
int iCount = 0;
foreach (FileInfo f in fInfo)
{
Console.WriteLine("Adding File " + ++iCount + " of " + fInfo.Length + "; " + f.Name);
Parameter p1 = cmdPrepStmnt.CreateParameter("@FileName", ADODB.DataTypeEnum.adVarWChar, ParameterDirectionEnum.adParamInput, 255, f.FullName);
Parameter p3 = cmdPrepStmnt.CreateParameter("@IsCopied", ADODB.DataTypeEnum.adBoolean, ParameterDirectionEnum.adParamInput, 10, false);
Parameter p5 = cmdPrepStmnt.CreateParameter("@Size", ADODB.DataTypeEnum.adInteger, ParameterDirectionEnum.adParamInput, 10, f.Length);
cmdPrepStmnt.Parameters.Append(p1);
cmdPrepStmnt.Parameters.Append(p3);
cmdPrepStmnt.Parameters.Append(p5);
cmdPrepStmnt.Execute(out objAffected, ref obj, 0);
}
Code Snippet
FileName is coming as blank in db, while in debug mode i can see that it is being set with a proper filename. The other 2 params are inserting fine.
The FileName field is set as Text field with width length 255, and the incoming data is always shorter than 255 chars. The insertion happens without any errors/exceptions. I have also tried replacing the data for filename with a string like "Test", still it is inserting blank string.
Please help.
View 3 Replies
ADVERTISEMENT
Nov 18, 2005
Hi!
If I'm running a DTS with 2 connections(flat file source and SQL table destination) and a Data Driven Query Task, and then I get an error message saying "Stataments sould not be prepared. Line 2: Invalid syntax near "."
Which code should I look for the error? The message says Line 2 but i don't know where to look.
Thanks.
View 3 Replies
View Related
Jun 2, 2006
Hi,I'm running SQL Profiler on an SQL Server 2000 database. I see that onestored procedure gets repeatedly executed having a handle of '1'. Thisquery takes a long time to complete.How do I find what the text of the stored procedure is? I cant see anyhandle being created (using sp_prepare) with an id of '1' in theprofiler. Is there any way to force the server to re-prepare allstatements so that I can see the statement text and its preparation inSQL Profiler?Cheers,Birju
View 3 Replies
View Related
Jul 20, 2005
Hello,I have a table which contains some nullable columns. I want to write asingle query, which can be prepared (ie. prepared statement), that canhandle null or non-null values for the where clause. Is this possiblein a standard-conforming manner?The simple for of the query is this:SELECT * FROM <table> WHERE <column> = <value>But when the value to be matched on is NULL, the syntax of the querymust change to be:SELECT * FROM <table> WHERE <column> IS <value>In the second case <value> is NULL.I know one option might be to change the ANSI NULL handling option,but I am loathe to do this (I have five RDBMS's to support, not justMSSSQL).I thought I might have been able to cheat using an IN clause to makethe SQL consistent, but no luck.ThanksKevin
View 6 Replies
View Related
Nov 3, 2002
I have an application which uses ODBC to insert, update, delete records in an MSSQL DB. The application uses prepared statements and keeps the odbc statement handles to execute the same statement multiple times.
This application works fine until triggers are introduced. The triggers are pretty simple and take the inserted, updated record and insert this information into another table.
If we change the application to not re-use the prepared statement handle, by closing the statement handle after each execute, it also works fine. (However, this results in bad performance.)
The error message is: "[ODBC SQL Server Driver]Connection is busy with results for another hstmt". If anyone has any information on this, I would greatly appreciate the help!!!
Thank you.
Christine
View 1 Replies
View Related
Jan 25, 2008
Hello Folks,
I have a problem with a prepared statement against SS2005 SP2 that I do not fully understand. I have a query that is prepared and executed from a java application that looks similar to this:
SELECT TOP 1 str.ID FROM bigtable1 big1
LEFT OUTER JOIN bigtable2 big2
ON (big1.ID = big2.ID)
LEFT OUTER JOIN htable h ON (big2.HID = h.HID AND h.DIRECTSW=1)
WHERE big1.ID IN (SELECT ID FROM ltable WHERE LID IN(SELECT
LID FROM LAS WHERE LASID = ? )) AND big2.COMPLID <> 2 AND big2.COMPLID <> 3
AND ISNULL(big2.date1, big2.date2) BETWEEN ? AND ?
When run as a prepared statement this runs in about 27 seconds. When run under the query component of Management Studio it runs in about 1 second. Before each run I am clearing the buffers and the prepared statement cache. When running these I have looked at the execution plan from profiler and they both look the same. If I change the
WHERE big1.ID IN (SELECT ID FROM ltable WHERE LID IN(SELECT
LID FROM LAS WHERE LASID = ? ))
to INNER JOIN it runs ~1 second in both environments.
Any pointers would be welcome to explain the disrepency.
Thanks, Mark
View 4 Replies
View Related
Oct 8, 2007
Hi all,
I recieve an error when I use the Lookup component in SSIS that reads:
Statement(s) could not be prepared.
I'm using a SQL 2005 DB as the source which runs into a lookup table and is use to compare records with an SQL 2000 Database. I've created connection managers successfully to both these databases. When trying to use the results of an SQL Query for the lookup to the SQL 2000 database (which is a linked server) and I try to map the columns, the error pops up and exits out of the lookup properties Window
The details to the error read:
Program Location:
at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.PrepareSQLStatement(String sql, Boolean bypassPrepare)
at Microsoft.DataTransformationServices.Design.DtsConnectionCommonControl.CheckSqlQuery()
I'm looking to use the results of this comparison to output in some form of a report. Ideas would be greatly appreciated!
View 36 Replies
View Related
Nov 10, 2007
Prepared statement '(@CORP_NAME varchar(150),@REP_NAME varchar(150),@REP_TC_NO varch' expects parameter @CORP_NAME, which was not supplied
I know this is a classical error and I searched through the forum but I could no solve it. I am sure that I defined @CORP_NAME, but it says you did not. My code is below,please help me...
private void Submit1_ServerClick(object sender, System.EventArgs e)
{
erkaner = Page.Session.Contents["CORP_ID"].ToString();
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox32.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox27.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TC_NO", System.Data.SqlDbType.VarChar, 50, TextBox28.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ID", System.Data.SqlDbType.Int, 4, erkaner));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_EMAIL", System.Data.SqlDbType.VarChar, 50, TextBox29.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TEL", System.Data.SqlDbType.VarChar, 50, TextBox30.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_DUTY", System.Data.SqlDbType.VarChar, 150, TextBox31.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FOUND_YEAR", System.Data.SqlDbType.VarChar, 50, TextBox33.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@COUNTRY", System.Data.SqlDbType.VarChar, 50, DropDownListUlke.SelectedValue.ToString()));
if (DropDownListUlke.SelectedIndex == 148)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, DropdownlistSehir.SelectedValue.ToString()));else if(DropDownListUlke.SelectedIndex != 0 && DropDownListUlke.SelectedIndex != 148)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, TextBox1.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SECTOR", System.Data.SqlDbType.VarChar, 150, RadioButtonList6.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMP_NUM", System.Data.SqlDbType.VarChar, 50, RadioButtonList1.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ACT", System.Data.SqlDbType.VarChar, 150, DropDownList3.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PC_NUM", System.Data.SqlDbType.VarChar, 50, TextBox35.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_USER_NUM", System.Data.SqlDbType.VarChar, 50, "INT_USER_NUM"));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_LAN", System.Data.SqlDbType.VarChar, 50, RadioButtonList3.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_SERVER", System.Data.SqlDbType.VarChar, 50, RadioButtonList4.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ECOM_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox37.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@OT_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox38.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_CON_TYPE", System.Data.SqlDbType.VarChar, 50, RadioButtonList5.SelectedValue.ToString()));
if (CheckBox1.Checked)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "Ticari Uygulamalar (B2B,B2C,e-iş,e-ihracat,…)"));else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox2.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "Yönetişim Uygulamaları (Muhasebe, stok, satış, kalite, raporlama,denetim...)"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox3.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "ERP Uygulamalari(Unity, SAP, JDE, BAAN, QAD vb.)"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox4.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "Müşteri İlişkileri Yönetimi (CRM) Uygulamaları"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox5.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "Bilgisayar Destekli Tasarım / Bilgisayar Destekli Üretim (CAD/CAM) Uygulamaları"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox6.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "Veri Ambarı ve Veri Madenciliği Uygulamaları "));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox7.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, TextBox36.Text));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, ""));
sqlUpdateCommand1.CommandText = "UPDATE dbo.CORP_TBL SET REP_NAME = @REP_NAME, CORP_NAME = @CORP_NAME, REP_TC_NO =" +
" @REP_TC_NO, REP_EMAIL = @REP_EMAIL, REP_TEL = @REP_TEL, REP_DUTY = @REP_DUTY, F" +
"OUND_YEAR = @FOUND_YEAR, COUNTRY = @COUNTRY, CITY = @CITY, SECTOR = @SECTOR, EMP" +
"_NUM = @EMP_NUM, CORP_ACT = @CORP_ACT, PC_NUM = @PC_NUM, INT_USER_NUM = @INT_USE" +
"R_NUM, HAVE_LAN = @HAVE_LAN, HAVE_SERVER = @HAVE_SERVER, ECOM_SER_NUM = @ECOM_SE" +
"R_NUM, OT_SER_NUM = @OT_SER_NUM, INT_CON_TYPE = @INT_CON_TYPE, APPLICATION1 = @A" +
"PPLICATION1, APPLICATION2 = @APPLICATION2, APPLICATION3 = @APPLICATION3, APPLICA" +
"TION4 = @APPLICATION4, APPLICATION5 = @APPLICATION5, APPLICATION6 = @APPLICATION" +
"6, APPLICATION7 = @APPLICATION7 WHERE CORP_ID = @CORP_ID";
sqlUpdateCommand1.Connection = sqlConnection1;
sqlUpdateCommand1.Connection.Open();
sqlUpdateCommand1.ExecuteNonQuery(); //This line gives the errorstring mySqlQuery = "UPDATE CORP_TBL SET FLAG = 1 WHERE CORP_ID=" + Page.Session.Contents["CORP_ID"].ToString();SqlCommand myCommand = new SqlCommand(mySqlQuery, sqlConnection1);
myCommand.ExecuteNonQuery();
Page.Response.Redirect("main.aspx");
}
View 10 Replies
View Related
May 1, 2006
I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++.
I am not using / importing any dlls.
I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery.
I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed.
First time when I bind paramaters using SQLBindParameter it works perfect.
When loop gets executed secondtime onwards, it gives an error.
SQLState: 24000 [ODBC Client Interface]Invalid cursor state.
If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error.
I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time.
Have I missed any step?
Where & when I should code the cursor type? Any specific libraries I need to link?
Thanks
View 2 Replies
View Related
Mar 5, 2004
Hello,
I am trying to get my mobilink aplication to synchronize with MS SQL Server 2000.
After getting rid of the global variable references I was using from ASA that the synchonization was failing on, it is now failing on the 8180 error.
I am not sure what it is not liking. I set the begin connection script to be blank as before it had "CREATE VARIABLE @UserID VARCHAR ( 128 )", as I found no easy way to delete the script sets with ISQL.
Microsoft says it has a hotfix for this particular error code, but I am not interested in calling their tech support line and paying to get it.
Any ideas?
The log from the Mobilink server says:
I. 03/05 11:24:07. <thread 1.4>: Working on a request
I. 03/05 11:24:07. <thread 1.4>: Synchronization request from: JM (version: 50data)
I. 03/05 11:24:07. <1.4> [JM]: Warning: [10050] ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'busler'. (ODBC State = 01000, Native error code = 5701)
I. 03/05 11:24:07. <1.4> [JM]: Warning: [10050] ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (ODBC State = 01000, Native error code = 5703)
I. 03/05 11:24:07. <1.4> [JM]: ODBC isolation set to: Read Committed
I. 03/05 11:24:08. <1.4> [JM]: COMMIT Transaction: begin_connection
E. 03/05 11:24:08. <1.4> [JM]: Error: ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (ODBC State = 42000, Native error code = 8180)
I. 03/05 11:24:08. <1.4> [JM]: Error Context:
User Name: JM
Modified User Name: JM
Transaction: begin_synchronization
Table Name: NULL
I. 03/05 11:24:08. <1.4> [JM]: Script Version: 50data
Script:
End of Error Context
I. 03/05 11:24:08. <1.4> [JM]: Warning: [10010] No error-handling script is defined. The default action code (3000) will decide error behaviour.
I. 03/05 11:24:08. <1.4> [JM]: ROLLBACK Transaction: begin_synchronization
I. 03/05 11:24:08. <1.4> [JM]: COMMIT Transaction: end_connection
I. 03/05 11:24:08. <1.4> [JM]: Disconnected from consolidated database
I. 03/05 11:24:08. <1.4> [JM]: Synchronization failed
View 1 Replies
View Related
Mar 22, 2006
I have two tables:tb_news---story_id-productlisttb_lookup---story_id-productwhen an insert command is run on tb_news, productlist field ispopulated with a value such as 'abc, def, de'when this happens, i need tb_lookup to be populated with seperaterecords for each product in productlist and the story_id from tb_news.Example:INSERT INTO tb_news (story_id, product_list)VALUES (12345, 'abc, def, de')Results:tb_news--12345, 'abc, def, de'tb_lookup--12345, 'abc'12345, 'def'12345, 'de'Ideally, I would like this to use recursion and give me tha ability tochange the delimiter at any time (might not always be a comma). someproducts may have a period in them. number of products is unknown andmight be 0 (field may be empty or NULL).
View 2 Replies
View Related
Feb 1, 2007
I am using PowerDesigner to reverse engineer a SQL 2000 database. I am getting the following error. I can't find out what this error means. Does anyone know?
Unable to list the users. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. SQLSTATE =37000
View 1 Replies
View Related
Aug 29, 2006
Can anyone please tell me why this query works:
DECLARE @valuation_date smalldatetime, @non_admit_date smalldatetime
SET @valuation_date = '2006-03-31'
SET @non_admit_date = DATEADD(dd, -90, @valuation_date)
SELECT SUM(Premium) FROM Reserves WHERE Reserve_Paid_to_Date >= @non_admit_date
And this one does not:
DECLARE @valuation_date smalldatetime, @non_admit_date smalldatetime, @arc_sql nvarchar(4000)
SET @valuation_date = '2006-03-31'
SET @non_admit_date = DATEADD(dd, -90, @valuation_date)
SET @arc_sql = 'INSERT INTO Results SELECT SUM(Premium) FROM Reserves WHERE Reserve_Paid_to_Date >= ' @non_admit_date
SELECT @arc_sql
exec sp_executesql @arc_sql
The second one gives me this error: "Syntax error converting character string to smalldatetime data type."
How can I run the second one? I need to dynamically build an Insert statement and this is stopping me.
Thanks!
-Ticia
View 3 Replies
View Related
Mar 15, 2007
I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery)
This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.
If I change the query to be a normal select it works. For example:
select * from tblEmample
That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
View 1 Replies
View Related
Mar 13, 2005
Hi all,
Recently i have installed (winxp sp2) and i have encountered with some
problems, for example i always use the following Connection string for
connecting to SqlServer 2000 and work with it:
"Server=localhost;database=Test;Trusted_Connection=yes"
But now this string doesn't work and after some minutes i found out the solution as follows:
"Server=MYPCNAME;database=Test;Trusted_Connection=yes"
or
"Server=(local);database=Test;Trusted_Connection=yes"
or
"Server=.;database=Test;Trusted_Connection=yes"
(Note that my computer name really is "MYPCNAME")
Between three above solutions i want to know why "Server=MYPCNAME" work properly
BUT "Server=localhost" doesn't work after installing WinXP SP2.
Thanks in advanced.
View 1 Replies
View Related
Jun 4, 2007
hi,
i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
Any solutions????
View 4 Replies
View Related
Sep 21, 2015
I put this series of select statements to verify that the BETWEEN statement is working properly. I should always get “Between” below.
SELECT
CASEWHEN'1/1/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
SELECT
CASEWHEN'1/31/15'BETWEEN'1/1/15'AND'1/31/15'THEN'Between'ELSE'Not
Between'END
[Code] .....
View 4 Replies
View Related
Jul 20, 2005
Hi,I have a database stored in MS SQL 2000 and an application written inVB5, which connects the database via JET/ODBC.I have a problem with the UNION statement.When I run a simple query like:"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"I get the following error:"Runtime error 3078 - The Microsoft Jet database engine cannot find theinput table or query 'select field1 from table1'. Make sure it existsand that its name is spelled correctly."I can run the queries separately "SELECT field1 FROM table1" and "SELECTfield1 FROM table2", so that I'm sure table and field names are correctand I have permission to access them.Both field1 and field2 are the same type (int).If I run the query in MS SQL Query Analyzer, it works fine.It doesn't work only when I run it from VB/JET/ODBC.Has anyone already had this kind of problem?Any help will be highly appreciated!Thank you so much for the attention.--Posted via http://dbforums.com
View 1 Replies
View Related
Nov 14, 2005
Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case
select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"
If I remove the last 1 from the search string i get lot of records, Can anybody help me out.
View 2 Replies
View Related
Mar 9, 2008
Hi, I have the following query:UPDATE lista SET audio='3847f5e9-4ef7-42d7-9e57-e5cbad9131b1.jpg' WHERE id='13';If the id already exists it'll modify the row correctly as expected. But if the id doesn't exist at the table I want the row to be inserted anyway, but this is not happening. The same query works well in mysql + php. The table has an identity increment of 1 for the primary key also. Any ideas?Thanks in advance.
View 1 Replies
View Related
Nov 10, 2007
Hi
I sent a long string of ID from front end to my stored procedure...till now I was using varchar(8000)...but if the string crossess that limit it is breaking.
If I try to use text datatype..It doesn't support rtrim, stuff functions etc...
So could any one suggest me a best way to save a long string without any restriction of size...
My front end is C#.Net and Back End is SQL SERVER 2000
Thanks in advance
View 1 Replies
View Related
Jun 4, 2007
Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430'
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha
View 2 Replies
View Related
Oct 24, 2007
I have an T-SQL statement that runs in under 4 minutes on the Management Studio, and freezes forever in Integration Services. The statement ran fine before in both places, as well, but now I'm running on x64 OS with everything 64 bits including SQL.
The statement is this:
DECLARE @data datetime
set @data = DATEADD(DAY,-1,DATEADD(YEAR,-1, GETDATE()))
INSERT INTO TempDateModelo (Data) (SELECT Data FROM [tPosting Date] a WHERE a.Data>=@data)
INSERT INTO fModelo([Item Department], [Centro Custo], [Posting Date]) (Select b.[Item Department], c.[Centro Custo], d.[Data] FROM [mItem Department] b, [mCentrosCusto] c, [TempDateModelo] d)
UPDATE fModelo SET [Vendas MM Anterior]=
(SELECT SUM(b.[Custo Vendas]) FROM fValues b WHERE (b.[Posting Date] BETWEEN DATEADD(YEAR,-1,DATEADD(DAY,-27,fModelo.[Posting Date])) AND DATEADD(YEAR,-1,fModelo.[Posting Date])) AND b.[Item Department]=fModelo.[Item Department] AND b.[Centro Custo]=fModelo.[Centro Custo])
UPDATE fModelo SET [Vendas MM Posterior]=
(SELECT SUM(b.[Custo Vendas]) FROM fValues b WHERE (b.[Posting Date] BETWEEN DATEADD(YEAR,-1,fModelo.[Posting Date]) AND DATEADD(YEAR,-1,DATEADD(DAY,27,fModelo.[Posting Date]))) AND b.[Item Department]=fModelo.[Item Department] AND b.[Centro Custo]=fModelo.[Centro Custo])
UPDATE fModelo SET Dia = DATEPART(DAY, [Posting Date]), Mes = MONTH([Posting Date])
View 2 Replies
View Related
Nov 9, 2015
Primary platofrm: Sql12k, 7.0 Ultimate Pro OS
I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
View 4 Replies
View Related
Apr 13, 2006
I want to insert values from the querystring but nothing happens with this code (the sp works great from the Query Analyzer):
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"InsertCommand="spSearch_row_insert" InsertCommandType="StoredProcedure">
<InsertParameters>
<asp:QueryStringParameter Name="CustomerID" QueryStringField="1" DefaultValue="1" Type="String" />
<asp:QueryStringParameter Name="SearchID" QueryStringField="2" DefaultValue="1" Type="String"/>
<asp:QueryStringParameter Name="SearchDate" QueryStringField="3" DefaultValue="1" Type="String" />
<asp:QueryStringParameter Name="IP" QueryStringField="4" DefaultValue="1" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</form>
I'm very grateful for help!// G
View 3 Replies
View Related
Jul 31, 2015
I have a Bulk insert that doesn't load but doesn't error,
SET @SQL= 'BULK INSERT dbo.LexisNexis_import_BANKRUPTCY FROM ''' + @ImportFile + ''' WITH (FIRSTROW = 2, FORMATFILE = ''' + @FormatFilePath + ''' )'
EXEC(@SQL)
All columns in the csv are double quoted so I stip them out in a format file.There is data in the source file. Why this Isn't working?
View 4 Replies
View Related
Jan 4, 2008
Hi,
I'm trying to transfer data between two 2005 databases using the import/export method. I set the Enable Identity insert to true - edit mappings option - on all the tables but I still get the ID set back to 1. I want to add some live data to an altered database structure, and I'm assuming the import/export tool is the way to do this.
I'm using sql server management studio, SP1
Thanks, this is becoming really frustrating.
Peter Cat
View 6 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Sep 17, 2007
This is simple enough and I'm stumped. Why doesn't this work?
Code Snippet
use w
go
-- Create image warehouse
create table dbo.ImageWarehouse (
[ImageWarehouseID] int identity(1,1) primary key,
[ImageName] varchar(100),
Photo varbinary(max))
Go
-- Import image
Insert into dbo.ImageWarehouse
([ImageName]) values ('testingimage.gif')
update dbo.ImageWarehouse
set Photo =
(SELECT * FROM OPENROWSET(BULK 'c: estingimage.gif', SINGLE_BLOB)AS x )
WHERE [ImageName]='testingimage.jpg'
go
-- Check population
--delete from dbo.ImageWarehouse
select * from dbo.ImageWarehouse
go
-- Export image
declare @SQLcommand nvarchar(4000)
set @SQLcommand = 'bcp "SELECT top 1 Photo FROM w.dbo.ImageWarehouse WHERE ImageName = ''testingimage.gif''" queryout "c: estingimage_out.gif" -T -N -S [my server name]'
exec xp_cmdshell @SQLcommand
go
I successfully see
Code SnippetImageWarehouseID ImageName Photo
1 testingimage.gif 0x4749463839615802C (long hex string)
then
Code Snippet
output
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL
But testingimage_out.gif is unreadable. It's the same size (28k) as the original trestingimage.gif. Same result with a .jpg file. Try to open the file and it's just red X.
No SQL errors. Just result error. What am I overlooking?
View 4 Replies
View Related
Aug 28, 2015
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300
What I would like to see is a result like:
St. Louis 1000
Other Missouri City 2450
View 5 Replies
View Related
Feb 26, 2008
Hi,
I'm using MSSQL 2005 JDBC driver version 1.2 with MSSQL Server 2005. Getting parameter's metadata doesn't seem to be working properly there. PreparedStatement#getParameterMetaData() works fine if PreparedStatement is created for some SELECT but fails for any INSERT statements. The same code works fine with other DBs (I checked with HSQLdb and PostgreSQL). See sample below
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbcqlserver://mssql2005\mssql2005;DatabaseName=alexdtms;user=xxx;password=xxx";
// I don't care about non-closed statements, it's just sample code
Connection con = DriverManager.getConnection(connectionUrl);
con.createStatement().executeUpdate("CREATE Table Table1 (RECORD_ID varchar, TgtQty float)");
PreparedStatement ps = con.prepareStatement("INSERT INTO Table1 VALUES ( ?,? )");
ParameterMetaData pmd = ps.getParameterMetaData();// <-- !!!! Exception is thrown here
for (int i = 1, count = pmd.getParameterCount(); i <= count; i++)
{
System.err.println(">>>>AD_TEMP " + pmd.getParameterType(i) + " " + pmd.getParameterTypeName(i) + " " + pmd.getParameterMode(i));
}
Thanks,
Alexander Dolgin
View 1 Replies
View Related
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 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