Re: storing data into access - Database Discussion Boards

How to Insert a comma "," in the SQL SERVER?

Anonymous26-May-04 12:00

26-May-04 12:00

Insert into Names(Name) values('FirstName,I,LastName');

it has 3 commas, but SQL server doesn't accept commas.

Does anyone have any idea?

Re: How to Insert a comma "," in the SQL SERVER?

Om Prakash Pant5-Jun-04 1:11

Om Prakash Pant

5-Jun-04 1:11

SQL server does inserts comma in the table.

Check the following query :

Insert into Names(Name) values(FirstName + ',' + I + ',' + LastName');

Om Prakash

Re: How to Insert a comma "," in the SQL SERVER?

afronaut14-Jul-04 14:46

afronaut

14-Jul-04 14:46

Another cheap trick if you have characters in code is to do a cast with the ascii value:

string apos = (char)39;

But yeah, I can't see why you wouldn't be able to insert a comma - it must be syntactical.

*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";

How to Insert a comma "," in the SQL SERVER?

Anonymous26-May-04 11:59

Anonymous

26-May-04 11:59

Insert into Names(Name) values('FirstName,I,LastName);

it has 3 commas, but SQL server doesn't accept commas.

Does anyone have any idea?

Re: How to Insert a comma "," in the SQL SERVER?

Garth J Lancaster26-May-04 12:56

Garth J Lancaster

26-May-04 12:56

I think you're a bit confused with the syntax of the insert command (Im presuming that your table 'Names' has more than one column ... )

INSERT INTO table_name
VALUES (value1, value2,....)

or

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)

here's a link to a handy ref site :
http://www.w3schools.com/sql/sql_insert.asp[^]

'G'

Re: How to Insert a comma "," in the SQL SERVER?

Edbert P26-May-04 12:57

Edbert P

26-May-04 12:57

If you're saying you need to insert FirstName, I, Lastname into one column and separate them with commas then this is how you should do it:

INSERT INTO Names(Name) VALUES (FirstName + ',' + I + ',' + LastName)

This should insert the name into the database like this: 'John,M,Smith'
Hope that helps

Edbert P.
Sydney, Australia.

storing data into access

DustinWehler26-May-04 8:18

DustinWehler

26-May-04 8:18

i'm new to the .net enviroment, and i'm trying to make my web application store to an access database .

my database fields are:
E-Name
E-Email
StartDate
EndDate
Reason
S-Name
S-Email
Approved
ApprovedBy
ApprovedDate

my textboxes that correspoond are
tbName
tbEmail
tbStart
tbEnd
tbReason
tbSupervisor
tbPermission
tbApproved
tbapprDate

all help would be greatly appreciated.

also, the database key is ID which is set to autonumber. i also need to query and retrieve that number.

Thanks greatly.
Dustin

Dustin Wehler
"Sometimes you win, sometimes you lose, sometimes it rains...think about it."
- Ebby Calvin "Nuke" LaLoosh in Bull Durham

Re: storing data into access

Mike Ellison26-May-04 10:45

Mike Ellison

26-May-04 10:45

Hi there, Dustin. I would recommend you look at some tutorials to get you started with ASP.NET and database handling. The QuickStart tutorials[^] are a good place to start. In particular, you'll want to read the tutorial "Server-Side Data Access" from the QuickStarts.

You'll start to familiarize yourself with the data objects that you'll need. In a nutshell, you'll find them in the System.Data and System.Data.OleDb namespaces (look for these in the .NET SDK documentation.) You'll use an OleDbConnection object to establish a connection to your access .mdb file. You'll then create an OleDbCommand object to represent your insert statement. Use OleDbParameter objects with the OleDbCommand to apply the values from your textboxes, and use the ExecuteNonQuery method to issue the command to the database. When selecting records you'll likely use a DataSet object or perhaps an OleDataReader

Here is a very raw example of a page that performs an insert to an Access database (in C#):

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="C#" %>

<script runat="server">
  void btnSubmit_Click(object o, EventArgs e)
  {
    // provide a connection string to the database
    const string kCONNECT_STRING = 
      "Provider=Microsoft.Jet.OLEDB.4.0; "
      + @"Data Source=c:\inetpub\wwwroot\tests\test.mdb";
    
    // provide the insert statement, with @parameters
    const string kINSERT_STATEMENT = 
      "INSERT INTO [MyTable] ([E-Name], [E-Email]) " 
      + " Values (@pName , @pEmail) ";
    
    OleDbConnection con = null;
    OleDbCommand cmd = null;  
    OleDbParameter pName = null; 
    OleDbParameter pEmail = null; 
    int insertedID = -1;
    
    try
    {
      // get the connection
      con = new OleDbConnection(kCONNECT_STRING);
      con.Open();
      
      // create the insert command
      cmd = new OleDbCommand(kINSERT_STATEMENT, con);
      
      // add parameter values
      pName = new OleDbParameter("@pName", DbType.String);
      pName.Value = tbName.Text;
      cmd.Parameters.Add(pName);
      
      pEmail = new OleDbParameter("@pEmail", DbType.String);
      pEmail.Value = tbEmail.Text;
      cmd.Parameters.Add(pEmail);
      
      // execute the insert command
      cmd.ExecuteNonQuery();
      
      // retrieve the autonumber that was just inserted
      cmd.CommandText = "SELECT @@Identity";
      cmd.CommandType = CommandType.Text;
      cmd.Parameters.Clear();
      insertedID = (int)cmd.ExecuteScalar();

      
      // if we're still here there's been no exceptions;
      // display a message
      Response.Write(
        string.Format("Record has been inserted and assigned ID# {0}."
                      ,insertedID) );
      
    }
    catch (Exception x)
    {
      // on an error, display a message
      Response.Write("There has been an error.  ");
      Response.Write(x.Message);
    }
    finally
    {
      // dispose objects
      if (cmd != null) cmd.Dispose();
      if (con != null) con.Dispose();      
    }
      
  }    
</script>

<html>
  <head>
  </head>
  
  <body>
    <form runat="server">
      <h3>Insert into Access</h3>
      <table>
        <tr>
            <td>Name</td>
            <td><asp:TextBox id="tbName" runat="server" /></td>
        </tr>
        <tr>
            <td>Email</td>
            <td><asp:TextBox id="tbEmail" runat="server" /></td>
        </tr>
      </table>
      <asp:Button id="btnSubmit" runat="server" text="Insert"
                  onClick="btnSubmit_Click" />
    </form>
  </body>
  
</html>

Another way to go about it is to use the OleDbDataAdapter object - there happens to be a good article on MSDN [^] that discusses how to retrieve the autonumber value after an insert, and their sample code uses OleDbDataAdapter. You may want to go in that direction.

One final word: Access is a good single-user desktop database. It is not as good as other RDBMS (for a variety of reasons) when it comes to multi-user web applications. SQL Server or Oracle may be better choices if you have the $$$. If cost is an issue, you may want to investigate using MSDE - a lighter-weight SQL Server from Microsoft, or perhaps even MySQL.

I hope this helps.

Re: storing data into access

afronaut14-Jul-04 14:42

afronaut

14-Jul-04 14:42

Thanks -

that is very helpful. I had some code running Select Max(...) type stuff and this seems a bit more robust.

I love CP.

*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";

ACCESS 2000 & SQL

Bob Stanneveld26-May-04 5:30

Bob Stanneveld

26-May-04 5:30

Hello,

I'm trying to build a database. I'm using ACCESS 2000 to test my queries, but instead of a good start, I'm having a bad one...

When I tried to exucute one of my first queries if faild due to syntax errors. The query looks like this:

CREATE TABLE a
( col_a char(3) not null,
  col_b integer not null default('3'),

  primary key(col_a),
  foreign key(col_b) references b
    on delete no action
    on update cascade
);

The errors I say that I cant use DEFAULT and ON...

Now is my question: Is my syntax that bad, or do I need a complete DBMS (like SQL server 2000) to execute such advanced queries?

Thanks in advandce

A student knows little about a lot.
A professor knows a lot about little.
I know everything about nothing.

Re: ACCESS 2000 & SQL

Michael Potter26-May-04 6:51

Michael Potter

26-May-04 6:51

Every SQL based engine has slightly different syntax. On the DDL side the syntax differences are even more varied.

You will also find many differences on the syntax for JOINS between TSQL and Access queries.

SQL should expand to 'Suggested Query Language'.

Re: ACCESS 2000 & SQL

Bob Stanneveld26-May-04 6:57

Bob Stanneveld

26-May-04 6:57

True, but I looked the syntax up in msdn (even copy/paste the damn code) and it didn't work Mad | :mad:

A student knows little about a lot.
A professor knows a lot about little.
I know everything about nothing.

Re: ACCESS 2000 & SQL

Michael Potter26-May-04 8:19

Michael Potter

26-May-04 8:19

Try:

http://support.microsoft.com/default.aspx?scid=kb;en-us;180841
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q275561

The syntax is a bit more wordy but, serves its purpose.

Re: ACCESS 2000 & SQL

Bob Stanneveld26-May-04 8:29

Bob Stanneveld

26-May-04 8:29

Unfortunately those SQL statements don't work. I guess that I have to test my DBase on a complete DBMS instead of access 2k..

Thanks for your help Big Grin | :-D

A student knows little about a lot.
A professor knows a lot about little.
I know everything about nothing.

Re: ACCESS 2000 & SQL

Serge Lobko-Lobanovsky1-Jun-04 0:41

Serge Lobko-Lobanovsky

1-Jun-04 0:41

Try Tools->Options->Tables and Queries->turn on SQL Server compatible syntax. That could help.

Regards,
Serge (Logic Software, Easy Projects .NET site)

Re: ACCESS 2000 & SQL

Bob Stanneveld1-Jun-04 6:02

Bob Stanneveld

1-Jun-04 6:02

Thanks,

I solved the problem. I did a minimum installation. I figured that the MSDE (Microsoft Database Engine) was needed for the complexe queries.

Everything works fine since I fully installed MS Acces 2k.

A student knows little about a lot.
A professor knows a lot about little.
I know everything about nothing.

@@trancount problem

williamchou25-May-04 21:01

williamchou

25-May-04 21:01

hi,
why after execution of 'begin transaction' the @@trancount=2(original value is 0)?
thanks!

Re: @@trancount problem

Venkatraman25-May-04 22:04

Venkatraman

25-May-04 22:04

When ever a transaction is initiated the @@trancount increases by 1. when a rollback is issued (Even with in nested transaction) the value reset to 0. i am not sure what happens during commit trans (may be it decremented by 1)

Cheers,
Venkatraman Kalyanam
Bangalore - India

Reality bites: I am reality

Re: @@trancount problem

williamchou25-May-04 22:10

williamchou

25-May-04 22:10

yes,commit trans decremented by 1,but after this command ,the @@trancount is
1,not 0,the problem is what makes the strange,the @@trancount incremented by 2?

Re: @@trancount problem

Hesham Amin25-May-04 23:51

Hesham Amin

25-May-04 23:51

william_zhou wrote:
the problem is what makes the strange,the @@trancount incremented by 2?

Do you work in Implicit Transactions mode which may be causing this effect ?

Re: @@trancount problem

williamchou26-May-04 0:03

williamchou

26-May-04 0:03

no,i assure i had not used this mode

Re: @@trancount problem

williamchou26-May-04 0:08

williamchou

26-May-04 0:08

oh,i'm sorry,
i did work in Implicit Transactions mode,but this mode was not setted by me,
now how can i cancel this mode

Re: @@trancount problem

Hesham Amin26-May-04 0:10

Hesham Amin

26-May-04 0:10

SET IMPLICIT_TRANSACTIONS OFF

good luck Smile | :)

Re: @@trancount problem

williamchou26-May-04 0:24

williamchou

26-May-04 0:24

thanks,
but a new problem appears,when i call this proc in my embeded c sql,the return
value is -266,which is the same problem @@trancount,i am nearly insane for this bad luck,why?

Re: @@trancount problem

williamchou26-May-04 15:54

williamchou

26-May-04 15:54

i set implicit_transactions off at the beginning of the proc,such as:
'alter proc sp_myproc @i_input
as
set implicit_transactions
declare @tmp int
...
'
but when i debug it,i find behind the 'set implicit_transactions' is a strange
message:'set implicit_transactions on',now who active this event,why can it happen?

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