|
In other words, create a stored procedure with parameters?
|
|
|
|
|
That would be one way. Or you could use a regular insert statement with parameters. If you're using Sql Server for example, your CommandText could look like this:
INSERT INTO MyTable
(Column1, Column2, Column3)
Values
(@val1, @val2, @val3) In this example, @val1, @val2, and @val3 would be the parameters. Then you add SqlParameter objects to your SqlCommand's Parameters collection (I like using the method AddWithValue like this psudo-code:
SqlCommand cmd = new SqlCommand(...);
cmd.CommandText = "INSERT INTO...";
cmd.Parameters.AddWithValue("@val1","theValue");
cmd.Parameters.AddWithValue("@val2",intOtherValue);
cmd.Parameters.AddWithValue("@val3",dateTimeThirdValue);
|
|
|
|
|
Ok...had to vpn to the office and grab the code and database.
Heres what I have in C# Code
SqlCommand cmdUpdateTime = new SqlCommand("SetTime",conn);
cmdUpdateTime.Parameters.Add("@EmpNumber",SqlDbType.Int).Value = m_intEmployeeID;
cmdUpdateTime.Parameters.Add("@TimeIn",SqlDbType.SmallDateTime).Value = DateTime.Now;
switch(DateTime.Now.DayOfWeek)
{
case DayOfWeek.Wednesday:
cmdUpdateTime.ExecuteNonQuery();
break;
}
conn.Close();
}
catch (SqlException caught)
{
MessageBox.Show(caught.Message,"Error");
}
and here is the Stored Procedure:
ALTER PROCEDURE dbo.SetTime
(
@EmpNumber int,
@TimeIn smalldatetime
)
AS
INSERT INTO Emptime(EmpID,WedIn)
VALUES(@EmpNumber,@TimeIn)
RETURN
What I am getting nw is the error:
Incorrect syntax near "SetTime"
???
Thanx for all your help!
|
|
|
|
|
Try setting the CommandType of your SqlCommand object to CommandType.StoredProcedure.
|
|
|
|
|
Thanx a bunch! Hopefully I may be able to pay the favor forward one day 
|
|
|
|
|
|
Ok..Now I don't understand this...I am getting an error in the Stored Procedure Designer
ADO error:Incorrect syntax near DECLARE
ALTER PROCEDURE dbo.SetTime
/* (
)
*/
DECLARE @MySqlStr nvarchar(100)
RETURN
I had more commands and variablea, but I kept on getting the same error, so I cut it down to just that one line.
Here is the whole thing:
CREATE PROCEDURE db.SetTime
(
@EmpId int,
@TimeIn smalldatetime,
@NameOfTable nvarchar(10)
)
DECLARE @MySqlStr nvarchar(100)
SET @MySqlStr = "INSERT INTO " + @NameOfTable + "( EmpID,TimeIn) VALUES(@EmpId,@TimeIn)"
EXEC sp_sqlexec @MySqlStr
I googled and searched, but this error message seems very dynamic, and I do't get it...
Thanx again
|
|
|
|
|
Are you leaving out an AS keyword?
CREATE PROCEDURE myProc
(
@var1 int
,@var2 nvarchar(10)
)
AS
BEGIN
DECLARE @myVar nvarchar(100)
-- ... blah blah blah ...
RETURN
END
|
|
|
|
|
syntax,syntax,syntax...
Thanx
|
|
|
|
|
Ok Got it working (first module) as far as functionality. But I have a problem with what is actually writing to the database. Here is the command in question:
cmdUpdateTime.Parameters.Add("@TimeIn",SqlDbType.SmallDateTime).Value = DateTime.Now.ToShortTimeString();
Using the debugger, after this command, this parameter is in the format "HH:mm:ss AM/PM", but is being stored as "MM/DD/YYYY HH:mm:ss: AM/PM"
Why is this?
Thanx again ( and again...)
|
|
|
|
|
Hi,
I have 2 tables, t_a, t_b:
They are linked by a common_idd fieldd.
I want to find out the rows in t_a, but not in t_b.
My sql like:
select * from t_a where t_a.common_id not in (select common_id from t_b)
it works, but because there are so so many rows in both of the 2 tables, it runs so so so slow!!!
I am using Access, is there any way to implement the same function???
please help me!!
Thank a lot!
Alan Shen
MCAD for .NET Version
^~^~^~^~^~^~^~^~^~^~^
Great idea is the beginging of success!
|
|
|
|
|
Since it's Access, you may not be able to speed it up anyway!
But try this
select * from t_a
where not exists ( select t_b.common_id from t_b
where t_b.common_id != ta.common_id
)
As long as the field common_id is INDEXED on both tables it should be much faster.
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
BAD DAY FOR: Friendly competition, as Ford Motor Co. declared the employee parking lot at its truck plant in Dearborn, Mich., off limits to vehicles built by rival companies. Workers have to drive a Ford to work, or park across the street. [CNNMoney.com]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi,
I have created 2 tables in Visual Studio 2005, and am attempting to access the Database Diagrams. When I am not connected to our network domain, I get the message, "This database does not have a valid dbo user... Do you want to make yourself the dbo of this database in order to use database diagramming?" I then select "Yes", but then I receive the message that, "This database does not have a valid dbo user, or you do not have permissions to impersonate the dbo user".
As soon as I connect to our network, I have no problems. But If I want to take my work home, I cannot access the Database Diagrams.
My question is, how do I create a valid dbo user in Visual Studio 2005 SQL Express edition?
Thanks in advance,
Rashar
-- modified at 11:56 Wednesday 8th February, 2006
|
|
|
|
|
How can we retrive the day(Sunday,Monday....etc) of a date
|
|
|
|
|
You can use the following code:
<br />
lblDay.Text=Format(Now().Today, "dddd")<br />
|
|
|
|
|
Is it in C# or SQL?
In C#, use the DayOfWeek[^] property.
In T-SQL use DATEPART[^] to get the weekday part.
I hope this helps!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Hi all,
im having problems using a alias name to perform calculations in the select clause. My code is as follows:
SELECT<br />
<br />
CASE WHEN STK_SORT_KEY2 = 'TREND' THEN ([OD_UNITCST] * (1 - ISNULL(dbo.TrendDiscount.Discount, '0.32'))) ELSE 0 END as Expr2, <br />
[Expr2] / [OD_UNITCST] AS Expr11<br />
<br />
FROM...<br />
My question is how do use a field to store calculations which i can reference later in the select clause. I keep getting a Invalid name 'Expr2' error
Any ideas?
Thanks in advance,
|
|
|
|
|
I think you need to enter the full calculation for your Expr11 field. I don't think you can refer to it by the alias.
|
|
|
|
|
Hi,
I'm using a OleDbDataReader to get values from a MS Access db.
I've noticed that if the db field is "null" I get an error, so I
put in: myReader.IsDBNull (see code below). Is there a better way to code this null value protection? (besides forcing no nulls in db)
- working with C#
while (myReader.Read())
{
if (myReader.IsDBNull(0) == false
cost = myReader.GetDecimal(0);
}
thanks,
Ron
|
|
|
|
|
what u used is the best method one more thing u can do ie cast the result to string now no error will come but its not correct. checking for null values and then assigning is the proper usage
VMSSanthosh
|
|
|
|
|
|
Hi
We have a MySql database (A) on a Linux machine and a MS Access database (B) on a windows machine. One of the tables in each of these two databases must be synchronised constantly. In A new records are added but no other modifications to records are done. In B records can be added, modified or deleted. At the moment we are trying a C# application that constantly compares the two tables by loading them in a dataset and basically comparing them record by record. I do not think this is very accurate and efficient.
Does anybody have ideas for us? It will really be greatly appreciated.
Kobus
|
|
|
|
|
One thing that comes to mind is the possibility of adding the MySql table to your Access database as an ODBC linked table. You could then, within Access, create a query that outer joins the two tables such that you can identify the records which exist in one but not the other... with a single INSERT query then you can bulk add all those records.
You could do something similar to look for records that have been modified.
|
|
|
|
|
iam generating a crystal report using VB 6.0 SQL server 2000 and crystal report 8.0 the stored procedure iam using is working fine the report is also working when i opened it in crystal report but when i run the application before generating report it shows an error "ERROR DETECTED BY DATABASE DLL" what is it?
|
|
|
|
|
Hi:
How can i can connect to an oracle Server in visual studio 2005?
thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
