Database

13-Mar-08 5:48

Here's an msdn link to building a custom membership provider. While it doesn't use the aspnet database and stored procs I believe if you look at how the logic is implemented it could help answer your question.

http://msdn2.microsoft.com/en-us/library/6tc47t75.aspx[^]

Running SQL Server stored procedure

ambrose11-Mar-08 22:56

ambrose

11-Mar-08 22:56

How do i run SQL server stored procedure in visual basic 6 to INSERT records to a table from the form (frmsuppliers)?

the properties are bundled into an array for insertion in the stored procedure as: -
Public Sub SaveSupplier(ByVal vntSupplierInfo As Variant)
'Declarations
Dim strSQL As String
Dim command As New ADODB.command

If vntSupplierInfo(0, 0) = 1 Then '
Dim a(0 To 7) As Variant

a(1) = vntSupplierInfo(2, 0)
a(2) = vntSupplierInfo(3, 0)
a(3) = vntSupplierInfo(4, 0)
a(4) = vntSupplierInfo(5, 0)
a(5) = vntSupplierInfo(6, 0)
a(6) = vntSupplierInfo(7, 0)
a(7) = vntSupplierInfo(0, 0)

'Create a connection in the database
command.ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Asset;Data Source=AMKILA"
command.CommandText = "sp_SaveSupplier" 'find the insert stored procedure for that language
command.Execute , a() 'Execute the the array of a's into the stored procedure and run the SP

The error i get is:

Run-time error "-2147217904(80040e10)';
Procedure 'sp_savesupplier' expects parameter '@supplierID, which was not supplied.

Here is the procedure which i want to run in order to INSERT RECORDS in table called suppliers in the database called Asset.

CREATE PROCEDURE SP_SaveSupplier
@SupplierID smallint,
@SupplierCode varchar(10),
@CompanyName Varchar(100),
@Address Varchar(50),
@Telephone1 Varchar(20),
@Faxnumber Varchar(20),
@ContactPerson Varchar(30),
@Email Varchar(50)
AS
BEGIN
INSERT INTO Suppliers(SupplierID, Supplier_Code, Company_Name, Address, Telephone1, Faxnumber, Contact_Person, Email_Address)values(@supplierID, @SupplierCode, @CompanyName, @Address, @Telephone1, @Faxnumber, @ContactPerson, @Email )
END
GO

Kindly assist as soon as possible

Amkila $Shucks | :-\$

Re: Running SQL Server stored procedure

Mark J. Miller13-Mar-08 5:56

13-Mar-08 5:56

I haven't used VB6 since I started working with C# 6 years ago, so I don't have it installed and can't give the exact syntax. But you need to define the command parameters and pass the values of your array to the appropriate paramters. Something like this:

'define paramsters
command.Parameters.Add("@SupplierId", ....... )
command.Parameters.Add("@SupplierCode", ....... )
command.Parameters.Add("@CompanyName", ....... )
command.Parameters.Add("@Address", ....... )
' ... add other parameters ...

'set parameter values
command.Parameters("@SupplierId").Value = a(1)
command.Parameters("@SupplierCode").Value = a(2)
command.Parameters("@CompanyName").Value = a(3)
command.Parameters("@Address").Value = a(4)
' ... set other parameters ...

command.Execute()

Re: Concurrency violation

Concurrency violation

baranils11-Mar-08 21:31

baranils

11-Mar-08 21:31

Hello : I've seen defferent post about tthis issue but no real fix

The problem:
If you add a new row with an autoincrement key in the database, using sqlcommand builder, it seems that the newly created key is not updated in the DS
So if just after that add you try to delete the row you get the message :

Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."

One yay I've found to fix that is

1- Tho the update
1- clear the dataset ds.clear()
2- refill the ds using adapter adapter.fill(ds)

But it sounds a little bit odd

Any other Idea ?

Mark J. Miller13-Mar-08 6:01

13-Mar-08 6:01

I've never used the command builder because I'd rather control the SQL than have it written for me. But I assume the command builder isn't including the correct statement to retrieve the autoincrement value. Here's an msdn article on how to update the autoincrement value when you call DataAdapter.Update.

http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.80).aspx[^]

Re: Concurrency violation

baranils13-Mar-08 6:54

baranils

13-Mar-08 6:54

Thank you

Yes te article describe the problem.
For now what I was doing is to clear the dataset and refill it

I will try to find if I can get the returned record from the adapter

I'm using MySql connector

sql structure

Tara1411-Mar-08 21:02

11-Mar-08 21:02

Hi,

I have two table like this:

quote_tb:

ID   item  vendor  quote
11     a     v1    100
11     a     v2	   200
11     b     v1    400
11     b     v2    300
11     c     v1    555
11     c     v2    777
22     a     v1    122
22     a     v2	   222
22     b     v1    322
22     b     v2    422
22     c     v1    555
22     c     v2    777

