|
I cant answer all your questions but i can give you a few ideas.
1. Yes this is correct. ADO recordcount is not like it was in the older DAO. It basically returns the recordcount to the current cursor location so yes you need to call recordset.movelast before you can get a count. It should work for you like this.
Just as a side issue which may help i prefer to set all the recordset properties before opening the recordset. Its more readable eg
set rs = new recordset
rs.cursorlocation = adUseCLient
rs.cursortype = adOpenDynamic
rs.locktype = adLockOptimistic
rs.open sql,connection
2 & 6. try to look at it logically. Define a string variable to hold the SQL eg
dim sSQL as string
sSQL = "SELECT * FROM " & TableName & " WHERE ID = " & CStr(id)
rs.open ssql,connection
you can then set a breakpoint and see what the sql actually contains. Also i would probably set the recordset to a new recordset then set it to nothing within the loop since you are actually creating a new recordset.
3. Do you have any code in the errorhandler to report the Error? What is the error? It could be more of a structure problem if it works without the error handling.
4. OK. You seem to be a bit confused about the use of batch uptates etc.
I am pretty sure you need a client side cursor to use the batch updates. If you use updatebatch you need to use adlockbatchoptimistic as the lock type. I suggest you find some examples on the web but in general the flow should be.
set rs = new recordset
rs.cursorlocation = adUseCLient
rs.cursortype = adOpenDynamic
rs.locktype = adLockBatchOptimistic
rs.open sql,connection
for i = 0 to 9
rs.add
rs.fields(0) = Value
rs.fields(1) = value
next
rs.marshaloptions = admarshallall ' or admarshallmodifiedonly
rs.updatebatch
5. None specific to using SQL unless you have joins
Its been a while since i used ADO but i hope i have given you some ideas.
Jon
|
|
|
|
|
Hi,
i am new to SQL-Server and Web Technology. i have just joined a company and its my first task to do. So, Please Help me immediately.
I have to send a Http Post Request to a server with 4 parameters from a Stored Procedure. I got a Stored procedure on google which use Http post method from Stored Procedure. but i dont know how to send Parameters alongwith it on server and how to get back the status message(e.g. error message if failed).
the stored procedure is:
****************************************************************************
CREATE procedure HTTP_POST( @sUrl varchar(200), @response varchar(8000)
out)
As
Declare
@obj int
,@hr int
,@status int
,@msg varchar(255)
exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @obj OUT
if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end
exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end
exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL,
'Content-Type', 'application/x-www-form-urlencoded'
if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed'
goto eh end
exec @hr = sp_OAMethod @obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end
exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read status failed' goto
eh
end
if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +
str(@status) goto eh end
exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <>0 begin set @msg = 'sp_OAMethod read response failed' goto
eh end
exec @hr = sp_OADestroy @obj
return
eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
GO
****************************************************************************
the parameters which i have to send are:
variable name ----- Parameter value
------------------------------------------------------------
name -> any character String
msisdn -> any numeric string (Like Mobile No.)
toAddress -> any numeric string (Like Mobile No.)
message -> any character string (Like SMS Message)
-------------------------------------------------------------
Actually i have to send these information on a server which will send SMS to any mobile. so, plz can any one help me.....
Thanx in Advance....
-- modified at 13:25 Monday 31st October, 2005
|
|
|
|
|
I wish it was a joke, but our DBA passed away. Till such time we find a new DBA, I am the man. I have this script:
exec sp_droprole 'SAM_USER'
exec sp_addrole 'SAM_USER'
exec sp_droprole 'Manage_Presentation'
exec sp_addrole 'Manage_Presentation'
grant select , insert,delete , update on Presentation to Manage_Presentation
grant select , insert,delete , update on Presentation_Menu_Link to Manage_Presentation
grant select , insert,delete , update on PresentationOwner to Manage_Presentation
grant select , insert,delete , update on PresentationSlide to Manage_Presentation
grant select , insert,delete , update on PresentationSlideOverride to Manage_Presentation
grant select , insert,delete , update on PresentationSlideVerticalMenu to Manage_Presentation
exec sp_addrole 'Manage_Slides'
grant select , delete , update on dbo.Slide to Manage_Slides
grant select , delete , update on dbo.Slide_Menu_Link to Manage_Slides
grant select , delete , update on dbo.SlideComment to Manage_Slides
grant select , delete , update on dbo.SlideFile to Manage_Slides
grant select , delete , update on dbo.SlideType to Manage_Slides
grant select , delete , update on dbo.SYSFile to Manage_Slides
exec sp_addrolemember 'SAM_USER','Manage_Presentation'
exec sp_addrolemember 'SAM_USER','Manage_Slides'
--create user section of the database
exec sp_addlogin @loginame = 'Sameer',@passwd = 'Sameer',@defdb = 'SAM_DEV'
exec sp_grantdbaccess 'SameerHanda'
exec sp_addrolemember 'SAM_USER','SameerHanda'
Well all well and good, but when I signon as Sameer and do the following
select * from Presentation, I ge this error:
SELECT permission denied on object 'Presentation', database 'SAM_DEV', owner 'dbo'.
Well what am I missing. Any help would be greatly appreciated
Thanks
Sameer
PS: RIP Kev Dawg 
|
|
|
|
|
You've added the Manage_Presentation and Manage_Slides roles to the SAM_USER role. I think you intended it the other way around. You've also specified a different username in the sp_grantdbaccess and sp_addrolemember calls than you did in the sp_addlogin call.
I think you simply need to change
exec sp_addrolemember 'SAM_USER','Manage_Presentation'
exec sp_addrolemember 'SAM_USER','Manage_Slides' to
exec sp_addrolemember 'Manage_Presentation','SAM_USER'
exec sp_addrolemember 'Manage_Slides','SAM_USER'
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
 Cool thanks it seems to have worked, I am a c# guy being forced to act as a DBA for the time being. Good experience none the less.
