Re: Sql Server does not exists or acces denied - Database Discussion Boards

Christian Graus27-Jun-04 11:16

27-Jun-04 11:16

Yeah, I got an autoreply on an abusive post he made to me, he seems since to have packed up his trailer and moved on Smile | :)

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

Oracle GUID support

Michael Wolski23-Jun-04 4:46

Michael Wolski

23-Jun-04 4:46

We currently use the .net SQL Server managed data provider for database access, but we are looking at possibly extending our app support to Oracle. The .net Oracle managed data provider looks like a good solution, but it brought up some questions. What support does Oracle provide for a GUID data type? What versions of Oracle support this? We notice that the Oracle data reader has the same GetGuid() SetGuid() that SQL Server provides, but are not sure how it is implemented in an actual Oracle table.

So if anyone has any info on this (or can give us a juicy link with this info) us non-Oracle guys would appreciate it!

Michael
Developer, Author, Chef

JET download

RChin23-Jun-04 0:56

RChin

23-Jun-04 0:56

We are having a bit of a problem with a remote client using our software to access a JET database via ado.

I've got a few hunches on what the problem might be but I need to test this on a machine with an older MDAC and JET provider setup.

Does anyone know if Microsoft still have download sites for older service packs of the JET 4.0 files?

I Dream of Absolute Zero

Re: JET download

Dave Kreskowiak25-Jun-04 7:48

Dave Kreskowiak

25-Jun-04 7:48

You can't get just the JET stuff, you have to install the entire MDAC. You can do to http://download.microsoft.com[^] and search for the keyword MDAC to get the eariler versions.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Re: JET download

Graham Bradshaw26-Jun-04 14:16

Graham Bradshaw

26-Jun-04 14:16

Try http://www.microsoft.com/downloads/details.aspx?FamilyID=4335216C-8CDD-4168-A030-46F815D3F5CD&displaylang=en[^]

SQLXML & Default parameters

RichardGrimmer22-Jun-04 22:30

RichardGrimmer

22-Jun-04 22:30

Hi there,

Have an app which uses SQLXML to provide a webservice interface to my SQLServer DB.

Have a stored procedure declared:

<br />
CREATE PROCEDURE GetSurveyCompanies<br />
<br />
@SurveyCompanyID integer = 1<br />
<br />
AS<br />
<br />
Declare @SQL NVARCHAR (4000)<br />
<br />
set @SQL = 'SELECT * FROM SurveyorCompany WHERE IDSurveyorCompany='<br />
<br />
set @sql = @sql + CAST (@SurveyCompanyID as VARCHAR(2)) + ' FOR XML AUTO'<br />
<br />
exec sp_executesql @sql<br />
<br />

And am calling it using the SOAP toolkit from VB6 using the following...

<br />
oClient.GetSurveyCompanies(1)<br />

