I'm running a (phpBB) forum which uses a MYSQL data base - I can access the database via a webpage through my ISP and it lets me run short scripts (through a web interface). I need to do the following (shown in psuedo code below). I would be eternally grateful if someone could translate it into pure SQL commands for me to paste into my SQL quiery input field on the web page I'm using to access my database. (I'm a complete SQL novice)
=====================
/* I need to be sure these commands will do nothing if 'jeff' does not exist in the user table */
/* extract the id value for the user called 'jeff' from the users table */
user = GET id FROM users WHERE username = "jeff"
/* remove all entries from the forums_watch and topics_watch tables that contain Jeff's userID */
REMOVE ALL ENTRIES WHERE userID = user
notify = 0;
/* insert many entries into the topics_watch table, one for each topic that exists in the topic table */
arrayOfTopicIDs = GET ALL id FROM topics
FOR EACH current_Topic IN arrayOfTopicIDs {
INSERT INTO topics_watch
(topic_id, user_id, notify_status)
values (current_Topic, user, notify)
}
/* insert many entries for jeff into the forums_watch table, one entry for each forum that exists in the forum table */
arrayOfForumIDs = GET ALL id FROM forums
FOR each current_Forum in arrayOfTopicIDs {
INSERT INTO forums_watch
(topic_id, user_id, notify_status)
values (current_Forum, user, notify)
}
=====================
I'm going to implement this with php and MYSQL as a mod for my forum application (phpBB) - but I could really do with a way of doing this immediately with pure SQL commands as a quick and dirty one shot paste into the input field of the MYSQL database web interface until I get the php mod going.
Can this be done using only SQL commands without tedious repetition?
I'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection Me.cmdUpdate = New System.Data.SqlClient.SqlCommand Me.cmdGetAll = New System.Data.SqlClient.SqlCommand Me.cmdSelect = New System.Data.SqlClient.SqlCommand Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand Me.OdbcSelect = New System.Data.Odbc.OdbcCommand Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand ' 'SqlConnection1 ' Me.SqlConnection1.ConnectionString = "this works fine" ' 'cmdUpdate ' Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _ "Holder)" Me.cmdUpdate.Connection = Me.SqlConnection1 Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing)) ' 'cmdGetAll ' Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.cmdGetAll.Connection = Me.SqlConnection1 ' 'cmdSelect ' Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE (CertHolder = @CertHolder)" Me.cmdSelect.Connection = Me.SqlConnection1 Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder")) ' 'OdbcConnection1 ' Me.OdbcConnection1.ConnectionString = "This works fine" ' 'OdbcGetAll ' Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.OdbcGetAll.Connection = Me.OdbcConnection1 ' 'OdbcSelect ' Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE CertHolder = @CertHolder" Me.OdbcSelect.Connection = Me.OdbcConnection1 Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder")) ' 'OdbcUpdate ' Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder" Me.OdbcUpdate.Connection = Me.OdbcConnection1 Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub Protected WithEvents btnSave As System.Web.UI.WebControls.Button Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList Protected WithEvents txtName As System.Web.UI.WebControls.TextBox Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox Protected WithEvents txtState As System.Web.UI.WebControls.TextBox Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
I get this error message when I try to connect to Reporting Services via the Management Studio.
I can see my machine listed in the Server Name > Browse For More > Local Servers dialogue. But no luck,
Ive tried:
Servername: localhost Servername: DED1774 (the machine name) Servername: localhost/reportserver Servername: DED1774/reportserver Servername: http://ded1774/reportserver (from the rsreportserver.config file
<UrlRoot>http://ded1774/reportserver</UrlRoot>)
I've Googled the error message and found postings for solutions, but none of these helped. Can anyone suggest some simple steps I can take to try to find the issue and get the connection working?
Hey all - been knocking my head on this on for awhile - perhaps someone could shed a little light. I have 2 tables that I want to link: Table 1: UserID - Primary key First Name Last name AuditionID - FK
Table 2: AuditionID - PK Description Sing instrument
So - I have a web form that collects the information for both tables and then insets the collected data. But, the AuditionID value in Table1 does not update. Now, I assume I am incorrect in thinking the AuditionID in Table1 will be automagically updated when Table2 creates it's key...? If that is indeed the case, how do you update 2 tables and maintain a foreign key relationship?
I hope I am being clear enough - any help would be greatly appreciated.
How do you work with an xml file that resides in a database column using IS XML related features? From what i understand so far, the path cannot point to a column within a database but only to a source outside of the database. Am I correct?
Hello, I am asking a question i have seen many threads on, but I am looking for an idiot's guide on how to convert my SQL 2005 database to SQL 2000 so i can get it to run on my web hosting server. I'm very new to asp.net , but have ahd years of experience in normal HTML and a year or two in the old ASP. I was advised to learn ASP.Net 2.0 and have found it nothing but brilliant. The intergration with SQL 2005 made it a lot quicker to link up a database than using Access. Unfortunatly my hosting company is a little behind and still using SQL 2000. There isn't mch databse intergration (a few aplication forms) so I dont mind re-writing the whole database but I dont know how to set Visual web developer up with a SQL 2000 Database. I have also read from various other forums that you can convert a databse to 2000 by doing something with the scripts, but the explaination is too complicated for me to follow. Is there anyone out there who wouldn't mind going over some old ground and explain this all in simple terms? Im using 'SQL Server Managment Studio Express' (although i dont know how to use it) and 'Microsoft visual web developer 2005 express edition' . Thanks for reading this
I'm sure this problem borders on retarded for most readers of this form, but here it is:
I have table with contest and another table with user ratings on those contests.
Contests: ContestID, CreatorUserID, Title Ratings: ContestID, UserID, Rating
If I
SELECT Contests.*, Ratings.* FROM Contests INNER JOIN Ratings ON Contests.ContestID = Ratings.ContestID WHERE (Ratings.UserID = '1')
This only returns the contests that User 1 has rated. But I need all contests. If the user hasn't rated one, I want the result set to show Rating = NULL. The problem is that if a user hasn't rated a contest, that ContestID does not exist in the Ratings table.
seems like it should be a simple ALTER TABLE query but im getting a 'Incorrect syntax near the keyword 'column'.' error. Trying to add a boolean column, heres the script, any help would apprectiated.
ALTER TABLE SHIPPING_ADDRESS ADD COLUMN isUpdated bit,
What is the common mechanism (if any) to make the server notify aclient when some condition is met (say, upon completion of asql-transaction)? The only way to implement this feature I am aware ofnow is to set up a timer and query the server every once in a whilebut surely this technique isn't much good.
I'm creating a website for a student organization at my university. Basically, I have an Excel file with a list of contacts that the members of our organization need to be able to search on our website.
The only field that I need our members to be able to search is "contact location." So for example, a member might want to know if our organization has any contacts in Spain, so the member would type "spain" into the "contact location search" field" and would get results.
I really don't known where to turn... I need the easiest thing that would be manageable for a novice. It doesn't need to be an intense search function; just something to make the website functional (so it obviously needs to be uploadable to a server; the server has PHP capability...no idea if that has any relevance). I've been searching around all day and have come across potentials like PHP, SQL, XML, etc. but have no idea which way to turn... I have Office and Adobe Studio programs at my disposal.
Thank you all-powerful and all-knowing web gods! I owe you my deepest gratitude!
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
Hi guys,I wanna ask bout the problem with my web application. I'm doing a select a statement from table 1 and and with the query results i got, i need it to store the result on table 2. How will i do this? I need your tips and suggestions.
I'm running asp.net 2.0 and acessing MSSQL 2K. I am trying to run a query in which I need to set up variables first. I tried the following group of commands as shown, passing it to the SqlDataReader object, but it failed.. does anyone know how i can pass multiple SQL commands? MainQuery = "declare @MinGrades as Table(GradeID Bigint) " & _ " INSERT @MinGrades SELECT MIN(CreditGrades.SplitID) AS Expr1" & _ " FROM CreditGrades INNER JOIN" & _ " CreditGradeSplits ON CreditGradeSplits.CreditGradeSplitID = CreditGrades.SplitID " & _ " WHERE (CreditGrades.x0x30 = - 1 OR " & _ " CreditGrades.x0x30 >= 0) AND (CreditGrades.x1x30 = - 1 OR " & _ " CreditGrades.x1x30 >= 0) AND (CreditGrades.x2x30 = - 1 OR " & _ " CreditGrades.x2x30 >= 0) AND (CreditGrades.x3x30 = - 1 OR " & _ " CreditGrades.x3xNOD >= 0) " & _ " GROUP BY CreditGradeSplits.CreditGradeGroupID" & _ " Select * from @MinGrades "
I am experiencing a situation where I issue a lengthy SQL command to MS SQL Server 7.0 through MTS and it "disappears" - no errors or recordsets are returned. The command is "SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE 'SMITH%'". When I issue this command from SQL Query Analyzer it takes 27 seconds to return 87 rows. When I issue this exact same command through MTS it does not return at all.
I've used the SQL Profiler to analyze the requests. It shows the commands from MTS starting but they never stop (or at least the profiler never reports them as stopping). The same commands coming from Query Analyzer are reported as starting and stopping without fail.
Here's a twist: I can issue less demanding commands (ie, one that doesn't take so long to process) through MTS and they come back fine. For example, when a user logs into my application, I use an SQL statement to verify the user name and password and status the user as logged in. This is routed through MTS and it comes back fine in less than a second. Same application, same PC, same MTS and SQL server, same SQL database. The only difference is that the CUSTOMERS table has over 800,000 records and the USERS table has only 5 records.
I am very new to sql server and I have been reading up on the dbcc commands. For instance, DBCC SHOWCONTIG where do I execute this command? I went into the cmd prompt and opened osql and typed: 1> DBCC SHOWCONTIG 2> GO
This gives me the information for what I assume is the master database. But, how do I use this command under the other databases?
Also, I have been trying to learn how to determine when to do index maintenance by using the index tuning wizard. But, I don't know what a workload is. Can someone point me in the right direction or give me some information about this topic.
Please can someone point me in the direction, i built a very badly designed database consisting of only one huge table when i first started databases, since learning about normalization i have designed and set up a new database which consists of many more tables instead of just the one. My question is where do i start in transfering the data from the old single tabled database to my new multi-tabled database?
I have MS SQL server 2005 managment studio if that helps, but want to transfer around 200,000 rows of data into the new database. Both new and old databases are on the same server.
I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
hi all when i set up a new subcription ,i notice in hte replication monitor thet this subscription 'uninitialize subscription' and the are 70 undelevier commands.
how can i fixed this problem,or how can i remove those undeleveirs commands?
Suppose there are two tables employee and salary and a,b are two instances of tables employee and salary.There are 20 records in each table.Then what will be the result of the following query
Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition server.
The SQL database gets changed/updated through a web interface. I was wondering if there was any way for me to see which SQL commands were being run on the database when changed like deleting/inserting users are being applied from the Web frontend?
How can I make sure that a couple of commands are either all executed on the database or none of them. For example right now I have an insert, update and delete command. I'm calling each of them with a SqlCommand. So I am afraid that that one of them might be executed, then there's a bad connection and the other two are not. How can I prevent this so that only all commands or nothing is executed on the database?
Hi everyoneI created a database graphically in VS2005.Now I want the text version of those DDL(create) commands. Question is where and how are those commands stored in SqlServer I have the management studio too. Any ideas??
Please forgive me if my questions seem very simple. I'm trying to pick up vb.net, asp.net, and SQL all at the same time, and while making progress in some areas I'm finding other questions I have harder to figure out where to look for answers. First question - if I want to join 2 tables do the fields I'm joining on have to have the same name? I know they have to be of the same type, but does the name itself matter? For instance in this line of code I try to join forums objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.forum_moderators = users.user_ID" Second question nagging at me, since I specify the table <msg_forums and users> before the field names <forum_moderators and user_ID> does it matter which one comes first? See, my primary table in this line is msg_forums, so do I have to specify it's field first, or does that even matter (or do I have it backwards and need to specify the other one first)? Last question, and of course the root of what's caused me to wonder about all of this. I'm getting an error similar to some of what I found in the forums, but it's slightly different in that it says there is a problem near "OR". As far as I can tell I've written this correctly, and so, I'm stumped. Here is the error followed by the code. Thanks a bunch for any assistance anyone offers. :-) Incorrect syntax near the keyword 'ON'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'ON'. 'This time trying to learn an adapter instead of a reader, will also try a join 'Build the connectionDim StrConnection As String = (System.Configuration.ConfigurationManager.AppSettings("DbConnect")) Dim objConnection As New SqlConnection(StrConnection)Dim objdataadapter As New SqlDataAdapter() Dim objdataset As New DataSet() 'set the SelectCommand Properties objDataadapter.SelectCommand = New SqlCommand objdataadapter.SelectCommand.Connection = objConnection 'try after renaming to fields to match objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.user_ID = users.user_ID" 'Try without specifying the table 'objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON user_ID = user_ID" 'Original attempt objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.forum_moderators = users.user_ID" objdataadapter.SelectCommand.CommandType = CommandType.Text 'open the database connection objConnection.Open() 'Fill the dataset with object data giving the dataset to use and the table name (primary table even when pulling from a couple)objdataadapter.Fill(objdataset, "msg_forums") 'close connection objConnection.Close() 'Set DataGridView properties grdforums.AutoGenerateColumns = True grdforums.DataSource = objdataset grdforums.DataMember = "msg_forums"End Sub End Class