|
|
|
|
|
If your DBA died (sorry to hear that) and you are the acting DBA, I would be more than a little concerned about what happens if your DB dies too.
Murphy's Law would suggest that since your DBA died, you are at much higher risk than normal that your SQL Server is going to crash sometime in the near future too.
If it does and you are not prepared for it, you could be in for a lot of nasty suprises.
If I were in your position, I would run full backups on all the databases and especially the master database immediately.
And if you don't know a lot about what you are doing, until you get a new DBA I would advise not worrying so much about the media cost and keep making regular full backups of any databases in active use on a pretty regular basis.
Your DBA probably has some kind of transactional backup system implemented. If you don't have his expertise, and your server dies tomorrow, you could really be in a jam.
|
|
|
|
|
It's a good thing that I am familair with Oracle DBA stuff. Then again DB's are DB's its just a different way of doing things.
I have already made backups of our MDF file, the transaction logs. I am not sure where the redo logs of Sql server are. Working on figuring that out.
|
|
|
|
|
Hello everybody,
I wanted to transfer XML data to SQL Server using MS Sql Server DTS. But there is no connection object for XML data in MS Sql Server 2000. So without using DOM object how can i transfer XML data to MS Sql Server ?
|
|
|
|
|
Hi,
I'm currently developing a forge where all the receipts is stored in a mySQL database. The players suppose to insert their items within the forge, and activate it.
When the forge activates, the script goes through each item located within, and get the unique id for each item. An example of a result would be:
item1 item4 items5 item8
I want to check if the field 'components' contains these id's. If so, the row should be returned.
I've tried the following statement, but will it work if the order were differnt?
SELECT FROM `recept` WHERE `komponenter` LIKE 'item1%item4%item5%item8'
|
|
|
|
|
it will work... notice that a select don't know the order of the records in a table... if you want to sort the, use after your where statement the ORDER BY keywords...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
Thank you for your reply!
So if komponenter contains the ID's arranged in the following order:
item1 item5 item3 item2 item4
... a SQL statement like this will find the row?
SELECT `...` FROM `recept` WHERE `komponenter` LIKE '%item5%item4%item3%item2%item1%'?
The reason I'm asking instead of just trying, is the fact that each time I want to try something out, I have to update the server via a console-interface - after I've uploaded the 25 meg new version of the game.
|
|
|
|
|
nop, you don't have it.
SELECT komponenter
FROM recept
WHERE komponenter LIKE 'item%'
ORDER BY komponenter ASC;
this will return :
item1<br />
item2<br />
item3<br />
item4<br />
item5
well, tell me, i don't know what database you're using in your SW, but you might have tool to test SQL statements before updating your whole code and test it each time you change your query...
(Entreprise Manager with SQL Server 2000, SQL*Plus with Oracle DB...)
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
You may also want to consider the IN Syntax if you have the full id:
SELECT * FROM MyTable WHERE MyField IN('Value1','Value2','Value3')
|
|
|
|
|
OK i've used just Microsoft Access database for my application made in c#.
Now i want to know how can use mySQL database.
My questions are the following:
1)How can create a new mysql database within my c# application? (using just sintax with .NET objects or using VisualStudio tools)
2)What i need installed on my (for developing) computer?
3)What is needed installed on the final user computer?
4)I know how create and manageing database in mySQL(using the daemon) so i want to know what are the differences with mySQL and SQL.
Thanks.
|
|
|
|
|
Sasuko,
You may want to visit MySQl's site and look at their .net connector in the tools section.
You may just need to distribute the .dll file with your application.
I hope this is a start for you 
Paul
|
|
|
|
|
so the final user doesn't need the mysql server installed on its pc?
And where can i find the database (where is the file?) i've created before and i want to distribuite with my application?
What directory have i to put the database in during the installation?
|
|
|
|
|
The user should not need to have a mySQL server installed on hir or her machine. Your application should point to the server in the connection string. You'll need to dig around with your mySQL server installation for the database file. If there is an existing database server, you should not have to install a database file anywheres.
|
|
|
|
|
Hola!
Is it possible to so something like this with SQL Server 2005 (in a SPROC)?
DECLARE @Variable varchar(1000)
SET @Variable = 'LastName ASC, FirstName ASC'
SELECT * FROM table ORDER BY @Variable
This doesn't work for sure... but is there a known workaround? I don't want to do "EXEC @sql" for performance and good programming practice reasons.
Thanks!
|
|
|
|
|
ITs not clear to me why you don't just write 'LastName ASC, FirstName ASC' into your query in the first place. If you do have a good reason, then you'll have to write a dynamic query and make use of EXEC @sql. If you're worried about performance, you should know that the optimizer WILL be able to optimize dynamic queries too. It remembers the query plan from that last query so as long as the query isn't going to change too much, you shouldn't have to worry. The only other danger with dynamic queries is that someone would try to inject SQL into the query. If you're using ASP.NET, ValidateRequest is turned on by default.
|
|
|
|
|
The reason is pretty simple, I want the user to be able to sort data by whatever they want. The query I posted was just an example. The real query is about 1 screen long and has 37 parameters.
|
|
|
|
|
I like Edbert's solution (CASE statement in the ORDER BY clause) if the user can only order by one field. However, if you want to support sorting by multiple fields, I think that the dynamic query method will be easier to implement.
|
|
|
|
|
You can't use ORDER BY @Variable.
However, you can use CASE statements to do ORDER BY, e.g.:
SELECT * FROM table
ORDER BY
CASE WHEN @Variable = 'LastName' THEN
LastName
CASE WHEN @Variable = 'FirstName' THEN
FirstName
END ASC
Note: this is limited to one field only. But theoritically you can combine it with another case statement below to order by several columns.
Does that help?
Edbert P.
Sydney, Australia
|
|
|
|
|
 Hi all. I have a problem to solve with an MS Access database. In a login form the user have to enter username and password. In the back there is a table called users witch have the fallowing fields: userid, username, userpass. I wrote the fallowing code to check if the username and password match with those in the table users:
