|
Can somebody tell me how to get this done on SQL reporting.
I need to print the column headings vertically like this H
E
A
D
I
N
G
instead of this - HEADING
thanx
|
|
|
|
|
I wrote a stored procedure to do a simple update to a table in one of my dbs:
ALTER PROCEDURE usp_cmrapprupd<br />
(@id nvarchar(50),@name int,@appr nvarchar(1))<br />
AS<br />
UPDATE approvers SET approver_identifier = @name, approved_by_approver = @appr<br />
<br />
WHERE identifier = @id
now, with the exception of @id being an nvarchar, this stored procedure is identicle to another one for another table. the only difference between the two tables is that one uses integers as the id, and the other uses nvarchars.
the update on the int table works just dandy, but the one listed about keeps giving me the following error:
Violation of PRIMARY KEY constraint 'PK_approvers'. Cannot insert duplicate key in object 'approvers'.
What is giving me a headache is how am I violating primary key constraints if i am doing an update rather than insert?
Update:
OKay, I discovered a consistancy. The table uses both identifier and approver_identifier as primary keys. If i try to update something with a single instance of identifier, it works (example, identifier 1-1 has 3, 1-1 with approver_identifier of 1, 1-1 with approver_identifier of 2, etc, i get the error. however, if i have an identifier of 4-1 and only an approver_identifier of 1, the update works)
Any ideas?
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Okie dokie... Iguess this works the same as venting to a stranger.. Putting things out for others to see makes you look it over in a new light. I figured it out:
ALTER PROCEDURE usp_cmrapprupd<br />
(@id nvarchar(50),@name int,@appr nvarchar(1))<br />
AS<br />
UPDATE approvers SET approved_by_approver = @appr<br />
<br />
WHERE identifier = @id AND approver_identifier = @name
Apparently, the proc didnt know which field to update (which makes sense when you dont specify both identifiers....)
This procedure works like a charm. Thanks for letting me complain 
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
hi all,
I wonder if anyone can help with this one, alls im trying to do is add a parameter to a query, i have tried for days but cannot get this to work!!
heres my code;
myConnection = New MySqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
strSQL = "SELECT SubCatID,CategoryID,SubCatName,Multibuy,SubCatPic FROM SubCategory WHERE CategoryID = ?CategoryID AND Multibuy='Single' ORDER BY SubCatName, SubCatID;"
Command = New MySqlCommand(strSQL, myConnection)
myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
Command.Parameters.Add("?CategoryID", 1)
Command.Parameters("?CategoryID").Direction = ParameterDirection.Input
I get the error ;
Parameter '?CategoryID' must be defined
i have tried everyway i think possible, PLEASE i hope someone can help or point me in the right direction!
Thanks.
Jetset
|
|
|
|
|
Hi there. Do you need to issue the .Prepare() method after setting up your Command?
|
|
|
|
|
.Prepare method?
Out of all the searches Ive done on google ive not seen this mentioned before?
could you expand on this?
Thanks.
|
|
|
|
|
Hi there. I may have offered that too soon - I saw it used in some MySql .NET Connector examples and thought it may be something to look at. As I look at the .chm documentation, it indicates that the method is not yet supported (?) But then there is a different section that describes the following:
4.3.2. Preparing Statements in MySQL Connector/NET
To prepare a statement, create a command object and set the .CommandText property to your query.
After entering your statement, call the .Prepare method of the MySqlCommand object. After the statement is prepared, add parameters for each of the dynamic elements in the query.
After you enter your query and enter parameters, execute the statement using the .ExecuteNonQuery(), .ExecuteScalar(), or .ExecuteReader methods.
For subsequent executions, you need only modify the values of the parameters and call the execute method again, there is no need to set the .CommandText property or redefine the parameters.
So now I'm not sure which is right.
|
|
|
|
|
Mike,
Thanks alot, you have helped me out loads with that, my parameter code was correct but becuase I wasnt using the execute read method it wasnt working!
It is now!
Thanks alot
Jet set
|
|
|
|
|
Hi all,
i want to export the result of a query to an excel/csv file.if nyone has an idea about it, please let me know. need it badly..
thanks & regards,
manasi
|
|
|
|
|
if csv is ok for you just create a normal txt file with values seperated by comas and change file extension to csv.
|
|
|
|
|
This works with ADO,
CString sql = _T("SELECT * INTO [Excel 8.0;DATABASE=" + strExcelFileLoc + <br />
"].[" + strWorksheetName + "] FROM [" + strTable + "]");<br />
<br />
DB->Execute(sql.AllocSysString());
The results are outputted strait to the excel file specified by strExcelFileLoc.
Hope this helps....
-------------------------------
|
|
|
|
|
Hello,
I have a child table that contains rows related to a parent table. I would like, in a single SELECT query for the parent table, to have two counts, one for rows in the child table that match a certain criteria, and another that match some other criteria.
A simple example, a table of Classrooms, with a child table Students, where each student is either male of female; and the query should return ClassroomID, NumMales, and NumFemales.
I can easily get only one count (males for example) using GROUP BY with something like:
SELECT COUNT(StudentID) AS NumMales FROM Students
GROUP BY Sex WHERE Sex = 0but I can't find a way to get two different aggregate functions to work on the same table.
In my case it's actually a bit more complicated, because I have three tables related. But a little pointer on how to do it with two tables should help me get started.
Any ideas? Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Like this:
SELECT COUNT(StudentID) AS Females,<br />
(SELECT COUNT(StudentID) AS Males<br />
FROM dbo.Students<br />
WHERE (Sex = 'M')) AS Males<br />
FROM dbo.Students AS Students_1<br />
WHERE (Sex = 'F')
|
|
|
|
|
|
Is there anything like C# style ternary operator in SQL?
|
|
|
|
|
|
Very many thanks for your great answer, Colin
|
|
|
|
|
I created a dts package that imports data into sqlserver. I run the dts package from a vb.net application on a machine that does not have sqlserver installed. I get the error quoted above. I used an installer project to install my vb.net application on the machine running the application.
Included in the folder with my exe is dtspkg.dll, Interop.DTS.dll, as well as some other config and dll files. Why am I getting this error?
|
|
|
|
|
It sounds like you need to redistribute the DTS files.
The interop library is just a component which uses these files.
Have a look at this[^].
|
|
|
|
|
Exactly what I needed. Thanks 
|
|
|
|
|
I am using Access to work in a large SQL database. There are stored procedures in the db. When I run them from Access, I get a message saying the procedure has executed and has not returned results (which is correct). Problem is that examining the table it is apparent only a small portion of the rows were updated.
If I run the procedure from osql, everything is fine. There are no parameters, it is simply copying a very large amount of information. I suspect Access is timing out.
Is there any way to see exactly what Access is doing when I run the procedure?
|
|
|
|
|
When i run my crystal report from my installed application it prompts me for database logon information. Does anyone know how to get rid of this so it just uses the connection string
everything else is using??????
|
|
|
|
|
Heres the crack:
During data binding on a repeater control, the RepeaterItem's DataItem property is populated with the current DataRowView that it has enumerated over whilst and during the invokation of the OnItemDataBound routine and ItemDataBound event.
This provides very handy access to the data that this UI component is building its visual dependancy on.
HOWEVER, the moment that this RepeaterItem has finished its data binding and is added to the repeater controls Items collection, its DataItem property is de-referenced and you can no longer gain access to the underlying data.
Now I understand that this may be to encourage good practice in the reliance upon using ViewState, but I just don't have the time to fanny about with all these loose type castings when all I want to do is cast the DataItem{ DataRowView }.Row to my strong typed row from my dataset so I can immediately work with the Repeater controls Items collection to determine the state of controls and work directly with their associated data!
WHY is the DataItem property de-referenced?
Still, a workaround for me at the moment is the handy correlation between the DefaultView's row indexing and the RepeaterItem's ItemIndex property, ALA:
(ItemDataBound Event Context):
ctype(ctype(e.Item.bindingcontainer, DataList).datasource, MyDataTable).Item(e.Item.itemindex) is ctype(e.Item.DataItem, DataRowView).Row
returns True.
Why does it have to be like this!??! Why not just keep the reference in DataItem of the RepeaterItem class? DataList is also the same. Have I missed something totally blatant here?
I look forward to your views on the subject!
|
|
|
|
|
Hi,
I have, for example, a Stored Procedure wich validates users into my web.
I'd like to validate the users's input in order to enhance security.
What would be the best way to validate the input inside the Stored Procedure?
for example I'd like to make sure that numbers are actually numbers and username like "joe" isn't something like "'; DROP table;--"
thanks,
H
|
|
|
|
|
hhrafn wrote:
for example I'd like to make sure that numbers are actually numbers and username like "joe" isn't something like "'; DROP table;--"
Well, if you are using stored procedure this shouldn't be too much of a problem. You are passing the parameters to the stored procedures correctly, aren't you? I mean your code is something like this:
SqlCommand cmd = new SqlCommand("MyStoredProcName");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@userNameParam", "joe");
If you add parameters in this way you are protected from a first order attack. If your command string is something like
EXEC MyStoredProcName @userNameParam = 'joe' then you should replace it with code like the first example above.
In your stored procedure you can then have code like this
IF EXISTS (SELECT * FROM UsersTable WHERE UserName = @userNameParam)
BEGIN
-- Do stuff based on the user existing
END
ELSE
BEGIN
-- Do stuff based on this being an incorrect user
END
For more information see SQL Injection Attack and Tips on How to Prevent Them[^]
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