vendor_tb

name  phone#   fax#    address#
v1     1111    2222    3, x street
v2     1212    2323    4, m street

I am trying to write an sql that given me the following result:

lowestQ_tb
ID   item  vendor  quote  phone#    fax#
11     a    v1       100   1111      2222
11     b    v2       300   1212      2323
11     c    v1       555   1111      2222

i.e for each of the items a,b,c having the id(11) select the vendors that have the lease quote and then get the phone and fax numbers of the vendors form the table vendor_tb.

To get the first part i.e. lowest quote for each item, this is the sql I use:

SELECT f.item, f.vendor, f.quote 
	FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item) 
	AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice

which gives me the result:

ID   item  vendor  quote
11     a    v1       100
11     b    v2       300
11     c    v1       555

I don't know how to take it further form here to get lowestQ_tb. Please can you tell me how to get the above desired result i.e tabele lowestQ_tb. Is it possible to do such a thing?

Thanks,
Tara

andyharman12-Mar-08 0:08

andyharman

12-Mar-08 0:08

How about:

select Q.ID, Q.item, Q.vendor, Q.Quote, V.phone, V.fax
from (
  select ID, item, min(quote) AS MinQuote
  from quote_tb
  where ID = 11
  group by ID, item
) AS MinQ
inner join quote_tb Q
  on Q.ID = MinQ.ID
  and Q.item = MinQ.item
  and Q.quote = MinQ.quote
inner join vendor_tb AS V
  on V.name = Q.vendor
order by Q.ID, Q.item

Note that if two different suppliers give the same quote for the same item then they will both be displayed. You could correct that using:

...
from (
  select Q.ID, Q.item, min(vendor) AS MinVendor
  from (
    select ID, item, min(quote) AS MinQuote
    from quote_tb
    where ID = 11
    group by ID, item
  ) AS MinQ
  inner join quote_tb AS Q
    on Q.ID = MinQ.ID
    and Q.item = MinQ.item
    and Q.quote = MinQ.quote
  group by Q.ID, Q.item
) AS MinS
inner join quote_tb Q
  on Q.ID = MinS.ID
  and Q.item = MinS.item
  and Q.vendor = MinS.MinVendor
...

It may help you to run each sub-query in turn to see how it is built-up. If you are using SQL-Server 2005 then you may be able to rewrite this more concisley using the windowing-functions.

Regards
Andy

Tara1412-Mar-08 7:45

12-Mar-08 7:45

Thank you for the reply.

I am basically connecting to MS Access through my VC++ code. I tried the code that you have given and it works with a few parenthesis added. Thank you.

Thanks,
Tara

Syed Mehroz Alam12-Mar-08 0:17

Syed Mehroz Alam

12-Mar-08 0:17

See my addition in bold, I hope this will help you.

SELECT f.item, f.vendor, f.quote, v.phone, v.fax
	FROM (SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item) 
	AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice
        inner join vendor_tb as v on f.vendor = v.name

Regards,
Mehroz

Tara1412-Mar-08 5:50

12-Mar-08 5:50

Thank you for the reply.

But for some reason, whatever way I try it doesn't seem to be working.

I am connecting to MS Access database through VC++. In whatever way I try to add an extra 'inner join' it gives me the error : "Syntax error(missing operator) in query expression". If I remove the extra 'inner join' its all fine.

Can't understand why. Is it something to do with the formatting?

Thanks,
Tara

Thanks,
Tara

Tara1412-Mar-08 7:36

Cannot resolve collation conflict for equal to operation.

12-Mar-08 7:36

I got it. Just needed to place a parenthesis.

SELECT f.item, f.vendor, f.quote, v.phone, v.fax
	FROM ((SELECT item, min(quote) AS minprice FROM quote_tb WHERE ID='11' GROUP BY item) 
	AS x INNER JOIN quote_tb AS f ON f.item = x.item AND f.quote = x.minprice)
        inner join vendor_tb as v on f.vendor = v.name

Thank you for the help.

Thanks,
Tara

Password Expiry

manowj11-Mar-08 20:24

manowj

11-Mar-08 20:24

Hi Everyone,

Is it possible to detect the expiry of password in oracle without being an DBA?. Can anyone help me in this regard.

Regards,
Manowj

AlexeiXX311-Mar-08 14:44

AlexeiXX3

11-Mar-08 14:44

Thanks for looking

