|
Hi guys,
I want to replace two values in a column and I don't know how to do it with the replace function. I only know how to replace one.
select replace(col0, '100', 'add') as transaction_type
from sometable
Thanks in advance!
|
|
|
|
|
try nesting the replaces:
select replace(replace(col0, '100', 'add'), '200', 'del') as transaction_type
from sometable
|
|
|
|
|
Hello,
I need to add a new record in an Access database. The database has 4 fields:
[id] (autonumber)
[title] (string)
[text] (memo)
[publication_date] (DateTime)
I created my code but it's not working. I can't find the problem.
Can someone help me?
My code is as follows:
Dim connectionString As String =
System.Configuration.ConfigurationSettings.AppSettings("connectionString")
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
Dim queryString As String = "INSERT INTO [t_news] ([title], [text], [publication_date]) VALUES (@title, @text, @publication_date)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_title As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter dbParam_title.ParameterName = "@title"
dbParam_title.Value = "title example"
dbParam_title.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_title)
Dim dbParam_text As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter dbParam_text.ParameterName = "@text"
dbParam_text.Value = "text example"
dbParam_text.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_text)
Dim dbParam_publication_date As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter dbParam_publication_date.ParameterName = "@publication_date"
dbParam_publication_date.Value = DateTime.Now
dbParam_publication_date.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_publication_date)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Thanks,
Miguel
|
|
|
|
|
1. If you use SQL Query Analyer
Exec InsertTable2
Everything works fine.
2. If you use ADO to call stored procedure, as following code, the data will be inserted into the table; but you got empty recordsets returned.
3. If you comment out the Insert statement in the stored procedure, you will get all the recordsets returned. So the Select statement works.
4. This has nothing to do with the permission. Even with sa account, it behaves the same.
5. Does any one know why the Insert statement will block Select statement during ADO call?
----------------------------------------------------------
Create PROCEDURE [InsertTable2]
AS
INSERT INTO Table2 ( field1, field2) VALUES ('1', '2')
Select * From Table2
GO
The VB code to call this procedure
Private Sub cmdInsert2_Click()
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim oCommand As ADODB.Command
Dim rStr As String
Dim index2 As Integer
Set oCommand = New ADODB.Command
sSQL = "InsertTable2"
With oCommand
.CommandType = adCmdStoredProc
.CommandText = sSQL
.ActiveConnection = cn
End With
Set rs = oCommand.Execute
Do While (Not rs Is Nothing)
If rs.State = adStateClosed Then
Debug.Print "Returned recordset is empty"
Exit Do
End If
Debug.Print "----------------------"
While Not rs.EOF
rStr = ""
For index2 = 0 To rs.Fields.Count - 1
If index2 = 0 Then
rStr = rs(index2)
Else
rStr = rStr & ", " & rs(index2)
End If
Next index2
Debug.Print rStr
rs.MoveNext
Wend
Set rs = rs.NextRecordset
Loop
Exit Sub
End Sub
|
|
|
|
|
The insert statement generates a result set consisting of the number of records affected. The select then generates another result set. When you get your recordset back, it is positioned on the first result set (the number of records inserted), so you have to move the recordset to the second result set before you can access it. Use the NextRecordset method of the recordset object to get to it.
If you are not interested in the number of records affected from the first statement, use 'SET NOCOUNT ON' at the beginning of your stored procedure, then the records affected will not be returned in the recordset, and you will have the values you expect returned in the recordset as normal.
Like this:
Create PROCEDURE [InsertTable2]
AS
SET NOCOUNT ON
INSERT INTO Table2 ( field1, field2) VALUES ('1', '2')
Select * From Table2
GO
|
|
|
|
|
Hi Scott:
It works.
Thank you very much
jchsiung2
|
|
|
|
|
Can anyone help me in suggesting a way to pull the number of active bugs for a particular databaase in the product studio using C#.NET
|
|
|
|
|
Hello,
I'm having problems with MSDE. Since approximately two days ago, it suddenly started slowing down randomly on the SqlConnection.Open() call. It just happens randomly, and most of the time it works fine, but it's common enough to be disturbing (maybe around every 5-10 minutes, and some calls can last up to a minute or more).
The network is made of only 3 clients plus one server, and the database is about 8 MBs. It's a LAN.
I checked http://www.petefreitag.com/item/194.cfm[^] and performed the concurrency check and there are none.
So, does anyone here have any ideas? any more details I could provide?
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I've not really used MSDE, so the solutions are a bit of guesswork. MSDE uses connection throttling after so many connections are made (This is to nudge the user to upgrade to a non-free version of SQL Server.)
Are you ensuring you are closing the connections properly? Are you opening connections from more than one process? (possibly even multiple connections within the same process?)
Like I said, these are just guesses as I've not really used MSDE, just SQL Server.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Colin Angus Mackay wrote:
Are you ensuring you are closing the connections properly?
Well, I suppose so.
Colin Angus Mackay wrote:
Are you opening connections from more than one process? (possibly even multiple connections within the same process?)
No, just one process per client machine is using the database, and nobody else. Actually I started the development with one global connection opened in Main at startup... and later changed it to using(SqlConnection conn = new SqlConnection(...)) for all new code. So, at most there should be two connections open by process, and most of the time only one. And for three clients, that's only 6 connections at the very most.
What puzzles me is that it started happening suddenly, and not progressively as one would expect with this kind of performance problems.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Hi
I need to transfer the data from ms access to sql 2000 using Replication
I do not know how to do.
Could any one help in this regard?
|
|
|
|
|
You need to use DTS not Replication.
|
|
|
|
|
I just downloaded SQL Server 2005 Express for the first time last night, and created a simple mockup of another database I'm using to store reports. I need to store unsigned 4 byte and 2 byte numbers, but I can't figure out how, or if it's possible. Is there any way to store an "unsigned int" or "unsigned smallint" as 4 and 2 byte fields, or is it simply a case of moving up and using a bigint and int respectively and wasting a little space?
 Pssst. You see that little light on your monitor? That's actually a government installed spy camera. Smile and wave to big brother!
