|
I am trying to execute a crosstab query in SQL Server 2000, but alot of the columns display null values for every row. This is an example of what I am getting:
Test | Prompt | 0412-0001 | 0412-0002 | 0412-0003
-------------------------------------------------------
BTEX | Benzene | NULL | NULL | ND
ph VALUE | ph VALUE | NULL | NULL | 7
And what I want displayed is the following:
Test | Prompt | 0412-0003
-------------------------------
BTEX | Benzene | ND
ph VALUE | ph VALUE | 7
(I think the formatting is screwing up with the columns, but | indicates a column separator)
I am using the stored procedure found here:
http://www.sqlteam.com/item.asp?itemID=2955[^]
I do not know much about SQL programming and I was wondering if there is a way to modify this stored procedure to eliminate all columns that contain no data.
Nick
|
|
|
|
|
|
 hi,dear buddies:
I have finished some instroductory books on SQL, but need some practice.
Do you guys have any suggestions on where I can find some good exerciese or quizs or real-world cases I can play with? OR any exercises based on the pre-installed database in SQL-server2000 (such as pub,northwind)?
Thanks a lot.
Waiting online !
Dennis
|
|
|
|
|
I currently strip out any quote marks from data input (and many other characters) when sending data to the database but would like the user to be able to enter quote marks if they wish.
I use mostly stored procedures to update data but often compose sql on the fly for ad hoc searching. Can anyone tell me how I should be handling the characters which are used in T-SQL such as ' and %
Thanks in advance - Dave
|
|
|
|
|
|
Nothing but porn on that link.
|
|
|
|
|
|
Can anyone tell me how can i read the DataTable of a Dataset..
Let's Say i used an adapter to access a dataset ,i put a query to the database (through the adapter)and the results are then stored in the Dataset's dataTable(after filling it)...How can i read the contents of the datatable(each row) and pass it to a parameter of mine..Let's Say i want to read each cell and return it to my String* Reader variable..
PLs help guys...It's an emergency..
Keep up the good work..
Thnx in advance
May it be your fate to live in interesting times
|
|
|
|
|
|
Thnx Colin.It does help a lot..
One more thing.I am working C++.Net and i want to organise the Connection to the database and the results of a specific SQL Statement through the adapter and the table in a single class... TO BE MORE SPECIFIC
#pragma once
class DBConn
{
public:
DBConn(void);
~DBConn(void);
public:
String* Type;
int Cost;
public:
String* FigureUserType(String* SenderNo)
{
SqlConnection * myConn = new SqlConnection(S"data source=(local);",S"integrated security=true;initial catalog=SMSCenter");
myConn->Open();
SqlDataAdapter * daResults = new SqlDataAdapter();
daResults->SelectCommand=new SqlCommand("SELECT Type FROM Subscribers WHERE PhoneNumber=@SenderNo",myConn);
SqlParameter* SenderParam=new SqlParameter();
//Set Parameter Properties
SenderParam = daResults->SelectCommand->Parameters->Add(
new SqlParameter("@SenderNo", SqlDbType::VarChar));
SenderParam->Value=SenderNo;
SenderParam->SourceVersion = DataRowVersion::Current;
//Create The Dataset to Fill
DataSet * dsresults = new DataSet();
//Fill the DataSet
daResults->Fill(dsresults,"Apotelesmata")
//Create The datarow
DataRow* drResults=new DataRow();
for(int i=0;>= dsResults->Tables->Item ["Apotelesmata"]->Rows->Count;i++)
{
myAuthorRow = myDataSet->Tables->Item["Apotelesmata"]->Rows->Item[i];
String* RetType=myAuthorRow->Item ["SenderNo"]->ToString ());
}
return RetType // Suppose we have only one Result
};
But I don't know where in the class i should declare the following...
using namespace System::Data; // Generic ADO.NET definitions, such as DataSet
using namespace System::Data::SqlClient; // SQL Server data provider definition
namespace system and so goes on...
PLS HELP
May it be your fate to live in interesting times
|
|
|
|
|
How can I do it? Please tell me.
Thank you
Nothing
|
|
|
|
|
|
I am using ADO.NET to access a SQL Server 2000 database in which there is a table Orders whose primary Key 'OID' is incremented automatically, so when I insert a new order I don't specify its OID. However, I need to know the OID of the inserted order to add items to it. Isn't there a function or something to do that ? Thank you.
Ahmed Said Hefny
Computer Engineering Student
Cairo University
|
|
|
|
|
You can use IDENT_CURRENT('table_name') to get the last automatically generated value inserted into the table that you specify.
Probably the best way to do this is to create a stored procedure to do the increment and then get the value
CREATE PROCEDURE InsertOrder
-- Set parameters for the values to be inserted here
AS
INSERT INTO Orders( column_names )
VALUES ( parameters containing the data to be inserted );
SELECT IDENT_CURRENT('Orders');
GO
Then in your .NET application you can use the SqlCommand.ExecuteScalar() to get the value out.
Does this help?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Hi All,
I'm working on a c# application that suppose to store and retrieve images in the sql server database. By choosing a column of type image, it's size is defaulted to 16bytes but images usually are larger than that. How can I increase the size of an image column so that it can hold images of some 10+kb? Plz answer and oblige.
Thanks
|
|
|
|
|
|
An image column can hold up to 2GB of data, IIRC. As Colin says, the 16 byte value is the pointer to where the data actually lives. Image data is not held on the same data page as the rest of the row - you should be aware that more I/O will occur if you use text, ntext or image columns.
Having said that SQL Server has a feature 'text in row' which allows you to specify that up to a certain amount of data will be placed in the row itself, not on separate pages.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
As a work around u can save the path to the images which will reduce the size of the db and also reduces the effort to handle images in sql.
i hope this helps.
|
|
|
|
|
I have a VB.Net windows app that retrieves data from 3 different tables. The SQL command uses a 3 table join. The results from the query is filled into a datatable and later populated into a combobox.
The SQL uses the values from a date field inside a text field, for example, 01-15-2005. So, each time I submit the subroutine it executes the SQL command and VB.Net code to retrieve the required data. The date field changes according to the user’s needs.
There’s no consistency when the error pops up.
The error message I’m getting when it executes, da.Fill(dataTable), “There’s no row at position 0”,. The information I’m on the stack track is as follow: Calling SQLDisconnect --- OleDB UnInitialize not called!!!
Connection Object destroyed
If I copy the sql into a sql query tool it does retrieve the required data according to the date field.
Here’s a sample of the subroutine:
cmdQueryINVOICE = _
"SELECT PAYITEM.TRANSACT_NUMBER, " _
& "PAYITEM.ACCOUNT_NUMBER, " _
& "PAYITEM.PAY_DATE, " _
& "LSEINVH.INVOICE_NUMBER " _
& "FROM LSEINVH, PAYITEM, TRANSHDR " _
& "WHERE " _
& " LSEINVH.INVOICE_NUMBER > '" & 75000 _
& "' AND LSEINVH.STATUS <> '" & sStatus _
& "' AND LSEINVH.INVOICE_NUMBER = TRANSHDR.INVOICE_NUMBER " _
& " AND PAYITEM.PAY_DATE > '" & InvoiceDates.StartDate _
& "' AND PAYITEM.PAY_DATE < '" & InvoiceDates.EndDate _
& "' AND PAYITEM.COMPANY = '" & sCompany _
& "' AND PAYITEM.TRANSACT_NUMBER = TRANSHDR.TRANS_NUMBER order by PAYITEM.PAY_DATE desc "
Dim sqlQuery As OleDbCommand = connDf.CreateCommand
Dim dtInvDate As New DataTable()
Dim iInvCnt, x As Integer
Dim sUnitQuery As String
Dim daInv As New OleDbDataAdapter()
Try
daInv = New OleDbDataAdapter(cmdQueryINVOICE, connDf)
daInv.Fill(dtInvDate)
Dim LoopRow As Data.DataRow
Dim test As String = dtInvDate.Rows.Item(0).ToString
For Each LoopRow In dtInvDate.Rows
cboInvoice.Items.Add(LoopRow.Item("INVOICE_NUMBER").ToString + " " + LoopRow.Item("PAY_DATE"))
Next
Catch e As Exception
Dim errMsg As String = e.Message
Finally
connDf.Close()
End Try
Thanks, for any help!
|
|
|
|
|
|
Thanks for your suggestion. I decided to retrieve the entire results from the query and fill the dataset, so I don't experience the problem.
Thanks again for the recommendation of sql injection I'll look into to it, but this is a windows application I thought sql was only for web application?
|
|
|
|
|
Hi there,
I'm trying to access a CSV file using jet from my C++ app using ADO. Here is my connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<my db="" directory="">;Extended Properties=text;"
I have also tried:
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=<my db="" directory="">;Extensions=asc,csv,tab,txt;"
(where <my db="" directory=""> is the path and name of the directory where the csv file is stored)
The query is, eg, "select * from MyTable" where MyTable.txt exists in the directory given in the control string.
The error message I get is: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
This seems odd to me because it seems like the name of the file it is trying to open is empty. But I know it's going to MyTable.txt because if I rename MyTable.txt I get the error message:
"The Microsoft Jet database engine could not find the object 'MyTable.txt'. Make sure the object exists and that you spell its name and the path name correctly."
More information:
-I am logged in as Administrator and have full access rights to MyTable.txt.
-This is the very first query I do
-The error occurs in the call to RecordSet::Open, ie when I query the file, not when I open the connection
-If I change the connection string and nothing else, I can connect and query an MSDE DB without any problems, so I think my ADO is OK.
Does anyone know what's going on?
|
|
|
|
|
connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csDir +
";Extended Properties=\"text;HDR=Yes;FMT=Delimited;\";"
where csDir is the full path (excluding the file name) to the file
HDR = YES if first line is column names, FMT=Delimited to indicate comman delimited,
note the escaped embeded quotes.
Query: "select * from [myfile.txt]" - USE THE FULL FILE NAME ENCLOSED IN BRACKETS
Hope this helps...
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Thank you Rob, I *think* that helped... I now get a different error message "No value given for one or more required parameters (1)" which sound more like it could be a problem with my query than actually accessing the file, is this right? Do you know what this error means? The query I am using is:
SELECT [Language] FROM [myFile.txt];
And the first line of the text file is:
"Student","Language"
Also, it's rather unfortunate for me that getting the query to work requires having the .txt on the end of the file name. My app needs to work with either an MSDE or a CSV db and I was hoping I could just reuse the queries. Is there any smarter way to do this than using a macro or calling a function inside every query, like this or something?
<br />
CString from(CString table) {<br />
#ifdef MSDE<br />
return _T("FROM ") + table;<br />
#else CSV<br />
return _T("FROM ") + table + _T(".txt");<br />
}<br />
|
|
|
|
|
Sadly, Microsoft messed this up, SQL Access, Excel, and CSV all follow different rules in some of the details (Excel wants a $ appended to the worksheet name for the 'Table' name ), so conditional code seems the only answer.
Not sure about the error message, sounds like it is interpreting [Language] as a parameter name. Does your connection string include the HDR=1 part to indicate that the first row is column names?
What happens if you "Select *" instead of specifying the fields?
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
What the....