Private sub command2_click()
Set rst1 = CurrentDb.OpenRecordset("useri", dbOpenDynaset)
Private Sub Command2_Click()
Dim qdef1, qdef2 As QueryDef
Set rst1 = CurrentDb.OpenRecordset("useri", dbOpenDynaset)
Set qdef1 = CurrentDb.CreateQueryDef("")
With qdef1
.SQL = "SELECT * FROM users " & "WHERE username= '" & [Forms]![form1]![input_user] & "'" & "and userpass= '" & [Forms]![form1]![input_pass] & "'"
Set rst = qdef1.OpenRecordset()
nri = rst.RecordCount
End With
rst1.FindFirst "username= '" & Me.input_user & "'"
If rst1.NoMatch Then
MsgBox "There is not such user!"
Exit Sub
Else
If nri = 0 Then
MsgBox "Password does not match!"
Exit Sub
Else
' Everythiong is ok... go on and do the stuff!!!
End If
End If
End Sub
My question is if there is any possibility to optimize this code, to make it simple.
Thank you very much.
Respect,
Gabriel
|
|
|
|
|
Aside from a note that you should try using parameters instead of concatenating your SQL statements, the only optimization I can see is this:
Set rst = qdef1.OpenRecordset()
If rst.EOF Then
MsgBox "Not Found"
Exit Sub
Else
MsgBox "Found. Do whatever you want"
End If
First, your SQL statement will not work as you're concatenating the apostrophe with the and operand, see: username= '" & [Forms]![form1]![input_user] & "'" & "and userpass=. You should put a space before the and operand.
Second, your SQL statement will only return a record(s) that have the same username AND userpass, therefore it will not be able to tell if there are no records returned whether it is because the username does not exist or the password is wrong. You do not want to tell users whether it is the password or the username that is wrong. Just tell them username or password is invalid.
Does that help?
Edbert P.
Sydney, Australia
|
|
|
|
|
Hi. you suggest using parameters and by saying that u refer to using querys? because I was thinking is much more easier to user a query in query builder and then just to refer to it in the code... I was telling you I'm a newbie. Now... the question is: how can I do that?
Thank you.
Gabriel - Romania
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
