|
If it's going to be a regular operation I open a Connection to each database, use a DataReader to read from one, and ExecuteNonQuery to INSERT into the other. A major benefit of this technique is that it allows the app to periodically log its progress (and speed in rows per minute) so you're not flying blind.
This technique also allows you to catch and ignore selected Exceptions if you like and not have the whole process die.
|
|
|
|
|
Two seperate questions, but apply to the same query, so here goes:
SELECT ProdID, ProdTitle, CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName) AS Author FROM Products LEFT OUTER JOIN ...
This works and returns each item with multiple "author" like:
ProdID Prod Title Author
123 Title 1 John Doe
123 Title 1 Jane Doe
465 Title 2 John Doe
456 Title 2 Jane Smith
But what I want is:
ProdID Prod Title Authors
123 Title 1 John Doe, Jane Doe
465 Title 2 John Doe, Jane Smith
So I add a Group Concat to the Author and i get one line and a BLOB in the AUTHORS
SELECT ProdID, ProdTitle, GROUP_CONCAT(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName)) AS Authors FROM Products LEFT OUTER JOIN...
Question #1 is why does this not work.
Question #2 is how can I use the resulting "Authors" in the WHERE clause against a search string.
Edit 1 ref Question 2: I realize I can redo the concat in the where clause, and that is a fine answer if there are no other options, I just thought you could use the newly created "column" in the where clause directly. Seems cleaner then doing the code twice.
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
modified 29-Jan-12 19:09pm.
|
|
|
|
|
You're not stating what database you're using or what error message you're getting.
GROUP_CONCAT needs a GROUP BY clause in the query. It's unclear if you have one.
|
|
|
|
|
Sorry! Thought the Pre Tags listed the language... Using MySQL.
The group by was the error. I also ended up converting back to chars... For anyone's help, here is the code:
SELECT ProdID, ProdTitle, GROUP_CONCAT(convert(CONCAT(Contacts.ContactFName, " ", Contacts.ContactLName), CHAR(24))) AS Authors FROM Products LEFT OUTER JOIN ... GROUP BY ProdID
The second question is more of a best practice I guess. Is there a way to then use the "Authors" list in the where clause? I want the whole list returned, but only with matches to a search string. I know I can duplicate the entire GROUP_CONCAT(convert(CONCAT... but it would be nice if it was only once. Also, will the server actually run those twice? as that seems inefficient.
Thanks again!
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
|
|
|
|
|
The answer to #2 is:
HAVING Authors LIKE %SearchString%
Thanks for the help...
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
|
|
|
|
|
Can someone help me understand why my urlrewriter is not working? It works fine with one rule, but when I add a second it doesn't recognize the second rule and just sends me back to the homepage (Default.aspx). I've played around with the regular expression in so many ways and nothing seems to work. I've also had the 1st rule as "^~/(\D+)/(.*).aspx"
Here is what I have thus far:
<rewrites>
<add name="Rule1" virtualurl="^~/(Article)/(.*).aspx" destinationurl="~/Article.aspx" rewriteurlparameter="ExcludeFromClientQueryString" ignorecase="true">
<add name="Rule2" virtualurl="^~/(\d+)/(.*).aspx" destinationurl="~/Default.aspx?oid=$1&title=$2" rewriteurlparameter="ExcludeFromClientQueryString" ignorecase="true">
Thanks in Advance!
|
|
|
|
|
sorry, i the original didn't post my code...
<rewrites>
<add name="Rule1" virtualUrl="^~/(Article)/(.*).aspx" destinationUrl="~/Article.aspx" rewriteUrlParameter="ExcludeFromClientQueryString" ignoreCase="true"/>
<add name="Rule2" virtualUrl="^~/(\d+)/(.*).aspx" destinationUrl="~/Default.aspx?oid=$1&title=$2" rewriteUrlParameter="ExcludeFromClientQueryString" ignoreCase="true"/>
</rewrites>
|
|
|
|
|
You should probably repost this one in the Asp.net forum as it doesn't seem to have much to do with databases.
|
|
|
|
|
How to take database backup on online server ?
|
|
|
|
|
Start here[^].
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Simple; you ask the database-administrator. He'll know which database-application is installed. Each manufacturer has it's own implementation, and you can find examples for all major vendors on the internet.
Happy searching 
Bastard Programmer from Hell 
|
|
|
|
|
I'm using VB2008 I created a MsAccess 2003 database connection to my project,and make my project exe application. and put it with database Access in one folder and it worked well.
Now I 'm working on the Ms sql server database. I created a DataBase called MyDBs and made connection with it with Vb2008.
My question is can I create Ms Sql database(MyDBs) movable,attachement . means as I did with database access, by put it with application exe in same folder.
I hope I'm clear.
|
|
|
|
|
Yes, by changing the connectionstring[^]. Look under the header "Attach a database file on connect to a local SQL Server Express instance". Your customers will still need to have Sql Server installed, regardless.
If your database is local to the app (say, in the same folder) then stick with Access, or go for SqlCe. If your database needs to support more than a single user, you use Sql Server - in a centralized place where everybody who needs to access the database, can access it.
Bastard Programmer from Hell
|
|
|
|
|
I made this for dtatbase: Script database as - CREATE TO - File
and took the file Of DataBase(MyDBs) and put it in Debug folder in my project.
then I tried this way but didn't succed with me.
Dim MyConnection As SqlConnection = New SqlConnection("Data Source=.\SQLExpress; Integrated Security=true; AttachDbFilename=|DataDirectory|\MyDBs.sql;")
Public Sub Load_DB()
Dim sql As String = " Select Name,Tel,Addr from Table1 "
Dim dp As New SqlDataAdapter(sql, MyConnection)
Dim Table1 As New DataTable
dp.Fill(Table1)
Form1.DataGridView1.DataSource = Table1
End Sub
|
|
|
|
|
Mangore75 wrote: then I tried this way but didn't succed with me.
HOW didn't is succeed? What EXACTLY happened so you know it didn't work?
|
|
|
|
|
it showed me this error;
An attempt to attach an auto-named database for file C:\Users\Nader\Desktop\OOP- CreatProject\OOP- CreatProject\bin\Debug\MyDBs.sql failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
|
|
|
|
|
Error seems to be specifically telling what the possible problems are.
|
|
|
|
|
Only one instance with the name of the database is allowed on the server. Detach the database that's connected, or use an other name.
Bastard Programmer from Hell 
|
|
|
|
|
Why did you expect it too succeed? 
Sql isn't the same beast as Access. You'll need to have a server installed, connect to that, and *hope* that you have sufficient rights to create a database on that server.
Is there a server installed? Can you connect to the master-database on "127.0.0.1\SqlExpress"?
Bastard Programmer from Hell
|
|
|
|
|
I ahve a server installed and it succedd with this conetion but the daabase doesn't in the folder debug. but I tried to use the same way as I did with access, by put the file i debug as I said in my first post.
Dim MyConnection As SqlConnection = New SqlConnection("Data Source=Name-PC\SQLEXPRESS;Integrated Security=true;Initial Catalog=MyDBs")
|
|
|
|
|
It's not Access - Sql databases aren't meant to be portable.
The user that the server is running under needs read/write access on that path. Your connection-string also doesn't mention which db-file to attach. I suggest you use the connection-string that I pointed to.
Bastard Programmer from Hell
|
|
|
|
|
Hi All,
I want to convert DTS Packages to SSIS 2008.
Please suggest me better way to conversion.
Regards,
Vishnu.
|
|
|
|
|
I have a problem of inserting a record which includes data type conversion. There are two databases with the same data structure. One database is ok to insert that record, but another is not ok for data type conversion. I've checked all the data fields.Please give me any suggestion to solve it.
|
|
|
|
|
What type is it you're trying to insert, and what type is the field?
What DB are you using?
|
|
|
|
|
I'm using MSSQL2005. Inserting includes code such as 'X001' which is varchar and it needs to be incremented automatically. So that I converted varchar to numeric. It's ok for one database, but it's not ok for another one. I've checked all the data fields. It's all the same. Any solution for this?
Thanks a lot.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