(Have omitted the rest of the setup - sure it's OK)

When calling speciying the param, everything is OK (apart from the XML coming back garbled, but think that's a different issue!), but when I attempt to use the default param value specified in the sproc, am getting Invalid procedure call - the SOAP CLient is reporting that "incorrect number of parameters supplied" (predictably!)

Question is, how do I use the default value - have tried passing in NULL (type mismatch), Nothing (same error), and even 'NULL', but nothing seems to work - any ideas anyone?

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Sql Server does not exists or acces denied

pdnitin22-Jun-04 21:09

pdnitin

22-Jun-04 21:09

Hi,
I have an application running on a server. I have ported this application to other server but it gives me the above error Sql Server does not exists or access denied error.I would like to know if i have to make any changes to the connection string.
Can any one help.
Thanks in advance,
Nitin.

Re: Sql Server does not exists or acces denied

Thea Burger22-Jun-04 21:16

Thea Burger

22-Jun-04 21:16

You posted this in the asp.net section as well. Gave you an answer there.

check for the duplicate data

viviansm21-Jun-04 20:06

viviansm

21-Jun-04 20:06

Greeting from Malaysia
i am a beginner in VB.NET. Please how do I check the duplicate records in the database? For example, I have a table called category in SQL server. This table
contains with CategoryID and Category.
In the front end I created a form that contains only 2 textbox. One is for the category ID that the ID is auto generated and the other textbox is for the user to enter the categoris(ed. adult, kids, comedy,.......) . So, how do I check if the user enters the category that already exists in the category table in the SQL Server. Please help!

Have a nice day!
Thanks and regards,
Vivian D'Oh! | :doh:

Re: check for the duplicate data

Wilbur J. Pereira21-Jun-04 22:11

Wilbur J. Pereira

21-Jun-04 22:11

this should solve ur problem.

the value of ExecuteScalar should be checked.

Dim strCheck = "Select Count(*) from Employeemaster where Name = '" & txtEmpName.Text.Trim & "'"
Dim sqlINQry As String
Dim sqlINcmd As SqlCommand
Dim varSiteAdmin As Int32

If chkSiteAdmin.Checked = True Then
varSiteAdmin = 1
Else
varSiteAdmin = 0
End If

Try
Sqlcon = New SqlConnection(ConfigurationSettings.AppSettings("conString2"))
Sqlcon.Open()
sqlINcmd = New SqlCommand(strCheck, Sqlcon)

If sqlINcmd.ExecuteScalar() = 0 Then
sqlINQry = "Insert into EmployeeMaster " & _
"(DepartmentID,UserType,Name,Password,Moth_Maid_Name,Designation,Email, " & _
"Mobile,Phone,Fax,SiteAdmin,Status)Values (23,1," & _
"'" & txtEmpName.Text.Trim & "', " & _
"'" & txtPassword.Text.Trim & "'," & _
"'" & txtMotMaidName.Text.Trim & "'," & _
"'" & txtDesignation.Text.Trim & "'," & _
"'" & txtEmail.Text.Trim & "'," & _
"'" & txtMobile.Text.Trim & "'," & _
"'" & txtPhone.Text.Trim & "'," & _
"'" & txtFax.Text.Trim & "'," & _
"'" & varSiteAdmin & "'," & "0)"
sqlINcmd = New SqlCommand(sqlINQry, Sqlcon)
sqlINcmd.ExecuteNonQuery()
Response.Redirect("SelectEmployee.aspx")
Else
lblAddNew.Text = "Record already exists"
Exit Sub

Re: check for the duplicate data

VenkatFor.NET22-Jun-04 3:06

VenkatFor.NET

22-Jun-04 3:06

Am sure this would solve the problem. But, moving all this to a stored procedure would be a good thing future enhancements. For example, if the record exists, what if we have to delete the existing record and then insert a new record. or what if we just have to update. Moving all the DB related activities would simply enhancements or modifications in future. Would suggest to use SP's wherever possible.

Bhaskara

Pulling the SECOND latest readings for ALL properties in a table!

lostsheep00721-Jun-04 19:48

lostsheep007

21-Jun-04 19:48

I have a problem that's an extension of one I was helped with before -hope someone can help!

A table (wm7_raw_spl_data)
Fields;
propertyID
date_2016_all (date field)
day_reading
night_reading

This table contains loads of readings for all properties. Each day/night reading is a separate record. The only way to separate records for a property is by the date the readings were calculated on.

So.. I want to choose the latest rundata for all properties. Properties are run off and rerun ad hoc, i.e. NOT all at the same time.

I did this like so:

SELECT *
FROM dbo.wm7_raw_spl_data INNER JOIN
(SELECT propertyID AS property_id2, MAX(date_2016_all) AS assessmentdate
FROM dbo.wm7_raw_spl_data
GROUP BY propertyID) wm7_raw_spl_data_1 ON dbo.wm7_raw_spl_data.date_2016_all = wm7_raw_spl_data_1.assessmentdate AND
dbo.wm7_raw_spl_data.propertyID = wm7_raw_spl_data_1.property_id2

OK! I now have a view I can use this to get the latest readings for all properties.

Now I want create a view to choose the 2nd latest run data for all properties...

Any help would VERY gratefully received. I'm having to do a big comparison of data and this is a basic requirement for the process.....

Patrick

Everything & Nothing
http://www.lostsheep.com

Re: Pulling the SECOND latest readings for ALL properties in a table!

Michael Potter22-Jun-04 6:16

Michael Potter

22-Jun-04 6:16

I think I would add an Identity to wm7_raw_spl_data to ease the query development but, given the original query try:

SELECT 
    *
FROM 
    dbo.wm7_raw_spl_data 
INNER JOIN
    (SELECT 
        propertyID AS property_id2, 
        MAX(date_2016_all) AS assessmentdate
     FROM 
        dbo.wm7_raw_spl_data
     WHERE 
        date_2016_all < (SELECT 
                            MAX(date_2016_all)
                         FROM 
                            wm7_raw_spl_data
                         WHERE propertyID = property_id2)
     GROUP BY 
         propertyID) wm7_raw_spl_data_1 
     ON 
        (dbo.wm7_raw_spl_data.date_2016_all = wm7_raw_spl_data_1.assessmentdate AND 
         dbo.wm7_raw_spl_data.propertyID = wm7_raw_spl_data_1.property_id2)

Password protection and Access

Guinness4Strength21-Jun-04 14:21

Guinness4Strength

21-Jun-04 14:21

I have an Access MDB file with a password on it, but I cannot access it via ADO.NET. I know its my connection string, but I've searched the web far and wide and I cannot get the correct connection string.

static public DataTable MDBPasswordQuery(string MDBPath,string Query,string Table,string Password)
{
	try
	{
		DataTable dTable = new DataTable(Table);
		string ConnText;
		ConnText="Provider=Microsoft.Jet.OLEDB.4.0;"; 
		ConnText+="Data Source="+MDBPath+";";
		ConnText+="Jet OLEDB:Database Password="+Password+";";
		System.Data.OleDb.OleDbDataAdapter HL7db = new OleDbDataAdapter(Query,ConnText);
		HL7db.Fill(dTable);
		return dTable;
	}
	catch(Exception Err)
	{
		throw new Exception("MDBQuery: "+Err.Message);
	}
}

I get the following error: No value given for one or more required parameters.

Can anyone help ?

Re: Password protection and Access

Mike Ellison21-Jun-04 14:27

Mike Ellison

21-Jun-04 14:27

Hi there. Which line is triggering the exception? is it on the creation of the OleDbDataAdapter, or is it on the .Fill(dTable) statement?

Re: Password protection and Access

Guinness4Strength21-Jun-04 16:27

Guinness4Strength

21-Jun-04 16:27

Its the Fill() Method.

Re: Password protection and Access

Guinness4Strength21-Jun-04 16:30

Guinness4Strength

21-Jun-04 16:30

Unbelieveable, it was my Query statement the whole time. Thanks for making me take a step back from the problem.

Re: Password protection and Access

Mike Ellison22-Jun-04 5:45

Mike Ellison

22-Jun-04 5:45

I'm glad you got it figured out.

variables in sql statement

Member 54508121-Jun-04 8:13

Member 545081

21-Jun-04 8:13

I am connecting to an MSACCESS DB. I would like to loop through the database and return which records are different. I cannot get the statement to work. When I hardcode the vars the statement works, when I reference the loop counter var it does not -HELP PLZ!!!

FOR j = 1 TO 10
k = j + 1
SQL_query = "SELECT ["&k&"].id, ["&k&"].x, ["&k&"].y FROM "&k&" LEFT JOIN "&j&" ON ["&k&"].x = ["&j&"].x WHERE (((["&j&"].x) Is Null));"

Question on updating multiple tables thru DataGrid

Flack21-Jun-04 7:02

Flack

21-Jun-04 7:02

Hello,

I have two tables in a DataSet (Customers and Orders) and they are displayed in a DataGrid. A relation is set up between the two tables.

Now, I have set up all of the necessary commands to update the tables. The user edits the data grid however he/she wishes and then hits a button to update. In the button click code I have

<br />
daCust.Update(ds,"Customers");<br />
daOrders.Update(ds,"Orders");<br />

If the user inserts a new Customer and some new Orders for that customer and then hits update, everything is fine. However, if he/she then tries to delete that new customer, I get a DELETE statement conflicted with COLUMN REFERENCE constraint error because it deletes the Customer and then tries to delete the Orders which reference that customer.

Can anyone suggest a simple solution where I can display two tables in a data grid and have the user edit them in any possible way or order and be able to update the database without getting a constraint error? The way I have it done it doesnt work unless you follow a certain order.

Thanx for the help,
-Flack

table name as a string

si_6921-Jun-04 1:14

si_69

21-Jun-04 1:14

hi all

im not sure if you can do this or not, but i want to pass into a stored procedure
the name of the table as a string and do a query on that

i tried doing it below, but that way doesnt work

any ideas ?????

si

declare @table_name varchar(20)

set @table_name = 'table1'

select *
from @table_name

Re: table name as a string

Steven Campbell21-Jun-04 2:57

Steven Campbell

21-Jun-04 2:57

try exec ('select * from ' + @table_name)

It will be slower than just executing the SQL directly, and it bypasses all of the advantages of stored procedures (security, strongly typed parameters).

Re: table name as a string

VenkatFor.NET21-Jun-04 3:01

VenkatFor.NET

21-Jun-04 3:01

You can certainly do that.

But, instead of using "SELECT" as a T-SQL, construct it dynamically. Here is how that can be done within your SP.

DELCARE @sql_stmt varchar2(100)
SET @sql_stmt = "SELECT * FROM " + @table_name

EXEC ( @sql_stmt )

Bhaskara

Difference between Providers and drivers??

Anonymous20-Jun-04 20:15

Anonymous

20-Jun-04 20:15

I want to know the exact difference between a data provider(e.g. oledb provider) and driver(e.g. odbc driver). Is there any difference between them??
How r they related??

Re: Difference between Providers and drivers??

Karthikeyan Muthurajan20-Jun-04 23:15

Karthikeyan Muthurajan

20-Jun-04 23:15

ODBC is Open Data Base Connectivity, which is a connection method to data sources and other things. It requires that you set up a data source, or what's called a DSN using an SQL driver or other driver if connecting to other database types. Most database systems support ODBC.

OLE is Object Linking and Embedding. OLEDB is partly distinguished from OLE itself, now called "automation".

OLEDB is the successor to ODBC, a set of software components that allow a "front end" such as GUI based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2, mySQL etal.

OLEDB is a different type of data provider that came about with MS's Universal Data Access in 1996 and does not require that you set up a DSN. It is commonly used when building VB apps and is closely tied to ADO. It works with COM, and DCOM as of SQL 7.0.

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