Creating multiple sql stored procedures from vb .net - Database Discussion Boards

How do I update a date/time field in a Microsoft Access database using SQL ?

I am using VB .net and up to now all my sql statements work fine.

I have tried several variations of this statement but none work. They all give me a generic "Error in UPDATE statement" message.If i try any other column of my database, the UPDATE statement works fine.It only has a problem with the TIME field which is declared in the database as DATE/TIME.

For instance the following will NOT work.

UPDATE tablename SET timecolumn='12:34' where id=82

Never mind.I figured it out.

I needed brackets for the time field.
So it should be :

UPDATE tablename SET [time]='12:34' where id=80

Hi all,

I am having a table in which one field data type is text and the data stored in it is characters + numbers for example SD01TU9,SD01TU8,SD01TU10.

Now the problem is i want the max from it.As in the above example the max should show SD01TU10 but when i use max function on that field it shows SD01TU9.

Please any one can solve it.

Thanks in advance.

You have a number of choices, you have a requirement to do numerical operations on a field with mixed data.

Create another field and extract the numeric component from the mixed field
Use a view (query in Access I think) to do the same thing just don't store it
Go back to your initial design that put the mixed data in there and address the problem at the source.

As your data seems to be consistent (SD and TU) you could do this with nested replace statements. It gets a little tricky if your text data changes from the standard, then use regex.

Never underestimate the power of human stupidity
RAH

Are you stalking me? Laugh | :laugh:

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

If you need a laugh, check out my Vodafone World of Difference application | If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

If all the strings include the letters "TU" before the numbers at the end, you could use a combination of instr() and len() and right() to get what you need...

I'm doing this from memory and without access to Access to test, but it would be something like this:

clng(right(FIELDNAME, len(FIELDNAME)-instr(FIELDNAME, "TU")))

Which basically says locate the position of TU in the field, then take the characters to the right of them, convert to long and then you can happily find the max of those numbers.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

If you need a laugh, check out my Vodafone World of Difference application | If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

If you have the option, you might consider ensuring that the data all have the same number of digits at end.
E.g. use SD01TU09 instead of SD01TU9, or SD01TU009 if there can be three digits.
This would mean you don't have to monkey around with substrings.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Hi everyone,

I'm creating a database installer where I have some code to create database, tables, stored procedures, etc.

I don't have problems when creating the database, tables and inserting required data. My problem is when my code gets to the stored procedures section.

Here is what I do:

I have different text files (database.txt, tables.txt, insertdata.txt, storeprocedures.txt) where my sql syntax is located.

I execute every process/text file using the following:

ExecuteSql("master", GetSql("database.txt")) ' Creating 1 database.
ExecuteSql("mynewdatabase", GetSql("tables.txt")) ' Creating 15 tables.
ExecuteSql("mynewdatabase", GetSql("insertdata.txt")) ' Inserting data to many tables.
ExecuteSql("mynewdatabase", GetSql("storeprocedures.txt")) ' Creating 6 stored procedures.

Here is part of the error:

In exception handler: Incorrect syntax near the keyword 'PROCEDURE'. "This is the second stored procedure because the variables I get below belong to the second one"

Must declare the scalar variable "@intVariable1".
Must declare the scalar variable "@intVariable1".
Must declare the scalar variable "@intVariable1".
Must declare the scalar variable "@intVariable1".

When trying to create 6 or even 2 stored procedures using the same file ‘storeprocedures.txt' is not possible. If I leave just 1 it works but I really want to keep them together.

Creating 15 tables using the same file 'tables.txt' works, inserting data in different tables using the same file 'insertdata.txt' works.

This is the general syntax I use:

Create Procedure sp_stored1

@intVariablex int
As
Begin
Code
End

Create Procedure sp_stored2

intVariabley int
As
Begin
Code
End

..........

..........

..........

Is is possible to create more than 1 stored procedure the way I'm trying to?

Do I need to end every stored procedure with specific sql syntax, other than the 'end'?

I appreciate your response!!!

Thanks,

Israel

Try placing GO between each store proc creation script to separate each batch

I know the language. I've read a book. - _Madmatt

Thanks Mark but I have tried that and only works when using the sql editor. I actually had to remove the GO from my other text files (database.txt, tables.txt) in order to work.

Thanks,

Israel

iramirezp wrote:
Create Procedure sp_stored1
@intVariablex int

iramirezp wrote:
Create Procedure sp_stored2
intVariabley int

