Database Discussion Boards

MeterMan24-Mar-06 6:25

24-Mar-06 6:25

Yeah unfortunately I'm using access for now. We will be converting to sql server soon but for now Access. Sorry I should have been more specific.

Win32newb
"Programming is like Sex, make a mistake and you end up providing support for a long time"

Re: Please help totally lost on syntax

Michael Potter24-Mar-06 8:39

Michael Potter

24-Mar-06 8:39

Access syntax is very different from SQL. The join syntax is really nasty when mulitple joins are involved. I used to write code that converted between the two but, that was many years ago. I think you can modify your original WHERE clause like this:

SELECT
    ExpenseReport.tboxBeginDate, 
    BeginDates.tboxBeginDate 
FROM
    BeginDates.tboxBeginDate 
LEFT JOIN ON 
    ExpenseReport.tboxBeginDate 
WHERE
    BeginDates.tboxBeginDate == ExpenseReport.tboxBeginDate AND
    ExpenseReport.tboxBeginDate IS NULL

The 'IS NULL' is what I am not sure about. Create a new query and paste this into the SQL view of the query and see if it resolves correctly by going back to the design view. I can't remember how null is resolved in Access. It might be ISNULL([Field Name]) or something like that.

Do remember the following, when NULL is directly compared with a defined value:

BeginDats.tboxBeginDate != ExpenseReport.tboxBeginDate

Your result will be NULL not TRUE as you expected. That is why you would never get the correct result.

-- modified at 14:48 Friday 24th March, 2006

Re: Please help totally lost on syntax

MeterMan25-Mar-06 12:43

MeterMan

25-Mar-06 12:43

Thanks for all your help. I was wondering about that != but yeah makes scense kinda like multiplying something by 0 it is always goning to be zero.
again thanks

Win32newb
"Programming is like Sex, make a mistake and you end up providing support for a long time"

Delete Procedure

shapper24-Mar-06 3:28

shapper

24-Mar-06 3:28

Hello,

I am creating my first procedures in SQL using SQL 2005.
I have 3 tables, with the following columns:

Surveys - [SurveyId](PK) and [SurveyName] Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion] Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and [SurveyAnswer]

Each survay can include various questions and each question can include several answers.
This is way I am using the Foreign Keys in both Questions and Answers tables. To relate the tables.

I created a procedure which deletes a Survey given its SurveyId. This is part is done.

I also need to delete all the questions dependent on that survey and all the answers dependent on those questions.
How can I delete survey, its questions and their answers when receiving the SurveyId?

Thank You Very Much,
Miguel

Here is the code of the procedure that I created which in this moment only deletes the survey from the Surveys table:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]

-- Procedure Parameters
@SurveyId As uniqueidentifier

AS
BEGIN

-- Check if SurveyId is null
IF( @SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @SurveyId FROM dbo.Surveys WHERE @SurveyId = SurveyId ) )
RETURN -1
END

-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @SurveyId = SurveyId

-- Return '0' when successful
RETURN 0

END

Re: Delete Procedure

Michael Potter24-Mar-06 4:10

Michael Potter

24-Mar-06 4:10

You could just implement Cascade on Delete when you implement referencial integrity. But, I always do it the hard way also:

ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
(
    @SurveyId As uniqueidentifier
)
AS
BEGIN
    
    -- Check if SurveyId is null
    IF( @SurveyId IS NULL )
        RETURN -1
    ELSE
    BEGIN
        -- Return '-1' if a survey with SurveyId given value is not found
        IF( NOT EXISTS( SELECT @SurveyId FROM dbo.Surveys WHERE @SurveyId = SurveyId ) )
            RETURN -1
    END
    
    -- Delete Answers
    DELETE FROM 
        dbo.Answers 
    WHERE 
        SurveyQuestionId IN (SELECT 
                                SurveyQuestionId 
                             FROM 
                                Questions 
                             WHERE
                                SurveyId = @SurveyId)
    
    -- Delete Questions
    DELETE FROM 
        QUestions
    WHERE 
        SurveyId = @SurveyId
    
    -- Delete the survey with SurveyId given value
    DELETE FROM dbo.Surveys WHERE @SurveyId = SurveyId
     
    -- Return '0' when successful
    RETURN 0
END

SQL Server over Virtual Private Network

StyleGuide24-Mar-06 0:08

