|
Hi Joe,
You can use following query:
SELECT ISNULL( (SELECT File_Name
FROM Files
WHERE File_Name = @FileName1),1)
This would solve your problem, in case there is no result, it will return you 1 otherwise the matching result.
|
|
|
|
|
There are also a number of other columns for each row, I assume that I just populate the query with those separated by columns?
|
|
|
|
|
Yup 
SELECT Col1
,ISNULL(Col2, '1')
,Col3
,Col4
FROM SomeTable
WHERE SomeCondition
..or even nest another select, like this;
SELECT Col1
,ISNULL(Col2, '1')
,(SELECT 1) AS Col3
,Col4
FROM SomeTable
WHERE SomeCondition
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
SELECT isnull(File_Name,1) as FileName from tblname
WHERE (File_Name = @File_Name1)
|
|
|
|
|
Darn, you're right 
I was returning the value as a VARCHAR, not an INTEGER - well spotted.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
U can use following statment..
SELECT ISNULL(File_Name, '')
FROM Files
WHERE File_Name = @FileName1
because is is string so it should return blank
|
|
|
|
|
Try the following query.
SELECT CASE WHEN COUNT(File_Name)=0 THEN 1 ELSE COUNT(File_Name) END as FileName from tblname
WHERE (File_Name = @File_Name1)
|
|
|
|
|
if v want to store name like Mohammed's then sql didn't allow to save data and give an error
|
|
|
|
|
Only goes wrong if you concatenate strings, which shouldn't be done in the first place. Use a 'parameterized query';
using (cmd = new MySqlCommand("SELECT EmployeeName FROM Employees WHERE EmployeeId = @P1;"))
{
cmd.Parameters.AddWithValue("@P1", Parameter1);
m.ExecuteNonQuery();
}
Research the AddWithValue member.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
to store name like Mohammed
|
|
|
|
|
|
Hi ,
When i run this Stored Procedure I get this error :
Msg 102, Level 15, State 1, Line 1<br />
Incorrect syntax near 'Pages'.
AND my SP is :
declare @ModuleSettings NVarChar(max) = N'<Article type=''Pages''><Ucl name=''~/UserControls/ModuleSettings/ArticleModuleSettings.ascx''></Ucl><Election>0</Election><IsPaging>False</IsPaging><PageSize>5</PageSize><PagingLocation>Top</PagingLocation><Title>توليد کنندگان مواد اوليه / Suppliers of raw materials</Title><ThumbNail location=''3''>True</ThumbNail><ContentTitle>True</ContentTitle><ContentAbbr>True</ContentAbbr><Icon address=''''>False</Icon><Category>429</Category><All><item align="right" visible="true" order="1" block="true" navigateUrl="~/Management/Modules/Details/">Title</item><item align="right" visible="true" order="2" block="true">Abbr</item><item align="left" visible="true" order="4" block="true">CreateDate</item><item align="right" visible="false" order="5" block="false">Content</item><item align="right" visible="false" order="6" block="false">Keyword</item><item align="right" visible="false" order="7" block="false">Category</item><item align="right" visible="false" order="8" block="false">File</item><item align="right" visible="false" order="9" block="false">Icon</item><item align="right" visible="false" order="10" block="false">writer</item><item align="right" visible="false" order="11" block="false">Source</item></All></Article>'
declare @param nvarchar(max)= N'update [Management].[PageModules] set '
if 100 is not NULL
BEGIN
set @param = @param + '[AreaId]=''' + cast(100 as nvarchar(3)) + ''','
End
if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + @ModuleSettings + ''','
END
if 0 is not NULL
BEGIN
set @param = @param + '[Election]=''' + cast(0 as nvarchar(3)) + ''','
End
if 'sdfg' is not NULL
BEGIN
set @param = @param + '[Title]=''' + 'sdfg' + ''','
End
if 418 is not NULL
BEGIN
set @param = @param + '[CategoryId]=''' + cast(418 as nvarchar(17)) + ''','
End
if NULL is not NULL
BEGIN
set @param = @param + '[DepartmentId]=''' + cast(NULL as nvarchar(9)) + ''','
End
if 38 is not NULL
BEGIN
set @param = @param + '[CultureId]=''' + cast(38 as nvarchar(5)) + ''','
End
if 15 is not NULL
BEGIN
set @param = @param + '[ModuleId]=''' + cast(15 as nvarchar(5)) + ''','
End
if 1 is not NULL
BEGIN
set @param = @param + '[PageId]=''' + cast(1 as nvarchar(9)) + ''','
End
set @param= substring(@param,0,len(@param))
set @param = @param + ' Where Id=''' + cast( 68 as nvarchar(17)) + ''' '
exec sp_executesql @param
modified 6-Nov-12 4:38am.
|
|
|
|
|
Change this;
N'<Article type=''Pages''>
to this;
N'<Article type="Pages">
..and do the same for the "ucl name".
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Building a dynamic query in SQL, even inside a stored procedure, will not prevent SQL injection[^].
Your example could easily be rewritten as a simple UPDATE statement. (NB: Constant values like 100 are never null!) If any of the new values are passed in as parameters, then your query is open to SQL injection.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi
Please help ..
Solutions are not usefull!
but its SP and for example i fill info in that!
|
|
|
|
|
Hi,
I my Self solved the problem via this strategy
Change the bellow code :
if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + <code>@ModuleSettings</code> + ''','
END
With :
if @ModuleSettings is not NULL
BEGIN
set @param = @param + '[ModuleSettings]=''' + <code>replace (@ModuleSettings,CHAR(39),char(39)+CHAR(39))</code> + ''','
End
conclusion :
you should replace :
@ModuleSettings
with :
replace (@ModuleSettings,CHAR(39),char(39)+CHAR(39))
|
|
|
|
|
I believe I've found a very unexpected bug in SQLBulkCopy that took me a while to track down. My test program reads in records from a CSV file with exactly 1 column which is defined as a "numeric(19,5)" for statistical uploads.
Condition 1:
If the input file has 25 rows with the only value being the digit zero, followed by a number with a decimal (like '1.23456'), the data is imported perfectly.
Condition 2:
If the input file has 26 rows with the only value being the digit zero (or any non-decimal number), followed by a number with a decimal (like '1.23456'), the data is imported and the last (and any subsequent) rows have the field imported with truncated decimal digits (like '1.00000').
Condition 3:
If the input file has 26 rows and the first row is '0.0', all of the rows are imported perfectly.
I've checked the table/field definition for Condition 2 and it's the same as 1 and 3 so there's no manipulation of the field type (as far as I can tell).
Is this a bug or am I missing something?
|
|
|
|
|
You say the test file has "1 column".
Then you say that each row has "the digit zero, followed by a number with a decimal (like '1.23456'),"
Does that mean that it has two columns or that the single number is '01.23456'?
Also you should post the following
1. What database version you are using.
2. Example data
3. The relevant code.
4. The database schema you using.
|
|
|
|
|
Re: the numbers
The test only had a single column although I first noticed the behavior with 100 columns. Each row only has 1 value (zero) until the 27th row which is the decimal number, i.e.
0
0
0
...
0
1.23456
Re: other questions
1) The destination was SQL Server 2008 and the SQLBulkCopy came from the .Net 4.5 library. This brings up the question "Is the SQLBulkCopy the culprit or the destination SQL Server instance?"
2) Example data: see above
3) Relevant code
using (SqlConnection connection = new SqlConnection(strConn))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = strSQL_table_name;
bulkCopy.BatchSize = GetBulkCopySize();
bulkCopy.BulkCopyTimeout = 2000;
bulkCopy.NotifyAfter = GetNotifyAfter();
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
try
{
bulkCopy.WriteToServer(results);
}
catch (Exception ex)
{
string error = ex.Message;
MessageBox.Show("Error(CSV_To_SQL-a): " + ex.Message);
return;
}
4) single column in a table, name="BigNumeric", data type="numeric(18,5)
|
|
|
|
|
Where is the reader ('results') close?
|
|
|
|
|
I presume results is a datatable, have you inspected the content of the table before BC?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
As it turns out, the process still isn't working. The .Clone() didn't work so I tried the .FillSchema() method and it's failing too.
adapter.FillSchema(table, SchemaType.Source);
table.Columns[2].DataType = typeof (Int32);
adapter.Fill(table);
The column still gets cast as an Int32 from FillSchema(), then I try to change it to typeof(Decimal) followed by Fill() and the decimal points are still truncated in the resulting DataTable. I even tried to set the DataType to String but that didn't work either.
Next I've discovered another practically hidden Microsoft "feature" called TypeGuessRows (and IMEX=1) which supposedly can be modified in the registry or the OleDbConnection string. I haven't gotten it to work yet but at least I know that others have seen this behavior too.
|
|
|
|
|
Keep your column in an numeric format OR convert it manually in numeric format so it dont create destruction.
|
|
|
|
|
[NM] this[^] and now it works
I would like to connect to my SQL Server 2005 instance running on my Windows Server 2003 at home from my office. I'v never tried this before, so bear with me...
First, I follow all the instructions here[^]
I then open SSMS an another PC and attempt to connect to my IP, which fails with
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
Do I need to do something at my router page to allow access to the server? If so, what?
What else could be wrong??
If it's not broken, fix it until it is
modified 11-Oct-12 17:44pm.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
assumed