Re: sql exec - Database Discussion Boards

Colin Angus Mackay29-Dec-04 1:33

29-Dec-04 1:33

RomanD wrote:
Every time I run the code below, it tells me "No update permissions!"

I would guess that there is an error in the SQL statement.

I would also guess that err does not necessariliy indicate a permissions problem, but your code assumes that any error must be a permissions problem. You should find out what err actually is, and find out what that error number actually means.

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

Re: asp ado problem.

mysorian29-Dec-04 10:57

mysorian

29-Dec-04 10:57

Could you update using this SQl from within MS Access?

Re: asp ado problem.

mysorian29-Dec-04 10:57

mysorian

29-Dec-04 10:57

Could you update using this SQl from within MS Access?

Re: asp ado problem.

RomanD29-Dec-04 12:37

RomanD

29-Dec-04 12:37

Here is my newest code:

sql="SELECT * FROM NEWS WHERE ID="&jon&";"
Set rsSrc = Server.CreateObject("ADODB.Recordset")
rsSrc.Open sql, oDBConn,0,3
if(not(rsSrc.EOF))then
rsSrc("DATE")= Now()
rsSrc("CONTENT")=Request.Form("test1")
rsSrc.update
end if
rsSrc.close
set rsSrc=nothing

Now, the error message I get is that my database/object is readonly. But I think that's a problem with my host.

Re: asp ado problem.

Mike Dimmick30-Dec-04 3:58

Mike Dimmick

30-Dec-04 3:58

I suspect that the user account that your ASP page is running under - typically IUSR_machinename - doesn't have NTFS write permissions to the directory that contains the .MDB file. Hence the database is opened read-only and you can't insert new rows or update or delete existing ones.

The account needs to be able to write to the directory, not just the file, as the Jet database engine needs to create an .ldb file if multiple connections access the file concurrently.

Stability. What an interesting concept. -- Chris Maunder

sql exec

SudeepPradhan28-Dec-04 13:59

SudeepPradhan

28-Dec-04 13:59

I normally do like this..

if exists ( select * from Table1 )<br />
begin<br />
...<br />
end

But now I don't know Table1 until runtime so I did

declare @sql nvarchar(50)<br />
set @sql = 'Select * from'+@Table <br />
if exists ( exec (@sql) )

How can I achieve last line of code?? It is not working? How can I check the condition?

Re: sql exec

Javier Lozano28-Dec-04 14:40

Javier Lozano

28-Dec-04 14:40

You can use the @@ROWCOUNT value to see if any row exists (@@ROWCOUNT <> 0). If you need to know if the table exists, I suggest you use the sysobjects table to see if the table exists. Here's a simple example:

<br />
<br />
DECLARE @sql varchar(50)<br />
DECLARE @table varchar(50)<br />
SET @table = 'Employees'<br />
SET @sql = 'SELECT * FROM '+ @table<br />
<br />
IF EXISTS(SELECT * FROM sysobjects WHERE [name] = @table AND xtype = 'U')<br />
BEGIN<br />
	EXEC (@sql)<br />
	IF @@ROWCOUNT <> 0<br />
	BEGIN<br />
		PRINT 'DATA FOUND!'<br />
	END<br />
END<br />

Hope this helps!

~javier lozano
(blog)

Re: sql exec

Colin Angus Mackay28-Dec-04 14:44

Colin Angus Mackay

28-Dec-04 14:44

It isn't something SQL is very good at and the answer, at least as best as I can find it, is not pretty.

DECLARE @result int
DECLARE @sql nvarchar(500)
SET @sql = 'IF EXISTS(SELECT * FROM '+@Table+
           ') SELECT 1 AS Result ELSE SELECT 0 AS Result';
CREATE TABLE #Result (Result int)
INSERT INTO #Result
EXEC(@sql)
SELECT @result = Result FROM #Result
IF @result = 1
BEGIN
    -- This code is run if there are existing records in @table
END

DISCLAIMER: I typed this directly into Code Project, there may be errors

[EDIT]
Actually, after seeing Javier's solution I think that his is better on security grounds as it also helps protect against secondary sql injection attacks. Although, I would say that if your table contains a lot of rows then it would be slower as Javier's solution is returning all the rows in the table out of the Stored Procedure it is contained within in order to work out the number of rows.
[/EDIT]

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

Adding user-defined property to Access db

sharonz28-Dec-04 6:06

sharonz

28-Dec-04 6:06

Hi,

