Database Discussion Boards

-Dr_X-20-Feb-04 11:16

20-Feb-04 11:16

I posted this about 5 posts ago.

http://www.codeproject.com/script/comments/forums.asp?msg=739476&forumid=1725#xx739476xx

Type = 'U' are tables and Type = 'V' are Views

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Access

Anonymous16-Feb-04 16:05

Anonymous

16-Feb-04 16:05

can anyone please tell me how I can have a form in MS access 2003 with Slave master relationship. I mean just a form where I can input and edit data in two different related tables at the same time.
does it make sense?
I hope it makes sense to you.
Thank brother or sister!

Re: Access

-Dr_X-20-Feb-04 11:23

-Dr_X-

20-Feb-04 11:23

It's been a while, like 6 years if you are refering to a subform. If so, check out the help or have the wizard create one for you.

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Select MID?? instead of TOP

krisp16-Feb-04 11:26

krisp

16-Feb-04 11:26

Is there a way instead of using SELECT TOP 10

to say something like:

SELECT MID 11 10 or something, so it starts at the 11th record and takes 10 records after it.

This way I can easily put my data into pages, straight from the database, with 10 records on each page. I would also need to be able to first order them and then take 10 records starting from some nth record.

Any suggestions, returning 2000 records when i only display 10 seems very wastefull.

Thanks.

http://www.codeproject.com/aspnet/custompaging.asp
I found this link here, but this seems very inefficient as well. It creates a temp table and still selects all records and inserts them into the temp table. Regarding this way... does the # in front of the table name mean it gets dropped when the stored procedure is done running? The Author never drops it, and so if multiple people called this function at the same time, what happens to the temp table? Never seen the pound sign # in front of a table before.

Thanks some more.

Re: Select MID?? instead of TOP

John Kuhn16-Feb-04 11:46

John Kuhn

16-Feb-04 11:46

How about:

SELECT TOP 10 field1, field2 ORDER BY field1 WHERE field2 IS BETWEEN 'VALUE1' AND 'VALUE2'

...hey that could even be a stored procedure, where you pass VALUE1 and VALUE2 as params...

Re: Select MID?? instead of TOP

krisp16-Feb-04 11:52

krisp

16-Feb-04 11:52

but i have no idea what value1 and value2 would be. Maybe I have an auto-increment PK but that won't work because if some rows get deleted, then you dont have a range of 10 records knowing that a multiple of 10 records was skipped.

This wont work.

I modified my question, see the bottom of the original question, i added an example i found.

Thanks anyways.

Re: Select MID?? instead of TOP

John Kuhn16-Feb-04 15:13

John Kuhn

16-Feb-04 15:13

Doing a SELECT TOP N ... without a WHERE and ORDER BY clause is kind of trivial, and you shouldn't rely on an id or auto-increment field in an ORDER BY, as it is no guarantee of the chonological order in which records are added to a table. You should work with other fields, such as datetime fields, foreign key fields, etc.

Another question: Are you referring to paging through a DataSet using ASP.NET? If so, there are plenty of examples and articles out there on data paging and controls like the DataGrid control.

SQL Server 2000 Connection From ASP

SamAmman15-Feb-04 22:32

SamAmman

15-Feb-04 22:32

when i initialize a connection string in my ASP page to connect to SQL Server 2000 Database as follows:

ConnStr = "Provider=SQLOLEDB; Data Source=DB1; Initial Catalog=MYDB; User ID=sa; Password=0000;"

===========================================================
Then I use the open method to open the connection for the Database as follows:

connStr.open 'This is Line 20

===========================================================
An error is shown as follows:
Error Type:
(0x80040E4D) Line 20

===========================================================
What is the error in this case?
Coz no error is specified. I'm waiting for your help.

Sam2004

Re: SQL Server 2000 Connection From ASP

Marcie Jones16-Feb-04 2:59

Marcie Jones

16-Feb-04 2:59

Double-check your connection string, is your password really 0000?

Marcie

Day Job, CP!: http://www.codeproject.com
Datagrid Stuff: http://www.datagridgirl.com

Re: SQL Server 2000 Connection From ASP

Mike Dimmick17-Feb-04 6:41

Mike Dimmick

17-Feb-04 6:41

This error code is 'authentication failed'. Basically, SQL Server wouldn't let you log in.

Either your password is incorrect, or your server is configured for Windows Authentication only. If you can connect using Windows authentication, specify 'Integrated Security=SSPI' and omit the Username and Password. However, ASP will connect to the database using whichever account the script engine is running under - typically IUSR_machinename. If you don't want to deal with this, you need to configure the database server for SQL Server and Windows authentication. Right-click the server in Enterprise Manager, choose Properties and the Security tab to find this option.

