|
Just look at:
suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%'
If @suburb2 is different from @suburb1, that will select exactly 0 rows because suburb is either like @suburb1 or like @suburb2 but surely not like both at the same time. Use OR instead of AND here.
To differentiate between @suburb2 being set or not, use a CASE statement.
|
|
|
|
|
|
I hate it when someone univotes a post with no explanation.
You error is that this is a database forum and you are asking a question that requires VBA, I know you think you are using a database - Access but that is because Access should no longer be called a database, abomination maybe but not a database.
You will get a much better response in the VB forum. You should also mention that you are using spit MS Access and the version as there are differences that may be critical.
A possible answer - define some global variables representing the data, load them from the dataset/recordset or make the dataset/recordset a global. Sorry have not used Access for more than a decade.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
when i should use nvarchar(max) type and when not?
|
|
|
|
|
I can't say when to use or not, but this link[^] should help with explaining the differences. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
An nvarchar column can stores unicode data but requires twice the space compared to varchar.
A varchar column is restricted to an 8-bit codepage and is therefore limited to one or a few alphabets/languages at a time. Codepage incompatabilities are a PITA, and Unicode is the cure for codepage problems.
Normally you take a performance hit using nvarchar as it takes twice the space in both memory and disc. But if the client uses a different codepage than the server you might take a performance hit on the conversion of the varchar instead.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Use it for strings; not for numerical and temporal data. 
|
|
|
|
|
I don't really understand your question. Are you asking:
- when should I use nvarchar instead of varchar?
- when should I use nvarchar(max) instead of a specified size such as nvarchar(100)?
- when should I use nvarchar(max) instead of ntext?
- when should I use nvarchar(max) instead another datatype?
- something else
- all of the above
These are all different questions (some of which have been answered already).
|
|
|
|
|
Problem we are Facing Currently: Records getting inserted as only one during the process of updating the server.
Previously, all the given records get inserted into the database table during the updation process.
"updateprice" is the .mdb exists locally in the same folder where the calling file for updating records exists. It contains record data to be uploaded to the server database. It acts as an temporary storage database.
The records to be updated to the server are either in an notepad or excel. Whatever be the count in records, previously it was updated fine. "rsTextData.Update" is line 86. I'm blinking where it fails.The error which we're facing and the code is given below for the assistance.
Error Showing as
****************
1 record(s) converted
File Name:DTRinggit081110Ringgit Extends Downtrend Against Greenback
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
/data_maintain/News.asp, line 86
code
****
<table width="100%" cellspacing="0" cellpadding="0" align="center">
<tr><td height="25" class="mtext" background="../images/top_bar.jpg" align="center"><font color="#FFFFFF"><b>ADMINISTRATION</b></font></td></tr>
<tr><td height="10"></td></tr>
<tr><td height="20"></td></tr>
<tr><td width="100%" class="mtext">
<p style="margin-left: 10; margin-right: 3">
<center>
<b> Uploading Text Data in progress...<br>
<br>Please wait..</b>
</center>
<%
set myconn=server.createobject("ADODB.connection")
myconn.open "updateprice"
myconn.CommandTimeout=0
set rsData = Server.CreateObject ("ADODB.Recordset")
NewsCodeSelectSql="Select * from text_data"
set rsData=myconn.execute(NewsCodeselectsql)
code = 0
intRecCount = 0
set myconn1=server.createobject("ADODB.connection")
myconn1.open "DSN=commodities;uid=fbspl07;pwd=07#web&;"
myconn1.commandtimeout=0
Response.Write("1 record(s) converted<br>")
do while not rsdata.eof
Set cmdTemp=Server.CreateObject("ADODB.Command")
cmdTemp.CommandType=2
sql="select * from text_Data where 1<>1"
set rsTextData=Server.CreateObject("ADODB.Recordset")
rstextdata.locktype=adLockOptimistic
rsTextData.Open "select * from text_Data where 1<>1",myconn1
msgbox rsTextData.RecordCount
rsTextData.Addnew
response.write "File Name:" &rsdata(3)
'response.write "Title: " &rsdata(4)&"<br>"
rsTextData("Text_id")=rsdata(0)
rsTextData("Date1")=rsdata(1)
rsTextData("Comname_id")=rsdata(2)
rsTextData("Filename")=rsdata(3)
'response.write "Title= " & rsdata(4) &"<br>"
title1=replace(rsdata(4),"("," ")
title1=replace(title1,")"," ")
title1=replace(title1,":","-")
response.write title1
'response.end
rsTextData("Title")=title1
'rsTextData("Title")= rsdata(4)
rsTextData("Source")=rsdata(5)
rsTextData("isspot")=rsdata(6)
rsTextData("Module_type")=rsdata(7)
rsTextData("Submodule_type")=rsdata(8)
rsTextData("Time1")=time()
rsTextData.Update
rsTextData.close
rsData.movenext
code = code + 1
If code mod 25 = 0 then
Response.Write(code &" records converted<br>")
end If
loop
response.WRITE("<br><br><b> Text Data is Inserted Successfully....</b><br><br>")
%>
<p align="RIGHT"><a href="javascript:history.back()" target="_parent"><font face="Arial" size="2">Back</font></a> </td></tr>
<tr><td height="20"></td></tr>
</table>
M.Sworna Vidhya
|
|
|
|
|
swornavidhya_m wrote: Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
There's already a record in the "text_data" table that holds the same value in the "Text_Id" field as the one that you're trying to insert. That fails, since a primary key needs to be unique; you can't add two records in the table with the same key.
Do a SELECT on the table, using the value from rsdata(0) - you'll see that it's already in there.
I are Troll 
|
|
|
|
|
Eddy Vluggen wrote: There's already a record in the "text_data" table that holds the same value in the "Text_Id" field as the one that you're trying to insert. That fails, since a primary key needs to be unique; you can't add two records in the table with the same key.
Actually, the datas stored in notepad or excel. Then those stored values are copied to "updateprice.mdb". The values from "updateprice.mdb" are transferred to the "text_data" table in server with the method called for updating the values to the server. In this method only i'm facing the problem.
The "Text_Id" field is the unique one and won't have any duplicates in any case. Also, the data team members who are in-charge for inserting datas to server, said that the server updation by inserting is done properly when the record count<=3. If the recordcount exceeds 3, then the error is showing as:
Error Showing as
****************
1 record(s) converted
File Name:DTRinggit081110Ringgit Extends Downtrend Against Greenback
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.
/data_maintain/News.asp, line 86
I'm blinking and stuck with datas getting updated to the server. Kindly help me.
M.Sworna Vidhya
|
|
|
|
|
swornavidhya_m wrote: The "Text_Id" field is the unique one and won't have any duplicates in any case.
You'll get the error that you described if you try to insert one that's already there.
swornavidhya_m wrote: If the recordcount exceeds 3
What's the value of the PK-field (Text_Id) in those three records?
I are Troll
|
|
|
|
|
The value of TEXT_ID will be as like 1779562, 1779563, 1779564, and so on.
|
|
|
|
|
There are three records already in the database you said. What are *those* values? (List them all three please) And what is the value of the PK-field of the record that you're inserting?
I are Troll
|
|
|
|
|
I have a stored procedure with two params in sql2005. If I excute the stored procedure it takes 2 min but i copied the same query in new window, declare variables and run the query it takes 1sec. What is the problem.
|
|
|
|
|
Can you post the query?
I are Troll
|
|
|
|
|
Is it having dynamic queries? Without watching the code, it is quite difficult to find the problem. Can you post relevant code? (Please do not dump everything. Just provide relevant code and use code block link.) 
|
|
|
|
|
|
Good info!
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thank you, I've been looking for that article for ages, it (or a similar article) was posted some time ago as a similar response and I didn't grab it then.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A friend of mine just informed me about that this week. 
|
|
|
|
|
When you encounter such a performance difference, does it then matter, which one was executed first? Or in other words: is the stored procedure faster after you run the normal query?
SQL Server can cache execution plans and results, maybe that's the issue here.
|
|
|
|
|
how to write a query change the datatype for primary key coulm in atable using sqlserver 2008.
Thnaks In advance
Ch.Gayatri
|
|
|
|
|
use ALTER TABLE, you might also consider this[^].
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i tired but its did not worked ou becoz my primary key is used as Fkey in so many tables.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
