Re: ODBC - Visual Basic Discussion Boards

Stephen Lintott27-Jan-09 21:08

27-Jan-09 21:08

Hi all. I am currently looking to insert a value into a memo field in vfp using a odbc connection but keep getting a error (ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Data type mismatch). Does anybody know if this is supported by the odbc connection object or does anybody have sample code to do this. The sql is correct this thing just fails with the odbc connection.

Stephen Lintott Bsc IT (RAU)

Re: ODBC

Jay Royall27-Jan-09 22:37

Jay Royall

27-Jan-09 22:37

You say that the SQL is correct, what type of database have you tested it on? The reason I ask is because I believe that the required type of data can vary between database engines.

If you haven't done so already (and I apologise if you have), I would test the SQL on the VFP database directly.

Re: ODBC

Wendelius27-Jan-09 22:43

Wendelius

27-Jan-09 22:43

Could yo post the code. Perhaps the problem is in the parameter usage.

The need to optimize rises from a bad design.My articles[^]

Re: ODBC

Stephen Lintott28-Jan-09 1:31

Stephen Lintott

28-Jan-09 1:31

After a discusion with the developer in charge of this I found out that the following sql statement causes the break. If anyone can see why I would surely appreaciate it. I sure can't it seems correct to me.

INSERT INTO OrdHeader (Uniqueid,trantype,ledgerno,accountid,userno,entrydate,entrytime,lineno, trandate,periodno,batchno,internref,externref,hedgecode,logged,descriptn,payee,presented,held,previous,ositem,ledger2, account2,taxtype1,taxcode1,taxamount1,taxtype2,taxcode2,taxamount2,taxtype3,taxcode3,taxamount3,salescode,costcode,stage, branch,delivery,quantity1,quantity2,quantity3,amount1,amount2,amount3,amount4,amount5,cncycode,exchange,netvarnce,netgain, netloss,taxrebate,duedate,rateid,staffid,complqty,ppsrate,purchtype,isjobquote,rpsgross,recordno,temppres,changed,discperc, taxperiod,zcostproc,zpostatus) Values(5,49,2,'CNBPZF@CQU',1, CTOD('1/26/2009'),58694.616,0,CTOD('12/31/2008'),78, 356550,'PO-JAC01360         ','PO-JAC              ','N','F' ,'Purchase Order                                                                                                                                                                                                                                                 ','                                   ',0,'F','F' ,'F',2,'CNBPZF@CQU',' ',0,0 ,' ',0,-69.96,' ',0 ,0,0,'         0','         0',1 ,CTOD('12/31/2008'),0,0,0 ,-699.56,0,-699.56,0,0 ,0,1,0,0,0 ,0,CTOD('12/30/1899 12:00:00 AM'),'F          ','F          ',0 ,0,0,'F',0,0 ,0,'T',0,0,' ' ,' ')

Stephen Lintott Bsc IT (RAU)

Re: ODBC

Wendelius28-Jan-09 1:46

Wendelius

28-Jan-09 1:46

If you execute that directly against the database without your application, does it fail?

The need to optimize rises from a bad design.My articles[^]

Re: ODBC

Stephen Lintott28-Jan-09 2:56

Stephen Lintott

28-Jan-09 2:56

It fails when running it against a RBDMS. The booleans don't cause any havoc. It inserts to9 the Description field and then no more

Stephen Lintott Bsc IT (RAU)

Re: ODBC

Wendelius29-Jan-09 9:33

Wendelius

29-Jan-09 9:33

Since I don't know the structure of your table, I'm not able to sy if all the values are in correct places. However, you could check if the pairs match (if not done so already)

Here's the colum/value list in different format. The Descriptn value look quite large, perhaps it's over the max length?

Uniqueid            5	
trantype            49	
ledgerno            2	
accountid           'CNBPZF@CQU'	
userno              1	
entrydate           CTOD('1/26/2009')	
entrytime           58694.616	
lineno              0	
trandate            CTOD('12/31/2008')	
periodno            78	
batchno             356550	
internref           'PO-JAC01360         '	
externref           'PO-JAC              '
hedgecode           'N'
logged              'F' 
descriptn           'Purchase Order                                                                          
                                                                 
                                                  
                                                    '