StyleGuide

24-Mar-06 0:08

Hi All,

A couple of basic questions - can SQL server run over a VPN? If so, can it use Windows Password Verification? Can I use SQL Server Express? (has it network capabilities?).

I am moving from an internal networked solution, which has an Access Back end, to a scenario where workers are based around different parts of the country, so I'm thinking SQL Server is the best way forward.

I do use SQL Server over an internal network on another project, but any advice work be greatly appreciated.

Thanks

Jonny

Re: SQL Server over Virtual Private Network

Luis Alonso Ramos24-Mar-06 5:45

Luis Alonso Ramos

24-Mar-06 5:45

StyleGuide wrote:
can SQL server run over a VPN?

For apps, VPN is basically a normal network, so they can hardly tell the difference. So, SQL can run in a network that is accessed by VPN (or even be run in a VPN client and be used by other computers in the network.)

StyleGuide wrote:
Can I use SQL Server Express? (has it network capabilities?).

Yes, but they are not enabled by default. You have to go to the SQL Configuration Manager and enable the SQL Browser Service, and also enable the TCP/IP protocol.

StyleGuide wrote:
If so, can it use Windows Password Verification?

I'm not 100% sure on this one, but you should if you are part of a domain.

Luis Alonso Ramos
Intelectix
Chihuahua, Mexico

Not much here: My CP Blog!

Re: SQL Server over Virtual Private Network

StyleGuide26-Mar-06 6:21

StyleGuide

26-Mar-06 6:21

thanks Luis, thats really helpful.

Jonny

Re: SQL Server over Virtual Private Network

Luis Alonso Ramos26-Mar-06 17:47

Luis Alonso Ramos

26-Mar-06 17:47

You are welcome! I am glad I could be of help Smile | :)

Luis Alonso Ramos
Intelectix
Chihuahua, Mexico

Not much here: My CP Blog!

mrkeivan23-Mar-06 11:00

mrkeivan

23-Mar-06 11:00

hey guys, i was working on a simple login form that i get the UserID from Access database and match ir with selected password also got from that database but I get this erorr message. I just can't figure out what the problem is.

My code :

VerificationConn.Open();<br />
OleDbCommand Cmd = new OleDbCommand("Select PassCode from Password where UserID = '" + ID.Text + "'", VerificationConn);<br />
string lookupPassword = (string)Cmd.ExecuteScalar();<br />
VerificationDA.Fill(VerificationDS, "Password");<br />
VerificationConn.Close();<br />
if(lookupPassword == Pass.Text)<br />
{<br />
    //next from will be opend<br />
}

Error:
System.Data.OleDb.OleDbException was unhandled
Message="Syntax error in FROM clause."
Source="Microsoft JET Database Engine"
ErrorCode=-2147217900
StackTrace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteScalar()
at Phone.Verification.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Mr.K\My Documents\Visual Studio 2005\Projects\Phone\Phone\Verification.cs:line 38

I thank you in advance Cool | :cool:

Re: Login ID and Password

Krisky23-Mar-06 17:39

Krisky

23-Mar-06 17:39

hi,

try like this.

strSQL="SELECT passcode FROM PassWordTable WHERE Useranme=@Username AND Password=@Password"

'Add the parameters for the query

Dom ComObj as New SqlCommand(strSQL,myConnection)

ComObj.Parameters.Add("@Username",txtUserName.text.Trim())

Like that for password also.
Dim dr As SqlDataReader

dr=ComObj.ExecuteReader(CommandBehavior.CloseConnection)

Try If dr.Read() then

Response.Write("We are logged in")
FormsAuthentication.RedirectFromLoginPage(txtUserName.Text,False)

else

Response.Write("Login failed")

End Try

I hope this helps.

Regards
Bhar

Bharathi
Books for Programmers
http://www.vkinfotek.com

HEY i did what you said

mrkeivan23-Mar-06 23:21

mrkeivan

23-Mar-06 23:21

hey man thanx for your help i did exactly what you said but I still have the same error !! I have changed everything the same. the thing is I used this code somewhere else and it works perfectly but i have no i dea why it doesn't work here !!!

D'Oh! | :doh:

Re: Login ID and Password

Michael Potter24-Mar-06 4:36

Michael Potter

24-Mar-06 4:36

Are you sure Password is not a keyword in Access? Try [Password] and see if you get better results.