It's a bad idea to log in as sa unless you're really performing system administration. Add a new login to your database (using Enterprise Manager, under Security > Logins) and give it a password. You'll then need to set the permissions for the operations that the web site needs to perform. Typically I make the new login part of the database's public role and assign that role the appropriate permissions.

Boy I have a goober of a problem.

Anonymous15-Feb-04 21:23

Anonymous

15-Feb-04 21:23

I don't know if I should put this under php or sql, but it is a little of both. I have secure info that I do not want published to a file on the server at any time even a few seconds. I just want to take it strait out of the data base and have the person click a link, to this php file below and when they go to it it prompts them to download. I want the sql info in the file. I think I have most of the code right, but it isn't quite working. As of now, it is downloading a blank file.

<br />
<?php<br />
<br />
function show_file_information($filename)<br />
{<br />
	@readfile($filename);<br />
}<br />
<br />
if(!class_exists(MySQL))<br />
{<br />
	require '../home/MySQL.php';<br />
	require '../home/conf_global.php';<br />
	$mysql = new MySQL();<br />
	$mysql->connect($INFO['sql_user'],$INFO['sql_pass'],$INFO['sql_database'],$INFO['sql_host']);<br />
}<br />
<br />
$news_result = $mysql->query("SELECT * FROM newsletters WHERE id='1'");<br />
$news = mysql_fetch_assoc($news_result);<br />
<br />
$filename = $news['add_id'];<br />
<br />
header("Pragma: public");<br />
header("Expires: 0");<br />
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); <br />
header("Content-Type: application/force-download");<br />
header("Content-Type: application/octet-stream");<br />
header("Content-Type: application/download");<br />
header("Content-Disposition: attachment; filename=".basename(emaillist).".txt");<br />
header("Content-Transfer-Encoding: binary");<br />
header("Content-Length: ".strlen($filename));<br />
//@readfile("$filename"); <br />
<br />
<br />
?>

Problem with simple SQL query :D

gamerPotatoe15-Feb-04 21:10

gamerPotatoe

15-Feb-04 21:10

Hi there I'm playing around on SQL server 2000 and I get a error when i try to run this query:

SELECT *, COUNT(P.PTICKETNO) AS counter from TICKETS_PURCHASED T,PASSENGER P WHERE P.PTICKETNO = T.TKTNO AND T.TKTNO = 'TKT000000000001'

The error is as such.

Column 'T.TKTNO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
What could be the problem guys . My SQL is very weak and I' just getting my feet wet, I would appreciate all the help Smile | :)

Re: Problem with simple SQL query :D

Mazdak15-Feb-04 21:29

Mazdak

15-Feb-04 21:29

Add a GROUP BY clause at the the end with the name of proper column and you will see that it will works. Look into GROUP BY in SQLServer Books Online for how to use it.

Mazy

You're face to face,
With the man who sold the world - David Bowie

Concatenating variables in SQL query

solostar***15-Feb-04 16:19

solostar***

15-Feb-04 16:19

SqlString = "SELECT Start1, End1, Start2, End2, Start3, End3, Start4, "
"End4, Start5, End5, Start6, End6, Start7, End7, Start8, End8, "
"Start9, End9, Start10, End10, Start11, End11, Start12, End12 "
"FROM BANDSETTING "
"WHERE BANDNAME = &",
" &m_Store ";

Hi... the above is my code. m_Store is the variable i want to concatenate.
but it doesnt work the way i want it. what's wrong?? =)

by the way, im not using VBA...

this query is called in the windows application accessing the database.

Re: Concatenating variables in SQL query

Mike Dimmick16-Feb-04 2:32

Mike Dimmick

16-Feb-04 2:32

Firstly, where possible, you should use parameters. Parameters get rid of a whole host of SQL injection and invalid character problems.

It looks like you're writing ~~a letter~~ C# or C++ code. If it's C#, you could write

SqlString = "SELECT Start1, End1, Start2, End2, Start3, End3, Start4, "
"End4, Start5, End5, Start6, End6, Start7, End7, Start8, End8, "
"Start9, End9, Start10, End10, Start11, End11, Start12, End12 "
"FROM BANDSETTING "
"WHERE BANDNAME = " + m_Store;

If it's C++, then it depends on the type of m_Store: if it's a CString or a std::string, then the same as above; otherwise, you'll probably have to use strcat.

At base, it's a string concatenation problem; use whatever your language and environment provide for concatenating strings.