I have this query:
select * from Table1 where field1 in (SELECT Value FROM fnSplitString ('a,b,c', ','))
Ive also tryed casting Value:
select * from Table1 where field1 in (SELECT cast(Value as varchar(50)) FROM fnSplitString ('a,b,c', ','))
Field1 has <database default> as collation and is of type varchar(50)
fnSplitString returns a table, in this case, it would return:
Value
a
b
c
of type varchar
Im getting error: Cannot resolve collation conflict for equal to operation.

And i dont know why
I am able to use this function but using it with integer values:
select * from table where ID in (SELECT Value FROM fnSplitString ('1,2,3', ','))

Ill appeciate any help

Code of the function

<br />
CREATE function dbo.fnSplitString(<br />
	@String varchar (4000),<br />
	@Delimiter varchar (10)<br />
)<br />
returns @ValueTable table ([Value] varchar(4000))<br />
begin<br />
 declare @NextString varchar(100)<br />
 declare @Pos int<br />
 declare @NextPos int<br />
 declare @CommaCheck varchar(1)<br />
 <br />
 --Initialize<br />
 set @NextString = ''<br />
 set @CommaCheck = right(@String,1) <br />
 <br />
 --Check for trailing Comma, if not exists, INSERT<br />
 --if (@CommaCheck <> @Delimiter )<br />
 set @String = @String + @Delimiter<br />
 <br />
 --Get position of first Comma<br />
 set @Pos = charindex(@Delimiter,@String)<br />
 set @NextPos = 1<br />
 <br />
 --Loop while there is still a comma in the String of levels<br />
 while (@pos <>  0)  <br />
 begin<br />
  set @NextString = substring(@String,1,@Pos - 1)<br />
 <br />
  insert into @ValueTable ( [Value]) Values (@NextString)<br />
 <br />
  set @String = substring(@String,@pos +1,len(@String))<br />
  <br />
  set @NextPos = @Pos<br />
  set @pos  = charindex(@Delimiter,@String)<br />
 end<br />
 <br />
 return<br />
end<br />
<br />

Alexei Rodriguez

Re: Cannot resolve collation conflict for equal to operation.

AlexeiXX311-Mar-08 15:17

AlexeiXX3

11-Mar-08 15:17

OK i "fixed it"
This works:

select * from Table1 where (Field1 COLLATE Traditional_Spanish_CI_AI) in (SELECT Value FROM fnSplitString ('a,b,c', ','))

This also woks if i use Field1 COLLATE Traditional_Spanish_CI_AI

But whats wrong with my original query??

Is there any other way to make this work?

Thanks

Alexei Rodriguez

Problems Exporting From DataSet to .DBF File

Snaider11-Mar-08 9:00

Snaider

11-Mar-08 9:00

Hi!!!
I'm creating a program to build a DataTable and I want to export it to a .dbf file.
One of the result is this line:

Create Table Results.dbf ( NumerodeIndividuosouNinhos number(4,0), Quadricula Char(5))

Unfortunely, it gave me a OleBdException and the error was "Field name is a duplicate or invalid.".
But where is the error here? It isn't a duplicate field...if it's invalid, why is invalid?

Thank You

Re: Problems Exporting From DataSet to .DBF File

perryf_0011-Mar-08 12:12

perryf_00

11-Mar-08 12:12

I believe creating a DBF this way requires that Column names be limited to 10 chars.

Re: Problems Exporting From DataSet to .DBF File

Snaider12-Mar-08 0:18

Snaider

12-Mar-08 0:18

Hi There!!!

Thank You, perryf_00. That worked well. Big Grin | :-D

Now I've to modify that Column names to 10 chars max.

DataRelation and NOCHECK add constraint

CJacobus11-Mar-08 6:21

CJacobus

11-Mar-08 6:21

Anybody know how to define a NOCHECK add constraint (or equivalent) in a DataSet?

I see the DataRelation and ForeignKeyConstraint classes, but I don't see anything that I can use to set a constraint in the DataSet as a nocheck constraint.

Any help is greatly appreciated.

Thanks!

Charles.

Re: DataRelation and NOCHECK add constraint

Mark J. Miller11-Mar-08 7:28

11-Mar-08 7:28

It's been a while since I worked with this, but I remember having the same issue. I believe adding a relation adds both a check constraint and a foreign key constraint. You should be able to loop through the constraints collection and remove the check constraints and leave the foreign key constraints in place.

Re: DataRelation and NOCHECK add constraint

CJacobus12-Mar-08 11:15

CJacobus

12-Mar-08 11:15

Thanks for the reply.

Any idea how I distinguish them? I expected a Type property or something like that on the Constraint object. Or, checking the constraint type (i.e., ForeignKeyConstraint)...

Charles.

Re: DataRelation and NOCHECK add constraint

Mark J. Miller13-Mar-08 3:29

13-Mar-08 3:29

You can use GetType() and check the type of the object. If you're using C# you could use :

if(constraint is ForeignKeyConstraint)