Trying To Reference Another Database In Update Command On Same SQL Server
Aug 15, 2006
I'm trying to update a table in MyDB1 with a value in MyDB2 on the same SQL server (2000)
UPDATE MyDB1.dbo.Users
SET MyDB1.dbo.Users.InstantASP_UserID = MyDB2.dbo.InstantASP_Users.UserID
FROM MyDB2
INNER JOIN dbo.aspnet_Membership a ON a.UserId = MyDB1.dbo.Users.UserID
WHERE MyDB2.dbo.InstantASP_Users.EmailAddress = a.Email
I don't want to have to create a linked server. It's on the same box.
I'm getting Invalid object name 'MyDB2'.
View 1 Replies
ADVERTISEMENT
Mar 27, 2006
Hi all,
I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).
Anyone know how to get this done.
Please advice.
Thanks and appreciated.
View 4 Replies
View Related
Feb 23, 2007
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies
View Related
Mar 18, 2005
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
View 2 Replies
View Related
Apr 25, 2008
I have a table like the following
Table=Items
Type / Name
3 / Active Item
3 / Active Item
4 / New Item
4 / New Item
5 / Old Item
5 / Old Item
How do I run a querry to update the Type Colum entrys from 5's to 3's? I have tried the following:
UPDATE Items SET Type = 3 WHERE Type = 5
The above querry passes SQL Querry analyzer, but the querry itself provides the following error: Unable to parse query text.
I think the error is due to the fact that I am using a circular reference to update the same Column that I am testing on.
MS-SQL 2005 SP-2
Any help would be FANTASTIC!
View 4 Replies
View Related
Jun 16, 2006
I am in the process of moving a SQL2000 database to a SQL2005 database.
Porting from: SQL200, Windows Server 2000(SP4) (32 bit dual processor)
to:SQL2005, Windows Server 2003(SP1) (x64 bit dual processor)
After porting the database from SQL2000 to SQL2005 (no changes) running the same update statement from Management Studio on the 2003 Server and and Query analiser on the 2000 Server.
SQL2000 completes the command in 2 minutes SQL2005 is still running after 60 minutes.
SQL2000 is the live/production system with users connected, the SQL2005 is in a test environment with no other processors running.
When the problem first showed up the SQL2005 activity monitor displayed CXPACKET wait type on 2 processes with the same pid number. I now no longer have any wait type being displayed but my wait time is increasing rapidly. No block is reported.
I assume that I have an CXPACKET lock problem.
Am I correct that I have a CXPACKET problem and if so what is the resolution?
The update statement is as follows...
update BI_LENDING_TRANSACTIONS
set [Balance Movement Month] = M.[Balance Movement Month]
from BI_LENDING_TRANSACTIONS as T,
BI_BALANCE_MOVEMENT_DATES as M,
BI_COMPANIES as C
where (T.[Transaction Date] >=
(SELECT DATEADD(d, - 70, minDate) from (select min([Transaction Date]) minDate
from p_BI_LENDING_TRANSACTIONS) t1)
OR
T.[Transaction Date] >= C.[MostRecentSnapShotDate] or
T.[Value Date] = T.[Balance Movement Month] ) and
T.[Value Date] <= C.[MostRecentSnapShotDate] and
T.[Value Date] >= T.[Transaction Date] and
T.[Company_Code] = M.[Company_Code] and
T.[Value Date] > M.[SnapShotFromDate] and
T.[Value Date] <= M.[SnapShotToDate] and
C.[Company_Code] = M.[Company_Code]
View 1 Replies
View Related
Oct 19, 2005
UPDATE #TempTableESR SET CTRLBudEng = (SELECT SUM(Salaries) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudTravel = (SELECT SUM(Travels) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudMaterials = (SELECT SUM(Materials) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudOther = (SELECT SUM(Others) from ProjectBudget WHERE Project = @Project)UPDATE #TempTableESR SET CTRLBudContingency = (SELECT SUM(Contingency) from ProjectBudget WHERE Project = @Project)above is the UPDATE command i am using in one of my stored procedures. I have to SELECT from my ProjectBudget table 5 times to update my #TempTableESR table. is there an UPDATE command i can use which would let me update multiple fields in a table using one SELECT command?
View 1 Replies
View Related
Dec 17, 2007
Does any one has any clue for this error ? I did went through a lot of articles on this error but none helped . I am working in Visual studie 2005 and trying to upload image in sql database through a simple form. Here is the code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.IO;
public partial class Binary_frmUpload : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void btnUpload_Click(object sender, EventArgs e)
{if (FileUpload.HasFile == false)
{
// No file uploaded!lblUploadDetails.Text = "Please first select a file to upload...";
}
else
{string str1 = FileUpload.PostedFile.FileName;
string str2 = FileUpload.PostedFile.ContentType; string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
//Initialize SQL Server Connection SqlConnection con = new SqlConnection(connectionString);
//Set insert query string qry = "insert into Officers (Picture,PictureType ,PicttureTitle) values(@ImageData, @PictureType, @PictureTitle)";
//Initialize SqlCommand object for insert. SqlCommand cmd = new SqlCommand(qry, con);
//We are passing Original Image Path and Image byte data as sql parameters. cmd.Parameters.Add(new SqlParameter("@PictureTitle", str1));
cmd.Parameters.Add(new SqlParameter("@PictureType", str2));Stream imgStream = FileUpload.PostedFile.InputStream;
int imgLen = FileUpload.PostedFile.ContentLength;byte[] ImageBytes = new byte[imgLen]; cmd.Parameters.Add(new SqlParameter("@ImageData", ImageBytes));
//Open connection and execute insert query.
con.Open();
cmd.ExecuteNonQuery();
con.Close(); //Close form and return to list or images.
}
}
}
Object reference not set to an instance of an object.
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.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 32:
Line 33: string str2 = FileUpload.PostedFile.ContentType;
Line 34: string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
Line 35:
Line 36: //Initialize SQL Server Connection Source File: c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs Line: 34
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
Binary_frmUpload.btnUpload_Click(Object sender, EventArgs e) in c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs:34
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
View 2 Replies
View Related
Jan 5, 2007
I am construvcting a number of databases, some of which containsensitive data and most of which do not. I am attempting to handle thesecurity issues involved in protecting sensitive data in part byputting it in its own database. If the sensitive data is in a databasecalled d_SensitiveData, and in that database there is a table called't_A' (I know, not very informative, but this is only a triviallysimple example :-), and I have a script running in a database'NotSensitiveData' (i.e. there is a statement at the beginning of thescript "USE NotSensitiveData"), how do I referenece the primary key intable 'A'?I tried a variety of things like:ALTER TABLE t_nsXADD CONSTRAINT FK_PersonID FOREIGN KEY (p_idPerson)REFERENCES SensitiveData.t_A (p_idPerson);The above is, in fact, my latest attempt. Everything I tried hasfailed precisely at the point where I specify a table in a differentdatabase. So what is the trick to refering to a table in one databasewhen using another database?This will be used in a ASP.NET v3 application where one of the things Iwant to do is have the authentication provider be a different databasefrom the one used for the main application data.Does anyone know of an example I can download from the web that doesthe same sort of thing I want to try, with some discussion of securityissues involved (i.e. what I can do to harden the application and dataserver)?NB: I am an application developer, not a DBA nor a systemadministrator.ThanksTed
View 6 Replies
View Related
Aug 10, 2006
I have a database on a server that im trying to update using asp.net. I tested this on another server and everything worked perfectly. The test system was set up where the website is hosted. But the live system is placed on a different server than the webpage. Does that make a difference?
Test System
Client -> Website/database
Live System:
Client -> website -> database
I created a user account to use when accessing the database on the live system because i read the double hop causes problems. By using that account i can access and view the data. But whenever i update it nothing happens. NO ERROR either. It works perfectly but does nothing. Anyone have any ideas please?!?!?!
View 2 Replies
View Related
Dec 10, 2007
Hi all, just need a LITTLE help here. I am very close, I know I am, but just can't seem to fit the last piece in on this. I have a page that shows current data for a customer and allows you to make changes to the data, then I have a button to push to update the system. If I hard-code in a value in place of '@Pf_Value' it will update that value, so I know my where is working, just SOMETHING missing in syntax on the set statement or something wrong on my cmd.parameters statement. Any help would be great!!Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
Dim sql As String = "update CustomerPOFlexField set [Pf_Value] = @Pf_Value where Pf_property = 'Attrib1' and Pf_CustomerNo = @CustomerNo"Dim newc As String = NewCustomer.Text
Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("HCISDataConnectionString").ConnectionString)Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@CustomerNo", DropDownlist1.SelectedValue)cmd.Parameters.Add(New SqlParameter("@Pf_Value", TextBox2.Text))
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Sub
Thanks,
Randy
View 16 Replies
View Related
Jan 8, 2008
Hi all,
Im working in VB web application.Im having some values in some textbox and trying to update it using update command and its not working for me... I used ExecuteNonQuery statement after that. The alert message after that is working for me. But the values i changed in textbox in not updating in my database.
What could be wrong? cid = lbcid.Text
cname = txtname.Text
contactname = txtconame.Text
tele = Val(txttele.Text)
mob = Val(txtmob.Text)
email = txtemail.Text
genmess = txtgen.Text cmd = New SqlCommand("Update CompanyDetails set CompanyName='" & cname & "',ContactName='" & contactname & "',Telephone=" & tele & ",Mobile=" & mob & ",Generalmess='" & genmess & "',Email='" & email & "'where CompanyId= '" & cid & "'", con)
con.Open()
cmd.ExecuteNonQuery()
Response.Write("<script>alert('Updated')</script>")
View 5 Replies
View Related
Nov 5, 2007
I'm using SQL Server Management Studio to do an extremely simple table update (I thought). I wish to fill a newly added table field with a value, however I didn't do this before... What I do is:
UPDATE _t_Computerhuys_MSCRM.dbo.ContactExtensionBase
SET New_Test = "Testvalue"
However then I get the errormessage:
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Testvalue'.
Apparently what I see as a value is being looked at as a column name. I guess it has to do with syntax, but I can't find what's wrong. How to solve?
View 1 Replies
View Related
Dec 4, 2006
Hi all,At this moment I'm trying to get database mail working. According tosome people it should be easy. Well...that is not the case for me.I'm having the following error:The mail could not be sent to the recipients because of the mail serverfailure. (Sending Mail using Account 2 (2006-11-24T08:48:15). ExceptionMessage: Cannot send mails to mail server. (Command not implemented.The server response was: Command not Supported).)SQL Server 2005 is installed on a separate server and the SMTP Serveris Lotus Notes. connecting with port 25.Start and stopping the SQL Server and SQL Server agent i tried already.i tried sysmail_stop_sp and sysmail_start_sp.Sometimes when starting or stopping the Mail with the followingcommands :exec sysmail_stop_spGOexec sysmail_start_spGOI will get the following error:Msg 233, Level 20, State 0, Line 0A transport-level error has occurred when sending the request to theserver. (provider: Shared Memory Provider, error: 0 - No process is onthe other end of the pipe.)So any help would be appreciated.Hennie
View 1 Replies
View Related
Sep 10, 2014
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
View 0 Replies
View Related
Oct 10, 2006
Im looking for example code to make a sql update... I want to use command.Parameters and variables from text boxes and i'm unsure how to do this... Please help. This code below doesn't work but it is an example of what i've been working with.. <code> { string conn = string.Empty; ConnectionStringsSection connectionStringsSection = WebConfigurationManager.GetSection("connectionStrings") as ConnectionStringsSection; if (connectionStringsSection != null) { ConnectionStringSettingsCollection connectStrings = connectionStringsSection.ConnectionStrings; ConnectionStringSettings connString = connectStrings["whowantsConnectionString"]; conn = connString.ConnectionString; using (SqlConnection connection = new SqlConnection(conn)) using (SqlCommand command = new SqlCommand("UPDATE users SET currentscore=5)", connection)) { updateCommand.Parameters.Add("@currentscore", SQLServerDbType.numeric, 18, "currentscore"); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } }</code>
View 3 Replies
View Related
Feb 13, 2007
i am using visual web developer 2005 and SQL Express 2005 and VB as the code behindi have a table called orderdetail and i want to update the fromdesignstatus field from 0 to 1 in one of the rows containing order_id = 2so i am using the following coding in button click event Protected Sub updatebutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim update As New SqlDataSource() update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() update.UpdateCommandType = SqlDataSourceCommandType.Text update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = '1' WHERE order_id = '2'" End Sub but the field is not updatedi do not know where i have gone wrong in my coding. i am sure that my database connection string is correctplease help me
View 1 Replies
View Related
Jul 10, 2007
Here I have the following command
Dim dtNow As DateTime = DateTime.NowSqlDataSource1.UpdateCommand="Update [db] Set [LW]='TRUE', LWD=dtnow Where [PK]=@PK"What I was ttrying to accomplish was , in my grid view, when someone clicks update, it would automatically set LW to true and set LWD to today's date. No user intervention required. However, I figured the above script would not work. What would I have to do to make LWD = dtnow? I do not want to give the user the option to update anything.
View 3 Replies
View Related
Jul 26, 2007
Hello, I'm using a Gridview to display a list of servers. Each server has a column in a table called "Enabled." I want to create a button that is supposed to toggle the value called "Enabled." I am able to make the button either to set Enabled to true or false, but I don't know how to make it toggle. Here is the command:UpdateCommand="UPDATE [ServerStatus] SET [Enabled] = 1 WHERE [ServerName] = @ServerName" The button is a buttonfield in the gridview:<asp:ButtonField ButtonType="Button" CommandName="Update" HeaderText="Toggle" ShowHeader="True" Text="Toggle" /> Does anyone know the syntax, or a way to make the button set Enabled to true when it is false, and false when it is set to true?
View 2 Replies
View Related
Aug 2, 2007
Hi,i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.This happens in event DetailsView1_ItemUpdating with my own code.It works without parameters (i know, bad way) like this:SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"But when using parameters like here below, i get the error:"Input string was not in a correct format"Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdatingSqlDataSource1.UpdateCommand = "UPDATE mytable set name= @myname"SqlDataSource1.UpdateParameters.Add("@myname", SqlDbType.NVarChar, na)SqlDataSource1.Update()End SubI don't see what's wrong here.Thanks for helpTartuffe
View 2 Replies
View Related
Sep 24, 2007
hello all..,i have problem in update sqldatasource, my code like that:me.sqldatasource.updateparameter(index).defaultvalue=valueme.sqldatasource.update()it can not update data, why?but if i use insert or delete like:me.sqldatasource.insertparameter(index).defaultvalue=valueme.sqldatasource.insert()me.sqldatasource.deleteparameter(index).defaultvalue=valueme.sqldatasource.delete()it can work for insert and delete data...can anyone give me update command code in sqldatasouce? plsss...thx...
View 11 Replies
View Related
Jan 5, 2008
I have the code as fallows to update my SQL data ;Sub Kaydet(ByVal TickerKod As String, ByVal op1 As Double, ByVal op2 As Double, ByVal op3 As Double, ByVal op4 As Double, ByVal op5 As Double, ByVal op6 As Double, ByVal op7 As Double, ByVal mov1 As Double, ByVal mov2 As Double)
'Dim nop1 As Decimal = FormatNumber(Replace(op1, ",", "."), 2)Dim mysource As New SqlDataSource
mysource.ConnectionString = ("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Stock.mdf;Integrated Security=True;User Instance=True")mysource.UpdateCommand = ("UPDATE StockParametre SET OPT1 = " & FormatNumber(op1, 2) & ", OPT2 = " & FormatNumber(op2, 2) & ", OPT3 = " & FormatNumber(op3, 2) & ", OPT4 = " & FormatNumber(op4, 2) & ", OPT5 = " & FormatNumber(op5, 2) & ", OPT6 = " & FormatNumber(op6, 2) & ", OPT7 = " & FormatNumber(op7, 2) & ", MOVY = " & FormatNumber(mov1, 2) & ", MOVD = " & FormatNumber(mov2, 2) & " WHERE (Stock = '" & TickerKod & "')")
mysource.Update()Response.Write(mysource.UpdateCommand & "<br>")
End Sub
When I call this code inside of Virtual Web Developer it functions perfect and updates the data..There is no problerm
But when I call it like http:// .......myip/updatedata.aspx then I have the error " System.Data.SqlClient.SqlException: Incorrect Syntax near 32
I see that 32 is the value inside the update command like SET OPT1=107,32, OPT2=25,00, ........and so on Where do I make wrong ?
Thanks
View 4 Replies
View Related
Feb 3, 2008
Hi here's a bit of code. What am I doing wrong here? Visual Studio isn't even accepting the Set word on line 56. It deletes it everytime. What am I doing wrong here? Why is Visual studio putting the parenthese around the table name in 55? I generated an update query for my Websitetableadapter. Here it is:
UPDATE [tblWebSite] SET [Rating] = @Rating, WHERE (([WebSiteID] = @Original_WebSiteID))
How do I use this to update the Rating column after I've done my calculation below?
1 Imports RatingsTableAdapters2 3 4 Partial Class admin_ratings5 Inherits System.Web.UI.Page6 7 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load8 Dim I As Integer = 09 Dim J As Integer = 010 Dim Rating As Integer11 Dim Rate As Decimal12 Dim tblwebsiteAdapter As New tblWebSiteTableAdapter13 Dim tblWebsite As ratings.tblWebSiteDataTable14 tblWebsite = tblwebsiteAdapter.GetData()15 For Each tblwebsiteRow As ratings.tblWebSiteRow In tblWebsite16 Rate = 017 Dim tblLinkAdapter As New tblLinkTableAdapter18 Dim tblLink As ratings.tblLinkDataTable19 Dim tblLinkTot As ratings.tblLinkDataTable20 tblLink = tblLinkAdapter.GetSuccessfulExchanges(tblwebsiteRow.WebSiteID)21 tblLinkTot = tblLinkAdapter.GetTotalLinks(tblwebsiteRow.WebSiteID)22 For Each tbllinkRow As ratings.tblLinkRow In tblLink23 If tbllinkRow.LinkID < 1 Then24 I = 0.125 Else : I = I + 126 End If27 Next28 If I <> 0 Then29 For Each tbllinktotrow As ratings.tblLinkRow In tblLinkTot30 If tbllinktotrow.LinkID < 1 Then31 J = 0.132 Else : J = J + 133 End If34 Next35 End If36 If I <> 0 And J <> 0 Then37 38 Rate = I / J39 If Rate <= 0.3 Then40 Rate = 041 End If42 If Rate <= 0.5 Then43 Rate = 144 End If45 If Rate <= 0.65 Then46 Rate = 247 End If48 If Rate <= 0.75 Then49 Rate = 350 End If51 End If52 53 Response.Write(tblwebsiteRow.WebSiteID & " " & tblwebsiteRow.SiteURL & " Rating: " & Rate & "54 I = 055 J = 056 Update(tblWebsite)57 Rating = Rate58 where(tblwebsiteRow.WebSiteID <> 0)59 Next60 End Sub61 End Class
View 34 Replies
View Related
Feb 18, 2008
hi, i am tryuing to use the gridview as means for the user to be able to edit delete and update columns of the database. however when it is run in the browser it allows the user to edit the fields but when i click on the update button it throws an error. can someone please offer me advice on how i can sort this problem out or provide me with any examples as i cant see what the error is. i used the option in edit columns which allows you to specify you want update delete etc controls added to the gridview. how can i make it so it supports updating? thank you
Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.
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.NotSupportedException: Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
View 2 Replies
View Related
Apr 15, 2008
HI
i make form to update some data in table
the code:protected void Update_Click(object sender, EventArgs e)
{SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=AinShams;Integrated Security=True");
con.Open();SqlCommand cmd = con.CreateCommand();
int iid = int.Parse(DropDownList1.SelectedValue);string name2 = DropDownList1.SelectedItem.Text;
string des2 = txt_dsc.Text;string loc2 = txt_loc.Text;
int act=-1;if (active_check.Checked == true)
{
act = 1;
}
else
{
act = 0;
}int yearsc = int.Parse(txt_yc.Text);
int pr = -1;if (prep_ck.Checked == true)
{
pr = 1;
}
else
{
pr = 0;
}
cmd.CommandText = "update Faculties set FacultyName=" +name2+ ",FacultyDescription=" + des2 + ",FacultyLocation=" + loc2 + ",FacultyActive='" + act + "',FacultyYearsCount='" + yearsc + "',FacultyIsPrep='" + pr + "' where FacultyId='" + iid + "'";
//cmd.CommandText = "update Faculties set FacultyName=@name,FacultyDescription=@des,FacultyLocation=@loc,FacultyActive=@act,FacultyYearsCount=@years,FacultyIsPrep=@p where FacultyId=@iid";
// cmd.CommandText = "update Faculties set FacultyDescription=" + des2 + " where FacultyId='" + iid + "'";
//cmd.Parameters.AddWithValue(@name, name);
//cmd.Parameters.AddWithValue(@des, des);
//cmd.Parameters.AddWithValue(@loc, loc );
//cmd.Parameters.AddWithValue(@act, act);
//cmd.Parameters.AddWithValue(@years, yearsc);
//cmd.Parameters.AddWithValue(@p , pr);
//cmd.Parameters.AddWithValue(@iid, iid );
cmd.ExecuteNonQuery();
con.Close();
}
but it give mw exception:
Server Error in '/try' Application.
Invalid column name 'sara'.Invalid column name 'mayada'.Invalid column name 'mmmmmm'. 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: Invalid column name 'sara'.Invalid column name 'mayada'.Invalid column name 'mmmmmm'.Source Error:
The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:1. Add a "Debug=true" directive at the top of the file that generated the error. Example: <%@ Page Language="C#" Debug="true" %>or:2) Add the following section to the configuration file of your application:<configuration> <system.web> <compilation debug="true"/> </system.web></configuration>Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario. Stack Trace:
[SqlException (0x80131904): Invalid column name 'sara'.
Invalid column name 'mayada'.
Invalid column name 'mmmmmm'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +180
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
Manage_Faculties22.Update_Click(Object sender, EventArgs e) +879
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
when i click the update button this error appear
sara mayada mmmmm the data i enter in the form
what couse this error please?
Thanx in advance
View 1 Replies
View Related
Apr 18, 2008
Hi I and using gridview. And binding the data in the code behind.I need to use update command in code behind. How do I achieve this? I protected void lookUP (object sender, EventArgs e)
{
string strSql, strConn;
System.Text.StringBuilder whereClause = new System.Text.StringBuilder();
strConn = ConfigurationManager.ConnectionStrings["drake_CSMConnectionString1"].ConnectionString;
SqlConnection Conn = new SqlConnection(strConn);
if (newClientName.Text != "")
whereClause.Append("'" + newClientName.Text + "'");
strSql = "SELECT * FROM [ftsCSM] where [client_name] = " + whereClause.ToString();
SqlDataAdapter dataAdapter = new SqlDataAdapter(strSql, Conn);
DataSet ds2 = new DataSet();
dataAdapter.Fill(ds2, "ftsCSM");
DataTable dataTable2 = ds2.Tables["ftsCSM"];
int totalRec = dataTable2.Rows.Count;
Clients.DataSource = ds2;
Clients.DataBind();
} // end of lookup()
View 1 Replies
View Related
May 31, 2008
I thought this would work. I use this code to update another table in another page. In this page, after someone has cast their vote for an article, the article db is updated for the current rating. It reads all the votes so far and takes and average and is supposed to write this number to the article database. There are 2 columns. ArticleRating is a decimal and is the average and ArticleReaders is the total number of votes.
The response.write in lines 3 and 4 are the correct numbers appearing in my upper left corner of the page. They are not getting written to the db. Can someone help me understand why this is the case? 1
2 Public Function UpdateRating(ByVal ArticleID As Integer, ByVal ArticleRating As Decimal, ByVal Votes As Integer) As Boolean
3 Response.Write("Values: " & ArticleRating)
4 Response.Write("Votes: " & Votes)
5
6 Dim con As New SqlConnection(DataFuncs.GetConnectionString)
7 Const sSQL As String = "UPDATE tblArticle SET ArticleRating = @ArticleRating, ArticleReaders=@Votes WHERE ArticleID = @ArticleID"
8 Dim xSqlCommand As SqlCommand = New SqlCommand(sSQL, con)
9 Try
10 xSqlCommand.Parameters.Add("@Rating", Data.SqlDbType.Decimal)
11 xSqlCommand.Parameters("@Rating").Value = Rating
12 xSqlCommand.Parameters.Add("@Article", Data.SqlDbType.Decimal)
13 xSqlCommand.Parameters("@ArticleID").Value = ArticleID
14 con.Open()
15 xSqlCommand.ExecuteNonQuery()
16 con.Close()
17 Return True
18 Catch ex As Exception
19 Return False
20 Throw ex
21 Finally
22 xSqlCommand.Dispose()
23 con.Dispose()
24 End Try
25 End Function
View 7 Replies
View Related
Jun 15, 2004
i want to update a table where a linked table is a certian value
for example
i have a table that queues up calls and has whether they have been contacted or not as a boolean value.
but if the call is closed in another way i would like to update that table where the linked location table has a certian value
but in the query analizer it will only let you use one table to update is there another way to do this?
View 2 Replies
View Related
Mar 3, 2007
Hi
I've created this simple trigger that will insert records into another table when an insert has occurred on another.
table A
table B
CREATE TRIGGER [TG_MYTRIGGER]
ON [dbo].[A]
AFTER INSERT
AS
Begin
INSERT B (date, id)
Select DISTINCT i.date,i.id
from inserted i
End
What i cant figure out is how to insert the date plus one month into table B
so instead of just inserting into the second table the i.date i want the i.date + one month.
Does anyone know how this could be done.
many thanks
Bil
View 1 Replies
View Related
Mar 8, 2015
I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO
View 1 Replies
View Related
Dec 25, 2006
Hi all,
I have this store procedure as follows:
Create Proc UpdateProblem@ProblemID int,@CompanyName varchar (50),@Firstname varchar (50),@Lastname Varchar (50),@Address varchar (50),@Postcode varchar (50),@City varchar (50),@Phone varchar (50),@Cutype varchar (50),@ProDescript varchar (50),@Sol varchar (50),@Email varchar (50)
as Update Problemset CompanyName = @CompanyName,Firstname = @Firstname,Lastname = @Lastname,Address = @Address,PostCode = @Postcode,City = @City,Phone = @Phone,Cutype = @Cutype,ProDescript = @ProDescript,Sol = @Sol,Email = @Email
where ProblemID = @ProblemID
when I test the querry
exec UpdateProblem10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@ht.com'
what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.
Please help. I have set the ProblemID as the Primary key.
View 5 Replies
View Related
Feb 16, 2007
I wrote a sproc which does four things:
1. It looks at an option master to see if the record exists before inserting a new one
2. If the record is not there it inserts the optino record
3. Once the record is inserted I have to run a CASE statement on the record to determine its level
4. Once the level is determined, the record needs to be updated with the correct level.
1-3 work fine (when run without the update command).
However, even though I set a criteria, UPDATE still updates and not the one records.
Any idea why? set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--CREATE PROCEDURE [dbo].[sp_AddNewOption_OptionMaster_WithLevel]
@BuilderIDint,
@OptionIDINT,
@CommunityID INT,
@PhaseID INT,
@SeriesID INT,
@PlanID INT,
@ElevationID INT,
@CurrentSalesPrice Smallmoney,
@LocalComments Nvarchar (500),
@RoomID int,
@Package bit,
@Active bit
AS
--check to see if the option record exists
IF EXISTS (SELECT 1 FROM optionmaster WHERE BuilderID = @BuilderID AND OptionID = @OptionID AND CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)
BEGIN
SELECT ' This option already exists in your Option Master'
END
ELSE BEGIN
--if the option record option does not exist, insert it
INSERT INTO [OptionMaster] ([BuilderID], [OptionID], [CommunityID], [PhaseID], [SeriesID], [PlanID], [ElevationID], [CurrentSalesPrice], [LocalComments], [RoomID], [Package], [Active], [DateAdded], [DateAvailable], [SalesPriceEffective], [OptionLevel])
VALUES (@BuilderID, @OptionID, @CommunityID, @PhaseID, @SeriesID, @PlanID, @ElevationID, @CurrentSalesPrice, @LocalComments, @RoomID, @Package, @active, GETDATE(), GETDATE(), GETDATE(),'10' )
SELECT ' Added to Option Master Successfully'
END
--once the option record is inserted, case it to find the its level (1-9)
--update the record with the approciate level.
UPDATE Optionmaster
SET optionlevel (
SELECT CASE WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '9' WHEN CommunityID = '0' AND PhaseID = '0' AND
PlanID > '0' AND ElevationID = '0' THEN '8' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '7' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '6' WHEN CommunityID > '0' AND
PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '5' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '4' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID = '0' AND ElevationID = '0' THEN '3' WHEN CommunityID > '0' AND
PhaseID > '0' AND PlanID > '0' AND ElevationID = '0' THEN '2' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND
ElevationID > '0' THEN '1'
END AS OptionLevel --provides the option level required to update the record
FROM optionmaster
WHERE (BuilderID= @BuilderID And OptionID = @OptionID and CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID))
--even through I specify the above criteria, it upates all records.
View 9 Replies
View Related
Aug 2, 2007
Hi,i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.This happens in event DetailsView1_ItemUpdating with my own code.It works without parameters (i know, bad way) like this:SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"But when using parameters like here below, i get the error:"Input string was not in a correct format"Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdatingSqlDataSource1.UpdateCommand = "UPDATE mytable set name= @myname"SqlDataSource1.UpdateParameters.Add("@myname", SqlDbType.NVarChar, na)SqlDataSource1.Update()End SubI don't see what's wrong here.Thanks for helpTartuffe
View 4 Replies
View Related