payee               '                                   '
presented           0
held                'F'
previous            'F' 
ositem              'F'
ledger2             2
account2            'CNBPZF@CQU'
taxtype1            ' '
taxcode1            0
taxamount1          0
taxtype2            ' '
taxcode2            0
taxamount2          -69.96
taxtype3            ' '
taxcode3            0
taxamount3          0
salescode           0
costcode            '         0'
stage               '         0'
branch              1
delivery            CTOD('12/31/2008')
quantity1           0
quantity2           0
quantity3           0
amount1             -699.56
amount2             0
amount3             -699.56
amount4             0
amount5             0
cncycode            0
exchange            1
netvarnce           0
netgain             0
netloss             0
taxrebate           0
duedate             CTOD('12/30/1899 12:00:00 AM')
rateid              'F          '
staffid             'F          '
complqty            0
ppsrate             0
purchtype           0
isjobquote          'F'
rpsgross            0
recordno            0
temppres            0
changed             'T'
discperc            0
 taxperiod          0
zcostproc           ' ' 
zpostatus           ' '

The need to optimize rises from a bad design.My articles[^]

Re: ODBC

Ben Fair28-Jan-09 2:50

Ben Fair

28-Jan-09 2:50

I notice multiple booleans that appear as single character strings ('T' or 'F'). Are those
fields actually character type or boolean type? In VFP .T. and .F. are used to indicate
booleans. If you are using parameters for the ODBC command and assign a boolean value to them it should handle them for
you, but if you are specifying their values in the command text then you may need to use the VFP boolean syntax.

Hope in one hand and poop in the other; see which fills up first. Hope and change were good slogans, now show us more than words.

FindFile file type question

monsieur_jj27-Jan-09 19:26

monsieur_jj

27-Jan-09 19:26

Hi all,

I am new to vb and I am trying to get the file type of every file found during findfile here is my code:

iSearchHandle = FindFirstFile(SrcDir, pFindFileBuff)
    If iSearchHandle <> INVALID_HANDLE_VALUE Then

        strFile = TrimNull(pFindFileBuff.cFileName)
        Do While strFile <> ""
            Do While FindNextFile(iSearchHandle, pFindFileBuff)
            strFile = TrimNull(pFindFileBuff.cFileName)
            If strFile <> "." And strFile <> ".." Then
            strFile = strDir + "\" + strFile
            'strLen = Len(strFile)
            
            fileType = Right$(strFile, 4)

I tried using Right$ to get the file type for example "TonyRobertson.ppsx", ,"version2.5.0.doc", "IMIT031211_TonyRobertson.ppt" and "MARBLES.G4"

by using that I get ppsx, .doc, .ppt and S.G4, is there another way to get the right format?

Thanks,
Jj

Re: FindFile file type question

Ashutosh Phoujdar27-Jan-09 20:13

Ashutosh Phoujdar

27-Jan-09 20:13

Hope you are using VB6.
There are two options to get the file type e.g.
1. Split the filename using (.) dot character and get last element in array.
2. Use FileSystemObject, that provides a lot more options on file information. For more information : Click[^]

PS: If you are using VB.NET then there are lot of utility classes in System.IO Namespace[^]

dnpro
"Very bad programmer"

My Latest Articles
RichTextBox Control with Find functionality
LogViewer - A Simple Log Listening Utility

how to create line graph using C#

karthick sampangi27-Jan-09 18:59

karthick sampangi

27-Jan-09 18:59

Hi,
I'm trying to create linear graph using C# in windows application
please help me to solve this problem. D'Oh! | :doh:

Re: how to create line graph using C#

N a v a n e e t h27-Jan-09 19:02

N a v a n e e t h

27-Jan-09 19:02

karthick sampangi wrote:
I'm trying to create linear graph using C# in windows application

Your question is vague. What you have tried so far and where you are facing problem? You can draw it using the classes in System.Drawing[^] namespace.

Smile | :)

Navaneeth

How to use google | Ask smart questions

Re: how to create line graph using C#

Ashutosh Phoujdar27-Jan-09 20:07

Ashutosh Phoujdar

27-Jan-09 20:07

Please try to search in code project article base. CP itself have nice project for drawing line graphs Click here to get one[^]

dnpro
"Very bad programmer"

My Latest Articles
RichTextBox Control with Find functionality
LogViewer - A Simple Log Listening Utility

Re: how to create line graph using C#

dan!sh 27-Jan-09 23:57

dan!sh

27-Jan-09 23:57

You can use third party tools available. If you do not want to do that, you can create excel chart or do as Navneeth suggested.

The word "politics" describes the process so well: "Poli" in Latin meaning "many" and "tics" meaning "bloodsucking creatures."

जय हिंद

Anybody help me in CR

BalasubramanianK27-Jan-09 18:23

BalasubramanianK

