Database

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

29-Jan-05 1:35

What the....

Here is the link again: http://www.codeproject.com/useritems/SqlInjectionAttacks.asp[^]

Reading Dataset's DataTable

ZisisGR28-Jan-05 0:22

ZisisGR

28-Jan-05 0:22

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

Re: Reading Dataset's DataTable

Colin Angus Mackay28-Jan-05 0:36

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

28-Jan-05 0:36

DataTable table = myDataSet.Tables[0];
foreach(DataRow row in table.Rows)
{
    // Do what you want with each row.
}

This assumes that there is only one table in the result set.

Does this help?

Re: Reading Dataset's DataTable

ZisisSim28-Jan-05 3:33

ZisisSim

28-Jan-05 3:33

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

Convert SqlDataReader to DataSet

ILoveSing27-Jan-05 22:47

ILoveSing

27-Jan-05 22:47

How can I do it? Please tell me.
Thank you

Nothing

Re: Convert SqlDataReader to DataSet

Colin Angus Mackay27-Jan-05 22:51

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

27-Jan-05 22:51

Why don't you just use a data adapter to fill the dataset in the first place?

Determining the ID of an inserted row

Ahmed Hefny27-Jan-05 21:26

Ahmed Hefny

27-Jan-05 21:26

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

Re: Determining the ID of an inserted row

Colin Angus Mackay27-Jan-05 22:58

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

27-Jan-05 22:58

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?

saving and retrieving image data

Rashid_Mehmood27-Jan-05 20:01

Rashid_Mehmood

27-Jan-05 20:01

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

Re: saving and retrieving image data

Colin Angus Mackay28-Jan-05 0:39

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

28-Jan-05 0:39

Rashid_Mehmood wrote:
it's size is defaulted to 16bytes

That is the size of the pointer to the image, not the image.

Re: saving and retrieving image data

Mike Dimmick28-Jan-05 2:38

Mike Dimmick

28-Jan-05 2:38

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

Re: saving and retrieving image data

Mr.Cooper9-Feb-05 1:01

Mr.Cooper

9-Feb-05 1:01

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.

datatable only working occasionally

lxhan27-Jan-05 18:49

lxhan

27-Jan-05 18:49

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!

Re: datatable only working occasionally

Colin Angus Mackay27-Jan-05 23:08

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

27-Jan-05 23:08

You should tell us what the database is. There may be subtle differences between them.

Your code is most insecure. It is the potential subject of a SQL Injection Attack. You should read this article about how to prevent them: http://www.codeproject.com/useritems/SqlInjectionAttacks.asp[^]

Is your connection open before calling the Fill on the data adapter? If the connection is closed when you call Fill then the data adapter will open the connection, perform the action and then close the connection again. So the Close statement in the finally block is redundant.

Re: datatable only working occasionally

lxhan1-Feb-05 3:33

lxhan

1-Feb-05 3:33

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?

Querying CSV db with Jet and ADO

wilsonian27-Jan-05 12:26

27-Jan-05 12:26

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?

Rob Graham31-Jan-05 5:15

31-Jan-05 5:15

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

wilsonian31-Jan-05 8:50

31-Jan-05 8:50

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 />

Rob Graham31-Jan-05 11:14

31-Jan-05 11:14

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

wilsonian31-Jan-05 11:53

31-Jan-05 11:53

Select * works.

If I change the name of the Language column to UnlikelyToBeAKeyword and then select on that, with or without square brackets, it still gives the same error.

I have HDR=Yes but HDR=1 gives the same result.

Actually it seems I can put whatever rubbish I want in the Extended Properties string and it just gives that "No value give for one or more required parameters" error. I don't think that string is working properly.

Here my connection string is again in case I've done something daft:

connection = _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myTextDB\\;Extended Properties=\"text;HDR=1;FMT=Delimited;\";");

Can you see anything wrong with it?

Rob Graham31-Jan-05 14:44

31-Jan-05 14:44

if select * works, then the connection string is likely ok.
Possibly something in a 'data' row? an '@' or '?'character perhaps?
try adding IMEX=1 to the extended properties section to force all column datatypes to become strings (IMportEXport flag). Normally the provider scans the first 8 rows to determine 'numeric' columns, and could get tricked. Forcing all string datatypes gets around the 'too smart' provider. This is usually needed only for excel files, but may apply to csv as well...

One last possibility: for CSV fies, the provider normally expects an extension of .csv, or at least anything other than .txt. The reason for this is that it looks for an identically named .txt file for 'schema' definitions to use. Perhaps the filename is causing some confusion, select * works because the code takes a different path...

Looking back at code I have used in the past, your connection string looks fine (I have used 1 and YES interchangably as well).

The only thing I see different is that I always used .csv for the extension, and when writing, deleted any .txt files to avoid having the schema file overriding user changes on subsequent reads. If it exists, colum definitions in the .csv file must match those defined in the .txt file exactly or read and write fails...

At any rate it sounds like a bug.

Wish I could help more, but I am a bit mistified too.

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

wilsonian31-Jan-05 16:34

31-Jan-05 16:34

You don't think the fact that I can add "foo" into the extended properties is a bad sign?

I do have question marks in some of my strings. Is this a problem? MSDE didn't seem to care.

A file with .csv extension behaves the same as .txt. IMEX=1 makes no difference either.

Rob Graham1-Feb-05 2:42

1-Feb-05 2:42

wilsonian wrote:
You don't think the fact that I can add "foo" into the extended properties is a bad sign?

At least one could hope it was just gracefully ignoring things it did not understand in the connection string. Smile | :)

wilsonian wrote:
do have question marks in some of my strings. Is this a problem? MSDE didn't seem to care.

I would hope that ? marks in data would be ok, in field names or elsewhere in the query they could be mistaken for parameter placeholders.

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

wilsonian1-Feb-05 8:52