|
I want to use the following update command multiple times:
command.CommandText = ("UPDATE EndOfMonth SET [Rented Depreciation] = @RD, [Unrented Depreciation] = @UD,");
command.CommandText += ("CashSales = @CS, BuyOuts = @BO, ChargeOffs = @CO WHERE StoreNumber = @SN");
command.Parameters.Add("@RD",OleDbType.Numeric);
command.Parameters.Add("@UD",OleDbType.Numeric);
command.Parameters.Add("@CS",OleDbType.Numeric);
command.Parameters.Add("@BO",OleDbType.Numeric);
command.Parameters.Add("@CO",OleDbType.Numeric);
command.Parameters.Add("@SN",OleDbType.VarChar);
How do I assign values to this command after defining it?
I can assign values to it at this time, but if I call it again, I will be adding more and more parameters, correct?
I am google searching the earth and this site, but only had 3 returns for OleDbCommand UPDATE.
Thanx in advance
Jude
|
|
|
|
|
command.Parameters.Item("@SN").Value = "MyNewValue"
|
|
|
|
|
|
Ok now I am stumped about something. Here is the parameter:
command.Parameters.Add("@RD",OleDbType.Decimal);
Here is the assignment:
command.Parameters["@RD"].Value = decTotDepr; (holds a decimal value of 40873.77)
The field is defined as Number, Field Size: Decimal, Format: General Number, Precision: 18(left at default), Scale: 0, Decimal Places:2 (in access).
When writing to the database, it stores only the integer part(40873).
Do I need to add more information to the definition of the parameter?
Jude
|
|
|
|
|
After scouring the earth for information on scale and finding nothing, I found ONE line defining what scale was in my 900+ page SQL 2000 book and nothing in my 400+ page access book. Just something little I guess you forget when you don't program everyday!
Jude
|
|
|
|
|
Hi Everyone.
I am trying to delete data from datagrid
here is my code:
DataTable tbl = new DataTable("timesheet");
tbl = ds.Tables[0];
int i = this.iRowIndex
tbl.Rows[i].Delete();
this.daDisplay.Update(tbl);
I am getting an exception: "there is no row at position 0",whereas there is data in the database...what can be the proble,I can't seem to figure it out.
pls help..
|
|
|
|
|
Well, you create the table as a standalone datatable, and then you point it to ds.Tables[0]. If you haven't actually read anything from the database then this will be the problem.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi Everyone.
I am trying to delete data from datagrid
|
|
|
|
|
SQL Server usage is ...
ALTER TABLE tablename ALTER COLUMN columnname datatype
also the new column added to the table will have NULL initially.
however after updating all existing NULLs with some data, the column can be changed to NOT NULL
|
|
|
|
|
thanks for the reply.
i have one more question to ask.
can i drop the primary key constraint on a column without actually dropping the column??
|
|
|
|
|
Yes.
ALTER TABLE tablename DROP CONSTRAINT constraintname
to know the Primayr key constraint name
SP_HELP tablename
|
|
|
|
|
thanks man ur the best
|
|
|
|
|
I have written the following code but it doesn't seem to be working. I think it's pretty clear what it is supopposed to achieve. I believe the error to be purely syntax. Any ideas?
ALTER PROCEDURE [dbo].[SP_PermutationsUnconfiguredSelect] <br />
-- Add the parameters for the stored procedure here<br />
@UserID nvarchar(5),<br />
@Type nvarchar(15),<br />
@Credit nvarchar(15)<br />
AS<br />
DECLARE @Query varchar(500)<br />
DECLARE @Where varchar(500)<br />
<br />
SET @Where = ''<br />
SET @Query = 'SELECT PermID, ([Type] + " with "+ Credit + " credit for " + Purpose)As Permutation<br />
FROM Permutations<br />
WHERE PermID NOT IN (SELECT PermutationID FROM Products WHERE UserID = ' + @UserID + ')'<br />
<br />
BEGIN<br />
IF @Credit = 'Both'<br />
SET @Where = 'AND [Type] = ' + @Type<br />
ELSE<br />
SET @Where = 'AND [Type] = ' + @Type + ' AND Credit =' + @Credit<br />
<br />
EXEC(@Query + @Where )
|
|
|
|
|
You can put in a PRINT statement before (or temporarily in place of) the EXEC to see what is actually being generated
PRINT(@Query)
PRINT(@Where)
I think that probably the " is what is causing confusion. When expanded out the " is being seen as defining a column name. You can use [ColumnName] and "ColumnName" to define explicit column names, especially ones that contain odd characters or spaces.
|
|
|
|
|
As you are using dynamic SQL (and I don't see any reason why you need to do that), you need to put apostrophes around your variables. So, the where clause becomes WHERE UserID = ''' + @UserID and so on.
Also, the BEGIN doesn't need to be there - in fact, it causes an error.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi all,
I have a problem that have haunted me for days and no suggestion I tried from CP worked... Please help!
I have installed SQL Express 2005 on Win2003 server and created a database. Authentication is in Mixed Mode. No firewall.
Here is what happened:
LOCALLY: (login as administrator)
1. I can open it using SQL Server Management Studio.
2. I SUCCESSFULLY connect to it programmatically (VC++) with any of the following connection strings:
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Trusted_Connection=yes;<br />
REMOTELY on XP SP2: (login as administrator and with SQL Server Native Client installed)
1. I can open it using SQL Server Management Studio (in both modes, SQL and Windows).
a. This confirms that I have correctly set up and enabled remote connection.
b. I enabled remote connection using both TCP/IP and named pipes.
c. I enabled Shared memory, Named pipes, and TCP/IP protocols.
d. I enabled and activated SQL Server Browser
2. I FAILED to connect programmatically. Here are some connection strings I've tried:
(most error messages: "Login failed for user 'admin'." (which is a renamed 'sa').
<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
Provider=SQLOLEDB;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
Provider=SQLOLEDB;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Uid=myUsername; Pwd=myPassword;<br />
<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Trusted_Connection=yes;<br />
Provider=SQLOLEDB;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Trusted_Connection=yes;<br />
<br />
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Trusted_Connection=yes;<br />
Provider=SQLOLEDB;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Trusted_Connection=yes;
I've read thru www.connectionstrings.com and I don't think I missed anything, did I?
Did anyone have similar problem before, or have any idea why?
Thanks a lot.
|
|
|
|
|
I finally found and fixed the problem. It was my original hunch, that 1433 is not the right LISTENING port (although the setting of SQL Express still points to 1433). To check the port, run cmd.exe and look at the result of "netstat -ano".
Hopefully someone find this useful and don't need to spend days figuring this out.
|
|
|
|
|
Hi,
Is there any way to store the unicode characters in sql DB.
I want to store the unicode characters in sql DB and show it back to the user again.
Thanks in advance.
Be simple and Be sample.
|
|
|
|
|
Look into the nchar, nvarchar, and ntext data types.
|
|
|
|
|
Yes I tried with nText and stored in database. While retrieve usenig select, it will not return the unicode characters it shows some '?' mark characters.
Be simple and Be sample.
|
|
|
|
|
I just ran the following update on my local database. It has one user, me, and the update query took 57 seconds to update 111k records. That seems pretty long too me, so I assume the cost of the update is in the newid() function call. Pretty expensive.
|
|
|
|
|
Can you provide the update statement.
|
|
|
|
|
You might want to provide the actual update statement. It might be something else that you are doing that is expensive. I've updated hugh quantities of rows with a NEWID() before and it wasn't this long.
|
|
|
|
|
Expensive queries tend to be the result of not using key fields in the filter criteria. Have you viewed your execution plan to see what it is doing? If you are doing a sequential scan, this will be the cause of your problem.
I really don't see the problem being with the newid() function.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
It was a table scan, but redoing the column addition and running the update again gave a time of 2s. The first one is probably due to some housekeeping work my very underused server was doing. The query is:
alter table myDoc<br />
add<br />
GlobalDocID uniqueidentifier default newid()<br />
go<br />
update myDoc<br />
set GlobalDocID = newid() where GlobalDocID is null
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
