|
hi everybody,
I have a small proplem with my stored procedure that looks like this
create procedure MonthlyBill @startDate datetime, @endDate datetime as
select * from Bills where Bills.date >=@startDate and Bills.date<@endDate
the problem I have is that when I execute this query using the following command:
execute MonthlyBill '01/10/2005','30/10/2005'
I get the following error msg: Error converting data type varchar to datetime.
both @endDate and @startDate are declared as datetime variables.From where does come the problem?I anybody knows and would like to help please do!!!I'm thankfull
The will to learn
|
|
|
|
|
You need to convert from the varchar type to the datetime type. This might help.
execute MonthlyBill to_date('01/10/2005','DD/MM/YYYY') to_date('30/10/2005','DD/MM/YYYY')
Hopefully whatever DB platform you are using, there is a function, 'to_date' that will translate a varchar to a datetime. 
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Look at this example stored procedure:
<br />
create procedure MonthlyBill <br />
@startDate datetime, <br />
@endDate datetime <br />
as<br />
select * from Bills where Bills.date >=convert(datetime,@startDate, 102) and Bills.date<convert(datetime,@endDate, 102)<br />
then you can call the stored procedure like this:
exec MonthlyBill '2005/10/1', '2005/10/30'
I actually use this source[^] to use stored procedures and I forget about type cast problems. Take a look at it, it's really good.
daniero
|
|
|
|
|
One note I would like to add to the other respondents. I am not a big fan of using operators when comparing datetimes. If, for example, one of your parameters is using GetDate() (sql server) the <,> etc operators check the time. This can lead to very undesirable results! If you are not interested in the time, it is better to use DATEDIFF(dd,date1,date2) < 0. You'll find yourself chasing down less problems this way.
|
|
|
|
|
I have a windows program that basically takes data from an MS Access DB and updates a SQL Server table. Then it clears the Access Table and inserts ALL the records from the SQL Server to the MS Access DB. Everything works except the very last step. I have tried the OleDbDataAdapter.Update Method and also used a loop and a dynamic SQL statement to update the Accesss DB with no luck. Does anyone have any suggestions? I know my DataSet has data in it because I can count the rows and also look at the data using GetXML method. Code below:
Try
Dim accessDS2 As New DataSet
sqlDA.SelectCommand = New SqlCommand("SELECT nameFirst, nameLast, phonenumber, addressStreet1, addressStreet2, addressCity, addressState, addressZip, addressEmail, dateAvailable, JobFairs, areaCert1, areaCert2 FROM tb_HR_Recruits", SqlConn)
sqlDA.Fill(accessDS2)
MsgBox(accessDS2.GetXml())
Dim accessInsertCmd As String
Dim nRow As AccessDS2.tblRecruitInfoRow
Dim x As Integer
For Each nRow In AccessDS2.tblRecruitInfo
accessInsertCmd = "INSERT INTO tblRecruitInfo VALUES (" & nRow.nameFirst & "," & nRow.nameLast & "," & nRow.phoneNumber & "," & nRow.addressStreet1 & "," & nRow.addressStreet2 & "," & nRow.addressCity & "," & nRow.addressState & "," & nRow.addressZip & "," & nRow.addressEmail & "," & nRow.dateAvailable & "," & nRow.jobFairs & "," & nRow.areaCert1 & "," & nRow.areaCert2 & ")"
accessDA.InsertCommand = New OleDbCommand(accessInsertCmd, accessConn)
accessDA.InsertCommand.ExecuteNonQuery()
Next
'accessDA.Update(accessDS2)
Catch ex As Exception
MsgBox(ex.Message)
Finally
SqlConn.Close()
'accessConn.Close()
lblDone.Text = "Sync Complete."
End Try
|
|
|
|
|
Hi All
I have a simple Access database that consists of two tables, one for staff details (name, telephone, job title etc) and one as a look-up table that holds valid data for job titles (Technician, Scientist etc). I've created the database like this because I want to limit the selectable job titles in a C# ADO.Net application when data about new staff is added.
Now I want to display information about staff on a simple windows form that has say three text boxes on it. One text box displays the name, one the telephone and the other should display the job title as referred to in the look up table. I don't want to amend anything, just display the uneditable details.
Can anyone point me in the right direction of how to proceed? I have had all manner of problems with datasets and dataadapters. Anyone got or know of any examples that do the same sort of thing in C#?
Any help would be gratefully appreciated.
Billy
|
|
|
|
|
Billy Whizz wrote: C# ADO.Net application when data about new staff is added
While I'm not familiar with C# the concepts you need are pretty simple.
Billy Whizz wrote: I've created the database like this because I want to limit the selectable job titles
Use a combo box to with approved job titles; only allow items in the combo box to be selected.
Billy Whizz wrote: Now I want to display information about staff on a simple windows form that has say three text boxes on it. One text box displays the name, one the telephone and the other should display the job title as referred to in the look up table
Display your data, disable the three edit boxes. Or open the data adapter read only.
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
ANyone any idea how to manually remove SQL Native client Beta2, the remove tool fails and i cant even find the files .
Im trying to install the express sql.
Thanks
.nuetter
|
|
|
|
|
I have hard disk of 40 gb for d:.Now i am creating a test environment for a database that should hold more than 1 cr records in one of the tables.I am imorting data from flat files .It gives me primary filegroup full .So i created a secondary filegroup and created the table in this filegroup.After 40 lakh data the hard disk space left is 32 gb still it gives me filegroup full error while importing data.Databse growth is unrestricted.help
|
|
|
|
|
how can i list all sql server currently running on a network?
is there have any system stored procedure for this.
|
|
|
|
|
|
yes i have searched
and i got the following code
using System.Data;
using System.Data.Sql ;
using System;
public class Repro
{
public static int Main(string[] args)
{
SqlDataSourceEnumerator sqldatasourceenumerator1 = SqlDataSourceEnumerator.Instance;
DataTable datatable1 = sqldatasourceenumerator1.GetDataSources();
foreach (DataRow row in datatable1.Rows)
{
Console.WriteLine("****************************************");
Console.WriteLine("Server Name:"+row["ServerName"]);
Console.WriteLine("Instance Name:"+row["InstanceName"]);
Console.WriteLine("Is Clustered:"+row["IsClustered"]);
Console.WriteLine("Version:"+row["Version"]);
Console.WriteLine("****************************************");
}
return 1;
}
}
'but i not get the dll for
system.data.sql namespace
|
|
|
|
|
I'm not quite sure what you mean as the System.Data.Sql libraries ship with the .NET runtime. Have you attached the relivent System.Data components into your project using the 'Add Namespaces' menu?
Gavin Taylor
w: http://www.gavintaylor.co.uk
-- modified at 8:09 Wednesday 9th November, 2005
|
|
|
|
|
|
Hi everyone,
I am working in a company where data is changed daily (it is a shopping center) where item prices change everyday, quantity in stock vary daily,...
What is the scenarios available for the back up on SQL Server 2000 (I am a developer not a database administrator, but I have a task to do which is to make a back up for the critical data) .
could u help me with some links or idea.
I was thinking of doing a full back up one time everyweek and daily I will do a differential backup?
I can't do a full back up daily because data is more than 20 GB and also some tables in the SQL database hold data that is very critical and (I want to include these tables in the backup) other tables holds data not important (I don't want to include these tables in the backup).
is that the best solution.
Thanks.
|
|
|
|
|
|
I'm not a DBA, but Yes, a weekly full backup with differential daily backup is a good start. Also look into log backups maybe every hour or so. In order to do partial database backups, I think the db has to be structured into filegroups; you then can choose not to backup certain filegroups.
Also, some of the commercial backup software out there have suggestions for SQL backup.
|
|
|
|
|
Hi
I'm trying to write some data to an SQL table. I'm using MSDE.
The SQL table has mostly real (4byte) columns.
according to CLS Single.NaN is a leagle type.
For some reason I can't insert NaN values to the table records.
I'm using DataSets for the transfer and working with C#, Visual2003.
Yes, I DO need to save and restore both numbers and NaN values.
And yes, I know there are tons of work-arounds to simulate this but I really need to do this simple task.
Anyone ?
Gilad.
|
|
|
|
|
I have two tables that I am trying to read using OleDB (tableA and tableB) the primary keys for tableA are: col1, col2, col3. tableB has col1, col2, col3, col4, and col5 where col1, col2, and col3 are foreign keys. Because of large number I instances I cannot read all the instances at once. The reason is that I get cache Exception error and Db2 admin does not want to increase the size of db2 cache. I used two nested loops in order to create and execute following sql statements
String SQLSt = "SELECT tableA.col2, tableA.col3, tableA.col4, tableB.col2, tableB.col3, tableB.col4, tableB.col5, TableB.Col6
From tableA inner join tableB
On (tableA.col1 = tableB.col1 and tableA.col2 = tableB.col2 and tableA.col3=tableB.col3 AND tableA.col4 = tableB.col4) where tableA.col1 = 55 order by tableA.col2, tableA.col3, tableA.col4, tableb.col5, TableB.col6 FETCH FIRST " + 2000 + " ROW ONLY";
I use the above statement outside of the loop to read the first 2000 record.
I used following statement in a outer loop to read the following records in chunk of 2000.
SQlSt = "SELECT tableA.col2, tableA.col3, tableA.col4, tableB.col2, tableB.col3, tableB.col4, tableB.col5, TableB.col6
From tableA inner Join tableB
On (tableA.col1 = tableB.col1 and tableA.col2 = tableb.col2 and tableA.col3=tableB.col3 AND tableA.col4 = tableB.col4) where tableA.col1 = 55 and tableA.col2 >= previous_value and tableA.col3 >= previous_value and tableA.col4 >= previous_value and tableb.col5 = previous_value And TableB.col6 >= Previous_value
order by tableA.col2, tableA.col3, tableA.col4, tableB.col5, TableB.col6 FETCH FIRST " + 2000 + " ROW ONLY”;
The inner loop would read one record at a time and store the information in a memory cache.
The number of records that is read is much less than actual records for instances where col1 = 55. That is there are 45000 records and I read only 29000. If I do FECH FIRST 100 row only
Then I get about 20000 records.
1. Is there a way that I can specify in the sql statement to read record I to record I+2000?
I would appreciate any solution to this problem.
Thanks
The Ole command is:
OleDbCommand cmdIndx = new OleDbCommand();
cmdIndx.Connection = new OleDbConnection( ConfigurationSettings.AppSettings["DBConn"]);
OleDbDataReader rdrIndx;
cmdIndx.CommandText = strSQL;
cmdIndx.Connection.Open();
rdrIndx = cmdIndx.ExecuteReader();
rdrIndx.Read()
|
|
|
|
|
Hi there,
I've got the following two tables:
<br />
CREATE TABLE [dbo].[Countries] (<br />
[ID] [int] IDENTITY (1, 1) NOT NULL <br />
) ON [PRIMARY]<br />
GO<br />
ALTER TABLE [dbo].[Countries] WITH NOCHECK ADD <br />
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED <br />
(<br />
[ID]<br />
) ON [PRIMARY] <br />
and...
<br />
CREATE TABLE [dbo].[Cities] (<br />
[ID] [int] IDENTITY (1, 1) NOT NULL ,<br />
[CountryID] [int] NOT NULL <br />
) ON [PRIMARY]<br />
GO<br />
<br />
ALTER TABLE [dbo].[Cities] WITH NOCHECK ADD <br />
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED <br />
(<br />
[ID]<br />
) ON [PRIMARY] <br />
Now I'd like to establish a one to many relationship between them. I guess I know how to do that in the enterprise manager. My questions are as follows:
If I have a relationship between those tables:
- how will I go upon deleting a country record? do I necessarily have to do that within a transaction?
- shall I use the "Force relationship for INSERT and UPDATE(s)?" (roughly translated from the enterprise manager)
- shall I use the CASCADE options?
- and, If I use those options, how will they affect inserting, updating and deleting.
I know that my questions might be a bit vague. I've been trying to google for a primer on this but couldn't come up with something really helpful. I'd appreciated anyone shedding some light here. Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
matthias s. wrote: how will I go upon deleting a country record? do I necessarily have to do that within a transaction?
I can't answer this one  .
matthias s. wrote: shall I use the "Force relationship for INSERT and UPDATE(s)?" (roughly translated from the enterprise manager)
Well, you need a country to exist or not? if you do need the country to exist then...
matthias s. wrote: shall I use the CASCADE options?
Most certainly, countries won't be disappearing anytime soon (I hope  ) but in terms of programming, how important is it that you delete 50 cities if you try to delete a country? I think it's probably more important that you keep those cities. Imagine 400 records related to those cities being deleted as well .
matthias s. wrote: and, If I use those options, how will they affect inserting, updating and deleting.
Well, using enforcement of relationship will ensure inserts and updates have valid values for countryID. Deleting a country can have a serious impact on the infromation, as I stated above, is it ok for you to delete 50 cities because you delete a country? is that a good idea? you need to ask that yourself.
I think it's good practice to define relationships (the most important being enforce relationship for inserts and updates) because this makes programming easier. Cascade Delete... be very careful with this one, think if it's appropiate or not to remove information with this. Cascade Update doesn't affect much, specially if you use identity fields or GUID keys, you won't be needing this.
I just hope I don't make it more confusing  .
daniero
-- modified at 19:19 Tuesday 8th November, 2005
|
|
|
|
|
hello,
i am very new to VC++.Net. i am getting lots of bugs while working with
MsAccess Database. Here is my code fragment. i used the Connection
object from toolbox. so the connection is ok. i am able to retrieve the
data. But i failed to modify (inserting, updating and deleting).
DataSet *ds;
OleDb::OleDbDataAdapter* adapter=new OleDb::OleDbDataAdapter("select
prt_text,cut from spool",oleDbConnection1);
oleDbConnection1->Open();
adapter->Fill(ds,"spool");
DataRow *dr=ds->Tables->Item[0]->Rows->get_Item(1);
ds->Tables->Item[0]->Rows->Remove(dr);
ds->AcceptChanges();
adapter->Update(ds,"spool");
oleDbConnection1->Close();
how do i delete a record, update a record and add a record. plz help
me.
Thank u in advance.
regards,
basheer.
|
|
|
|
|
Hello everyone, i have a very strange problem when i using the full-text searching engin provided by SQL Server to search the web site.
If the keyword is directly after the tag , then there will be no matched result return. But if i change the to , then the correct result will be returned by the server. I spent whole day to solve this problem, but still get nothing.
Why this happend?
Thanks a lot!!
|
|
|
|
|
Hallo
I have 3 Tables
<br />
1st table with idfrist - descriptionfirst<br />
2nd table with idsecond - descriptionfirst<br />
3rd table with id - idfirst - idsecond - value<br />
Now i'd like to built a transformed tabel:
colheader with all items from idfirst
rowheader with all item from idsecond
values as sum from 3rd tables value (cols are( tofirst - tosecond - value)
The transformation works fine for all values entered.
But if one col from the colheader isn't used in the 3rd table,
there is no output column for this field defined in the first table ?!?
here si what is already have done with 2 tables
<br />
TRANSFORM Sum(third.value) three<br />
SELECT second.tofirst<br />
FROM third RIGHT JOIN second ON third.idsecond = second.tosecond<br />
WHERE (((third.tofirst) Like "*"))<br />
GROUP BY third.tofirst<br />
PIVOT second.idsecond;<br />
thx for helping
Tim
|
|
|
|
|
Got to this link and register free for
training your self for VS2005
https://www.microsoftelearning.com/visualstudio2005/
or
http://www.financialexpress.com/latest_full_story.php?content_id=107645
Microsoft dont give anything free except training
Vikas Amin
Embin Technology
Bombay
vikas.amin@embin.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