27-Jan-09 18:23

Hi,

I'm using cr 8.5 and vb6. I'm using crystal report control component in my appln. I've created report with two parameters. My report is working well alone by refreshing and passing parameters. But I'm facing problem in vb6, while I call report with passing parameters. I'm getting Invalid parameter field value in "cr1.Action = 1" line. This is my code below.

       cr1.ReportFileName = App.Path & "\rptCovLetToCommodityPmnts.rpt"
       cr1.ParameterFields(1) = "pBankCode:" & lstrBankCode & ":True"
       cr1.ParameterFields(2) = "pDate:" & Format(cmbPaymentDate, "dd/MM/yyyy") & ":True"


cr1.Action = 1

Pls. anybody give me the soln. I tried in different ways. Tnx in adv.

Balasubramanian K.

Re: Anybody help me in CR

Jon_Boy28-Jan-09 5:35

Jon_Boy

28-Jan-09 5:35

Its been a while since I've used CR8.5 and VB6. Just eyeballing the code, I think it should be something like the following

cr1.LogonInfo(0) = strLogin 'This will depend on how your report is setup.

cr1.ReportFileName = App.Path & "\rptCovLetToCommodityPmnts.rpt"

'I **think** the params are zero based and pass the param value only.
cr1.ParameterFields(0) = lstrBankCode
cr1.ParameterFields(1) = cmbPaymentDate
cr1.Destination = crptToWindow

cr1.Action = 1

If you are using MS SQL for the report, you can open profiler to see exactly what Crystal is using as the query to see what is wrong as well.

Hope this points you in the right direction.

Any suggestions, ideas, or 'constructive criticism' are always welcome.

"There's no such thing as a stupid question, only stupid people." - Mr. Garrison

reading a text file from the resource file

moonshaddow27-Jan-09 15:16

moonshaddow

27-Jan-09 15:16

hi i am using a stream reader to read a text file from the resource file, i have tried two way to read each line and put it into i combobox but i get the following errors, can someone point out where i am going wrong, thanks in advance

Using readfile As StreamReader = File.OpenText(My.Resources.mages)'Illegal characters in path.
                        Do 
                            line = readfile.ReadLine
                            My.Forms.Main.cbspells.Items.Add(line)
                        Loop Until line Is Nothing
                        readfile.Close()
                    End Using

Using readfile As StreamReader = New StreamReader(My.Resources.magea)'Empty path name is not legal.
                        Do 
                            line = readfile.ReadLine
                            My.Forms.Main.cbabilities.Items.Add(line)
                        Loop Until line Is Nothing
                        readfile.Close()
                    End Using

J.Hardy

Re: reading a text file from the resource file

Ashutosh Phoujdar27-Jan-09 21:23

Ashutosh Phoujdar

27-Jan-09 21:23

Can you please try something like this :

Dim line As String
For Each line In My.Resources.mages.Split(System.Environment.NewLine)
    If line <> String.Empty Then
        My.Forms.Main.cbspells.Items.Add(line)
    End If
Next
For Each line In My.Resources.magea.Split(System.Environment.NewLine)
    If line <> String.Empty Then
        My.Forms.Main.cbabilities.Items.Add(line)
    End If
Next

Since My.Resource.<<resource-name>> will return content of resource, and it is not suitable to reading with StreamReader.

Please reply if this works for you Smile | :)

dnpro
"Very bad programmer"

My Latest Articles
RichTextBox Control with Find functionality
LogViewer - A Simple Log Listening Utility

Re: reading a text file from the resource file

moonshaddow28-Jan-09 12:13

moonshaddow

28-Jan-09 12:13

that works fine thank you Thumbs Up | :thumbsup:

J.Hardy

Looking up Cell data with Mutiple Excel Workbooks running.

frankiebaby227-Jan-09 13:19

frankiebaby2

27-Jan-09 13:19

Hi,

If I have more thank one Workbook open and running in the background, how do I specify which wookbook my "" line is to look up?

(See the "PROBLEM IS HERE" & "AND HERE" lines below to see where I require help with code. Something must be insertable I figure.)

The other option might be in the module where the declairations are. Maybe it's possible to link the "xlsCellTs" to "xlsSheetTs" and or "xlsBookTs".

