VBForums - Database Development

Windows 7 vs XP and ADODB???

aa9gg — Tue, 18 Jun 2013 17:05:33 GMT

I have a VB6 application that uses a ADODB.Connection, Microsoft.Jet.OLEDB.4.0;(MS Access database .mdb). The program runs fine on my XP machine, but when I run it on a Windows 7 machine, I get errors. At first I was getrting a "invalid use of NULL" error. I fixed that by adding " to my text fields (DATA_KEY.Text = C_rs.Fields("Data_key") & "").

Ok...I get passed that error now, but now I noticed that when ever it opens the program with the default "empty" database, the Win7 machine automaticaly adds a new entry and screwas up the key. Again...the XP machine runs the software flawlessly.

What am I doing wrong? is there an issue with Win7?

[RESOLVED] date between ... not working.. :((

developerno1 — Tue, 18 Jun 2013 03:46:04 GMT

i'm trying to generate a report of entries between two dates....
here date1 and date2 are date-time picker values...

Code:

Select * From Expense where supplier = '" & supp & "' AND Date BETWEEN " & date1 & " AND " & date2

and in the db they are date-time values...
but even if i pass the dates as 1/1/2013 and 1/1/2014 it retrieves nothing.. though there are many entries...

Parameter efficiency

Shaggy Hiker — Mon, 17 Jun 2013 21:44:58 GMT

In the interest of wringing a bit of extra speed out of some code, I stumbled across something that surprised me: It looks like using a parameterized query in a SQL query is faster than concatenating values directly into the string. I haven't tested this, but I was wondering if anybody could back that up?

It doesn't seem reasonable, to me, but perhaps it is.

SCHEMA.INI with formatted column???

luca90 — Wed, 12 Jun 2013 09:17:39 GMT

Part of my schema.ini:
[H_-_Report.csv]
MaxScanRows=1
Format=Delimited(;)
ColNameHeader=False
Col1=DR Text
.....
part my test.csv:

122;ecc....
1; ecc...
....

i nedd to format the col1 DR in schema ini with #00000, possible?

in effect when the vb6 code import the csv in table in need in filed DR:
00122
00001
...
and not

122
1
...

Maintaining Code and It Uses LINQ

MMock — Fri, 07 Jun 2013 16:03:03 GMT

Am I in the right forum? I am enhancing an ASP.NET page, but my question has to do with LINQ and getting data from a table, so I hope so...

I've never written a LINQ statement before but that's how this page gets all its data. This page creates a PDF and I am changing a section of the PDF. The data aren't available in a table yet, so I was going to have a temp routine in the code that made and populated the table, then I can "select" from it, and later that code table will be replaced by a SQL table. The table is very simple, two columns Age and Rate and the ages are "<1 - 20", 21, 22 etc to 65+ and the Rate for now is always $100.00.

How do I learn what LINQ syntax to use against this table? I started googling LINQ and was overwhelmed, or confused, because all I've found so far is examples of using it with lists and arrays, and a high-level (no example) explanation of how it's better then SQL queries.

So my opening question is do you have a good tutorial of how to use it with a very simple table that selects every row? You can anticipate other questions as I proceed... Thanks.

VB6 SQL string

hanssnah — Thu, 06 Jun 2013 20:15:21 GMT

I don't know how to describe the problem
I have a sql string that is working

Code:

CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _

     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _

     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _

     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _

     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _

     & "BirdPairing, Species, BreedingCard, Misc " _

     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _

     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID"

I also needed fields( Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10 from table breedingcard)
I've put them in the string
so far so good still working

Code:

CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _

     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _

     & "Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10, " _

     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _

     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _

     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _

     & "BirdPairing, Species, BreedingCard, Misc " _

     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _

     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID"

Then I tried to do this that is working except when Young1 in the table is empty ore 0 then it won't work
( & "Young1.BirdID, Young1.BirdName, Young1.BirdColor, Gender, " _
& "Young2.BirdID, Young2.BirdName, Young2.BirdColor, Gender, " _)

when I leave the last part of ( & "BreedingCard.Young1 = Young1.BirdID And Young1.GenderID = Misc.MiscID AND " _
& "BreedingCard.Young2 = Young2.BirdID And Young2.GenderID = Misc.MiscID")
it works again but the result is wrong

Code:

CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _

     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _

     & "Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10, " _

     & "Young1.BirdID, Young1.BirdName, Young1.BirdColor, Gender, " _

     & "Young2.BirdID, Young2.BirdName, Young2.BirdColor, Gender, " _

     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _

     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _

     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _

     & "BirdPairing, Species, BreedingCard, Misc " _

     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _

     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID And " _

     & "BreedingCard.Young1 = Young1.BirdID And Young1.GenderID = Misc.MiscID AND " _

     & "BreedingCard.Young2 = Young2.BirdID And Young2.GenderID = Misc.MiscID"

I hope this is enough info else let me know

NAVIGATE in recordset...

luca90 — Thu, 06 Jun 2013 11:16:38 GMT

I just have a filled recordset.

i need an aexample to naviagate in this recodrset...

similar with the arrow label:

>>> goto the last rset
> move to next rset
<<< goto the first reset
< move bak one rset

ecc...

note:
i use ado jet and vb6 classic

I don't understand how foreign keys work

jcharles12 — Thu, 06 Jun 2013 10:39:30 GMT

My application is generating a receipt and save to the database.

Someone mentioned I should use foreign keys.

I have created two tables: Receipts and ReceiptDetails

Receipt details table will link the receipt number From `Receipts` and list the items bought.

My questions are: How foreign keys are created? and Why use foreign keys.

I don't understand.

Internal Error 2349 during SQLSERVER 2005 installation in windows7.

sreemonv — Thu, 06 Jun 2013 07:44:38 GMT

Hi
Am new to SQL SERVER and I am trying to install SQL SERVER 2005 in WIndows 7 but it continously end up with the following error message

Code:

Internal Error 2349 during SQLSERVER 2005

Please someone help me...

compact and reapair

luca90 — Thu, 06 Jun 2013 06:40:50 GMT

:sick:I use a routine ADOX and vb6 classic, to compact and repair a Access DATABAE.
In this database are present 2 lionked table from othr database access.

But... if i compact and reapair the master database the routine in ADOX, automaticlly compact and repair the secondary database where the tables are linked on the master database or i need to compact also the sencondary database?

Sorry for my bad english:wave:

[RESOLVED] SQL Server replace HTML anchors in Text Col with different style link

FishGuy — Wed, 05 Jun 2013 11:30:32 GMT

I have data in the my tables which can contain text like the below.

As you can see it can contain HTML Anchors. I need to remove this html, and replace it with a link in the format of {url:http://www.venuesevent.com/qa/,click here}

Quote:

Issue your certificate to you electronically.
To find out more click here blah blah

As you can see it looks like I need to attain the url attribute and the inner text, create the link and replace what is currently in place.

I need to do this in TSQL, however the only information I can find on the net is for removing HTML entirely.

NB: The text in the columns other than the links needs to remain and the data could contain more than one link with various attributes.

[RESOLVED] SQLSERVER 2005: Unable to create new Database

sanju4kk — Wed, 05 Jun 2013 05:56:09 GMT

I am newly installed SQL SERVER 2005 and when i trying to create a new database its showing the following message

Code:

Either the 'admin-PC\admin' user does not have permission to create a new object in 'ADMIN-PC\SQLSERVER', or the object does not exist.

 (Microsoft.AnalysisServices)

admin-pc is my system name and SQLSERVER is my instance name

Please help me to resolve this.......

Thanks in advance

use a LIKE statement in parameter query

luca90 — Tue, 04 Jun 2013 17:07:38 GMT

my piece of code:

Code:

....

VALORE = Trim(Me.TextBox3.Text)



    With CMD

        .ActiveConnection = CONN

        .CommandType = adCmdStoredProc

        .CommandText = "ESTRAI_NOMINATIVO"

        .Parameters.Append .CreateParameter("[NOMIN]", adVarChar, adParamInput, 7, "*" & VALORE & "*")

        Set RS1 = CMD.Execute

        Set CMD = Nothing

    End With

...

naturally dont work:-(

Based VALORE="IP"

i need to get recordset in query based filed NOMINATIVO:

IPA
SAIP
IP
... ecc

Attached Images

[RESOLVED] Problem opening some EXCEL files with bound controls

camoore — Tue, 04 Jun 2013 12:33:41 GMT

I have two problems, of related but different nature. This question refers to only the first :

(VB6 running under XP)

On my form I have placed an ADODC1 and a DATAGRID1. The DATAGRID has its DATASOURCE set as ADODC1.

I am using this simple code to load a .xls data file to the ADODC : The string PATH has been set to the path of the desired .xls file - eg. "C:\Test.xls"

CHARA = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & PATH & ";Extended Properties=""Excel
8.0;HDR=No;IMEX=1"";"

Adodc1.ConnectionString = CHARA

Adodc1.RecordSource = "[SHEET1$]"

Adodc1.Refresh

Adodc1.Caption = PATH

On certain .xls files this works fine, and all the rows/columns appear in the datagrid. However when I attempt to load other .xls data files I get an error message :

"Invalid SQL Statement. Expected Delete, Insert, Procedure, Select or Update."

I suspect the problem lies with the connection string. Can anyone please assist?

camoore

[RESOLVED] SSRS - delete a textbox in a tablix

MMock — Tue, 04 Jun 2013 12:10:29 GMT

I have a report on which goes a zip code and if the US zip code is nine characters instead of five, it wraps to the next line.

To the right of the zip code is a textbox which I don't need. It's always blank and it's just there because it falls in a column. See my attached screen shot.

In Visual Studio with the report solution loaded, I tried to delete the textbox by right-clicking and selecting delete, but it created another one. So, for example, the name of the textbox is Textbox181. I deleted it and another textbox was created in its place called Textbox6. If I delete that, it creates one called Textbox7. I think that would go on indefinitely...

You can see I have eight columns in the tablix. See how the first six rows run across the whole tablix? Can I have Zip run across the remaining two columns on the right? I tried editing the rdl file's xml directly but when VS loaded it I had an index out of bounds error (or something to that affect). I did not create this report initially, but it would be like how Fax is allowed to take up three columns.

Thanks.

Attached Images