Not sure if this is just a typo, but you seem to be missing a @ in the second Sproc

It was a typo in the example I put just to have an idea how I have the stored procedures lines.

have 2 tables:

Table A: code | name

Table B: barcode | name

Table B has full barcode and name, Table A has only code.

I need to run update query that fill name in Table A.

I tried something like:

update A set name = (select top 1 Name from B where B.Code = mid(A.Barcode,1,8))
but it doesn't work.

update A set name = (select top 1 Name from B where B.barcode = mid(A.code,1,8))

Does that work any better? Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

'mid' is not a recognized built-in function name.

Then you will need to find out what built-in function does string extraction and replace 'mid' with that function name. It probable that the parameters will be different to. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

and that is one more reason why giving appropriate names to everything is important.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

I have a datagridview control on my win form, pls how do i get it to show multiple tables records.
thanks .

get your tables from the database
put them all in the same dataset
create the relationships between the tables
set the dataset as the datasource for the DGV

[edit] this belongs in the winforms forum, but the above is the way to do it [/edit]

Never underestimate the power of human stupidity
RAH

I'm preparing an WPF article that creates storages automatically from classes with nesting and else.
The issue is that in the ACCESS db execute , everything works fine but images bigger than 12 KB I get the error:
'System resource exceeded.'

I show the process:

public Database db;
public DBEngine engine;
this.db = this.engine.OpenDatabase(file, Missing.Value, Missing.Value, Missing.Value);
access.db.Execute(String.Concat(query, values), Missing.Value);

in values, the data is directly:

0x89504E47...454E44AE426082

It works with small images, and if I add with the Access directly design it works, so the field type is right, Maybe is an issue with connection size properties. Does any store a big image with DAO12,ADO, or OLEDB with an Insert statement?

Thanks in advance, a great article is coming...

Juan Pablo G.C.
Overrider Blog

Whenever I have needed to use images with an Access database, I have always stored the images in the file system and then just a pointer to the image in the database (an url, path or whatever is appropriate for the app). Access does not play well with images when trying to store them in the database.

Of course, you can always do that, but is a test, in access I can do, and I would like to know how to do in code.

Juan Pablo G.C.
Overrider Blog

Hello,

in one of my tables, I'm using Int32 as a data type for the identity column.

This Int32 identity value is autogenerated. Now it has reached the maximum value for Int32, although the total number of rows is "only" 1.6 billion, because some rows were deleted and because I used an identity seed of 400 million on this column when creating the table for various reasons.

Now, no new rows can be added to the table, because the Int32 autogenerated identity column value is hitting the maximum value for Int32.

One of the options would obviously be to change the data type of the identity column to another data type, let's say Int64 (biginit in MS SQL terms). However, changing Int32 to Int64 on 1.6 billion rows might seem like a small step for a human, but it's a big step for such a database..

Even if I turn off all logging, I switch the recovery model to simple and I delete all indexes on this table, the process of changing Int32 to Int64 on this column fails since there's not enough disk space to make this change. I'm using two 1000 GB hard drivers in RAID0, so the total space is 1000GB and the database with all indexes is 315 GBs big. For some reason, even 700 GBs is not enough for this operation.

Since there are still roughly 500 million values for this Int32 identity column theoretically available, I'm thinking my best bet at this point would be to try to reorganize the identity column values so that they reorder themselves to begin with 1 and will defragment themselves so there will be no holes in the used Int32 values for the identity column.

But I haven't found a way on how to do this. Does anyone know if this is possible?

Thanks very much for any input,
Michal

You could do this by using the CASCADE ON UPDATE for Foreign Key relationships to the Identity PK that you are having problems with. If you try this, make sure that you have thoroughly tested it many times before attempting it on your production server. Even then, I would be extremely concerned about taking this route.

If I had your problem, I would pay for a Sql Server DBA expert to come in and advise on how to partition your data to enable you to change the PK to a BigInt.

Turn off logging, switch to simple recovery, then use a SELECT INTO statement to make a copy of the table (but with bigint column instead of int). SELECT INTO is a bulk copy operation which requires minimal resources.

GO
SELECT CAST(ColumnName AS BIGINT) AS ColumnName,
       OtherColumns
  INTO NewTable
  FROM Table1;
GO
DROP TABLE Table1;
GO
-- Use sp_rename to rename NewTable to Table1
GO
-- Re-add primary key/constraints/etc.
GO