Re: Concatenating variables in SQL query

Bill Dean16-Feb-04 2:34

Bill Dean

16-Feb-04 2:34

Hi,

Is this C#? If so, you need a concatination operator (+) between each pair of strings that you want to concatination:

SqlString = "SELECT Start1, End1, Start2, End2, Start3, End3, Start4, " +
"End4, Start5, End5, Start6, End6, Start7, End7, Start8, End8, " +

Bill

Free C# training

Anonymous15-Feb-04 15:14

Anonymous

15-Feb-04 15:14

http://www.dotnetspider.com/technology/ Rose | [Rose]

Re: Free C# training

John Kuhn16-Feb-04 11:49

John Kuhn

16-Feb-04 11:49

How about not posting the same advertisement in every single forum?

Hey, how about no advertising at all?

Problem in using Stream object's write method

Member 48497613-Feb-04 21:15

Member 484976

13-Feb-04 21:15

I want to write some long encryted text during my code in .doc file. I have used Stream Object and use write() method of stream object which take object of type variant as input. MY code throws exception 0x800A0BB9 when it execute write() method. Below is my code. I have spend around 1.5 days in this. Pls help me how to solve this.
If i read text from some other using Read() method of Stream object and use output of Read() method (i.e. output of Read() is Variant object) then it work find and write() is not throwing exception.

CString outileName = "myenc.doc";
ADODB::_StreamPtr outputStream = NULL;
hr = outputStream.CreateInstance(__uuidof(ADODB::Stream));
outputStream->Charset = "ascii" ;//"iso-8859-1";
outputStream->Type = ADODB::adTypeBinary;
outputStream->Open(vtEmpty, ADODB::adModeUnknown, ADODB::adOpenStreamUnspecified,bstrEmpty, bstrEmpty);

outputStream->Position = 0;

VARIANT myVt;
VariantInit(&myVt);
myVt.vt = VT_BSTR;
myVt.bstrVal = SysAllocString(L"testing");

outputStream->SaveToFile(outileName.AllocSysString(), ADODB::adSaveCreateOverWrite);

outputStream->Write(&myVt);

ODBC and TCP/IP

Kwai Cheng Kane13-Feb-04 10:25

Kwai Cheng Kane

13-Feb-04 10:25

I am using the ODBC classes in ADO.NET. I just found that some connections being made by a database maintenance program I am writing are connecting to the wrong server because they are using named pipes rather than connecting through TCP/IP using the DSN I have set up on my system.

Is there a way to FORCE the connection to always use the TCP/IP connection and never use the named pipes??? Using named pipes for this particular program can be dangerous because the program is a database management program, and it is designed connect to the desired database exclusively using the DSN. If it uses a named pipe to connect, then it could perform maintenance on the wrong database with disasterous results!!!

Listing tables and views?

Bill Dean13-Feb-04 8:08

Bill Dean

13-Feb-04 8:08

Hi all,

I have an OleDbConnection object and I would like to be able to get a list of all the tables and views available through it.

Do I need to know anything about the underlying database architecture to do this? For example, if it were mssql, I would know to query sysobjects. But if I do not know where the underlying database stores the data am I SOL?

I suspect the answer is "no" because I can get a list of tables and views when I add this data connection to my server explorer in VS.NET but I haven't a clue how the do it in code.

Thanks in advance,
Bill

Re: Listing tables and views?

-Dr_X-13-Feb-04 14:27

-Dr_X-

13-Feb-04 14:27

The easiest way is to use the following query.

Select name, type from Sysobjects
where	type = 'U' 
or type = 'V'

The other way is to use ADOX. However, I have not used it in .Net only in the old VB world.

Michael

Re: Listing tables and views?

Bill Dean13-Feb-04 15:08

Bill Dean

13-Feb-04 15:08

Michael,

Thanks for the response! I did not realize it was that easy.

Bill

Re: Listing tables and views?

-Dr_X-13-Feb-04 15:36

-Dr_X-

13-Feb-04 15:36

I would recommend doing this from a stored procedure.
Really don't want users messing around with sysobjects unless there very knowlegeable.

Michael

Re: Listing tables and views?

Bill Dean13-Feb-04 15:43

Bill Dean

13-Feb-04 15:43

If I had sql server on the back-end...I would. But we are trying to get at data in Informix...which as far as I know doesn't support sprocs.

Like I said before, I really a complete novice with this particular back-end so I was looking for a way to do this that generalized to all ado.net.

Your suggestion works great btw, even with the Informix back-end. Thanks again.

Bill

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.