I would like to know how I can add/read user-defined properties to an MS Access DB using ADO.NET. I need to add a property such as version.
In DAO, one could do that using the "CreateProperty()" function, but here I'm stumped.

Thanks!

ADO.net in unmanaged code

MARROT28-Dec-04 4:42

MARROT

28-Dec-04 4:42

Hello
I think it's possible to export a .Net dll and use it from unmanaged code, using the utility tlbexp.exe, or regasm.exe, that export and registers it.

I'm trying to export System.Data.dll, that contains the most part of ADO.net, in order to use it in a MFC application. So, I do:

regasm /verbose /tlb:systemdata.tlb "c:\....\System.Data.dll"

but, it answers:

type SQLDebbuging was exported.

Why only SQLDebbuging type is exported? What happens with all the others?
Is there any way to use ADO.NET from unmanaged code?

Thank you very much for your help.

Same SQL Statement in access and in vb.net return difference result

Mekong River27-Dec-04 21:30

Mekong River

27-Dec-04 21:30

Hi, now I have a big trouble in vb.net and access. I have one table call tblTest with the following field:

ID        Name
N01       Kagna
N02       Bopha

When I run the following SQL statement in access query: SELECT * FROM tblTest WHERE tblTest.ID LIKE 'N*' then the result return both row. But when I run the same SQL statement from VB.NET it did not return any row. But if I run the following SQL statement SELECT * FROM tblTest WHERE tblTest.ID LIKE 'N01' from VB.NET then it return 1 result (the N01). I don't what is the problem that cause in VB.NET, I really in a difficult situation now because the result did not return as what I expected. Here is my code:

Dim cnnSearch As New ADODB.Connection
Dim rstCat As New ADODB.Recordset

With cnnSearch
			.Open(strConnection)
			.CursorLocation = ADODB.CursorLocationEnum.adUseClient
		End With

rstCat.Open("SELECT * FROM tblTest WHERE tblTest.ID LIKE 'N*'", cnnSearch, _
				ADODB.CursorTypeEnum.adOpenKeyset, _
				ADODB.LockTypeEnum.adLockOptimistic)

Note: I'm using VB.Net and connect to database using ADODB.

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: Same SQL Statement in access and in vb.net return difference result

WoutL28-Dec-04 0:38

WoutL

28-Dec-04 0:38

You could try the MS-SQL syntax, and use % instead of *:
SELECT * FROM tblTest WHERE tblTest.ID LIKE 'N%'

Wout Louwers

Re: Same SQL Statement in access and in vb.net return difference result

Mekong River28-Dec-04 10:16

Mekong River

28-Dec-04 10:16

I also try to use it, but it is not working. But as far as I know use % if we working with SQL server. But in this case I use Ms access so I have to use * instead of %. What I wonder is the same statement in Ms access is working while in vb.net is not working... Until now what I suspect is because I did not use ADO.NET in VB.NET. I use COM technology by adding the reference from the COM tab of Add new reference dialog. As the code above, if I use in vb6 is working fine, may be I have to test ADO.NET in vb.net make sure it is working or not.

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: Same SQL Statement in access and in vb.net return difference result

Mekong River28-Dec-04 14:56

Mekong River

28-Dec-04 14:56

Many thankkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk Blush | :O

Now it is working with your suggestion. Thankkkkkkkkkkkk alottttttttttttttt Rose | [Rose]

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Querying CSV file using ODBC

Wayne Phipps27-Dec-04 9:12

Wayne Phipps

27-Dec-04 9:12

I don't know if anyone here can help me, I have a working application that uses an ODBC connection to retrieve data from a CSV file.

The only problem I have is that it always treats the first line as Column Names but our company gets sent data from different sources, some include Column Names, others don't.

I would like to make the application more flexible and enable it to work with either format files without having to write a schema.ini file for each import.

The connection string is something like:

<br />
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\MyTest;"<br />

Can the connection string be changed to indicate if column info is not included?

Regards

Wayne Phipps
____________

Time is the greatest teacher... unfortunately, it kills all of its students
LearnVisualStudio.Net

MS-Access insert problem

Imtiaz Murtaza27-Dec-04 0:38

Imtiaz Murtaza

27-Dec-04 0:38

Friends,
I am using MS Access for the first time. Before that,i used SQL-server. The problem is that, in SQL-server, i always ran an insert query successfully. But this query is not running in Access. Letme explain the problem.

In SQL server i have a simple table "Students" with fields 'id', 'StudentName', 'FatherName'. The 'id' field is primary key and is set to "Auto Increment". Now if i need to insert a row in this table i don't need to supply the value of id (if it is set to Auto increment). I always successfully used following query to insert row in this table:

