In Excel VBA, I've established an ODBC connection to a SQLServer db,
and I can do a successful SELECT query, but I'm having trouble doing
an UPDATE query.
When I run the following code, I get the error, "Cannot Execute a
Select query."
Dim dbsCurrent As Database
Dim qdfChange As QueryDef
Dim rs1 As Recordset
Dim sqlStmt As String
sqlStmt = "UPDATE DATA_99.dbo.IMINVLOC_SQL SET std_cost = .550000"
Set dbsCurrent = OpenDatabase("VW")
Set qdfChange = dbsCurrent.CreateQueryDef("", sqlStmt)
With qdfChange
.Connect = "ODBC;DATABASE=DATA_02;DSN=LabelData;UID=sa;PWD="
.Execute
End With
dbsCurrent.Close
Perhaps I just need some clarification on the general format of how to
update a SQLServer db with VBA. Can someone please suggest some
general code which they've successfully used?
Hello everyone, I'm using sqlserver 2005 with SQL management studio.I have a view and it has a problem. The problem is that...SQL management studio automatically generated some sql script for me to update the view.But,it doesn't work.HELP ME please! This is the script it gave me... ------------- UPDATE [skips].[dbo].[Schedule] SET [ContractId] = <ContractId, int,> ,[DriverId] = <DriverId, int,> ,[TruckId] = <TruckId, int,> ,[Completed] = <Completed, bit,> ,[Day] = <Day, varchar,> WHERE <Search Conditions,,> -------------- When I try to execute it...I get this error , Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '<'.
How do I make this work and update the view? Thanks so much in advance
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System; using System.Collections.Generic; using System.Text; using System.Net; using System.IO;
namespace Predict.Services { public static class Foo { public static string GetIntradayQuote(string symbol) { string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close(); response.Close();
return returnQuote; } } }
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers. OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error". OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954. http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Hi, I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query: SELECT * FROM SYS.Objects. It gives me following error: Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways. Any help would be appreciated.
Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?
I have SQLServer 2005 runs well for months and stop working after install SqlServer2005 SP1. I try to reinstall the SQLServer 2005 but I have problem when install work station component on my and the error is "There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor". Please help me to fix this bug. I do not want to reformat my machine.
I am trying to make a stored procedure in my website for updating an address:1 CREATE PROCEDURE dbo.UPDATE 2 ( 3 @add NVarchar(50), 4 @cit NVarchar(50), 5 @state NVarchar(50), 6 @zip NVarchar(50), 7 @CNum int 8 ) 9 10 UPDATE table_name 11 AppAdd = @add, AppCity = @cit, AppState = @state, AppZip = @zip 12 WHERE CertNum = @CNum When I try to save it it give me an error: Incorrect syntax near keyword 'UPDATE'Must declare scalar variable '@add'
Hi, I'm new to ASP.Net quite new to C# (My first attempt at a database website) and am trying to get a button to add "1" to "int" value called "Comments" a each time its pressed basically counting each time a comment is added. I also only wnat it to affect the row where "ModID" in my database is equal to the query string "ModID" I'm using on the page. I cannot find any tutorials so this is my best guess so far. This is probably a Noobie type stupid question but I'm stuck. This is the code I have so far for my Button_Click event: protected void Button_Click(object sender, EventArgs e){ SqlDataSource CommentCountDataSource = new SqlDataSource(); CommentCountDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["Main_Site_DatabaseConnectionString1"].ToString(); CommentCountDataSource.UpdateCommandType = SqlDataSourceCommandType.Text; CommentCountDataSource.SelectCommand = "SELECT (ModID, DateTimeLastComment, Comments) FROM Mods"; CommentCountDataSource.UpdateCommand = "UPDATE Mods SET (DateTimeLastComment=@DateTimeLastComment, Comments=@Comments) WHERE ModID=@ModID"; //How do i get the Where to use the query string info? CommentCountDataSource.UpdateParameters.Add("DateTimeLastComment", DateTime.Now.ToString()); CommentCountDataSource.UpdateParameters.Add("Comments", "10"); //"10" is just a value to test I'll change this to add "1" once I figure how. CommentCountDataSource.Update();} Sorry if I'm using the wrong lingo but as I say I'm new. If my code is a mile off then please can you send me in the right direction of some code that works.Thanks in advance if anyone can help me. Cheers,Alan
I am a DBA who is moving in the direction of minor database design. I have gone through the steps to get my tables normalized, and I am ready to get them set up so they will update from table to table. How do I set this up? I know SQL does not cascade like Access, so how is this done? From what I have read (Robert Vieira's book) I should use triggers? Any help would be appreciated. Dallas
strSQL = "UPDATE customers SET " strSQL &= "entryid = '" & strtheEntryid & " ' " strSQL &= "WHERE id =1 " right now in table id is my primary key field it increments by 1, I want to say get first record instead of saying id = 1, how to do that?
I have some code to hack in c#.. I have a database and when a form (C#) is loaded a copy of the table is made in a DataTable object. The form has a list box showing just the one column of info from the local DataTable. On the form it is possible to change a record, delete a record and add a record. These are all done in the local table.
The question, how do i send these changes back to the database. Currently it deletes the lot and copies all the local table values in. How do only do the affected row, UPDATE, INSERT etc
I have an ASP application that uses a stored procedure and ADO.net to update a sql server data file. The problem is I know the code is working, I don't have any errors with the ADO, no exceptions are caught. I can use the same basic code to insert a record using a different procedure. It is the update procedure that does not carry through.
So, I know I have a connection, the procedure works using the query builder directly so the procedure works, but when I run the code, I get no errors and no update to the datafile. I am not even sure how to trouble shoot this since I don't have an error to look up.
C# Code:-------------
private void UpdateIssue() { DateTime date = new DateTime(); date = Convert.ToDateTime(this.txtDate.Text);
//edit record in HelpDeskIssuesTbl here.
SqlConnection con = new SqlConnection("Data Source..."); SqlCommand comUpdateTicket = new SqlCommand("sp_UpdateHelpDeskIssues", con); comUpdateTicket.CommandType = CommandType.StoredProcedure; comUpdateTicket.Parameters.Add("@IssueID", this.GridView1.SelectedIndex.ToString()); comUpdateTicket.Parameters.Add("@EmpID", this.ddlEmployee.SelectedValue.ToString()); comUpdateTicket.Parameters.Add("@Date", date.ToShortDateString()); comUpdateTicket.Parameters.Add("@StatusID", this.ddlStatus.SelectedValue.ToString());
( @IssueID int, @EmpID int, @Date datetime, @StatusID int )
AS UPDATE HelpDeskIssuesTbl SET EmployeeID = @EmpID, IssueDate = @Date, IssueStatusID = @StatusID WHERE (IssueID = @IssueID) RETURN
Like I said the Stored Procedure does work when I run it directly in Visual Studio. I have double checked all the params and they all match up unless I am missing something.
My feeling with the SCD component is not that very solid. I have the feeling that the behavior of the insert/update strategy is not always correct an working. I will describe two problems that i encounter.
1. My destination table contains records with the value ''. Cause i don't want '' ( 2 single quotes) in our DWH i update the view that is the source with a case statement that changes the '' to NULL. But when i run the packages the '' values are not update with the NULL values. When i delete the destination table and run the package, the records are inserted with the NULL value as expected. Anyone who has experienced this problem?
2. When i create a new table and run the package so the destination table gets filled with records the SCD will insert alle records (for example 100). When i start the run directly after the first run, all records are updated instead of doing nothing what it should do cause all records exists.
I am looking for a way to update a sqldatasource what I have is a ASP Wizard applicationstep oneis a dataview with the select ability it displays an ID and Namein step two what i want it to do is take the ID from step ones select and put that into the where clause so I have select * from table where id = step1selectedID
I am trying to update a users status from Pending to either Approved or Rejected. I created the following handers to update me db by I keep getting a syntax error. What am I doing wrong? public partial class admin_beta : System.Web.UI.Page{ protected void ApproveButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Approved"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); } protected void DenyButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Rejected"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); }}
I have this web store that I have been creating. When a customer goes to check out, he has to log in, then he is redirected to a page where he can view/add/or edit shipping and billing address. I have based all the sql statements on the profile username, adding records and retrieving them works just fine. When I go to change something in the info it uses an sql statement that updates based on "Where AccountUserName = @AccountUserName", I have @AccountUserName set to Profile("Username"). Keep in mind this works fine for adding new or bring up current records. I even put in code in the updated event for the sql data source to post a msgbox telling me how many rows were affected, it says 1 even though I dont see any change in the data. What am I doing wrong here, it's driving me nuts, its just a very simple update.
Appreciate your efforts in answering queries of so many newbees!I hope to find answering my query..I have created a logon screen to which i have also given the option of changing the password ... Now below is the code for updating the new password given by the user ....Imports System.Data.SqlClient Dim con As New SqlConnection("server=sys2;initial catalog=kris;integrated security=SSPI") Dim cmd As New SqlCommand("select * from u_login", con) Dim dr As SqlDataReader Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load End Sub Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click con.Open() dr = cmd.ExecuteReader While dr.Read() If dr(0) = txtEmail.Text And dr(1) = txtoldpwd.Text Then Dim NewPwdStr As String = txtnewpwd.Text Dim OldPwdStr As String = txtoldpwd.Text Dim sqlstr As String = "Update U_Login set pwd = ('" & NewPwdStr & "') Where pwd = '" & OldPwdStr & "'" Dim cmd1 As New SqlCommand("sqlstr", con) cmd1.ExecuteNonQuery() Response.Write(" Password Changed ... Please login again") End If End While dr.Close() con.Close() End Sub The above code although doesnt throw any error however it shows a blank screen and doesnt even update the new password. Can you plz help me understand what could possibly be wrong in my code n why is that am getting the blank screen. Your help will be highly appreciated!Thanks,Brandy
I am in a fix where I cant seem to update my records. I am trying to follow a template for an Event calendar and cant seem to get the update page working correctly. basically the update on the server never occurs but the message is still returned. Very confused is it my stored procedure? Imports System.Data Imports System.Data.SqlClientPartial Class _Default Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Bind() End SubSub Bind()Using Conn As New SqlConnection("Data Source=WINSERV;Initial Catalog=EventCalendar;User ID=antonio;Password=xxxx;User Instance=False") Conn.Open()Dim cmd As New SqlCommand("sp_GetEvent", Conn) cmd.CommandType = Data.CommandType.StoredProcedure Dim ParameterID As New SqlParameter("@Event_ID", SqlDbType.Int, 4)ParameterID.Value = Request.QueryString("Event_ID") cmd.Parameters.Add(ParameterID)Dim myDataReader As SqlDataReader myDataReader = cmd.ExecuteReader myDataReader.Read()txtapeDate.Text = myDataReader.Item("apeDate") txtapEvent.Text = myDataReader.Item("apEvent")txtWho.Text = myDataReader.Item("Who") txt_type.Text = myDataReader.Item("_type")txtEvent_ID.Text = myDataReader.Item("Event_ID") myDataReader.Close() Conn.Close() End UsingEnd Sub Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim APEvent As String = txtapEvent.Text Dim Who As String = txtWho.TextDim apeDate As String = txtapeDate.Text Dim Event_ID As Integer = txtEvent_ID.TextDim _type As Integer = txt_type.Text 'add it to the DBUsing Conn As New SqlConnection("Data Source=WINSERV;Initial Catalog=EventCalendar;User ID=antonio;Password=xxxx;User Instance=False") Conn.Open()Dim cmd As New SqlCommand("sp_editSingleEvent", Conn) cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@Who", Who) cmd.Parameters.AddWithValue("@apEvent", APEvent)cmd.Parameters.AddWithValue("@apeDate", apeDate) cmd.Parameters.AddWithValue("@_type", _type)cmd.Parameters.AddWithValue("@Event_ID", Event_ID) cmd.Parameters.Add(New SqlParameter("@message", SqlDbType.VarChar, 200))cmd.Parameters("@message").Direction = ParameterDirection.Output cmd.ExecuteNonQuery()Dim sMessage As String = Convert.ToString(cmd.Parameters("@message").Value) Conn.Close() lblMessage.Text = sMessage End Using End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Response.Redirect("MainEvent.aspx")End Sub End Class ===========Stored Procedure==========USE [EventCalendar] GO /****** Object: StoredProcedure [dbo].[sp_EditSingleEvent] Script Date: 06/13/2007 15:15:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER PROCEDURE [dbo].[sp_EditSingleEvent] -- Add the parameters for the stored procedure here @apeDate smalldatetime, @apEvent nvarchar(250), @who nvarchar (50), @Event_ID int, @message nvarchar (200)OUTPUT,@_type int AS Begin Set @Message = 'Hasnt added yet' UPDATE apEvent SET apeDate=@apeDate, apEvent=@apEvent, Who=@Who, _type=@_type WHERE Event_ID = @Event_ID Set @message = 'its added' END
CREATE PROCEDURE discussions_DeletePost@post_id as intAS--prepare the variable to hold the topic_id of the post we are deletingDECLARE @topic_id int--get the topic_id from the post we are going to deleteSET @topic_id = (SELECT topic_id from discussions_PostsWHERE post_id = @post_id)--now that we now what topic the post was in, we can delete the postDELETE FROM discussions_PostsWHERE post_id = @post_id--update topics, and just in case, if the post we deleted was the last post from the topic, give the topic a new last post_idUPDATE discussions_Topics SET topic_last_post_id = (SELECT MAX(post_id) FROM discussons_Posts WHERE topic_id = @topic_id) WHERE topic_id = @topic_id RETURN The problem with this is that, if I delete the last post from the topic, the topic_last_post_id feild does not update with a new post_id Any help?
Hi I'm new to all of this. I have a database that holds customer information (fictitious) and i can select that data and display it in a set of textboxes. I also have an SQL command "UPDATE" that is designed to update the text field that i want to edit. However the problem i'm having is that it'll let me write the info in the textbox but as soon as i click my update button it just flashses and goes back to what it says before e.g. FIRST NAME: LEE i enter TOM and then it reverts it back to LEE This is my SQL UPDATE command statement:
UPDATE CustomerDetails SET [First Name] = @firstnameTextBox, [Last Name] = @lastnameTextBox, [Address line 1] = @addressTextBox, Town = @townTextBox, Postcode = '@postcodeTextBox', [Tel Number] = '@telephoneTextBox' 1 1 2 2 Partial Class Update 3 3 Inherits System.Web.UI.Page 4 4 5 5 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 6 6 custIDTextBox.Text = Session("Label2") 7 7 8 8 Dim updatepage As System.Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView) 9 9 10 10 For Each update As Data.DataRow In updatepage.Table.Rows 11 11 12 12 firstnameTextbox.Text = update.Item("First Name").ToString 13 13 lastnameTextBox.Text = update.Item("Last Name").ToString 14 14 addressTextBox.Text = update.Item("Address Line 1").ToString 15 15 townTextBox.Text = update.Item("Town").ToString 16 16 postcodeTextBox.Text = update.Item("Postcode").ToString 17 17 telephoneTextBox.Text = update.Item("Tel Number").ToString 18 18 19 19 Next 20 20 21 21 End Sub 22 22 23 23 Protected Sub updatebutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles updatebutton.Click 24 24 25 25 'Dim parameters firstnameTextBox, lastnameTextBox, addressTextBox, townTextBox, postcodeTextBox, telephoneTextBox 26 26 'Dim UpdateParameters As QueryStringParameter 27 27 28 28 SqlDataSource1.Update() 29 29 SqlDataSource1.UpdateParameters.Add("@CustomerDetails", System.TypeCode.String) 30 30 'SqlDataSource1.UpdateParameters.Add("@Last Name", System.TypeCode.String) 31 31 'SqlDataSource1.UpdateParameters.Add("@Address line 1", System.TypeCode.String) 32 32 'SqlDataSource1.UpdateParameters.Add("@Town", System.TypeCode.String) 33 33 'SqlDataSource1.UpdateParameters.Add("@Postcode", System.TypeCode.String) 34 34 'SqlDataSource1.UpdateParameters.Add("@Tel Number", System.TypeCode.String) 35 35 36 36 'Label2.Text = ("Update successful") 37 37 End Sub 38 38 End Class 39 39 40 41 42 anyone?