|
|
|
|
|
use MS SQL Server smallint , int or bigint types, to have integers on 2, 4 or 8 bytes, but it seems that there's no unsigned notion on sql server integers...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
Hello
I want to know your opinion about the following situation.
This is a situation in the database
-------------------------------------
Table Address
- id
- street
- number
- cityID
Table City
- id
- name
In Code
--------
Should I have an address service class that loads (using a address dal)
id, street number, cityid from the address table and calls the city service (using a city dal) to load the corresponding city. Merge the results into an object and pass that around
OR
should the address service class load (using a address dal) to get everything back in one shot?
Meaning: one stored procedure containing 'difficult' logic or two stored procedures containg very simple logic?
Thnx for any response
|
|
|
|
|
Here's my opinion...
These kind of queries are what relational databases are all about. Doing a simple join in the query, instead of two database hits is WAY better. I don't think I've ever run across a case where I'd do two queries rather than a single join to get all of the data I need at the time.
|
|
|
|
|
Go for the one-shot. It is more effective... Give up about the entity-objects... they are useful when the colexity is very high, else use the database for what the databse is effective and good about...
/Mattias
|
|
|
|
|
natural join looks betteer, but you could make a view for this...
CREATE OR REPLACE VIEW AS
SELECT your_columns_to_return
FROM Address, City
WHERE (Address.CityID = City.id);
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
why there is a syntax error on 2 ?
1 . set @sql = @sql + N' and (summary like ''%' + @keywords + '%'')'
2 . set @sql = @sql + N' and (summary like ' + '%' + @keywords + '%' + ')'
|
|
|
|
|
I don't see a syntax error, but in number 2, @keywords isn't single quoted... your query will look something like...
and (summary like '%somevalue%') and (summary like %somevalue%)
|
|
|
|
|
I would guess it is because the '' in line 1 isn't a quotation mark, but double pings ' which means escaping the second ping.
Ping normally starts a string, but if you need pings in the string, you must escape them by adding a ping infront.
Thus the string isnt' terminated proberly in line 2.
I'd think something along that line?
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
|
ahh - yeah. I've always myself used and heard ' refered to as a ping when used in code - but yes - it is an apostorphe in english 
sorry - habbits die hard 
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
Hi there...
this is the problem...
U have to have an apostorph after like an you dont... so change the code to:
set @sql = @sql + N' and (summary like ''' + '%' + @keywords + '%' + ''')'
/M
|
|
|
|
|
Hi
This is part of a sp to search on db.
set @sql = @sql + N' and (summary like ''%' + @keywords + '%'')'
but it doesnt find unicode cells of table.
what should i do ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
You mean "apostrophe"