insert into Students values ("Bill", "Gates");

So far so good. Now i have same scenario in MS-Access, same table, same fields and similarly 'id' field is of type "Auto Number". Now, whenever i run the insert query wrote above, MS-Access gives me error that i've not supplied complete arguments. Yes, if i manually give the value of "id" field, the query runs successsfully.

Please tell me how to get rid of manually giving of id value in case of MS-Access.

Imtiaz

Re: MS-Access insert problem

Rob Graham31-Dec-04 3:54

Rob Graham

31-Dec-04 3:54

You need to specify the names of fields being inserted:
Insert into Students ([StudentName],[FatherName]) values ("Bill", "Gates");

Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright

how to use ADO Event with VC6.0?

深蓝26-Dec-04 19:26

深蓝

26-Dec-04 19:26

how could we use the ado event while using VC6.0?
please give a exsample if possible

Crystal Reports...

dSolariuM26-Dec-04 15:41

dSolariuM

26-Dec-04 15:41

Hello,
I want to use crystalReport10 in my VC++ app.
But after instert it shows me a white paper only,
Can any one help me?
Happy Christmas.
Thanks.

Sql variable Value Problem

Hemant_Mane200325-Dec-04 3:14

Hemant_Mane2003

25-Dec-04 3:14

Hi friends,

I am working on a project where i have to run following code int Sql Server 2000

declare @v int
declare @tab_name char(15)
set @tab_name = 'Employee_master'
declare @str varchar(50)
set @v=exec('select empid from '+@tab_name+' where empid = 6 ')

after getting value of employeeid i have to perform some task. So i want that empid value in variable @v

another solution i found make temparory table and then insert that value in table and then retrive again in variable @v.
But it is very logn step and it make my query heavy coz i have to perform more task after that.

Is there any solution to retrive that exec(------) value in @v variable

Thank You Very much for reading my request Smile | :)

Hemant Mane

Looping Through Table Display Category Hierarchy

tech@untouchable24-Dec-04 9:35

tech@untouchable

24-Dec-04 9:35

I have my category hiearchy stored within a table called categories. What I need to do is display the actual category hiearchy with a single sql statement.

For example:
I have the following data in the table

category | category_no | category_above
---------------------------------------------------
Computers | 1 | 0
Dell | 24 | 1
Sony | 48 | 1
Pentium 4 | 33 | 24
Pentium 4 | 85 | 48

How would i query a database to sort the records as follows (in the category hierarchy)?

Computers, Dell, Pentium 4, Sony, Pentium 4

so i could then generate text that looks like

Computers
-->Dell
----->Pentium 4
-->Sony
----->Pentium 4

Anyone have any ideas? Right now I have a function that loops through, but the problem is it's using too many resources...I have to open a database connection each time i loop through. So if I had 500 categories i'd be opening 500 database connections, which i don't want.

Any help?

Re: Looping Through Table Display Category Hierarchy

sreejith ss nair27-Dec-04 19:05

sreejith ss nair

27-Dec-04 19:05

Try to normalize yout category table. That will be a better solution for this.
Once you normalize, you can show the data in hierarchy. Then use Datarelation to show the data.

Sreejith Nair
[ My Articles ]

Re: Looping Through Table Display Category Hierarchy

Anonymous28-Dec-04 5:13

Anonymous

28-Dec-04 5:13

Can you please explain a little further and give me an example...

How would it work say if I had two tables:

If the first table would carry the top level categories (Main) and the second table would then keep the sub level categories...how would that benefit me? I want to make sure that I don't limit the users from creating category levels.

Re: Looping Through Table Display Category Hierarchy

sreejith ss nair28-Dec-04 16:07

sreejith ss nair

28-Dec-04 16:07

Try to use DataRelation class for further reference.

Sreejith Nair
[ My Articles ]

SqlConnection string?

murali_utr24-Dec-04 2:14

murali_utr

24-Dec-04 2:14

hi,

while connecting in the local machine there no porblem in the connection string. while connecting to the sql server from another machine through network. i got error of "login failed to uesr null". Sql Server is Windows authentication. Connection string is given below. what's the change need in connection string or sql server. bother machines are windows 2000. any one help to me.
strConnectionString = "Integrated Security=SSPI; Persist Security Info=False;Initial Catalog=atc;Data Source=" + m_strServer + ";User ID=sa; Password=;";

Have A Nice Day!
Murali.M

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