Try
xlsBookTs = xlsApp.Workbooks.Open("F:\filelocation\ts" & Format_(Me.MonthCalendar1.SelectionStart, "yyyyMMdd") & ".xls")
ExcelTsOpen = True
'Counts slots used and assigns value to tbxTinsToday
SlotsUsedToday = 0
TotalSlots = 0
NRow = 2
'Learns how many Total Slots are avail on any day. 
PROBLEM IS HERE (Below)!!
Do Until xlsCellTs.Range("A" & NRow).Value = ""
TotalSlots = TotalSlots + 1
tbxTotalSlots.Text = TotalSlots.ToString
AND HERE (Below)!!
If xlsCellTs1.Range("F" & NRow).Value = 1 Then
SlotsUsedToday = SlotsUsedToday + 1
End If
NRow = NRow + 1
Loop
tbxSlotsToday.Text = SlotsUsedToday.ToString
Catch ex As Exception 'Blank Date not previously used open SlotStatsIST.xls
xlsBookTs = xlsApp.Workbooks.Open("F:\filelocation\SlotStatsDefault.xls")
ExcelTsOpen = True
xlsSheetTs = xlsBookTs.Worksheets(1)
xlsBookTs.SaveAs("F:\filelocation\ts" & Format(Me.MonthCalendar1.SelectionStart, "yyyyMMdd") & ".xls")
ExcelTsSaved = True
End Try

Many thanks in advance to all helpers.

Re: Looking up Cell data with Mutiple Excel Workbooks running.

Dave Kreskowiak28-Jan-09 3:06

Dave Kreskowiak

28-Jan-09 3:06

It looks like you already specified which workbook to use when you started using the Range object of the xlsBookTs object you created. Or are you asking how to specify which worksheet to use?

In that case, you can either use the corresponding Worksheet object in the Workbook's Worksheets collection, or you can directly specify which worksheet to use in the Range address specifier (.Range("A" & NRow) in your code). This would look something like .Range("SheetName!A" & NRow).

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

Re: Looking up Cell data with Mutiple Excel Workbooks running.

frankiebaby228-Jan-09 6:04

frankiebaby2

28-Jan-09 6:04

I think we are getting close thanks to your help Dave.

I failed to mention the other workbooks that would be open due to trying to keep the code example to a minimum, however I am including a sample of the Module code below to show what other delairationa may be floating around in use at any given time.

Public xlsBookFds As Excel.Workbook
    Public xlsSheetFds As Excel.Worksheet
    Public xlsCellFds As Excel.Range

    Public xlsBookTs As Excel.Workbook
    Public xlsSheetTs As Excel.Worksheet
    Public xlsCellTs As Excel.Range
    Public xlsCellTs1 As Excel.Range

    Public xlsBookMdrs As Excel.Workbook
    Public xlsSheetMdrs As Excel.Worksheet
    Public xlsCellMdrs As Excel.Range

In the format you mentioned;

.Range("SheetName!A" & NRow).

is the

the code so the computer can seperate the sheet name from the cell location? That being the case I might hazard a guess that the code

.Range("BookName!SheetName!A" & NRow).

might be right.

Can thank you enough for your help so far Dave, looking forward to your reply.

Regards,

Frank Smile | :)

Re: Looking up Cell data with Mutiple Excel Workbooks running.

frankiebaby228-Jan-09 6:05

frankiebaby2

28-Jan-09 6:05

*Can't thank you enough* I meant. Oops

Re: Looking up Cell data with Mutiple Excel Workbooks running.

Dave Kreskowiak28-Jan-09 16:22

Dave Kreskowiak

28-Jan-09 16:22

frankiebaby2 wrote:
is the !the code so the computer can seperate the sheet name from the cell location?

That's how Excel seperates worksheet names from cell names, yes.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

VBA Access design (sql) view

FMpol27-Jan-09 10:41

FMpol

27-Jan-09 10:41

I need help figuring out how to display the sql view of a query using VBA. I have an Access 2003 database that contains a little over 300 queries. Yikes!

I know in Access you can open a query in Design view and then change the view to SQL view so that you can actually see the Select statement. Instead of doing that more than 300 times to cut and paste the query statement, I would like to write a quick macro to display the information.

I was able to figure out how to loop through all my query objects and display the name, but I can't find a way to actually get at the sql view of the query. Below is the code I have:

Dim obj As AccessObject
Dim dbs As Object
Set dbs = Application.CurrentData

For Each obj In dbs.AllQueries
MsgBox "sql view: " & obj.FullName
Next obj

When I watch the obj data, there isn't any properties for the sql view. I thought this information would be in there. Am I misunderstanding what this object contains? It seems like I'm pretty close because I'm able to see the name of all the queries I have in my DB. What crucial piece of information am I missing?

Thanks,
FMPol

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