BTW: Why do you fill a dataset when you already have the result from the ExecuteScalar() call?

Re: Login ID and Password

Luis Alonso Ramos26-Mar-06 17:49

Luis Alonso Ramos

26-Mar-06 17:49

mrkeivan wrote:
leDbCommand Cmd = new OleDbCommand("Select PassCode from Password where UserID = '" + ID.Text + "'", VerificationConn);

Concatenating text to create SQL queries is a huge security risk. You should use parameterized queries. See this article for more information:

SQL Injection Attacks and Some Tips on How to Prevent Them[^]

Luis Alonso Ramos
Intelectix
Chihuahua, Mexico

Not much here: My CP Blog!

Visio

devfendar23-Mar-06 7:43

devfendar

23-Mar-06 7:43

Hey, two questions guys:
1. Visio - Database Model Diagram: I have two tables:

table 1 (Parent): Group {PK: GroupUIN, Attributes: Name, Desc}
table 2 (Child): User {PK: UserUIN, Attributes: ParentGroupUIN, Name, Desc}

I connected the two entities with a "Relationship". Under "Miscellaneous" tab, I specified relationship cardinality: One Group can related to "Zero or More" User. - I am having a hard to "show" cardinality in ER diagram (as supposed to just under "Miscellaneous" tab.

2. How can I generate SQL scripts from Visio ER/Database Diagrams?

Thanks.

Query the MaxLocksPerFile Count.

beatle1123-Mar-06 3:45

beatle11

23-Mar-06 3:45

I have an Algoritim in old C/CPP code that will update tables in an Access database. The database is very large and many records are affected. Unfortunately the MaxLocksPerFile Limit gets exceeded. After some research i have a bit more information. That limit can be changed in the registry. The default is 9500 locks. I was wondering if anyone knew of a way to get the current MaxLocksPerFile count. I would like to be able to see how the MaxLocksPerFile count changes as records are being modified. I would like to be able to get around this error without needing to change that limit if at all possible.More information is available at http://support.microsoft.com/kb/q173006/ if anybody else is having a similar problem. Any thoughts or ideas would be greatly appreciated.

Transfer sql server database to ms access in vb 6.0...

swirldan23-Mar-06 1:04

swirldan

23-Mar-06 1:04

Hello!

I have a problem with accomplishing task mentioned in a subject.
I want to transfer data from MS SQL server database to Ms Access database in Visual Basic.

Till this moment I only achieved to create an empty MS Access database (only structure:tables with fields, but without types). This is done with this simple code:

<br />
Set rs = dataEnv.sqlCon.OpenSchema(adSchemaTables, criteriaTables)<br />
    <br />
    While Not rs.EOF<br />
        criteriaFields(2) = rs!TABLE_NAME<br />
        Set cs = dataEnv.sqlCon.OpenSchema(adSchemaColumns, criteriaFields)<br />
            Set msTbl = msDb.CreateTableDef(rs!TABLE_NAME)<br />
            While Not cs.EOF<br />
                With msTbl<br />
                    .Fields.Append .CreateField(cs!COLUMN_NAME, dbLong)<br />
                End With<br />
                cs.MoveNext<br />
            Wend<br />
            msDb.TableDefs.Append msTbl<br />
        rs.MoveNext<br />
    Wend<br />
    msDb.Close<br />

Can anyone tell me how to copy contents of tables?

Thanks in advance!
Greetings!
Daniel

what is wrong in this code

mm31022-Mar-06 23:03

mm310

22-Mar-06 23:03

private void button1_Click(object sender, EventArgs e)
{
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\db4.MDB";
string strSQL = "SELECT * FROM ta1";

OleDbConnection myConn = new OleDbConnection(strDSN);
OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL, myConn);
DataSet dtSet = new DataSet();

string sqlQuery = "SELECT Name,Password FROM ta1 where Name = '" + textBox1.Text.ToString() + "' AND Password= '" + textBox2.Text.ToString() + "'";

myCmd.SelectCommand.CommandText = sqlQuery;

int numberOfRowsFeched = myCmd.Fill(dtSet, "ta1");

if (numberOfRowsFeched > 0)
{
try
{
myCmd.UpdateCommand.CommandText = "UPDATE Users SET " + "Password = '" +textBox3.Text + "'" + " WHERE Name = '" + textBox1.Text + "'";
myCmd.UpdateCommand.Connection = myConn;
myCmd.UpdateCommand.ExecuteNonQuery();
MessageBox.Show("Record updated Successfully");
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
}
catch (System.Data.OleDb.OleDbException exp)
{
myConn.Close();
MessageBox.Show(exp.Message);
}

}
else
{
MessageBox.Show("wrong Name Or Password", "Password", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
}

message : http://www.cpestudents.net/upload/up/54.gif[^]

Re: what is wrong in this code

albCode22-Mar-06 23:31

albCode

22-Mar-06 23:31

this keywords SET " + "Password as sql command in result is SETPassword, you have to separate by space, just set space character before Password like this

SET " + " Password

_____________________

Proud to be Albanian
_____________________

Re: what is wrong in this code

Colin Angus Mackay23-Mar-06 1:34

Colin Angus Mackay

23-Mar-06 1:34

mm310 wrote:
what is wrong in this code

You have left it wide open to SQL Injection Attacks. While the other response you got will apparently fix your problem it still leaves your code open to attack.

You should read: SQL Injection Attacks and Tips on How to Prevent Them[^]

This will explain a better way to perform your queries that will solve your problem AND make your code safer - Especially for code dealing with a login. What you have here is unforgivable and any tutorial that you have read that suggests you build SQL this way should be recalled and the author lined up and shot! (in my opinion). No wonder Software Developers have a bad reputation when it comes to security if developers think that concatenating strings together to form a SQL query is acceptable on a day-to-day basis. String concatenation as a way to build a SQL statement should only be done after careful consideration that there is absolutely no other way to achieve the desired result.

Sorry for my rant. It isn't your fault - You've obviously read the wrong tutorials and have picked up some extremely dangerous habits.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Unable to run the SQL Server critical update

Suj_7822-Mar-06 23:03

Suj_78

22-Mar-06 23:03

Hi!,
Most of you will be aware of a critical update on Sql server from Microsoft, it is called SQLCritUpdPkg_ENU.exe, I executed this exe, but it was unsuccessful. I am a local administrator on this machine.

Is there someone out there who can help! me on this please!!!!!?

Thank you.

happy coding!

Re: Unable to run the SQL Server critical update

André Ziegler23-Mar-06 22:32

André Ziegler

23-Mar-06 22:32

What SP do you have installed on your machine? SP3 or later are secured aginst the worm so they don't need the update you want to install.

André

'A programmer is just a tool which converts caffeine into code'

Re: Unable to run the SQL Server critical update

Suj_7823-Mar-06 23:04

Suj_78

23-Mar-06 23:04

I am running SP4 on Sql 2000.

Original Problem:I was unable to connect to my dev Sql server from my UAT enterprise manager. It came up with the error message as follows: SQL Server does not exist or access denied. ConnectionOpen(Connect()).I did a lot of googling but of no avail, finally I got to this web page http://www.doughughes.net/index.cfm?event=ViewEntry&entryId=91.
Then I downloaded SQLCritUpdWiz_ENU.exe and tried to execute it. I am a System administrator on this machine. However, I am unable to run this, it comes up with a message, Critical Update Unsuccessful.
System Info:My OS is win XP Pro(SP2) and SQL server is 2K SP4.

I have also tried to connect to the SQL server using port numbers via Server Network Utility and Client Network Utility , it didn't work.

Any more ideas?

happy coding!

Re: Unable to run the SQL Server critical update

André Ziegler24-Mar-06 0:43

André Ziegler

24-Mar-06 0:43

The update is for SP1 and Sp2, not for SP4 of SQL Server 2000.

Do you have access to the sql server from other tools? Ist the connectionstring correct?

I never had such problems with SQL Server 2000 (german, devoloper Edit. + Sp3).

André

'A programmer is just a tool which converts caffeine into code'

Re: Unable to run the SQL Server critical update

Suj_7824-Mar-06 2:11

Suj_78

24-Mar-06 2:11

Hey guess what !!!!!!
I am in Cloud Nine..........I have cracked it.

When you have a default instance of Sql Server 2000 on win xp pro (SP2), you need to change security settings in the exception tab of the Windows firewall.

Have a look at this article , this was like a missile to kill my frustration.
"http://support.microsoft.com/kb/841251/"

happy coding!

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