|
Hi all
I have a database named information and every row in this database has a unique id!
I want to update the row with the special id(that I have stored that id,for example it is in "ID" variable), but I dont know how should I use the select and update command to do this!
plz help me!
|
|
|
|
|
Start by providing the statements you are trying to use; others can only guess at what the table looks like.
|
|
|
|
|
Provide more details like table structure & sample data
|
|
|
|
|
The basic update statement is
update <yourtable>
set <yourfield>=<yourvalue>
where <conditional>
You probably want something like "where ID = 5"
Without more details, this is the best I can do to help.
|
|
|
|
|
ok,for example I have an information table with columns(id,fname,lnam) and I want to access the (fname,lnam) by Id,because I want to use them as string;
I know that I should the select command Like this:
select(id,fname,lname)WHERE id="" but I dont know how to access the fnam,lname with this Id as string!
|
|
|
|
|
BEGIN TRANSACTION
CREATE TABLE Person
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50)
)
INSERT INTO Person VALUES ('John', 'Doe'), ('Code', 'Project')
SELECT *
FROM Person
UPDATE Person
SET FIRSTNAME = 'Peter'
WHERE ID = 1
SELECT *
FROM Person
ROLLBACKExecute in SQL-Management Studio. You'll see two tables; one before, and one after the update. You will need a good book that introduces you to some basic SQL-constructions.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi all
I have a problem with database.I work on " add new user.aspx "file witch add a new user to the table(Authenticate)but it doesnt work! this is my code in add new user.aspx file:
<script runat="server">
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
string connectionstr=@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\EHSAN\Documents\Visual Studio 2010\Projects\The Club\The Club\App_Data\Database1.mdf;Integrated Security=True;User Instance=True";
con.ConnectionString=connectionstr;
string sqlstring;
sqlstring ="insert into Authenticate (username,password)VALUES ("+ CreateUserWizard1.UserName+","+CreateUserWizard1.Password+")";
System.Data.SqlClient.SqlCommand objcommand = new System.Data.SqlClient.SqlCommand(sqlstring, con);
Response.Redirect("~/Login.aspx");
}
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="cphMain" Runat="server">
<asp:CreateUserWizard ID="CreateUserWizard1" Runat="server"
oncreateduser="CreateUserWizard1_CreatedUser">
</asp:CreateUserWizard>
</asp:Content>
I execute that but it doesnt add any new row to my table!!!!
plz help me!
|
|
|
|
|
I suspect you need to put apostrophes around the values, but the better (much much better) solution is to use a parameterized command.
|
|
|
|
|
thank you so much
but I dont know how should I use parameter!for example I have the CreateUserWizard1.UserName variable but I dont know how should use it as parameter! can you say me how should I do it?
|
|
|
|
|
ur missing objcommand.ExecuteNonQuery(); and parenthesis in code
try this one
<script runat="server">
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
string connectionstr=@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\EHSAN\Documents\Visual Studio 2010\Projects\The Club\The Club\App_Data\Database1.mdf;Integrated Security=True;User Instance=True";
con.ConnectionString=connectionstr;
string sqlstring;
sqlstring ="insert into Authenticate (username,password)VALUES ('"+ CreateUserWizard1.UserName+"','"+CreateUserWizard1.Password+"')";
System.Data.SqlClient.SqlCommand objcommand = new System.Data.SqlClient.SqlCommand(sqlstring, con);
Con.Open();
objcommand.ExecuteNonQuery();
Con.Close();
Response.Redirect("~/Login.aspx");
}
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="cphMain" Runat="server">
<asp:CreateUserWizard ID="CreateUserWizard1" Runat="server"
oncreateduser="CreateUserWizard1_CreatedUser">
</asp:CreateUserWizard>
</asp:Content>
|
|
|
|
|
thank you so much,it works
I had 2 mistake:
1:in insert command
2:in execute command
thank you so much again
|
|
|
|
|
This version is still susceptible to SQL Injection[^]. For example, try a password of:
Robert');DROP TABLE Authenticate;--
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Avoiding SQL Injection[^] isn't hard:
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
const string connectionstr = @"...";
const string sqlstring = "insert into Authenticate (username, password) VALUES (@username, @password)";
using (var con = new System.Data.SqlClient.SqlConnection(connectionstr))
using (var objcommand = new System.Data.SqlClient.SqlCommand(sqlstring, con))
{
objcommand.Parameters.AddWithValue("@username", CreateUserWizard1.UserName);
objcommand.Parameters.AddWithValue("@password", CreateUserWizard1.Password);
con.Open();
objcommand.ExecuteNonQuery();
}
Response.Redirect("~/Login.aspx");
}
Once you've fixed that problem, you then need to reconsider how you're storing the passwords. Currently, you're storing them as plain text, which is a terrible idea. If anyone managed to gain access to your database, they would be able to see every password used on your site.
Instead, you should be storing a salted hash of the passwords:
http://crackstation.net/hashing-security.htm[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am receiving an Overflow error from an INSERT statement. I understand this is because a value that is assigned to a variable is larger than the data type, correct? The database was built in Access.
I pinned the values on screen while debugging and took a screen shot, which is linked herewhich is linked here.
I have double checked the data types and they all seem to match.What am I missing?
Thank you in advance.
Here is the code:
<pre lang="c#"> oleDbComQC.CommandText = "INSERT INTO RANKS ([ACCOUNTS], [EOB], [FPD], [F90P], [PDCHECK], [INCOME], [PD], [PD90P]) " +
"VALUES(@ACCTS, @EOB, @FPD, @F90P, @PDC, @INC, @PD, @PD90)";
oleDbComQC.Parameters.AddWithValue("@ACCTS", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@EOB", OleDbType.Decimal);
oleDbComQC.Parameters.AddWithValue("@FPD", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@F90P", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@PDC", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@INC", OleDbType.Decimal);
oleDbComQC.Parameters.AddWithValue("@PD", OleDbType.Integer);
oleDbComQC.Parameters.AddWithValue("@PD90", OleDbType.Integer);
oleDbComQC.Parameters["@ACCTS"].Value = iAccts;
oleDbComQC.Parameters["@EOB"].Value = decEOB;
oleDbComQC.Parameters["@FPD"].Value = iFPD;
oleDbComQC.Parameters["@F90P"].Value = i90PD;
oleDbComQC.Parameters["@PDC"].Value = iNumChks;
oleDbComQC.Parameters["@INC"].Value = decIncome;
oleDbComQC.Parameters["@PD"].Value = iPPD;
oleDbComQC.Parameters["@PD90"].Value = iP90PD;
Jude
|
|
|
|
|
Solved: The data types in the data table were off.
Jude
|
|
|
|
|
hii my project is school management system but i dont have any about that how to start my project i wanna use oracle 10g and what are the step to create a database ???
|
|
|
|
|
Have a HOWTO[^].
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
Firstly, you have to download the Oracle Database Software. go to oracle.com and download the software.
I suggest to download 11g R2 windows version. It's more easier to install on windows than on Linux.
nothimg
|
|
|
|
|
Using SQL Server 2008 r2
I am trying to design a table, which has 2 computed column based on the product of other columns including a computed column, which SQL Server doesn't like.
CREATE TABLE [dbo].[BaseRate2](
[ProductType] [varchar](100) NOT NULL,
[LinkToRate] [decimal](18, 0) NULL,
[CopyOfRates] [decimal](18, 0) NULL,
[BCdiscountLoading] [decimal](18, 0) NULL,
[OtherLoading] [decimal](18, 0) NULL,
[ClaimsLoading] [decimal](18, 0) NULL,
[NoClaimsDiscount3Yr] [decimal](18, 0) NULL,
[NoClaimsDiscount4Yr] [decimal](18, 0) NULL,
[NoClaimsDiscount5Yr] [decimal](18, 0) NULL,
[ConstructionLoading] [decimal](18, 0) NULL,
[DiscretionaryDiscount] [decimal](18, 0) NULL,
[MinPremiumLoading] [decimal](18, 0) NULL,
[SecurityDiscount] [decimal](18, 0) NULL,
[ProductLoading] AS ((((((([ClaimsLoading]*[NoClaimsDiscount3Yr])*[NoClaimsDiscount4Yr])*[NoClaimsDiscount5Yr])*[ConstructionLoading])*[DiscretionaryDiscount])*[MinPremiumLoading])*[SecurityDiscount])
) ON [PRIMARY]
Now when I try to add the second computed column when uses the first computed column (ProductLoading) this is where SQL throws an error as it should do.
RateUsed as ([CopyOfRates] * [BCdiscountLoading] * [OtherLoading] * [ProductLoading])
Is there some other technique that I could design this table without manually calculating these product columns (ProductLoadings and RateUsed).
Thanks
Simon
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
It blows, because you're using a value in your calculation that might not be available yet. Create the table, and a view - add the second computed field to the view.
Alternatively, you could use a normal (non-calculated) field, and update it's contents using a trigger.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy,
Thanks for that suggestion (views) it worked, but I decided against the idea after following through on the documentation of the table / view as it made it more complex to maintain in this situation.
So sadly I adopted the manual calculations before entering into the table.
Thanks again 
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
As Eddie suggested the Product loading may not exists.
This is not going to make you happy. You can replace [ProductLoading] in your second field with the ENTIRE formula from the first field.
ducks and runs I told you it would not make you happy!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I understood the why it isn't working and was trying to see if there was another way to do this computationally.
while I did consider the approach you suggested, I also have to write detailed documentation around this insurance rating API and was looking for a easy / elegant solution that would make documentation easy and clear.
With the documentation in mind I decided that it would be easier for me to explain the columns and how they are calculated manually. 
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Simon_Whale wrote: easy / elegant solution
Does not describe the way sql works some times.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
