|
Baz wrote:
SqlDataReader
First of all, shouldnt you be using an OledbDataReader rather than an SqlDataReader? The SqlClient namespace classes are intended only for use with SQL Server, and it is surprising to me that they work at all with Access. Not sure how you are constructing the string that caUses the escape sequence to be ingored (you're not prefixing with an @ are you?), but try this:
public const string sQuote = "\"";
public string QuoteString(string strToQoute)
{
return sQuote + strToQuote + sQuote;
}
...
x.CommandText = "Select ID from Company where CompanyName = " + QuoteString(strCompany);
....
|
|
|
|
|
Thanks for that Rob - yeah my bad! I am actually using the OleDbDataReader and not the SqlDataReader class in my code. I was trying to get this question up there before CSI came on the TV!!! (it comes on at 9pm here in the UK)
Anyway, the method you have presented there is the same method I am using, and the CommandText that gets passed to Access is actually:
SELECT ID FROM Company WHERE CompanyName=\"Microsoft\" and obviously not what I want!
EDIT: Woah! After rebooting my machine, it now seems to pass through the correct string... i.e SELECT ID FROM Company WHERE CompanyName="Microsoft"
Not sure what happened there... anyway, the query never finds the company regardless of the name supplied, even though the company is in the database! Any ideas on this one? I have seen a similar situation when searching on Google, but couldn't find a fix
|
|
|
|
|
Can't you use SqlParameter instead? I'm not 100% sure whether it works with Access but it's safer than using concatenated Sql strings and you don't need to worry about the quotes.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
I think you might be onto a winner here... it was the next thing to try on my list, if I didn't get this working
Cheers!
Baz
|
|
|
|
|
char qoute ='"';<br />
<br />
cmdFindLenders.CommandText = "SELECT ID FROM Company WHERE CompanyNameID=" + quote + <br />
<br />
strCompany + quote;
|
|
|
|
|
I’m having a problem updating a record in my AAH_CNTRCT_EVNT_LOG table. I know its because of the datetime. I’m using datetime as one of my keys. The Dates I physically put in the table work just fine but when I add a record I use a store procedure that adds the date using DECLARE @NOW datetime SET @NOW = GETDATE()and those are the ones that seem to give me the problem when updating. Dose anyone know how to fix this problem? 
Here's the store procedure for the update:
(
@iAahCntrctId int,
@sAahCntrctEvntNM varchar (50),
@dAahCntrctEvntLogDT datetime,
@sAahCntrctEvntLogCmnt varchar (500),
@sAahCntrctSignLN1NM varchar (20),
@sAahCntrctSignLN2NM varchar (20),
@sAahCntrctSignLN3NM varchar (20),
@sUpdtUserID varchar (8)
)
AS
DECLARE @EMPTY INT
DECLARE @BLK CHAR
DECLARE @BLKDT DATETIME
DECLARE @NOW datetime
SET @NOW = GETDATE()
SET @EMPTY = 0
SET @BLK = ''
SET @BLKDT = ''
UPDATE AAH_CNTRCT_EVNT_LOG
SET AAH_CNTRCT_EVNT_LOG_CMNT = @sAahCntrctEvntLogCmnt,
AAH_CNTRCT_SIGN_LN_1_NM = @sAahCntrctSignLN1NM,
AAH_CNTRCT_SIGN_LN_2_NM = @sAahCntrctSignLN2NM,
AAH_CNTRCT_SIGN_LN_3_NM = @sAahCntrctSignLN3NM,
UPDT_USER_ID = @sUpdtUserID,
UPDT_DTTM = @NOW
WHERE AAH_CNTRCT_ID = @iAahCntrctId
and AAH_CNTRCT_EVNT_NM = @sAahCntrctEvntNM
AND AAH_CNTRCT_EVNT_LOG_DT = @dAahCntrctEvntLogD
|
|
|
|
|
This should work. What error are you getting? (You are missing a "T" at the end of the UPDATE statement. I assume that is a copy/paste error on Code Project).
Does UPDT_DTTM have a unique constraint? If so, are you adding records so fast that you are violating the constraint?
|
|
|
|
|
We had populated the date field using the GetDate() function in SQL during the insert of the record. We were using a datetime type variable in C# to hold the date until we updated the record by calling the SQL statement. When we tried to update the record using the held variable and passing it to the SQL statement to the where clause, it could not find the record to update it.
We resolved the whole issue by changing the way the date field was poulated when it was inserted into the database. We removed the GetDate() function from the SQL and passed a date variable as a string to the datetime type field on the SQL statement. When we updated the record this time, it worked.
Thank you for your help in trying to resolve this.
|
|
|
|
|
If you could show how the procedure is invoked, that might help. How exactly is the date variable passed in the call?
As an aside, logging things like a create_date or update_date, I usually will do that using triggers instead of implementing them within the create or update procedure. It's an extremely subtle difference, but I'd be interested in knowing if others agree or not.
Chris Meech
I am Canadian. [heard in a local bar]
Nobody likes jerks. [espeir]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Thanks for replying to the question. We were able to resolve it by changing the SQL insert statement used to write(insert) the record. We removed the GetDate() function and used a string variable to hold the date read. Then we passed it into a datetime type variable in the SQL statement. Without changing the update SQL statement, we were able to update the record.
|
|
|
|
|
I have an existing SQL production database. During new developments, new fields/tables/stored procs etc have been added.
Now I want to merge these changes into the production server (without loosing data).
I could write a program to do it, but is there an easy way?
|
|
|
|
|
We use SQL Compare[^].
The alternative is of course to save a script every time you make a modification, or simply to make the changes using a script in the first place, but that's not really an option for you now.
The only other free alternative - the way we used to do it - is to export the database schema to a file using the Generate Script feature from both the old database and the new, and use a diff tool (such as WinDiff) to compare them. This will at least show what's changed. Then you write another script which uses ALTER DATABASE/ALTER TABLE/etc as appropriate to make the necessary changes.
|
|
|
|
|
hey guys,
How can i get the values oif multiple columns in rules? I was Googling up rules and all i could find was the method to use single column. Let me give you an example:
In an table i have many columns, three of which are X,Y & Z. I want to create a rule that checks if the sum of three columns are greater than 1000. If i do manage creating a rule and bind it to column X - how do i access the value of Y and Z from my Rule. Could some give explain. A peice of code would be really helpful.
Thanks.
|
|
|
|
|
I wanna ask a question bout datareader and dataset.
If i execute 1 Sp with 2 queries in it as
select * from employee
and
select * from Company bothe
this SP's result i execute in dataset as well as datareader at a time. so what o/p it will show in each -datareader and dataset ?
JAX
|
|
|
|
|
Jax_qqq wrote: so what o/p it will show in each -datareader and dataset ?
It will show the same output unless the database changed between runs.
DataReader provides a forward only view of the data. The DataSet is a container that is used by a Data Adapter so that the result set can be accessed in a random access within the application.
|
|
|
|
|
Hi,
I want to run Job using any command prompt utility. Such type of utility is there in SQL Server 2000?
I know about DTSRun.exe is used to run DTS package, but I want to run Job as a whole.
Thanks.
modified 15-Sep-13 3:09am.
|
|
|
|
|
You mean the OSQL command line utilty?
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
How ca i remove spaces in my database ?
my database : visualfoxpro 6.0
CDatabase_Name::CRecordset
(MFC)
col1 = "name"
col2 = "city"
col3 = "country"
tank you in advance !
Bravoone
|
|
|
|
|
How to reset a database's identity value to 0, after deleting all rows from a table.
FIRE
|
|
|
|
|
I don't know the exact way but one workaround is there. You change that column identity to no and again reset it to yes, next time it will start from one.
Best Regards,
Apurva Kaushal
|
|
|
|
|
|
I not sure is there way to show percentage of my sales.
I have done a SQL on showing the total sales and now i want to calculate the percentage of total for each department. Example
TOTAL sales= $100
Vendor1 =$20
Vendor2 =$10
........
how can correctly show another column that will calculate the percentage?? I have try many method but not working... i was thinking to write as
sum(ti.totalsales)/sum(sum(it.totalsales))*100 as precentage
is SQL able to support those complicated calculation? or i have to do it in vb...
---HEre is the basic show the total sales with out percentage...
select d.deptdesc,v.[name],ti.deptcode ,sum(ti.totalsales) as Total from
tot_item ti , dept d, item i, vendor v
where ti.itemcode=i.itemcode and v.vendorid=i.vendorid and
ti.deptcode=d.deptcode
group by v.[name], ti.deptcode, d.deptdesc
ORDER BY v.[name], ti.deptcode ASC
|
|
|
|
|
The sample query you list has both Department and Vendor in it (you could get departments listed more than once if they use different vendors). You are trying to get a percentage of total for each department but your sample result set lists no percentages and values are listed by vendor. Are you sure you know what you really need?
Given your 2nd line and assuming you are using SQL Server, first calculate a good total sales per department.
SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)
Now write a query that calculates total sales.
SELECT
SUM(ti.totalsales) AS FullTotal
FROM
tot_item ti
Now put them together
SELECT
dep.deptdesc,
(dep.DeptTotal /
(SELECT
SUM(ti.totalsales)
FROM
tot_item ti)) * 100 AS PercentOfSales
FROM
(SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)) AS dep
ORDER BY
deptdesc
Note: I am using a more modern form of the JOIN syntax. There are issues with expressing the JOIN in the WHERE clause. You can make this a lot cleaner using variables in a stored proc.
|
|
|
|
|
Sorry - forgot the GROUP BY clause in the aggregate:
SELECT
d.deptdesc,
SUM(ti.totalsales) AS DeptTotal
FROM
tot_item ti
INNER JOIN
dept d
ON (ti.deptcode=d.deptcode)
GROUP BY
deptdesc
This will also need to be modified in the final query.
|
|
|
|
|
You can try something like this:
DECLARE @TempTotal int
SELECT @TempTotal=sum(ti.totalsales) from ti
select d.deptdesc,v.[name],ti.deptcode ,(sum(ti.totalsales)/@TempTotal) as Total
from
tot_item ti,
dept d,
item i,
vendor v
where
ti.itemcode=i.itemcode and
v.vendorid=i.vendorid and
ti.deptcode=d.deptcode
group by v.[name], ti.deptcode, d.deptdesc
ORDER BY v.[name], ti.deptcode ASC
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
